MySQL索引使用方法及实例学

索引用于快速找到特定一些值的记录。如果没有索引，MySQL就必须从第一行记录开始读取整个表来检索记录。表越大，资源消耗越大。如果在字段上有索引的话，MySQL就能很快决定该从数据文件的哪个位置开始搜索记录，而无须查找所有的数据。如果表中有1000条记录的话，那么这至少比顺序地读取数据快100倍。注意，如果需要存取几乎全部1000条记录的话，那么顺序读取就更快了，因为这样会使磁盘搜索最少。

　　大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B树方式存储。只有空间类型的字段使用R树存储，MEMORY (HEAP)表支持哈希索引。

　　字符串默认都是自动压缩前缀和后缀中的空格。

　　通常，如下所述几种情况下可以使用索引。哈希索引(用于 MEMORY 表)的独特之处在后面会讨论到。

　　想要尽快找到匹配 WHERE 子句的记录。

　　根据条件排除记录。如果有多个索引可共选择的话，MySQL通常选择能找到最少记录的那个索引。

　　做表连接查询时从其他表中检索记录。

　　想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX() 值。优化程序会在检查索引的

　　key_col 字段前就先检查其他索引部分是否使用了 WHERE key_part_# = constant 子句。这样的话，

　　MySQL会为 MIN() 或 MAX() 表达式分别单独做一次索引查找，并且将它替换成常数。当所有的表达式都被替换成常数后，查询就立刻返回。如下：

　　SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;

　　对表作排序或分组，当在一个可用的最左前缀索引上做分组或排序时(如 ORDER

　　BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。

　　有些时候，查询可以优化使得无需计算数据就能直接取得结果。当查询使用表中的一个数字型字段，且这个字段是索引的最左部分，则可能从索引树中能很快就取得结果：

 

SELECT key_part3 FROM tbl_name WHERE key_part1=1

 

　　假设有如下 SELECT 语句：

 

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

 

　　如果在 col1 和 col2 上有一个多字段索引的话，就能直接取得对应的记录了。如果在 col1 和 col2 分别有独立的索引，那么优化程序会先找到限制最多的那个索引，然后根据哪个索引能找到更少的记录就决定使用哪个索引。

如果表里有一个多字段索引的话，那么该索引的任何最左前缀部分都可以被优化程序用来检索记录。例如，在 (col1, col2, col3) 上有一个索引，那么按字段组合 (col1), (col1, col2), 和 (col1, col2,col3) 搜索的时候都会用到索引。

　　MySQL无法使用非最左前缀索引中的部分索引。假如有以下 SELECT 语句：

 

SELECT * FROM tbl_name WHERE col1=val1; 　　SELECT * FROM tbl_name WHERE col2=val2; 　　SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

 

　　如果在 (col1, col2, col3) 上有一个索引，只有第一个查询用到索引了。第二和第三个尽管包括了索引字段，但是 (col2) 和 (col2, col3) 并非索引 (col1, col2, col3) 的最左前缀部分。

　　当对字段做 =, >, >=, <, <=, 或 BETWEEN 比较操作时，也会用到索引。

　　MySQL在做 LIKE 比较时也可能用到索引，如果 LIKE 的参数是非通配字符开始的固定字符串的话。以下的 SELECT 语句就用到了索引：

 

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; 　　SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

 

　　第一个查询中，只有的 'Patrick' <= key_col < 'Patricl' 记录才会被检索到。第二个查询中，只检索 'Pat' <= key_col < 'Pau' 的记录。

　　以下 SELECT 语句不使用索引：

 

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; 　　SELECT * FROM tbl_name WHERE key_col LIKE other_col;

 

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

　　MySQL 4.0及更高会做一个额外的 LIKE 优化。如果使用 ... LIKE '%string%' 并且 string 超过3个字符，MySQL就会用 Turbo Boyer-Moore 算法来初始化模式，并且利用这个模式来加快搜索。

　　用 col_name IS NULL 搜索时也会使用索引，如果字段 col_name 上有索引的话。

　　任何在 WHERE 子句中没有跨越全部 AND 级分句的索引都不会用来优化查询。换言之，想要启用一个索引，那么在任何 AND 分句中都必须使用索引的前缀字段。

　　以下 WHERE 子句使用索引：

　... WHERE index_part1=1 AND index_part2=2 AND other_column=3
　　/* index = 1 OR index = 2 */
　　... WHERE index=1 OR A=10 AND index=2
　　/* 优化了 like "index_part1='hello'" */
　　... WHERE index_part1='hello' AND index_part3=5
　　/* 使用索引 index1，但没有用到 index2 或 index3 */
　　... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

　　以下 WHERE 子句不使用索引：

 

/* 没用到 index_part1 */ 　　... WHERE index_part2=1 AND index_part3=2 　　/* 所有的 AND 部分没用到索引 */ 　　... WHERE index=1 OR A=10 　　/* 索引没有跨越全部字段 */ 　　... WHERE index_part1=1 OR index_part2=10

 

　　有些时候尽管有可用的索引，MySQL也不会用到它们。一种情况是优化程序认为如果使用索引会需要检索更大部分的表记录(这时候，扫描表可能更快，因为这支需要更少的搜索)。尽管如此，如果有一个查询用 LIMIT 限制只检索部分记录，MySQL就一定会使用索引，因为这样能更快检索到更少记录来返回给结果。

　　以下是哈希索引的一些不同的特性：

　　它们只用于 = 或 <=> 比较(但并不很快)。

　　优化程序无法使用哈希索引来加速 ORDER BY 操作(这种索引不能用于按顺序搜索下一个记录)。

　　MySQL大致无法判断出介于两个值之间有多少记录(这由范围优化程序来决定使用哪个索引)。这在把 MyISAM 表类型改为采用哈希索引的 MEMORY 类型后可能会影响一些查询。

　　只有全部索引键才能用于检索记录(如果是B树索引，任何前缀部分索引也能用于检索记录)。