Mysql的索引优化总结

Mysql的索引优化

一、回表，索引覆盖，索引下推，最左匹配

1、回表
数据库表test在两个字段 id(主键)，name，age，addres，deptno 在 name建立一个普通索引，然后
select * from test where name =‘aaa’：
name先走索引查询出name对应的id，然后拿id在回到表中查出所有的值。这个过程就是回表，数据量大了才会看到效率会增加，但是小的数据量反而会降低查询速度。

2、索引覆盖
select * from test where name =‘aaa’：
select id from test where name =‘aaa’： //不用触发回表。这种情况就是索引覆盖

3、最左匹配
针对的是组合索引：在test表中给**(name,age)**建立一个组合索引
where name = ? and age =? //走索引
where age= ? and name=? //走索引
where name = ? //不走索引
where age= ? //走索引

4、索引下推
select * from test where name =? and age=?
没有索引下推时：上面sql首先先从存储引擎中拉去数据（根据name筛选的数据），再在mysql的server中在进行age的筛选
有了索引下推之后：会在拉去数据的时候直接根据name，age获取数据，不需要在server中做数据筛选。mysql的有个版本之后就自己引进了该方式。筛选数据时降低了IO。
注意：
索引下推的唯一缺点是需要在磁盘上多做数据筛选，原来的筛选是放在内存中的，现在放到了磁盘查找数据的环节，这样做看起来成本比较高，但是数据数聚集存放的，是排序的，所以性能不会有影响，所以整体的IO大大减少，反而提升性能。

5、FIC faster index create —了解即可
插入删除数据
a：先创建临时表，将数据导入到临时表，临时表中已经是处理过的数据了
b：删除原来的表
c：修改临时表的名称
给当前需要操作的表添加一个share锁，不会有创建临时文件的资源消耗。还是在源文件中，但是此时有人发起DML操作，会出现数据不一致，所以添加了share锁，读取时没有问题，但是DML操作会有问题。

总结索引的优点：

A:大大减少了服务器需要扫描的数据量
B:帮助服务器避免排序和临时表的创建
C:将随机IO变成顺序IO

二、举例说明mysql的优化
testA(name,age,addres)
testB(name,age,addres,ssss)
1、给最前面的test表给 name，age，addres建立联合索引

A：where name > ？ // 走索引
B：where name like ‘aa%’ // 走索引
C：where name like ‘%aa%’ // 不走索引
D：where name =? and age>10 and addres=? // name age会走索引， addres不走索引
范围查询条件后的索引会全部失效
E：select * from testA where addres=? and name =? and age>10 // name age会走索引， addres 不走索引，因为涉及到了最左匹配原则
F：select * from testB where name=? or age=? //不走索引。要是给testA就会走索引，但是不常见，因为一般不会把一张表的所有列全部建成索引，但是要是 select name，age from testB where name=? or age=? //就会走索引

2、hash索引说明
A：基于hash表实现的，只有精确匹配索引所有列查询才有效
B：只有memory的存储引擎显示支持哈希索引
C：hash索引自身只需存储对应的hash值，所以索引的结构十分紧凑，所以让hash索引查找速度非常快

3、优化小细节
A：使用索引列查询时，条件中不要使用表达式，把计算放到业务层
B：尽量使用主键查询，而不是其他索引，不会触发回表查询
C：自然主键和代理主键（和业务无关的列建立主键），生产中推荐使用代理主键
D：left(name,4) 去name的前四个字符来建立索引，前几个需要根据数据来定。找一个最合适的，使用了前缀索引，来缩短索引的字符长度。
E：使用索引扫描来排序：因为索引本来是有序的，但是走不走索引还要看具体情况， using filesort就不走索引。但是单独使用的是order by 不会走索引
F：union all， in ， or 都能都使用索引（如果是单列索引，or会走索引，要是使用的是组合索引，or要分情况判断走不走索引参考上面的1.F），但是推荐使用in
G：范围列可以用到索引，但是范围列后面的就不会走索引。索引最多用一个范围列
H：强制类型转换会出发全表扫描、
I：更新比较频繁，数据区分度不高的列上不要建索引
J：使用join走索引时on的条件的数据类型必须一致。
K：最好使用limit限制输出： limit5 和 limit（10000,5）
L：单表索引控制在5个以内
M：不要让太多的列参与到组合索引中，一般不要超过5个。
N：优化必须是建立在了解系统和当前的业务逻辑场景的情况下。

---

•                 才疏学浅，要是有错误的地方希望在评论区留言批评指正