MySQL索引优化

from 仙士可

MySQL索引优化

1 什么是索引

索引就相当于字典前的目录，如果这个目录划分规划更好，那么我们找到想要的数据就会更方便，也就提高了查询的效率。
即索引是已排好序的一种数据结构。

2 适用场景

索引的更新会随着数据的变动而变动，所以不是单纯建的索引越多越好，索引也会在更新数据时消耗资源。一般是为了提高查询效率才去建立索引，所以主要针对做查询条件的字段。常见的：商品名，用户账号，手机号，账单号，订单号，日期时间。

3 使用注意

对较长字段建索引时，可能会导致索引占的空间太大了，所以可以使用前缀索引

4 索引不生效场景

• 当语句中带有or的时候，索引会失效
• 当索引的字段使用like查询，并且使用了前通配比如%Siam，索引失效。后通配会生效Siam%
• 当索引的字段是字符类型，但是储存的值是数字，比如 user_name:’123456’，在查询语句中要 user_name = ‘123456’而不能 user_name = 123456 否则发生类型转换，索引失效，其他类型的字段 比如日期等 也同理
• 当使用的条件语句，预计结果数量超过全表数据的一定比例时，会转为全表扫描（mysql一般是30%左右）这就是为什么在建立索引的时候要选择维度（区别度）比较高的列，性别这种字段不适合建立索引。
• 语句中出现列数据运算才判断的，比如where age – 10 > 0 每一行都要运算之后才知道是否大于0 所以就是全表扫描，如果age > 10 则可使用索引。使用函数转换列数据也一样原理。
• 组合索引时使用的条件语句。

多个单字段索引冲突

select * from test where user_name = 'siam_007' and create_time = 1563280050
假设user_name和create_time都建了索引，sql实际执行时回两个索引都用吗？
Explain select * from mysql_index_test where user_name = 'siam_007' and create_time = 1563280050
可见实际用的索引是time：

组合索引

即多个字段组合的一个索引，字段顺序很重要。如一个表建了一个索引 test_index，创建时字段和顺序是：a,b,c
在查询时使用where a = 1 and b = 2 and c= 3。那么这个语句肯定是生效的。
如果我们使用where b = 2 and c =3没有a条件 而a又是在组合索引中最左侧的，那么索引就不会生效。还是要扫描全部行

组合索引要注意字段顺序，是指在创建索引时候的排序，而不是sql语句中where的顺序，我们使用where b = 2 and a = 1 and c = 3也是 可以生效的