Mysql索引降维 优化查询 提高效率

举报
仙士可 发表于 2023/06/14 11:26:49 2023/06/14
【摘要】 写在前面在前一篇文章中,我们已经介绍了索引、索引的优化规则等等在其中我们有引申出组合索引,多个单字段索引冲突两个知识点。本文章主要是与后者有关联。在原文中,我们使用了下面的例子现在有这样子的数据量:100W条数据 user_name=’我是用户名’100条数据 user_phone=’110′5条数据 user_name=’我是用户名’ and user_phone=’110′假设有这样子一...

写在前面

在前一篇文章中,我们已经介绍了索引、索引的优化规则等等

在其中我们有引申出组合索引,多个单字段索引冲突两个知识点。

本文章主要是与后者有关联。

在原文中,我们使用了下面的例子

现在有这样子的数据量:
100W条数据 user_name=’我是用户名’
100条数据 user_phone=’110′
5条数据 user_name=’我是用户名’ and user_phone=’110′

假设有这样子一条语句:

select * from test where user_name = '我是用户名' and user_phone='110'

有两个字段都有索引可用,mysql会选择一个使用。这是属于mysql的内部处理判断

正常情况下,如果用user_phone索引生效的话,会很快得到结果(先筛选出100条 再筛选)

如果user_name生效,则要先筛选100W条数据,再筛选user_phone

mysql内部的错误判断可能使得user_name索引生效,此时效率就会很低了,我们可以强制使用某个索引
复制

指定使用索引的意义

从以上例子中,我们可以思考并归纳。能提升效率的核心是:在一开始就尽可能地筛选出准确的数据

所以当我们发现mysql可能处理出错的情况时,可以手动指定使用更优的索引来提高查询效率。

这个可以称为索引降维

降维

数据的选择度越大,则维度越大。

降维,按我个人的理解是:在大量的数据中,一层一层地筛选过滤,维度也会逐渐减低。

点线面中,共有黑红两种颜色。

目标:筛选出所有红色的点

步骤:选出所有带有红色点的面 –> 选出所有带有红色点的线 –> 在线上选出所有红色点

索引降维

在老旧的mysql版本中,where的条件顺序还会很大影响执行结果。 比如在上面的举例中,用条件语句来举例,而不是索引

select * from test where user_name = '我是用户名' and user_phone='110'
复制
select * from test where user_phone='110' and user_name = '我是用户名'
复制

这两个语句会出现上面索引冲突时 mysql没有使用更优索引的情况一样,第一条语句会先筛选出100W条数据,再筛选user_phone=110

然而在后续的mysql发展中,sql构造器优化器会自动帮我们排序执行,这种问题已不太需要人工去调整。

但是当我们建立组合索引的时候,则会根据我们的选择顺序来构建了。

比如有这么一个索引

index user_info (user_name, user_phone)
复制

我们可以用大小分类的情况举例看一下

└名字一
└──user_phone 110
└──user_phone 120
└──user_phone 119
└名字二
└──user_phone 110
└──user_phone 120
└──user_phone 119
└名字三
└──user_phone 110
└──user_phone 120
└──user_phone 119
复制

而如果我们把顺序调整成(user_phone, user_name)

那么就可以把组合索引看成

└─110
└──user_name 名字一
└──user_name 名字二
└──user_name 名字三
└─120
└──user_name 名字一
└──user_name 名字二
└──user_name 名字三
└─119
└──user_name 名字一
└──user_name 名字二
└──user_name 名字三
复制

两种情况,都会在某些场景下有自己的优势,所以我们就需要结合自己的业务数据来进行选择啦。

用我们的老例子来说: 以名字来区分,第一次筛选出现100W条数据,然后再筛选手机号。 以手机号来区分,第一次筛选出现100条数据,然后再筛选用户名。

同样的情况还出现在分表中,用什么条件来分表也是极其重要的。

分表中,如果我们以订单的年份作为分表条件,想要搜索ID=3的会员在2019年某个月份日期的订单,那么我们需要先搜索2019年的表(一年的订单假设有100W条记录),然后再筛选用户ID和其他月份等条件。

如果我们以订单的年份+月份作为分表条件(只是举例,有很多分表条件可以决定),那么初步筛选的数据就会少了很多了,后续的筛选步骤也会更快完成。

总结

在分表、组合索引等等场景下,我们可以结合业务数据,进行降维的顺序思考,尽可能地在一开始就筛选出比较准确的数据,在后续的筛选中则只需要遍历检查很少的一部分数据,已达到提高查询效率的效果

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。