数据库优化之慢查询

MySQL如何优化

表的设计合理化(符合3NF)

添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]

SQL语句优化

分表技术(水平分割、垂直分割)

读写[写: update/delete/add]分离

存储过程 [模块化编程，可以提高速度]

对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]

mysql服务器硬件升级

定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

MySQL优化前通过慢查询日志迅速的定位执行速度慢的语句 (即定位慢查询)

MySQL中的日志包括：错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能：通用查询日志和慢查询日志。

1）通用查询：建立的客户端连接和执行的语句。

2）慢查询：所有执行时间超过long_query_time秒的所有查询或者不使用索引的查询

慢查询日志:
MySQL慢查询日志记录下所有执行超过long_query_time时间的SQL语句，帮你找到执行慢的SQL，方便我们对这些SQL进行优化。

查询准备

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;

 

2.添加测试数据

创建生成n位随机字符串函数 rand_string(int  i);

创建生成一个 1-500之间的随机数rand_num();

#创建函数添加测试数据
#该函数(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 
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end 


#该函数(rand_num)会返回一个数字
create FUNCTION rand_num()
RETURNS int(5)
BEGIN
 DECLARE i int default 0;
 set i =floor(1+RAND()*500);
 return i;
END

   创建储存过程的函数 insert_emp(int min , int max);

表示添加  min+1  到  max 条数据

create procedure insert_emp(in start int(10),in max_numint(10))
begin
declare iint 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 

   执行储存过程(调用)

call insert_emp (100000,40000000); 

 

开始测试

常用mysql语句

显示当前数据库中与版本号相关的东西

SHOW VARIABLES LIKE '%version%';

查看当前的通用日志查询是否开启，如果general_log的值为ON则为开启，为OFF则为关闭（默认情况下是关闭的）。

SHOW VARIABLES LIKE '%general%';

查看当前查询日志输出的格式

SHOW VARIABLES LIKE '%log_output%';

如何开启MySQL通用查询日志，以及如何设置要输出的通用日志输出格式呢？

开启通用日志查询： set global general_log=on;

关闭通用日志查询： set globalgeneral_log=off;

设置通用日志输出为表方式： set globallog_output=’TABLE’;

设置通用日志输出为文件方式： set globallog_output=’FILE’;

设置通用日志输出为表和文件方式：set global log_output=’FILE,TABLE’;

（注意：上述命令只对当前生效，当MySQL重启失效，如果要永久生效，需要配置my.ini）

my.ini  文件的配置:

查看当前慢查询日志的开启情况

SHOW VARIABLES LIKE '%quer%';

 

--查询慢查询时间 show variables like 'long_query_time'; --修改慢查询时间 set long_query_time=1; ---但是重启mysql之后，long_query_time依然是my.ini中的值 查看有多少条慢查询语句 SHOW GLOBAL STATUS LIKE '%slow_queries%';

通过日志查看 那些语句超过设置查询时间 进行优化