文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

怎么在MySQL中对查询进行优化

2023-06-08 00:48

关注

本篇文章给大家分享的是有关怎么在MySQL中对查询进行优化,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。

一、创建索引规范

在学习索引优化之前,需要对创建索引的规范有一定的了解,此规范来自于阿里巴巴开发手册。

主键索引:pk_column_column

唯一索引:uk_column_column

普通索引:idx_column_column

二、索引失效原因

创建索引需知道在什么情况下索引会失效,只有了解索引失效的原因,在创建索引时才不会出现一些已知错误。

1.带头大哥不能死

这局经典的语句就是涵盖创建索引时一定要符合最左侧原则。

例如表结构为 u_id,u_name,u_age,u_sex,u_phone,u_time

创建索引为 idx_user_name_age_sex

查询条件必须带上u_name这一列。

不在索引列上做任何操作

不在索引列上做任何计算、函数、自动或者手动的类型转换,否则会进行全表扫描。简而言之不要在索引列上做任何操作。

俩边类型不等

例如建立了索引idx_user_name,name字段类型为varchar

在查询时使用where name = kaka,这样的查询方式会直接造成索引失效。

正确的用法为where name = "kaka"

不适当的like查询会导致索引失效

创建索引为idx_user_name

执行语句为select * from user where name like "kaka%";可以命中索引。

执行语句为select name from user where name like "%kaka";可以使用到索引(仅在8.0以上版本)。

执行语句为select * from user where name like ''%kaka";会直接导致索引失效

范围条件之后的索引会失效

创建索引为idx_user_name_age_sex

执行语句select * from user where name = 'kaka' and age > 11 and sex = 1;

上面这条sql语句只会命中name和age索引,sex索引会失效。

复合索引失效需要查看key_len的长度即可。

总结:%在后边会命令索引,当使用了覆盖索引时任何查询方式都可命中索引。

以上就是咔咔关于索引失效会出现的原因总结,在很多文章中没有标注MySQL版本,所以你有可能会看到is null 、or索引会失效的结论。

三、SQL优化杀手锏之 Explain

在写完SQL语句之后必须要做的一件事情就是使用Explain进行SQL语句检测,看是否命中索引。

下图就是使用explain输出格式,接下来将会对输出格式进行简单的解释。

怎么在MySQL中对查询进行优化

id 这列就是查询的编号,如果查询语句中没有子查询或者联合查询这个标识就一直是1。

如存在子查询或者联合查询这个编号会自增。

select_type

最常见的类型就是SIMPLE和PRIMARY,此列知道就行了。

table

理解为表名即可

**type

此列是在优化SQL语句时最需要关注的列之一,此列显示了查询使用了何种类型。

以下排序从最优到最差。

possible_keys

此列显示的可能会使用到的索引

**key

优化器从possible_keys中命中的索引

key_len

查询用到的索引长度(字节数),key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。

ref

如果是使用的常数等值查询,这里会显示const。

如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段。

如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func。

**rows

这是mysql估算的需要扫描的行数(不是精确值)。

这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。

filtered

此列表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数

**extra

在大多数情况下会出现以下几种情况。

总结

以上就是关于Explain所有列的说明,在平时开发的过程中,一般只会关注type、key、rows、extra这四列。

四、SQL优化杀手锏之 慢查询

上文说到了可以直接使用explain来分析自己的SQL语句是否合理,接下来再聊一个点那就是慢查询。

查看慢查询是否打开

怎么在MySQL中对查询进行优化

查看是否记录没有使用索引的SQL语句

怎么在MySQL中对查询进行优化

开启慢查询、开启记录没有使用到索引的SQL语句

set global log_queries_not_using_idnexes='on';set global log_queries_not_using_indexes='on';

怎么在MySQL中对查询进行优化

查询以上俩个配置是否打开

怎么在MySQL中对查询进行优化

设置慢查询时间,这个时间由自己把控,一般1s即可 set globle long_query_time=1;

如果查看这个时间没有变,则关于客户端在重新连接一次即可。

怎么在MySQL中对查询进行优化

查看慢查询存储位置

怎么在MySQL中对查询进行优化

然后随便执行一条不执行索引的语句即可在这个日志中查看到此语句

怎么在MySQL中对查询进行优化

上图中一般需要主要观察的是Query_time、SQL语句内容。

以上就是关于如何使用慢查询来查看项目中出现问题的SQL语句。

五、优化大法

此处跟大家聊一些常用的SQL语句优化方案,以上的俩个工具要好好的利用,辅助我们进行打怪。

以上就是怎么在MySQL中对查询进行优化,小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注编程网行业资讯频道。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯