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 ty