Mysql性能调优总结篇

优化方向

如何创建索引： 索引列的顺序的选择（将选择性更高的列放到索引最前列）、创建覆盖索引、索引下推

查询索引优化： 最左前缀原则，小表驱动大表，避免join连接多个表，延迟关联（mybatis的延迟关联查询），避免多个范围查询，索引信息统计、减少表数据与索引的碎片、索引失效的场景（使用函数、关联查询的字段字符集与数据类型不一致...）

库表优化： 选择适合的类型，类型越简单越好，避免NULL

B+Tree数据结构

主要分为非叶子页节点与叶子页节点 InnoDB 将数据划分为若干个页，InnoDB 中页的大小默认为 16KB 所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同 叶子节点使用双向指针相互连接，形成一个双向链表方便范围查找

总结： 由于B+树非叶子节点只存储了索引，这样大大提高了每个非叶子节点可以存储的数据行数量，保证了树的高度可控。 同时叶子节点使用双向指针连接形成链表，在执行范围查询，排序等操作时避免了重复从根节点开始查找数据。 所以InnoDB最终选择了B+树作为索引的数据结构。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

一个表只能有一个聚簇索引，InnoDB 将通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB 会隐式定义一个主键作为聚簇索引。

聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和 MVCC 的回滚指针以及剩余列

如果创建索引的字段不是主键字段，那么在叶子节点中存储的是该记录的主键，然后通过主键去查找对应的行数据，叫做回表查询

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于 I/O 密集型的应用，例如：从性能的角度考虑，使用 UUID 来作为聚簇索引会很糟糕，它使得聚簇索引的插入变得完全随机。UUID 主键插入行不仅花费的时间更长，而且索引占用的空间也更大

二级索引（非聚簇索引）

二级索引的叶子节点包含了引用行的主键列，二级索引访问需要两次索引查找，而不是一次 二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值，并以此作为指向行的“指针”

名词

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们称之为 覆盖索引 如果索引的叶子节点中已经包含要查询的数据，那么没什么必要再回表查询了 覆盖索引必须要存储索引列的值，MySQL 只能使用 B-Tree 索引做覆盖索引 InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询 当发起一个被索引覆盖的查询，在 Explain 的 Extra 列可以看到 Usung index 的信息

索引下推

索引下推( Index Condition Pushdown ，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率

不使用索引条件下推优化时：

• 存储引擎读取索引记录
• 根据索引中的主键值，定位并读取完整的行记录
• 返回给MySQL服务层，服务层判断数据是否符合条件

当使用索引条件下推优化时：

• 存储引擎读取索引记录（不是完整的行记录）
• 存在某些被索引的列的判断条件时，MySQL服务层将这一部分判断条件传递给存储引擎
• 判断条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
• 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
• 存储引擎把记录交给服务层，服务层检测该记录是否满足条件的其余部分