数据库范式及MYSQL优化整体思路

　　2.2、慎用子查询
　　几乎所有子查询都可以改写为连接查询，有时候，连接查询的效率要比子查询高，所以把子查询改写成连接查询是一个不错的注意。如果一条使用子查询的select语句执行时间过长，那么应该尝试把它改写为连接查询，看他是不是执行的更好。
　　下例中，我们使用子查询耗时5s，清空缓存后，改用连接查询，只需要0.02s：
//子查询耗时5s
mysql> select * from emp where ename in (select ename from ename);
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
|      2 | AsoqNR | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    466 |
|      3 | cAuvTj | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    155 |
|      6 | oOeekL | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |     13 |
|      9 | MFPixN | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    225 |
| 103219 | MfpiXn | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    251 |
| 318098 | mFpIxn | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    480 |
| 333225 | ASOqnr | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    466 |
| 443919 | AsoqNR | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    446 |
| 458077 | OoeEKL | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    266 |
| 473649 | AsoqNR | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    448 |
| 769138 | CAUVTJ | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    252 |
| 826307 | MFPixN | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    242 |
+--------+--------+----------+-----+------------+---------+--------+--------+
12 rows in set (5.04 sec)
//清空缓存
reset query cache;
//连接查询耗时0.02s
mysql> select emp.* from emp inner join ename on emp.ename=ename.ename;
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
|      6 | oOeekL | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |     13 |
| 458077 | OoeEKL | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    266 |
|      9 | MFPixN | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    225 |
| 103219 | MfpiXn | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    251 |
| 318098 | mFpIxn | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    480 |
| 826307 | MFPixN | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    242 |
|      3 | cAuvTj | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    155 |
| 769138 | CAUVTJ | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    252 |
|      2 | AsoqNR | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    466 |
| 333225 | ASOqnr | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    466 |
| 443919 | AsoqNR | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    446 |
| 473649 | AsoqNR | SALESMAN |   1 | 2014-10-29 | 2000.00 | 400.00 |    448 |
+--------+--------+----------+-----+------------+---------+--------+--------+
12 rows in set (0.02 sec)
　　2.3、在DISTINCT列上增加索引
　　如果在distinct列上不增加索引，那么mysql在查询时将创建临时表；如果我们在该列上增加索引，可以避免使用临时表：
create table message(
user_id int，
group_id int
);
//插入10条数据
insert into message values(24，67)，(15，76)，(134，986)，(6，98)，(46，988)，(13，2)，(12，89)，(17，34)，(63，19)，(92，74);
//没加索引，extra中有using temporary
mysql> explain select distinct user_id from message where group_id=2G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: message
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where; Using temporary
1 row in set (0.01 sec)
//增加索引
create index KEY_GID_UID on message (group_id， user_id);
//加了索引之后，终于不用创建临时表了
mysql> explain select distinct user_id from message where group_id=2G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: message
type: ref
possible_keys: KEY_GID_UID
key: KEY_GID_UID
key_len: 5
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
　　2.4、在group by后面增加order by null
　　在使用group by分组查询时，默认分组后，还会排序，可能会降低速度。如果不需要排序，那么可以在group by后面增加order by null，这样可以避免分组后排序：
//注意，没加order by null时，extra中有Using filesort
mysql> explain select * from emp group by deptnoG;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4296846
Extra: Using temporary; Using filesort
//加了order by null后，extra中没有Using filesort了
mysql> explain select * from emp group by deptno order by nullG;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4354494
Extra: Using temporary
1 row in set (0.00 sec)