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

示例4 语义优化技术中的唯一性使用，MySQL支持。

创建表如下：

CREATE TABLE E (e1 INT, e2 INT UNIQUE,e3 INT, PRIMARY KEY(e1));

插入数据：

INSERT INTO E VALUES(1,1,1);

INSERT INTO E VALUES(2,NULL,NULL);

INSERT INTO E VALUES(3,3,3);

INSERT INTO E VALUES(4,NULL,NULL);

INSERT INTO E VALUES(5,5,5);

分别在列e1和e2上执行DISTINCT操作，查询结果如下：

mysql> SELECT DISTINCT e1 FROM E; //在主键列e1上执行DISTINCT操作

+----+

| e1 |

+----+

|  2 |

|  4 |

|  1 |

|  3 |

|  5 |

+----+

5 rows in set (0.02 sec)

mysql> SELECT DISTINCT e2 FROM E; //在唯一列e2上执行DISTINCT操作

+------+

| e2   |

+------+

| NULL | /* 注意，本行的值为NULL */

|    1 |

|    3 |

|    5 |

+------+

4 rows in set (0.00 sec) /* 注意是四行不是三行 */

 

在有主键的e1列上执行DISTINCT，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT DISTINCT e1 FROM E;

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

| id | select_type | table | type  | key  | key_len | ref  | rows | filtered | Extra       |

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

|  1 | SIMPLE      | E     | index | e2   | 5       | NULL |    5 |   100.00 | Using index |

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

1 row in set, 1 warning (0.00 sec)

从查询执行计划看，在表E上执行索引扫描（type列的值为index）后没有看到再执行去重操作。这表明MyQL利用主键这个特性对DISTINCT进行了优化。注意key列的值为e2，表明使用的是e2列上的唯一索引而不是使用了e1列上的主键索引。

 

在有唯一索引的e2列上执行DISTINCT，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT DISTINCT e2 FROM E;

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

| id | select_type | table | type  | key  | key_len | ref  | rows | filtered | Extra       |

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

|  1 | SIMPLE      | E     | index | e2   | 5       | NULL |    5 |   100.00 | Using index |

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

1 row in set, 1 warning (0.00 sec)

从查询执行计划看，在表E上执行索引扫描（type列的值为index，Extra列的值为Using index）而不是全表扫描，这表明MyQL利用唯一索引这个特性对DISTINCT进行了优化。

 

在普通的e3列上执行DISTINCT，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT DISTINCT e3 FROM E;

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

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

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

|  1 | SIMPLE      | E     | ALL  | NULL | Using temporary |

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

1 row in set, 1 warning (0.00 sec)

从查询执行计划看，在表E上执行全表扫描（type列的值为ALL）且使用了临时文件（Extra列的值为Using temporary），这表明MyQL利用临时文件执行了DISTINCT操作，不存在优化。

 

示例5 语义优化中的连接消除技术，MySQL不支持。

创建表和视图如下：

CREATE TABLE A (a1 INT, a2 INT);

CREATE TABLE B (b1 INT, b2 INT);

CREATE VIEW V AS SELECT * FROM A, B;

INSERT INTO A VALUES(1,1);

INSERT INTO A VALUES(2,2);

INSERT INTO A VALUES(3,3);

对视图进行查询，只查询视图的某个基表的列，查询执行计划如下：

mysql> EXPLAIN SELECT a1, a2 FROM V WHERE a1>2;

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

| id | select_type | table | type | Extra                                              |

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

|  1 | SIMPLE      | b     | ALL  | NULL                                               |

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

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

2 rows in set (0.00 sec)

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

/* select#1 */ select `test`.`a`.`a1` AS `a1`,`test`.`a`.`a2` AS `a2`

from `test`.`a` join `test`.`b`

where (`test`.`a`.`a1` > 2)

从查询执行计划看，MySQL在两个基表A和B上分别进行了全表扫描，并进行了连接，没有消除t2表，所以不支持语义优化中的连接消除。