????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)