慢SQL日志里看到一个三张表的关联查询，如下：
成都创新互联公司是一家专业提供
龙南企业网站建设,专注与
成都网站建设、网站设计、
html5、小程序制作等业务。10年已为龙南众多企业、政府机构等服务。创新互联专业的建站公司优惠进行中。
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)
网页名称：SQL优化之多表关联查询-案例一-创新互联
本文来源：
http://cdkjz.cn/article/doijse.html
