八月MySQL之索引的设计和使用

本文只对索引中的重点进行概述，至于索引的基本介绍，不再累述。

MySQL支持前缀索引，即对索引字段的前N个字符创建索引。注意：前缀字符个数的限制应以字节为单位进行测量，而CREATE TABLE 语句中的前缀长度解释为字符数，所以，在为使用多字节字符集的列指定前缀长度时需要注意。

创建索引语法：

create [unique/fulltext/spatial] index index_name
[using index_type]
on table_name(index_column_name,...)
index_column_name:
column_name[(length)][ASC/DESC]

删除索引语法：

drop index index_name ON table_name

1、搜索的索引列，不一定是所要选择的列。也就是说，最适合索引的列是出现在where子句中的列，或者连接子句中指定的列，而不是出现在select关键字后的选择列表中的列。
2、要考虑某列中值的分布。索引列的不同值越多，索引的效果越好。比如，不建议将用来记录性别的列作为索引列，因为性别只含有“男”或者“女”，对此列建立索引没有多大用处，不管搜索哪个值，都将得到大约一半的行。
3、使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。比如：有一个char(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘IO较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。
4、利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
5、不要过度索引。索引不是越多越好，什么东西都用索引是错误的。索引太多，会降低写操作的性能，在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花费的时间就越长。
6、对于InnoDB存储引擎的表，记录默认按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存，如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键，也没有唯一索引，那么表会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以InnoDB尽量指定主键。另外，还需要注意，InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有器不同的适用范围。
HASH索引重要的特征有：
1、只用于使用=或<=>操作符的等式比较。
2、优化器不能使用HASH索引来加速order by操作。
3、MySQL不能确定在两个值之前大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY表，会影响一些查询的执行效率。
4、只能使用整个关键字来搜索一行。
而对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE ’pattern'（其中‘pattern’不以通配符开始）操作符时，都可以使用相关列上的索引。