mysql09---sql语句优化
Sql语句本身的优化
问题是: 如何从一个大项目中,迅速的定位执行速度慢的语句. (定位慢查询)
首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete.. / 当前连接)
show status
常用的:
show status like 'uptime' ; //查询启用时间
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 20492 |
+---------------+-------+
mysql> show status like 'com_select' ; //执行了多少次查询
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 0 |
+---------------+-------+
show status like 'com_insert' //执行了多少次添加, ...类推 update delete
show [session|global] status like .... 如果你不写 [session|global] 默认是session 会话,指取出当前窗口的执行,如果你想看所有(从mysql 启动到现在,则应该 global)
show status like 'connections'; //连接数量
netstat -an;
show status like 'slow_queries';//显示慢查询次数
②如何去定位慢查询
构建一个大表(400 万)-> 存储过程构建。默认情况下,mysql认为10秒才是一个慢查询.
修改mysql的慢查询:
show variables like 'long_query_time' ; //可以显示当前慢查询时间
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.010000 |
+-----------------+----------+
set long_query_time=1 ;//可以修改慢查询时间
构建大表->大表中记录有要求, 记录是不同才有用,否则测试效果和真实的相差大.
创建:
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 ;
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 NULL DEFAULT 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 $$
//删除自定义函数
drop function rand_string$$
数据库可以有各种数据对象(表,存储过程,视图,函数,触发器),创建存储过程和函数,用php代码都是可以直接调用的。
创建存储过程:
create procedure p4(n int)begin
select * from orde where gid=n;
end$
创建函数rand_string:
create function rand_string(n INT) #传一个整数
returns varchar(255) #该函数会返回一个字符串
begin
#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do #从大写小写字母中随机取出n个,
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
如果希望在程序中使用,是Ok!
mysql> select rand_string(10) from dual$$
+-----------------+
| rand_string(10) |
+-----------------+
| lNfnalfRUX |
+-----------------+
//dual是亚元表,空表,不想从哪个表查询就用这个亚元表
一定要指定数据库,因为函数,视图,存储过程是在数据库下面的。
#这里我们又自定了一个函数,返回一个随机数
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
创建一个存储过程:增加雇员,
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set 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 $$
#调用刚刚写好的函数, 1800000条记录,从100001号开始,大表创建完毕。
call insert_emp(100001,4000000);
mysql> select count(*) from emp$$
+----------+
| count(*) |
+----------+
| 4000000 |
+----------+
①这时我们如果出现一条语句执行时间超过1秒中,就会统计到(sql语句都有,不仅仅是查询语句).
②如果把慢查询的sql记录到我们的一个日志中
先关闭mysql(计算机---服务--stop),再启动,
在默认情况下,我们的mysql不会记录慢查询,需要在启动mysql时候,指定记录慢查询才可以,以下面的方式启动:
bin\mysqld.exe --safe-mode --slow-query-log #安全模式会写日志,能够恢复数据, [mysql5.5 可以在my.ini指定],启动完成之后会在data中生成log文件
bin\mysqld.exe –log-slow-queries=D:\MYSQL\abc.log [低版本mysql5.0可以在my.ini指定]
如果启用了慢查询日志,默认把这个文件放在my.ini 文件中记录的位置(如果只是拷贝数据库文件,那么索引是要失效的,要重建索引,数据库迁移而不是直接拷贝文件。)(最好不要修改这里)
#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"
③测试,可以看到在日志中就记录下我们的mysql慢sql语句.
优化问题.
通过 explain 语句可以分析,mysql如何执行你的sql语句, 这个工具的使用放一下,一会说.
添加索引 【小建议: 】