mysql 慢查询

April 29 2018

最近新来的 dba 特别勤快，一直督促我们改进 slow sql，发现了一些以前不太关注的点

Cardinality

发现某一个查询语句虽然添加了二级索引，但是查询的时候依然很慢。

网上查了发现了 Cardinality 的概念

官方文档的解释:

An estimate of the number of `unique` values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing

总结一下：
1、它代表的是索引中唯一值的数目的估计值。如果是myisam引擎，这个值是一个准确的值。如果是innodb引擎，这个值是一个估算的值，每次执行show index 时，可能会不一样
2、创建Index时（primary key除外），MyISAM的表Cardinality的值为null，InnoDB的表Cardinality的值大概为行数；
3、值的大小会影响到索引的选择
4、创建Index时，MyISAM的表Cardinality的值为null，InnoDB的表Cardinality的值大概为行数。
5、可以通过Analyze table来更新一张表或者mysqlcheck -Aa来进行更新整个数据库
6、可以通过 show index 查看其值

而我们的那个二级索引，即使 analyze 也没发修复，由于索引重复的比较多，所以权重很低，所以mysql 遇到这种，会把这个索引当作没有索引。

于是就全表查了。

filesort

filesort 其实和文件无关。

事实上，filesort本身命名是有问题的。只要一个排序不能使用索引进行排序，那么它就是filesort。它本身和文件无关。filesort应该被称为sort。而它的实现方式是：快速排序(quicksort)。

就是说 order by 语句没发根据索引来 order by，遇到这种情况，要么没有配置索引，要么索引无效，比如上面的原因会导致索引无效

late row lookups

发现类似的语句，性能差非常多

1	select xx from xx where xx order by xx limit 0, 100

和

1	select xx from xx where xx order by xx limit 3000, 100

查询结果是前者秒出结果，后者要500-1000ms

网上查了下

原因就是虽然你只需要100条纪录，但是 mysql 默认会先从文件读出3100条纪录，然后 orderby, limit.

解决方法就是加一个子查询，仅查询过滤索引字段，减少从文件读取纪录条数

1	select * from (select x from xx order by x limit 3000, 100) o join xx on o.x=xx.x order by o.x

order by 多个

order by 多个条件的话， mysql 必须配置联合索引，想象一下, 每一个索引 mysql 会给他维护一个 b+ tree, 所以，对于多个条件的 orderby 不存在相应的 b+ tree 的话则仍旧会 filesort

数据量过小的话，优先全表扫描

因为mysql 的索引也是保存到文件的，所以如果数据量小于 mysql 的文件缓冲区则会在缓冲区内用 filesort 排序，而不是用索引排序。

原因的话，因为使用 innoDB的话, 因为 innoDB 只有一级索引的叶子节点存放的是 row 数据，二级索引的叶子存放的是主键。

所以，如果使用二级索引作为排序，过滤依据，则至少需要读取2次索引文件。

而如果数据量小，还不如直接读表更实在。

reference

https://www.bo56.com/mysql%E7%9A%84cardinality%E5%BC%82%E5%B8%B8%EF%BC%8C%E5%AF%BC%E8%87%B4%E7%B4%A2%E5%BC%95%E4%B8%8D%E5%8F%AF%E7%94%A8/

https://stackoverflow.com/questions/14368211/mysql-very-slow-order-by

https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

https://tech.meituan.com/mysql-index.html

http://blog.codinglabs.org/articles/theory-of-mysql-index.html