mysql %w_MySql优化

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)