MYSQL 索引详解

索引的类型

索引是在MySQL的存储引擎层中实现的,而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持同一种类型的索引,其底层实现也可能不同。

B-Tree索引 
B-Tree是MyISAM和InnoDB引擎默认索引类型,也可以在创建索引时通过USING BTREE来显示指定。B-Tree是一种多叉平衡树,B-Tree 结构可以显著减少定位记录时所经历的中间过程,从而加快存取速度。一般用于数据库的索引,综合效率较高。

B-Tree索引的应用场景

• 等值匹配 
  可用于= != <> IN NOT IN <=>查询语句的优化
• 范围匹配 
  可用于 > >= < <= BTEWEEN AND等范围查询语句的优化
• 匹配最左前缀 
  对于 name like bai% 这种后模糊匹配的查询,是可以利用name字段上建立的索引来优化查询的,但是对于name like %bai这种前模糊匹配的查询则没有办法使用索引了
• 覆盖索引 
  B-Tree索引的key存放的是字段的值,如果key中包含所有需要查询字段的值,我们就称之为覆盖索引,覆盖索引能够极大的提高性能。
• 排序 
  B-Tree索引是排好序的,所以MySQL可以用来优化ORDER BY 和 GROUP BY等操作。

哈希索引(HASH) 
哈希索引基于哈希表实现,只有Memory引擎显示支持哈希索引,使用哈希索引可以一次定位,所以 Hash 索引的查询效率要远高于 B-Tree 索引。但是哈希索引是有很多限制的:

• 只有精确匹配索引所有列的查询才有效,因为哈希索引是利用索引的所有列的字段值来计算哈希值的。
• 只支持等值比较查询,不能用于范围查询。
• 哈希索引的只包含索引字段的哈希值和指向数据的指针,所以不能使用索引中的值来避免读取行。
• 哈希索引的数据并不是顺序存储的,无法用于排序。

全文索引(FULLTEXT) 
全文索引,是一种通过建立倒排索引,快速匹配文档的方式。

空间索引(SPATIAL) 
MyISAM支持空间索引,可以用作地理数据的存储。

聚集索引&非聚集索引

聚集索引 
聚集索引并不是一种单独的索引类型,而是一种数据存储方式,Innode的聚集索引实际上是将主键(PRIMARY kEY )与数据行存放在同一个文件的,一张表只能有一个聚集索引。 
 
InnoDB要求表必须有主键（MyISAM可以没有）,如果没有显式指定,则MySQL系统会用一个唯一且不为空的索引列做为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键。

InnoDB的普通索引(二级索引)的叶子节点中存放的是PRIMARY KEY的值,所以需要先查询普通索引(二级索引)的叶子节点找到对应的主键值,然后再根据主键值去聚集索引中查询到对应的数据。 

• InnoDB将主键与数据聚集在一起的方式,使得按主键顺序的插入和查询效率会很高,但是更新主键的字段或者不按主键的顺序插入数据的代价会比较高,所以主键的选取很重要(使用AUTO INCREMENT字段或者应用程序生成的顺序递增字段要比无序的UUID好的多)
• 二级索引会保存主键的值,所以主键的值不要太大。

非聚集索引 
非聚集索引的索引与数据是存在在不同文件的,对于MyISAM引擎的一张表,会有三种文件:FRM(表结构)、MYD（数据,就是数据库中的每个行）、MYI（索引）。 
MySQL使用索引查询数据时,先到MYI文件中找出数据存储的位置指针,然后再到MYD文件中读取数据。 
 
MyISAM中主键索引和其他索引在结构上没有什么不同,主键索引就是一个名为PRIMARY的唯一非空索引。

索引操作

创建 
在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

• CREATE TABLE

 
 
CREATE TABLE  table_name( 
column_name data_type, 
...... 
[UNIQUE|FULLTEXT|SPATIAL] {INDEX|KEY} index_name [USING {BTREE | HASH}] (col_name [(length)] [ASC | DESC]...)
);

• ALTER TABLE

 
 
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING {BTREE | HASH}] (col_name [(length)] [ASC | DESC]...)
ALTER TABLE table_name ADD PRIMARY KEY (col_name [(length)] [ASC | DESC]..)

• CREATE INDEX

 
 
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING {BTREE | HASH}] ON tbl_name (col_name [(length)] [ASC | DESC],...)

删除

 
 
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name

高效索引策略

既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引？答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担。所以要学习如何正确的创建和使用索引。

独立的列

索引列不能不能是表达式的一部分,也不能是函数的参数

 
 
select ...  where id+1=5 //不能使用索引

索引的选择性

索引的选择性是指不重复的索引值(Cardinality)和数据表的记录总数的比值(0, 1],索引的选择性高(越接近1),查询时能够过滤掉更多的行,效率也更高。

 
 
SELECT count(DISTINCT(colum_name))/count(*) AS Selectivity ...
//对于性别字段、地区字段、类型字段,它们可取值的范围很小,为低选择性,一般不建议在这些字段上加索引

前缀索引

对于字符列,可以使用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同时大大节约索引空间。 
对于BLOB、TEXT、或者很长的VARCHAR类型的列,必须使用前缀索引。

联合索引

MySQL允许对表上的多个列进行索引,联合索引的创建方法与单个索引创建的方法一样,不同之处仅在于有多个索引列。

 
 
CREATE TABLE t（ a INT, b INT, 
PRIMARY KEY（a）,
KEY idx_a_b（a, b）
）ENGINE=InnoDB

多个键值的B+树 

• 对于查询SELECT*FROM TABLE WHERE a=xxx and b=xxx,显然是可以使用（a, b）这个联合索引的。
• 对于单个的a列查询SELECT*FROM TABLE WHERE a=xxx,也可以使用这个（a, b）索引。
• 但对于b列的查询SELECT*FROM TABLE WHERE b=xxx,则不可以使用这棵B+树索引。

联合索引的第二个好处是已经对第二个键值进行了排序处理。

 
 
select * from t where a >1 order by id desc
//使用该联合索引取出数据,无须再对b做一次额外的排序操作,但是如果我们只是在字段a上创建单独的索引(KEY index_a)则免不了排序。

如果我们去掉联合索引,在a、b列上分别单独建立索引,早期的MySQL版本时只是使用其中某一个单列索引,MySQL5.0及以后的版本引入了一种“索引合并”的策略,一定程度上可以使用多个单列索引来定位指定的行。

 
 
CREATE TABLE t（ a INT, b INT, 
PRIMARY KEY（a）,
KEY idx_a（a）,
KEY idx_b (b)
）ENGINE=InnoDB
SELECT*FROM t WHERE a=xxx and b=xxx

覆盖索引

如果一个索引包含所有需要查询字段的值,我们就称之为覆盖索引,覆盖索引能够极大的提高性能。 
当发起一个被索引覆盖的查询时,在EXPLAIN的Extra列可以看到“Using index”的信息。

冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引,要避免重复的索引。

• 一个新手常犯的错误是在一个字段上创建了主键、唯一索引和普通索引(KEY),实际上MySQL的唯一限制和主键限制都是通过索引来实现的,所以上面实际上创建了三个重复索引。
• 如果创建了联合索引(A,B)那么再创建索引(A)就是冗余索引

1. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5. 不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。

最后总结一下，MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了