Mysql 查询优化

image.png

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
缓存在第一次查询后，MySQL便将查询语句以及查询结果进行hash处理并保留在缓存中，SQL查询到达之后，对其进行同样的hash处理后，将两个hash值进行对照，如果一样，则命中，从缓存中返回查询结果；否则，需要整个流程走一遍

image.png

发送SQL语句。
查询缓存，如果命中缓存直接返回结果。
SQL解析，预处理，再由优化器生成对应的查询执行计划。 （join过多，index过多都不好，影响速度！）
执行查询，调用存储引擎API获取数据。�返回结果。

show variables like '%query_cache%
show status like '%Qcache%';

索引

image.png
image.png
image.png
image.png

MyISAM的索引文件仅仅保存数据记录的地址，被称为非聚集索引

image.png

Innodb 的主键索引为聚集索引，叶子节点包含完整的数据，称为为聚集索引。

image.png

每次查找都要经过两次，第一次是通过二级索引找到主键， 然后再通过主键索引找到数据。

更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。

插入速度严重依赖插入顺序。按照主键的顺序插入式加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。

结论：所以如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键

索引最佳实践

不要使用表达式

索引列不能是表达式的一部分，也不能是函数的参数，否则不会使用索引。
如：SELECT * FROM student WHERE id + 1 = 5

前缀索引和索引选择性

如果是很长的字符串，可以只索引前面的部分字符，前提是保证选择性。

所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T

比如对一个url列建立索引。通过选择性计算选择前面几个字符建立索引。

多列索引

最左前缀匹配原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

多列索引的顺序很重要

将选择性高的排在第一位。

索引覆盖

建议select只查询真正需要的列，这样就能用到索引覆盖

利用索引排序

索引用于ORDER BY 和 GROUP BY , ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求

避免不必要的索引

占用空间
插入更新时操作会变慢

Profiling

最常用的方法：分析慢查询日志
分析工具：pt-query-digest

分析索引使用：
Explain
Explain extended, show warnings

分析单条语句执行时间：

Set profiling = 1
……
Show profiles
Show profile for query 1

分析单条语句计数器：

Flush status
……
SHOW STATUS WHERE Variable_name LIKE 'Handler%' OR Variable_name LIKE 'Created%';