1、查看表结构
mysql> show create table emp\G
*************************** 1. row ***************************
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
Table: emp
Create Table: CREATE TABLE `emp` (
`empno`
int
(
4
) NOT NULL DEFAULT
'0'
,
`ENAME` varchar(
10
) DEFAULT NULL,
`JOB` varchar(
9
) DEFAULT NULL,
`MGR`
int
(
4
) DEFAULT NULL,
`HIRE` date DEFAULT NULL,
`SAL`
int
(
7
) DEFAULT NULL,
`COMM`
int
(
7
) DEFAULT NULL,
`deptno`
int
(
2
) DEFAULT NULL,
PRIMARY KEY (`empno`),
KEY `deptno` (`deptno`),
CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1
row in set (
0.00
sec)
|
---在此表中empno 为主键，在deptno上建有索引
mysql> select * from emp;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ENAME | JOB | MGR | HIRE | SAL | COMM | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|
7369
| SMITH | CLERK |
7902
|
1980
-12
-17
|
800
| NULL |
20
|
|
7499
| ALLEN | SALESMAN |
7698
|
1981
-02
-20
|
1600
|
300
|
30
|
|
7521
| WARD | SALESMAN |
7698
|
1981
-02
-22
|
1250
|
500
|
30
|
|
7566
| JONES | MANAGER |
7839
|
1981
-04
-02
|
2975
| NULL |
20
|
|
7654
| MARTIN | SALESMAN |
7698
|
1981
-09
-28
|
1250
|
1400
|
30
|
|
7698
| BLAKE | MANAGER |
7839
|
1981
-05
-01
|
2850
| NULL |
30
|
|
7782
| CLARK | MANAGER |
7839
|
1981
-06
-09
|
2450
| NULL |
10
|
|
7788
| SCOTT | ANALYST |
7566
|
1987
-07
-13
|
3000
| NULL |
10
|
|
7839
| KING | PRESIDENT | NULL |
1981
-11
-17
|
5000
| NULL |
10
|
|
7844
| TURNER | SALESMAN |
7698
|
1981
-09
-08
|
1500
|
0
|
30
|
|
7876
| ADAMS | CLERK |
7788
|
1987
-06
-13
|
1100
| NULL |
20
|
|
7900
| JAMES | CLERK |
7698
|
1981
-12
-03
|
950
| NULL |
30
|
|
7902
| FORD | ANALYST |
7566
|
1981
-12
-03
|
3000
| NULL |
20
|
|
7934
| MILLER | CLERK |
7782
|
1982
-01
-23
|
1300
| NULL |
10
|
+-------+--------+-----------+------+------------+------+------+--------+
14
rows
in
set (
0.00
sec)
|
1）全表扫描查询
mysql> explain select * from emp \G
*************************** 1. row ***************************
|
1
2
3
4
5
6
7
8
9
10
11
|
id:
1
select_type: SIMPLE
table: emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows:
14
Extra: NULL
1
row
in
set (
0.00
sec)
|
2、通过主键查询
mysql> explain select * from emp where empno=7788\G
*************************** 1. row ***************************
|
1
2
3
4
5
6
7
8
9
10
11
|
id:
1
select_type: SIMPLE
table: emp
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len:
4
ref: const
rows:
1
Extra: NULL
1
row
in
set (
0.02
sec)
|
3）忽略主键索引
mysql> explain select * from emp ignore index(pri) where empno=7788\G
*************************** 1. row ***************************
|
1
2
3
4
5
6
7
8
9
10
11
|
id:
1
select_type: SIMPLE
table: emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows:
14
Extra: Using
where
1
row
in
set (
0.00
sec)
|
4）强制使用主键
mysql> explain select * from emp force index(pri) where empno=7788\G
*************************** 1. row ***************************
|
1
2
3
4
5
6
7
8
9
10
11
|
id:
1
select_type: SIMPLE
table: emp
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len:
4
ref: const
rows:
1
Extra: NULL
1
row
in
set (
0.00
sec)
|
5）通过索引deptno查询
mysql> explain select * from emp where deptno=10\G
*************************** 1. row ***************************
|
1
2
3
4
5
6
7
8
9
10
11
|
id:
1
select_type: SIMPLE
table: emp
type: ref
possible_keys: deptno
key: deptno
key_len:
5
ref: const
rows:
4
Extra: NULL
1
row
in
set (
0.00
sec)
|
6）忽略索引的使用
mysql> explain select * from emp ignore index(deptno) where deptno=10\G
*************************** 1. row ***************************
|
1
2
3
4
5
6
7
8
9
10
11
|
id:
1
select_type: SIMPLE
table: emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows:
14
Extra: Using where
1
row in set (
0.00
sec)
|
7）强制使用索引
mysql> explain select * from emp force index (deptno) where deptno=10\G
*************************** 1. row ***************************
|
1
2
3
4
5
6
7
8
9
10
11
|
id:
1
select_type: SIMPLE
table: emp
type: ref
possible_keys: deptno
key: deptno
key_len:
5
ref: const
rows:
4
Extra: NULL
1
row
in
set (
0.00
sec)
|
