MySQL之查询性能优化四

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL之查询性能优化四，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5085字，纯文字阅读大概需要8分钟。

内容图文

1.关联子查询

mysql的子查询实现得非常糟糕。最糟糕得一类查询是where条件中包含in()的子查询语句。
例如，我们希望找到sakila数据库中，演员Penlope Guiness参演的所有影片信息。
很自然的，我们会按照下面的方式用子查询实现：

   select * from sakila.film
　　where film_id in (
　　　　select film_id from sakila.film_actor where actor_id = 1
　　)

你很容易认为mysql应该由内而外的去执行这个查询，通过子查询中的条件先找出所匹配的
film_id。所以你看你会认为这个查询可能会是这样：

-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

不幸的是，事实恰恰相反。MYSQL想通过外部的关联条件用来快速的筛选子查询，它可能认为
这会让子查询更效率。mysql会这样重写查询：

SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);

这样的话，子查询将会依赖外部表的数据，而不会被优先执行。
mysql将会全表扫描film表，然后循环执行子查询。在外表很小的情况下，
不会有什么问题，但在外表很大的情况下，性能将会非常差。幸运的是，
很容易用关联查询来重写。

mysql> SELECT film.* FROM sakila.film
　　-> INNER JOIN sakila.film_actor USING(film_id)
　　-> WHERE actor_id = 1;

其他的好的优化方法是用group_concat手工生成in()的列表。有时甚至会比JOIN查询
更快。总之，虽然in()子查询在很多情况下工作不佳，但exist()或者其他等价的子查询
有时也工作的不错。

关联子查询性能并不是一直都很差的。

子查询 VS 关联查询

--关联子查询

mysql> explain select film_id, language_id from sakila.film
　　　　where not exsits (
　　　　　　select * from sakila.film_actor
　　　　　　where film_actor.film_id = film.film_id
　　　　)

********************* 1. row ***********************************
id : 1
select_type: PRIMARY
table: film
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 951
Extra: Using where

********************* 2. row ***********************************
id : 2
select_type: Dependent subquery
table: film_actor
type: ref
possible_keys: idx_fx_film_id
key: idx_fx_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where;Using index

--关联查询
mysql> explain select film.film_id, film.language_id from sakila.film

　　　　left outer join sakila.film_actor using(film_id)
　　　　where film_actor.film_id is null

********************* 1. row ***********************************
id : 1
select_type: simple
table: film
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 951
Extra:

********************* 2. row ***********************************
id : 1
select_type: simple
table: film_actor
type: ref
possible_keys: idx_fx_film_id
key: idx_fx_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where;Using index;not exists;


可以看到，这里的执行计划几乎一样，下面是一些细微的差别：
1. 表 film_actor的访问类型一个是Dependent subquery 另一是simple，这对底层存储引擎接口来说，没有任何不同；

2. 对 film表 第二个查询没有using where，但这不重要。using子句和where子句实际上是完全一样的。

3. 第二个表film_actor的执行计划的Extra 有 "Not exists" 这是我们先前提到的提前终止算法，mysql通过not exits优化
来避免在表film_actor的索引中读取任何额外的行。这完全等效于直接使用 not exist ，这个在执行计划中也一样，一旦匹配到一行
数据，就立刻停止扫描


测试结果为：
查询 每秒查询数结果(QRS)
NOT EXISTS 子查询 360
LEFT OUTER JOIN 425
这里显示使用子查询会略慢些。

另一个例子：
不过每个具体地案例会各有不同，有时候子查询写法也会快些。例如，当返回结果只有一个表的某些列的时候。
听起来，这种情况对于关联查询效率也会很好。具体情况具体分析，例如下面的关联，我们希望返回所有包含同一个演员参演的电影
因为电影会有很多演员参演，所以可能返回一些重复的记录。

mysql-> select film.film_id from sakila.film
　　　　 inner join sakila.film_actor using (film_id)

我们需要用distinct 和 group by 来移除重复的记录

mysql-> select distinct film.film_id from sakila.film
　　　　inner join sakila.film_actor using (film_id)

但是,回头看看这个查询，到底这个查询返回的结果意义是什么？至少这样的写法会让sql的意义不明显。
如果是有exists 则很容易表达"包含同一个参演演员"的逻辑。而且不需要使用 distinct 和 Group by，也不会有重复的结果集。
我们知道一旦使用了 distinct 和 group by 那么在查询的执行过程中，通常需要产生临时中间表。

mysql -> select film_id from sakila.film_actor 
　　　　where exists(select * from sakila.film_actor 
　　　　where film.film_id = film_actor.film_id)

测试结果为：
查询 每秒查询数结果(QRS)
INNER JOIN 185
EXISTS 子查询 325
这里显示使用子查询会略快些。

通过上面这个详细的案例，主要想说明两点：
一是不需要听取哪些关于子查询的 "绝对真理"，(即别用使用子查询)
二是应该用测试来验证子查询的执行疾患和响应时间的假设。

MySQL之查询性能优化四

标签：

本文系统来源：http://www.cnblogs.com/w2154/p/4702583.html

内容总结

以上是互联网集市为您收集整理的MySQL之查询性能优化四全部内容，希望文章能够帮你解决MySQL之查询性能优化四所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端