1.创建一个数据库
create database temp
2.创建表
/*部门表*/
CREATE TABLEdept(
deptno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,
dnameVARCHAR(20) NOT NULL DEFAULT"",
locVARCHAR(13) NOT NULL DEFAULT""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;CREATE TABLEemp
(empno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0, /*编号*/enameVARCHAR(20) NOT NULL DEFAULT "", /*名字*/jobVARCHAR(9) NOT NULL DEFAULT "",/*工作*/mgr MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,/*上级编号*/hiredate DATENOT NULL,/*入职时间*/salDECIMAL(7,2) NOT NULL,/*薪水*/commDECIMAL(7,2) NOT NULL,/*红利*/deptno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0 /*部门编号*/)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
#工资级别表CREATE TABLEsalgrade
(
grade MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,
losalDECIMAL(17,2) NOT NULL,
hisalDECIMAL(17,2) NOT NULL)ENGINE=MyISAM DEFAULT CHARSET=utf8;
3.插入数据
1 #测试数据2 INSERT INTO salgrade VALUES (1,700,1200);3 INSERT INTO salgrade VALUES (2,1201,1400);4 INSERT INTO salgrade VALUES (3,1401,2000);5 INSERT INTO salgrade VALUES (4,2001,3000);6 INSERT INTO salgrade VALUES (5,3001,9999);7
8 #随机返回一个字符串9 drop function if exists`rand_string`;10 delimiter $$11 create function rand_string(n INT)12 returns varchar(255) #该函数会返回一个字符串13 begin
14 #定义了一个变量 chars_str, 类型 varchar(100)15 #默认给 chars_str 初始值 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
16 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';17 declare return_str varchar(255) default '';18 declare i int default 0;19 while i
28 #随机返回一个部门号29 drop function if exists`rand_numn`;30 delimiter $$31 create functionrand_num()32 returns int(5)33 begin
34 declare i int default 0;35 set i = floor(10+rand()*500);36 returni;37 end$$38 delimiter ;39
40
41 #随机添加雇员max_num条,雇员的编号从start开始42 drop proceduce if exists`insert_emp`;43 delimiter $$44 create procedure insert_emp(in start int(10), in max_num int(10))45 begin
46 declare i int default 0;47 #set autocommit=0把autocommit设置为0,不要自动提交48 set autocommit = 0;49 while i <=max_num do50 insert into emp values((start+i), rand_string(6), 'SALESMAN', 0001, curdate(), 2000, 400, rand_num());51 set i = i + 1;52 end while;53 commit;#commit整体提交所有sql语句,提高效率54 end$$55 delimiter ;
call insert_emp(100001,4000000);
4.以记录慢查询的方式来启动mysql
先把mysql关闭后,再重启
{%mysql%}>bin/mysqld.exe --safe-mode --slow-query-log
5.查看当前数据库是否开启了日志:
1 mysql> show variables like 'log_%';2 +---------------------------------+-----------------------------+
3 | Variable_name | Value |
4 +---------------------------------+-----------------------------+
5 | log_bin | OFF |
6 | log_bin_trust_function_creators | OFF |
7 | log_error | E:\amp\mysql\Data\WIN-7.err |
8 | log_output | FILE |
9 | log_queries_not_using_indexes | OFF |
10 | log_slave_updates | OFF |
11 | log_slow_queries | ON |
12 | log_warnings | 1 |
13 +---------------------------------+-----------------------------+
14 8 rows in set (0.00 sec)
6.默认情况下,慢查询的时间是10s
show variables like 'long_query_time';
7.慢查询日志放在mysql的data目录下,在my.ini配置文件有data目录
#Path to the databaseroot
datadir="E:/amp/mysql/Data/"
8.为了测试,我们修改一下mysql默认的慢查询时间
set long_query_time = 1;
查询:
9.慢查询日志的查看
# Time: 180319 22:03:17#User@Host: root[root] @ localhost [127.0.0.1]# Query_time:0.633036 Lock_time: 0.001000 Rows_sent: 1 Rows_examined: 4000006
use temp;SET timestamp=1521468197;select * from emp where empno=347677;
10.分析
1 mysql> explain select * from emp where empno=347677\G2 *************************** 1. row ***************************
3 id: 1
4 select_type: SIMPLE5 table: emp6 type: ALL
7 possible_keys: NULL
8 key: NULL
9 key_len: NULL
10 ref: NULL
11 rows: 4000006
12 Extra: Using where
13 1 row in set (0.00 sec)
type依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL,除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引
A:system:表中只有一行数据或者是空表,且只能用于myisam和memory表。如果是Innodb引擎表,type列在这个情况通常都是all或者index
B:const:使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描
C:eq_ref:出现在要连接过个表的查询计划中,驱动表只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为not null,唯一索引和主键是多列时,只有所有的列都用作比较时才会出现eq_ref
D:ref:不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见与辅助索引的等值查找。或者多列主键、唯一索引中,使用第一个列之外的列作为等值查找也会出现,总之,返回数据不唯一的等值查找就可能出现。
E:fulltext:全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引
F:ref_or_null:与ref方法类似,只是增加了null值的比较。实际用的不多。
G:unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值
H:index_subquery:用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
I:range:索引范围扫描,常见于使用>,
J:index_merge:表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取所个索引,性能可能大部分时间都不如range
K:index:索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
L:all:这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。
11.通过刚刚的分析,初步判断为没有索引造成的
在empno这个字段上面添加主键索引
mysql> alter table emp add primary key(empno);
Query OK,4000006 rows affected (6.99sec)
Records:4000006 Duplicates: 0 Warnings: 0
索引文件变大,索引有开销,也需要占用磁盘
12.测试看看效果
mysql> select * from emp where empno=347677;+--------+--------+----------+-----+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 347677 | KwbVxC | SALESMAN | 1 | 2018-03-13 | 2000.00 | 400.00 | 208 |
+--------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (0.00 sec)