003_深入浅出mysql—索引

索引概述

MySQL的所有列类型都可以被索引，根据存储引擎可以定义每个表的最大索引数和最大索引长度。MyISAM和InnoDB存储引擎默认是BTREE索引。MySQL目前还不支持函数索引，但支持前缀索引。MySQL支持全文本（FULLTEXT）索引，该索引可用于全文搜索，但目前只限于MyISAM存储引擎，只限于CHAR、VARCHAR、TEXT列。索引总是针对整个列进行的，不支持局部索引。也支持空间列类型索引，但只有MyISAM支持，且索引字段非空。默认情况下，MEMORY存储引擎使用HASH索引，但也支持BTREE索引。

创建新索引的语法为：

CREATE [UNIQUE\FULLTEXT\SPATIAL] INDEX index_name[USING index_type]ON table_name(index_col_name,...);释 index_col_name: col_name[(length)] [ASC\DESC]

创建索引示例：

mysql> create index cityname on city(city(10));Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> explain select * from city where city = 'Beijing'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: city         type: refpossible_keys: cityname          key: cityname      key_len: 32          ref: const         rows: 1        Extra: Using where1 row in set (0.00 sec)

示例可以看出，以city为条件进行查询，可以发现索引cityname被使用。

索引的删除语法：

DROP INDEX index_name ON tbl_name

示例：

mysql> drop index cityname on city;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0

设计索引

索引的设计可以遵循一些已有原则：

• 搜索的索引列。最适合的索引列是出现在WHERE子句中的，或连接子句中指定的列。
• 使用唯一索引。索引的基数越大，索引的效果越好。
• 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度。例如有一个CHAR(100)列，前10到20个字符内，多数值是唯一的，比较好的做法是对前10或20个字符索引，查询更快。较小的索引涉及磁盘IO较少，较短的值比较起来更快。更重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值。
• 利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可引起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
• 不要过度索引。不要以为索引越多越好，过多的索引占用额外的磁盘空间，降低写操作的性能。因为修改表，索引同时更新，有时可能重构。MySQL生成执行计划，也要考虑各个索引，消耗大量时间。
• 对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就按照唯一索引的顺序保存。如果即没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个顺序保存。按照主键或者内部列进行访问的速度最快，索引InnoDB表尽量自己指定主键。另外，InnoDB表的普通索引都会保存主键的键值，索引主键要选择尽可能短的数据类型，以减少磁盘空间及I/O。

BTREE索引和HASH索引

HASH索引的特性：

• 只用于＝或<=>操作符的等式比较；
• 优化器不能使用HASH索引来加速ORDER BY 操作
• MySQL不能确定在两个值之间大约有多少行。
• 只能使用整个关键字搜索一行

而对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=、<>、LIKE ‘pattern’操作符时，都可以使用相关列上的索引。

下列范围查询适用于BTREE和HASH索引：

select * from t1 where key_col = 1 or key_col in (2,3,4)

下列范围查询只适用于BTREE索引：

select * from t1 where key_col > 1 and key_col < 100;select * from t1 where key_col like 'ab%' or key_col between 'lisa' and 'amy'; 

备注：对于HASH索引的表，范围查询也是全表扫描　

小结

1. 索引用于快速找出在某个列中特定值的行。如果不使用索引，MySQL必须从第１条记录开始然后读完整个表。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻数据文件的中间，没必要看所有数据。
2. 大多数MySQL索引（PRIMARY KEY、UNIQUE、INDEX和FULLTEXT等）在BTREE中存储。只有空间类型的索引使用RTREE，并且MEMORY表还支持HASH索引。