实际工作中,有时候打开一个页面响应时间非常慢,这背后通常牵涉到SQL语句查询慢的问题。
前面我们提到很多数据库结构设计,建索引等来视图提高MySQL的性能。但是如果我们实际业务场景中,SQL查询语句写的不合适,索引建的再好可能也达不到预期的高性能。
因此,我们很有必要对查询进行分析,我写的查询为什么慢,该怎么样对查询进行优化。
查询慢的原因
一般情况下,查询可以看成按如下顺序执行任务:由客户端向服务端发起查询请求,然后在服务器端进行解析,生成执行计划,执行,最后将结果返回给客户端。
![28e793687af3457a2364b8a600cc5db9.png](https://i-blog.csdnimg.cn/blog_migrate/7f435372efddb46bb3881c84d4aecd1a.png)
在完成上述查询任务的时候,查询需要在不同的地方话费相应的时间,比如网络
,CPU计算
,生成统计信息和执行计划
,锁等待
等操作,尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作,CPU操作和内存不足时导致的IO操作
上消耗时间。
不同的存储引擎,还会产生大量的上下文切换
以及系统调用
。
总之,查询慢的原因无外乎以下几点:
- 网络
- CPU
- IO
- 上下文切换
- 系统调用
- 生成统计信息和执行计划
- 锁等待时间
优化数据访问
查询性能低下的主要原因是访问的数据太多,某些查询不可避免的需要筛选大量的数据,我们可以通过减少访问数据量的方式进行优化。
对于低效查询,我们可以作如下分析:
- 确认应用程序是否在检索大量超过需要的数据。一旦确认查询了大量的数据,说明访问了太多的行,也可能是访问了太多的列。
- 确认MySQL服务器层是否在分析大量超过需要的数据行
是否向数据库请求了不需要的数据
查询不需要的记录
我们常常会误以为MySQL会只返回需要的数据,实际上MySQL却是先返回全部结果再进行计算,在日常的开发习惯中,经常是先用select语句查询大量的结果,然后获取前面的N行后关闭结果集。
优化方式是在查询后面添加limit。
多表关联时返回全部列
还是用sakila
数据库。
现在想查询所有在电影Academy Dinosaur
中出演的演员,涉及actor
,film_actor
以及film
三个表。
上来我就这样写:
select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';
返回了三个表的所有列。
其实人家只是想看所有演员就行了,没必要查出来那么多列,改成这样:
mysql> select actor.* from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
| 20 | LUCILLE | TRACY | 2006-02-15 04:34:33 |
| 30 | SANDRA | PECK | 2006-02-15 04:34:33 |
| 40 | JOHNNY | CAGE | 2006-02-15 04:34:33 |
| 53 | MENA | TEMPLE | 2006-02-15 04:34:33 |
| 108 | WARREN | NOLTE | 2006-02-15 04:34:33 |
| 162 | OPRAH | KILMER | 2006-02-15 04:34:33 |
| 188 | ROCK | DUKAKIS | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
10 rows in set (0.00 sec)
只返回四列就可以了。
用show status like 'last_query_cost';
分别查一下执行时间,第二个查询的时间少于第一种。
总是取出全部列
在公司的实际需求中,禁止使用select *,虽然这种方式能够简化开发,但是会影响查询的性能,所以尽量不要使用。
取出全部列,会让MySQL的优化器无法完成索引覆盖扫描
这类优化,还会为服务器带来额外的IO,内存和CPU消耗。
重复查询相同的数据
如果需要不断的重复执行相同的查询,且每次返回完全相同的数据,基于这样的应用场景,我们可以将这部分数据缓存起来,这样的话能够提高查询效率
执行过程的优化
查询缓存
在解析一个查询语句之前,如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据,如果查询恰好命中了查询缓存,那么会在返回结果之前会检查用户权限,如果权限没有问题,那么MySQL会跳过所有的阶段,就直接从缓存中拿到结果并返回给客户端。
查询缓存这东西MySQL 8版本已经废弃不用了。
缓存
有大名鼎鼎的redis
供我们使用。
查询优化处理
MySQL查询完缓存之后会经过以下几个步骤:解析SQL、预处理、优化SQL执行计划,这几个步骤出现任何的错误,都可能会终止查询。