聊一聊SQL优化
晚上睡不着,脑子里总想着一些问题,试着写一写对于SQL查询优化的见解。
首先,数据库有自己的查询优化器,执行一条查询SQL优化器会选择最优的方式(不走索引、走索引、走哪个索引),
所以索引不是越多越好,因为创建索引需要成本,提高索引命中率是优化SQL的关键。
索引分为主键索引和普通索引,在Mysql数据库的InnoDB下,索引的结构是一颗B+树(非叶子节点存放索引,叶子节点存放数据、所有叶子节点组成一个双向链表),
每一张表都有一个主键索引,意味着有一个主键索引的B+树,创建一个普通索引同时会创建一个该索引的B+树,和主键索引的不同点是,普通索引的叶子节点存放的是(索引列+主键ID),
如果查询优化器命中主键索引,直接通过主键索引查询出数据
如果查询优化器命中该索引,查询数据列被索引覆盖了则直接返回,如果没有覆盖,则需要通过主键ID去查询主键索引的数据,这个过程称为回表。
如果没有命中索引,会遍历主键索引的叶子节点(全表扫描)
主键索引 > 其他索引(索引未覆盖需要回表)> 全表扫描
使用执行计划查看SQL使用索引情况
如何排查慢查询?
1、线上日志
2、开启慢查询日志(影响性能)
3、查看SQL执行计划
如何选择合适的索引列?
适合作为索引的列:查询条件、排序条件
不适合做索引的列:没有作为查询条件、唯一性太差(走索引效率不高)、更新频繁(索引维护成本高)
组合索引的顺序选择:根据条件使用热度来排序
如何避免索引失效?
遵循最左前缀法则,比如一个组合索引(abc),要命中索引查询条件的组合有(a、ab、ac、abc)
避免在索引列上使用函数,这样会使索引失效
避免使用全模糊查询
数据类型不一致
对索引列进行运算
NOT NULL
IN子查询
。。。。?
减少网络IO、减少回表?
禁止使用 *
连表查询?
连表条件创建索引
小表驱动大表
业务优化?
举例,深分页可以把上一页最大ID作为Where条件,减少扫描范围
其他?
拉宽表,允许一些冗余字段,减少表连接
分库分表