mysql优化-索引优化

本文主要是介绍mysql优化-索引优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、索引设计原则

索引是优化数据查询效率的一种数据结构；

1. 对查询频次高且数据量比较大的表建立索引；
2. 索引字段的选择：最佳选列应从where子句中提取，也就是经常出现在条件语句中的字段；
3. 唯一索引：区分度越高，使用索引的效率越高；
4. 最左前缀法则：对N个列组合而成的组合索引，相当于创建了N个索引，当查询时where子句中使用了组成该索引的前几个字段，那么这条查询sql可以利用索引来提升查询效率；
   create index idx_name_eamil_status on tb_seller(name,email,status)
   where子句包含的索引字段是从最左侧的name字段开始且不跳过中间字段就会走这个复合索引

二、sql优化步骤

5. 查看sql执行频率（show global status like ‘Com_%’ ）,可以查看到这个数据库的查询和更新次数；
6. 定位低效率sql语句：慢查询日志、show processlist

三、索引的使用（避免索引）

1. 全值匹配（对索引中所有列都指定具体值），在该情况下，索引生效，执行效率高
2. 最左前缀法则：针对复合索引（包含最左列，不跳过中间列），违反最左前缀法则索引失效
3. 范围查询最右边的列，不能使用索引
   select * from table where name=‘xx’ and status> ‘1’ and address=‘北京’
   字段name和status走了索引，而address没有走索引
4. 不要对索引列进行运算操作，否则索引失效
5. 字符串不加单引号，索引失效
6. 尽量使用覆盖索引，避免使用select *
   覆盖索引就是包含的索引字段（不用回表查询）
7. 用or分割开的条件，如果or之前的条件有索引，or之后的条件没有索引，那么整体都不会走索引
8. 模糊查询：以%开头的like模糊查询，索引失效（只有当%位于最右边，索引生效）
   对于实际开发中的 like name=‘%哈哈%’的模糊查询，解决办法：通过覆盖索引解决，也就是不要使用select *，查询索引字段
9. 如果mysql评估发现全表扫描比索引更快，就不会走索引
10. is null和is not null有时会索引失效（当大部分都为null时，则不会走索引，is not null走索引）
11. in走索引，not in不走索引
12. 尽量使用复合索引，减少单列索引（多个单列索引，数据库会选择一个最优的单列索引来使用，并不会使用全部索引）

四、sql优化

1. insert优化：①批量插入 insert into tb_test values(1,‘Tom’),(2,‘Cat’),(3,‘June’)
   ②在事务中插入
   start transaction;
   insert into tb_test values (1,‘Tom’);
   commit;
   ③有序插入，尽量按照主键的顺序插入
   尽量使用多表联结查询来替换子查询

2. or优化：在or前后的字段都需要有索引，倘若有一方没有索引，则该语句整体不走索引；
   可以使用union来替换or
   select * from tb_test where id=1 union select * from tb_test where age=22

五、ACID底层实现原理

• 原子性（Atomicity):事务是一个不可分割的单位，是一个最小的操作单元；这个单元的操作要么全部成功，要么全部不成功。如果某一个SQL语句执行失败了，那么之前执行的SQL语句要执行回滚操作。实现原理：基于Undo log。Undo log会记录所有操作，一旦发生回滚，数据库就会按照Undo log做相反的操作，比如记录的是插入，那么数据库便会进行删除操作。

• 一致性（Consistency):事务执行之后，数据库的完整性约束没有被破坏，事务执行前后都是一个合法的数据状态。完整性体现在比如数据库的主键要唯一，字段类型大小要符合要求，外键的约束要符合要求。一致性是事务追求的最终目标。原子性、持久性、隔离性都是为了保证数据库最终的一致性。如果另外三个特性无法保证，那么一致性肯定也保证不了

• 隔离性（Isolation):写写操作：通过锁机制，保证当前只能有一个事务来操作某个数据。

• 持久性（Durability):实现原理：redo log。数据库除了要把数据写到Buffer中去，还会把内容记录到redo log里面，如果Mysql 宕机了，那么可以通过Redo log去恢复数据。Redo log是预写式日志，会把要进行的修改先写入到Redo log中，再更新到Buffer中。

六、并发事务处理带来的问题

1. 丢失更新：后一个事务修改提交覆盖了前一个事务的修改操作
2. 脏读：一个事务修改了某一数据但未提交，而另一个事务读取并使用了该数据
3. 不可重复读：一个事务读操作前后数据不一致
4. 幻读：一个事务按照相同的查询条件读取数据，发现其它事务插入了满足其条件的新数据

为了解决以上问题引入了事务隔离级别

mysql的默认事务隔离级别是：不可重复读

七、锁

共享锁（只能读）
排它锁（可以读写）
间隙锁：用范围条件查询数据时，对于键值在条件范围内但不存在的记录，叫做‘间隙’，innodb也会对这个间隙加锁，这种加锁机制就是’'间隙锁"
比如 select * from tb where id <10
其中id=1，2，3，4，6，9；
对于id=5，7，8的则为间隙

八、慢查询日志

1: 开启慢查询日志 slow_query_log=1
2: 设置慢查询日志文件名 slow_query_log_file=“文件名”
3: 设置时间限制 long_query_time=10 (默认10s)

这篇关于mysql优化-索引优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！