慢 SQL 分析与优化( 三 ) _慢SQL

1）主键索引

文章插图
2）普通索引

文章插图
3）组合索引

文章插图
3、索引页结构

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

文章插图
数据行格式
MySQL 有 4 种存储格式：

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

文章插图
Dynamic 行存储格式下，对于处理行溢出（当一个字段存储长度过大时，会发生行溢出）时，仅存放溢出页内存地址。
4、索引的设计原则
1）哪些情况适合建索引

数据又数值有唯一性的限制
频繁作为 where 条件的字段
经常使用 group by 和 order by 的字段，既有 group by 又有 order by 的字段时，建议建联合索引
经常作为 update 或 delete 条件的字段
经常需要 distinct 的字段
多表连接时的字段建议创建索引，也有注意事项：

a. 连接表数量最好不要超过 3 张，每增加一张表就相当于增加了一次嵌套循环，数量级增长会非常快
b. 对多表查询时的 where 条件创建索引
c. 对连接字段创建索引，并且数据类型保持一致

在确定数据范围的情况下尽量使用数据类型较小的，因为索引会也会占用空间
对字符串创建索引时建议使用字符串的前缀作为索引
这样做的好处是：

a. 能节省索引的空间；
b. 虽然不能精确定位，但是能够定位到相同的前缀，然后通过主键查询完整的字符串，这样既能节省空间，又减少了字符串的比较时间，还能解决排序问题。

区分度高（散列性高）的字段适合作为索引。
在多个字段需要创建索引的情况下，联合索引优先于单值索引。使用最频繁的列作为索引的最左侧。

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

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

5、索引优化之 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) 多返回查询。
6、索引下推
假设有索引(name, age), 执行 SQL: select * from tuser where name like '张%' and age=10;