SQL优化:一

概述

本系列专为SQL优化系列，包括但不限于db2、MySQL、OceanBase等数据库。将会不定时更新。
该SQL为MySQL等慢查询SQL，SQL文本如下：

SELECT qrc.*
FROM (
	SELECT qrc.*, c.BRNAME AS mcht_org, b.MCHT_AMR_NO, b.REMARK, b.MCHT_ORG_ID
		, d.chl_no, d.chl_name, b.MCHT_REG_TYPE
	FROM (
		SELECT qrc.QRC_CODE_ID, qrc.BATCH_NUMBER, qrc.MCHT_ID, qrc.MCHT_NAME, qrc.MCHT_SIMPLE_NAME
			, qrc.CRT_DATE_TIME, qrc.QRC_PIC_ID, qrc.BIN_DING_TIME
			, if(qrc.MCHT_ID IS NULL, '00', '01') AS BIN_DING_STATE
			, if(qrc.QRC_KIND = '01', '01', '02') AS YUZHIMA
			, qrc.QR_CODE, qrc.QRC_STAT, qrc.resp_Txn_Ssn, qrc.resp_Txn_Time, qrc.union_code
			, qrc.union_simple_code, qrc.union_PIC_ID
			, if(qrc.union_code IS NULL, '00', '01') AS TWO_OR_THREE_FLAG
		FROM PBS_MCHT_QRC_BASE_INFO qrc
	) qrc
		LEFT JOIN pbs_mcht_base_info b ON qrc.mcht_id = b.mcht_id
		LEFT JOIN ifs_org c ON b.MCHT_ORG_ID = c.BRCODE
		LEFT JOIN pbs_chl_base_info d ON d.chl_no = b.MCHT_CHL_NO
	WHERE qrc.QRC_STAT = '1'
		AND qrc.MCHT_ID IS NULL
	UNION
	SELECT qrc.*, c.BRNAME AS mcht_org, b.MCHT_AMR_NO, b.REMARK, b.MCHT_ORG_ID
		, d.chl_no, d.chl_name, b.MCHT_REG_TYPE
	FROM (
		SELECT qrc.QRC_CODE_ID, qrc.BATCH_NUMBER, qrc.MCHT_ID, qrc.MCHT_NAME, qrc.MCHT_SIMPLE_NAME
			, qrc.CRT_DATE_TIME, qrc.QRC_PIC_ID, qrc.BIN_DING_TIME
			, if(qrc.MCHT_ID IS NULL, '00', '01') AS BIN_DING_STATE
			, if(qrc.QRC_KIND = '01', '01', '02') AS YUZHIMA
			, qrc.QR_CODE, qrc.QRC_STAT, qrc.resp_Txn_Ssn, qrc.resp_Txn_Time, qrc.union_code
			, qrc.union_simple_code, qrc.union_PIC_ID
			, if(qrc.union_code IS NULL, '00', '01') AS TWO_OR_THREE_FLAG
		FROM PBS_MCHT_QRC_BASE_INFO qrc
	) qrc
		LEFT JOIN pbs_mcht_base_info b ON qrc.mcht_id = b.mcht_id
		LEFT JOIN ifs_org c ON b.MCHT_ORG_ID = c.BRCODE
		LEFT JOIN pbs_chl_base_info d ON d.chl_no = b.MCHT_CHL_NO
	WHERE qrc.QRC_STAT = '1'
		AND qrc.MCHT_ID IS NOT NULL
		AND b.MCHT_ORG_ID LIKE '8%'
) qrc
WHERE 1 = 1
	AND qrc.BIN_DING_STATE = '00'
ORDER BY qrc.QRC_CODE_ID DESC

表索引信息：

mysql> show index from PBS_MCHT_QRC_BASE_INFO;
+------------------------+------------+--------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                  | Non_unique | Key_name                             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+--------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| pbs_mcht_qrc_base_info |          0 | PRIMARY                              |            1 | QRC_CODE_ID | A         |      274660 |     NULL | NULL   |      | BTREE      |         |               |
| pbs_mcht_qrc_base_info |          1 | pbs_mcht_qrc_base_info_MCHT_ID_index |            1 | MCHT_ID     | A         |      261351 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------------+------------+--------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)


mysql> show index from PBS_MCHT_BASE_INFO;
+--------------------+------------+---------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name                              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+---------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| pbs_mcht_base_info |          0 | PRIMARY                               |            1 | MCHT_ID      | A         |      261157 |     NULL | NULL   |      | BTREE      |         |               |
| pbs_mcht_base_info |          1 | pbs_mcht_base_info_PAGY_MCHT_ID_index |            1 | PAGY_MCHT_ID | A         |      229032 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+---------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

mysql> show index from ifs_org;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ifs_org |          0 | PRIMARY  |            1 | BRCODE      | A         |         237 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from pbs_chl_base_info;
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| pbs_chl_base_info |          0 | PRIMARY  |            1 | CHL_NO      | A         |          32 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

执行计划：

分析过程

可以看到，性能点就是同个JOIN，要UNION两次，也就是说要查两次才能得到结果。再看执行计划，产生了临时驱动表，而且其他表都有回表操作，且都走了主键。
这里的临时表很明显是由于UNION语句产生的，如果是其他语句，那么可能要看optimizer_trace才能定位到到底是哪里产生了临时表，然后进行针对性优化，这个以后讲到。
走主键势必会有一个问题：在MySQL中，主键代表了全表的数据，也就是说，对比一行数据，要从原表中读取一页才能找到相应的数据，所以在优化时要注意观察走了主键的执行计划。
MySQL读取数据是以页为单位读取的，理想状态下，应该是建立单个或复合索引。
这条SQL本身比较简单，所以不做参数和优化器参数的调整等，只针对SQL本身做优化处理。
观察到这条SQL里有两个地方是重复的，可以考虑是否能进行合并，且合并后不改变原来的SQL逻辑和数据结果。
上下两部分有一个地方不一样，就是b.MCHT_ORG_ID LIKE '8%'这个条件，所以尝试改写，改写如下：

改写后SQL

SELECT qrc.*
FROM (
	SELECT qrc.*, c.BRNAME AS mcht_org, b.MCHT_AMR_NO, b.REMARK, b.MCHT_ORG_ID
		, d.chl_no, d.chl_name, b.MCHT_REG_TYPE
	FROM (
		SELECT qrc.QRC_CODE_ID, qrc.BATCH_NUMBER, qrc.MCHT_ID, qrc.MCHT_NAME, qrc.MCHT_SIMPLE_NAME
			, qrc.CRT_DATE_TIME, qrc.QRC_PIC_ID, qrc.BIN_DING_TIME
			, IF(qrc.MCHT_ID IS NULL, '00', '01') AS BIN_DING_STATE
			, IF(qrc.QRC_KIND = '01', '01', '02') AS YUZHIMA
			, qrc.QR_CODE, qrc.QRC_STAT, qrc.resp_Txn_Ssn, qrc.resp_Txn_Time, qrc.union_code
			, qrc.union_simple_code, qrc.union_PIC_ID
			, IF(qrc.union_code IS NULL, '00', '01') AS TWO_OR_THREE_FLAG
		FROM PBS_MCHT_QRC_BASE_INFO qrc
	) qrc
		LEFT JOIN pbs_mcht_base_info b ON qrc.mcht_id = b.mcht_id
		LEFT JOIN ifs_org c ON b.MCHT_ORG_ID = c.BRCODE
		LEFT JOIN pbs_chl_base_info d ON d.chl_no = b.MCHT_CHL_NO
	WHERE (qrc.QRC_STAT = '1'
			AND qrc.MCHT_ID IS NULL)
		OR b.MCHT_ORG_ID LIKE '1%'
		AND qrc.MCHT_ID IS NOT NULL
		AND b.MCHT_ORG_ID LIKE '8%'
) qrc
WHERE qrc.BIN_DING_STATE = '00'
ORDER BY qrc.QRC_CODE_ID DESC

改写后执行计划

改写后效果

从原来执行时间10秒钟下降至1秒