索引

1.基础

Mysql中,索引是在存储引擎层而不是在服务器层实现的.

索引类型

1.1 B-Tree索引

使用B-Tree数据结构来存储数据.InnoDB使用的B+Tree Mysql InnoDB B+Tree索引结构

B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索. 根节点的槽中存放了指向子节点的指针, 存储引擎根据这些指针向下层查找.通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际上定义了子节点页中值的上限和下限.最终存储引擎要么是找到对应的值,要么该记录不存在.

B-Tree对索引列是顺序组织存储的,所以很适合查找范围数据.

可以使用B-Tree索引的查询类型

  • 全值匹配: 和索引中的所有列进行匹配
  • 匹配最左前缀: 只使用索引的第一列(idx_A_B时, 只匹配A的索引)
  • 匹配列前缀: 也可以只匹配某一列的值的开头部分(like 后导查询)
  • 配置范围值
  • 精确匹配某一列并范围匹配另外一列
  • 只访问索引的查询: 即查询只需要访问索引, 而无须访问数据行

B-Tree索引的限制

  • 如果不是按照索引的最左列开始查找,则无法使用索引
  • 不能跳过索引中的列
  • 如果查询中有某个列的范围查询, 则其右边所有列都无法使用索引优化查询(比如LIKE属于范围条件查询)

1.2 哈希索引

哈希索引(hash index) 基于哈希表实现, 只有精确匹配索引所有列的查询才有效.

对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(has code), 哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针.

哈希索引的限制

  • 哈希索引值包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值类避免读取行
  • 哈希索引数据并不是按照索引值顺序存储的, 所以也就无法用于排序
  • 哈希索引不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的.例如,在数据列(A,B)上建立哈希索引,如果查询只有数据列,则无法使用该索引
  • 哈希索引只支持等值比较查询,包括=,IN(),<=>(安全等于).不支持任何范围查询
  • 访问哈希索引的数据非常快,除非有很多哈希冲突(不同的所以列值却有相同的哈希值).当出现哈希冲突的时候,存储引擎必需遍历链表中的所有的行指针,逐行进行比较,直到找到所有符合条件的行.
  • 如果哈希冲突很多的话,一些索引维护操作的代价也会很高.

InnoDB 自适应哈希索引(adaptive hash index).当某些索引值使用的非常频繁时, 它会在内存中基于B-Tree索引之上再创建一个哈希索引,可以进行快速的哈希查找. 只是一个完全自动的,内部的行为,用户无法控制或者配置,不过如果有必要,完全可以关闭该功能.

1.3 空间数据索引(R-Tree)

MyISAM表支持空间索引,可以用作地理数据存储. 空间索引会从所有纬度来索引数据,查询时,可以有效地使用任意维度来组合查询. 必需使用MySQL的GIS相关的函数(如MBRCONTAINS()等)来维护数据. 开源关系型数据库中对GIS的解决方案做的比较好的是PostgreSQL的PostGIS.

1.4 全文索引

全文索引是一种特殊类型的索引,它查找的是文本中关键词,而不是直接比较索引中的值.

在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突,全文索引适用于 MATCH AGAINST操作,而不是普通的WHERE条件操作.

1.5 聚簇索引

InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行.

优点

  • 可以把相关数据保存在一起,减少磁盘读取数据页次数
  • 数据访问更快
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

  • 聚簇索引最多限度地提高了I/O密集型应用的性能,但如果数据全部放在内存中,那访问顺序也没那么重要了,聚簇索引也就没什么优势了
  • 插入速度严重依赖于插入顺序
  • 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的列移动到新的位置
  • 居于聚簇索引的表在插入新行或者主键被更新需要移动行的时候,可能面临"页分裂(page split)问题".当插入行到某个已满页时,会将该页分成两个页面容纳,这就是一次页分裂操作,会导致表占用更多的磁盘空间
  • 可能导致全表扫描变慢,尤其是行比较稀疏或者数据存储不连续的时候
  • 二级索引(非聚簇索引)可能比想象的要更大
  • 二级索引访问需要两次索引查找,而不是一次(原因:二级索引保存的是"行指针")

聚簇和非聚簇对照

2.优点

索引的优点

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机I/O变成顺序I/O

3.索引策略

  • 索引列不能是表达式的一部分,而不能是函数的参数(例如where a+1=5则无法使用索引a)
  • 对于BLOB,TEXT或者很长的VARCHAR类型的列,必需使用前缀索引
  • 多列B-Tree索引中,索引列的顺序因为着索引首先按照最左列进行排序,其次是第二列.索引可以按照升序或者降序进行扫描,以满足符合列顺序的ORDER BY,GROUP BY和DISTINCT等字句的查询需求.

覆盖索引 覆盖索引: 一个索引包含(或者说覆盖)所有需要查询的字段的值.

覆盖索引必须要存储索引列的值,而哈希索引,空间索引和全文索引都不存储索引列的值,所以MySQL只能使用B-Tree索引做覆盖索引.

4. 索引和锁

InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量.但这只有当InnoDB在存储引擎层能够过滤到所有不需要的行时才会有效.如果无法过滤到无效的行,那么在InnoDB检索到数据并返回给服务器层以后,MySQL服务器才能应用WHERE字句.

InnoDB索引和锁的细节: InnoDB在二级索引上使用共享(读)锁,但访问主键索引需要排他(写)锁.这消除了使用覆盖索引的可能性,并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢得多

数据碎片

  • 行碎片: 数据行被存储为多个地方的多个片段中
  • 行间碎片: 逻辑上顺序的页,或者行在磁盘上不是顺序存储的
  • 剩余空间碎片: 数据页中有大量的空余空间.会导致服务器读取大量不需要的数据,从而造成浪费.

results matching ""

    No results matching ""