Effective MySQL之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