MyISAM和Innodb（非聚集索引和聚集索引）、B+树的存储、MySQL的几种索引

MySQL索引

索引数据结构

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

二叉查找树

缺点：当插入的元素是递增时，二叉查找树退化成单链表，查询效率低

例如，依次插入1、2、3、4、5等

红黑树

优点：相对于二叉查找树，可以平衡结点（二叉平衡树）

缺点：当数据表中的数据有几十万甚至上百万条时，查询的次数，也就是树的深度仍然很大

Hash表

计算Hash值可以快速定位到查询内容

缺点：不支持区间查找

例如：select * from table where col1 >6;

B-Tree

特点：

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

B+Tree（B-Tree变种）

特点：

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

MyISAM（非聚集索引）

MyISAM索引文件和数据文件是分离的

当我们创建了一张存储引擎是MyISAM的Test表，此时数据库中：

Test.frm：记录表结构的文件

Test.MYD：存储data

Test.MYI：存储index

Innodb（聚集索引）

当我们创建了一张存储引擎是Innodb的Test表，此时数据库中：

Test.frm：记录表结构的文件

Test.ibd：存储index和data

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶子结点包含了完整的数据记录（index和data存储在一个文件）
• 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
  • 答：整型相比于UUID查询速度更快，UUID需要将字符串转换为ASCII再进行比较。自增不会改变原来的B+Tree结构。
• 为什么非主键索引结构叶子结点存储的是主键值？
  • 答：一致性和节省存储空间

MySQL索引优化

最佳左前缀原则

联合索引（a，b）

a的值是有顺序的

b的值是没有顺序的。在a相等时，b的值是有顺序的

索引失效：

//遵循最佳左前缀法则，正常使用索引
EXPLAIN SELECT * from t where phone="1212" and id=1;
//索引失效，因为没有a，只有b，所以查找整张表
EXPLAIN SELECT * from t where id=1;
//范围查找的右边失效，b没有顺序，所以查找整张表
EXPLAIN SELECT * from t where phone>1212 and id=1;
//like只有前缀才会使用索引，中缀和后缀都不会用到索引
EXPLAIN SELECT * from t where phone like "1212%";