高性能MySql进化论(七):正确的使用索引

数据库引擎利用索引提高查询效率，同时也针对索引增加了很多的优化策略，但是在使用索引的过程中也有很多的细节问题需要注意，如果忽略了这些问题，辛辛苦苦建立的索引可能得不到很好的应用，而且有可能还会对性能有一定的影响，下面列出了在使用索引的过程

数据库引擎利用索引提高查询效率，同时也针对索引增加了很多的优化策略，但是在使用索引的过程中也有很多的细节问题需要注意，如果忽略了这些问题，辛辛苦苦建立的索引可能得不到很好的应用，而且有可能还会对性能有一定的影响，下面列出了在使用索引的过程中需要遵守的原则

1        保持单纯的列

所谓的“单纯的列”指的就是在作为查询条件时，不要使用运算符，函数对字段进行处理，否则相关的索引将不能使用

下面列出两种最常见的错误情况
(1) select wordfrom dictionary where id+1=999;

      不应该使用id+1的方式

(2) selectword from dictionary where to_days(CURRENT_DATE)-to_days(id)

      不应该使用TO_DAYS(id)的方式

 

2        正确的采用“前缀索引”的前缀长度

在有些时候，需要使用长字符串作为索引，这样的索引由于占用的空间比较大，以及排序的时候值之间的比较会花费很多的时间，效率会比较低。对于这种情况可以只使用这个字段的前N个字符作为索引的值，这种策略就叫做”前缀索引”。比如，在MYSQL中如果需要使用BLOB/TEXT类型的字段作为索引的话，那么必须使用前缀索引，因为这几种类型不允许作为索引。

 

使用“前缀索引”会带来选择性的问题，比如某张表的长字段索引记录如下,如果采用整个字段的值作为索引的值的话虽然效率很低，但是匹配到最后应该只有一条记录与之相符，这种情况下选择性为1，是最高的。如果采用4个字符作为前缀，那么其实这个索引也就失去了价值，因为它的数量和表记录的数量是相等的，不管怎么优化，做的也都是全表扫描。所以前缀长度的选择非常的重要

ABCDEFGHIJKDDD8

ABCDEFFSKDJKJKD7

ABCDKJH65654654K

ABCDEFGHIJKKJKJG

ABCDEFGHIJKFFFFEJ

可以采取以下方式确定前缀长度,应用别的书籍上的例子,这个例子中city是索引字段

         2.1 首先确定整个表索引值的选择率：

 select count(distinct city)/ count(*) from City_Demo;             

              假设执行的结果是0.0312

         2.2 算出几个候选长度的选择率

 

SELECT COUNT(DISTINCTLEFT(city, 3))/COUNT(*) AS sel3,

COUNT(DISTINCT LEFT(city,4))/COUNT(*) AS sel4,

COUNT(DISTINCT LEFT(city,5))/COUNT(*) AS sel5,

COUNT(DISTINCT LEFT(city,6))/COUNT(*) AS sel6,

COUNT(DISTINCT LEFT(city,7))/COUNT(*) AS sel7

FROM city_demo;

+--------+--------+--------+--------+--------+

| sel3 |       sel4 |       sel5 |  sel6|         sel7 |

+--------+--------+--------+--------+--------+

| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |

       2.3   选一个最靠近的值，从比较的结果看，应该选7作为前缀的长度

       2.4  接下来创建这个前缀索引

               ALTER TABLE sakila.city_demo ADD KEY (city(7));

              最后要指出的是“前缀索引”的缺点：MYSQL无法使用前缀索引做orderby,group by, 以及覆盖索引（后面会提到）

 

3        组合索引中字段的顺序问题

在创建B-Tree的组合索引时，由于B-Tree的匹配顺序是按照存储的顺序来比较的，所以说如果前面的字段可以过滤掉更多的记录的话，后面的条件就会比较更少的记录，当然效率也就更高，用下面的组合索引做个简单的解释

NAME

AREA

 

如果把NAME放在第一个位置，那么在用AREA进行比较时，可能只用10条记录需要比较，如果把AREA放在第一个位置，那么在用NAME进行比较是，可能会有1000条记录需要比较，可以简单的推断出需要把NAME放在索引的第一列，这个原理决定了Hash索引是不适用该规则的。

 

上面的小例子，可以通过字段的选择率来确定索引的字段顺序，转换成SQL的表示方式如下,

 

SELECT COUNT(DISTINCT NAME)/COUNT(*) AS name_selectivity,

COUNT(DISTINCT AREA)/COUNT(*) AS area_selectivity,

COUNT(*)

 FROM student\G

*************************** 1. row***************************

area_selectivity: 0.0001

name_selectivity: 0.0373

COUNT(*): 16049

 

结论：把结果中选择率高的字段放在组合的前面

 

4        聚合索引(Cluster Indexes)

首先要说明的是“聚合索引”并不是指某种具体的索引类型，而是指索引数据的存储方式。其具体的实现细节和数据库引擎实现紧密相关，基本的思想是”将索引和数据行保存在一个数据结构中，且数据行和相邻的键值存储在一起”。如果查询的结果都可以被索引覆盖的话，就不需要再回数据库中进行数据的检索。

 

在Oracle中，可以显示的指定哪些索引为聚合索引。在目前版本的MySQL中，各个存储引擎还不能通过“任意指定”的方式来设置聚合索引。在InnoDB中默认的情况会使用主键作为聚合索引，如果没有建立主键，会选择一个“非空且唯一”的索引来代替，如果 “非空且唯一”的索引也不存在，它会自定义一个主键作为聚合索引。

 

最后需要指出的是，因为这种存储方式中，相邻键值的数据行是存储在一起的，所以应该尽量采用键值递增的插入方式。

如果是使用UUID这种随机的主键，会导致大量的随机IO访问，插入效率会很低

 

 

5        覆盖索引（Converting Indexes）

覆盖索引是指：索引的字段包含了所有要查询的字段，就成为覆盖索引。

例如Student表中在(Name,Area)上建立了索引：

则 select name,area from student wherename=’Eric’ 这样的查询就成为是被索引覆盖的，从执行计划上看，如果EXPLAIN一条查询的Extra列包含“Using Index”就说明这条查询被索引覆盖了。

 

通常情况下利用索引查询到记录的时候，首先会查询到索引的Node,然后根据索引Node中存储的记录指针再到数据库的中查找记录，这种方式也就相当于进行了两次查询，而且数据库记录查询的效率往往会比较低。覆盖索引由于它本身就包含了要查询的字段值，也就避免了对数据库记录的访问，从而极大的提高了查询的效率。

 

有一点需要注意，因为覆盖索引用到的是索引字段的值，而“Hash/空间/全文” 索引中并没有存储索引字段的实际值，所以他们是不支持覆盖索引的。

关于覆盖索引，除了上面提出的问题外，还需要注意MYSQL的版本问题。MYSQL5.5以后的版本对该特性提供了更好的支持，所以尽可能还是使用最新的版本。

 

6        索引顺序扫描排序

MYSQL 可以按照索引顺序扫描(Explain 中的type为index)以及排序操作来实现排序功能。

 

如果使用了索引顺序扫描以及覆盖索引可以使排序的速度大大的提高，如果索引不能包含所有要查询的列，则在取每条记录值的时候都需要根据记录指针去查找对应的记录，这有可能会带来大量的随机I/O的产生，从而使查询效率下降。所以在设计索引的时候，既要考虑到查询条件，也要考虑到排序操作

 

为了使索引更好的服务于排序动作，在拼写SQL的过程中有以下几个问题需要注意

(1)    使用“覆盖索引”

(2)    索引的顺序和order by的顺序一致，且所有列的排序方向一致

(3)    多表关联排序时，order by的条件字段全部都是第一个表的

(4)    Order by 和where一样，也要遵守“最左原则“

(5)    如果where或者join中指定了某个列为常量，则可以弥补规则(4),

例如  INDEX(Name,Age,ID) 则 ….where name=”Eric” order by age, id 也可以使用索引顺序扫描排序

7        轻便的索引

MYSQL允许在相同的Column上创建多个索引，MYSQL需要单独维护每个重复的索引，而且查询优化器也会在这些重复的索引上花时间，从而可能导致整体性能的下降。

所以应该尽量保持索引的"轻便"

 

·             重复的索引

定义:在相同的列上，按照相同的顺序，创建相同类型的索引。

例如 下面的语句将在ID上创建三个索引，对于Primary KEY而言，UNIQUE以及INDEX都是重复的

Create table test(

 ID int not null PRIMARY KEY,

 UNIQUE(ID),

 INDEX(ID),

)

·             冗余的索引

这里的“冗余“指的是多个索引有着相同的类型，且功能重复。

例如对于B-Tree索引而言，如果创建了索引(A,B),再创建索引(A),则(A)就被认为是重复的，但是如果创建了(B)则不被认为是冗余的。

 

冗余的索引一般是发生在扩展的索引的时候，例如已经存在了一个索引(A),有人为了满足新的查询需求，创建了一个新的索引(A,B),这个时候(A)就成了冗余的索引，应该进行删除，或者是用(A)来扩展成(A,B)

 

有时候冗余的索引也能带来查询性能上的提升，例如当需要向索引中添加了一个超长的字符串字段，因为这个字段会导致索引存储空间的变大，导致了查询效率的降低，所以适当的使用冗余索引对性能会有帮助。

 

可以通过对INFORMATION_SCHEMA中的数据来判断索引是否是重复/冗余，也可以通过专门的工具例如Percona-Toolkit的pt-duplicate-key-checker 来检测(http://www.percona.com/doc/percona-toolkit/2.1/pt-duplicate-key-checker.html)

·             未使用的索引

 

和前面两种情况类似，当系统中存在大量未使用的索引时，同样对查询的效率会有影响，可以通过以下两种方式来判断那些索引时未使用的

 

（1）     在Percona或者是MariaDB中，通过打开userstates服务器变量，然后正常运行一段时间，最后通过INFORMATION_SCHEMA.INDEX_STATISTICS变量来判断

（2）     通过专门的工具，例如Percona-Toolkit的pt-index-usage来判断，该工具不仅可以查出那些索引是没有被使用的，还可以了解查询的执行计划(http://www.percona.com/doc/percona-toolkit/2.1/pt-index-usage.html)

 

需要注意的是，并不是未使用的索引就是没有用的索引，例如有些索引的功能是唯一性约束，虽然该索引一直没有被使用，但是却可以避免产生重复的数据，这种类型的索引在处理的时候需要小心