有效利用表索引-mysql

关键字: mysql 

概述:本小结讲述了索引在优化查询中的作用，包括了索引优化查询的原理，索引在各种情况的检索中的益处，也包括索引的的弊端：增加了存储的空间，使装载数据变慢。 

索引是优化查询的最常用也是最有效的的方法，一个数据表，尤其是容量很大的表，建立合适的索引，会使查询的速度提高很大. 

一:索引对单个表查询的影响 

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。 

例如对下面这样的一个student表： 

mysql>SELECT * FROM student 

+------+---------+---------+---------+---------+

| id     | name      | english | chinese | history |

+------+---------+---------+---------+---------+

|     12 | Tom       |        66 |        93 |        67 |

|     56 | Paul      |        78 |        52 |        75 |

|     10 | Marry     |        54 |         |        74 |

|      4 | Tina      |        99 |        83 |        48 |

|     39 | William   |        43 |        96 |        52 |

|     74 | Stone     |        42 |        40 |        61 |

|     86 | Smith     |        49 |        85 |        78 |

|     37 | Black     |        49 |        63 |        47 |

|      | White     |        94 |        31 |        52 |

+------+---------+---------+---------+---------+

这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生：

mysql>SELECT name,english FROM student WHERE english<60;

+---------+---------+

| name      | english |

+---------+---------+

| Marry     |        54 |

| William   |        43 |

| Stone     |        42 |

| Smith     |        49 |

| Black     |        49 |

+---------+---------+

其中，WHERE从句不得不匹配每个记录，以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。

如果，我们为english列创建一个索引：

mysql>ALTER TABLE student ADD INDEX (english) ;

+-------------------+

| index for english |

+-------------------+

|                  42 |

|                  43 |

|                  49 |

|                  49 |

|                  54 |

|                  66 |

|                  78 |

|                  94 |

|                  99 |

+-------------------+

如上表，此索引存储在索引文件中，包含表中每行的

english

列值，但此索引是在

 english

的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找分数小于

60

的所有行，那么可以扫描索引，结果得出

5

行。然后到达分数为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。索引值是排序的，因此在读到包含Tom的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，这些技术是什么并不重要，重要的是它们工作正常，索引技术是个好东西。 

因此在执行下述查询 

mysql>SELECT name,english FROM user WHERE english<60; 

其结果为： 

+---------+---------+

| name      | english |

+---------+---------+

| Stone     |        42 |

| William |        43 |

| Smith     |        49 |

| Black     |        49 |

| Marry     |        54 |

+---------+---------+

你应该可以发现，这个结果与未索引english列之前的不同，它是排序的，原因正式如上所述。 

二:索引对多个表查询的影响 

假如有三个未索引的表 t1、t2、t3，分别只包含列 c1、c2、c3，每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示： 

此查询的结果应该为 1000 行，每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000（十亿），比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时，将会怎样？很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下： 

1) 如下从表 t1 中选择第一行，查看此行所包含的值。 

2) 使用表 t2 上的索引，直接跳到 t2 中与来自 t1 的值匹配的行。类似，利用表 t3 上的索引，直接跳到 t3 中与来自 t1 的值匹配的行。 

3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。 

在此情形下，我们仍然对表 t1 执行了一个完全扫描，但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。 

如上所述，MySQL 利用索引加速了 WHERE 子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。 

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

假定你发出下列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节记录装载和修改的速度中，我们将更为详细地介绍这些性能问题，并讨论怎样解决。

选择索引的准则 

创建索引的语法已经在4.5 索引属性 中进行了介绍。这里，我们假定您已经阅读过该节。但是知道语法并不能帮助确定表怎样进行索引。要决定表怎样进行索引需要考虑表的使用方式。本节介绍一些关于怎样确定和挑选索引列的准则：

1、搜索的索引列，不一定是所要选择的列 

换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列，例如：

SELECT

col_a                        ←不适合作索引列

FROM

Tbl1 LEFT JOIN tbl2

ON tbl1.col_b = tbl2.col_c      ←适合作索引列

WHERE

col_d = expr                  ←适合作索引列

当然，所选择的列和用于 WHERE 子句的列也可能是相同的。关键是，列出现在选择列表中不是该列应该索引的标志。

出现在连接子句中的列或出现在形如 col1 = col2 的表达式中的列是很适合索引的列。查询中的 col_b 和 col_c 就是这样的例子。如果 MySQL 能利用连接列来优化一个查询，表示它通过消除全表扫描相当可观地减少了表行的组合。

2、使用惟一索引 

考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处（不管搜索哪个值，都会得出大约一半的行）。

3、使用短索引 

如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。（当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的，因为这个索引中不会有许多不同的值。）

4、利用最左前缀 

在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。（这与索引一个列的前缀不同，索引一个列的前缀是利用该的前 n 个字符作为索引值。）

假如一个表在分别名为 state、city 和 zip 的三个列上有一个索引。索引中的行是按 state/city/zip 的次序存放的，因此，索引中的行也会自动按 state/city 的顺序和 state 的顺序存放。这表示，即使在查询中只指定 state 值或只指定 state 和 city 的值，MySQL 也可以利用索引。因此，此索引可用来搜索下列的列组合：

MySQL 不能使用不涉及左前缀的搜索。例如，如果按 city 或 zip 进行搜索，则不能使用该索引。如果要搜索某个州以及某个 zip 代码（索引中的列1和列3），则此索引不能用于相应值的组合。但是，可利用索引来寻找与该州相符的行，以减少搜索范围。

5、不要过度索引 

不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。

如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。

6、考虑在列上进行的比较类型