Mysql优化原理分析

一、存储引擎

1.1 MyISAM

一张表生成三个文件

• xxx.frm：存储表结构
• xxx.MYD：存储表数据
• xxx.MYI：存储表索引

索引文件和数据文件是分离的（非聚集）

select * from t where t.col1 = 30;

先去t.MYI文件查找30对应的索引所在磁盘文件地址，去t.MYD文件找出对应磁盘文件地址的数据

1.2 Innodb 

一张表生成两个文件

• xxx.frm：存储表结构
• xxx.ibd：存储索引和数据

索引和数据（索引行对应的所有列数据）不是分离的（聚集） 

二、索引

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

2.1 索引数据结构

• 二叉树

• 红黑树（二叉平衡树）
• Hash表

对索引的key进行一次hash计算就可以定位出数据存储的位置

等值查询效率高（=，IN），不能排序，不能进行范围查询，hash冲突问题

• B-Tree

节点具有相同的深度，叶节点的指针为空

所有索引元素不重复

节点中的数据所有从左到右递增排列

数据有序，范围查询

•  B+Tree（B-Tree变种）

非叶子节点不存储data，只存储索引（冗余），可以放更多的索引

叶子节点包含所有的索引字段

叶子节点用指针（双向指针）连接，提高区间访问的性能

MySQL加载的时候，一二层叶子数据（冗余索引）加载在内存中，底层叶子数据存储在磁盘 

假设索引大小为bigint（8b），一个底层叶子节点数据大小（1kb），MySQL默认叶节点大小（16384kb），索引指针大小（6kb），那么一棵B+Tree可以存储的索引元素：(16384/6)*(16384/6)*16

 2.2 联合索引