MySQL查询优化器--语义优化（二）

本文链接：https://blog.csdn.net/fly2nn/article/details/61924411

本文探讨MySQL中索引和排序优化的技术细节，包括谓词引入技术的应用限制及语义优化中的排序优化条件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

示例2 语义优化中的谓词引入技术，MySQL不支持。

创建表如下，列c2有唯一索引存在，并创建CHECK约束：

CREATE TABLE C (c1 INT, c2 INT UNIQUE, CHECK(c1<c2));

在c1列上进行条件查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM C WHERE c1>60;

+----+-------------+-------+------+------+---------+------+------+-------------+

+----+-------------+-------+------+------+---------+------+------+-------------+

| 1 | SIMPLE | C | ALL | NULL | NULL | NULL | 1 | Using where |

+----+-------------+-------+------+------+---------+------+------+-------------+

1 row in set, 1 warning (0.00 sec)

从查询执行计划看，WHERE条件的子句“c1>60”作为全表扫描（type列的值为ALL）的过滤器的条件。

利用约束的定义，改造SQL语句中的条件，引入c2列，使得语义不发生变化，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM C WHERE c1>60 and c2>60;

+----+-------------+-------+-------+------+------------------------------------+

+----+-------------+-------+-------+------+------------------------------------+

| 1 | SIMPLE | C | range | c2 | Using index condition; Using where |

+----+-------------+-------+-------+------+------------------------------------+

1 row in set, 1 warning (0.00 sec)

从查询执行计划看，在列c2上利用了索引条件进行了范围扫描（range）。对比前一条SQL语句，说明MySQL的查询优化器不能主动利用约束条件，然后进行“谓词引入”优化。

示例3 语义优化中的排序优化，MySQL支持，但条件较为苛刻。

创建表如下：

CREATE TABLE D (d1 INT, d2 INT UNIQUE);

对D进行自连接，连接条件使用有唯一索引的列，且连接条件的列与排序列相同。查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM D F1, D F2 WHERE F1.d2=F2.d2 ORDER BY F1.d2;

+----+-------------+-------+------+------+-----------+-----------------------------+

| id | select_type | table | type | key | ref | Extra |

+----+-------------+-------+------+------+-----------+-----------------------------+

| 1 | SIMPLE | F1 | ALL | NULL | NULL | Using where; Using filesort |

| 1 | SIMPLE | F2 | ref | d2 | tt2.F1.d2 | NULL |

+----+-------------+-------+------+------+-----------+-----------------------------+

2 rows in set, 1 warning (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`f1`.`d1` AS `d1`,`test`.`f1`.`d2` AS `d2`,

`test`.`f2`.`d1` AS `d1`,`test`.`f2`.`d2` AS `d2`

from `test`.`d` `f1` join `test`.`d` `f2`

where (`test`.`f2`.`d2` = `test`.`f1`.`d2`)

order by `test`.`f1`.`d2`

从查询执行计划和被查询优化器处理后的语句看，F1、F2先连接后排序，排序操作没有优先于连接操作。MySQL没有利用d2列上的索引对排序进行优化。

对D进行自连接，连接条件的列使用普通列，且与排序列相同。查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM D F1, D F2 WHERE F1.d1=F2.d1 ORDER BY F1.d1;

+----+-------------+-------+------+------+----------------------------------------------------+

+----+-------------+-------+------+------+----------------------------------------------------+

| 1 | SIMPLE | F1 | ALL | NULL | Using temporary; Using filesort |

| 1 | SIMPLE | F2 | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+-------+------+------+----------------------------------------------------+

2 rows in set, 1 warning (0.09 sec)

从查询执行计划看，表F1、表F2连接，然后利用临时文件（Using temporary）进行了排序操作（Using filesort）。

对D进行自连接，排序使用普通列，连接条件的列与排序列不完全相同。查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM D F1, D F2 WHERE F1.d1=F2.d2 ORDER BY F1.d1;

+----+-------------+-------+------+------+-----------------------------+

+----+-------------+-------+------+------+-----------------------------+

| 1 | SIMPLE | F1 | ALL | NULL | Using where; Using filesort |

| 1 | SIMPLE | F2 | ref | d2 | NULL |

+----+-------------+-------+------+------+-----------------------------+

2 rows in set, 1 warning (0.00 sec)

从查询执行计划看，表F1、表F2连接后进行了排序操作（Using filesort）。但是MySQL没有利用d2列上的索引对排序进行优化。

再看如下两条SQL语句，只在单表的索引列上进行排序查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM D ORDER BY d2;

+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+

+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+

| 1 | SIMPLE | D | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using filesort |

+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+

1 row in set, 1 warning (0.00 sec)

从查询执行计划看，type列的值为ALL，表示对表进行全表扫描；Extra列的值为“Using filesort”，表示进行了排序。所以没有利用唯一索引进行排序操作。

再看查询语句的目标列为索引列的情况，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT d2 FROM D ORDER BY d2;

+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | D | index | NULL | d2 | 5 | NULL | 1 | 100.00 | Using index |

+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

从查询执行计划看，type列的值为index表示对表进行索引扫描，Extra列的值为“Using index”表示利用了索引。所以MySQL利用索引优化了排序操作。对比本条SQL和上一条SQL的差异，在于目标列不同，本条SQL语句只对索引列进行查询，才使得MySQL利用索引扫描优化排序操作。所以，MySQL对于利用索引优化排序操作支持，但条件较为苛刻。