相信很多人对于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语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。
在创建索引的时候都会考虑哪些因素呢?
- 搜索的索引列,即where中常用的
- 使用唯一索引,索引重复值越少,索引效果越好
- 使用短索引,索引越短占用空间越少,扫描速度越快
- 利用左前缀(复合索引),只有最左边那个列最高效
- 不要过度索引,一般不超过5个