MySQL索引的设计、使用和优化

原文：http://bbs.landingbj.com/t-0-243071-1.html

 

MySQL索引概述

所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

在MySQL 5.1中，对于MyISAM和InnoDB表，前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量，而CREATE TABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。

还 可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR 和 TEXT列。索引总是对整个列进行，不支持局部(前缀)索引。也可以为空间列类型创建索引。只有MyISAM存储引擎支持空间类型。空间索引使用R- 树。 默认情况MEMORY(HEAP)存储引擎使用hash索引，但也支持B-树索引。

设计索引的原则

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

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

2) 使用惟一索引。

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

3) 使用短索引。

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

4) 利用最左前缀。

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

5) 不要过度索引。

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

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

索 引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 运算。在模式具有一个直接量前缀 时，索引也用��LIKE 运算。如果只将某个列用于其他类型的运算时（如STRCMP( )），对其进行索引没有价值。

btree索引与hash索引

    对 于BTREE和HASH索引，当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时，关键元素与常量值的比较关系对应 一个范围条件。Hash索引还有一些其它特征：它们只用于使用=或<=>操作符的等式比较(但很快)。 优化器不能使用hash索引来加速 ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目）。MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索 引)。如果你将一 个MyISAM表改为hash-索引的MEMORY表，会影响一些查询。只能使用整个关键字来搜索一行。(用B-树索引，任何关键字的 最左面的前缀可用来 找到行)。

对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或 者<>，或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时，关键元素与常量值的比较关系对应一个范围条 件。“常量值”系指：查询字符串中的常量、同一联接中的const或system表中的列、无关联子查询的结果、完全从前面类型的子表达式组成的表达式。

下面是一些WHERE子句中有范围条件的查询的例子。

下列范围查询适用于 btree索引和hash索引：

SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);

 下列范围查询适用于btree索引

SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';

Mysql如何使用索引

索 引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第 1条记录开始然后读完整个表��到找出相关的行。表越大，花费的时间越多。如 果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中 间，没有必要看所有数据。如果一个表有1000行，这比顺序读取至少快 100倍。注意如果你需要访问大部分行，顺序读取要快得多，因为此时我们避免磁盘搜 索。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。