MySQL 索引

sagacity 的个人博客 / 1077 / 0 / 创建于 5年前 / 更新于 4年前

B-Tree索引
大多数mysql引擎都支持这种索引；
1.B-Tree通常意味着所有的值都是按顺序存储的，
2.并且每一个叶子页到根的距离相同；
存储引擎不需要进行全表扫描来获取需要的数据，取而代之是从索引的根节点开始进行搜索；
适用于：
A. 全键值查找（全值匹配）
B. 键值范围（若是有多列的合并索引，需要精确匹配某一列并范围匹配另一列）
C. 键前缀查找
D. 按顺序查找（order by）
限制：
A. 如果不是按照索引的最左列开始查找，则无法使用索引
B. 不能跳过索引中的列
C. 如果查询中有某个列的范围查询，其右边的所有列都无法使用索引优化查询
哈希索引

在mysql中，只有memory引擎显式支持哈希索引；基于哈希表实现，只有精确匹配索引所有列的查询才有效；对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，不同的键值行计算出的哈希码不一样，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针；（hash索引结构：hash值：指针）
优势：
查找速度非常快
限制：
A. 无法用于排序
B. 不支持部分索引列匹配查找
C. 只支持等值比较查询
聚集索引（InnoDB，使用B+Tree作为索引结构）
在一个结构中保存了b-tree索引和数据行；按照主键的顺序存储在叶子页上；
主键索引：叶节点存储key-value为（主键数据：所有剩余列数据）
二级索引（非聚簇索引）：叶节点存储key-value为（索引列数据：主键数据）
非叶节点只存储索引列
优点：
可以把相关数据保存在一起，如根据用户id聚集电子邮箱信息，只需要读取少数的数据页就能获取某个id用户的全部邮件；
数据访问更快，将索引和数据保存在同一个b-tree中；
使用覆盖索引扫描的查询可以直接使用叶节点中的主键值；
缺点：
插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到innodb表中速度最快的方式；
插入新行可能面临页分裂的问题，页分裂导致表占用更多磁盘空间；
通过二级索引需要两次查找，存储引擎找到二级索引的叶子节点获得对应的主键值，根据这个值去聚簇索引中找到对应的行
主键：
如果表没有什么数据需要被聚集（如上述邮件用户id），那么可以定义一个代理键作为主键，使用auto_increment自增列；

非聚集索引（MyISAM使用B+Tree作为索引结构）
按照数据插入顺序存储在磁盘上，访问数据需要一次系统调用；
主键索引/二级索引：叶节点存储（索引列数据：数据在磁盘上的行号）

对比：
InnoDB提供事务支持事务，外键等功能；MyISAM不支持。
InnoDB支持行级锁；MyISAM只支持表级锁
InnoDB要求必须有主键；MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。

覆盖索引

一个索引包含（或者说覆盖）所有需要查询的字段的值
覆盖索引要存储索引列的值，只能用b-tree索引做覆盖索引（不能用哈希索引，全文索引等）
优点

MyISAM存储引擎在内存中只存储索引，覆盖索引不需要进行系统调用；
innodb存储引擎的聚簇索引机制，二级主键如果能覆盖查询，可以避免对主键索引的二次查询；

全文索引
希望通过关键字的匹配来进行查询过滤，而不是通过常规的数值比较，范围过滤操作；
MyISAM的全文索引是一类特殊的B-Tree索引，共有两层，第一层是所有关键字，对于每一个关键字的第二层，包含的是一组相关的“文档指针”；
对于文档对象中的所有词语的过滤条件：

停用词列表中的词语都不会被索引
长度大于指定范围和小于指定范围的词语不会被索引
另外，全文索引不会存储关键字具体匹配在哪一列。
B+树，它是一种非常适合用来做数据库索引的数据结构：
(1)很适合磁盘存储，能够充分利用局部性原理，磁盘预读；
(2)很低的树高度，能够存储大量数据；
(3)索引本身占用的内存很小；
(4)能够很好的支持单点查询，范围查询，有序性查询；

数据库的索引分为主键索引（Primary Inkex）与普通索引（Secondary Index）。InnoDB和MyISAM是怎么利用B+树来实现这两类索引，其又有什么差异呢？这是今天要聊的内容。

一，MyISAM的索引
MyISAM的索引与行记录是分开存储的，叫做非聚集索引（UnClustered Index）。

其主键索引与普通索引没有本质差异：
• 有连续聚集的区域单独存储行记录
• 主键索引的叶子节点，存储主键，与对应行记录的指针
• 普通索引的叶子结点，存储索引列，与对应行记录的指针
画外音：MyISAM的表可以没有主键。

主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。

举个例子，MyISAM：
t(id PK, name KEY, sex, flag);

表中有四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

其B+树索引构造如上图：
• 行记录单独存储
• id为PK，有一棵id的索引树，叶子指向行记录
• name为KEY，有一棵name的索引树，叶子也指向行记录

二、InnoDB的索引
InnoDB的主键索引与行记录是存储在一起的，故叫做聚集索引（Clustered Index）：
• 没有单独区域存储行记录
• 主键索引的叶子节点，存储主键，与对应行记录（而不是指针）
画外音：因此，InnoDB的PK查询是非常快的。

因为这个特性，InnoDB的表必须要有聚集索引：
(1)如果表定义了PK，则PK就是聚集索引；
(2)如果表没有定义PK，则第一个非空unique列是聚集索引；
(3)否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

聚集索引，也只能够有一个，因为数据行在物理磁盘上只能有一份聚集存储。

InnoDB的普通索引可以有多个，它与聚集索引是不同的：
• 普通索引的叶子节点，存储主键（也不是指针）

对于InnoDB表，这里的启示是：
(1)不建议使用较长的列做主键，例如char(64)，因为所有的普通索引都会存储主键，会导致普通索引过于庞大；
(2)建议使用趋势递增的key做主键，由于数据行与索引一体，这样不至于插入记录时，有大量索引分裂，行记录移动；

仍是上面的例子，只是存储引擎换成InnoDB：
t(id PK, name KEY, sex, flag);

表中还是四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

其B+树索引构造如上图：
• id为PK，行记录和id索引树存储在一起
• name为KEY，有一棵name的索引树，叶子存储id

当：
select * from t where name=‘lisi’;

会先通过name辅助索引定位到B+树的叶子节点得到id=5，再通过聚集索引定位到行记录。
画外音：所以，其实扫了2遍索引树。

三，总结
MyISAM和InnoDB都使用B+树来实现索引：
• MyISAM的索引与数据分开存储
• MyISAM的索引叶子存储指针，主键索引与普通索引无太大区别
• InnoDB的聚集索引和数据行统一存储
• InnoDB的聚集索引存储数据行本身，普通索引存储主键
• InnoDB一定有且只有一个聚集索引
• InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK

mysql 索引