索引的设计和使用

1. 索引概述

使用索引是提高SELECT操作性能的最佳途径；存储引擎不同，每张表可以设置的最大索引数和最大索引长度也会不一样；

MyISAM和InnoDB存储引擎的表默认创建的是BTREE索引；MySQL目前还不支持函数索引，但是支持前缀索引，即对索引字段的前N个字符创建索引。前缀索引的长度跟存储引擎相关：

　　对于MyISAM存储引擎的表，索引的前缀长度可以达到1000字节；

　　对于InnoDB存储引擎的表，索引的前缀长度可以达到767字节；

　　MySQL中还支持全文本(FULLTEXT)索引，该索引可以用于全文搜索；

　　MyISAM存储引擎支持空间类型索引，且索引字段必须是非空的；

　　默认情况下，MEMORY存储引擎使用HASH索引，但也支持BTREE索引；

2. 设计索引的原则

　　1). 最适合索引的列是出现在where子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列；

　　2). 索引列的基数越大，索引的效果越好；

　　3). 使用短索引，如果对字符串列进行索引，应该指定一个前缀；较小的索引设计的磁盘IO较少，较短的比较起来更快，此外，对于较短的键值，索引高速缓存中的块能容纳更多的键值，增加了找到行而不用读取索引中较多块的可能性；

　　4). 利用最左前缀；

　　5). 不要过度索引，每个额外的索引都会占据额外的磁盘空间，降低写操作的性能；修改表内容时，索引也必须更新，也有可能需要重构，因此，索引越多，花费的时间也会越多；此外，MySQL在生成执行计划时，也要考虑各个索引，创建多余的索引会为查询优化工作带来负担；

　　6). InnoDB表尽量自己指定主键，记录会按照主键的顺序进行保存；此外，InnoDB表的普通索引会保存主键的键值，所以主键要尽可能选择较短的数据类型，从而有效减少索引的磁盘占用，提高索引的缓存效果；

3. MySQL中使用索引的场景

　　1). 匹配全值，对索引中所有列都指定具体值；

　　2). 匹配值的范围查询，对索引的值进行范围查找；

　　3). 匹配最左前缀，仅仅使用索引中的最左边列进行查找；

　　4). 仅仅对索引进行查询，当查询的列都在索引的字段中时，查询的效率更高；

　　5). 匹配列前缀，仅仅使用索引中的第一列，并且只包含索引第一列开头的一部分；

　　6). 实现索引部分精确匹配，而其他部分范围匹配；

　　7). 如果列名是索引，则column_name is null 则会使用索引；