Effective MySQL之SQL语句最优化--索引(二)
pe = '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 语句来说是至关重要的。我们将