MySQL优化 - 慢查询

MySQL如何优化

• 索引
• SQL语句优化
• 分表分库、读写分离

主要是上面三部分优化入手，其次的才是硬件部分。

数据库设计

在设计数据库时，要考虑建立冗余较小、结构合理等规则，在关系数据库中这种规则叫做“范式”。范式要求某一设计要求符合其规定，想要设计一个合格的数据库就要遵守一定的范式（数据库范式）。

三大范式

创建数据库表时，必须要遵守的三大范式。

一、第一范式
第一范式是指属性的原子性的约束，要求属性(Colum)具有原子性，不可再分解 (只要是关系型数据库都满足第一范式）

二、第二范式
第二范式是对记录的惟一性的约束，表中的记录是唯一的, 就满足第二范式, 通常我们设计一个主键来实现，主键不能包含业务逻辑。

三、第三范式
第三范式是对字段冗余性的约束，它要求表中字段没有冗余。 没有冗余的数据库设计可以做到。

设计数据库表一定要遵守这三大范式吗？
不一定，没有冗余数据的数据库未必就一定是最好的，最适合项目的。在实际情况种，存在“反范式”的情况也是有的。为了提升效率，降低范式标准，设计适当的冗余是可以的。在某些情况下，降低范式增加字段，允许冗余可能会更好。

SQL优化 - 慢查询

想要优化SQL，那么你得需要定位到那些SQL花费时间长才行。
下面几个命令可以帮助你排查：

show status 命令

命令	详细说明
show status like ‘uptime’;	mysql已启动时长
show stauts like ‘com_select’;	显示数据库查询数据次数
show stauts like ‘com_insert’;	显示数据库增加数据次数
show stauts like ‘com_update’;	显示数据库修改数据次数
show stauts like ‘com_delete’;	显示数据库删除数据次数
show status like ‘connections’;	显示当前数据库连接数
show global status like ‘slow_queries’;	显示全局数据库慢查询次数,不加global的话只能查到当前会话(当次连接)的慢查询次数
show [session/global] status like …	指定命令范围，默认session为当前会话的执行，加上global可查看全局范围，而不是本次会话(连接)范围

比较常用的还是查慢查询次数的命令。

什么是慢查询？
在MySQL中，慢查询的定义简单来说就是 “10秒内没有得到SQL执行结果的就是慢查询”。

也就是说MySQL定义一个SQL是不是慢查询是根据时间定义的，同时也就是告诉我们MySQL默认慢查询的时间是10秒；慢查询的时间设定也是可以通过命令去修改的，最低可设定为1秒。

修改慢查询默认时长

// 查询慢查询时间
show variables like 'long_query_time';

// 修改慢查询默认时长（当次连接有效，重启后恢复为MySQL的my.ini文件的默认值）
set long_query_time=1;

慢查询实战模拟
模拟慢查询至少得需要一个拥有几百万数据的数据库吧，我这里网上搜索到有材料，选择好数据库（use databaes）执行建表语句，再创建函数，使用函数帮我们生成数据。（有点像我平时使用Postman的感觉，改天可以写一些关于Postman的博客）

// 创建下面的三个表

CREATE TABLE dept
( deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  /*编号*/
dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名称*/
loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
) ENGINE=InnoDB 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=InnoDB 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=InnoDB DEFAULT CHARSET=utf8;

// 创建存储函数

// 随机生成字符串的函数
CREATE DEFINER=`root`@`localhost` FUNCTION `rand_string`( n INT ) RETURNS varchar(255) CHARSET utf8
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
==============================分割线===================================
// 随机生成数字的函数
CREATE DEFINER=`root`@`localhost` FUNCTION `rand_num`() RETURNS int(5)
BEGIN
 DECLARE i int default 0;
 set i =floor(10+RAND()*500);
 return i;
END

// 创建存储过程

delimiter $$
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 $$

// 调用存储过程

// 执行，生成数据id从10000开始起步，生成2百万条，花费时间大概是500秒内（具体看电脑）
call insert_emp (10000,2000000); 

生成个百万条数据就行了（使查询数据时间超过1S的数据量就行），生成数据过程还算块，不会很耗费时间，我两百万数据才400多秒，具体看计算机配置。

如果你已将慢查询的时间修改为1S的话，可以执行SQL查询一下数据，肯定会超过1S；

// 表里有两百万条数据，我这么查法 肯定超过1秒啦
select * from emp;

你会发现执行时间已经达到慢查询时间了，这个时候你可以使用命令show status like 'slow_queries';查看慢查询的次数，多了一次。

知道慢查询次数有什么用？实际工作中我怎么知道具体那条SQL产生慢查询？有没有日志什么的记录下来了，我看日志就知道？答案是有的，往下看。

慢查询日志

默认情况下，MySQL不会记录慢查询，需要在启动时指定记录慢查询。想要通过日志的方式将慢查询记录到日志中的话就需要做些调整了。（我使用的MySQL5.6 -version）下面的命令可以帮助你快速的完成。

命令	详细说明
show variables like ‘slow_query_log’;	慢查询开启状态，ON为开启
show variables like ‘long_query_time’;	慢查询超过多少秒才记录
show variables like ‘slow_query_log_file’;	慢查询日志文件名
set global slow_query_log=‘ON’;	开启慢查询
set global slow_query_log_file=‘路径’;	设置慢查询日志存放的位置,通过my.ini文件的datdir属性也可以设置
set global long_query_time=1;	查询超过1秒就记录（自定义）

tips：粘贴使用的话可能会报错，引号的问题。

我在上面 “SQL优化 - 慢查询” 部分的 show status 命令那一块的内容有解释【global】加上这个参数表示全局范围，而不是默认的当前会话【session】级别哦。

通过上面的命令，你可以轻松的查询你的慢查询是否开启了，以及慢查询日志的存放位置，以及慢查询时长的修改。如果不想使用命令完成的话你修改MySQL的my.ini文件的slow_query_log、slow_query_log_file、long_query_time属性也是一样的，修改了配置重启一下。

完成上面的操作你可以执行一条查询时长超过1S的SQL语句，然后去生成慢查询日志的地方看下慢查询日志就OK啦，如下图示例。

这篇博客，主要内容偏慢查询，想要SQL优化首先要定位到问题，再去解决问题。知道慢查询的SQL才能去索引优化、语句优化。