位置: 编程技术 - 正文

Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE(mysql语句性能优化)

编辑:rootadmin

推荐整理分享Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE(mysql语句性能优化),希望有所帮助,仅作参考,欢迎阅读内容。

文章相关热门搜索词:mysql的性能优化方案有哪些,mysql性能优化方案,mysql性能优化策略,mysql语句性能优化,mysql8性能优化,mysql语句性能优化,mysql高性能优化,mysql5.7性能优化,内容如对您有帮助,希望把文章链接给更多的朋友!

场景

产品中有一张图片表pics,数据量将近万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化

表结构很简单,主要字段:

一个用户会有多条图片记录,现在有一个根据user_id建立的索引:uid,查询语句也很简单:取得某用户的图片集合:优化前

执行查询语句(为了查看真实执行时间,强制不使用缓存,为了防止在测试时因为读取了缓存造成对时间上的差别)执行了次,平均耗时在ms左右

使用explain进行分析:

使用了user_id的索引,并且是const常数查找,表示性能已经很好了

优化后

因为这个语句太简单,sql本身没有什么优化空间,就考虑了索引

修改索引结构,建立一个(user_id,picname,smallimg)的联合索引:uid_pic

重新执行次,平均耗时降到了ms左右

使用explain进行分析

看到使用的索引变成了刚刚建立的联合索引,并且Extra部分显示使用了'Using Index'

总结

‘Using Index'的意思是“覆盖索引”,它是使上面sql性能提升的关键

一个包含查询所需字段的索引称为“覆盖索引”

MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后进行回表操作,减少IO,提高了效率

例如上面的sql,查询条件是user_id,可以使用联合索引,要查询的字段是picname smallimg,这两个字段也在联合索引中,这就实现了“覆盖索引”,可以根据这个联合索引一次性完成查询工作,所以提升了性能。

扩展研究

一、Mysql缓存,SQL_NO_CACHE和SQL_CACHE 的区别

上边在进行测试的时候,为了防止读取缓存造成对实验结果的影响使用到了SQL_NO_CACHE这个功能,对于SQL_NO_CACHE的介绍官网如下:

当我们想用SQL_NO_CACHE来禁止结果缓存时发现结果和我们的预期不一样,查询执行的结果仍然是缓存后的结果。其实,SQL_NO_CACHE的真正作用是禁止缓存查询结果,但并不意味着cache不作为结果返回给query。

在说白点就是,不是本次查询不使用缓存,而是本次查询结果不做为下次查询的缓存。

还有就是,mysql本身是有对sql语句缓存的机制的,合理设置我们的mysql缓存可以降低数据库的io资源,因此,这里我们有必要再看一下如何控制这个比较安逸的功能。

Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE(mysql语句性能优化)

看图如下:

其中各项的含义为:

1、have_query_cache 是否支持查询缓存区 “YES”表是支持查询缓存区

2、query_cache_limit 可缓存的Select查询结果的最大值 byte / = kB 即最大可缓存的select查询结果必须小于 KB

3、query_cache_min_res_unit 每次给query cache结果分配内存的大小 默认是 byte 也即 4kB

4、query_cache_size 如果你希望禁用查询缓存,设置 query_cache_size=0。禁用了查询缓存,将没有明显的开销

5、query_cache_type 查询缓存的方式(默认是 ON)

1、完整查询的过程如下

当查询进行的时候,Mysql把查询结果保存在qurey cache中,但是有时候要保存的结果比较大,超过了query_cache_min_res_unit的值 ,这时候mysql将一边检索结果,一边进行慢慢保存结果,所以,有时候并不是把所有结果全部得到后再进行一次性保存,而是每次分配一块query_cache_min_res_unit 大小的内存空间保存结果集,使用完后,接着再分配一个这样的块,如果还不不够,接着再分配一个块,依此类推,也就是说,有可能在一次查询中,mysql要进行多次内存分配的操作,而我们应该知道,频繁操作内存都是要耗费时间的。

2、内存碎片的产生

当一块分配的内存没有完全使用时,MySQL会把这块内存Trim掉,把没有使用的那部分归还以重复利用。比如,第一次分配4KB,只用了3KB,剩1KB,第二次连续操作,分配4KB,用了2KB,剩2KB,这两次连续操作共剩下的1KB+2KB=3KB,不足以做个一个内存单元分配,这时候,内存碎片便产生了。

3.内存块的概念

先看下这个:

Qcache_total_blocks 表示所有的块

Qcache_free_blocks 表示未使用的块 这个值比较大,那意味着,内存碎片比较多,用flush query cache清理后,为被使用的块其值应该为1或0 ,因为这时候所有的内存都做为一个连续的快在一起了.

Qcache_free_memory 表示查询缓存区现在还有多少的可用内存Qcache_hits 表示查询缓存区的命中个数,也就是直接从查询缓存区作出响应处理的查询个数Qcache_inserts 表示查询缓存区此前总过缓存过多少条查询命令的结果Qcache_lowmem_prunes 表示查询缓存区已满而从其中溢出和删除的查询结果的个数Qcache_not_cached 表示没有进入查询缓存区的查询命令个数Qcache_queries_in_cache 查询缓存区当前缓存着多少条查询命令的结果

优化提示:

如果Qcache_lowmem_prunes 值比较大,表示查询缓存区大小设置太小,需要增大。如果Qcache_free_blocks 较多,表示内存碎片较多,需要清理,flush query cache

关于query_cache_min_res_unit大小的调优,书中给出了一个计算公式,可以供调优设置参考:还要注意一点的是,FLUSH QUERY CACHE 命令可以用来整理查询缓存区的碎片,改善内存使用状况,但不会清理查询缓存区的内容,这个要和RESET QUERY CACHE相区别,不要混淆,后者才是清除查询缓存区中的所有的内容。可以在 SELECT 语句中指定查询缓存的选项,对于那些肯定要实时的从表中获取数据的查询,或者对于那些一天只执行一次的查询,我们都可以指定不进行查询缓存,使用 SQL_NO_CACHE 选项。 对于那些变化不频繁的表,查询操作很固定,我们可以将该查询操作缓存起来,这样每次执行的时候不实际访问表和执行查询,只是从缓存获得结果,可以有效地改善查询的性能,使用 SQL_CACHE 选项。 下面是使用 SQL_NO_CACHE 和 SQL_CACHE 的例子:注意:查询缓存的使用还需要配合相应得服务器参数的设置。

二、覆盖索引(偷懒整理一下,来自百度百科)

理解方式一:就是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。理解方式二:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。理解方式三:是非聚集复合索引的一种形式,它包括在查询里的Select、Join和Where子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段,也即,索引包含了查询正在查找的数据)。

作用:

如果你想要通过索引覆盖select多列,那么需要给需要的列建立一个多列索引,当然如果带查询条件,where条件要求满足最左前缀原则。

Innodb的辅助索引叶子节点包含的是主键列,所以主键一定是被索引覆盖的。

(1)例如,在sakila的inventory表中,有一个组合索引(store_id,film_id),对于只需要访问这两列的查 询,MySQL就可以使用索引,如下:(2)再比如说在文章系统里分页显示的时候,一般的查询是这样的:通常这样的查询会把索引建在created字段(其中id是主键),不过当LIMIT偏移很大时,查询效率仍然很低,改变一下查询:

此时,建立复合索引”created, id”(只要建立created索引就可以吧,Innodb是会在辅助索引里面存储主键值的),就可以在子查询里利用上Covering Index,快速定位id,查询效率嗷嗷的

注:本文是参考《Mysql性能优化案例 - 覆盖索引》 的一篇文章借题发挥,参考了原文的知识点,自己做了一点的发挥和研究,原文被多次转载,不知作者何许人也,也不知出处在哪个,如需原文请自行搜索。

Mysql性能优化案例 - 覆盖索引分享 场景产品中有一张图片表,数据量将近万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化表结构很简单,主要字段:user_id用

简单解决Windows中MySQL的中文乱码与服务启动问题 中文乱码问题当我第一次接触mysql,首先让我难受的是mysql的乱码问题,百度上也有许多有关的解决方案,不过作为亲身受害者,我想很有必要贴出我的

Ubuntu上安装MySQL+问题处理+安全优化 0.说明当然,MySQL的安装方法多种多样,在Ubuntu上,你可以采用apt-get的方式安装,这样的好处是:快速方便。基本上,它会帮你解决所有的函数库依赖问

标签: mysql语句性能优化

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

上一篇:Mysql中 show table status 获取表信息的方法

下一篇:Mysql性能优化案例 - 覆盖索引分享(mysql的性能优化方案有哪些)

  • 一般纳税人企业所得税多久申报一次
  • 法人买社保不领钱可以吗
  • 发票上的收款人复核开票人
  • 发票遗失如何入账
  • 简单的记账方法明细表格怎么做
  • 什么叫备品备件
  • 应交税金应交增值税已交税金怎么结转
  • 小规模纳税人企业所得税怎么申报
  • 母公司为子公司担保是利好还是利空
  • 公司破产后资产怎么处理
  • 软件著作权如何入账
  • 材料暂估价结算调整规定
  • 科目余额表期初借贷一定要相等吗
  • 医疗器械技术服务合同
  • 288000元大写怎么写
  • 不抵扣的发票怎么导入做账系统
  • 股权转让未分配利润如何做账
  • 填写a201030减免所得税优惠明细怎么填
  • 手工发票有效期多久
  • 企业购入交易性金融资产支付的交易费用
  • 个体户生产经营所得怎么报税
  • 软件远程调用失败是什么意思
  • 计提工资大于实际工资
  • paypal授权书怎么填
  • 报关代理费是什么
  • 在建工程如何转为成本费用
  • php bcmath off
  • PHP:pg_set_client_encoding()的用法_PostgreSQL函数
  • 返点收入怎么会计分录
  • windows7简洁版
  • 修建污水处理厂施工方案包括哪些内容
  • php imagecopy
  • HTML布局方式
  • 期末应交增值税转入未交增值税
  • 杭州西湖古风
  • php语言之面向对象编程 educoder
  • 嵌入式从业10年,聊聊我对工业互联网和消费物联网的看法 | 文末赠书4本
  • 华为云从入门到实战电子版
  • 商业企业常用会计科目
  • 现代服务包括哪些税目
  • 只有销售才能使你成功的名言
  • 代发工资怎么做
  • js array.fill
  • java mongodb模糊查询
  • 实收资本认缴怎么做账,要做账吗
  • 标准工资表怎么填
  • 国家税务总局纳税服务规范
  • 可供出售交易性金融资产申报表怎么填
  • 实收资本一定要到账吗
  • 盈余公积的会计科目
  • 小公司没有财务软件怎么手工记账
  • 小微企业所得税怎么算
  • 营业外收支计算公式
  • 异地预缴税款怎么做账
  • 先征后退增值税是否征收企业所得税
  • 发票开错是不是可以作废?
  • 应收帐款坏帐会计分录怎么处理
  • 汇算清缴后多交的企业所得税能退吗
  • 进项发票已认证未抵扣分录
  • 制造费用和生产成本影响营业利润吗
  • windowsxp教程
  • centos 操作
  • win7安装ubuntu20.10
  • win10怎么安装运行库
  • Mtdacq.exe - Mtdacq是什么进程 有什么用
  • Win10 Mobile 10586.63截图曝光:或为正式推送版本
  • android开发环境搭建实验报告总结
  • 服务器自动关机什么原因
  • css渲染规则
  • html头像代码
  • js 获取图片大小
  • 置顶pyq会被自己屏蔽的人看到吗
  • jQuery实现的tab标签切换效果示例
  • Android属性动画缩放
  • linux显示中文
  • 河东区地税局上班时间
  • 郑东新区社保局电话号码是多少
  • 个人意外所得税税率
  • 2020年残保金申报截止到什么时间
  • 地税局开的发票
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

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

    友情链接: 武汉网站建设