SQL优化之多表关联查询-案例一

慢SQL日志里看到一个三张表的关联查询，如下：

SELECT COUNT(1) 
FROM refund_order_item i, artisan a, user u 
WHERE u.userid = i.user_id
	AND a.artisan_id = i.artisan_id;

测试查询时间：

mysql> SELECT COUNT(1) 
    -> FROM refund_order_item i, artisan a, user u 
    -> WHERE u.userid = i.user_id
    -> AND a.artisan_id = i.artisan_id;
+----------+
| COUNT(1) |
+----------+
|   260605 |
+----------+
1 row in set (2.30 sec)

查看执行计划：

mysql> explain SELECT COUNT(1) 
    -> FROM refund_order_item i, artisan a, user u 
    -> WHERE u.userid = i.user_id
    -> AND a.artisan_id = i.artisan_id;
+----+-------------+-------+------------+--------+----------------------------+---------+---------+------------------+--------+----------+-------------+
| id | select_type       | table   | partitions   | type      | possible_keys        | key        | key_len | ref              | rows            | filtered | Extra       |
+----+-------------+-------+------------+--------+----------------------------+---------+---------+------------------+--------+----------+-------------+
|  1 | SIMPLE      | i      | NULL       | ALL     | idx_user_id,idx_artisan_id       | NULL    | NULL    | NULL             | 255599 |   100.00 | NULL        |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY                               | PRIMARY | 122   | hlj.i.artisan_id |      1     |   100.00 | Using index |
|  1 | SIMPLE      | u     | NULL       | eq_ref | userid                                    | userid    | 122      | hlj.i.user_id    |      1     |   100.00 | Using index |
+----+-------------+-------+------------+--------+----------------------------+---------+---------+------------------+--------+----------+-------------+

可以看到refund_order_item表没有走索引。

创建联合索引：

ALTER TABLE refund_order_item ADD INDEX idx_aid_uid (artisan_id, user_id);

查看执行计划：

explain SELECT COUNT(1)  FROM refund_order_item i, artisan a, user u  WHERE u.userid = i.user_id AND a.artisan_id = i.artisan_id;
+----+-------------+-------+------------+--------+----------------------------------------+-------------+---------+------------------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys                                  | key         | key_len | ref              | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+----------------------------------------+-------------+---------+------------------+--------+----------+-------------+
|  1 | SIMPLE      | i      | NULL       | index  | idx_user_id,idx_artisan_id,idx_aid_uid | idx_aid_uid | 244     | NULL             | 255599 |   100.00 | Using index |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY                                           | PRIMARY     | 122     | hlj.i.artisan_id |      1 |   100.00 | Using index |
|  1 | SIMPLE      | u     | NULL       | eq_ref | userid                                                 | userid      | 122        | hlj.i.user_id    |      1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+----------------------------------------+-------------+---------+------------------+--------+----------+-------------+

可以看到执行计划已经走索引。

测试查询时间：

mysql> SELECT COUNT(1) 
    -> FROM refund_order_item i, artisan a, user u 
    -> WHERE u.userid = i.user_id
    -> AND a.artisan_id = i.artisan_id;
+----------+
| COUNT(1) |
+----------+
|   260605 |
+----------+
1 row in set (1.15 sec)