1、索引
索引是表的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。对于索引,会保存在额外的文件中。
2、索引,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。
索引由数据库中一列或多列组合而成,其作用是提高对表中数据的查询速度
索引的优点是可以提高检索数据的速度
索引的缺点是创建和维护索引需要耗费时间
索引可以提高查询速度,会减慢写入速度
索引分类
1.普通索引
2.唯一索引
3.全文索引
4.单列索引
5.多列索引
6.空间索引
7.主键索引
8.组合索引
- 普通索引:仅加速查询
- 唯一索引:加速查询 + 列值唯一(可以有null)
- 主键索引:加速查询 + 列值唯一 + 表中只有一个(不可以有null)
- 组合索引:多列值组成一个索引,
专门用于组合搜索,其效率大于索引合并 - 全文索引:对文本的内容进行分词,进行搜索
索引合并,使用多个单列索引组合搜索
覆盖索引,select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖
如何创建索引?记住一个单词—explain
创建表的时候创建索引
1
2
3
4
5
6
|
<span class=
"hljs-keyword"
>
CREATE
<span class=
"hljs-keyword"
>
TABLE
tbl_name(
字段名称 字段类型 [完整性约束条件],
,,,,
[<span class=
"hljs-keyword"
>
UNIQUE
|FULLTEXT|SPATIAL] <span class=
"hljs-keyword"
>
INDEX
|<span class=
"hljs-keyword"
>
KEY
[索引名称](字段名称[(长度)])
[<span class=
"hljs-keyword"
>
ASC
|<span class=
"hljs-keyword"
>
DESC
]
);</span></span></span></span></span></span></span>
|
在已经存在的表上创建索引:
1
2
|
1.
CREATE
[
UNIQUE
|FULLTEXT|SPATIAL]
INDEX
索引名称
ON
表名{字段名称[(长度)] [
ASC
|
DESC
]}
2.
ALTER
TABLE
tbl_name
ADD
[<span class=
"hljs-string"
>
UNIQUE
|FULLTEXT|SPATIAL]
INDEX
索引名称(字段名称[<span class=
"hljs-string"
>(长度)][<span class=
"hljs-symbol"
>
ASC
|
DESC
]);</span></span></span>
|
如何删除索引?
1
|
<span class=
"hljs-keyword"
>
DROP
<span class=
"hljs-keyword"
>
INDEX
索引名称 <span class=
"hljs-keyword"
>
ON
tbl_name</span></span></span>
|
1.普通索引
普通索引仅有一个功能:加速查询
创建表+索引
1
2
3
4
5
6
7
|
<span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
table
in1(
nid <span class=
"hljs-built_in"
>
int
<span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
auto_increment
primary
<span class=
"hljs-keyword"
>
key
,
<span class=
"hljs-keyword"
>
name
<span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>32) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
email <span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>64) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
extra <span class=
"hljs-built_in"
>text,
<span class=
"hljs-keyword"
>
index
ix_name (<span class=
"hljs-keyword"
>
name
)
)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|
创建索引
1
|
<span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
index
index_name <span class=
"hljs-keyword"
>
on
table_name(column_name)</span></span></span>
|
删除索引
1
|
<span class=
"hljs-keyword"
>
drop
index_name <span class=
"hljs-keyword"
>
on
table_name;</span></span>
|
查看索引
1
|
<span class=
"hljs-keyword"
>show <span class=
"hljs-keyword"
>
index
<span class=
"hljs-keyword"
>
from
table_name;</span></span></span>
|
注意:对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。
1
|
<span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
index
ix_extra <span class=
"hljs-keyword"
>
on
in1(extra(<span class=
"hljs-number"
>32));</span></span></span></span>
|
2、唯一索引
唯一索引有两个功能:加速查询 和 唯一约束(可含null)
创建表+唯一索引
1
2
3
4
5
6
7
|
<span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
table
in1(
nid <span class=
"hljs-built_in"
>
int
<span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
auto_increment
primary
<span class=
"hljs-keyword"
>
key
,
<span class=
"hljs-keyword"
>
name
<span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>32) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
email <span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>64) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
extra <span class=
"hljs-built_in"
>text,
<span class=
"hljs-keyword"
>
unique
ix_name (<span class=
"hljs-keyword"
>
name
)
)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|
创建唯一索引
1
|
<span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
unique
<span class=
"hljs-keyword"
>
index
索引名 <span class=
"hljs-keyword"
>
on
表名(列名)</span></span></span></span>
|
删除唯一索引
1
|
<span class=
"hljs-keyword"
>
drop
<span class=
"hljs-keyword"
>
unique
<span class=
"hljs-keyword"
>
index
索引名 <span class=
"hljs-keyword"
>
on
表名</span></span></span></span>
|
3、主键索引
主键有两个功能:加速查询 和 唯一约束(不可含null)
创建表+创建主键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
<span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
table
in1(
nid <span class=
"hljs-built_in"
>
int
<span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
auto_increment
primary
<span class=
"hljs-keyword"
>
key
,
<span class=
"hljs-keyword"
>
name
<span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>32) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
email <span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>64) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
extra <span class=
"hljs-built_in"
>text,
<span class=
"hljs-keyword"
>
index
ix_name (<span class=
"hljs-keyword"
>
name
)
)
<span class=
"hljs-keyword"
>
OR
<span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
table
in1(
nid <span class=
"hljs-built_in"
>
int
<span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
auto_increment,
<span class=
"hljs-keyword"
>
name
<span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>32) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
email <span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>64) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
extra <span class=
"hljs-built_in"
>text,
primary
<span class=
"hljs-keyword"
>
key
(ni1),
<span class=
"hljs-keyword"
>
index
ix_name (<span class=
"hljs-keyword"
>
name
)
)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|
创建主键
1
|
<span class=
"hljs-keyword"
>
alter
<span class=
"hljs-keyword"
>
table
表名 <span class=
"hljs-keyword"
>
add
primary
<span class=
"hljs-keyword"
>
key
(列名);</span></span></span></span>
|
删除主键
1
2
|
<span class=
"hljs-keyword"
>
alter
<span class=
"hljs-keyword"
>
table
表名 <span class=
"hljs-keyword"
>
drop
primary
<span class=
"hljs-keyword"
>
key
;
<span class=
"hljs-keyword"
>
alter
<span class=
"hljs-keyword"
>
table
表名 <span class=
"hljs-keyword"
>
modify
列名 <span class=
"hljs-built_in"
>
int
, <span class=
"hljs-keyword"
>
drop
primary
<span class=
"hljs-keyword"
>
key
;</span></span></span></span></span></span></span></span></span></span>
|
4、组合索引
组合索引是将n个列组合成一个索引
其应用场景为:频繁的同时使用n列来进行查询,如:where n1 = ‘alex’ and n2 = 666。
创建表
1
2
3
4
5
6
|
<span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
table
in3(
nid <span class=
"hljs-built_in"
>
int
<span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
auto_increment
primary
<span class=
"hljs-keyword"
>
key
,
<span class=
"hljs-keyword"
>
name
<span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>32) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
email <span class=
"hljs-built_in"
>
varchar
(<span class=
"hljs-number"
>64) <span class=
"hljs-keyword"
>
not
<span class=
"hljs-literal"
>
null
,
extra <span class=
"hljs-built_in"
>text
)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|
创建组合索引
1
|
<span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
index
ix_name_email <span class=
"hljs-keyword"
>
on
in3(<span class=
"hljs-keyword"
>
name
,email);</span></span></span></span>
|
如上创建组合索引之后,查询:
- name and email – 使用索引
- name — 使用索引
- email — 不使用索引
注意:对于同时搜索n个条件时,组合索引的性能好于多个单一索引合并。
相关命令
1
2
3
4
5
6
7
8
9
10
11
12
13
|
- 查看表结构
desc
表名
- 查看生成表的SQL
<span class=
"hljs-keyword"
>show <span class=
"hljs-keyword"
>
create
<span class=
"hljs-keyword"
>
table
表名
- 查看索引
<span class=
"hljs-keyword"
>show <span class=
"hljs-keyword"
>
index
<span class=
"hljs-keyword"
>
from
表名
- 查看执行时间
<span class=
"hljs-keyword"
>
set
profiling = <span class=
"hljs-number"
>1;
SQL...
<span class=
"hljs-keyword"
>show <span class=
"hljs-keyword"
>profiles;</span></span></span></span></span></span></span></span></span></span>
|
使用索引和不使用索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
由于索引是专门用于加速搜索而生,所以加上索引之后,查询效率会快到飞起来。
<span class=
"hljs-comment"
># 有索引
mysql>
select
*
from
tb1
where
name
= <span class=
"hljs-string"
>
'wupeiqi-888'
;
+
-----+-------------+---------------------+----------------------------------+---------------------+
<span class=
"hljs-params"
>| nid |
name
<span class=
"hljs-params"
>| email | radom <span class=
"hljs-params"
>| ctime |
+
-----+-------------+---------------------+----------------------------------+---------------------+
<span class=
"hljs-params"
>| 889 | wupeiqi-<span class=
"hljs-number"
>888 <span class=
"hljs-params"
>| wupeiqi888@live.com | <span class=
"hljs-number"
>5312269e76a16a90b8a8301d5314204b <span class=
"hljs-params"
>| 2016-08-03 09:33:35 |
+
-----+-------------+---------------------+----------------------------------+---------------------+
<span class=
"hljs-number"
>1 row <span class=
"hljs-keyword"
>
in
set
(<span class=
"hljs-number"
>0.<span class=
"hljs-number"
>00 sec)
<span class=
"hljs-comment"
># 无索引
mysql>
select
*
from
tb1
where
email = <span class=
"hljs-string"
>
'wupeiqi888@live.com'
;
+
-----+-------------+---------------------+----------------------------------+---------------------+
<span class=
"hljs-params"
>| nid |
name
<span class=
"hljs-params"
>| email | radom <span class=
"hljs-params"
>| ctime |
+
-----+-------------+---------------------+----------------------------------+---------------------+
<span class=
"hljs-params"
>| 889 | wupeiqi-<span class=
"hljs-number"
>888 <span class=
"hljs-params"
>| wupeiqi888@live.com | <span class=
"hljs-number"
>5312269e76a16a90b8a8301d5314204b <span class=
"hljs-params"
>| 2016-08-03 09:33:35 |
+
-----+-------------+---------------------+----------------------------------+---------------------+
<span class=
"hljs-number"
>1 row <span class=
"hljs-keyword"
>
in
set
(<span class=
"hljs-number"
>1.23 sec)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|
正确使用索引
数据库表中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也会不奏效。
即使建立索引,索引也不会生效:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
-
like
'%xx'
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
<span class=
"hljs-keyword"
>
name
<span class=
"hljs-keyword"
>
like
<span class=
"hljs-string"
>
'%cn'
;
- 使用函数
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
<span class=
"hljs-keyword"
>reverse(<span class=
"hljs-keyword"
>
name
) = <span class=
"hljs-string"
>
'wupeiqi'
;
-
or
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid = <span class=
"hljs-number"
>1 <span class=
"hljs-keyword"
>
or
email = <span class=
"hljs-string"
>
'seven@live.com'
;
特别的:当
or
条件中有未建立索引的列才失效,以下会走索引
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid = <span class=
"hljs-number"
>1 <span class=
"hljs-keyword"
>
or
<span class=
"hljs-keyword"
>
name
= <span class=
"hljs-string"
>
'seven'
;
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid = <span class=
"hljs-number"
>1 <span class=
"hljs-keyword"
>
or
email = <span class=
"hljs-string"
>
'seven@live.com'
<span class=
"hljs-keyword"
>
and
<span class=
"hljs-keyword"
>
name
= <span class=
"hljs-string"
>
'alex'
- 类型不一致
如果列是字符串类型,传入条件是必须用引号引起来,不然...
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
<span class=
"hljs-keyword"
>
name
= <span class=
"hljs-number"
>999;
- !=
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
<span class=
"hljs-keyword"
>
name
!= <span class=
"hljs-string"
>
'alex'
特别的:如果是主键,则还是会走索引
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid != <span class=
"hljs-number"
>123
- >
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
<span class=
"hljs-keyword"
>
name
> <span class=
"hljs-string"
>
'alex'
特别的:如果是主键或索引是整数类型,则还是会走索引
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid > <span class=
"hljs-number"
>123
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
<span class=
"hljs-keyword"
>num > <span class=
"hljs-number"
>123
- <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
<span class=
"hljs-keyword"
>
select
email <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
<span class=
"hljs-keyword"
>
name
<span class=
"hljs-keyword"
>
desc
;
当根据索引排序时候,选择的映射如果不是索引,则不走索引
特别的:如果对主键排序,则还是走索引:
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
nid <span class=
"hljs-keyword"
>
desc
;
- 组合索引最左前缀
如果组合索引为:(
name
,email)
name
and
email <span class=
"hljs-comment"
>
-- 使用索引
name
<span class=
"hljs-comment"
>
-- 使用索引
email <span class=
"hljs-comment"
>
-- 不使用索引</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|
其他注意事项
1
2
3
4
5
6
7
8
9
|
<span class=
"hljs-deletion"
>- 避免使用
select
*
<span class=
"hljs-deletion"
>-
count
(1)或
count
(列) 代替
count
(*)
<span class=
"hljs-deletion"
>- 创建表时尽量时
char
代替
varchar
<span class=
"hljs-deletion"
>- 表的字段顺序固定长度的字段优先
<span class=
"hljs-deletion"
>- 组合索引代替多个单列索引(经常使用多个条件查询时)
<span class=
"hljs-deletion"
>- 尽量使用短索引
<span class=
"hljs-deletion"
>- 使用连接(
JOIN
)来代替子查询(Sub-Queries)
<span class=
"hljs-deletion"
>- 连表时注意条件类型需一致
<span class=
"hljs-deletion"
>- 索引散列值(重复少)不适合建索引,例:性别不适合</span></span></span></span></span></span></span></span></span>
|
limit分页
无论是否有索引,limit分页是一个值得关注的问题
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
每页显示10条:
当前 118 120, 125
倒序:
大 小
980 970 7 6 6 5 54 43 32
21 19 98
下一页:
<span class=
"hljs-keyword"
>
select
*
<span class=
"hljs-keyword"
>
from
tb1
<span class=
"hljs-keyword"
>
where
nid < (<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
(<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid < 当前页最小值 <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
nid <span class=
"hljs-keyword"
>
desc
<span class=
"hljs-keyword"
>limit 每页数据 *【页码-当前页】) A <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
A.nid <span class=
"hljs-keyword"
>
asc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>1)
<span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
nid <span class=
"hljs-keyword"
>
desc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>10;
<span class=
"hljs-keyword"
>
select
*
<span class=
"hljs-keyword"
>
from
tb1
<span class=
"hljs-keyword"
>
where
nid < (<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
(<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid < <span class=
"hljs-number"
>970 <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
nid <span class=
"hljs-keyword"
>
desc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>40) A <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
A.nid <span class=
"hljs-keyword"
>
asc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>1)
<span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
nid <span class=
"hljs-keyword"
>
desc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>10;
上一页:
<span class=
"hljs-keyword"
>
select
*
<span class=
"hljs-keyword"
>
from
tb1
<span class=
"hljs-keyword"
>
where
nid < (<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
(<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid > 当前页最大值 <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
nid <span class=
"hljs-keyword"
>
asc
<span class=
"hljs-keyword"
>limit 每页数据 *【当前页-页码】) A <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
A.nid <span class=
"hljs-keyword"
>
asc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>1)
<span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
nid <span class=
"hljs-keyword"
>
desc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>10;
<span class=
"hljs-keyword"
>
select
*
<span class=
"hljs-keyword"
>
from
tb1
<span class=
"hljs-keyword"
>
where
nid < (<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
(<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid > <span class=
"hljs-number"
>980 <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
nid <span class=
"hljs-keyword"
>
asc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>20) A <span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
A.nid <span class=
"hljs-keyword"
>
desc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>1)
<span class=
"hljs-keyword"
>
order
<span class=
"hljs-keyword"
>
by
nid <span class=
"hljs-keyword"
>
desc
<span class=
"hljs-keyword"
>limit <span class=
"hljs-number"
>10;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|
执行计划
explain + 查询SQL – 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化
1
2
3
4
5
6
7
|
mysql> explain
select
*
from
tb2;
+
----+-------------+-------+------+---------------+------+---------+------+------+-------+
<span class=
"hljs-params"
>| id | select_type <span class=
"hljs-params"
>|
table
| type <span class=
"hljs-params"
>| possible_keys |
key
<span class=
"hljs-params"
>| key_len | ref <span class=
"hljs-params"
>|
rows
| Extra <span class=
"hljs-params"
>|
+
----+-------------+-------+------+---------------+------+---------+------+------+-------+
| <span class=
"hljs-number"
>1 <span class=
"hljs-params"
>| SIMPLE | tb2 <span class=
"hljs-params"
>|
ALL
|
NULL
<span class=
"hljs-params"
>|
NULL
|
NULL
<span class=
"hljs-params"
>|
NULL
| <span class=
"hljs-number"
>2 <span class=
"hljs-params"
>|
NULL
|
+
----+-------------+-------+------+---------------+------+---------+------+------+-------+
<span class=
"hljs-number"
>1 row <span class=
"hljs-keyword"
>
in
set
(<span class=
"hljs-number"
>0.<span class=
"hljs-number"
>00 sec)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|
执行计划
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
id
查询顺序标识
如:mysql> explain <span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
(<span class=
"hljs-keyword"
>
select
nid,
name
<span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid < <span class=
"hljs-number"
>10) <span class=
"hljs-keyword"
>
as
B;
+
----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | <span class=
"hljs-keyword"
>ref |
rows
| Extra |
+
----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| <span class=
"hljs-number"
>1 |
PRIMARY
| <derived2> |
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| <span class=
"hljs-number"
>9 |
NULL
|
| <span class=
"hljs-number"
>2 | DERIVED | tb1 | range |
PRIMARY
|
PRIMARY
| <span class=
"hljs-number"
>8 |
NULL
| <span class=
"hljs-number"
>9 | Using <span class=
"hljs-keyword"
>
where
|
+
----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
特别的:如果使用
union
连接气值可能为<span class=
"hljs-literal"
>
null
select_type
查询类型
SIMPLE 简单查询
PRIMARY
最外层查询
SUBQUERY 映射为子查询
DERIVED 子查询
UNION
联合
UNION
RESULT 使用联合的结果
...
table
正在访问的表名
type
查询时的访问方式,性能:
all
<
index
< range < index_merge < ref_or_null < <span class=
"hljs-keyword"
>ref < eq_ref < system/<span class=
"hljs-keyword"
>const
ALL
全表扫描,对于数据表从头到尾找一遍
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1;
特别的:如果有limit限制,则找到之后就不在继续向下扫描
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
email = <span class=
"hljs-string"
>
'seven@live.com'
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
email = <span class=
"hljs-string"
>
'seven@live.com'
limit <span class=
"hljs-number"
>1;
虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。
INDEX
全索引扫描,对索引从头到尾找一遍
<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
tb1;
RANGE 对索引列进行范围查找
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
name
< <span class=
"hljs-string"
>
'alex'
;
PS:
between
and
<span class=
"hljs-keyword"
>
in
> >= < <= 操作
注意:!= 和 > 符号
INDEX_MERGE 合并索引,使用多个单列索引搜索
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
name
= <span class=
"hljs-string"
>
'alex'
<span class=
"hljs-function"
>
or
nid <span class=
"hljs-title"
>
in
(<span class=
"hljs-params"
><span class=
"hljs-number"
>11,<span class=
"hljs-number"
>22,<span class=
"hljs-number"
>33);
REF 根据索引查找一个或多个值
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
name
= <span class=
"hljs-string"
>
'seven'
;
EQ_REF 连接时使用
primary
key
或
unique
类型
<span class=
"hljs-keyword"
>
select
tb2.nid,tb1.
name
<span class=
"hljs-keyword"
>
from
tb2
left
<span class=
"hljs-keyword"
>
join
tb1
on
tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,<span class=
"hljs-keyword"
>const表很快,因为它们只读取一次。
<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid = <span class=
"hljs-number"
>2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是<span class=
"hljs-keyword"
>const联接类型的一个特例。
<span class=
"hljs-keyword"
>
select
* <span class=
"hljs-keyword"
>
from
(<span class=
"hljs-keyword"
>
select
nid <span class=
"hljs-keyword"
>
from
tb1 <span class=
"hljs-keyword"
>
where
nid = <span class=
"hljs-number"
>1) <span class=
"hljs-keyword"
>
as
A;
possible_keys
可能使用的索引
key
真实使用的
key_len
MySQL中使用索引字节长度
rows
mysql估计为了找到所需的行而要读取的行数
------ 只是预估值
extra
该列包含MySQL解决查询的详细信息
“Using
index
”
此值表示mysql将使用覆盖索引,以避免访问表。不要把覆盖索引和
index
访问类型弄混了。
“Using <span class=
"hljs-keyword"
>
where
”
这意味着mysql服务器将在存储引擎检索行后再进行过滤,许多<span class=
"hljs-keyword"
>
where
条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带<span class=
"hljs-keyword"
>
where
子句的查询都会显示“Using <span class=
"hljs-keyword"
>
where
”。有时“Using <span class=
"hljs-keyword"
>
where
”的出现就是一个暗示:查询可受益于不同的索引。
“Using
temporary
”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成,explain不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成。
“<span class=
"hljs-function"
>Range <span class=
"hljs-keyword"
>checked <span class=
"hljs-keyword"
>
for
each <span class=
"hljs-title"
>record(<span class=
"hljs-params"
>
index
map: N)”
这个意味着没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的。</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|
慢日志查询
a、配置MySQL自动记录慢日志
1
2
3
4
|
<span class=
"hljs-attr"
>slow_query_log = <span class=
"hljs-literal"
>
OFF
是否开启慢日志记录
<span class=
"hljs-attr"
>long_query_time = <span class=
"hljs-number"
>2 时间限制,超过此时间,则记录
<span class=
"hljs-attr"
>slow_query_log_file = /usr/slow.log 日志文件
<span class=
"hljs-attr"
>log_queries_not_using_indexes = <span class=
"hljs-literal"
>
OFF
为使用索引的搜索是否记录</span></span></span></span></span></span></span>
|
注:查看当前配置信息:
show variables like ‘%query%’
修改当前配置:
set global 变量名 = 值
b、查看MySQL慢日志
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
参数详解
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
""
"
<span class="
hljs-comment
">--verbose 版本
<span class="
hljs-comment
">--debug 调试
<span class="
hljs-comment
">--help 帮助
-v 版本
-d 调试模式
-s ORDER 排序方式
what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average <span class="
hljs-keyword
">lock <span class="
hljs-keyword
">time
ar: average <span class="
hljs-keyword
">rows sent
<span class="
hljs-keyword
">at: average <span class="
hljs-keyword
">query <span class="
hljs-keyword
">time
c: <span class="
hljs-keyword
">count
l: <span class="
hljs-keyword
">lock <span class="
hljs-keyword
">time
r: <span class="
hljs-keyword
">rows sent
t: <span class="
hljs-keyword
">query <span class="
hljs-keyword
">time
-r 反转顺序,默认文件倒序拍。<span class="
hljs-keyword
">reverse the <span class="
hljs-keyword
">sort <span class="
hljs-keyword
">order (largest <span class="
hljs-keyword
">last instead <span class="
hljs-keyword
">of <span class="
hljs-keyword
">first)
-t <span class="
hljs-keyword
">NUM 显示前N条just <span class="
hljs-keyword
">show the top n queries
-a 不要将<span class="
hljs-keyword
">SQL中数字转换成N,字符串转换成S。don<span class="
hljs-string
">'t abstract all numbers to N and strings to 'S<span class="
hljs-string
">'
-n NUM abstract numbers with at least n digits within names
-g PATTERN 正则匹配;grep: only consider stmts that include this string
-h HOSTNAME mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
default is '*<span class="
hljs-string
">', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l 总时间中不减去锁定时间;don't subtract <span class="
hljs-keyword
">lock <span class="
hljs-keyword
">time <span class="
hljs-keyword
">from total <span class="
hljs-keyword
">time
<span class="
hljs-string
">"
""
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
|