MySQL慢查询优化求助：事务报表查询超时问题

尝试运行事务报表时，查询耗时超40秒被服务器超时终止，需要优化帮助。

原始查询语句 #

select 
      test_txn.abcid, 
      txndate,
      txndate2 
   from 
      test_txn 
         inner join test_abc 
            on test_txn.abcid = test_abc.abcid 
         inner join typetest 
            on test_txn.txntypeid = typetest.id 
   WHERE 
      typetest.name = 'DAILY';

表结构信息 #

SHOW CREATE TABLE test_txn;

-- 事务表实际包含1243万+条记录

CREATE TABLE `test_txn` (
   `id` int(11) unsigned NOT NULL DEFAULT 0,
   `txndate` datetime NOT NULL DEFAULT current_timestamp(),
   `txndate2` date DEFAULT NULL,
   `abcid` char(15) DEFAULT NULL,
   `txntypeid` int(11) unsigned DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `txntypeid` (`txntypeid`),
   KEY `abcid` (`abcid`,`txndate`),
   CONSTRAINT `test_txn_ibfk_1` FOREIGN KEY (`txntypeid`) REFERENCES `typetest` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW CREATE TABLE test_abc;
-- 仅包含数百条记录

CREATE TABLE `test_abc` (
   `id` int(11) unsigned NOT NULL DEFAULT 0,
   `abcid` char(15) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `abcid` (`abcid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW CREATE TABLE typetest;
-- 小型查找表，仅20条记录
CREATE TABLE `typetest` (
   `id` int(11) unsigned NOT NULL DEFAULT 0,
   `name` char(20) NOT NULL DEFAULT '',
   `payment` tinyint(1) NOT NULL DEFAULT 0,
   `description` varchar(100) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `txnName` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

慢查询执行计划 #

Analyze select count(*) from test_txn inner join test_abc on test_txn.abcid=test_abc.abcid inner join typetest on test_txn.txntypeid=typetest.id WHERE typetest.name='DAILY';
+------+-------------+----------+------+-----------------+-----------+---------+------------------------+--------+-------------+----------+------------+--------------------------+
| id   | select_type | table    | type | possible_keys   | key       | key_len | ref                    | rows   | r_rows      | filtered | r_filtered | Extra                    |
+------+-------------+----------+------+-----------------+-----------+---------+------------------------+--------+-------------+----------+------------+--------------------------+
|    1 | SIMPLE      | typetest | ref  | PRIMARY,txnName | txnName   | 20      | const                  | 1      | 1.00        |   100.00 |     100.00 | Using where; Using index |
|    1 | SIMPLE      | test_txn | ref  | txntypeid,abcid | txntypeid | 5       | residev.typetest.id    | 386642 | 10969301.00 |   100.00 |     100.00 | Using where              |
|    1 | SIMPLE      | test_abc | ref  | abcid           | abcid     | 16      | residev.test_txn.abcid | 1      | 1.00        |   100.00 |     100.00 | Using index              |
+------+-------------+----------+------+-----------------+-----------+---------+------------------------+--------+-------------+----------+------------+--------------------------+
3 rows in set (49.26 sec)

移除typetest连接后的查询执行计划 #

Analyze select count(*) from test_txn inner join test_abc on test_txn.abcid=test_abc.abcid;
+------+-------------+----------+-------+---------------+-------+---------+------------------------+--------+-----------+----------+------------+--------------------------+
| id   | select_type | table    | type  | possible_keys | key   | key_len | ref                    | rows   | r_rows    | filtered | r_filtered | Extra                    |
+------+-------------+----------+-------+---------------+-------+---------+------------------------+--------+-----------+----------+------------+--------------------------+
|    1 | SIMPLE      | test_abc | index | abcid         | abcid | 16      | NULL                   | 131202 | 131304.00 |   100.00 |     100.00 | Using where; Using index |
|    1 | SIMPLE      | test_txn | ref   | abcid         | abcid | 16      | residev.test_abc.abcid | 66     | 88.23     |   100.00 |     100.00 | Using index              |
+------+-------------+----------+-------+---------------+-------+---------+------------------------+--------+-----------+----------+------------+--------------------------+
2 rows in set (5.60 sec)

仅连接typetest的查询执行计划 #

Analyze select count(*) from test_txn inner join typetest on test_txn.txntypeid=typetest.id WHERE typetest.name='DAILY';
+------+-------------+----------+------+-----------------+-----------+---------+---------------------+--------+-------------+----------+------------+--------------------------+
| id   | select_type | table    | type | possible_keys   | key       | key_len | ref                 | rows   | r_rows      | filtered | r_filtered | Extra                    |
+------+-------------+----------+------+-----------------+-----------+---------+---------------------+--------+-------------+----------+------------+--------------------------+
|    1 | SIMPLE      | typetest | ref  | PRIMARY,txnName | txnName   | 20      | const               | 1      | 1.00        |   100.00 |     100.00 | Using where; Using index |
|    1 | SIMPLE      | test_txn | ref  | txntypeid       | txntypeid | 5       | residev.typetest.id | 386642 | 10969301.00 |   100.00 |     100.00 | Using index              |
+------+-------------+----------+------+-----------------+-----------+---------+---------------------+--------+-------------+----------+------------+--------------------------+
2 rows in set (4.87 sec)

补充统计信息 #

SELECT COUNT(*) from test_txn;
+----------+
| COUNT(*) |
+----------+
| 12430021 |
+----------+
1 row in set (3.70 sec)

SELECT COUNT(*) from test_txn where abcid IS NULL;
+----------+
| COUNT(*) |
+----------+
|   844795 |
+----------+
1 row in set (0.65 sec)

优化建议 #

1. 创建覆盖索引：
   在test_txn上创建复合索引(txntypeid, abcid, txndate, txndate2)，这样查询可以直接通过索引获取所有需要的字段，避免回表操作，大幅减少IO开销。

2. 强制调整关联顺序：
   当前优化器的执行顺序会先扫描近1100万条test_txn记录再关联test_abc，可以用STRAIGHT_JOIN强制先关联test_abc和test_txn，利用小数据集过滤后再关联typetest：

   select 
         test_txn.abcid, 
         txndate,
         txndate2 
      from 
         test_abc
         straight_join test_txn 
            on test_txn.abcid = test_abc.abcid 
         inner join typetest 
            on test_txn.txntypeid = typetest.id 
      WHERE 
         typetest.name = 'DAILY';
   

3. 提前过滤无效数据：
   test_txn中有约84万条abcid为NULL的记录，这些记录在关联test_abc时会被过滤，可在查询中直接排除：

   select 
         test_txn.abcid, 
         txndate,
         txndate2 
      from 
         test_txn 
         inner join test_abc 
            on test_txn.abcid = test_abc.abcid 
         inner join typetest 
            on test_txn.txntypeid = typetest.id 
      WHERE 
         typetest.name = 'DAILY'
         AND test_txn.abcid IS NOT NULL;
   

内容的提问来源于stack exchange，提问作者blackstone