sql语句优化

  1. 云栖社区>
  2. 博客>
  3. 正文

sql语句优化

范大脚脚 2017-11-15 10:01:00 浏览568
展开阅读全文
复制代码
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语句, 这个工具的使用放一下,一会说.

添加索引 【小建议: 】 
复制代码

 


本文转自农夫山泉别墅博客园博客,原文链接:http://www.cnblogs.com/yaowen/p/8205220.html,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
范大脚脚
+ 关注