问题描述
有慢SQL如下：
SELECT
vendor.id,
third_vendor_id,
ctrip_id,
vendor_type,
vendor.lat as lat,
vendor.lon as lon,
vendor.create_time,
vendor.update_time,
l.hotel_name as name,
l.hotel_address as address,
l.hotel_seq as seq
FROM vendor
LEFT JOIN hotel_linkage_new l
ON vendor.ctrip_id != 0
AND vendor.ctrip_id = l.extra
AND l.status = 'on'
where vendor.id>18891
ORDER BY vendor.id
LIMIT 1000;
对应执行计划为：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: vendor
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 30095
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: l
type: ref
possible_keys: idx_extra
key: idx_extra
key_len: 8
ref: prmt_raven.vendor.ctrip_id
rows: 1
Extra: Using where
问题分析
测试vendor表单表查询性能(返回数据较快，耗时低于10ms)
SELECT
vendor.id,
third_vendor_id,
ctrip_id,
vendor_type,
vendor.lat as lat,
vendor.lon as lon,
vendor.create_time
FROM vendor
WHERE vendor.id>18891
ORDER BY vendor.id
LIMIT 1000;
1000 rows in set (0.00 sec)
尝试使用子查询来改写SQL：
SELECT
T1.id,
T1.third_vendor_id,
T1.ctrip_id,
T1.vendor_type,
T1.lat as lat,
T1.lon as lon,
T1.create_time,
T1.update_time,
l.hotel_name as name,
l.hotel_address as address,
l.hotel_seq as seq
FROM (
SELECT
vendor.id,
third_vendor_id,
ctrip_id,
vendor_type,
vendor.lat as lat,
vendor.lon as lon,
vendor.create_time,
vendor.update_time
FROM vendor
WHERE vendor.id>18891
ORDER BY vendor.id
LIMIT 1000
) AS T1
LEFT JOIN hotel_linkage_new l
ON T1.ctrip_id != 0
AND T1.ctrip_id = l.extra
AND l.status = 'on'
ORDER BY T1.id
LIMIT 1000
改写后SQL执行性能无明显提升。
查看LEFT JOIN操作关联的数据量：
SELECT
COUNT(1) AS total_rows
FROM (
SELECT
vendor.id,
ctrip_id
FROM vendor
WHERE vendor.id>18891
ORDER BY vendor.id
LIMIT 1000
) AS T1
LEFT JOIN hotel_linkage_new l
ON T1.ctrip_id = l.extra;
+------------+
| total_rows |
+------------+
| 1028824 |
+------------+
查看按照匹配记录较多的ctrip_id：
SELECT
T1.ctrip_id,
COUNT(1) AS total_rows
FROM (
SELECT
vendor.id,
ctrip_id
FROM vendor
WHERE vendor.id>18891
ORDER BY vendor.id
LIMIT 1000
) AS T1
LEFT JOIN hotel_linkage_new l
ON T1.ctrip_id = l.extra
GROUP BY T1.ctrip_id
ORDER BY COUNT(1) DESC
LIMIT 10;
+----------+------------+
| ctrip_id | total_rows |
+----------+------------+
| 0 | 1028223 |
| 968878 | 2 |
| 1978038 | 1 |
| 732266 | 1 |
| 764158 | 1 |
| 445344 | 1 |
| 420028 | 1 |
| 669306 | 1 |
| 437412 | 1 |
| 918312 | 1 |
+----------+------------+
由于ctrip_id=0的记录超过100万，而LEFT JOIN中包含有vendor.ctrip_id != 0 AND vendor.ctrip_id = l.extra，便存在下面两种执行可能(伪代码)：
- 先执行
vendor.ctrip_id != 0再执行vendor.ctrip_id = l.extra:
def get_left_join_rows1():
vendor_rows = get_table_rows_by_primary_index(
'SELECT * FROM vendor WHERE vendor.id>18891 ORDER BY vendor.id LIMIT 1000'
)
left_join_rows = []
for vendor_row in vendor_rows:
if vendor_row.ctrip_id != 0:
linkage_rows = []
index_rows = get_index_rows_by_secondary_index(
'SELECT extra,id FROM hotel_linkage_new WHERE extra={}'.format(vendor_row.ctrip_id)
)
for index_row in index_rows:
linkage_row = get_table_row_by_primary_index(
'SELECT * FROM hotel_linkage_new WHERE id={}'.format(index_row.id)
)
linkage_rows.append(linkage_row)
if len(linkage_rows) > 0:
match_rows = linkage_rows
else:
match_rows = [None]
else:
match_rows = [None]
for match_row in match_rows:
left_join_row = (vendor_row, match_row)
left_join_rows.append(left_join_row)
return left_join_rows
- 先执行
vendor.ctrip_id = l.extra再执行vendor.ctrip_id != 0:
def get_left_join_rows2():
vendor_rows = get_table_rows_by_primary_index(
'SELECT * FROM vendor WHERE vendor.id>18891 ORDER BY vendor.id LIMIT 1000'
)
left_join_rows = []
for vendor_row in vendor_rows:
linkage_rows = []
index_rows = get_index_rows_by_secondary_index(
'SELECT extra,id FROM hotel_linkage_new WHERE extra={}'.format(vendor_row.ctrip_id)
)
for index_row in index_rows:
linkage_row = get_table_row_by_primary_index(
'SELECT * FROM hotel_linkage_new WHERE id={}'.format(index_row.id)
)
linkage_rows.append(linkage_row)
if len(linkage_rows) > 0:
if vendor_row.ctrip_id != 0:
match_rows = linkage_rows
else:
match_rows = [None]
else:
match_rows = [None]
for match_row in match_rows:
left_join_row = (vendor_row, match_row)
left_join_rows.append(left_join_row)
return left_join_rows
虽然上面两种执行方式很相近，但执行性能相差极大：
- 如果先执行
vendor.ctrip_id = l.extra再执行vendor.ctrip_id != 0，则需对hotel_linkage_new表执行l.extra =vendor.ctrip_id = 0`的查询操作，该操作会造成1次索引范围扫描和1028223次主键回表查找，对另外的999条记录产生的999次索引范围扫描和999次主键回表查询。 - 如果先执行
vendor.ctrip_id != 0再执行vendor.ctrip_id = l.extra，则可跳过对hotel_linkage_new表执行l.extra =vendor.ctrip_id = 0`的查询操作，对另外的999条记录产生的999次索引范围扫描和999次主键回表查询。
经过对比跟踪会话统计变量Handler_read_next和Handler_read_key可以断定执行计划采用先执行vendor.ctrip_id = l.extra 再执行vendor.ctrip_id != 0的执行计划，导致查询性能极差。
优化建议
在确认性能问题原因后，我们可以通过修改SQL语句来避免，修改后SQL为：
SELECT * FROM (
SELECT
T1.id,
T1.third_vendor_id,
T1.ctrip_id,
T1.vendor_type,
T1.lat as lat,
T1.lon as lon,
T1.create_time,
T1.update_time,
l.hotel_name as name,
l.hotel_address as address,
l.hotel_seq as seq
FROM (
SELECT
vendor.id,
third_vendor_id,
ctrip_id,
vendor_type,
vendor.lat as lat,
vendor.lon as lon,
vendor.create_time,
vendor.update_time
FROM vendor
WHERE vendor.id>18891
ORDER BY vendor.id
LIMIT 1000
) AS T1
LEFT JOIN hotel_linkage_new l
ON T1.ctrip_id = l.extra
AND l.status = 'on'
WHERE T1.ctrip_id != 0
UNION ALL
SELECT
T1.id,
T1.third_vendor_id,
T1.ctrip_id,
T1.vendor_type,
T1.lat as lat,
T1.lon as lon,
T1.create_time,
T1.update_time,
NULL as name,
NULL as address,
NULL as seq
FROM (
SELECT
vendor.id,
third_vendor_id,
ctrip_id,
vendor_type,
vendor.lat as lat,
vendor.lon as lon,
vendor.create_time,
vendor.update_time
FROM vendor
WHERE vendor.id>18891
ORDER BY vendor.id
LIMIT 1000
) AS T1
WHERE T1.ctrip_id = 0
) AS T2
ORDER BY T2.id
LIMIT 1000