索引的设计和使用

1、索引概述

（1）可以定义每个表的最大索引数（至少16个）和最大索引长度（总索引长度只是256字节）

（2）MyISAM和InnoDB存储引擎的表默认创建的都是btree索引。

（3）MySQL目前不支持函数索引

（4）支持前缀索引（对索引字段的前N非字符创建索引）。MyISAM索引的前缀长度可以达到1000字节长，InnoDB索引的长度可达767字节。

（5）支持全文本（FULLTEXT）索引，可以用于全文搜索。5.0版只有MyISAM支持，并且只限于CHAR、VARCHAR和TEXT列。索引总是对整个列进行的，不支持局部（前缀）索引。

（6）只有MyISAM支持空间类型索引，且索引的字段必须是非空的。

（7）默认情况，MEMORY存储引擎使用HASH索引，但也支持btree索引。

2、设计索引的原则

（1）最适合索引的列是出现在where子句中的列，或连接子句中指定的列，而不是出现在select关键字后的选择列表中的列。

（2）使用唯一索引。索引的列的基数越大，索引的效果越好。

（3）使用短索引。若对字符串进行索引，应该指定一个前缀长度。

（4）利用最左前缀

（5）不要过度索引

（6）InnoDB的表默认按主键顺序保存。没有主键有唯一索引那就按唯一索引的顺序保存。既没主键又没唯一索引，表中自动生成一个内部列，按这个列的顺序保存。按主键或内部列的访问是最快的，所以InnoDB的表尽量自己指定主键。而且InnoDB表的普通索引都会保持主键的键值，所以主键要尽可能选择较短的数据类型。

3、btree索引和hash索引

（1）hash索引

只用于=或<=>操作符的等式比较，

优化器不能使用hash索引来加速order by操作

MySQL不能确定在2个值之间大约有多少行。若将MyISAM改为HASH索引的MEMORY表，会影响一些查询的执行效率、

只能使用整个关键字来搜索一行。

（2）btree索引

当使用>、<、<=、>=、between、!=、<>、like 'pattern'（其中pattern不以通配符开始）操作时，都可以使用相关列上的索引。

注意：MEMORY表，对索引字段进行范围查询时，只有btree索引可以通过索引访问，而hash索引实际上是全表扫描的。