本文章来自 http://www.codeceo.com/article/mysql-index-2.html
什么是索引？
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 3221 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">bynid <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">bynid <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">fromtb1; 特别的：如果有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> |
浙公网安备 33010602011771号