深入分析MySQL数据库索引

什么是索引

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

索引的类型

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

B-Tree索引

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

B-Tree索引的应用场景

等值匹配

可用于= != <> IN NOT IN <=>查询语句的优化

范围匹配

可用于 > >= < <= BTEWEEN AND等范围查询语句的优化

匹配最左前缀

对于 name like bai% 这种后模糊匹配的查询，是可以利用name字段上建立的索引来优化查询的，但是对于name like %bai这种前模糊匹配的查询则没有办法使用索引了

覆盖索引

覆盖索引是指所有需要查询的字段都在索引已经存在了，那么就不需要再去查询数据了，这种查询效率很高。
select id where id >100

排序

B-Tree索引是排好序的,所以MySQL可以用来做ORDER BY 和 GROUP BY操作。

哈希索引(HASH)

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

只有精确匹配索引所有列的查询才有效，因为哈希索引是利用索引的所有列的字段值来计算哈希值的，
只支持等值比较查询，不能用于范围查询。

哈希索引的只包含索引字段的哈希值he和指向数据的指针，所以不能使用索引中的值来避免读取行。
哈希索引的数据并不是顺序存储的，无法用于排序。

全文索引(FULLTEXT)

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

空间索引(SPATIAL)

聚集索引&非聚集索引

聚集索引

聚集索引并不是一种单独的索引类型，而是一种数据存储方式，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

查看

SHOW INDEX FROM table_name

高效索引策略

Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.