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

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

@[TOC]

场景 :

这里创建了一个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万以后了, 而且最重要的一点是发现它使用了文件内排序 : 这是很恐怖的一件事情, 把表中的数据复制了一份放在了内存里面, 在内存里开辟了一段空间对此进行排序, 内存空间是非常宝贵的资源

优化方法 : 使用覆盖索引

可以看到我们已经成功的使用到了索引, 没有使用文件内排序
关于排序的问题 : 我们其实可以不在mysql中进行, 我们使用java对其进行排序, 要知道java的速度可比mysql快多了

1. mysql有个sort_buffer_size参数，小于就在内存里，否则就会用临时文件；
2. 要看索引的优化的，java肯定是内存里，mysql这个是磁盘io啊。另外，只有B+树的头节点或者前两层在缓存里，其他的数据块都在硬盘里。总之mysql order by操作成本比较高。
   

   如果这篇【文章】有帮助到你💖，希望可以给我点个赞👍，创作不易，如果有对Java后端或者对mysql调优感兴趣的朋友,请多多关注💖💖💖
   👨‍🔧个人主页 : 阿千弟