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

SELECTCOUNT(1)FROMrefund_order_itemi,artisana,useruWHEREu.userid=i.user_idANDa.artisan_id=i.artisan_id;

测试查询时间：

mysql>SELECTCOUNT(1)->FROMrefund_order_itemi,artisana,useru->WHEREu.userid=i.user_id->ANDa.artisan_id=i.artisan_id;+----------+|COUNT(1)|+----------+|260605|+----------+1rowinset(2.30sec)

查看执行计划：

mysql>explainSELECTCOUNT(1)->FROMrefund_order_itemi,artisana,useru->WHEREu.userid=i.user_id->ANDa.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|Usingindex||1|SIMPLE|u|NULL|eq_ref|userid|userid|122|hlj.i.user_id|1|100.00|Usingindex|+----+-------------+-------+------------+--------+----------------------------+---------+---------+------------------+--------+----------+-------------+

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

创建联合索引：

ALTERTABLErefund_order_itemADDINDEXidx_aid_uid(artisan_id,user_id);

查看执行计划：

explainSELECTCOUNT(1)FROMrefund_order_itemi,artisana,useruWHEREu.userid=i.user_idANDa.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|Usingindex||1|SIMPLE|a|NULL|eq_ref|PRIMARY|PRIMARY|122|hlj.i.artisan_id|1|100.00|Usingindex||1|SIMPLE|u|NULL|eq_ref|userid|userid|122|hlj.i.user_id|1|100.00|Usingindex|+----+-------------+-------+------------+--------+----------------------------------------+-------------+---------+------------------+--------+----------+-------------+

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

测试查询时间：

mysql>SELECTCOUNT(1)->FROMrefund_order_itemi,artisana,useru->WHEREu.userid=i.user_id->ANDa.artisan_id=i.artisan_id;+----------+|COUNT(1)|+----------+|260605|+----------+1rowinset(1.15sec)