MySQL条件优化一例

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

把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。

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主从服务器

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二进制安装

--------------------------------------分割线 --------------------------------------