如何使用索引优化表(续)

三:多列索引对查询的影响

假定你发出下列SELECT语句：

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。

你可以这样创建一个多列索引：

mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);

而你应该这样创建分开的单行列索引：

mysql>ALTER TABLE tble_name ADD INDEX(col1);

mysql>ALTER TABLE tble_name ADD INDEX(col1);

l       如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有一个3行列索引(col1,col2,col3)，你已经索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。

如果列不构成索引的最左面前缀，MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句：

mysql> SELECT * FROM tbl_name WHERE col1=val1;

mysql> SELECT * FROM tbl_name WHERE col2=val2;

mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果一个索引存在于(col1、col2、col3)上，只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前缀。

l       如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串，MySQL也为LIKE比较使用索引。例如，下列SELECT语句使用索引：

mysql> select * from tbl_name where key_col LIKE "Patrick%";

mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

在第一条语句中，只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中，只考虑有"Pat" <= key_col < "Pau"的行。

下列SELECT语句将不使用索引：

mysql> select * from tbl_name where key_col LIKE "%Patrick%";

mysql> select * from tbl_name where key_col LIKE other_col;

在第一条语句中，LIKE值以一个通配符字符开始。在第二条语句中，LIKE值不是一个常数。

l       如果 column_name 是一个索引，使用column_name IS NULL的搜索将使用索引。

l       MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列：=、>、>=、<、<=、BETWEEN和一个有一个非通配符前缀象'something%'的LIKE的列。

l       对于一个多列索引，如果在WHERE子句的所有AND层次使用索引，将不使用来索引优化查询。为了能够使用索引优化查询，必须把一个多列索引的前缀使用在一个AND条件组中。

下列WHERE子句使用索引：

... WHERE index_part1=1 AND index_part2=2

... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */

... WHERE index_part1='hello' AND index_part_3=5

          /* optimized like "index_part1='hello'" */

这些WHERE子句不使用索引：

... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */

... WHERE index=1 OR A=10                  /* No index */

... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */

四:索引的作用

索引用于：

l         快速找出匹配一个WHERE子句的行。

l         在多个表的查询时，执行连接时加快了与其他表中的行匹配的行的搜索。

l         对特定的索引列找出MAX()或MIN()值。

l         如果排序或分组在一个可用索引的最左面前缀上进行(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有键值部分跟随DESC，键以倒序被读取。

l         在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。

五:索引的弊端

首先，索引文件要占磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，索引文件加快了检索，但增加了插入和删除，以及更新索引列中的值的时间（即，降低了大多数涉及写入的操作的时间），因为写操作不仅涉及数据行，而且还常常涉及索引。一个表拥有的索引越多，则写操作的平均性能下降就越大。在8.4.4节记录装载和修改的速度中，我们将更为详细地介绍这些性能问题，并讨论怎样解决。