MySQL调优

索引

索引是帮助MySQL高效获取数据的排好序的数据结构

索引数据结构

• 二叉树：【问题】顺序插入后形成类似链表的线性结构
• 红黑树（二叉平衡树）：【问题】数据量大时层级会比较多
• Hash表：【问题】不方便范围查找
• B+Tree

B+Tree

B-Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列

B+Tree（B-Tree变种）

• 非叶子节点不存储data，只存储索引（冗余），可以放等多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

存储引擎

MyISAM

索引文件和数据文件分离（非聚集）
一张表包含以下三个部分：

• frame表结构(.frm)
• MyISAM data数据（.myd）
• MyISAM index索引（.myi）

MyISAM存储引擎索引实现

缺点：事务和行级锁

InnerDB

聚集

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶节点包含了完整的数据记录

问题：

• 为什么建议InnoDB表必须建主键，并且推荐使用整形的自增主键？（性能考虑，如果没有主键mysql会自动根据唯一索引或rowid建立索引。自增插入效率高）
• 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

一张表包含以下两个部分：

• frame表结构(.frm)
• InnerDB 数据+索引（.ibd）

聚集索引

非聚集索引

联合索引

最左前缀原理

Explain工具

在select语句之前增加explain关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL（如果from中包含子查询，仍会执行该子查询，将结果放入临时表中）

字段

• id：系列号，id列越大执行优先级越高
• select_type：表示对应行是简单还是复杂查询（simple、primary、subquery、derived，是否包含子查询和union）
• key：索引名称

参考资料：

数据结构
Explain详解与索引优化最佳实践.pdf 提取码：qixi