Mysql索引进阶篇

Explain命令详解

type

• ALL：全表扫描
• index：基于索引的全表扫描（多用于排序中涉及到索引）
• range：基于索引的范围查询
• ref：表示使用非唯一索引进行单值查询
• eq_ref：出现在多表连接查询的情况下，表明前面的记录都只能匹配后面表的一行
• const：使用主键或唯一索引进行等值查询

extra

• using where：查询的时候回表==> 纠正：表示针对于查询的结果还需要根据where后面的条件进行筛选才能出来结果

  例：select * from where id%2=0

• using index：使用到索引并且不涉及到回表

• using filesort：对结果进行额外排序

• using temproary：查询中使用到了临时表空间（一般针对于去重）

• Using index condition 说明使用了索引下推。

filtered

• filtered是越大越好吗？

  引擎层向server层是以row为单位进行传输数据的，然后需要在server进行过滤，经过过滤后符合条件的数据越多，则filter越大

高阶问题

主键索引和唯一索引的区别

• 唯一索引可以要求字段为空，主键索引字段不能为空

普通索引和唯一索引的区别

• 在写入性能上有什么差异

  针对于普通索引，innodb会在buffer pool中有一块change buffer，针对于非唯一索引的索引页的更新，会先将操作缓存在change buffer中，等到该数据被查询的时候

Innodb要求表必须有聚簇索引

Innodb因为需要通过聚簇索引来组织数据，因此要求必须包含聚簇索引

• 如果有主键的话，会选用主键做聚簇索引
• 如果没有主键的话，会选择第一个不包含null的唯一列作为聚簇索引
• 上面两个都没有的情况下，Innodb将自动新增一个唯一的列row_id作为聚簇索引

索引失效的情况

• 联合索引非最左前缀匹配

  • llike ‘%xx’

    特例：如果name列上有索引，那么select name from user where name like '%xx%'会走索引，因为会存在覆盖索引的情况，所得是索引全表扫描

  • 特列：例如表中有（a, b, c）联合索引，如果我查询 a = 1 and c=2这样索引会失效吗？

    • Mysql 5.5的话，会先根据联合索引查出a=1的记录，然后根据主键进行回表，拿到数据后在server层进行比较
    • Mysql5.6的话，会进行索引下推，在存储引擎遍历的过程中，对索引中包含的字段先做判断，直接在引擎层过滤掉不满条件的行，以此来减少回表的次数
    • 联合索引在遇到（>，<）的时候，就会停止匹配**，也就是范围查询的字段可以用到联合索引，但是范围查询之后的字段就用不到联合索引了，但是针对于(>=， <=，between and，like前缀匹配)，并不会停止匹配**

• 使用函数

  执行 select "10" > 9结果为1，从结果来看证明mysql会把字符串转换成int然后进行比较

• 进行表达式计算

  例如 select * from user where age + 1 > 10，但是如果把当前语句变换成 select * from user where age > 10 -1就可以使用索引了

• where 子句中的or

是否适用索引的情况

适用索引的情况

• 字段唯一，例如商品编码
• 经常出现在GROUP BY、where和ORDER BY中的

不适用索引的情况

• 经常会进行修改的字段，例如用户的余额
• 字段中区分度比较小的，例如性别
• 不出现在GROUP BY、where和ORDER BY中的

索引优化的经验

• 前缀索引优化

• 覆盖索引优化

  假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

  可以建立联合索引（商品ID、名称、价格）联合索引

• 防止索引失效

• 主键索引最好是自增的

  如果不是有序的，会进行页分裂，出现碎片

• 索引字段尽量为非null

分页查询的优化

使用子查询进行优化

select * from teacher where id >= (SELECT id FROM teacher limit 10000,1) LIMIT 10

---

参考链接：
《小林coding索引常见问题》

《Mysql实战45讲》

《MySQL技术内幕：InnoDB存储引擎》