慢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)
