索引的重要性

什么是索引

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

索引的类型

索引是在 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

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

• ALTER TABLE

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

• CREATE INDEX

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

删除

1. DROP INDEX index_name ON talbe_name
2. ALTER TABLE table_name DROP INDEX index_name

高效索引策略

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

独立的列

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

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

索引的选择性

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

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

前缀索引

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

联合索引

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

1. CREATE TABLE t（ a INT, b INT,
2. PRIMARY KEY（a）,
3. KEY idx_a_b（a, b）
4. ）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+ 树索引。

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

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

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

1. CREATE TABLE t（ a INT, b INT,

2. PRIMARY KEY（a）,

3. KEY idx_a（a）,

4. KEY idx_b (b)

5. ）ENGINE=InnoDB

6. SELECT*FROM t WHERE a=xxx and b=xxx

覆盖索引

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

冗余和重复索引

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

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

相关问题

这里列出几个有关于 MySQL 索引的问题：

1. MySQL 的索引有什么作用？可以分成几类？
2. InnoDB 的索引和 MyISAM 的索引有什么区别？
3. InnoDB 为什么要有主键？
4. InnoDB 的主键选哪种类型的字段性能最好？
5. 组合索引什么时候生效，什么时候失效？
6. 为什么使用 like "%ABC%" 索引会失效而 like "ABC%" 不会呢？

答不出来吗，没关系
再看看这篇，相信你会有一个比较深的理解了
http://twei.site/2017/09/18/MySQL%E7%B4%A2%E5%BC%95%E8%AF%A6%E8%A7%A3/