MySQL 索引

简介

• 索引(也叫做键key)是存储引擎用于快速找到记录的一种数据结构；

• 索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时；

• 索引优化应该是对查询性能优化最有效的手段了，索引能够轻易将查询性能提高几个数量级；

 

索引的优点

• 索引大大减少了服务器需要扫描的数据量：即让服务器快速定位到表的指定位置，而不需要全表扫描；

• 索引可以帮助服务器避免排序和临时表：如常见的B-Tree索引，是按照顺序存储数据的，可以做order by和group by操作等；

• 索引可以将随机I/O变为顺序I/O：因为索引中存储了实际的列值，故某些查询只使用索引就能够完成全部查询；

 

索引类型

B-Tree索引：

• 最常见的一种索引类型，所有的值都是按顺序存储的，且每一个叶子页到根的距离相同；

• B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索；

• 根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找；

• 通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限；

• 最终存储引擎要么找到对应的值，要么该记录不存在；

• B-Tree索引适用于：全键值、键值范围或键前缀查找；

• B-Tree索引的抽象表示如下：

• 

• B-Tree索引的数据分布如下：

• 

哈希索引

• 基于哈希表实现，只有精确匹配索引所有列的查询才有效；

• 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code)；

• 哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样；

• 哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针；

缺点：

• 只包含哈希值和行指针，而不存储字段值，故不能使用索引中的值来避免读取行；

• 数据不是按照索引值顺序存储的，故不能用于排序；

• 不支持部分索引列匹配查找；

• 只支持等值比较查询，包括=，IN()，，也不支持任何范围查询；

• 当不同的索引列却有相同的哈希值时，就出现了哈希冲突，当在哈希冲突很多的列上建立哈希索引时，就会导致维护代价过高；

InnoDB的“自适应哈希索引(adaptive hash index)”:

• 当InnoDB注意到某些索引值被使用得非常频繁时，他会在内存中基于B-Tree索引之上再创建一个哈希索引；

• 这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找；

• 这是一个完全自动的、内部的行为，用户无法控制或者配置

空间数据索引

• MyISAM表支持空间索引，可以用作地理数据存储；

• 和B-Tree索引不同，这类索引无需前缀查询；

• 空间索引会从所有维度来索引数据，查询时可以有效的使用任意维度来组合查询；

全文索引

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

• 全文搜索涉及更多的细节，包括停用词、词干和复数、布尔搜索等；

• 在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突；

• 全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作；

其它索引类别：如分形树索引，聚簇索引，覆盖索引等

高性能的索引策略

独立的列

• 指索引列不能是表达式的一部分，也不能是函数的参数，否则MySQL就不会使用索引；

• 如：select actor_id from sakila.actor where actor_id +1 = 5;

前缀索引和索引选择性

• 当需要索引很长的字符列时，会使索引变得大且慢，解决方式是只索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但仍需注意索引选择性的降低；

• 索引的选择性是指，不重复的索引值和数据表的记录总数的比值，范围从0-1之间；

• 索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行；

• 当选择了合适长度的前缀，使得前缀索引的选择性接近于索引整个列的选择性时，就可以使用此前缀索引了；

多列索引

• 在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能；

• 当出现服务器对多个索引做相交操作时(通常有多个AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引；

• 当服务器需要对多个索引做联合操作时(通常是有个OR条件)，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上；

选择合适的索引列顺序

• 正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要；

• 在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，以此类推，故多列索引的列顺序至关重要；

• 选择索引列顺序的经验法则：将选择性最高的列放在前面通常是很好的；

聚簇索引：

• 并非一种单独的索引类型，而是一种数据存储方式；

• 因为是存储引擎负责实现索引，故并不是所有的存储引擎都支持聚簇索引，但对InnoDB是适用的；

• InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行；

• 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中，所谓“聚簇”，即表示数据行和相邻的键值紧凑的存储在一起；

• 一个表只能有一个聚簇索引；

• InnoDB通过主键聚集数据；

• 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替；如果没有这样的索引，则会隐式定义一个主键来作为聚簇索引；

• InnoDB只聚集在同一页面中的记录，包含相邻键值的页面可能会相距甚远；

• 聚簇索引的数据分布如下：

  

优点：

• 可以把相关数据保存在一起；

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快；

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

缺点：

• 聚簇索引最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问速度就没那么重要了，聚簇索引也就没什么优势了；

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式；

• 更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；

• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“也分裂”问题；

• 聚簇索引可能导致全表扫描变慢，尤其是行比较疏松，或者由于页分裂导致数据存储不连续的时候；

• 二级索引(非聚簇索引)可能比想象的要更大；且二级索引访问需要两次索引查找；

聚簇索引和非聚簇索引的区别：

• 聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点；

• 非聚簇索引的顺序与数据物理排序顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块；

• 聚簇索引和非聚簇索引的对比图如下：

• 

覆盖索引：

• 如果一个索引包含所有需要查询的字段的值，则称之为“覆盖索引”

• 覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，故MySQL只能使用B-Tree索引做覆盖索引；

• 当发起一个被索引覆盖的查询(也叫做索引覆盖查询)时，在explain的Extra列可以看到“Using index”的信息；

优点：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量；

• 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多；

• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，那么覆盖索引就可以避免访问数据时的大量系统调用；

• InnoDB的二级索引在叶子节点中保存了行的主键值，若二级主键能够覆盖查询，则可以避免对主键索引的二次查询；

评价一个索引是否适合某个查询的“三星系统”

• 索引将相关的记录放在一起则获得一星；

• 如果索引中的数据顺序和查找中的排列顺序一致则获得二星；

• 如果索引中的列包含了查询中需要的全部列则获得三星；

MySQL中索引查看及添加

• show indexes from students/G #显示表中索引

• explain select查询语句 #显示查询的执行过程

• alter table students add index(Age); #添加索引