⚡几种索引

⚡聚集索引 (聚簇索引 cluster index)(主键索引)

聚集索引数据行的物理顺序与列值(一般是主键列)的逻辑顺序相同

注意,这里有一个很容易误解的点:

这里说的是物理顺序,而不是物理上连续

主要是由于是双向链表,而且会有页分裂时候的复制过程,那么在物理地址上,就肯定不是顺序的了

聚簇表示数据行和相邻的键值(逻辑上)紧凑地存储在一起

因为无法同时把数据数据行存放在两个不同的地方,所以一个表中只能拥有一个聚集索引

即对于 InnoDB 的聚集索引,就是表本身

• 优点:

  1. 把相关数据保存在一起,顺序读取,减少磁盘 IO

  2. 数据访问更快

  3. 覆盖索引扫描查询可以直接使用页节点的主键值

• 缺点:

  1. 虽然极大提高了 IO 密集应用的性能,但是如果数据全部放到内存中,访问顺序就没那么重要了

  2. 如果不是按照主键的顺序对表进行插入会非常慢(为了保持索引的物理顺序一致,可能在数据页中进行重排)

1. 更新索引列的代价很高,因为会强制将每个被更新的行移动到新的位置

   1. 插入是可能面行页分裂问题,导致表占用更多的磁盘空间

   2. 可能导致全表扫描比较慢,尤其是行比较稀疏或者由于页分页导致数据存储不连续的时候

   3. 非聚簇索引可能要比想象的大,因为包含了所有引用行的主键列

   4. 回表成本

⚡非聚集索引 (非聚簇索引 non cluster index)(二级索引,辅助索引)

辅助索引是相对主键索引而言的,其实就是非聚集索引

聚集索引实际上不是一种单独的索引,而是一种数据存储方式

网上说:

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的逻辑排列顺序是否一致

实际上不对,我认为应该是:

聚集索引和非聚集索引的根本区别是聚集索引在叶子节点存储行,而非聚集索引则是存储指针

非聚集索引的叶子节点记录数据页的指针或者主键而不是数据,虽然不同引擎的非聚集索引都是指针,但是不同的引擎不同的指针类型不一样

MyISAM 引擎都是非聚集索引,而且索引的叶子节点存储的都是数据文件的指针

InnoDB 引擎的主键索引是聚集索引,主键索引叶子节点都是存一行记录,辅助索引是存储主键的值

在 InnoDB 里面主键索引是存储了一行的值,但是辅助索引(非聚集索引)仅仅存储主键,如果没有主键,则会自动选择一个可以唯一标识的字段,如果也没有,则存储默认隐含的 row id

如果用到了辅助索引,但是所选取的属性又没有存储,那么就会进行回表:先拿到主键,然后根据主键在主键索引上再进行查找,解决办法则可以用下面写到的覆盖索引

⚡覆盖索引 (covering index)

指查询语句的执行只用从索引中就能够获得,不必从表中读取,也可以称之为索引覆盖

一般多是联合索引

如果一个索引包含了(或覆盖了)查询语句中的字段和条件的数据就叫覆盖索引

索引覆盖成立时,SQL 只需要通过索引就可以返回查询的数据,避免了回表操作

覆盖索引使用 InnoDB 比 MyISAM 好,因为 InnoDB 使用聚集索引组织数据,如果二级索引中包含查询所需的数据,就不需要在聚集索引中查找了

explain 的 extra 中有 using index 表示用到了覆盖索引

(如果同时有 using index 和 using where 说明索引不仅仅用来读取数据,还被用来查找)

注意:

1. 覆盖索引一般不可 select *,而一般是索引中的,并且是所需要用到的列

2. 不一定所有存储引擎都支持覆盖索引,并且不同的引擎实现方式都可能不同

3. Hash 索引和全文索引(FULL-Text)不存储值

4. 最左匹配原则

覆盖索引的优点:

1. 索引项一般比较小,可以访问更少的记录

2. 索引按照值的大小记录,减少随机IO

⚡全文索引 (full text index)

InnoDB 和 MyISAM 在 5.6 以后都支持全文索引

对于字符数量比较少的情况下,like 或者 % 都是常规解决方案,但是对于大量的文本数据检索，like 或者 % 是不可想象的

全文索引在大量的数据面前，能比 like 或者 % 快得多，速度不是一个数量级，但是全文索引可能存在精度问题

• 倒排索引(inverted index): 它在辅助表中存储了单词和单词自身在一个或者多个文档中的位置映射,具体有两种表现形式:

  1. inverted file index,表现为 {单词,单词所在文档ID}

  2. full inverted index,表现为 {单词,(单词所在文档ID,在文档中的具体位置)}

• InnoDB 采用 full inverted index 方式,将 (documentID,position)视为一个 ilist,单词视为 word,并且在 word 上建立索引

  (word,ilist)就是两列,建立的表被称为 Auxiliary Table (辅助表),并且为了提升性能,一共有6张 Auxiliary Table

  并且,由于有 position,所以可以进行模糊搜索

• FTS Index Cache (全文检索索引缓存)

  它是一个红黑树,根据(word,ilist)排序

  所以可能写入数据的表已经更新,但是全文索引的更新数据还在 FTS 中,Auxiliary Table 还没有更新

  InnoDB 会批量对 Auxiliary Table 进行批量更新,而不是一个个更新

  这样当查询时,会首先将在 TFS 中对应的 word 字段合并到 Auxiliary Table 中,在进行查询

  这种操作类似 Insert Buffer,不同的是 Insert Buffer 是持久对象,还是 B+ 树结构

⚡哈希索引

基于哈希表实现

• 缺点

  1. 哈希冲突,对于每一个 hash 值相同的数据,查找时,必须进行逐行比较,而插入时也要维护相同 hash 值的数据

  2. 哈希索引只包含哈希值和行指针,而不存储字段值

     1. 索引无法实现索引覆盖,必须回表

     2. 要么全部使用索引,要么不使用,无法使用部分索引加快查询速度

     3. 索引数据无法排序

     4. 必须精确匹配索引所有的列的查询才有效,不支持范围查询

⚡其它索引

• 外键索引: 对外键建立的索引,方便外键的级联

• 唯一索引: 索引值唯一

⚡索引优化–建立高性能索引

1. 独立的列

   SQL 语句的索引列不能是表达式的一部分,也不能是函数的参数 -> 化简

2. 前缀索引

   如果索引是很长的字符列,使索引变得很大并且很慢,则可以在适当范围内降低索引的选择性,只对字符序列的前面几位建立索引

   索引的选择性是指,不重复的索引值和记录总数的比值,选择性越高查询效率越高

   缺点: 无法使用前缀索引做 order by 和 group by,也无法做覆盖扫描

   如果有的列选择性都很低,例如 url 前面都是 www,那么我们可以尝试使用后缀索引,例如将 url 倒过来存 (类似的有爬虫中的倒排文档),再建立前缀索引

3. 多列索引

   MySQL 5.0 以后,其实查询可以同时使用多个单列索引进行扫描,并将结果合并 (explain 中 extra 的 index_merge)

   虽然 MySQL 可以这样,但是实际上更多说明了表上的索引建的很糟糕:

   通常有多个 AND 条件,则一般需要包含所有相关列的多列索引,而不是多个独立的索引

   通常有多个 OR 条件,则一般需要耗费大量 CPU 和内存资源在算法的缓存,排序和合并操作上,特别是索引的选择性不高,返回大量数据的时候

   更重要的是,优化器不会把这些计算计算到查询成本中,优化器只关心随机读取

   这样做不但更加消耗资源,还可能会影响查询的并发性

   在这种情况下,尽量建立多列索引甚至实现覆盖索引

4. 选择合适的索引列顺序

   经验法则: 将选择性最高的索引放到最前列

   然而,这是不考虑排序和分组的情况下,只优化 where 条件的查找

   如果可以的话,还是要考虑特殊的记录,例如,有一些特殊的记录,未登录的游客的session的记录查找就可能很庞大,这就需要我们在程序代码里面区分游客和普通用户了