更快的查询 | MySQL百万数据优化(索引调优)

阿千弟

已于 2022-11-17 10:36:20 修改

阅读量1.5k

点赞数

文章标签： mysql java spring boot

于 2022-11-16 17:29:38 首次发布

本文链接：https://blog.csdn.net/qq_51033936/article/details/127882268

版权

♨️本篇文章记录的为MySQL百万数据索引建立调优相关内容，适合在学Java的小白,帮助新手快速上手,也适合复习中，面试中的大佬🙉🙉🙉。
♨️如果文章有什么需要改进的地方还请大佬不吝赐教❤️🧡💛
👨‍🔧个人主页 : 阿千弟

文章目录

场景 :

在这里插入图片描述

这里创建了一个test_user表, 利用存储过程生成了400多万条假数据.

在这里插入图片描述

然后呢, 我们的表中是没有任何索引的

在这里插入图片描述

然后我们在不使用缓存的情况下通过phone, lan_id, region_id对数据进行查询,这样查出的时间比较真实,时间4.940ms

在这里插入图片描述

我们对phone, lan_id, region_id三个字段加上索引后再来执行, 时间很快为0.009s, 比刚才快多了

在这里插入图片描述

优化1 : 最左前缀法制

为什么是最左前缀呢, 比如说我们创建了a,b,c三个索引, 那么最好是按照这个索引建的顺序去执行?

介绍 :
如果建立的是复合索引，索引的顺序要按照建立时的顺序，即从左到右，如： a -> b -> c（和 B+树的数据结构有关）

无效索引举例 :
a -> c：a 有效，c 无效
b -> c : b、c 都无效
c：c无效

在这里插入图片描述
我们建立的这个复合索引的顺序是phone, lan_id, region_id, 我们查询的顺序也是安装这个顺序来的, 这个就叫做最左前缀法制

接下来我们通过EXPLAIN这个分析工具来分析一下这条select语句

在这里插入图片描述

可以看到:
tpye(索引级别) : ref级别(算是比较好的一个索引级别了)
possible_keys(可能使用到的索引字段) : idx_phone_lan_region
keys(真实使用到的索引字段) : idx_phone_lan_region
key_len(索引长度) : 90(不是个精确值, 是个预估值, 越小越好)
ref: const, const, const(代表你使用三个常量去查找)
rows(查找的行数) : 1(值越小越好)

刚才我们是按照我们建立索引的顺序查找的, 如果现在我们中间断了一个索引字段lan_id会是怎么样的?

在这里插入图片描述

可以看到原来的key_len=90现在变成key_len=82了, 为什么会这样呢?

原因:

就是因为有些字段没有用上索引, 我们再来看一下ref发现它只剩下一个常量, 那就说明它就只有这个phone用到了这个索引, 它的lan_id, region_id是没有用上索引的.这就是破坏了最左前缀法制

举个例子:

phone, lan_id, region_id这三个字段好比是 桥头, 桥身, 桥尾, 现在桥身给去掉了, 那么过桥的时候从桥头就无法走到桥尾, 只能走到桥头.

我们下面再把桥头(phone)去掉, 只剩下桥身和桥尾(lan_id, region_id)
在这里插入图片描述

可以看到可能用到的索引字段为空, 真实用到的索引字段也为空, 然后发现rows已经是400万的数据量了, 说明它没有用到索引. 大家可以想一下, 你没有桥头怎么去桥的中间和桥的尾部呢, 这就是最左前缀法则.

在这里插入图片描述

优化2 : 不要对索引做以下处理

在这里插入图片描述

顺便用一个看下效果: 发现也是没有用到索引

在这里插入图片描述

优化3 : 索引不要放在范围查询右边

在这里插入图片描述
之前我们按照最左前缀法制查到r的rows是90, 现在是86, 其实就是第三个字段(region_id)没有用到索引

在这里插入图片描述

现在我们把第三个字段去掉, 发现结果还是86, 所以呢索引字段放在放在范围查找的右边,它的索引是会失效的, 底层和它的数据结构有关

在这里插入图片描述

优化4 : 减少select * 的使用

在这里插入图片描述
就是我们的where字段后是a, b两个字段, select后面也要是a, b两个字段, 即select里面的字段被where里面的检索条件覆盖了, 还有一点就是select会查找很多不必要的字段, 在数据库向客户端传输数据的时候会浪费很多不必要的性能

优化5 : like模糊搜索

在这里插入图片描述
可以看到我们使用like ' %字段 %'是使用不了索引的.
解决方法 :
这样写 like ' 字段%', 可以看到它的type是range级别的(就是范围查找)

然后这样写数据也是可以查出来的

在这里插入图片描述

但是然后我们非要使用使用like ' %字段 %'写, 该怎样使用索引呢?

我们可以使用覆盖索引的方式, 这是扫描全部索引得出来的数据，没有回表查询

在这里插入图片描述

优化6 : order by 排序

我们对create_time字段创建索引, 然后进行查询发现没有使用到索引, 然后查询的数量级已经是在400万以后了, 而且最重要的一点是发现它使用了文件内排序 : 这是很恐怖的一件事情, 把表中的数据复制了一份放在了内存里面, 在内存里开辟了一段空间对此进行排序, 内存空间是非常宝贵的资源

在这里插入图片描述