1 如下是朋友的思考和实验
2 带有"--->"格式的内容是我的回答
3 期望能对有同样疑惑的朋友有帮助
----------------------------------------------------
1.讲解:
唯一性使用(Exploiting Uniqueness):
利用唯一性、索引等特点,检查是否存在不必要的DISTINCT操作.(DISTINCT=排序+去重复,所以性能不好)
例如:
在主键上执行DISTINCT操作,若有则可以把DISTINCT消除掉。
语义优化技术中的唯一性使用,MySQL支持。
2.我的疑问:
老师您在讲义中提到 MySQL支持,语义优化技术中的唯一性使用。我的理解是它支持在这种情况下可以把 DISTINCT 消除掉。
但是我经过实验发现,经过优化器优化后的语句里仍然看到了DISTINCT关键字,即并没有消除掉DISTINCT。这是为什么呢?是不是我理解错了,请老师解惑,谢谢。
下面是我的实验:
-- 我的实验
-- ---------------------------------------------------------------------------------------------------
-- 唯一索引
ALTER TABLE t_1 ADD UNIQUE INDEX uix_t_1 (t_1_id) USING BTREE ;
mysql> explain extended select distinct t_1_id from t_1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_1 | index | uix_t_1 | uix_t_1 | 5 | NULL | 96 | 100 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select distinct `zsm`.`t_1`.`t_1_id` AS `t_1_id` from `zsm`.`t_1` |
+-------+------+----------------------------------------------------------------------------------+
1 row in set
-- 结论:有唯一索引,使用唯一索引。但是优化器优化后的语句里仍然看到了DISTINCT关键字,即并没有消除掉DISTINCT。
-- 非唯一索引
ALTER TABLE t_1
DROP INDEX uix_t_1 ,
ADD INDEX ix_t_1 (t_1_id) USING BTREE ;
mysql> explain extended select distinct t_1_id from t_1;
+----+-------------+-------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_1 | index | ix_t_1 | ix_t_1 | 5 | NULL | 96 | 100 | Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+------+----------+-------------+
1 row in set
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select distinct `zsm`.`t_1`.`t_1_id` AS `t_1_id` from `zsm`.`t_1` |
+-------+------+----------------------------------------------------------------------------------+
1 row in set
mysql>
-- 结论:有非唯一索引,使用非唯一索引。
--->如果存在索引可用,ix_t_1列显示的值存在问题,一个bug
-- 没有索引
ALTER TABLE `t_1`
DROP INDEX `ix_t_1`;
mysql> explain extended select distinct t_1_id from t_1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | t_1 | ALL | NULL | NULL | NULL | NULL | 96 | 100 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select distinct `zsm`.`t_1`.`t_1_id` AS `t_1_id` from `zsm`.`t_1` |
+-------+------+----------------------------------------------------------------------------------+
1 row in set
mysql>
-- 结论:没有索引,使用临时表排序了。性能最差。
那海蓝蓝回答:
-------------
--->建议参看《数据库查询优化器的艺术》一书404页到406页,注意把相关示例结合起来综合比较,可以发现不同,更容易明白这点。
--->书中最后一个例子说明,如果不能对distinct操作优化,则没有索引被使用,且“extra”列会出现“Using temporary”,表明使用临时表完成排序和去重操作。
--->1 你提到了存在索引的时候,对索引的利用和显示(执行计划中的显示)的问题,这里有1个细小问题:
--->"key"列队索引的显示,是存在小bug的。从《数据库查询优化器的艺术》一书404页到406页的“示例4”我们所举的例子可以看出,同时存在多个索引,如e1和e2列上都有索引,但在e1列上的distinct操作,查询执行计划显示的却是"key"列值却是“e2”
--->2 你提到"show warnings"的结果中存在distinct,这是显示的问题,只是表明SQL的语义中存在distinct操作,不表明优化后存在distinct操作。能否被优化,关键是看索引是否被使用。