为什么navicat有时候查询时间会有30多秒_MySQL查询性能优化

关注我，更多精彩文章第一时间推送给你(公众号：Java之康庄大道)

Java之康庄大道

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

​ 首先对一个查询的生命周期简化分析，大致可以按照顺序分为：从客户端、到服务器、然后在服务器进行解析、生成执行计划、执行、并返回结果给客户端。其中的执行可以被认为是查询的生命周期中最重要的部分，执行主要包括了大量为了检索数据到存储引擎的调用以及调用后数据的排序、分组等处理。

​ 在完成生命周期的这些任务的时候，查询需要在不用的地方消耗时间，包括网络、CPU计算、生成执行计划、锁等待等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用操作需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间，根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用小号的时间。所有的这些任务消耗的时间共同构成了响应时间，在每一个消耗大量时间的查询操作中，我们都能看到一些不必要的额外操作，某些操作被额外的重复了很多次，某些操作执行太慢等，优化查询的目的就是减少和消除这些额外操作所花费的时间。

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

​ 查询性能低下的最根本原因就是访问的数据太多。虽然某些查询可能不可避免的需要筛选大量的数据，但这并不常见。大部分性能低下的查询都可以通过减少访问数据量的方式进行优化。可以通过以下两步进行分析：

​ 1.确认是否存在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是因为访问了太多的列。

​ 2.确认MySQL服务器层是否存在分析大量超过需要的数据行。

2.1是否向数据库请求了不需要的数据？

• 查询不需要的记录
• 多表关联时返回全部的列
• 总是取出全部列

针对以上三点我这里试举几个典型案例：

​ 1.查询不需要的记录

​ 前端分页就是经典例子，查询实际需求显示第一页10条， 却返回100万条数据，性能低下显而易见，建议后端分页 LIMIT 0, 10

​ 2.多表关联时返回全部的列

​ 假如你想查询所在电影大话西游中出现的演员，千万不能按照如下的方式查询，这将返回这三个表全部的列：

SELECT * FROM actor
    INNER JOIN film_actor USING(actor_id)
    INNER JOIN film USING(film_id)
    WHERE film.title = '大话西游';

​ 正确的做法是：

-- 规定只查询 actor 表的列
SELECT actor.* from actor ...

​ 3.总是取出全部的列

​ 每次看到 SELECT * 的时候都要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能是不必须的，取出全部列，会让优化器无法完成索引扫描这类优化，还会为服务器带来额外的 I/O、内存和CPU消耗。

2.2 MySQL是否在扫描额外的记录？

​ 在确定查询只返回需要的数据后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL而言，最简单的衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

​ 没有哪个指标能够完美的衡量查询的开销，但是他们大致反映了MySQL内部执行查询时候需要访问多少数据，并可以大概推算出查询运行的时间。这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。

下面通过一个例子来查看一下慢日志：

• 存在一个部门表t_dept，包括100万条部门记录，现在查询部门名称为事询部的所有部门

-- 没建立索引的情况下，100万条数据中查询大概0.8秒
SELECT t.id, t.department
    FROM t_dept t
    WHERE t.department = '事询部';

• 首先我将慢日志记录时间设置为0.5秒，然后指定了慢日志位置

-- 将慢日志时间设置为0.5
set long_query_time=0.5;

-- 设置慢日志位置
set global slow_query_log_file='E:mysql_slow_query.log';

• 执行上面的查询操作，之后查看慢日志

• 从慢日志中可以看到上面说到的主要指标：查询事件、返回行数、扫描行数
• 问题来了，返回行数899行，却扫描了整个表100万条数据，当然效率低下了，