浅谈数据库查询操作时的顺序
举个例子来解释一下查询时，数据库到底做了哪些操作。
create database TestDB;
CREATE TABLE table1 ( customer_id VARCHAR(10) NOT NULL, city VARCHAR(10) NOT NULL, PRIMARY KEY(customer_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8; CREATE TABLE table2 ( order_id INT NOT NULL auto_increment, customer_id VARCHAR(10), PRIMARY KEY(order_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8;
INSERT INTO table1(customer_id,city) VALUES('163','hangzhou'); INSERT INTO table1(customer_id,city) VALUES('9you','shanghai'); INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou'); INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou'); INSERT INTO table2(customer_id) VALUES('163'); INSERT INTO table2(customer_id) VALUES('163'); INSERT INTO table2(customer_id) VALUES('9you'); INSERT INTO table2(customer_id) VALUES('9you'); INSERT INTO table2(customer_id) VALUES('9you'); INSERT INTO table2(customer_id) VALUES('tx'); INSERT INTO table2(customer_id) VALUES(NULL);
mysql> select * from table1; +-------------+----------+ | customer_id | city | +-------------+----------+ | 163 | hangzhou | | 9you | shanghai | | baidu | hangzhou | | tx | hangzhou | +-------------+----------+ 4 rows in set (0.00 sec) mysql> select * from table2; +----------+-------------+ | order_id | customer_id | +----------+-------------+ | 1 | 163 | | 2 | 163 | | 3 | 9you | | 4 | 9you | | 5 | 9you | | 6 | tx | | 7 | NULL | +----------+-------------+ 7 rows in set (0.00 sec)
SELECT a.customer_id, COUNT(b.order_id) as total_orders FROM table1 AS a LEFT JOIN table2 AS b ON a.customer_id = b.customer_id WHERE a.city = 'hangzhou' GROUP BY a.customer_id HAVING count(b.order_id) < 2 ORDER BY total_orders DESC;
我们得知道这个sql语句做了那些事情。
首先执行的是from，因为我们需要知道最开始从哪个表开始的，这就是FROM告诉我们的。left join的连表操作导致了笛卡尔积的产生所以我们查到的表是这样一个笛卡儿积的表：
+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | 9you | shanghai | 1 | 163 | | baidu | hangzhou | 1 | 163 | | tx | hangzhou | 1 | 163 | | 163 | hangzhou | 2 | 163 | | 9you | shanghai | 2 | 163 | | baidu | hangzhou | 2 | 163 | | tx | hangzhou | 2 | 163 | | 163 | hangzhou | 3 | 9you | | 9you | shanghai | 3 | 9you | | baidu | hangzhou | 3 | 9you | | tx | hangzhou | 3 | 9you | | 163 | hangzhou | 4 | 9you | | 9you | shanghai | 4 | 9you | | baidu | hangzhou | 4 | 9you | | tx | hangzhou | 4 | 9you | | 163 | hangzhou | 5 | 9you | | 9you | shanghai | 5 | 9you | | baidu | hangzhou | 5 | 9you | | tx | hangzhou | 5 | 9you | | 163 | hangzhou | 6 | tx | | 9you | shanghai | 6 | tx | | baidu | hangzhou | 6 | tx | | tx | hangzhou | 6 | tx | | 163 | hangzhou | 7 | NULL | | 9you | shanghai | 7 | NULL | | baidu | hangzhou | 7 | NULL | | tx | hangzhou | 7 | NULL | +-------------+----------+----------+-------------+
接着sql语句执行到on，ON a.customer_id = b.customer_id条件过滤，根据ON中指定的条件，去掉那些不符合条件的数据，得到的临时表就是from查询的表。
+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | 163 | hangzhou | 2 | 163 | | 9you | shanghai | 3 | 9you | | 9you | shanghai | 4 | 9you | | 9you | shanghai | 5 | 9you | | tx | hangzhou | 6 | tx | +-------------+----------+----------+-------------+
接着执行where进行过滤，在使用WHERE子句时，需要注意以下两点：
由于数据还没有分组，因此现在还不能在WHERE中使用where_condition=MIN(col)这类对分组统计的过滤；
由于还没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的，如：SELECT city as c FROM t WHERE c='shanghai';是不允许出现的。
执行group by进行分组，GROU BY子句主要是对使用WHERE子句得到的虚拟表进行分组操作。我们执行测试语句中的GROUP BY a.customer_id，
+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | baidu | hangzhou | NULL | NULL | | tx | hangzhou | 6 | tx | +-------------+----------+----------+-------------+
下一步是进行having过滤函数，HAVING子句主要和GROUP BY子句配合使用，执行测试语句中的HAVING count(b.order_id) < 2时，将得到以下内容：
+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | baidu | hangzhou | NULL | NULL | | tx | hangzhou | 6 | tx | +-------------+----------+----------+-------------+
最后一步执行到select查的动作，其实select虽然写在一开始但是却是最后执行的。select查询上面得到的表进行匹配查找，上面的临时表就是我们几番周折所要查找的。行测试语句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders，得到结果为：
+-------------+--------------+ | customer_id | total_orders | +-------------+--------------+ | baidu | 0 | | tx | 1 | +-------------+--------------+
得到要查询的表以后才会执行order by进行排序，然后返回一个新的虚拟表，我们执行测试SQL语句中的ORDER BY total_orders DESC，就会得到以下内容：
+-------------+--------------+ | customer_id | total_orders | +-------------+--------------+ | tx | 1 | | baidu | 0 | +-------------+--------------+
优先级的底端是limit子句，对于没有应用ORDER BY的LIMIT子句，得到的结果同样是无序的，所以，很多时候，我们都会看到LIMIT子句会和ORDER BY子句一起使用。
注：我们通常用limit语句来解决分页问题。对于小数据，使用LIMIT子句没有任何问题，当数据量非常大的时候，使用LIMIT n, m是非常低效的。因为LIMIT的机制是每次都是从头开始扫描，如果需要从第60万行开始，读取3条数据，就需要先扫描定位到60万行，然后再进行读取，而扫描的过程是一个非常低效的过程。所以，对于大数据处理时，是非常有必要在应用层建立一定的缓存机制（现在的大数据处理，大都使用缓存）。
通过这个示例简单的梳理了一下select查询时的优先级问题。
所以可以简单的看作执行顺序为：
(7) SELECT (8) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) HAVING <having_condition> (9) ORDER BY <order_by_condition> (10) LIMIT <limit_number>
