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

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

 

 

 

 

  

 

posted @ 2019-04-18 12:12  三生石1314  阅读(97)  评论(0编辑  收藏