1.DISTINCT关键字
从explain语句来看，加上DISTINCT的结局是mysql会创建临时表来过滤重复行！
mysql> explain SELECT DISTINCT owner FROM pet;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| 1 | SIMPLE | pet | ALL | NULL | NULL | NULL | NULL | 8 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.01 sec)
mysql> explain SELECT owner FROM pet;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | pet | ALL | NULL | NULL | NULL | NULL | 8 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
mysql> explain select value2 from test where value1>0 order by value2;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
| 1 | SIMPLE | test | range | value1 | value1 | 4 | NULL | 1 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
1 row in set
mysql> explain select id from test where value1>0 order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | test | index | value1 | PRIMARY | 4 | NULL | 3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
mysql> explain select * from test where value1>0 order by id;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
| 1 | SIMPLE | test | range | value1 | value1 | 4 | NULL | 1 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
employee数据库中salaries表的建索引速度比较，emp_no和salary列相差八倍之多，总共280w记录左右。
原因是emp_no是单调递增的插入(基本每十个记录使用相同的emp_no，相比salary单调一些)，而salary列插入时则不是单调的，频繁移动的结局就是创建所以速度变慢。
emp_no的选择性为0.1， salary为0.03. 理论上创建salary索引之后，插入速度就会比创建钱慢8倍以上。
mysql> create index emp_no on salaries (emp_no);
Query OK, 2844047 rows affected (37.34 sec)
Records: 2844047 Duplicates: 0 Warnings: 0
mysql> create index salary on salaries (salary);
Query OK, 2844047 rows affected (4 min 3.98 sec)
Records: 2844047 Duplicates: 0 Warnings: 0
salary列上有索引和无索引的速度相差40倍之巨，可能我插入的这条记录需要btree移动的太多了，但也可见索引建在单调的key上是很重要的。选择性太小的列也没必要建
索引，一个是查找时效果不好，另外一个是导致维护索引的成本太高。
有salary单列索引的情况下
mysql> insert into salaries ( emp_no, salary,from_date, to_date) values (20541,
1178,'1992-08-23', '1993-08-23');
Query OK, 1 row affected (2.08 sec)
删除salary上所以之后
mysql> insert into salaries ( emp_no, salary,from_date, to_date) values (20541,6
1178,'1992-08-23', '1993-08-23');
Query OK, 1 row affected (0.06 sec)
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-1-4 16:53:46 |
赞(
操作
