mysql 语句优化的十个经验

2024-06-18 03:08

本文主要是介绍mysql 语句优化的十个经验,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

mysql 语句优化的十个经验mysql 语句优化的十个经验


本文算是前一篇 查询语句优化经验总结1的后续,总结了<高性能mysql>中与网上常见的一些优化经验中出现的案例进行总结与勘误.但是要注意本文中出现的explain结论都是出现在mysql 5.7.18中,要注意mysql在5.6中出现了大量的升级,以往许多经验都开始失效,需要注意


分解关联查询

分解关联查询是一种极为行之有效的办法优化查询语句.我们使用多条语句分解一条长的关联查询,例如
这里写图片描述
上面的查询中我们通过在应用程序这一层(PHP,Java中)缓存数据,从而实现了拆分关联查询.
这样有这些好处
- 让缓存的效率更高,尤其是应用程序比如PHP这一层
- 减少单个查询可以减少锁的竞争
- 在应用层做关联,可以更容易进行拆分,更容易做到高性能扩展
- 查询效率也有可能得到提升,在这里我们通过in 操作代替了关联查询,让MySQL可以按照ID顺序查询(通常而言会在主键上)
- 在应用层查询,可能会减少荣誉记录的查询

审慎的使用索引

对于查询优化而言,索引永远是最有效也是最直接的利器.但是作为一把双刃剑,请永远谨慎使用它.如果你和我一样是非dba但是需要自己建立库表,请尽量在了解索引的基础上建立索引.毕竟索引时有代价,我的上一篇关于索引的文章就是好一篇不错(我自认为不错的科普).
在某些情况下,如果你发现后某些查询语句很慢(比如开启了慢查询日志),可是使用explain来优化查询,添加使用索引.

关于索引的语句优化常见原则勘误

在网上我们经常看到许多作者写了许多关于语句优化的经验.我个人认为这一类优化经验往往都是很早引擎版本的经验,写作者自己没有写出版本,甚至只是看到别人这么写自己只是抄下来.因此我认为这一类索引优化的语句,在不引擎版本下都会有不同表现,每一次都应该切身实地使用explain判断一下.
现在我对于这一类优化语句进行勘误,当前MySQL 版本mysql 5.7.18
下面的观点都是正确的观点请尽量遵循
- 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
- 尽量避免默认NULL值的出现,否则将导致引擎放弃使用索引而进行全表扫描
这个观点实际上很复杂,我个人尝试is null,在explain中并不会影响索引使用.
但是网上关于他的讨论很多,在<高性能mysql>中也建议尽量不要使用默认NULL,改用其他的默认值(比如 0,空字符串).关于这一块的讨论我给两个连接,感兴趣的朋友可以继续阅读.链接1 链接2
我个人的建议是遵循这个原则

  • 在使用索引时,避免非做匹配情况出现.这一点我在索引的那篇文章中就已经提到.对于字符串的单索引,避免这一类语句的出现
    select id from t where name like ‘%c%’

    对于复合索引,要避免在右边的部分先出现.

  • 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描

错误,在5.7下会使用索引

这里写图片描述

但是处于代码优化的建议我尽量优化表达式,将表达式简化
- 应尽量避免在 where 子句中使用!=或<>操作符,否否则将引擎放弃使用索引而进行全表扫描
错误,在5.7下会使用索引

这里写图片描述


  • in 和 not in 也要慎用,否则会导致全表扫描

错误,在5.7下会使用索引


这里写图片描述

总结一下,对于索引的使用要慎重,在不同版本下的索引由于优化的区别,往往之前的经验可能就不在奏效,这里再一次建议使用explain语句分析查询语句的索引使用情况.在这里推荐一篇关于explain的文章

在只需要一条记录的情况下使用limit 1

我们在前文中提到了使用limit的种种好处,但是要注意这些好处是对于limit没有偏移量的情况,也就是只返回特定的行数的情况下.在添加了偏移量之后引擎依旧会去遍历直到到达偏移量的位置.我会在下文中提到如有优化这一类语句.
通常在框架中会提供get方法来获取一行,我们直接使用它就好.

对于大偏移量limit如何处理

事实上如果有如果有超大表,大数据下本身就是需要进行分表分库来处理数据了.不过有的时候我们可能暂时还是只能用一个表来处理,那么这时候该怎么办?
在<高性能mysql>一书中给出一个很好的解决方法,那就是通过子查询把对应偏移量转换成主键上的条件.
在这里我们通过一个explain 来测试一下首先我们给出一个没有使用子查询的语句
这里写图片描述

我们在前一篇文章中提到了如果不进行优化limit 偏移量会进行全表扫描
这时候我们使用在主键上的子查询来进行替换

这里写图片描述

在这里我们看到使用子查询的优化,重点在于子查询的效率.通过合理使用使用索引技术可以加快这一条子查询的效率

避免使用select *

取出所有的列,会让优化器无法完成索引覆盖这一类优化,同时带来额外的I/O,内存与CPU消耗.因此在许多DBA禁止使用该语句,同时在网上众多文章中也经常提及这一点.
但实际上在返回需要的数据,并不总是坏事.比如很多情况下可以提高代码的可复用性,使用缓存机制的应用程序也有好处.但是在使用诸如select * 时候,请注意我上面提到的代价,谨慎思考这样使用带来的好处时候覆盖了引发的代价

尽量使用count *

对于myisam引擎下,无条件的count * 速度非常快,这是因为数据表会直接保存当前的条数,所以可以直接访问,当然添加了条件后 count * 的速度会很慢
在当前的innodb引擎下,无条件的count * 等价于 count(主键).

这里写图片描述


这里写图片描述

而一旦使用条件查询,请尽量使用在where语句中使用索引条件,以避免退化全表扫描

这里写图片描述

拆分大的 DELETE 或 INSERT 语句

有时候对于一个大的查询我们需要分而治之,每一个查询都完全一样,每次只返回一小部分.
比如我们需要删除某个月的消息,一次性delete 对于服务器而言压力很大,我们可以每次只删除10000条,循环处理,大大降低对于服务器的影响,同事降低删除时锁的持有时间.
另外MySQL虽然没有提供批量插入的函数,但是允许insert同时插入多行,他的效率要远远大于很多次insert.应该尽量避免大量insert到引擎中.

谨慎使用临时表

一方面临时表本身需要资源进行维护,另一方面临时表本身是不具有索引的,即便你的原来的查询表有索引,因此使用临时表会大大拉慢查询.因此使用临时表时候,考虑是否有其他写法进行替换.

谨慎使用order by 进行排序

我之前在索引的文章中提到,对查询进行排序是一个成本是非高的操作.下面给出一组建议,从上到下,建议度依次下降
- 从性能上考虑,应该尽量避免排序,或者尽可能避免对大量数据进行排序.
- 如果不行也请尽量在主键上进行排序,因为主键会使用簇集索引
- 没有主键上排序,也请尽量在索引上排序,如果有多条排序,可以考虑组合索引,但是要注意索引的左匹配原则
- 如果没有索引,引擎,会自己排序,我们称之为文件排序(如果数据小,会在内存中进行,但是大多数情况下依旧会使用磁盘).这涉及到很底层的内容本文不会进行详细描述,感兴趣的朋友可以执行阅读<数据库实现>.总之,即便引擎进行大量优化,文件排序生成的临时存储空间和资源也要比你想象中的大,因此尽量避免这种情况

这篇关于mysql 语句优化的十个经验的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1071137

相关文章

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

HDFS—存储优化(纠删码)

纠删码原理 HDFS 默认情况下,一个文件有3个副本,这样提高了数据的可靠性,但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码,采用计算的方式,可以节省约50%左右的存储空间。 此种方式节约了空间,但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件,都会执行此策略。 默认只开启对 RS-6-3-1024k

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

使用opencv优化图片(画面变清晰)

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化,使其看起来更清晰,同时保持尺寸不变,通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多,主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器:相机传

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

[MySQL表的增删改查-进阶]

🌈个人主页:努力学编程’ ⛅个人推荐: c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构,刷题刻不容缓:点击一起刷题 🌙心灵鸡汤:总有人要赢,为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key: