1
2

explain select t.* from teacher t, course c, teacherCard tc 
where t.tid=c.tid and t.tid=tc.tcid and (c.cid = 2 or tc.tcid=3);

1
2

explain select tc.tcdesc from teacherCard tc, course c, teacher t 
where c.tid = t.tid and t.tcid = tc.tcid and c.cname='sql';

1
2
3
4

explain select tc.tcdesc from teacherCard tc where tc.tcid=
(select t.tcid from teacher t where t.tid = 
(select c.tid from course c)
);

1
2

explain select t.tname, tc.tcdesc from teacher t, teacherCard tc 
where t.tcid=tc.tcid and t.tid=(select c.tid from course c where cname='sql');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

create table test01
(
tid int(3),
tname varchar(20)
);

alter table test01 add constraint tid_pk primary key(tid);

insert into test01 values(1, 'a');

explain select * from (select * from test01) t where tid=1;

1
2
3
4
5

explain select tid from test01 where tid=1;
/* 删除 primary 索引 */
alter table test01 drop primary key;
/* 修改索引为一般索引 */
create index test01_index on test01(tid);

1
2
3
4

alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid);
delete from teacher where tcid>3;
explain select t.tcid from teacher t, teacherCard tc where t.tcid = tc.tcid;

1
2
3
4
5
6

insert into teacher values(4, 'tz', 4);
insert into teacherCard values(4, 'tzc');

alter table teacher add index index_name(tname);

explain select * from teacher where tname='tz';

1
2
3

alter table teacher add index tid_index(tid);

explain select t.* from teacher t where t.tid <3;

1
2
3
4

/* tid 有索引，只扫描 tid 列 */
explain select tid from teacher;
/* course 表无索引，扫描全部数据 */
explain select cid from course;

1
2
3
4
5
6
7

alter table course add index cname_index(cname);

explain select t.tname, tc.tcdesc from teacher t, teacherCard tc 
where t.tcid=tc.tcid and t.tid=(select c.tid from course c where cname='sql');

explain select tc.tcdesc from teacherCard tc, course c, teacher t 
where c.tid = t.tid and t.tcid = tc.tcid and c.cname='sql';

 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

create table test_kl 
(
name char(20) not null default ''
);

alter table test_kl add index index_name(name);

explain select * from test_kl where name='';

alter table test_kl add column name1 char(20);
alter table test_kl add index index_name1(name1);

explain select * from test_kl where name1='';

drop index index_name on test_kl;
drop index index_name1 on test_kl;

alter table test_kl add index name_name1_index (name, name1);

explain select * from test_kl where name1='';

alter table test_kl add column name2 varchar(20);
alter table test_kl add index name2_index(name2);

/* key_len=63 = 60+1(null)+2(varchar) */
explain select * from test_kl where name2='';

1
2
3

alter table course add index tid_index(tid);

explain select * from course c, teacher t where c.tid = t.tid and t.tname='tw';

1

explain select * from course c, teacher t where c.tid = t.tid and t.tname='tz';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

create table test02
(
    a1 char(3),
    a2 char(3),
    a3 char(3),
    index idx_a1(a1),
    index idx_a2(a2),
    index idx_a3(a3)
);

/* 排序和查找不是同一个字段 Using filesort */
explain select * from test02 where a1 = '' order by a2;

drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;

alter table test02 add index idx_a1_a2_a3(a1, a2, a3);
/* 复合索引跨列 */
explain select * from test02 where a1='' order by a3;
explain select * from test02 where a2='' order by a3;
explain select * from test02 where a1='' order by a2;

1

explain select a1 from test02 where a1 in ('1', '2', '3') group by a2;

1
2
3
4
5
6
7
8
9

explain select a1, a2 from test02 where a1='' or a2='';

drop index idx_a1_a2_a3 on test02;
alter table test02 add index id_a1_a2(a1, a2);
explain select a1, a3 from test02 where a1='' or a3='';

/* 对 possible_keys 和 key 的影响 */
explain select a1, a2 from test02 where a1='' or a2='';
explain select a1, a2 from test02;

1

explain select * from test02 where a1='x' and a1='y';

 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

create table test03
(
a1 int(4) not null,
a2 int(4) not null,
a3 int(4) not null,
a4 int(4) not null
);

alter table test03 add index idx_a1_a2_a3_a4(a1, a2, a3, a4);

/* Using index */
/* 推荐按照复合索引的顺序查询 */
explain select a1, a2, a3, a4 from test03 where a1=1 and a2=2 and a3=3 and a4=4;

/* Using index */
/* 经过 SQL 优化器后，效果与上一个查询语句一致 */
explain select a1, a2, a3, a4 from test03 where a4=1 and a3=2 and a2=3 and a1=4;

/* Using where; Using index */
/* a4 跨列，索引失效，造成回表查询 */
/* where a1=1 and a2=2 ... order by a3 仍然遵循复合索引的顺序，因此有 Using index */
explain select a1, a2, a3, a4 from test03 where a1=1 and a2=2 and a4=4 order by a3;

/* Using where; Using index; Using filesort */
/* where a1=1 ... order by a3 跨列，多了一次查找/排序，出现 Using filesort */
explain select a1, a2, a3, a4 from test03 where a1=1 and a4=4 order by a3;

 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

create table book
(
bid int(4) primary key,
name varchar(20) not null,
authorid int(4) not null,
publicid int(4) not null,
typeid int(4) not null
);

insert into book values(1, 'java', 1, 1, 2);
insert into book values(2, 'html', 2, 1, 2);
insert into book values(3, 'sql', 3, 2, 1);
insert into book values(4, 'C', 4, 4, 3);

commit;

/* type:All*/
/* Using where; Using filesort */
explain select bid from book where typeid in(2, 3) and authorid=1 order by typeid desc;

/* type:index */
/* Using where; Using index; Using filesort */
alter table book add index idx_bta(bid, typeid, authorid);

/* 为避免干扰，优化之前删除老的索引 */
drop index idx_bta on book;

/* 根据 sql 实际解析的顺序，调整索引顺序 */
/* type:index */
/* Using where; Using index */
alter table book add index idx_tab(typeid, authorid, bid);


/* 删除索引，创建新索引测试 */
drop index idx_tab on book;

/* 将出现范围查询的字段 typeid 放到后面 */
alter table book add index idx_atb(authorid, typeid, bid);

/* 将范围查询 typeid in (2, 3) 放到 authorid=1 后面 */
/* type:ref */
/* Using where; Using index */
/* key_len: 4 */
explain select bid from book where authorid=1 and typeid in(2, 3) order by typeid desc;

/* Using index */
/* key_len: 8 */
/* typeid in(2, 3) 改为 typeid=3，不使用范围查询，typeid 索引有效 */
/* 通过 key_len 也可以佐证，此处有 2 个索引，typeid 索引有效 */
explain select bid from book where authorid=1 and typeid=3 order by typeid desc;

 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

create table teacher2
(
tid int(4) primary key,
cid int(4) not null
);

insert into teacher2 values(1, 2);
insert into teacher2 values(2, 1);
insert into teacher2 values(3, 3);

create table course2
(
cid int(4),
cname varchar(20)
);

insert into course2 values(1, 'java');
insert into course2 values(2, 'python');
insert into course2 values(3, 'kotlin');
commit;

/* 左连接，将数据量少的表放到左边 */
/* type:All */
/* Extra:  */
/* type:All */
/* Extra: Using where; Using join buffer  */
select * from teacher2 t left outer join course2 c 
on t.cid=c.cid where c.cname='java';

/* 增加索引 */
/* type: index */
/* Extra: Using index */
/* type: All */
/* Extra: Using where; Using join buffer*/
alter table teacher2 add index index_teacher2_cid(cid);

/* type: ref */
/* Extra: Using where */
/* type: ref */
/* Extra: Using index*/
alter table course2 add index index_course2_cname(cname);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

mysql> drop index idx_typeid on book;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_authorid on book;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table book add index idx_book_at (authorid, typeid);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from book where authorid = 1 and typeid = 2;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | book  | NULL       | ref  | idx_book_at   | idx_book_at | 8       | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 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

mysql> explain select * from book where authorid = 1 and typeid = 2;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | book  | NULL       | ref  | idx_book_at   | idx_book_at | 8       | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from book where authorid > 1 and typeid = 2;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | book  | NULL       | range | idx_book_at   | idx_book_at | 4       | NULL |    3 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from book where authorid = 1 and typeid > 2;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | book  | NULL       | range | idx_book_at   | idx_book_at | 8       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from book where authorid < 1 and typeid = 2;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | book  | NULL       | range | idx_book_at   | idx_book_at | 4       | NULL |    1 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from book where authorid < 4 and typeid = 2;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | book  | NULL       | range | idx_book_at   | idx_book_at | 4       | NULL |    3 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

mysql> explain select * from teacher where tname = 'abc';
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | teacher | NULL       | ref  | index_name    | index_name | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from teacher where tname = 123;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | teacher | NULL       | ALL  | index_name    | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.36 sec)

1
2
3
4
5

/* 有数据 */
select tname from teacher where exists(select * from teacher);

/* 无数据 */
select tname from teacher where exists(select * from teacher where tid=9999);

1
2

mysql> select tname from teacher where exists (select * from teacher where tid = 9999);
Empty set (0.00 sec)

1
2
3
4
5
6
7
8

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | OFF                                 |
| slow_query_log_file | D:\Software\mysql\Data\wjq-slow.log |
+---------------------+-------------------------------------+
2 rows in set, 1 warning (0.35 sec)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

/* 模拟慢查询 */
select sleep(5);
select sleep(4);
select sleep(3);

/* 获取返回记录最多的 3 个 SQL */
mysqldumpslow -s r -t 3 /var/lib/mysql/bigdata01-slow.log

/* 获取访问次数最多的 3 个 SQL */
mysqldumpslow -s c -t 3 /var/lib/mysql/bigdata01-slow.log

/* 按照时间排序，前 10 条包含 left join 查询语句的 SQL */
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/bigdata01-slow.log

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

root@946bb7e7ee77:/# mysqldumpslow -s r -t 3 /var/lib/mysql/946bb7e7ee77-slow.log

Reading mysql slow query log from /var/lib/mysql/946bb7e7ee77-slow.log
Count: 2  Time=5.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@[192.168.163.1]
  select sleep(N)

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  # Time: N  N:N:N
  # User@Host: root[root] @  [N.N.N.N]  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select sleep(N)

Died at /usr/bin/mysqldumpslow line 167, <> chunk 3.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

root@946bb7e7ee77:/# mysqldumpslow -s c -t 3 /var/lib/mysql/946bb7e7ee77-slow.log

Reading mysql slow query log from /var/lib/mysql/946bb7e7ee77-slow.log
Count: 2  Time=5.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@[192.168.163.1]
  select sleep(N)

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  # Time: N  N:N:N
  # User@Host: root[root] @  [N.N.N.N]  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select sleep(N)

Died at /usr/bin/mysqldumpslow line 167, <> chunk 3.
root@946bb7e7ee77:/# 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

root@946bb7e7ee77:/# mysqldumpslow -s t -t 10 "left join" /var/lib/mysql/946bb7e7ee77-slow.log

Reading mysql slow query log from left join /var/lib/mysql/946bb7e7ee77-slow.log
Can't open left join: No such file or directory at /usr/bin/mysqldumpslow line 97.
Count: 2  Time=5.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@[192.168.163.1]
  select sleep(N)

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  # Time: N  N:N:N
  # User@Host: root[root] @  [N.N.N.N]  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select sleep(N)

Died at /usr/bin/mysqldumpslow line 167, <> chunk 3.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

create database testdata;
use testdata;

create table dept 
(
dno int(5) primary key default 0,
dname varchar(20) not null default '',
loc varchar(30) default ''
) engine=innodb default charset=utf8;

create table emp
(
eid int(5) primary key,
ename varchar(20) not null default '',
job varchar(20) not null default '',
deptno int(5) not null default 0
)engine=innodb default charset=utf8;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

use testdata;
delimiter $
create function randstring(n int) returns varchar(255)
begin 

    declare all_str varchar(100) default 'abcdefghijklmnopqrestuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i<n
    do
        set return_str=concat(return_str, substring(all_str, FLOOR(1+rand()*52), 1));
        set i=i+1;
    end while;
    return return_str;
end $

1
2
3
4
5
6
7
8
9

/* 开启慢查询日志，再创建存储过程/存储函数，报如下错误   */
/* ERROR 1418 (HY000): 
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA 
in its declaration and binary logging is enabled 
(you *might* want to use the less safe log_bin_trust_function_creators variable) */

/* 临时解决 */
show variables like '%log_bin_trust_function_creators%';
set global log_bin_trust_function_creators=1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

use testdata;
delimiter $
create function ran_num() returns int(5)
begin 

declare i int default 0;
set i=floor(rand()*100);
return i;

end$

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

delimiter $
create procedure insert_emp(in eid_start int(10), in data_times int(10))
begin 
declare i int default 0;
set autocommit =0;

repeat 
insert into emp values(eid_start+i, randstring(5), 'other', ran_num());
set i=i+1;
until i=data_times
end repeat;

commit;

end $

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

delimiter $
create procedure insert_dept(in dno_start int(10), in data_times int(10))
begin 
declare i int default 0;
set autocommit =0;

repeat 
insert into dept values(dno_start+i, randstring(6), randstring(8));
set i=i+1;
until i=data_times
end repeat;

commit;

end $

1
2
3
4
5
6
7

delimiter ;

call insert_emp(1000, 800000);
call insert_dept(10, 30);

/* 验证插入数据量 */
select count(1) from emp;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

show variables like '%profiling%';
/* profiling 影响性能，在部署实施前，应关闭此项 */
set profiling=on;

/* 记录 profiling 打开之后的所有 SQL 语句消耗的时间 */
show profiles;

/* 精确查询更多详情，Query_Id 参考上个语句的查询结果 */
show profile all for query 2;
show profile cpu, block io for query 2;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql> call insert_emp(1000, 800000);
Query OK, 0 rows affected (24.61 sec)

mysql> call insert_dept(10, 30);
Query OK, 0 rows affected (0.00 sec)

mysql> select count(1) from emp;
+----------+
| count(1) |
+----------+
|   800000 |
+----------+
1 row in set (0.15 sec)

 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

mysql> show profiles;
Empty set

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.04 sec)

mysql> set profiling = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show profiles;
Empty set

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.04 sec)

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00036875 | show variables like '%profiling%' |
+----------+------------+-----------------------------------+
1 row in set (0.04 sec)