位置: 编程技术 - 正文

一个优化MySQL查询操作的具体案例分析(mysql如何优化sql查询)

编辑:rootadmin

推荐整理分享一个优化MySQL查询操作的具体案例分析(mysql如何优化sql查询),希望有所帮助,仅作参考,欢迎阅读内容。

文章相关热门搜索词:优化mysql查询,mysql数据库查询优化的技巧,mysql查询优化的几种方法,mysql查询优化的几种方法,优化mysql查询,mysql会优化查询条件的顺序吗,mysql会优化查询条件的顺序吗,mysql查询优化的几种方法,内容如对您有帮助,希望把文章链接给更多的朋友!

问题描述

一个用户反映先线一个SQL语句执行时间慢得无法接受。SQL语句看上去很简单(本文描述中修改了表名和字段名):SELECT count(*) FROM a JOIN b ON a.`S` = b.`S` WHERE a.`L` > '-- ::' AND a.`L` < '-- ::' ;

且查询需要的字段都建了索引,表结构如下:

从语句看,这个查询计划很自然的,就应该是先用a作为驱动表,先后使用 a.L和b.S这两个索引。而实际上explain的结果却是:

分析

从explain的结果看,查询用了b作为驱动表。

上一篇文章我们介绍到,MySQL选择jion顺序是分别分析各种join顺序的代价后,选择最小代价的方法。

这个join只涉及到两个表,自然也与optimizer_search_depth无关。于是我们的问题就是,我们预期的那个join顺序的为什么没有被选中?

MySQL Tips: MySQL提供straight_join语法,强制设定连接顺序。

MySQL Tips: explain结果中,join的查询代价可以用依次连乘rows估算。

&#;join顺序对了,简单的分析查询代价:普通join是*1, straight_join是 *. 貌似MySQL没有错。但一定哪里不对!

发现异常

回到我们最初的设想。我们预计表a作为驱动表,是因为认为表b能够用上IX_S索引,而实际上staight_join的时候确实用上了,但这个结果与我们预期的又不同。

我们知道,索引的过滤性是决定了一个索引在查询中是否会被选中的重要因素,那么是不是b.S的过滤性不好呢?

MySQL Tips: show index from tbname返回结果中Cardinality的值可以表明一个索引的过滤性。

show index的结果太多,也可以从information_schema表中取。

可以这个索引的CARDINALITY: ,已经很大了。那这个表的估算行是多少呢。

从Rows: 看出,IX_S这个索引的区分度被认为非常好,已经近似于唯一索引。

一个优化MySQL查询操作的具体案例分析(mysql如何优化sql查询)

MySQL Tips: 在show table status结果中看到的Rows用于表示表的当前行数。对于MyISAM表这是一个精确值,但对InnoDB这是个估算值。

虽然是估算值,但优化器是以此为指导的,也就是说,上面的某个explain里面的数据完全不符合期望:staight_join结果中第二行的rows。

阶段结论

我们发现整个错误的逻辑是这样的:以a为驱动表的执行计划,由于索引b.S的rows估计为导致优化器认为代价大于以b为驱动表。而实际上这个索引的区分度为1.(当然对explan结果比较熟悉的同学会发现,第二行的type字段和Extra字段一起诡异了)

也就是说,straight_join得到的每一行去b中查询的时候,都走了全表扫描。在MySQL里面出现这种情况的最常见的是类型转换。比如一个字符串字段,虽然包含的是全数字,但查询的时候传入的不是字符串格式。

在这个case里面,两个都是字符串。因此,就是字符集相关了。

回到两个表结构,发现S字段的声明差别在于 COLLATE utf8_bin -- 这个就是本case的根本原因了:a表得到的S值是utf8_bin,优化器认为类型不同,无法直接用上索引b.IX_S过滤。

至于为什么还会用上索引,这个是因为覆盖索引带来“误解”。

MySQL Tips:若查询的所有结果能够从某个索引完全得到,则会优先用遍历索引替代遍历数据。

作为验证,

由于结果是select *, 无法使用覆盖索引,因此第二行的key就显示为NULL. (笔者泪:要是早出这个结果查起来可方便多了)

优化

当然最直接的想法就是修改两个表的S字段的定义,改成相同即可。这个方法可以避免修改业务代码,但DDL代价略大。这里提供两种在SQL语句方面的优化。

这个写法比较直观,需要注意最后b.S和ta.S的顺序

从前面的分析知道是由于b.S定义为utf8_bin.

MySQL Tips: MySQL中字符集命名规则中, XXX_bin与XXX的区别为大小写是否敏感。

这里我们将A.s全部增加binary限定,先转为小写,就是将临时结果集转成utf8_bin,之后使用b.S匹配时就能够直接利用索引。

其实两个改写方法的本质相同,区别是写法1是隐式转换。理论上说写法2速度更快些。

小结

做join的字段尽量设计为类型完全相同。

几个常见的MySQL的可优化点归纳总结 索引相关1.查询(或更新,删除,可以转换为查询)没有用到索引这是最基础的步骤,需要对sql执行explain查看执行计划中是否用到了索引,需要重点关

MySQL中查询的有关英文字母大小写问题的分析 mysql数据库在做查询时候,有时候是英文字母大小写敏感的,有时候又不是的,主要是由mysql的字符校验规则的设置决定的,通常默认是不支持的大小写

分析一个MySQL的异常查询的案例 问题用户工单疑问:相同的语句,只是最后的limit行数不同。奇怪的是,limit的性能比limit的语句还慢约倍。隐藏用户表信息,语句及结果如下SELECTf

标签: mysql如何优化sql查询

本文链接地址:https://www.jiuchutong.com/biancheng/347310.html 转载请保留说明!

上一篇:在MySQL中使用通配符时应该注意的问题

下一篇:几个常见的MySQL的可优化点归纳总结(几个常见的收敛级数)

  • 个体户文化事业建设费征收范围
  • 预提费用的附件怎么做
  • 个税什么情况可以抵扣
  • 公司的纳税信用等级对财务人员有什么影响
  • 企业与政府土地合作开发模式
  • 开发区代管
  • 会计法对填制审核会计凭证有哪些规定
  • 预付账款属于什么账户
  • 社保的计提和缴纳
  • 支出没有发票怎么做账
  • 总公司委托分公司工程委托书模板
  • 委托加工物资的账务处理例题
  • 货物抵扣如何入账
  • 移动电子发票怎么发送到邮箱
  • 耕地占用税和土地使用税的区别
  • 关联业务报告表需要填吗
  • 申请办理银行承兑流程
  • 采购核算成本的核算方法
  • 附加税申报表
  • 代收代付差额征税
  • 无法取得发票的费用如何入账
  • 稽查补缴增值税怎样做账
  • 负债转为投资 资本增加吗
  • 办理企业土地证要多少钱
  • 房地产企业收到房款账务处理
  • 待抵扣进项税期末要结转吗
  • 苗木发票抵扣政策2021
  • 购买展示柜怎么做会计分录
  • 会计基础工作是会计工作的基本环节
  • 如何取消网络拦截
  • 收到对方公司开的电子专票怎么入账
  • linux 查看文件内容 转换字符编码
  • 施工企业自建自用的工程
  • 担保公司的会计分录
  • 增值税留抵退税政策2023
  • 应付账款讲解
  • 长期应收款在哪一章
  • web前端入门教程
  • 模型如何优化
  • 交通费补贴与报销的区别
  • 金税第一次使用怎么用
  • 通用机打发票还能用吗
  • 征地费用包括
  • 应交增值税如何计算,如何进行会计处理
  • 不动产的进项税额转出
  • mysql8绿色版安装
  • 进口关税增值税在哪里打印
  • 新会计准则有哪三个
  • 查看、修改mysql的用户名和密码
  • 一般纳税人开普票和专票有什么区别
  • 法定盈余公积和任意盈余公积可用于
  • 物业临时用工人员管理制度范本
  • 提交免税申请
  • 人工费的发票税率怎么算
  • 事业单位其他特殊人员医疗保障是什么意思
  • 银行借款一年按多少天计算
  • 预收账款最多挂几年
  • 如何设置银行存款日记账
  • win7系统如何彻底删除xp
  • 安全组件异常,请重新下载并安装
  • centos配置yum
  • linux安装有几种方法
  • win7系统玩游戏怎么样
  • Win10 Build 14267截图欣赏:贴心功能大展示
  • ES6 javascript中class静态方法、属性与实例属性用法示例
  • cocos2dx schedule
  • Javascript获取元素的父元素
  • 查看网关的mac地址是多少
  • 行为怪异的人有问题吗
  • 如何获得select选中的值
  • nodejs跳转到指定页面
  • 批处理中数字可以用什么代替
  • 炉石传说用什么语言开发的
  • jQuery插件封装时如要实现链式编程,需要
  • android系统内核基于什么操作系统?
  • 南方电网统一供应链平台
  • 技术合同备案要求
  • 税务和海关哪个单位比较好
  • 注册会计师和注册审计师哪个厉害
  • 2021年福建医保什么时候交
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设