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%'; |
通过日志查看 那些语句超过设置查询时间 进行优化