索引的设计与使用

1、MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引。MySQL支持前缀索引；支持全文本索引用于全文搜索，但只有M有ISAM存储引擎支持全文索引，并且仅限于char、varchar和text列；不支持函数索引。MEMORY存储引擎使用HASH索引，但也支持ＢＴＲＥＥ索引。 ２、设计索引的原则：搜素的索引列不一定是所要选择的列。最适合索引的列是出现在ｗｈｅｒｅ子句中的列或链接子句中指定的列；使用唯一索引，考虑某列中值的分布，索引的列基数越大索引的效果越好；使用短索引。利用最左前缀；不要过度索引；对于InnoDB 存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以InnoDB 表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。 3、hash索引只适用于=或<=>操作符的等式比较，优化器不能使用hash索引来加速order by操作，MySQL不能确定在两个值之间大约有多少行，只能使用整个关键字来搜索一行。而对于BTREE 索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'（其中'pattern'不以通配符开始）操作符时，都可以使用相关列上的索引。