MySQL索引详解——full-text，b-tree，hash，r-tree

一、MySQL索引类型

mysql里目前只支持4种索引分别是：full-text，b-tree，hash，r-tree

b-tree索引应该是mysql里最广泛的索引的了，除了archive基本所有的存储引擎都支持它.

1. full-text索引

full-text在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型。

full-text主要是用来代替like “%***%”效率低下的问题

2. b-tree索引

b-tree在myisam里的形式和innodb稍有不同

在 innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的b-tree差不多，只是还存放了指向主键的信息.

而在myisam里，主键和其他的并没有太大区别。不过和innodb不太一样的地方是在myisam里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.

3. hash索引

目前我所知道的就只有memory和ndb cluster支持这种索引.

hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree，但hash也有缺点，主要如下：

(1)由于存放的是hash值，所以仅支持<=>以及in操作.

(2)hash索引无法通过操作索引来排序，这是因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序.

(3)在组合所以里，无法对部分使用索引.

(4)不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，就是不同的索引键，可能存在相同的hash值.

(5)当存在大量相同hash值得时候，hash索引的效率会变低.

4. r-tree索引

r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。

相对于b-tree，r-tree的优势在于范围查找.

二、mysql里sql语句值得注意的地方

1. myisam里所有键的长度仅支持1000字节，innodb是767.

2. blob和text字段仅支持前缀索引.

3. 使用!=以及<>不等于的时候，mysql不使用索引.

4. 当在字段时候函数的时候，mysql无法使用索引；在join时条件字段类型不一致的时候，mysql无法使用索引；在组合索引里使用非第一个索引时也不使用索引.

5. 在使用like的时候，以%开头，即”%***”的时候无法使用索引；在使用or的时候，要求or前后字段都有索引.

有时候mysql query optimizer会认为使用索引并不是最优计划,所以不使用索引。可以在sql语句里可以用use,force index,当然有时候使用也不会比不用快,所以需要忽略掉index方法是ignore index.

关闭查询缓存sql_no_cache

select sql_no_cache * from table_name;

这样可以让一些很少使用的语句不放在缓存里，查找的时候不会去缓存里找；对应的是强制缓存sql_cache

select sql_cache * from table_name;

另外，在my.cnf中如果设置query_cache_type=2的话，那么只有在使用sql_cache后才会使用缓存;

还有mysql里的优先操作hight_priority让mysql优先操作这个语句

select high_priority * fromtable_name;

与其对应的是low_priority;

mysql里还有延时插入insert delayed

insert delayed into table_name….;

#当提交之后，mysql返回ok，但不立即插入，而是当mysql有空再插入。假如等待时服务器崩溃，那么所有数据丢失，并且插入不会返回自增id.

三、几个技巧

1. 强制连接顺序： STRAIGHT_JOIN

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

2. 强制使用临时表： SQL_BUFFER_RESULT

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT，选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。

3. 分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;

一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。

什么是索引

索引是存储引擎用于快速找到记录的一种数据结构,索引类似一本书的目录,我们根据目录可以快速的查找到我们感兴趣的内容。索引就是存储引擎的目录,如果没有索引存储引擎必须遍历整个数据库表来查询符合条件的记录,索引的建立和优化应该是提升查询性能最有效的手段了。

索引的类型

索引是在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、B+树索引(O(log(n)))：关于B+树索引，可以参考 MySQL索引背后的数据结构及算法原理

2、hash索引：
a 仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询
b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
c 只有Memory存储引擎显示支持hash索引

3、FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）

4、R-Tree索引（用于对GIS数据类型创建SPATIAL索引）

从物理存储角度

1、聚集索引（clustered index）

2、非聚集索引（non-clustered index）

从逻辑角度

1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值

2、普通索引或者单列索引

3、多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

4、唯一索引或者非唯一索引

5、空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

1、unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；

2、index和key为同义词，两者作用相同，用来指定创建索引

3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；

4、index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；

5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

6、asc或desc指定升序或降序的索引值存储

首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

MySQL里的索引类型主要有以下几种。

1. B-Tree索引

最常见的索引类型，基于B-Tree数据结构。B-Tree的基本思想是，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）。但是当索引多列时，列的顺序特别重要，需要格外注意。InnoDB和MyISAM都支持B-Tree索引。InnoDB用的是一个变种B+Tree，而MyISAM为了节省空间对索引进行了压缩，从而牺牲了性能。

2. Hash索引

基于hash表。所以这种索引只支持精确查找，不支持范围查找，不支持排序。这意味着范围查找或ORDER BY都要依赖server层的额外工作。目前只有Memory引擎支持显式的hash索引（但是它的hash是nonunique的，冲突太多时也会影响查找性能）。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引。

例子：

CREATE TABLE testhash (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    KEY USING HASH(fname)
) ENGINE =MEMORY;

3. Spatial (R-Tree)（空间）索引

只有MyISAM引擎支持，并且支持的不好。可以忽略。

4. Full-text索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。Full-text索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的WHERE语句的参数匹配。你可以对某列分别进行full-text索引和B-Tree索引，两者互不冲突。Full-text索引配合MATCH AGAINST操作使用，而不是一般的WHERE语句加LIKE。

以上内容基本取自High Performance MySQL一书，对MySQL性能调优感兴趣的话，此书值得一读再读。