【MySQL】慢查询优化

一 SQL的执行过程

1、首先查询缓存（这一步骤新版本MYSQL已经不用了）
2、解析器进行解析
3、优化器进行优化
4、执行语句

二 慢查询

2.1 慢查询的危害

慢查询–>连接池打满–>超时异常–>操作失败–>用户重试–>情况继续恶化–>MySQL继续恶化

2.2 索引的结构

1、每一个数据页都是16K，仅仅叶子节点存储数据。
2、多叉树，减少层高，减少IO的次数。
3、叶子节点之间是双向链表。
B+树的优点：
1、减少了层高，减少了IO次数。

2.3 慢查询的优化

2.3.1 explain

explain相关的含义：
1、 type：
（1）ALL 表示 全表扫描。
（2）Index： 扫描全部索引。
（3）range： 范围扫描。
（4）ref：索引查询，匹配单值。
（5）const： 主键或者唯一键，只匹配一行。
2、key： 使用到的索引
3、rows：预估扫描的行数
4、extra：using fileSort、using temporary 使用了排序和临时表，排序不一定是文件排序。

2.3.2 优化的思路

从底到上，分为四个模块，分别是
（1）索引层： 索引区分度不高、没有索引
（2）SQL层： limit使用过大、错误使用索引（联合索引使用不当）
（3）表层面： 数据量过大
（4）应用层： 使用当前读锁表

索引层的优化

案例一 没有使用索引
案例二 回表导致全表扫描

select * from tmp where 'name' = tom and 'age' = 17;

因为二级索引不包含查询的字段，所以需要进行回表查询，如果需要回表的数据量超过了20%，则mysql会进行全表扫描。
索引使用效率低
索引对应的行数比较多。
解决办法： 如果查询条件比较多，建立联合索引。
内存排序
使用到了order by 语句，但是字段未建立对应的索引，导致使用到了内存排序或者文件排序。

语句层的优化

（1）limit offset模式的深分页优化为通过主键id的分页方式.
（2）减少SQL事务的范围，将查询语句移出到事务外。

表层面

单表数据量过大，将不用的数据进行归档。

应用层

建立缓存，降低对数据库的查询次数。

总结

1、禁止使用select * for update语句
2、单表大小禁止超过1kw
3、注意索引的区分度（count(discount(列名字) )/ count(*)） 计算索引区分度