MySQL——创建高性能索引

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。

MySQL服务器逻辑架构图：事务和索引都是在存储引擎层实现的。所以索引的特性基本由存储结构决定。

一、索引基础

1、索引类型

索引有很多类型，可以为不同场景提供更好的性能。MySQL支持的索引类型。

① B-Tree索引

存储结构：B-Tree索引实际是用B+树的数据结构来存储数据的。

索引引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照元数据格式进行存储。

B+树存储结构决定了，B-Tree联合索引只能从最左列匹配，下面索引查询使用都是建立在最左列匹配的前提下。从左向右匹配。

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

全值匹配：和索引中所有的列进行匹配，具体匹配是从最左列开始的。

匹配最左前缀：和索引中最左列匹配。

匹配列前缀：只匹配最左列的值的开头部分。

匹配范围值：只能根据索引最左列匹配范围值。

精确匹配某一列并范围匹配另一列：匹配最左列相同，范围匹配第二列。

只访问索引的查询：查询只需要访问索引，无须访问数据行。

order by操作（顺序查询）：如果order by 符合上面几种查询类型（最左列匹配），索引也可以满足对应的排序需求。

B-Tree索引的限制：

a 如果不是按照索引的最左列开始查找，则无法使用索引。

b 不能跳过索引中的列。必须从最左列到最右列，无法跳过中间的列。

c 如果查询中索引的某个列的范围查询，则其右边所有列都无法使用索引优化查找。

② hash索引

hash索引基于hash表实现，只有精确匹配索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有索引列计算一个hash值，hash索引将所有hash值存储在hash表中，同时在hash表保存指向每个数据行的指针。

MySQL中只有Memory引擎显示支持hash索引。

hash索引的限制：

a hash索引只包含hash值和行指针，而不存储字段值，所以不能使用索引中的实际值来避免读取行，是精确匹配读取。

b hash索引数据并不是按照顺序存储的，所以无法用于排序。

c hash索引不支持部分索引列匹配查找，因为hash值是通过全部索引列生成的。

InnoDB引擎上有个特殊的功能叫做“自适应hash索引”，当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引上再创建一个hash索引，这样让B-Tree索引也具有hash索引的一些优点（hash快速查找）。这是一个完全自动的、内部的行为，用户无法控制或配置。

另外，还可以创建自定义hash索引。

小技巧：在B-Tree基础上创建一个伪hash索引，可以模拟InnoDB创建“自适应hash索引”一样，本质还是使用B-Tree查找，不过查找的不是键，而是键的hash值。

例如：select id from URL where url = “http://www.mysql.com”;中url本身很长，用B-Tree索引存储，存储内容会非常大。

这个时候我们可以删除url上的索引，新增一个url_crc32列并设置为索引，此时：select id from URL where url_crc32 = CRC32("http://www.mysql.com");

这样做性能会非常高 ,但要注意hash表实现的弊端——hash冲突，所以必须带上具体字符串条件；select id from URL where url="http://www.mysql.com" and url_crc32 = CRC32("http://www.mysql.com");

③ R-Tree索引（空间数据索引）

MyISAM表支持空间索引，可以用作地理数据存储。与B-Tree索引不同，这类索引无需前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数入MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人不会使用这个特性。具体一点空间三维，可以根据x,y,z确定某一条数据。

④全文索引

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

二、索引的优点

1、索引大大减少了服务器需要扫描的数据量。

2、索引可以帮助服务器避免排序和临时表。

3、索引可以将随机I/O变为顺序I/O。

索引并不总是最好的解决方案。只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。

非常小的表，大部分情况下简单的全表扫描更高效。

中到大型的表，使用索引非常有效。

但特大型表，建立使用索引代价随之增长。这种情况下需要一种技术可以直接区分查询需要的一组数据，而不是一条一条匹配。例如分区技术。

如果表的数量特别多，可以建立一个元数据表，用来查询需要用到的某些特性。例如执行哪些需要聚合多个应用分布在多张表上的数据的查询，则需要记录“哪个用户的信息存储在哪个表中”的元数据。

这样查询时就可以直接忽略哪些不包含指定用户信息的表。

三、高性能的索引策略

1、独立的列：索引列不能是表达式的一部分，也不能是函数的参数。

select actor_id from sakila.actor where actor_id + 1 = 5;-- 无法使用索引actor_id
select actor_id from sakila.actor where actor_id = 5 - 1; -- 正确写法
-- 常见的错误
select ... where to_days(current_date) - to_days(date_col) <= 10;

2、前缀索引和索引的选择性

有时候需要索引很长的字符列，这会让索引变大且慢。一个策略就是上面由B-Tree实现的伪hash索引。但有时这样还不够，

通常可以索引开始的部分字符（前缀索引），这样可以大大节约索引空间，从而提高索引效率。

但这样也会降低索引的选择性。索引选择性是指不重复的索引值 / 数据表的记录总数（#T），范围1/#T~1。

索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引选择性为1，是最好的索性选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR列，必须使用前缀索引。

诀窍在于：选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，使得前缀索引的选择性接近于索引整个表。举个栗子：

-- city
-- hubei
-- hunan
-- hebei
-- henan
select count(*) as cnt, city from sakila.city_demo group by city order by cnt desc limit 10;
-- 索引开始的部分字符 
select count(*) as cnt, left(city,2) as pref from sakila.city_demo group by pref order by cnt desc limit 10; -- 索引选择性 2/4 = 50%
select count(*) as cnt, left(city,3) as pref from sakila.city_demo group by pref order by cnt desc limit 10; -- 索引选择性 4/4 = 100%  比上面2位前缀要好
-- 我们找到了合适的前缀索引，创建前缀索引
alter table sakila.city_demo add key(city(3));

前缀索引是一种能使索引变小、更快的有效方法，但另一方面也有其缺点：MySQL无法使用前缀索引做order by 和 group by，也无法使用前缀索引做覆盖扫描。

3、多列索引

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

MySQL5.0版本后引入一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

索引合并策略有时候是一种优化结果，但实际上更多时候说明了表上的索引键得很糟糕。毕竟是MySQL自身的策略实现，会耗费MySQL服务器的性能

① 当出现服务器对多个索引做AND操作时，通常意味着也要一个包含所有相关列的多列索引，而不是多个独立的单例索引

② 当服务器需要对多个索引做OR操作时，通常需要耗费大量的CPU和内存资源在算法的缓存、排序、和合并上。特别时其中一些独立索引选择性不高，返回了大量数据的时候。

③ 更重要的是，优化器不会把这些计算到“查询成本中”，优化器只关心随机页面读取，这会使得查询成本被低估了，还不如直接走全表扫描，改用union方式。

Create table t(
    c1 int,
    c2 int,
    c3 int,
-- 当发现C1 C3联合使用场景更多的时候
    key(c1,c3),
-- 而不是
    key(c1),
    key(c3)    
);

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

针对B-Tree的最左匹配原则，必须合适的选择索引顺序。

select count(distinct threadId) as count from message where (group = 10137) and (userId = 1288826) and (anonymous = 0)
-- 使用索引key(groupId,userId) 
-- 根据最左匹配会先匹配groupId 一般groupId会有很多条数据groupTable
-- 然后根据groupTable查询userId可能是一条数据
-- 使用索引key(userId,groupId)
-- 根据最左匹配会先匹配userId ，查出一条数据
-- 根据这一条数据，查询group，查出一条数据

另外where子句中的排序、分组、范围条件因素也会对查询性能造成非常大的影响。

5、聚簇索引

 聚簇索引并不是一种简单的索引类型，而是一种数据存储方式。

InnoDB的聚簇索引实际上在同一结构中保存了B-Tree索引和数据行。B+树的叶子节点不仅保存索引还直接保存所有的数据行。不再是保存索引加一个指向数据行的指针

InnoDB通过主键聚集数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义一个主键来作为聚簇索引。

聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。特别是将表的存储引擎从InnoDB改成其他引擎的时候。

优点：

① 可以把相关数据保存在一起。例如实现电子邮箱时，可以设置用户ID为聚簇索引，这样从磁盘读取少量的页就可获取某个用户全部邮件，否则没封邮件都可能导致一次磁盘I/O。

② 访问数据更快。聚簇索引中数据保存在B+数的叶子节点中，不需要根据指针再查找一次。

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

缺点：

① 聚簇数据最大限度地提高了I/O密集型应用的性能

② 插入速度严重依赖于插入顺序。不按照主键顺序插入，磁盘探针。

③ 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置，比移动索引代价大的多。

④ 可能面临“页分裂”的问题。基于聚簇索引的表在插入新行，或者主键更新导致需要移动行的时候，将一行插入到已满的页中时。

⑤ 聚簇索引可能导致全部扫描变慢，尤其是页分裂导致数据存储不连续时。

下面图有点问题：

innoDB的二级索引（非聚簇）：叶子节点存储的是键值对<二级索引key，聚簇索引key>，确定聚簇索引key后，需要回聚簇索引查询（回表查询）最终查出数据行也就是说需要查询两次索引

MyISAM的主键二级索引（非聚簇）：叶子结点存储的到数据地址的指针<key,指针>,通过指针直接获取数据行。仅需要查询一次索引

 

 另外，聚簇索引最好避免随机的主键。自增id>uuid，uuid不仅自身长占用空间，也会产生更多的页分裂和碎片。

6、覆盖索引

如果一个索引包含（覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

select store_id,film_id from sakila.inventory;
-- 当存在索引key(store_id,film_id)时，索引包含所有查询结果，覆盖索引
-- explain sql 时extra：using index

有时我们需要查询少量数据时，可采用联合索引做“覆盖索引”。

聚簇索引的主键索引可视为一个覆盖索引。

覆盖索引效率高查询更快，以下原因：

① 索引条目通常小于数据行大小。

② 索引按照列值顺序存储

③ 内存缓存

④ 由于InnoDB的聚簇索引，所以覆盖索引对InnoDB很有用

7、使用索引扫描来做

MySQL有两种方式可以生成有序的结果：通过排序操作；通过索引顺序扫描。

索引顺序扫描：explain sql 时type：index

MySQL可以使用同一个索引既满足排序，又用于查找行，因此，如果有可能，设计索引时应该尽量同时满足这两种任务。索引顺序扫描排序的具体场景：

① 单表查询，只有索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时。

② 多表查询，order by 子句引用的字段全部为第一个表且满足①中条件。

8、压缩（前缀压缩）索引

MyISAM采用前缀压缩索引来减少索引的大小

9、冗余和重复索引和未使用的索引

冗余和重复的索引应该立即删除到一个，未使用的索引也应清理删除。

10、索引和锁

InnoDB采用行级锁，并且只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量

四、索引案例学习

1、支持多种过滤条件

在右更多不同值的列上创建索引的选择性会更好。

2、避免多个范围条件

MySQL一个索引进行了范围查询，就无法使用其他索引。

补充：">""<"范围查询，MySQL无法使用范围列后面的其他索引列，但是对于"in"多个等值条件，则没有这个限制。但是多个"in"也会带来新的问题

where eye_color in ('brown','blue','black')
and hair_color in ('black','red','white','yellow')
and sex in('M','F');
-- MySQL优化器会转化3*4*2=24种组合，24对于MySQL完全可以接受
-- 但是当有上千个组合则需要特别小心，优化器优化可能会花很多时间，并消耗大量内存。

3、优化排序

select <cols> from profiles where sex = 'M' order by rating limit 10;
-- 翻页越往后越慢 例如：
select <cols> from profiles where sex = 'M' order by rating limit 10000,10;
-- 解决策略 ①给翻页做个上限，并不影响用户体验
-- ②使用覆盖索引，延迟关联
select <cols> from profiles inner join (
select <priamry key cols> from profiles
where x.sex='M' order by rating limit 10000,10
) as x using(<primary key cols>);

五、总结

1、InnoDB B-Tree索引因B+树存储结构，导致的最左匹配

2、小技巧：创建自定义的基于B-Tree的伪hash索引（新增crc32(url)列代替url）

3、SQL中索引列不能是表达式的一部分，也不能是函数的参数。（SQL中id +1 = 5与id = 4）

4、当索引占用空间太大时，除了上面小技巧，还可通过建立前缀索引。（注意选择性）

5、不要建立无意义的多个独立列索引，根据具体需求尽量建立一个多列索引。（c1、c2和c1-c2区别）

6、根据B-Tree最左匹配，尽量建立合适的索引顺序，（groupid-userid和userid-groupid区别）

7、聚簇索引的性能

8、如果具体情况允许，尽量实现覆盖索引。

9、建立索引时尽量满足索引查询+索引排序。

10、避免多个范围查询，MySQL一个索引进行了范围查询，就无法使用其他索引。（另能用in，尽量不用><）

参考《高性能MySQL》