【MySQL】索引详解

索引就是帮助MySQL高效获取数据的数据结构。

索引的劣势

• 索引也要占用空间；
• 虽然索引大大提高了查询速度，但是会减低更新表的速度；

索引分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引；
• 唯一索引：索引列的值必须唯一，但允许有空值；
• 复合索引：一个索引包含多个列；

索引创建的时机

• 主键自动创建唯一索引；
• 频繁作为查询条件的字段应该创建索引；
• 查询中年与其他表关联的字段，外键关系建立索引；
• 频繁更新的字段不适合创建索引，因为每次更新需要维护索引；
• Where条件里用不到的字段不创建索引；
• 单值/组合索引的选择问题：在高并发推荐使用组合索引；
• 查询中排序的字段适合创建索引，可以使排序速度加快；
• 查询中统计或者分组字段也可以创建索引；

不适合创建索引的情况

• 数据量较小；
• 增删改操作较多的表；
• 记录中重复字段较多的，比如性别只要男/女之分，这个字段创建索引就没什么意义；

索引失效的几种情况

• 最佳左前缀法则
• 不再索引列上做任何操作（计算、函数或类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引范围条件右边的列
• 尽量索引和查询列一致
• 使用了 != 或 <>
• is null 或 is not null
• like 以通配符开头
  可以建立组合索引解决使用 like 条件使索引失效问题。
• 字符串不加单引号，如果不加数据库会做一次隐式类型转换。
• 使用 or 条件
  背诵口诀

1.带头大哥不能死；
2.中间兄弟不能断；
3.索引列上不计算；
4.like百分加右边；
5.范围之后全失效；
6.字符串里有引号；

索引使用建议

连接查询时，左连接索引建立在右表的字段中；
连接时以小表驱动大表

经典场景

MySQL底层对语句进行优化，将顺序进行调整

create index idx_c1234 on test(c1, c2, c3, c4);

# 使用了索引：1234
explain select * from test where c1="a1" and c2="a2" and c4="a4" and c3="a3"
# 使用了索引：1234
explain select * from test where c1="a1" and c2="a2" and c4>"a4" and c3="a3"
# 使用了索引：123
explain select * from test where c1="a1" and c2="a2" and c4>"a4" order by c3
# 使用了索引：12，using firesort
explain select * from test where c1="a1" and c2="a2" order by c4
# 使用了索引：1,23用于排序
explain select * from test where c1="a1" and c5="a5" order by c2,c3;
# 使用了索引：1, 但出现了using firesort
explain select * from test where c1="a1" and c5="a5" order by c3,c2;
# 使用了索引：12, 此时无using firesort，因为 c2在条件里面已经限制成常量了
explain select * from test where c1="a1" and c2="a2" order by c3,c2;