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)