一文带你理解慢SQL分析与优化(2)-木庄网络博客

随着业务数据的增长 status='S'的数据基本占据数据的 90%以上，此时该条件无法走索引。我们可以结合业务特征，对数据获取按日期进行拆分。

date = now; minDate = now - 10 days
while(date > minDate) {
select * from order where order_date={#date} and status='S' and update_time < now-5min  limit 500
date = data + 1
}

数据库结构优化

范式优化：表的设计合理化（符合 3NF），比如消除冗余（节省空间）；
反范式优化：比如适当加冗余等（减少 join）
拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘 I/O，一个精心设置的分区可以将数据传输对磁盘 I/O 竞争均匀地分散开。对数据量大的表可采取此方法，可按月建表分区。

SQL 语句优化

SQL 检查状态及分数计算逻辑

尽量避免使用子查询
用 IN 来替换 OR
读取适当的记录 LIMIT M,N，而不要读多余的记录
禁止不必要的 Order By 排序
总和查询可以禁止排重用 union all
避免随机取记录
将多次插入换成批量 Insert 插入
只返回必要的列，用具体的字段列表代替 select * 语句
区分 in 和 exists
优化 Group By 语句
尽量使用数字型字段
优化 Join 语句

大表优化

分库分表（水平、垂直）
读写分离
数据定期归档

原理剖析

MySQL 逻辑架构图：

索引的优缺点

优点

提高查询语句的执行效率，减少 IO 操作的次数

创建唯一性索引，可以保证数据库表中每一行数据的唯一性

加了索引的列会进行排序，在使用分组和排序子句进行查询时，可以显著减少查询中分组和排序的时间

缺点

索引需要占物理空间

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

当对表中的数据进行增删改查时，索引也要动态的维护，这样就降低了数据的更新效率

索引的数据结构

主键索引

普通索引

组合索引

索引页结构

索引页由七部分组成，其中 Infimum 和 Supremum 也属于记录，只不过是虚拟记录，这里为了与用户记录区分开，还是决定将两者拆开。

数据行格式：

MySQL 有 4 种存储格式：

Compact
Redundant （5.0 版本以前用，已废弃）
Dynamic （MySQL5.7 默认格式）
Compressed

Dynamic 行存储格式下，对于处理行溢出（当一个字段存储长度过大时，会发生行溢出）时，仅存放溢出页内存地址。

索引的设计原则

哪些情况适合建索引

数据又数值有唯一性的限制
频繁作为 where 条件的字段
经常使用 group by 和 order by 的字段，既有 group by 又有 order by 的字段时，建议建联合索引
经常作为 update 或 delete 条件的字段
经常需要 distinct 的字段
多表连接时的字段建议创建索引，也有注意事项
- 连接表数量最好不要超过 3 张，每增加一张表就相当于增加了一次嵌套循环，数量级增长会非常快
- 对多表查询时的 where 条件创建索引
- 对连接字段创建索引，并且数据类型保持一致
在确定数据范围的情况下尽量使用数据类型较小的，因为索引会也会占用空间
对字符串创建索引时建议使用字符串的前缀作为索引
这样做的好处是：
- 能节省索引的空间，
- 虽然不能精确定位，但是能够定位到相同的前缀，然后通过主键查询完整的字符串，这样既能节省空间，又减少了字符串的比较时间，还能解决排序问题。
区分度高（散列性高）的字段适合作为索引。
在多个字段需要创建索引的情况下，联合索引优先于单值索引。使用最频繁的列作为索引的最左侧。

哪些情况下不需要使用索引

在 where 条件中用不到的字段不需要。
数据量小的不需要建索引，比如数据少于 1000 条。
由大量重复数据的列上不要建索引，比如性别字段中只有男和女时。
避免在经常更新的表或字段中创建过多的索引。
不建议主键使用无序的值作为索引，比如 uuid。
不要定义冗余或重复的索引
例如：已经创建了联合索引 key(id,name)后就不需要再单独建一个 key(id)的索引

索引优化之 MRR

例如有一张表 user，主键 id，普通字段 age，为 age 创建非聚集索引，有一条查询语句 select* user from table where age > 18;(注意查询语句中的结果是*)

在 MySQL5.5 以及之前的版本中如何查询呢？先通过非聚集索引查询到 age>18 的第一条数据，获取到了主键 id；然后根据非聚集索引中的叶子节点存储的主键 id 去聚集索引中查询行数据；根据 age>18 的数据条数每次查询聚集索引，这个过程叫做回表。

上述的步骤有什么缺点呢？如何 age>18 的数据非常多，那么每次回表都需要经过 3 次 IO(假设 B+树的高度是 3)，那么会导致查询效率过低。

在 MySQL5.6 时针对上述问题进行了优化，优化器先查询到 age>3 的所有数据的主键 id，对所有主键的 id 进行排序，排序的结果缓存到 read_rnd_buffer，然后通过排好序的主键在聚簇索引中进行查询。

如果两个主键的范围相近，在同一个数据页中就可以之间按照顺序获取，那么磁盘 io 的过程将会大大降低。这个优化的过程就叫做 Multi Range Read(MRR) 多返回查询。