Mysql索引的理解

最新推荐文章于 2024-10-30 20:28:26 发布

luu_一只程序猿

最新推荐文章于 2024-10-30 20:28:26 发布

阅读量222

点赞数

CC 4.0 BY-SA版权

分类专栏： # mysql

本文链接：https://blog.youkuaiyun.com/weixin_40663800/article/details/95358297

mysql 专栏收录该内容

2 篇文章

订阅专栏

本文围绕MySQL索引展开，介绍了索引概念，它是帮助高效获取数据的数据结构。阐述了常见的Hash和B+ Tree两种索引数据结构，以及聚簇索引、覆盖索引等。还提到联合索引的最左前缀匹配原则，最后说明了创建索引时要考虑搜索列、唯一性、长度等因素。

相信很多人对于MySQL的索引都不陌生，索引(Index)是帮助MySQL高效获取数据的数据结构.

索引概念

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。

索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

Mysql各种索引区别：
普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它是一种特殊的唯一索引，不允许有空值。
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

索引的数据结构

常见的MySQL主要有两种结构：Hash索引和B+ Tree索引，我们使用的是InnoDB引擎，默认的是B+树。

Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。hsah索引在mysql比较少用,他以把数据的索引以hash形式组织起来,因此当查找某一条记录的时候,速度非常快.当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布.所以他并不支持范围查找和排序等功能.
B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。b+tree是mysql使用最频繁的一个索引数据结构,数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能.相对hash索引,B+树在查找单条记录的速度虽然比不上hash索引,但是因为更适合排序等操作,所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作.

科普时间：B+Tree索引和Hash索引还有其他区别：
1.哈希索引适合等值查询，但是无法进行范围查询。-因为是hash结构每个键只对应一个值,而且是散列的方式分布
2.哈希索引没办法利用索引完成排序。-因为是hash结构每个键只对应一个值,而且是散列的方式分布
3.哈希索引不支持多列联合索引的最左匹配规则。
4.如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

常用的B+Tree索引的聚簇索引、覆盖索引

聚簇索引：聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引是将数据行的内容放在B--tree的叶子节点中，节点列存放数据列，由于不能把数据行放在两个不同的地方，所以每个表只能有一个聚簇索引。

非聚簇索引：叶子节点保存了主键值，要定位记录还要再查一遍聚簇索引。

覆盖索引：一个索引包含所有要查询的字段的值。

提问：

B+ Tree的叶子节点都可以存哪些东西吗？

InnoDB的B+ Tree可能存储的是整行数据，也有可能是主键的值。

那这两者有什么区别吗？

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

聚簇索引和非聚簇索引，在查询数据的时候有区别吗？

聚簇索引查询会更快，因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询，这是一个回表的过程。

是所有情况都是这样的吗？非主键索引一定会查询多次吗(非聚簇索引一定会查询多次？)？

覆盖索引，这种情况不会，不需要回表。覆盖索引指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。比如我们的索引里面保护了我们需要查询的字段，这就相当于覆盖索引，之间就能拿到不用再去回表。

联合索引的最左前缀匹配

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：
对列col1、列col2和列col3建一个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

注意

索引的字段可以是任意顺序的，如：

SELECT * FROM test WHERE col1=“1” AND clo2=“2”
SELECT * FROM test WHERE col2=“2” AND clo1=“1”

这两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。

有人会疑惑第二个查询语句不符合最左前缀匹配：首先可以肯定是两个查询语句都包函索引(col1,col2)中的col1、col2两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？此时我们可以借助mysql查询优化器explain，explain会纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。