MySQL查询性能优化详情介绍

黄舟

发布时间：2017-03-15 17:22:10

1325人浏览过

来源于php中文网

原创

对于高性能数据库来说：库表结构优化、索引优化和查询优化需要齐头并进

1.为什么查询的速度会慢？

查询其实就是一系列的子任务组成，优化查询实际上就是：要么消除一些子任务，要么减少子任务执行的次数。

2.慢查询基础：优化数据访问

（1）是否向数据库请求了不需要的数据

1）查询了不需要的数据：
比如我们通过select 查询出了大量的结果，获取前面的N行之后就关闭结果集，实际上MySQL会查询出所有的结果集，客户端接收部分数据后丢弃剩余的数据，这里就存在查询冗余。所以我们只需要查询前面的n条记录就好，利用 limit 关键字限制。

2）多表关联时返回全部的列
我们在进行多表查询时，经常会碰到
mysql>select * from …….
这样的查询其实是非常非常影响性能的，应该用具体的字段名来代替通配符 *

3）总是取出全部的列
禁止写出 select * 这样的语句。

（2）MySQL是否扫描了额外的记录

在确定了查询只返回了需要的数据之后（也就是定制查询的具体字段不要使用通配符 * ）

接下来关注的应该是返回结果是否扫描了过多的数据。对于MySQL最简单的三个指标如下：
（1）响应时间

（2）扫描的行数

（3）返回的行数。

响应时间
响应时间：包括服务时间（真正的查询时间）和排队时间(阻塞等待的时间)。

扫描行数和返回的行数
分析查询时，查看该查询扫描的行数是非常有帮助的，一定程度上说明该查询的效率高不高。

扫描的行数和访问类型
MySQL有好几种访问方式可以查找并返回一行结果：全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。

这里加索引的作用就出来了，索引可以让MySQL以最高效、扫描行数最少的方式找到记录。

3.重构查询的方式

目的就是：找到一个更加优的方法获得实际需要的结果。

（1）一个复杂查询还是多个简单查询
我们在写SQL的时候经常需要考虑的一个问题就是：是否需要将一个复杂的查询分成多个简单的查询？

对于MySQL来说，连接和断开都是非常轻量级的，在返回一个小的查询结果方面很高效。虽然说尽可能少的查询当然好，但是在衡量了工作量是否明显减少之后，将大的查询分解成小的查询有时还是很有必要的。

（2）切分查询
分而治之的思想。有时候我们需要将一个大的查询切分成片，分部分执行，而且分步之间做一个延时，这样避免了长时间的锁住很多的数据。

比如我们在删除数据时 delete，如果一次删除所有需要删除的数据，可能长时间占用事务，但是我们可以分片，将一个大的delete，通过条件限制，分成多个delete执行，这样就能提高效率。

（3）分解关联查询
很多高性能的应用都会对关联查询拆分，比如：

mysql>select * from tag    
left join tag_post on tag_post.tag_id=tag.id    
left join post on tag_post.post_id = post.idwhere tag.tag='mysql';

可以分解成

mysql>select * from tag where tag='mysql';mysql>select * from tag_post where tag_id=1234;
mysql>select * from post where post.id in (123,345,456,8933);

这么分解的原因是什么呢？
（1）让缓存的效率更高；（比如上面查询的tag已经被缓存了，那么应用就可以跳过第一个查询了。）

（2）将查询分解后，执行单个查询可以减少锁的竞争。

iWebShop开源商城系统

iWebShop是一款基于PHP语言及MYSQL数据库开发的B2B2C多用户开源免费的商城系统，系统支持自营和多商家入驻、集成微信商城、手机商城、移动端APP商城、三级分销、视频电商直播、微信小程序等于一体，它可以承载大数据量且性能优良，还可以跨平台，界面美观功能丰富是电商建站首选源码。iWebShop开源商城系统 v5.14 更新日志：新增商品编辑页面规格图片上传优化商品详情页面规格图片与主图切

下载

（3）某些情况下效率也会更高，比如上面的分解后用 in 关键字查询，效率更高。

4.查询执行的基础

首先来看看查询执行的路径的示意图：
这里写图片描述

步骤如下：
（1）客户端发送一条查询给服务器；

（2）服务器先检查查询缓存，如果命中了缓存，则立刻返回存在缓存中的结果，否则进入下一步。

（3）服务器对SQL进行解析、预处理、再由优化器生成对应的执行计划。

（4）MySQL会根据优化器生成的执行计划、调用存储引擎的API来执行查询。

（5）将结果返回给客户端。

（1）MySQL客户端/服务器通信协议

我们不需要了解通信协议内部是如何实现的，只需理解通信协议是如何工作的。

MySQL的客户端和服务器通信协议是半双工的，意味着同一时刻，只能有一方向另一方发送数据。

（2）查询缓存

在解析一个SQL语句之前，如果缓存是打开的，MySQL会优先检查这个查询是否命中查询缓存中的数据。如果命中了缓存就会直接从缓存中拿到结果集并返回给客户端。如果没有命中缓存就会进入下一阶段。

（3）查询优化器

在这一部分最重要的就是查询优化器了，一条查询语句可以有很多种执行方式，最后都将返回相同的结果，优化器的作用就是找到最高效的执行计划。

下面给出MySQL查询优化器能够自动处理的优化类型：
（1）重新定义关联表的顺序：数据表的关联顺序并不总是按照在查询中指定的顺序进行，这个与优化器有关。

（2）将外连接转换成内连接：

（3）使用等价变换规则：可以减少一些比较或则移除一些恒等的判断。比如（5=5 and a>5）将被改写成（a > 5）。

（4）优化 COUNT()、 MIN() 和 MAX() 函数：索引和列是否允许为空可以帮助优化这类表达式：比如求最小值，利用B-Tree结构特点，只需要查询B-Tree的最左端记录就OK了。同理对于求max()函数也是一样。但是对于COUNT(*)这个函数，MyISAM存储类型维护了一个变量来专门存储表中记录行的总数。

（5）覆盖索引扫描：当索引中的列包含所有查询中需要使用的列时候，MySQL可以直接使用索引返回需要的数据，无需再查询对应的数据行。

（6）子查询优化

（8）提前终止查询：在发现已经满足查询需求的时候，MySQL总是能够立即终止查询。比如 limit 关键字。

（9）列表 IN 的比较代替OR：MySQL会先将IN语句中的数据排序，再通过二分查找来确定列表中的数据是否满足需求，这是一个O(logn)的复杂度的操作。如果等价转换成 OR 就会变成O(n)的时间复杂度。