韩顺平2013版mysql优化视频_韩顺平 Mysql数据库优化(三) 慢查询

使用 showstatus 可以参考到mysql的各个参数，我们需要掌握的是以下参数, 其它请参考手册.

比如 com_select com_update com_insert com_delete , 比如我们在选择表究竟时候用MyISAM 还是 InnoDB , 可以看看该网站是以读和写操作为主，则可以使用MyISAM.

☞这里注意当我们使用 showstatus 来查询参数时，默认是当前会话.

show session status like xxxx

如果你要查看从数据库启动到现在状态

show global status like xxx

show status like ‘connections’  可以查看当前连接的数量.

表示数据库启动时间

show status like ‘slow_queries’

显示慢查询次数, 默认情况下mysql认为慢查询时间是10s

我们使用两个方法，蠕虫复制,可以构建大表，但是测试效果不好.

使用存储过程来创建海量表

模拟一个雇员管理系统

#模拟一个雇员管理系统

CREATE TABLE dept( /*部门表*/

deptno MEDIUMINT   UNSIGNED NOT NULL  DEFAULT 0,

dname VARCHAR(20)  NOT NULL  DEFAULT "",

loc VARCHAR(13) NOT NULL DEFAULT ""

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

#创建表EMP雇员

CREATE TABLE emp

(empno  MEDIUMINT UNSIGNED  NOT NULL DEFAULT 0, /*编号*/

ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/

job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/

hiredate DATE NOT NULL,/*入职时间*/

sal DECIMAL(7,2)  NOT NULL,/*薪水*/

comm DECIMAL(7,2) NOT NULL,/*红利*/

deptno MEDIUMINT UNSIGNED NOT NULLDEFAULT 0 /*部门编号*/

)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE salgrade

(

grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

losal DECIMAL(17,2)  NOT NULL,

hisal DECIMAL(17,2)  NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

#测试数据

INSERT INTO salgrade VALUES (1,700,1200);

INSERT INTO salgrade VALUES (2,1201,1400);

INSERT INTO salgrade VALUES (3,1401,2000);

INSERT INTO salgrade VALUES (4,2001,3000);

INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $$

#创建一个函数，可以返回一个随机的字符串

create function rand_string(n INT)

returns varchar(255) #该函数会返回一个字符串

begin

#定义了一个变量 chars_str，类型 varchar(100)

#默认给 chars_str 初始值  'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'

declare chars_str varchar(100)default

'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

declare return_str varchar(255)default '';

declare i int default 0;

while i < n do

set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));

set i = i + 1;

end while;

return return_str;

end $$

#这里我们又自定了一个函数,返回一个随机的部门号

create function rand_num( )

returns int(5)

begin

declare i int default 0;

set i = floor(10+rand()*500);

return i;

end $$

#随即添加雇员[光标]  400w,Mysql开发中，可以在存储过程中调用你自己

#编写的函数

create procedure insert_emp(in start int(10),in max_num int(10))

begin

declare i int default 0;

#set autocommit =0 把autocommit设置成0

#autocommit = 0 含义: 不要自动提交

set autocommit = 0;

repeat

set i = i + 1;

insert into emp values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());

until i = max_num

end repeat;

commit;

end $$

调用存储过程添加400w数据

call insert_emp(100001,4000000);

步骤

①在默认情况下，mysql是不会记录慢查询, 所以我们要使用另外一种方式启动mysql,指令: cmd>bin\mysqld.exe – safe-mode –slow-query-log

这样就会在mysql 的 data目录，生成一个日志文件，该文件可以把慢查询语句记录到文件.

②为了测试我们修改默认的慢查询时间

show variables  like ‘long_query_time’  【查询当前慢查询时间】

set long_query_time=1

③当执行一个时间超过1秒的sql语句，就会被记录下来.

# Query_time: 1.500000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 4000000

use temp100;

SET timestamp=1371870578;

select * from emp whereempno=456784;

④分析慢查询时如何导致explain 工具

基本语法: explain  sql \G

可以来分析mysql是如何执行你的sql语句

☞细致的说明请参考优化.ppt

⑤解决问题: 我们发现目前这个语句没有使用到索引，因此我们先考虑使用索引解决.

创建普通索引: CREATE INDEX 索引名 ON 表名(列)

⑥看看此时速度怎样

图:

①当一个表的存储引擎是MyISAM 时，对应三个文件

表名.frm 【表的结构】

表名.myd 【表的数据】

表名.myi  【索引的数据】

未完待续...