唯一性使用（Exploiting Uniqueness）优化技术,在MySQL中的应用

与朋友讨论唯一性使用（Exploiting Uniqueness）这一优化技术,在MySQL中的应用

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操作。能否被优化，关键是看索引是否被使用。