【索引】索引的介绍与优化

1. 索引基础

　　索引：存储引擎用于快速找到记录的数据结构

1.1 索引类型

1.1.1 B+ Tree

　　B+ 树： 多路搜索树

 

　　B+树能加快访问数据速度， 不需要访问全表来查询数据，而是从根节点中进行搜索， 根节点中保存了子节点的指针（并且包含子节点的上限与下限），选择正确的子节点进行查找，既可以找到对应值。

 

可以使用B-Tree 查询类型：

　　使用如下表说明查询类型

Create Table People{
    f_name varchar(50) not null,
    l_name varchar(50) not null,
    birth date not null,
    gender enum('m','f') not null,
    key(f_name, l_name, birth)
}

1. 全值匹配： 和索引中所有列匹配（所有列有 f_name, l_name, birth）， 如： 查找 f_name 为‘Allen’ l_name 为 ‘Cuba’  生日是 '1990-01-01'的
2. 匹配最左前缀： 匹配所有的前几列（一定要从左到右， 依次选，不能跳跃）， 如 查找 f_name 为‘Allen’ l_name 为 ‘Cuba’ 的
3. 匹配列前缀：匹配某一列值的开头， 如 查找f_name 为 ‘Allen’  并且 l_name 中第一个字为 ‘C’ 的人 
4. 匹配范围值：用于查找 f_name 在 'Allen' 和 'Barrymore' 之间的

索引的限制：

1. 如果不是从索引的最左列开始（第一列）则无法使用索引（如：无法查找生日为某一天的特定生日的人）
2. 不能跳过索引中的列
3. 如果查询中某个列有范围查询，则右边的索引都无法使用

 

哈希索引

　　哈希索引采用Hash表实现， 只有精确匹配才有效， 存储引擎为每一列添加一个索引列计算得到的索引值，并且不同的值得到的索引不一样，hash存储在索引中，也在hash表中保存指向每个数据行的指针

限制

1. 索引自身只需要存储 hash值，所以不能使用索引中的值来避免读取行
2. 不能排序（按照hash读取的）
3. 不支持匹配查找，只支持等值查找
4. 存在hash冲突

 

全文索引

　　一种特殊的索引，查找文中关键词，而不是比较索引的值，类似于搜索引擎，需要注意：停用词、词干和复数、布尔搜索等

 

1.2 索引优缺点

• 减少了服务器需要扫描的数据量
• 避免了排序和临时表
• 将随机I/O改变为顺序I/O

 

索引类别

唯一索引：加速查询 + 列值唯一（null）

主键索引： 加速查询 + 列值唯一（null）

组合索引：多列值构成索引

覆盖索引：索引包含了查询的所有列，查询时候只需要执行一次查询就可以得到结果数据

聚簇索引

叶子结点是数据而非数据指针（InnoDB）

优点：

• 把数据保存在一起
• 数据访问快
• 使用覆盖索引可以直接找到主键值

缺点：

• 插入速度依赖于插入速度
• 更新代价高
• 在表中插入新行，或者主键被更新需要移动时候，可能面临页分裂
• 全表扫描变慢（行比较稀疏，或者页分裂导致数据不连续）
• 二级索引较大，在二级索引的叶子结点包含了主键的值， 需要再一次IO

　　

　　　　

 

　　非聚簇索引

　　　　叶子结点为数据指针而非数据

 

 

　　

 

InnoDB 和 myisam 对比

数据分布

InnoDB

　　

 

myisam