SELECT o.orders_id, s.orders_status_name, ot.text ,af.affiliate_id
FROM orders o
LEFT JOIN orders_total ot ON (o.orders_id = ot.orders_id)
LEFT JOIN affiliate_sales AS afs ON afs.affiliate_orders_id = o.orders_id
LEFT JOIN affiliate_affiliate AS af ON af.affiliate_id = afs.affiliate_id
LEFT JOIN orders_status s ON o.orders_status = s.orders_status_id
WHERE s.language_id = '1'
AND (ot.class = 'ot_total' OR ot.orders_total_id IS NULL)
ORDER BY o.orders_id DESC LIMIT 0, 20
有客户反应某后台查询非常慢,通过程序找到对应的sql，如上！
explain发现
+----+-------------+-------+--------+----------------------------+----------------------------+---------+-----------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------+----------------------------+---------+-----------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | s | ALL | PRIMARY | NULL | NULL | NULL | 21 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | o | ref | orders_status | orders_status | 4 | banggood.s.orders_status_id | 31747 | |
| 1 | SIMPLE | ot | ref | idx_orders_total_orders_id | idx_orders_total_orders_id | 4 | banggood.o.orders_id | 19 | Using where |
| 1 | SIMPLE | afs | ref | PRIMARY | PRIMARY | 4 | banggood.o.orders_id | 11 | Using index |
| 1 | SIMPLE | af | eq_ref | PRIMARY | PRIMARY | 4 | banggood.afs.affiliate_id | 1 | Using index |
+----+-------------+-------+--------+----------------------------+----------------------------+---------+-----------------------------+-------+----------------------------------------------+
s表被作为驱动表，s表为全表扫描,o表使用了status类型的可选择性非常低的字段作为索引。
初步一看就知道索引使用不恰当！
我们可以看到这条语句where条件中，没有什么合适的可驱动条件；但是，在order by中，发现order by o.orders_id(orders_id为orders表的主键)。我们就可以利用这个特性！
强制使用orders表的orders_id索引进行驱动！
更改如下：
EXPLAIN SELECT o.orders_id, s.orders_status_name, ot.text ,af.affiliate_id
FROM orders o FORCE INDEX(PRIMARY)
LEFT JOIN orders_total ot ON (o.orders_id = ot.orders_id)
LEFT JOIN affiliate_sales AS afs ON afs.affiliate_orders_id = o.orders_id
LEFT JOIN affiliate_affiliate AS af ON af.affiliate_id = afs.affiliate_id
LEFT JOIN orders_status s ON o.orders_status = s.orders_status_id
WHERE s.language_id = '1'
AND (ot.class = 'ot_total' OR ot.orders_total_id IS NULL)
ORDER BY o.orders_id DESC LIMIT 0, 20;
+----+-------------+-------+--------+----------------------------+----------------------------+---------+--------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------+----------------------------+---------+--------------------------------+------+-------------+
| 1 | SIMPLE | o | index | NULL | PRIMARY | 4 | NULL | 1 | |
| 1 | SIMPLE | s | eq_ref | PRIMARY | PRIMARY | 8 | banggood.o.orders_status,const | 1 | Using where |
| 1 | SIMPLE | ot | ref | idx_orders_total_orders_id | idx_orders_total_orders_id | 4 | banggood.o.orders_id | 19 | Using where |
| 1 | SIMPLE | afs | ref | PRIMARY | PRIMARY | 4 | banggood.o.orders_id | 11 | Using index |
| 1 | SIMPLE | af | eq_ref | PRIMARY | PRIMARY | 4 | banggood.afs.affiliate_id | 1 | Using index |
+----+-------------+-------+--------+----------------------------+----------------------------+---------+--------------------------------+------+-------------+
数据库
发布于：2021-07-04 10:19
|
阅读数：315
|
评论：0
QQ好友和群
QQ空间
