MySQL的索引知识

本文目录

• 一、什么是索引。

• 二、索引的类型

• 三、B树索引和Hash索引的比较

• 四、索引的挑选

• 五、索引的代价

• 六、创建索引

• 七、删除索引

• 八、[PRIMARY|UNIQUE]KEY与[UNIQUE]INDEX的关系

• 九、参考资料

一、什么是索引。

索引是用来加速查询的技术的选择之一，在通常情况下，造成查询速度差异 的因素就是索引是否使用得当。当我们没有对数据表的某一字段段或者多个 字段添加索引时，实际上执行的全表扫描操作，效率很低。而如果我们为某 些字段添加索引，mysql在执行搜索时便可以通过扫描索引，然后再找出索 引对应的值，从而提高效率。

二、索引的类型

实际上索引的类型不多，以下只是针对个人以前遇到的索引概念的解释，有 可能某个索引有多种称呼，只是取决于你用哪个角度去描述它。

• B树索引：采用B-trees数据结构存储索引，比如PRIMARY KEY，UNIQUE ，INDEX。

• Hash索引：将一个散列函数应用于每一个列值，最终的散列值都会被存入索引，用于执行查找。

• R树索引：采用R-trees数据结构存储索引，比如Spatial index。（空间数据类型的索引）

• 全文索引（FULLTEXT INDEX）：一般在CHAR，VARCHAR或者TEXT列上创建此索引。可用来代替like ‘%xx%’实现模糊查询。

• 前缀索引：只对一个列或者多个列的前几个字符或者字节索引。

• 唯一索引：只对一个列创建索引。

• 多列索引：对多个列创建索引。在多列索引中必须注意最左前缀这个原则。比如对于（col1，col2，col3）这三列进行索引时，只有（col1），（col1,col2),(col1,col2,col3)才能进行索引搜索。
  注意（col1，col3）也不能进行索引。

• 聚簇索引：每个InnoDB表都有一个特殊的索引称为聚簇索引，一般来说，当为一个表定义一个PRIMAY KEY时，InnoDB就会使用它作为聚簇索引。如果没有定义PRIMARY KEY时，MySQL就会查找第一个非空的 UNIQUE index作为聚簇索引。如果以上两种情况都不满足的话，InnoDB内部会在表的每一行产生一个隐藏的并且名为GEN_CLUST_INDEX的聚簇索引。这个聚簇索引是一个六个字节长度的行ID字段，ID值随着新行的插入而单调增长。实际上，除了聚簇索引，其他索引都称为二级索引。在InnoDB中，二级索引的每一行（将索引假设为行方便理解，实际上索引的存储方式取决于具体的存储引擎）中都包含着一个PRIMARY KEY列，InnoDB使用PRIMARY KEY这一列的列值在聚簇索引中查找相对应的数据（可以将聚簇 索引理解为中间值），从而最后得到最终的结果集。聚簇索引的数据分布如下图：（图来自《高性能MySQL》）
  
  上图中，节点页存放是索引（对应着二级索引的PRIMARY KEY），叶子页存放着所对应的数据，节点页和叶子页这个整体就称为聚簇索引，由此可见，聚簇索引更像是一种数据存储结构。

• 覆盖索引：当查询的结果集可以通过所创建的索引查找出来时，这个索引就称为覆盖索引。
  下面举个例子：
  
  对于上面这个表，当执行下面的语句时，就会使用覆盖索引查询。
  
  因为我们在创建表的时候对last_name、first_name创建了多列索引，并且在查询的时候只查询这两列的结果，因此MySQL会使用覆盖索引查询数据，这也意味着MySQL不会对实际的数据行进行查询，因为所需结果已经可以从索引中查找出来了。
  另外可以看一下下面的SQL语句：
  
  在上面的SQL语句中，我们想查询id和last_name的值，而id是主键，last_name是多列索引中的最左索引，但是此时的查询依旧使用覆盖索引查询。原因在于id实际是作为聚簇索引的，而多列索引自然就是二级索引了，上面提到，二级索引都包含着一列PRIMARY KEY列，而列值就是聚簇索引的索引值，因此此时MySQL可以直接使用覆盖索引中查找出对应的结果集。

三、B树索引和Hash索引的比较

• InnoDB存储引擎和MyISAM存储引擎都只支持B树索引（实际上InnoDB还支持自适应的hash索引，只是不能人为创建），MEMORY存储引擎默认使用hash索引，但它也支持B树索引。

• 在使用<、<=、=、>=、>、<>、!=和BETWEEN运算符，进行精确比较或者范围比较时，使用B树索引会带来高效。如果匹配模式是以一个纯字符串，而不是一个通配符作为开头的，那么B树索引还可以用在使用like进行模式匹配的操作里。
  下面举个例子：
  
  
  

• 对于hash索引，在使用运算符=或者<=>（安全等于的意思，当比较的值含有null值的时候，来返回一个布尔值）完成精确（这里说精确是因为hash索引是用一个hash函数对整个列值hash，而不是某几个字符或者字节）匹配的比较操作里，散列索引的速度非常快。
  

四、索引的挑选

• 一般对于出现在WHERE子句中的列、连接子句中的列、或者出现在ORDER BY或GROUP BY子句中的列创建索引是比较好的。

• 尽量索引短小值。应尽量选用较小的数据类型。比如值的长度不超过25个字符，那么就不要用CHAR（200），其他数据类型同理。特别是InnoDB表来说，因为它使用的是聚簇索引，如果主键过长的话，会导致二级索引占用的存储空间过大。

• 索引字符串值的前缀。当对字符串列进行索引时，应当尽可能指定前缀长度。比如某一个列的前N个字符足够唯一的话，那么就可以不用为整列进行索引。

五、索引的代价

索引确实可以加快检索速度，但是它同时也降低了索引列的插入、删除和更新值的速度，因为写入一个行不仅是写入一个数据行，还要更改索引。表的索引越多，需要做出的更改就越多，平均性能下降得也就越多。并且当所创建的索引过多时，mysql查询优化器在选择使用哪种索引方案时，也会降低一定的效率。其次，索引也会占用磁盘空间，多个索引会占据更大的空间。与没有索引相比，使用索引很快便达到表的大小极限。

六、创建索引

• 使用CREATE TABLE创建索引（index_name可选）
  

• 使用ALTER TABLE为已有表创建索引（index_name可选）
  

• 使用CREATE INDEX创建索引（index_name不可省略）
  
  如果某个索引列在索引时使用了PRIMARY KEY或SPATIAL，则它必须为NOT NULL的。其他索引列允许包含NULL值。
  如果想要限制某个索引，让它只包含唯一值，那么可以把这个索引创建为PRIMARY KEY或UNIQUE索引。 这两种索引很像，主要区别有一下两点：

• 每个表只能包含一个PRIMARY KEY。因为PRIMARY KEY的名字总是为PRIMARY，而同一个表不允许有两个同名的索引。可以在一个表里放置多个UNIQUE索引。

• PRIMARY KEY不可以包含NULL值，而UNIQUE索引可以。如果某个UNIQUE索引包含了NULL值，那么它就可以包含多个NULL值。因为NULL值不会与任何值相等，包括它本身。

七、删除索引

最后，我们可以通过DROP INDEX或ALTER　TABLE语句来删除索引

• 通过DROP INDEX删除索引
  
  

• 通过DROP INDEX删除索引
  

八、[PRIMARY|UNIQUE]KEY与[UNIQUE]INDEX的关系

首先来看一下MySQL创建表的语句：（图来自《MySQL官方文档》，图太大所以省略了一部分）


从上图可以看出，实际上INDEX和KEY是同义词，之所以同时存在主要是为了与其他数据库系统做兼容，另外还有以下两个结论。

• PRIMARY KEY 与 UNIQUE[INDEX|KEY]很相似，具体区别可以查看上面的内容。
  

• INDEX和KEY允许出现相同的列值，但是UNIQUE[INDEX|KEY]不允许出现相同的列值。（记住NULL != NULL）

九、参考资料

• MySQL 8.0官方文档

• 《高性能MySQL》第三版

• 《MySQL技术内幕》第五版

• https://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql

作者：之旅

原文链接：https://www.cnblogs.com/tomiku/p/10050373.html