MySql 索引

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)