(4)MySQL进阶篇SQL优化(常用SQL的优化)

时间:2022-05-19 07:54:09

1.概述

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

2.优化INSERT语句

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

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倍。

3.优化GROUP BY语句

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

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

(4)MySQL进阶篇SQL优化(常用SQL的优化)

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

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

4.优化ORDER BY语句

在某些情况中,MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序。WHERE条件和ORDER BY使用相同的索引,并且ORDER BY的顺序和索引顺序相同,并且ORDER BY的字段都是升序或者都是降序。例如下列SQL可以使用索引:

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

(4)MySQL进阶篇SQL优化(常用SQL的优化)
但是在以下几种情况下则不使用索引:

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

(4)MySQL进阶篇SQL优化(常用SQL的优化)

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

(4)MySQL进阶篇SQL优化(常用SQL的优化)

EXPLAIN SELECT * FROM goods_stock ORDER BY Model,LotNO;

(4)MySQL进阶篇SQL优化(常用SQL的优化)

5.优化嵌套查询

MySQL支持SQL的子查询。可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)替代。在下面的例子中,要从goods_stock表中找到那些在goods_stock_price表中不存在阶梯价格的库存:

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

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

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;

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

6.MySQL如何优化OR条件

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

SHOW INDEX FROM goods_stock;

(4)MySQL进阶篇SQL优化(常用SQL的优化)
然后在两个独立索引上面做OR操作,具体如下:

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

(4)MySQL进阶篇SQL优化(常用SQL的优化)
可以发现查询正确的用到了索引,并且从执行计划的描述中可以发现MySQL在处理含有OR字句的查询时,实际是对OR的各个字段分别查询后的结果进行了UNION。但是当在建有复合索引的列Model和Brand上面做OR操作的时候,却不能用到索引,具体结果如下:

EXPLAIN SELECT * FROM goods_stock WHERE Model='LM358' OR Brand='2020';

(4)MySQL进阶篇SQL优化(常用SQL的优化)

7.使用SQL提示

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

SELECT SQL_BUFFER_RESULTS * FROM...

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

7.1USE INDEX

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

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

(4)MySQL进阶篇SQL优化(常用SQL的优化)

7.2IGNORE INDEX

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

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

(4)MySQL进阶篇SQL优化(常用SQL的优化)
从执行计划可以看出,系统忽略了指定的索引,而使用了全表扫描。

7.3FORCE INDEX

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

EXPLAIN SELECT * FROM goods_stock_price WHERE GoodsStockID>0;

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

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

(4)MySQL进阶篇SQL优化(常用SQL的优化)
果然,执行计划中使用了FORCE INDEX后的索引。

8.总结

SQL优化问题是数据库性能优化最基础也是最重要的一个问题,实践表明很多数据库性能问题都是由不合适的SQL语句造成。本章通过实例描述了SQL优化的一般过程,从定位一个有性能问题的SQL语句到分析产生性能问题的原因,最后到采取什么措施优化SQL语句的性能。另外还介绍了优化SQL语句经常需要考虑的几个方面,比如索引、表分析、排序等。

参考文献:
深入浅出MySQL大全

(4)MySQL进阶篇SQL优化(常用SQL的优化)的更多相关文章

  1. (6)MySQL进阶篇SQL优化(MyISAM表锁)

    1.MySQL锁概述 锁是计算机协调多个进程或线程并发访问某一资源的机制.在数据库中,除传统的计算资源 (如 CPU.RAM.I/O 等)的抢占以外,数据也是一种供许多用户共享的资源.如何保证数 据并 ...

  2. mysql使用 分区表使用,常用sql

    mysql使用 分区表使用,常用sql 前言 本文的原文连接是: https://blog.csdn.net/freewebsys/article/details/84839478未经博主允许不得转载 ...

  3. 【目录】mysql 进阶篇系列

    随笔分类 - mysql 进阶篇系列 mysql 开发进阶篇系列 55 权限与安全(安全事项 ) 摘要: 一. 操作系统层面安全 对于数据库来说,安全很重要,本章将从操作系统和数据库两个层面对mysq ...

  4. mysql进阶(二十九)常用函数

    mysql进阶(二十九)常用函数 一.数学函数 ABS(x) 返回x的绝对值 BIN(x) 返回x的二进制(OCT返回八进制,HEX返回十六进制) CEILING(x) 返回大于x的最小整数值 EXP ...

  5. mysql补充(2)常用sql语句

    补充:MySQL数据库 详解 常用的Mysql数据库操作语句大全 1.连接Mysql 格式: mysql -h主机地址 -u用户名 -p用户密码 1.连接到本机上的MYSQL.首先打开DOS窗口,然后 ...

  6. MySQL进阶篇(02):索引体系划分,B-Tree结构说明

    本文源码:GitHub·点这里 || GitEE·点这里 一.索引简介 1.基本概念 首先要明确索引是什么:索引是一种数据结构,数据结构是计算机存储.组织数据的方式,是指相互之间存在一种或多种特定关系 ...

  7. MySQL进阶篇(03):合理的使用索引结构和查询

    本文源码:GitHub·点这里 || GitEE·点这里 一.高性能索引 1.查询性能问题 在MySQL使用的过程中,所谓的性能问题,在大部分的场景下都是指查询的性能,导致查询缓慢的根本原因是数据量的 ...

  8. (3)MySQL进阶篇SQL优化(索引)

    1.索引问题 索引是数据库优化中最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数 的SQL性能问题.本章节将对MySQL中的索引的分类.存储.使用方法做详细的介绍. 2.索引的存储分类 ...

  9. (11)MySQL进阶篇SQL优化(InnoDB锁问题排查与解决)

    1.概述 前面章节之所以介绍那么多锁的知识点和示例,其实最终目的就是为了排查与解决死锁的问题,下面我们把之前学过锁知识重温与补充一遍,然后再通过例子演示下如果排查与解决死锁. 2.前期准备 ●数据库事 ...

随机推荐

  1. ASP.NET Core project.json imports 是什么意思?

    示例代码: "frameworks": { "netcoreapp1.0.0": { "imports" : "portable- ...

  2. CROSS APPLY应用实例

    --功能说明:统计每个人的平均分数,新字段[AVG_Score]根据PersonID链接到原表[tbiz_AssScore]上 SELECT [ID] ,[ProjectID] ,[PersonID] ...

  3. jQuery 自带的动画效果

    1.方法: show:显示选中元素. hide:隐藏选中元素. toggle:显示或隐藏选中元素. fadeIn:将选中元素的不透明度逐步提升到100%. fadeOut:将选中元素的不透明度逐步降为 ...

  4. 分布式Hadoop安装(一)

    本文旨在介绍通过两台物理节点,实现完全分布式hadoop程序的部署 writen by Bob Pan@20140609 环境介绍: 主机名 机器IP 用途 描述 Hadoop0 192.168.80 ...

  5. [linux] linux 破解版confluence安装

    OS  centos 6.5  需要的安装包如下: jre-7u67-linux-x64.rpm atlassian-confluence-5.4.4-x64.bin mysql-connector- ...

  6. C#学习笔记(七):智能编译器

    自动实现属性 C#提供的set/get可以让我们很方便的使用属性,但是某些情况下书写还是稍微麻烦了点,如下: public class Test { private string _name; pub ...

  7. codevs1955光纤通信(并查集)

    /* 第一眼以为就是个区间覆盖 然后敲完提交60分0.0 然而觉得自己的做法很对 以为数据错了 后来发现XXX他的牛棚是一圈(牛过得挺好的啊 还能赏湖...) 然后枚举断开的点 可惜n=750 p=1 ...

  8. PV和并发

    几个概念 网站流量是指网站的访问量,用来描述访问网站的用户数量以及用户所浏览的网页数量等指标,常用的统计指标包括网站的独立用户数量.总用户数量(含重复访问者).网页浏览数量.每个用户的页面浏览数量.用 ...

  9. Java ByteArrayOutputStream中buf 的大小增长问题

    问题:写入固定长度的字符串[write(byte b[])],观察ByteArrayOutputStream中buf 的大小始终比字符串 Bytes的Size大很多,很是不解 分析发现: privat ...

  10. oracle事务(转)

    今天温习oracle事务,记录如下: 事务定义            事务是保持数据的一致性,它由相关的DDL或者DML语句做为载体,这组语句执行的结果要么一起成功,要么一起失败.        我们 ...