大数据|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索引查找速度非常快
【大数据|Mysql的索引优化总结】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:优化必须是建立在了解系统和当前的业务逻辑场景的情况下。

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

    推荐阅读