开发者社区> 范大脚脚> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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语句, 这个工具的使用放一下,一会说.

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

 


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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
一次SQL查询语句的优化
1.项目中之前的"我关注的拍品列表"需要添加筛选功能,因为目前显示的关注的拍品太多没有进行分类,用户体验差。       2.添加筛选条件之后,可以筛选出“未开始”“进行中”“已结束”三种情况的拍品。    其中              “未开始”--->状态为    1     “进行中”---->状态为   2     “已结束”---->状态为   3  or   4   or 5  or 6  or 7     3.优化之前,每一个条件都书写了一个SQL 语句。
558 0
SQL不完全手册(二):高级语句 上
SQL不完全手册(二):高级语句
36 0
SQL不完全手册(一):概念和基础语句
SQL不完全手册(一):概念和基础语句
24 0
SQL 手册-实用 SQL 语句-TRACE
TRACE 语句用于查看具体 SQL 的执行情况。TRACE [SQL] 和 SHOW TRACE 要结合使用。
57 0
SQL 手册-实用 SQL 语句-多语句
本文将介绍PolarDB-X多语句的相关信息。
52 0
经典SQL语句大全
来源:http://www.cnblogs.com/yubinfeng/archive/2010/11/02/1867386.html 一、基础 1、说明:创建数据库 CREATE DATABASE database-name  2、说明:删除数据库drop database dbname3、说明:备份sql server--- 创建 备份数据的 device USE mas
1364 0
T-SQL查询:语句执行顺序
原文:T-SQL查询:语句执行顺序 读书笔记:《Microsoft SQL Server 2008技术内幕:T-SQL查询》   ===============  T-SQL查询的执行顺序 ===============      =============== T-SQL查询的示意图...
787 0
+关注
3656
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载