浅谈sql优化+索引

sql优化常见问题

参考自-小林coding文章-https://www.cnblogs.com/xiaolincoding/p/15839040.html

什么是索引

索引是已经排好序的有序数据结构，类似于目录，当通过索引去查询数据时，能大大提高查询效率。

如何诊断一段sql是否使用索引

explain 关键字
explain + sql

type, 表示sql性能级别, 如果是all，则走全表查询
possible_keys 表示sql查询用到的索引
key 表示sql实际查询时决定使用的索引
rows 表示sql查询时必须检查的行数

mysql默认的存储引擎和特点

mysql默认使用innoDB，它使用B+树作为索引的数据结构。
还可以选择mylSam，支持多种数据结构，B+树，R树，Full-Txt。mylSam在建表时也会默认用主键建立一个B+树索引。

innoDB 叶子节点存储数据本身

mylSam 存储数据的地址

聚簇索引和二级索引

InnoDB 存储引擎根据索引类型不同，分为聚簇索引（上图就是聚簇索引）和二级索引。它们区别在于，聚簇索引的叶子节点存放的是实际数据，所有完整的用户数据都存放在聚簇索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。

索引失效的情况

• 使用 % 在前进行模糊查询， like %xx 或者 like %xx%
• 对索引使用函数，索引中保存的是索引的原始值，而不是函数计算后的值，无法对函数计算后的值比较
• 对索引进行表达式计算，select * from t_user where id + 1 = 10; 索引存储的是id的值，而不是id+1的值，只有将所有id值拿出并+1进行比较。
• 对索引类型进行隐式转换， phone = 1300000001，phone是字符类型
• 联合索引最左匹配原则
• or条件中有字段没有索引，or 两边条件满足一个即可，此时走索引没有意义

联合索引

多个字段组合在一起创建的就叫联合索引。

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

mysql为什么使用B+树/优点

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。