MySQL 5.1中小表LEFT JOIN大表查询速度骤降问题求助

API 探路者

2026-8-1

问题

需要给一张约600万行的line表新增字段，但无法修改原表，因此创建了仅含两列的小表line_info：DI_line对应大表主键ID_line，LICLIES为需存储的属性。该小表仅约250行，但LEFT JOIN关联后查询速度慢了约1000倍，添加索引无效，使用MySQL 5.1，求优化建议。

技术信息

表结构

--
-- 表`line`的结构
--

CREATE TABLE IF NOT EXISTS `line` (
  `ID_line` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `LISOCI` char(2) DEFAULT '02',
  `LICLIE` int(11) DEFAULT '0',
  `LIADLIV` tinyint(4) NOT NULL DEFAULT '0',
  `LICMD` int(11) NOT NULL DEFAULT '0',
  `LICMDM` varchar(32) NOT NULL,
  `LILIG` int(11) NOT NULL DEFAULT '0',
  `LIARTI` varchar(8) NOT NULL,
  `LIDES1` varchar(40) NOT NULL,
  `LIDES2` varchar(40) NOT NULL,
  `LIQTE1` float DEFAULT '0',
  `LIQTE2` float DEFAULT '0',
  `LIQTE3` float DEFAULT '0',
  `LIQTE4` float DEFAULT '0',
  `LIQTE5` float DEFAULT '0',
  `LIQTE6` float DEFAULT '0',
  `LIQTE7` float DEFAULT '0',
  `LIQTE8` float DEFAULT '0',
  `LIQTE9` float DEFAULT '0',
  `LIQTE10` float DEFAULT '0',
  `LIQTE11` float DEFAULT '0',
  `LIQTE12` float DEFAULT '0',
  `LICOMMENT` varchar(80) NOT NULL,
  `LICMT1` varchar(80) NOT NULL,
  `LICMT2` varchar(80) NOT NULL,
  `LICMT3` varchar(80) NOT NULL,
  `LICMT4` varchar(80) NOT NULL,
  `LICMT5` varchar(80) NOT NULL,
  `LICMT6` varchar(80) NOT NULL,
  `LICMT7` varchar(80) NOT NULL,
  `LICMT8` varchar(80) NOT NULL,
  `LICMT9` varchar(80) NOT NULL,
  `LICMT10` varchar(80) NOT NULL,
  `LICMT11` varchar(80) NOT NULL,
  `LICMT12` varchar(80) NOT NULL,
  `LIUNIV` char(3) NOT NULL,
  `LILIBQTE` varchar(10) DEFAULT NULL,
  `LIQNIF` float NOT NULL DEFAULT '0',
  `LIDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `LIPRIX` float NOT NULL DEFAULT '0',
  `LIPVCONSO` double NOT NULL DEFAULT '0',
  `LITOTAL` float NOT NULL DEFAULT '0',
  `LIPRV` double NOT NULL DEFAULT '0',
  `LILOGON` varchar(12) NOT NULL,
  `LIADRIP` varchar(16) NOT NULL,
  `LISESSION` varchar(32) NOT NULL,
  `LIGRATUIT` varchar(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (`ID_line`),
  KEY `I1` (`LISOCI`,`LICLIE`,`LICMD`,`LILIG`),
  KEY `I2` (`LISOCI`,`LICLIE`,`LICMD`,`LISESSION`,`LILIG`),
  KEY `L12` (`LICMDM`,`LILIG`),
  KEY `LICMD` (`LICMD`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7185054 ;

--
-- 表`line_info`的结构
--

CREATE TABLE IF NOT EXISTS `line_info` (
  `DI_line` bigint(20) unsigned NOT NULL,
  `LICLIES` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`DI_line`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

表状态信息

`line`表状态

+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| 名称 | 引擎   | 版本    | 行格式     | 行数    | 平均行长度     | 数据长度    | 最大数据长度    | 索引长度     | 空闲数据  | 自增ID         | 创建时间            | 更新时间            | 检查时间            | 排序规则          | 校验和  | 创建选项       | 注释    |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| line | MyISAM |      10 | Dynamic    | 6339101 |            179 |  1135581592 | 281474976710655 |    429571072 |      3356 |        7185054 | 2023-01-27 09:31:29 | 2023-02-06 14:49:08 | 2023-01-27 09:34:49 | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

`line_info`表状态

+-----------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| 名称      | 引擎   | 版本    | 行格式     | 行数 | 平均行长度     | 数据长度    | 最大数据长度     | 索引长度     | 空闲数据  | 自增ID         | 创建时间            | 更新时间            | 检查时间   | 排序规则          | 校验和  | 创建选项       | 注释    |
+-----------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| line_info | MyISAM |      10 | Fixed      |  239 |             13 |        3107 | 3659174697238527 |         6144 |         0 |           NULL | 2023-02-07 09:50:45 | 2023-02-07 09:50:45 | NULL       | latin1_swedish_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)

查询语句及执行计划

待优化查询

EXPLAIN SELECT SQL_NO_CACHE `line`.*, `line_info`.`LICLIES` 
FROM `line` 
LEFT JOIN `line_info` ON DI_line = ID_line 
WHERE (LISOCI LIKE '02') 
AND (LICLIE IN ('44', '55') OR LICLIES IN ('44', '55')) 
AND LICMD = 0 
AND LICMDM = '' 
AND LILOGON = 'WWW' 
AND LIDATE >= '2022-12-02 16:53:56'

执行计划结果

+----+-------------+-----------+--------+-----------------+---------+---------+--------------+-------+-------------+
| id | select_type | table     | type   | possible_keys   | key     | key_len | ref          | rows  | Extra       |
+----+-------------+-----------+--------+-----------------+---------+---------+--------------+-------+-------------+
|  1 | SIMPLE      | line      | ref    | I1,I2,L12,LICMD | L12     | 34      | const        | 56594 | Using where |
|  1 | SIMPLE      | line_info | eq_ref | PRIMARY         | PRIMARY | 8       | line.ID_line |     1 | Using where |
+----+-------------+-----------+--------+-----------------+---------+---------+--------------+-------+-------------+
2 rows in set (0.00 sec)

优化建议

拆分OR条件为UNION ALL查询
原WHERE子句中的OR条件涉及两张表的字段，导致MySQL必须先关联所有行再过滤。拆分后可以分别处理两种情况，减少无效关联：

SELECT SQL_NO_CACHE line.*, line_info.LICLIES 
FROM line 
LEFT JOIN line_info ON line_info.DI_line = line.ID_line 
WHERE LISOCI = '02' 
  AND LICLIE IN ('44', '55') 
  AND LICMD = 0 
  AND LICMDM = '' 
  AND LILOGON = 'WWW' 
  AND LIDATE >= '2022-12-02 16:53:56'
UNION ALL
SELECT SQL_NO_CACHE line.*, line_info.LICLIES 
FROM line 
INNER JOIN line_info ON line_info.DI_line = line.ID_line 
WHERE LISOCI = '02' 
  AND line_info.LICLIES IN ('44', '55') 
  AND LICMD = 0 
  AND LICMDM = '' 
  AND LILOGON = 'WWW' 
  AND LIDATE >= '2022-12-02 16:53:56'
  AND line.LICLIE NOT IN ('44', '55') -- 避免重复数据

创建覆盖索引减少回表
当前查询使用的L12索引只包含LICMDM和LILIG，无法覆盖其他过滤条件。创建包含所有查询条件和关联字段的覆盖索引：
```
CREATE INDEX idx_line_optimize ON line (LICMDM, LICMD, LISOCI, LILOGON, LIDATE, ID_line, LICLIE);
```
该索引可让MySQL直接从索引中获取所需数据，无需读取表数据，大幅提升查询效率。
*避免SELECT ，只查询必要字段
line.*会返回40多个字段，大量数据传输会拖慢查询。明确列出需要的字段，配合覆盖索引可实现索引-only扫描，进一步提速。

用子查询替代JOIN（适用于小表数据量极小的场景）
由于line_info仅250行，可直接提取符合条件的DI_line，用IN条件替代JOIN：

SELECT SQL_NO_CACHE line.*, 
       (SELECT LICLIES FROM line_info WHERE DI_line = line.ID_line) AS LICLIES
FROM line 
WHERE LISOCI = '02' 
  AND (LICLIE IN ('44', '55') OR ID_line IN (SELECT DI_line FROM line_info WHERE LICLIES IN ('44','55'))) 
  AND LICMD = 0 
  AND LICMDM = '' 
  AND LILOGON = 'WWW' 
  AND LIDATE >= '2022-12-02 16:53:56'

强制小表使用内存临时表
在MySQL 5.1中，可将小表查询结果放入内存临时表，减少磁盘IO：

SELECT SQL_NO_CACHE line.*, li.LICLIES 
FROM line 
LEFT JOIN (SELECT * FROM line_info) li ON li.DI_line = line.ID_line 
WHERE LISOCI = '02' 
  AND (LICLIE IN ('44', '55') OR li.LICLIES IN ('44', '55')) 
  AND LICMD = 0 
  AND LICMDM = '' 
  AND LILOGON = 'WWW' 
  AND LIDATE >= '2022-12-02 16:53:56'

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

MySQL 5.1中小表LEFT JOIN大表查询速度骤降问题求助

技术信息 #

表结构 #

表状态信息 #

line表状态

line_info表状态

查询语句及执行计划 #