MySQL条件优化一例-Mysql Tutorial-php.cn

MySQL条件优化一例

WBOY

Release： 2016-06-07 16:43:50

Original

1295 people have browsed it

MySQL条件优化一例，不是任何情况下HAVING条件都可以并入WHERE条件，只有在SQL语句中不存在GROUPBY条件或聚集函数的情况下，才能

有朋友试验如下,并提出问题:

把having条件并入where条件
优点：
便于统一、集中化解条件子句，节约多次化解时间。
注意：
不是任何情况下HAVING条件都可以并入WHERE条件，只有在SQL语句中不存在GROUPBY条件或聚集函数的情况下，才能将HAVING条件与WHERE条件的进行合并。

mysql> explain extended select id,genre from movies where id>10 having genre>1000;
+----+-------------+--------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | movies | ALL | PRIMARY | NULL | NULL | NULL | 107230 | 100.00 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `Portal_19`.`movies`.`id` AS `id`,`Portal_19`.`movies`.`genre` AS `genre` from `Portal_19`.`movies` where (`Portal_19`.`movies`.`id` > 10) having (`Portal_19`.`movies`.`genre` > 1000)
1 row in set (0.00 sec)
mysql> explain extended select id,genre from movies where id>10 and genre>1000;
+----+-------------+--------+------+---------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------+---------------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | movies | ALL | PRIMARY,genre_index | NULL | NULL | NULL | 107230 | 67.72 | Using where |
+----+-------------+--------+------+---------------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `Portal_19`.`movies`.`id` AS `id`,`Portal_19`.`movies`.`genre` AS `genre` from `Portal_19`.`movies` where ((`Portal_19`.`movies`.`id` > 10) and (`Portal_19`.`movies`.`genre` > 1000))
1 row in set (0.00 sec)
二者的执行顺序都是一样的，但优化后filtered值and小于having。
mysql> select id,genre from movies where id>10 having genre>1000;
......
72187 rows in set (0.36 sec)
mysql> select id,genre from movies where id>10 and genre>1000;
......
72187 rows in set (0.37 sec)
优化后的执行时间比没优化时的执行时间还长0.01s。why？（mysql不支持把having条件并入where条件中去）
---
---答复：
---1 这个时间，并不一定能够作为“精准比较”的依据
---2 “精准比较”的方式，至少是多次求均值
---3 方法1：比较从打开表到获取数据全部过程的均值：循环，，每次都执行“flush table movies” ，然后累计每次查询的时间，之后求求均值
---4 方法2：去掉打开表等过程，只求获取数据的过程均值：先执行一次查询，时间不计。然后循环累计每次查询的时间，之后求求均值
---5 方法3：方法1和方法2可以合并。求“单次打开表+多次查询语句循环的时间=总时间”，然后求均值
---6 本质上，优化前后，都是全表扫描，所以不会有差异。但filtered值不同，这应该是个bug。