MySql索引优化

索引下推

索引下推（index condition pushdown，ICP），如 like KK%

联合索引第一个字段用范围不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描。若需要强制走索引 用force index

order by与group by优化

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。 2、order by满足两种情况会使用Using index： 1）order by语句使用索引最左前列 2）使用where子句与order by子句条件列组合满足索引最左前列。 3，尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。 4，如果order by 的条件不在索引列上，就会产生using filesort。 5，能用覆盖索引尽量使用覆盖索引。 6，group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

索引中间字段不能断 所以第一个无using filesort(文件排序) 第二个using filesort 加上中间的字段后 第三条sql走了索引无using filesort 第四条sql 联合索引顺序打乱了 排序时age和position颠倒了 所以出现using filesort

当age为常量 等于没有参与排序 在排序中被优化 所以联合索引未颠倒 没出现 using filesort

排序升序降序方式不同 也会导致using filesort mysql8以上版本有降序索引可以支持该种查询方式

多个条件也是范围查询 order by排序时 不走索引

数据量太大 走了全表扫描 减少查询字段 用覆盖索引优化

索引设计原则

分页查询优化

1：根据自增且连续的主键排序的分页查询

2：根据非主键字段排序的分页查询(让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录)

关联查询

1、嵌套循环连接 Nested-Loop Join(NLJ) 算法 一次一行循环地从第一张表(称为驱动表)中读取行，在这行数据中取到关联字段，根据关联字段在另一张表(被驱动表)里取出满足条件的行，然后取出两张表的结果合集。

2、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法 把驱动表的数据读入到join buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join buffer 中的数据做对比。

count(*)查询优化

4种方式效率相同

常见优化方法 1：查询mysql自己维护的总行数 对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myuisam存储引擎的表的总行数会被musql存储在磁盘上，查询不需要计算 对于innodb存储引擎的表mysql不会存储表的总记录行数（因为有mvcc机制），查询count需要实时计算

2 show table status 如果只需要知道表总行数的估计值可以用如下sql查询

3 将总数威虎到redis里 插入或删除表数据行的时候同时维护redis里的表总行数kev的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

4 增加数据库计数表 插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

阿里巴巴mysql规范