mysql二级索引需要回表_MySql优化随笔

每次百度sql优化都是那么几个东西,这次自己来写一下自己的总结

一、数据库是怎么存数据的

首先你要知道数据库中的数据是怎么存储的,数据库将数据放置在一个叫做数据页的结构中,这个数据页中的数据用单链表串起来,ok,到这,我只要找到这个数据页就能遍历他,也就是能查到所有数据了,但是一个页放不了所有数据,所以呢,得在增加一个页,我们成为页溢出,或者叫页分裂,一大堆数据得用多个页储存,多个页用双向链表串起来,这样数据就都能查到了.

光查到还不行,我们还得更快的查到,所以数据存放就是个问题了,不能乱放,得有顺序的放,有顺序的放就会导致放的时候比较麻烦(比如像arraylist一样,插入得把后面的数据全部移动才能完成中间的插入),但是这样方便查找,所以我们得按顺序去存放数据,但是刚才也说了我们不能把所有数据都放到一块,所以数据放到页中,多个页散乱起来也不好管理,所以还要有一个管理页的东西,把页管理起来,自然而然就会形成一个叫树的结构,这样我们只要有根节点在手,所有数据都能查找到了(这个根在mysql中是位置不变的,固定的),由于有序,这就是一个搜索树.

二、树

刚才说到树,我们听过的有二叉树,二叉搜索树,平衡二叉树,红黑树,树就是一种能更快的查找数据的数据结构,那么mysql中用的是哪种树呢?

上面的都不是(所以上面那个图没花成二叉树),我们知道,树的查找跟树的深度有关系,所以为了更快的查找我们不能一个节点只能有2个儿子,我们让他有多个,这样下来,一个瘦高的树就会变的矮胖,更加方便查找,这样的一棵树我们称之为多路搜索树(传说中的B-树,不念B减树,就是B树,中间是杠不是减号,就是多路搜索树),B树有个特点,他的每个节点都能储存数据,这样的话我们要遍历树中的内容,必须使用中序遍历,这样搞就很麻烦,于是出现了B-树另外一种变种,B+树,B+树比B-树更加矮胖,说明他查找效率更高,而且B+树规定所以内容必须储存在叶子节点中,就是树的最下面一层,而且这最下面一层还得用链表串起来这样我们要遍历所有数据就更好查找了,直接遍历最下面一层的链表就行了,所以我们的mysql用的就是这种树,叫B+树

具体树的数据结构可以自己去看关于数据结构的书,这里参考小灰的漫画数据结构

B-树    https://mp.weixin.qq.com/s/rDCEFzoKHIjyHfI_bsz5Rw

B+树https://mp.weixin.qq.com/s/jRZMMONW3QP43dsDKIV9VQ

B+树示意图

二、索引

B+树是搜索树,所以上面那些没有存值的节点,自然就类似于一个目录,这个目录,就是我们说的索引,一个索引就对应一颗B+树

数据库在存储数据时,会根据主键,建立一颗向上面一样的B+树(如果没有主键,数据库会在储存数据的时候自动给你一个隐藏列作为主键),而我们说的优化,就是如何更好的使用这个树,这才是sql优化的关键点.

这个树是有序的,所以你要是随便在中间插入数据,一定会调整整个的树的结构,(为了保证有序性嘛),所以我们要知道的第一点,索引会影响插入,修改,删除的效率.

接下来我们说说如何利用这个树(索引)

索引的分类

1.聚簇索引(就是主键索引)

表自动会创建这个索引,这个B+树,会把主键的值作为索引KEY按顺序储存,每个叶子节点会将整个数据内容全部储存下来,也就是你一条数据里有什么,我这个叶子节点中就有什么,最慢的查询,全表扫描,就光用我这颗树就行,我啥都有

2.非聚簇索引(二级索引)

除了主键的这个索引,其他索引都是非聚簇索引,也就是你自己建立的那个索引,就相当于在表空间下,新建立了一个B+树,这个树跟上面不一样,这个树是用你指定的列的值作为KEY按顺序储存,你指定的  列的值和主键的id 作为值储存在叶子节点中,也就是说,你要是根据我来查询,查完之后还得拿着主键id再去聚簇索引中查找一遍,才能拿到所有的值,这个过程称之为回表,那就又有一个优化点,尽量减少回表操作

查询非聚簇索引是顺序io / 回表是随机io,顺序io要比随机io快很多 ,如果回表查询量大,偏向于使用全表扫描,直接访问聚簇索引

3.联合索引(这个本质还是个二级索引)

这颗树关键点如下:

假如按照A/B/C三列来建立了一个联合索引,联合索引是一棵树,   分别给ABC三列建立二级索引,是三棵树

假如按照A/B/C三列来建立了一个联合索引,会先按照A的值作为KEY,当A相同时,再按照B的值作为KEY,当B相同时,再按照C的值作为KEY的顺序来储存数据,叶子节点中数据是这三列的值和主键id

看到这里是不是感觉跟order by字段好相似,这个确实跟order by有关系

三、索引的优化

1.索引不是万能的,不能随便加索引

上面说索引会影响插入,修改,删除的效率,这个影响不止一点点,因为一个索引就是一棵树,你要是在某列上建立了多个索引,当修改这列时,会重新调整相关的好几颗树,效率就浪费在这里了,所以索引对查询有好处,对修改没有一点好处,不能随便建立索引

2.能用主键做条件,就别用别的

根据主键查询只会访问聚簇索引,而且聚簇索引中包含其他所有数据,所以说这是最快的访问方式

3.根据列中数据的分散性,来决定是否添加索引

列中数据越分散,通过条件筛选后留下来的数据就会越少,比如身份证号,等值查询一次匹配一个,性别,等值查询一次匹配一坨,我们知道我们二级索引是可能需要回表的,你查询出来一坨的时间其实并不多,但是拿着你返回的一坨id去回表,这就浪费时间了,所以,列中数据越分散,加索引后的查询效率会越高

4.在有索引的列中少使用null,或者说少用null作为条件去查询

因为数据库中null不等于null,所以即便是唯一约束,都无法限制多个null的插入,所以一般某列设置为唯一约束,都要限制非空,否则可能没有唯一的意义了

5.巧用联合索引

第一点 : 联合索引建立的顺序就是按顺序一级一级的建立,所以我们查询的时候有个最左匹配特性,比如按照name,age,sex建立联合索引,你按照name,age,sex三列查询(顺序无所谓,查询优化器会帮你做优化)就能用上索引,你按照name,age两列也能用到索引,你按照name单列也可以,因为这些都是联合索引的全部或者局部,但是你要是按照age或者sex单列,或者age,sex两列这就不行了,因为我name是不知道是不是有序的,这时优化器可能就更偏向于使用全表扫描了

第二点 : 我们上面说了,联合索引的叶子节点中数据是这三列的值和主键id,如果你查询后显示的条件也是这三列,那么数据在二级索引的B+树中就有,就不会去回表了,少一步回表操作,当然更快,这叫覆盖索引

6.利用索引覆盖

原因如上,同时,我们也要求显示的列尽量不要用*,生产环境中,需要什么查什么,不要随便写*,目的就是为了碰上覆盖索引

7.关于排序

单列排序能直接使用索引,但是如果是多列排序就要考虑一下了,mysql中规定规定使用联合索引的各个排序列的排序顺序必须是一致的.在多列排序中,如果要用就只能使用联合索引,而联合索引建立的B+树有特定顺序,如果sql中多列排序顺序与B+树中顺序统一,则可以使用,但是如果建立了ABC联合索引,但是order by A,B desc ,C  这样就无法使用联合索引了

mysql8的新特性,降序索引  https://www.cnblogs.com/ivictor/p/9072361.html

8.关于模糊查询

我们都知道模糊查询会全表扫描,但是为什么呢?因为建树的时候我是根据数据的值一个一个比较出顺序建立的树,但是你给我一个%开头的数据,比如%aaa%,那就是匹配谁都可以了啊,所以只能用全表扫描了.但是索引可以匹配列前缀,如果你给我的是 aaa% 以固定的字符串开头的数据,那我也能利用索引,因为 aaa开头的数据一定是存放在二级索引某一片连续的区域的,但是如果你要是%aaa就不行了,如果非要这样做,可以在入库时将数据的逆序字符串也存入数据库,也建立索引这样就能利用这个逆序列做 aaa%的匹配了

9.只为用于搜索、排序或分组的列创建索引

也就是只为  where  order by  group by 后面的列建立索引,不要为查询出来显示的列建立索引

10.不要冗余索引

比如我们建立了联合索引ABC,和单列的非聚簇索引A,如果单纯查找A=‘aaa‘的数据,其实这两个索引理论上我们都能利用到,而联合索引的功能比单列的A的索引还广,所以完全可以删除掉A索引,功能没有差别,还少维护一颗B+树

11.保证索引列是以单独列的形式出现

a = 5+3 可以利用索引, 但是 a-3 = 5 就无法利用索引了 ,因为没有一棵树中的值对应着 a-3的值,同时优化器也不会自动优化这种写法

12.索引中的数据占用的空间尽量小

比如bitint效率就会高于int,因为你占用数据空间越小,表示我同样大小的地方就能多存数据,同一个节点数据越多,B+树就越矮胖,反之,就越瘦高,影响查找的层数,所以某些大字段又不需要全值匹配的,可以在索引上限制只对前多少个字符建立索引

四、索引查询的速度

1.const   速度为常量,这是最快的一种查询速度,一般的根据主键等值查找,或者根据唯一约束的二级索引查找,一次只查到一条,就是这么快(这时虽然会回表,但是也是会很快),但是条件中不能有跟null产生关系的

2.ref       速度也挺快,一般是由于普通二级索引等值匹配,但由于二级索引并不限制索引列值的唯一性,所以可能找到多条对应的记录,再根据这些记录回表查找, 也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数.如果匹配的记录较少,则回表的代价还是比较低的,但如果匹配的记录太多了,可能会直接使用全表扫描(这中间有个叫查询优化器的东西,在sql执行之前,会在表的元数据表中先评判一下用哪种方式查询,比如在这里,表中一共10w条数据,查询优化器估摸了一下大约有9.9w数据会被匹配到,就会直接跳过索引,使用算表扫描)

这里关于null要做一个解释

因为数据库中null != null,所以不论是普通的二级索引,还是唯一二级索引,它们的索引列对包含NULL值的数量并不限制,所以我们采用key IS NULL这种形式的搜索条件##最多##只能使用ref的访问方法，而不是const的访问方法。

3.ref_or_null　　比上面慢一点,因为条件中多了一些带null值的条件,可能回表次数就会偏多

4.range　　　　范围查询时,比如用 in  or关联的条件,速度可能就不会太快了,因为有回表操作

5.index　　　　使用覆盖索引时,也就是说我们可以直接通过遍历二级索引的叶子节点的记录来比较某个条件是否成立，把匹配成功的二级索引记录的列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多(聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键)，而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多

6.all 　　　　　全表扫描,直接访问聚簇索引

五、EXPLAIN

六、单表联表查询过程

七、查询优化器

八、optimizer trace

九、写在后面

参考文章 : 掘金小册中的< >

作者 文章写的还是很详实的,通过阅读他的这本小册,写了上边的这篇随笔,这本小册读完让我对mysql中的一些原理又略知了三四,但是本人对于小册中很多细节还未能吸收,所以上面的文字描述并不是类似于教学一样教读者怎么做,权当自己的一篇随笔,记录一些关键知识点,简单串联了一下,如果有缘能看到这篇文章,建议可以去我提供的链接或者自己搜索其中知识点,已获得更加清晰的了解.

这篇文章能起到抛砖引玉的作用,足矣