【MySQL技术之旅】(7)总结和盘点优化方案系列之常用SQL的优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【MySQL技术之旅】(7)总结和盘点优化方案系列之常用SQL的优化

概述

前面我们介绍了MySQL中怎么样通过索引来优化查询。日常开发中,除了使用查询外,我们还会使用一些其他的常用SQL,比如 INSERT、GROUP BY等。对于这些SQL语句,我们该怎么样进行优化呢?本节将针对这些SQL语句介绍一些优化的方法。

优化INSERT语句

当进行数据INSERT的时候,可以考虑采用以下几种优化方式:

如果同时从同一客户插入很多行,尽量使用多个值表的INSERT语句,这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗,使得效率比分开执行的单个INSERT语句快(在一些情况中几倍)。下面是一次插入多值的一个例子:

sql

复制代码

insert into test values(1,2),(1,3),(1,4)…
  • 如果从不同客户插入很多行,能通过使用INSERT DELAYED语句得到更高的速度。DELAYED的含义是让INSERT语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘,这比每条语句分别插入要快的多;LOW_PRIORITY刚好相反,在所有其他用户对表的读写完后才进行插入;
  • 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项)
  • 如果进行批量插入,可以增加bulk_insert_buffer_size变量值的方法来提高速度。但是,这只能对MyISAM表使用
  • 当从一个文本文件装载一个表时,使用LOAD DATA INFILE这通常比使用很多INSERT语句快20倍

优化GROUP BY语句

默认情况下,MySQL对所有GROUP BY col1,col2....的字段进行排序。这与在查询中指定ORDER BY col1,col2...类似。因此,如果显式包括一个包含相同的列的ORDER BY子句,则对MySQL的实际执行性能没有什么影响如果查询包括GROUP BY,但用户想要避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序,如下面的例子:

sql

复制代码


EXPLAIN SELECT StockType,SUM(StockQty) FROM goods_stock GROUP BY StockType;


sql

复制代码


EXPLAIN SELECT StockType,SUM(StockQty) FROM goods_stock GROUP BY StockType ORDER BY NULL;


从上面的例子可以看出第一个SQL语句需要进行“filesort”,而第二个SQL由于ORDER BY NULL不需要进行“filesort”,而filesort往往非常耗费时间。

优化ORDER BY语句

MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序。WHERE条件和ORDER BY使用相同的索引,并且ORDER BY的顺序和索引顺序相同,并且ORDER BY的字段都是升序或者都是降序

例如下列SQL可以使用索引:

sql

复制代码


EXPLAIN SELECT * FROM goods_stock WHERE Model='LM358' ORDER BY Model,LotNO;


但是在以下几种情况下则不使用索引:

1.order by 后面跟的排序方式不一致

sql

复制代码


EXPLAIN SELECT * FROM goods_stock ORDER BY Model DESC,LotNO ASC;


2.where条件后面跟着的查询条件和order by 排序的条件不一致

sql

复制代码


EXPLAIN SELECT * FROM goods_stock WHERE LotNO=2020 ORDER BY Model;


3.单纯加入order by 不加入where条件做过滤

sql

复制代码


EXPLAIN SELECT * FROM goods_stock ORDER BY Model,LotNO;


优化嵌套查询

MySQL支持SQL子查询。可以使用SELECT语句来创建单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。


使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)替代。在下面的例子中,要从goods_stock表中找到那些在goods_stock_price表中不存在阶梯价格的库存:

sql

复制代码


EXPLAIN SELECT * FROM goods_stock WHERE StockGUID NOT IN (SELECT StockGUID FROM goods_stock_price);



从上面执行计划可以看到goods_stock表是走了全表扫描的,goods_stock、goods_stock_price表查询结果是在内存上创建临时表存储的,如果使用连接(JOIN)来完成这个查询工作,速度将会快很多。尤其是当goods_stock_price表中对 goods_stock.StockGUID建有索引的话,性能将会更好,具体查询如下:

sql

复制代码


EXPLAIN SELECT s.* FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp
ON s.StockGUID=sp.StockGUID
WHERE sp.StockGUID IS NOT NULL;


(此部分可以走内连接,在这不做重复说明了!)


从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。连接(JOIN)之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

MySQL如何优化OR条件


对于含有OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引;如果没有索引,则应该考虑增加索引。例如,首先使用show index命令查看goods_stock表的索引,可知它有3个非聚集索引,在StockGUID、LotNO两个字段上分别有1个独立的索引,在Model和Brand字段上有1个复合索引。

sql

复制代码


SHOW INDEX FROM goods_stock;


然后在两个独立索引上面做OR操作,具体如下:

sql

复制代码


EXPLAIN SELECT * FROM goods_stock WHERE LotNO='2020' OR StockGUID='werer-1weq-hdf1-qgqq';

使用SQL提示

SQL提示(SQL HINT)是优化数据库的一个重要手段,简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。下面是一个使用SQL提示的例子:

sql

复制代码

SELECT SQL_BUFFER_RESULTS * FROM...

这个语句将强制MySQL生成一个临时结果集。只要临时结果集生成后,所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助,因为可以尽快释放锁资源。下面是一些在MySQL中常用的SQL提示。

USE INDEX

在查询语句中表名的后面,添加USE INDEX来提供希望MySQL去参考的索引列表,就可以让MySQL不再考虑其他可用的索引:

sql

复制代码


EXPLAIN SELECT * FROM goods_stock USE INDEX (idx_stock_3) WHERE LotNO='2020';

IGNORE INDEX

如果用户只是单纯地想让MySQL忽略一个或者多个索引,则可以使用IGNORE INDEX作为HINT。同样是上面的例子,这次来看一下查询过程忽略索引idx_stock_3的情况:

sql

复制代码


EXPLAIN SELECT * FROM goods_stock IGNORE INDEX (idx_stock_3) WHERE LotNO='2020';


从执行计划可以看出,系统忽略了指定的索引,而使用了全表扫描。

FORCE INDEX

为强制MySQL使用一个特定的索引,可在查询中使用FORCE INDEX作为HINT。例如,当不强制使用索引的时候,因为goods_stock_price.GoodsStockID(已加索引)的值都是大于0的,因此MySQL会默认进行全表扫描,而不使用索引,如下所示:

sql

复制代码


EXPLAIN SELECT * FROM goods_stock_price WHERE GoodsStockID>0;


但是,当使用FORCE INDEX进行提示时,即便使用索引的效率不是最高,MySQL还是选择使用了索引,这是MySQL留给用户的一个自行选择执行计划的权力。加入FORCE INDEX提示后再次执行上面的SQL:

sql

复制代码

EXPLAIN SELECT * FROM goods_stock_price FORCE INDEX(idx_stock_price_1) WHERE GoodsStockID>0;

果然,执行计划中使用了FORCE INDEX后的索引。

总结

SQL优化问题是数据库性能优化最基础也是最重要的一个问题,实践表明很多数据库性能问题都是由不合适的SQL语句造成。本章通过实例描述了SQL优化的一般过程,从定位一个有性能问题的SQL语句到分析产生性能问题的原因,最后到采取什么措施优化SQL语句的性能。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
SQL 存储 关系型数据库
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
|
3天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
2天前
|
缓存 关系型数据库 MySQL
如何优化MySQL数据库查询性能
MySQL是一款常用的关系型数据库,但在实际使用过程中,由于数据量增加和查询操作复杂度增加,会导致查询性能下降。本文将介绍一些优化MySQL数据库查询性能的方法。
|
2天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之从MySQL到Flink 1.16.2 Flink-SQL的数据同步工作出现了一个异常如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
14 0
|
2天前
|
SQL 存储 数据处理
实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-sql-connector-mysql-cdc有什么区别
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
12 1
|
SQL 存储 索引
SQL优化方案参考
SQL优化方案参考http://www.bieryun.com/3045.html 在sql查询中为了提高查询效率,经常会采取一些措施对查询语句进行sql优化,下面是一些可供参考的方法: 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
1469 0
|
存储 SQL 索引
SQL优化方案参考
SQL优化方案参考 在sql查询中为了提高查询效率,经常会采取一些措施对查询语句进行sql优化,下面是一些可供参考的方法: 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
1173 0
|
1天前
|
SQL 数据处理 API
实时计算 Flink版产品使用合集之遇到SQL Server锁表问题如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
8 0
|
2天前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
17 1
|
2天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
12 0

推荐镜像

更多