Effective MySQL之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 操作时会出现这种这种索引合并操作。请
看下面的示例：

 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 系统变量，可以
通过启用或禁用这个变量来控制这些附加选项。想了解更多信息可
以参考以下链接：http:///。

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

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 第三种类型的索引合并操作和对两个索引取并集比较类似，但它需要先经过排序：

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)

可以通过以下链接了解更多关于索引合并的信息：http:///。

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

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 语