（三）创建高性能的索引

【概述】

索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响很重要。

索引优化应该是对查询性能优化最有效的手段。

总的来说索引（B-Tree）有如下优点：

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

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

c.  索引可以将随机I/O变为顺序I/O.

1.  索引基础

     基础性的东西参照我的MySQL基础篇，这里着重重点。

      索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也是非常重要的，

因为MySQL只能高效地使用最左前缀列。

      ——关于ORM

               我们经常使用ORM产品（如Hibernate等），它们能够生产符合逻辑、合法的查询语句，

除非生成非常基本的查询（例如根据主键查询），否则它很难生成适合索引的查询。无论多么的

牛X的ORM工具，在精妙和复杂的索引面前都是浮云。

1.1  索引的类型

1.1.1  B-Tree 索引

           B-Tree通常是所有的值按照顺序存储。下图展示的B-Tree在InnoDB下的结构表示（不同的

存储引擎对B-Tree的实现可能不同，但思想都是类似）。

 

         例如，有如下表：

         CREATE  TABLE  persons(

             last_name  varchar(30)  NOT NULL,

             first_name  varchar(30)  NOT NULL,

             birthday  date NOT  NULL,

             KEY   last_first_birthday_IDX (   last_name , first_name ,  birthday  )

         );

这个表上创建了一个索引，索引包含了last_name  ，first_name  ，birthday  列的值，下面展示了

这个索引是如何组织数据的。

 

       

针对B-Tree索引，重点注意如下：

a.  MySQL索引是按照最左列开始查找。即索引列的最左边的列不在没有在语句中，则其他列

无法使用索引。

b.  因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY

操作（按顺序查找）。

c.  不能跳过索引中的列。如，WHERE last_name = 'Z  AND birthday  = '2000-01-01'不能使用索引

查找last_name为Z且生日为2000-01-01的人。因为没有在WHERE语句中指定

first_name列，所以只能使用索引额第一列last_name，而不能跳过first_name列。明白否？  呵呵

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

WHERE  last_name = 'Z'  AND  first_name  LIKE  'YJ%'  AND  birthday  = '2000-01-01'，这个查询

只能使用索引的前两列，因为这里LIKE是一个范围条件。

针对刚才的persons表，伙计们，来看三个不同的SQL的执行计划。SQL不同部分我用红色标出：

  1) .  SELECT *  FROM persons WHERE  last_name   LIKE  'Z%'  AND  first_name  =  'YJ'  

                     AND  birthday  = '2000-01-01' .

 

  2) .  SELECT *  FROM persons WHERE  last_name = 'Z'  AND  first_name  LIKE  'YJ%'  

                     AND  birthday  = '2000-01-01' .

 

  3) .  SELECT *  FROM persons WHERE  last_name   =   'Z'  AND  first_name  =  'YJ'  

                     AND  birthday  = '2000-01-01' .

 

有人肯定要问，红线处三个SQL是不同，有何区别呢？ 看完下面的东西：

- - - - - - - - - - - - -  - - - - -- - - - - -解释 - - - - -- - - - - --  - - - - - -- - -  - -- -  - - - - -- - -  - -- - 

idSELECT识别符。这是SELECT的查询序列号select_type

SELECT类型,可以为以下任何一种:

• SIMPLE:简单SELECT(不使用UNION或子查询)
• PRIMARY:最外面的SELECT
• UNION:UNION中的第二个或后面的SELECT语句
• DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
• UNION RESULT:UNION 的结果
• SUBQUERY:子查询中的第一个SELECT
• DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
• DERIVED:导出表的SELECT(FROM子句的子查询)

table

输出的行所引用的表

type

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:

• system:表仅有一行(=系统表)。这是const联接类型的一个特例。
• const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
• eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
• ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
• ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
• index_merge:该联接类型表示使用了索引合并优化方法。
• unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
• index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
• range:只检索给定范围的行,使用一个索引来选择行。
• index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
• ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。

possible_keys

指出MySQL能使用哪个索引在该表中找到行

key显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。key_len显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。ref显示使用哪个列或常数与key一起从表中选择行。rows显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。filtered显示了通过条件过滤出的行数的百分比估计值。Extra

该列包含MySQL解决查询的详细信息

• Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
• Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
• range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
• Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
• Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
• Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
• Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
• Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
• Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

 -- - - -  - - --  --  --  --  -- - - - - - -  -解释完毕- - -  -2013- 10-27 01:28          - - - - - - - - - -- - -  - --  -- - - 

1.1.2 哈希索引

           哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才有效。关于哈希表可以

百度相关资料。目前MySQL中只有Memory存储引擎显示支持哈希索引。

      看下面的例子：

CREATE  TABLE myhash(

 firstname VARCHAR(30) NOT NULL,

 lastname VARCHAR(30) NOT NULL,

 KEY test_hashIDX USING HASH(firstname)

) ENGINE = MEMORY;

 

 

表中有如下数据，如图：

 

假如索引使用哈希函数f(),那么将有如下值（表示值而已，并非真实数据，仅仅为了便于理解）：

 

哈希索引总结：

      1)  哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不

过，访问内存中的行速度会非常快，故大部分情况下对性能影响不明显。

      2)   哈希索引数据并不是按照索引值顺序存的，所以也就无法用于排序。

      3)   哈希索引不支持部分索引列匹配查找，因为哈希索引始终都是使用索引列的全部内容来计算哈

希值的。

      4)   哈希索引只支持等值比较查询。

      5)   哈希值有可能冲突，太多的冲突哈希值的话，维护代价会很高的。

      6)   哈希索引非常适合数据仓库中的 “星型” schema，需要关联很多查找表。

      7)   INNODB 中有种 “自适应哈希索引” 。这完全是innodb内部操作。它的原理是，innoDB注意到某些

索引值使用得很频繁，它会在内存中基于B-Tree索引之上再创建一个Hash索引，这样就让B-Tree索引也

具有了哈希索引的特点。

1.1.2 创建自定义哈希索引

        之前，我们讨论过，尽量让表使用同一种存储引擎，这样就会带来某些问题。如一些存储引擎不支

持某些特性，这时，我们的做法是变通——改变一种方式让该存储引擎支持某种特性。比如InnoDB不支

持显示的哈希索引，那么我们应该这么做呢？——自定义哈希索引。

还是用刚才的表，举例。我们在myhash表中添加一个字段，个人主页URL字段。表名称改为

mydefinehash，存储引擎默认为innoDB。

CREATE  TABLE mydefinehash(

 firstname VARCHAR(30) NOT NULL,

 lastname VARCHAR(30) NOT NULL,

 url VARCHAR(500)  ,

 url_crc VARCHAR(50)

);

 

      由于url会很长，所以要匹配url查找，性能不会太好。我在表中加一个url_crc列，用于存储url的哈希值。

哈希值是一个体积很小的数字，这样查找就很快了。 如下：

 

CRC32返回的HASH值可能在大量数据情况下有重复，为此，可以使用下面的函数：

CONV(  RIGHT( MD5( 'http://zyj.com'),16 ), 16, 10 ) AS hash64 . 如下：

自定义哈希索引总结：

     它不是真正的哈希索引，实际上，上述例子中还是通过B-Tree索引来快速定位url_crc列的值， 

这个办法，可以解决内容很多的列的查找。同时会有另外一个问题，就是额外索引列（上述例

子中的url_crc列）需要维护，可以通过触发器维护。如：

/*  新增触发器  */

 DELIMITER //

CREATE  TRIGGER  mydefinehash_crc_insert  BEFORE  INSERT ON  mydefinehash 

FOR  EACH  ROW   BEGIN

SET NEW.url_crc = CRC32(NEW.url);

END;

//

/*    修改触发器   */

CREATE  TRIGGER mydefinehash_crc_upd BEFORE  UPDATE  ON  mydefinehash 

FOR  EACH  ROW   BEGIN

SET NEW.url_crc = CRC32(NEW.url);

END;

//

DELIMITER  ;

 

 -- - - -  - - --  --  --  --  -- - - - - -- - -  -2013- 10-27 23:28          - - - - - - - - - -- - -  - --  -- - - 

提示：【索引并不是通吃，比如在海量数据面前，可以考虑分区、分库、建立元数据信息表等。

特别是元数据表在对于大型系统来说，是经常用到的技巧】

1.2  高性能的索引策略

1.2.1  独立的列

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

1.2.2  前缀索引和索引选择性

           有时候需要的索引很长的字符串列，这会让索引变得很大且慢，一个策略是前上述的自定义

哈希索引（或者叫模拟哈希索引）。

           还有一点很重要，就是一点要高选择性。

1.2.3  多列索引

           就是我在Oracle系列里面提到的组合索引。不多说了。

1.2.4  选择合适的索引列顺序

           在一个多列B-Tree索引中，索引列顺序按照最左列进行排序，其次是第二列...。索引索引可以

按照升序或者降序进行扫描，以满足符合列顺序的Order  BY 、GROUP BY 等。

           通常当需不要考虑排序和分组的时候，将选择性最高的列放到索引最前列是最佳实践。

1.2.5  聚簇索引

           聚簇索引并不是一种索引类型，而是一种存储方式。参考我的Oracle相关博文。

1.2.6  整理碎片

           本来不应该把整理碎片列为索引里面的一个小结，但由于重要且易忽略，故单独提出来。

导致MySQL产生碎片的原因很多，整理碎片有助于提升性能。一般可以通过备份/还原 整理碎片。还

可以通过OPTIMIZE  TABLE来重新组织一下表。语法如下：

             OPTIMIZE   TABLE  表名称  ;

           

1.3  覆盖索引

        这一节应该被列为1.2.7章节，但由于很重要。所以专门给它一个二级章节。够意思吧 哈哈。

        覆盖索引——如果一个索引包含（或者说覆盖）所有需要查询的字段的值。目前，MySQL只有

B-Tree索引才支持覆盖索引。

1.4  使用索引扫描来做排序

        同样，给它一个二级章节，其重要性，你懂的！

      举例说明：

创建如下表：

CREATE TABLE rental(

 rental_id VARCHAR(30),

 customer_id VARCHAR(20),

 staff_id VARCHAR(20),

 inventory_id VARCHAR(20),

 rental_date date,

 PRIMARY  KEY (rental_id),

 UNIQUE  KEY rental_date (rental_date,inventory_id,customer_id),

 KEY  idx_fk_inventory_id (inventory_id),

 KEY idx_fk_customer_id(customer_id),

 KEY idx_fk_staff_id(staff_id)

 )

;

 

上面的SQL：

EXPLAIN  SELECT rental_id ,staff_id  FROM rental  WHERE rental_date ='2013-11-01'

 ORDER BY inventory_id, customer_id ;

并不满足“最左前缀”，但是由于最左的列被指定为了常数，所以讲剩下的两列作为“最左前缀”。

用例子来讲理论，便于理解。下面是一些不能使用索引做排序查询的SQL：

1)  下面这个语句使用了两种不同的排序方向，但索引列都是正序排列的：

... WHERE rental_date ='2013-11-01' ORDER  BY inventory_id DESC ,customer_id  ASC;

2)  下面这个语句的ORDER BY中引用了一个不再索引中的列：

... WHERE rental_date ='2013-11-01' ORDER  BY inventory_id , staff_id;

3)  下面这个语句的WHERE 和ORDER BY中的列无法组合成索引的“最左前缀”：

... WHERE rental_date ='2013-11-01' ORDER  BY customer_id  ;  /* 换成 inventory_id 就OK*/

4)  下面这个语句在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列：

... WHERE rental_date > '2013-08-01' ORDER  BY inventory_id ,customer_id  ;

5)  下面的语句在inventory_id 列上有多个等于条件。对于排序来说这也是一种范围查询：

...WHERE rental_date ='2013-11-01' AND inventory_id IN('10','20') 

ORDER  customer_id ;

看下面的例子，我先删除几个额外的单列索引：

ALERT   TABLE rental  DROP KEY  idx_fk_inventory_id , idx_fk_customer_id ,  idx_fk_staff_id;

看排序情况：

 

 

1.5  冗余和重复索引

     应该禁止这类索引。即：在相同的列上按照相同的顺序创建相同类型的索引。

结束语： 这篇博文写了几天才算勉强完成。为什么说勉强呢？因为还有很多需要去挖掘。

重点提醒：一定要学会用执行计划去优化自己的SQL。要熟透执行计划。

下一课题，会探讨 查询性能优化

       

2013-11-02   0:14

                            上海