Effective MySQL之SQL语句最优化--索引_MySQL

bitsCN.com

effective mysql之sql语句最优化--索引

1 两个索引取并集组合

[sql] 

ALTER TABLE album ADD INDEX name_release (name,first_released);  

EXPLAIN SELECT a.name, ar.name,  

a.first_released  

  FROM album a  

 INNER JOIN artist ar USING (artist_id)  

 WHERE a.name = 'Greatest Hits'  

 ORDER BY a.first_released;  

mysql> EXPLAIN SELECT a.name, ar.name,  

    -> a.first_released  

    ->   FROM album a  

    ->  INNER JOIN artist ar USING (artist_id)  

    ->  WHERE a.name = 'Greatest Hits'  

    ->  ORDER BY a.first_released;  

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

| id | select_type | table | type   | possible_keys                  | key          | key_len | ref               | rows | Extra       |  

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

|  1 | SIMPLE      | a     | ref    | name_release,name_2,name_part2 | name_release | 257     | const             |  659 | Using where |  

|  1 | SIMPLE      | ar    | eq_ref | PRIMARY                        | PRIMARY      | 4       | union.a.artist_id |    1 |             |  

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

2 rows in set (0.00 sec)  

ALTER TABLE album ADD INDEX name_release (name,first_released);  

MySQL 可以在WHERE、ORDER BY 以及GROUP BY 列中使用索引；然而，一般来说MySQL 在一个表上只选择一个索引。

从MySQL 5.0 开始，在个别例外情况中优化器可能会使用一个以上的索引，但是在早期的版本中这样做会导致查询运行更加缓慢。

2 两个索引取并集

第一种: 最常见的索引合并的操作是两个索引取并集，当用户对两个有很

高基数的索引执行OR 操作时会出现这种这种索引合并操作。请

看下面的示例：

[sql] 

 SET @@session.optimizer_switch='index_merge_intersection=on';  

 EXPLAIN SELECT artist_id, name  

 FROM artist  

 WHERE name = 'Queen'  

 OR founded = 1942/G  

mysql>  EXPLAIN SELECT artist_id, name  

    ->  FROM artist  

    ->  WHERE name = 'Queen'  

    ->  OR founded = 1942;  

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

| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                  |  

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

|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using where |  

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

1 row in set (0.01 sec)  

Extra: Using union(name,founded); 采用了union的联合索引模式,取合集.

注意

在MySQL 5.1 中首次引入了optimizer_switch 系统变量，可以

通过启用或禁用这个变量来控制这些附加选项。

2 第二种类型的索引合并是对两个有少量唯一值的索引取交集，如下所示：

[sql] 

SET @@session.optimizer_switch='index_merge_intersection=on';  

EXPLAIN SELECT artist_id, name  

 FROM artist  

  WHERE type = 'Band'  

 AND founded = 1942;  

 mysql> SET @@session.optimizer_switch='index_merge_intersection=on';  

Query OK, 0 rows affected (0.00 sec)  

mysql>   

mysql>   

mysql> EXPLAIN SELECT artist_id, name  

    ->  FROM artist  

    ->   WHERE type = 'Band'  

    ->  AND founded = 1942;  

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

| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |  

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

|  1 | SIMPLE      | artist | ref  | founded       | founded | 2       | const |  498 | Using where |  

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

1 row in set (0.00 sec)  

Extra: Using intersect(founded,type); Using where 这里由于是AND,所以只需要取2个索引中最高效的那个索引来进行遍历取值.

3 第三种类型的索引合并操作和对两个索引取并集比较类似，但它需要先经过排序：

[sql] 

EXPLAIN SELECT artist_id, name  

 FROM artist  

 WHERE name = 'Queen'  

  OR (founded BETWEEN 1942 AND 1950);  

  mysql> EXPLAIN SELECT artist_id, name  

    ->  FROM artist  

    ->  WHERE name = 'Queen'  

    ->   OR (founded BETWEEN 1942 AND 1950);  

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

| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                       |  

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

|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL | 5900 | Using sort_union(name,founded); Using where |  

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

1 row in set (0.00 sec)  

4 数个索引合并的情况

在创建这些示例的过程中，还发现一种以前在任何客户端的查询中未曾出现过的新情况。以下是三个索引合并的示例：

[sql] 

mysql> EXPLAIN SELECT artist_id, name  

  FROM artist  

  WHERE name = 'Queen'  

 OR (type = 'Band' AND founded = '1942');  

 .....  

mysql> EXPLAIN SELECT artist_id, name  

    ->   FROM artist  

    ->   WHERE name = 'Queen'  

    ->  OR (type = 'Band' AND founded = '1942');  

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

| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                  |  

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

|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using where |  

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

1 row in set (0.00 sec)  

技巧

应该经常评估多列索引是否比让优化器合并索列效率更高。多个单列索引和多个多列索引到底哪个更有优势？这个问题

只有结合特定应用程序的查询类型和查询容量才能给出答案。在各种不同的查询条件下，将一些高基数列上的那些单列索引进行

索引合并能够带来很高的灵活性。数据库写操作的性能参考因素也同样会影响到获取数据的最优的数据访问路径。

5 创建更好的MySQL 索引

主要用的比较多的2个特殊的索引

通过使用索引，查询的执行时间可以从秒的数量级减少到毫秒数量级，这样的性能改进能够为你的应用程序的性能带来飞跃。

合理的调整你的索引对优化来说是非常重要的，尤其是对于高吞吐量的应用程序。即使对执行时间的改进仅仅是数毫秒，但对于

一个每秒执行1000 次的查询来说这也是非常有意义的性能提升。例如，把一个原本需要20 毫秒执行的每秒运行1 000 次的查询的

执行之间缩短4 毫秒，这对于优化SQL 语句来说是至关重要的。我们将使用第4 章介绍的方法创建多列索引，并在这一基础

上创建更好的覆盖索引。

● 创建覆盖索引

ALTER TABLE artist

 DROP INDEX founded,

 ADD INDEX founded_name (founded,name);

 在InnoDB 中，主码的值会被附加在非主码索引的每个对应记录后面，因此没有必要在非主码索引中指定主码。

这一重要特性意味着InnoDB 引擎中所有非主码索引都隐含主码列了。并且对于那些从MyISAM 存储引擎转换过来的表，通常会

在它们InnoDB 表索引中将主码添加为最后一个元素。 当QEP 在Extra 列中显示Using index 时，这并不意味着在访

问底层表数据时使用到了索引，这表示只有这个索引才是满足查询所有要求的。这种索引可以为大型查询或者频繁执行的查询带

来显著的性能提升，它被称为覆盖索引。覆盖索引得名于它满足了查询中给定表用到的所有的列。想

要创建一个覆盖索引，这个索引必须包含指定表上包括WHERE语句、ORDER BY 语句、GROUP BY 语句(如果有的话)以及

SELECT 语句中的所有列。

[Comment]:随着数据容量的增加，尤其是超过内存和磁盘最大容量的时候，为一个大型列创建索引可能

会对系统整体性能有影响。覆盖索引对于那些使用了很多较小长度的主码和外键约束的大型规范化模式来说是理想的优化方式。

● 创建局部列的索引

[sql] 

ALTER TABLE artist  

 DROP INDEX name,  

  ADD INDEX name_part(name(20));  

  这里主要考虑的是如何减小索引占用的空间。一个更小的索引意味着更少的磁盘I/O 开销，而这又意味着能更快地访问到需

要访问的行，尤其是当磁盘上的索引和数据列远大于可用的系统内存时。这样获得的性能改进将会超过一个非唯一的并且拥有低

基数的索引带来的影响。局部索引是否适用取决于数据是如何访问的。之前介绍覆盖索引时，你可以看到记录一个短小版本的name 列不会对执行过

的SQL 语句有任何好处。最大的益处只有当你在被索引的列上添加限制条件时才能体现出来。

[sql] 

EXPLAIN SELECT artist_id,name,founded  

 FROM artist  

 WHERE name LIKE 'Queen%';  

 mysql> EXPLAIN SELECT artist_id,name,founded  

    ->  FROM artist  

    ->  WHERE name LIKE 'Queen%';  

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

| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra       |  

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

|  1 | SIMPLE      | artist | range | name          | name | 257     | NULL |   93 | Using where |  

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

1 row in set (0.00 sec)  

在这个示例中，Extra后面没有出现Using Index，所以在索引中记录全名并没有带来额外的益处。

而所提供的局部列索引满足了WHERE 条件。如何选择合适的长度取决于数据的分布以及访问路径。目前没有准确的方法计算索

引的恰当长度。因此对给定范围的列长度内的唯一值数目的比较

是必不可少的。

count了下SELECT count(*) FROM artist WHERE name LIKE 'Queen%'; 才93条记录，而SELECT count(*) FROM artist;有577983条记录，按照普遍的情况，可以走索引，难道是name(20)的20定义的太长了？

[sql] 

ALTER TABLE artist  

 DROP INDEX name_part,  

  ADD INDEX name_part2(name(10));  

  mysql> ALTER TABLE artist  

    ->  DROP INDEX name_part,  

    ->   ADD INDEX name_part2(name(10));  

Query OK, 0 rows affected (3.41 sec)  

Records: 0  Duplicates: 0  Warnings: 0  

mysql> EXPLAIN SELECT artist_id,name,founded  

    ->  FROM artist  

    ->  WHERE name LIKE 'Queen%';  

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

| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |  

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

|  1 | SIMPLE      | artist | range | name_part2    | name_part2 | 12      | NULL |   93 | Using where |  

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

1 row in set (0.00 sec)  

看结果，再用name(5) 试试看。  

mysql> ALTER TABLE artist  

    ->  DROP INDEX name_part2,  

    ->   ADD INDEX name_part3(name(5));  

Query OK, 0 rows affected (3.21 sec)  

Records: 0  Duplicates: 0  Warnings: 0  

mysql> EXPLAIN SELECT artist_id,name,founded  

    ->  FROM artist  

    ->  WHERE name LIKE 'Queen%';  

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

| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |  

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

|  1 | SIMPLE      | artist | range | name_part3    | name_part3 | 7       | NULL |   93 | Using where |  

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

1 row in set (0.00 sec)  

看来局部索引对like的效果不是很明显的，可能跟数据分布范围有关，也许这93条数据全部打散在各个数据库块中，

所以导致解析器认为不能简单地通过数次index就能遍历出数据，故而Extra栏里面就没有出现Using Index的提示。

总结：在索引中正确的定义列(包括定义列的顺序和位置)能够改变索引的实际使用效果。好的索引能够为一个执行缓慢的查询带来

巨大的性能提升。索引也可能使原来执行很快的查询的执行时间减少若干毫秒。在高并发系统中，将1 000 000 条查询减少几毫秒

将会显著改善性能，并且获得更大的容量和扩展性。为SQL 查询创建最优索引可以认为是一项艺术。