• 售前

  • 售后

热门帖子
入门百科

MySQL索引是啥?不懂就问

[复制链接]
Jacqueline季 显示全部楼层 发表于 2021-8-14 14:45:34 |阅读模式 打印 上一主题 下一主题
目录


  • 概述
  • 从二叉树到B+树
  • 聚集索引
  • 非聚集索引
  • 团结索引和覆盖索引
  • B+树索引VS哈希索引
  • 普通索引和唯一索引
  • InnoDB VS MyISAM
  • 用explain分析索引利用
  • 总结

概述

以下是必要创建索引的常见场景,为了对比,创建测试表(a带索引、d无索引):
  1. mysql> create table test( --创建测试表
  2.     -> id int(10) not null AUTO_INCREMENT,
  3.     -> a int(10) default null,
  4.     -> b int(10) default null,
  5.     -> c int(10) default null,
  6.     -> d int(10) default null,
  7.     -> primary key(id), --主键索引
  8.     -> key idx_a(a), --辅助索引
  9.     -> key idx_b_c(b,c) --联合索引
  10.     -> )engine=InnoDB charset=utf8mb4;
  11. Query OK, 0 rows affected, 5 warnings (0.09 sec)
  12. mysql> drop procedure if exists insert_test_data;
  13. Query OK, 0 rows affected, 1 warning (0.00 sec)
  14. mysql> delimiter | --创建存储过程,插入十万个数据
  15. mysql> create procedure insert_test_data()
  16.     -> begin
  17.     -> declare i int;
  18.     -> set i=1;
  19.     -> while(i<=100000) do
  20.     -> insert into test(a,b,c,d)values(i,i,i,i);
  21.     -> set i=i+1;
  22.     -> end while;
  23.     -> end |
  24. Query OK, 0 rows affected (0.11 sec)
  25. mysql> delimiter ;
  26. mysql> call insert_test_data(); --执行存储过程
  27. Query OK, 1 row affected (11 min 44.13 sec)
复制代码
数据检索时在条件字段添加索引

聚合函数对聚合字段添加索引

对排序字段添加索引

为了防止回表添加索引

关联查询在关联字段添加索引

可以看出利用索引后,对查询速率优化提拔是巨大的,本文将从底层到实践搞懂MySQL索引。

从二叉树到B+树

二叉树:
二叉树(Binary Tree)是指至多只有两个子节点的树形数据布局,没有父节点的节点为根节点,没有子节点的节点称为叶子节点。
二叉搜索树就是任何节点的左子节点小于当前节点键值,右子节点大于当前节点键值。
如下图的二叉搜索树,我们最多只必要 ⌈ l o g ( n ) ⌉ ⌈log(n)⌉ ⌈log(n)⌉即三次即可匹配到数据,而线性查找的话最坏情况必要 n n n次才可匹配到。

但是二叉树可能会退化成链表的情况,如下图所示,如许就相当于全部扫描了,导致服从不高,为了办理这个题目,必要确保二叉树一直保持平衡,即平衡二叉树。

平衡二叉树:
平衡二叉树(AVL树)在满意二叉树特性的底子上,要求每一个节点的左右子树高度差不能凌驾1。它包管了树构造的一个平衡,当插入或删除数据导致不平衡时,会举行节点调整来保持平衡(详细算法略),确保查找服从。

平衡二叉树的一个节点对应一个键值和数据,我们每次查找数据就必要从磁盘中读取一个节点,也就是我们说的磁盘块,一个节点对应一个磁盘块。当存储海量数据时,树的节点会非常多,会举行很多次的磁盘I/O,查找服从仍是极低的。这就必要一个单节点能存储多个键值和数据的一种平衡树了。
B树: B树(Blance Tree)就是可以单节点存储多键值和数据的平衡树,每一个节点我们称之为页(Page),即一页数据。每个节点存储了更多键值和数据,把键值和数据都放在一个页当中,而且每个节点拥有了更多子节点,子节点的个数一样平常称为阶。B树在查找数据读取磁盘的次数也就大大淘汰,查找服从比AVL高很多。
如下图的3阶B树中,查找id=42的数据。起首在第一页里判定42键值大于39,根据指针P3找到第4页,再举行比较,小于键值45,又根据指针P1找到第9页,发现匹配有匹配的键值42,即找到相应数据。

B+树:
B+树是对B树的进一步优化。简单说就是B+树的非叶子节点是不存储数据的,仅存放键值。之以是如许做,是由于数据库中页的大小是固定的(InnoDB默认16KB),假如不存储数据,就可以存储更多键值,节点个数就越大,查找数据举行磁盘I/O次数进一步淘汰。

别的B+树的阶数是即是它的键值数量的,假如一个节点存储1000键值的话,那么只必要三层就可存储10亿数据,以是一样平常查找10亿数据只需两次磁盘I/O即可(妙啊)。
同时B+树叶节点的数据是按次序举行分列的,以是B+树适合范围查找、排序查找和分组查找等(B各数据分散在节点上,相对就困难),也就是为什么MySQL接纳B+树索引的缘故原由了。

聚集索引

聚集索引或聚簇索引(Clustered Index)是一种对磁盘上现实数据重新构造并按指定的一个或多个列的值排序。数据行的物理次序与列值(一样平常是主键那列)的逻辑次序相同,一个表中只能有一个聚集索引(由于只能以一种物理次序存放)。
  1. InnoDB
复制代码
就是用的聚集索引,它的表中的数据都会有一个主键,即使你不创建主键,
  1. InnoDB
复制代码
会选取一个Unique键作为主键,假如表中连Unique键都没有界说的话,
  1. InnoDB
复制代码
会为表添加一个名为row_id的隐蔽列作为主键。
也就是说我们通过InnoDB把数据存放到B+树中,而B+树中的键值就是主键,那么在B+树中的叶子节点存储的就是表中的全部数据(即该主键对应的整行数据),数据文件和索引文件是同一个文件,找到了索引便找到了数据,以是我们称之为聚集索引。
聚集索引更新代价高。插入新行或更新主键时会强制将每个被更新的行移动到新的位置(由于要按主键排序),而移动行可能还谋面对页分裂题目(即页已满),存储引擎会将该页分裂成两个页面来容纳,页分裂会占用更多磁盘空间。即索引重排,造成资源浪费。
聚集索引适合范围查询。聚集索引查询速率很快,特别适合范围查抄(between、<、<=、>、>=)或group by、order by的查询。由于聚集索引找到包含第一个值的行后,后续索引值的行在物理上毗连在一起而不必进一步搜索,避免大范围扫描,大大提高查询速率。

好比查询id>=19而且id<30的数据:通常根节点常驻在内存中(即页1已在内存),起首在页1找到了键值19及其对应指针P2,通过P2读页3(此时页3不在内存中,必要从磁盘中加载),然后在页3查找键值19的指针P1,又定位到页8(同样的从磁盘加载到内存),由于数据是按链表举行次序链接的,可以通过二分找到键值19对应数据。
找到键值19后,由于是范围查找,这时可以在叶子节点里举行链表的查询,依次遍历并匹配满意的条件,一直找到键值21,到末了一个数据仍不能满意我们的要求,此时会拿着页8的指针P去读取页9的数据,页9不在内存中同样必要磁盘加载读进内存,然后依此类推,直到匹配到键值34时不满意条件则终止,这就是通过聚集索引查找数据的一种方法。

非聚集索引

非聚集索引或非聚簇索引(Secondary Index)就是以主键以外的列作为键值构建的B+树索引,索引中索引的逻辑次序与磁盘上行的物理存储次序不同,一个表中可以拥有多个非聚集索引。在
  1. InnoDB
复制代码
中处了主键索引外其他索引都可以称为辅助索引或二级索引。
MySQL中的
  1. MyISAM
复制代码
利用的就是非聚集索引。表数据存储次序与索引数据无关,叶节点包含索引字段值及指向数据页数据行的逻辑指针(其行数量与数据表数据量相同),以是想要查找数据还必要根据主键再去聚集索引中查找,根据聚集索引查找数据的过程就称为回表。
好比界说一张数据表test,他是由
  1. test.frm
复制代码
  1. tsst.myd
复制代码
  1. test.myi
复制代码
构成的:
       
  • .frm:记录了表界说语句.   
  • myd:记录了真实表数据.   
  • myi:记录了索引数据
再检索数据时,先到索引树
  1. test.myi
复制代码
中举行查找,取到数据地点
  1. test.myd
复制代码
的行位置,拿到数据。以是
  1. MyISAM
复制代码
引擎的索引文件和数据文件是独立分开的,找到索引不即是找到数据,即非聚集索引。
一个表可以有不止一个非聚集索引,现实上每个表最多可以创建249个非聚集索引,但是每次给字段建一个新索引,字段中的数据就会被复制出来一份用于天生索引,因此给表添加索引会增加表的体积,占据大量磁盘空间和内存。以是若磁盘空间和内存有限,应限定非聚集索引数量。
此外每当你改变了一个创建非聚集索引的表中数据时,必须同时更新索引,以是非聚集索引会降低插入和更新速率。

好比查找数据36,是用两个数字表现,前面谁人数字36代表的是索引的键值,背面谁人64代表的是数据的主键。以是说我们找到36后,并没有拿到数据,还要根据它对应的主键去到聚集索引表中去查找数据。

团结索引和覆盖索引

团结索引,顾名思义就是指对表上的多个列团结起来举行索引。在创建团结索引的时间会根据业务需求,把利用最频仍的列放在最左边,由于MySQL的索引查询会依照最左前缀匹配的原则。
最左前缀匹配原则即最左优先在检索数据的时间,从团结索引的最左边开始匹配,以是当我们创建一个团结索引的时间,如(a,b,c)相当于创建了(a)、(a、b)、(a、b、c)三个索引,这就是最左匹配原则。
覆盖索引(Covering index)只是特定于详细select语录而言的团结索引。也就是说一个团结索引对于某个select语句,通过索引可以直接获取查询效果,而不再必要回表查询啦,就称该团结索引覆盖了这条select语句。可以完满的办理非聚集索引回表查询的题目,但条件是注意查询时索引的最左匹配原则。

B+树索引VS哈希索引

原理:
       
  • B+树索引可能必要多次运用二分查找来找到对应的数据块。   
  • Hash索引时通过Hash函数,计算出Hash值,在表中找出对应的数据。
哈希索引适合大量不同数据等值精确查询,但不支持模糊查询、范围查询,无法用索引来举行排序,也不支持团结索引的最左匹配原则,而且有大量重复键值的情况下,还会存在哈希碰撞题目。

普通索引和唯一索引

普通索引的字段可以写入重复的值,而唯一索引的字段不能写入重复的值。先介绍Insert Buffer和Change Buffer:
       
  • Insert Buffer   
  • 对于非聚集索引的插入,先判定插入的非聚集索引是在在缓存池中。若在则直接插入,若不在,则先放入Insert Buffer,之后在一肯定频率和情况镜像Insert Buffer和辅助索引页子节点的归并操纵。将多次插入归并为一次操纵,淘汰磁盘离散读取。要求索引是辅助索引且不唯一。   
  • Change Buffer   
  • 是Insert Buffer的升级版,除了插入还支持删改。通过innodb_change_buffer设置利用场景,默以为all(另有none、inserts、changes等值),通过innodb_change_buffer_max_size设置最大利用内存占比(默认25%,最大值50%),但在RR隔离级别下会出现死锁。同样要求索引是辅助索引且不唯一。
唯一索引利用的是Insert Buffer,由于判定是否违反唯一性束缚,假如都已经读入内存了,那直接更新内存会更快,就没须要利用Change Buffer。
普通索引查找到满意条件的第一个记录后,继承查找下一个记录直到不满意条件,对唯一索引来说,查到第一个记录就返回效果竣事了。但是InnoDB按页读取到内存,背面满意条件的可能都在之前的数据页里,以是普通索引多的频频内存扫描斲丧可以忽略不计。
小结:
       
  • 数据修改时,普通索引可用Change Buffer,而唯一索引不行。   
  • 数据修改时,唯一索引在RR隔离级别下更容易出现死锁。   
  • 查询数据时,普通索引查到一条记录还需继承判定下一个记录,而唯一索引查到后直接返回。   
  • 当业务要求某字段唯一时,若代码能包管写入唯一值,则用普通索引,否则用唯一索引。

InnoDB VS MyISAM

                        MyISAM            InnoDB                                    数据存储            .frm存储表界说、.myd数据文件、.myi索引文件            不开启独立表空间则.frm文件,否则idb文件                            索引实现            非聚集索引随机存储,只缓存索引            聚集索引次序存储,缓存索引和数据                            存储空间            可被压缩,存储空间较小,支持静态表、动态表、压缩表三种格式            需更多内存和存储                            备份恢复            文件情势存储可跨平台,可单独针对某个表操纵            拷贝数据文件、备份binlog,体量可能非常大                            事件            不支持(也不支持外键,更强调性能)            支持(包罗外键、安全、回滚等高级功能)                            auto_increment            自增长列必须是索引,团结索引中可不是第一列            自增长列必须是索引,团结索引中也必须是第一列                            锁            支持表级锁            支持行级锁                            全文索引            支持FULLTEXT类型全文索引            不支持FULLTEXT,但可利用Sphinx插件                            表主键            允许没有任何索引和主键的表存在            会主动天生隐蔽主键                            总行数            保存有表的总行数            没有保存表的总行数,会利用辅助索引去遍历                            CRUD            相对适合大量查询            相对适合增改删        对比之下,基本上可以考虑利用
  1. InnoDB
复制代码
来替代
  1. MyISAM
复制代码
了,
  1. InnoDB
复制代码
也是目前MySQL的默认引擎,但是详细题目详细分析,也可根据现实情况对比两者优劣,选择更合适的。
再扩展一下为什么
  1. MyISAM
复制代码
查询比
  1. InnoDB
复制代码
快?
       
  • InnoDB要缓存数据和索引;MyISAM只缓存索引,换进换出的淘汰。   
  • InnoDB寻址要映射到块再到行;MyISAM直接记录文件的OFFSET,定位更快。   
  • InnoDB还要维护MVCC同等,或许你的场景没有,但也必要查抄和维护。
MVCC(Multi-Version Concurrency Control)多版本并发控制
InnoDB为每一行记录添加了两个额外的隐蔽值(创建版本号、删除版本号)来实现MVCC,一个记录行数据创建时间,一个记录行数据逾期/删除时间。但是InnoDB并不存储这些变乱发生的现实时间,相反它只存储这些变乱发生时的体系版本号。随着事件的不断创建而不断增长,每个事件在开始时都会记录它自己的体系版本号,每个查询必须去查抄每行数据的版本号与事件的版本号是否相同。也就是说每行数据的创建版本号不大于事件版本号,以确保事件创建前行数据是存在的;行数据的删除版本号大于事件版本号或未界说,以确保事件开始前行数据没有被删除。

用explain分析索引利用
  1. explain
复制代码
可以看SQL语句的实行效果,可以帮助选择更好的索引和优化查询语句,语法:
  1. explain select... from ... [where...]。
复制代码
用前面概述那节的test表做测试:
  1. mysql> explain select * from test where a=88888;
  2. +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
  3. | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
  4. +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
  5. |  1 | SIMPLE      | test  | NULL       | ref  | idx_a         | idx_a | 5       | const |    1 |   100.00 | NULL  |
  6. +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
  7. 1 row in set, 1 warning (0.03 sec)
  8. mysql> explain select b,c from test where b=88888;
  9. +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
  10. | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
  11. +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
  12. |  1 | SIMPLE      | test  | NULL       | ref  | idx_b_c       | idx_b_c | 5       | const |    1 |   100.00 | Using index |
  13. +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
  14. 1 row in set, 1 warning (0.00 sec)
  15. mysql> explain select * from test where a=(select a from test where a=88888);
  16. +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
  17. | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
  18. +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
  19. |  1 | PRIMARY     | test  | NULL       | ref  | idx_a         | idx_a | 5       | const |    1 |   100.00 | Using where |
  20. |  2 | SUBQUERY    | test  | NULL       | ref  | idx_a         | idx_a | 5       | const |    1 |   100.00 | Using index |
  21. +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
  22. 2 rows in set, 1 warning (0.00 sec)
复制代码
重点看这三列即可:
  1. select_type
复制代码
  1. type
复制代码
  1. extra
复制代码

            select_type值            说明                                    SIMPLE            简单查询(不利用关联查询或子查询)                            PRIMARY            包含关联查询或子查询                            UNION            团结查询中第二个及背面的查询                            DEPENDENT UNION            依赖外部的关联查询中第二个及以后的查询                            UNION RESULT            团结查询效果                            SUBQUERY            子查询中的第一个查询                            DEPENDENT SUBQUERY            依赖外部查询的子查询中的第一个查询                            DERIVED            用到派生表的查询                            MATERIALIZED            被物化的子查询                            UNCACHEABLE SUBQUERY            子查询效果不能被缓存,必须重新评估外层查询的每一行        type(显示这一行的数据是关于哪张表的)
            type的值            说明                                    system            查询对象只有一会数据 ,最好的情况                            const            基于注解或唯一索引查询,最多返回一条效果                            eq_ref            表毗连时基于主键或非NULL的唯一索引完成扫描                            ref            基于普通索引的等值查询或表间等值毗连                            fulltest            全文检索                            ref_or_null            表毗连类型是ref,但扫描的索引中可能包含NULL值                            index_merge            利用多个索引                            unique_subquery            子查询利用唯一索引                            index_subquery            子查询利用普通索引                            range            利用索引举行范围查询                            index            全索引扫描        extra(办理查询的详细信息)
            extra的值            说明                                    Using filesort            用的外部排序而不是索引排序                            Using temporary            需创建一个临时表来存储布局,通常发生在对没有索引的枚举行group by时                            Using index            利用覆盖索引                            Using where            利用where来处理效果                            Impossible where            对where子句判定效果总是false而不能选择任何数据                            Using join buffer            关联查询中,被驱动表的关联字段没有索引                            Using index condition            先条件过滤索引再查数据                            Select tables optimized away            利用聚合函数来访问存在索引的某个字段        
总结

本篇文章就到这里了,希望能给你带来帮助,也希望您可以或许多多关注草根技能分享的更多内容!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作