MYSQL学习笔记——sql语句优化工具
优化sql：思路：
使用explan-》先查询type类型看看是all还是ref，然后判断
possible_keys （显示可能应用在这张表中的索引, 一个或多个。查询涉及到的字段是若存在索引, 则该索引将被列出, 但不一定被查询实际使用） ；
如果这个值没有达到预期的效果（比如说本来预料到可能使用某个索引但是这里没显示），就去查看sql语句哪里出问题了，
如果这里显示了预料的可能使用的索引，之后再去查看key :这一列，看看是否用到了索引
如果没有索引，再去查看sql语句where条件哪里出问题，
最后再去查看row这一列查询了几行
如果几亿的并发量突然冲过来怎么办：
思路一：扔流量，当前面一个亿的用户点击抢红包的时候，不走数据库，直接在页面返回失败提醒（相当于这一亿用户直接扔掉，挡在页面上，他们一点击抢红包，页面直接返回失败，不把数据走进服务器处理），后面的用户在进行异步处理，redis，负载均衡等操作
我们要对sql语句进行优化，第一步肯定是找到执行速度较慢的语句，那么怎么在一个项目里面定位这些执行速度较慢的sql语句呢？下面就介绍一种定位慢查询的方法。
1.1、数据库准备
首先创建一个数据库表：
|
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE emp(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '编号',ename VARCHAR(20) NOT NULL DEFAULT "" COMMENT '名字',job VARCHAR(9) NOT NULL DEFAULT "" COMMENT '工作',mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级编号',hiredate DATE NOT NULL COMMENT '入职时间',sal DECIMAL(7,2) NOT NULL COMMENT '薪水',comm DECIMAL(7,2) NOT NULL COMMENT '红利',deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号')ENGINE=InnoDB DEFAULT CHARSET=utf8; |
然后我们构建一个存储函数，这个存储函数会返回一个长度为参数n的随机字符串：
delimiter $$
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
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 $$
delimiter ;
接下来我们再创建一个存储函数，该存储函数会返回一个随机int值： delimiter $$ create function rand_num( ) returns int(5) begin declare i int default 0; set i = floor(10+rand()*500); return i; end $$ delimiter ;
然后我们利用刚刚创建的两个存储函数创建一个存储过程，该存储过程包含一个参数，该参数表示插入数据表emp的数据条数
delimiter $$
create procedure insert_emp(in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into emp values (i ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;
最后，我们调用改改创建的存储过程，对emp表插入1000w条数据：
call insert_emp(10000000);
1.2、查看慢查询
我们可以用以下命令查看慢查询次数：
|
1
|
show status like 'slow_queries'; |
现在在mysql中敲入该命令，可以看到value为1，这个慢查询就是由刚刚批量插入1000w条数据产生。
使用该命令只能查看慢查询次数，但是我们没有办法知道是哪些查询产生了慢查询，如果想要知道是哪些查询导致的慢查询，那么我们必须修改mysql的配置文件。打开mysql的配置文件（windows系统是my.ini,linux系统是my.cnf），在[mysqld]下面加上以下代码：
|
1
2
|
log-slow-queries=mysql_slow.loglong_query_time=1 |
此时我们在mysql中运行以下命令，可以看到slow_query_log是ON状态，log_file也是我们指定的文件：
mysql> show variables like 'slow_query%'; +---------------------+------------------------------+ | Variable_name | Value | +---------------------+------------------------------+ | slow_query_log | ON | | slow_query_log_file | mysql_slow.log | +---------------------+------------------------------+ 2 rows in set (0.00 sec) 运行以下命令我们可以看到我们设定的慢查询时间也生效了，此时只要查询时间大于1s，查询语句都将存入日志文件。 mysql> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec) 现在我们运行一个查询时间超过1s的查询语句： mysql> select * from emp where empno=413345; +--------+--------+----------+-----+------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+--------+----------+-----+------------+---------+--------+--------+ | 413345 | vvOHUB | SALESMAN | 1 | 2014-10-26 | 2000.00 | 400.00 | 11 | +--------+--------+----------+-----+------------+---------+--------+--------+ 1 row in set (6.55 sec) 然后查看mysql安装目录下的data目录，该目录会产生一个慢查询日志文件：mysql_slow.log，该文件内容如下： /usr/local/mysql/bin/mysqld, Version: 5.1.73-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument # Time: 141026 23:24:08 # User@Host: root[root] @ localhost [] # Query_time: 6.547536 Lock_time: 0.002936 Rows_sent: 1 Rows_examined: 10000000 use temp; SET timestamp=1414337048; select * from emp where empno=413345;
在该日志文件中，我们可以知道慢查询产生的时间，最终产生了几行结果，测试了几行结果，以及运行语句是什么。在这里我们可以看到，这条语句产生一个结果，但是检测了1000w行记录，是一个全表扫描。
二、Explain执行计划
慢查询日志可以帮助我们把所有查询时间过长的sql语句记录下来，在优化这些语句之前，我们应该使用explain命令查看mysql的执行计划，寻找其中的可优化点。
explain命令的使用十分简单，只需要"explain + sql语句"即可，如下命令就是对我们刚刚的慢查询语句使用explain之后的结果：
mysql> explain select * from emp where empno=413345\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000351
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
可以看到，explain命令的结果一共有以下几列：id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra，这些列分别代表以下意思：
1、id：SELECT识别符。这是SELECT的查询序列号；
2、select_type：查询类型，主要有PRIMARY(子查询中最外层查询）、SUBQUERY（子查询内层第一个SELECT）、UNION（UNION语句中第二个SELECT开始后面所有SELECT）、SIMPLE（除了子查询或者union之外的其他查询）；
3、table：所访问的数据库表明；
4、type：对表的访问方式，包括以下类型all(全表扫描)，index（全索引扫描），rang（索引范围扫描），ref（join语句中被驱动表索引引用查询），eq_ref（通过主键或唯一索引访问，最多只会有一条结果），const（读常量，只需读一次），system（系统表。表中只有一条数据），null（速度最快）。
访问类型, 显示查询使用了何种类型, 从最好到最差依次是 :
system>const>eq_ref>ref>range>index>ALL
一般来说, 要保证查询至少达到range级别, 最好能达到ref
system : 表只有一行记录(等于系统表), 这是const类型的特例, 平时不会出现
const : 表示通过索引1次就找到了, const用于比较primary key或者unique索引。因为只匹配一行数据, 索引很快, 如将主键置于where列表中, MySQL就能将该查询转换为一个常量
eq_ref : 唯一性索引扫描, 对于每个索引键, 表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref : 非唯一性索引扫描, 返回匹配某个单独值的所有行。本质上也是一种索引访问, 它返回所有匹配某个单独值的行, 可能会找到多个符合条件的行, 所以这个应该属于查找和扫描的混合体
range : 只检索给定范围的行, 使用一个索引来选择行。key列显示使用了哪个索引, 一般就是在where语句中出现了between, < ,> ,in等的查询。这种范围索引扫描比全表扫描要好, 因为它只需要开始于索引的某一点, 而结束于另一点, 不用扫描全部索引。
ndex : Full Index Scan, index与ALL的区别为index类型只遍历索引树。这通常比ALL快, 因为索引文件通常比数据文件小。(也就是说虽然ALL和Index都是读全表, 但index是从索引中读取的, 而all是从硬盘中读取的)
5、possible_keys：查询可能使用到的索引；
6、key：最后选用的索引；
7、key_len：使用索引的最大长度；
8、ref：列出某个表的某个字段过滤；
9、rows：估算出的结果行数；
10、extra：查询细节信息，可能是以下值：distinct、using filesort（order by操作）、using index（所查数据只需要在index中即可获取）、using temporary（使用临时表）、using where（如果包含where，且不是仅通过索引即可获取内容，就会包含此信息）。
这样，通过"explain select * from emp where empno=413345\G"命令的输出，我们就可以清楚的看到，这条查询语句是一个全表扫描语句，查询时没有用到任何索引，所以它的查询时间肯定会很慢。
三、Profiling 的使用
mysql除了提供explain命令用于查看命令执行计划外，还提供了profiling工具用于查看语句查询过程中的资源消耗情况。首先我们要使用以下命令开启Profiling功能：
|
1
|
set profiling = 1; |
接下来我们执行一条查询命令：
mysql> select * from emp where empno=413345; +--------+--------+----------+-----+------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+--------+----------+-----+------------+---------+--------+--------+ | 413345 | vvOHUB | SALESMAN | 1 | 2014-10-26 | 2000.00 | 400.00 | 11 | +--------+--------+----------+-----+------------+---------+--------+--------+ 1 row in set (6.44 sec)
在开启了Query Profiler功能之后,MySQL就会自动记录所有执行的Query的profile信息了。 然后我们通过以下命令获取系统中保存的所有 Query 的 profile 概要信息：
mysql> show profiles; +----------+------------+--------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------+ | 1 | 0.00053000 | show tables | | 2 | 0.07412700 | select * from dept | | 3 | 0.06743300 | select * from salgrade | | 4 | 6.44056000 | select * from emp where empno=413345 | +----------+------------+--------------------------------------+ 4 rows in set (0.00 sec) 然后我们可以通过以下命令查看具体的某一次查询的profile信息： mysql> show profile cpu, block io for query 4; +--------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000107 | 0.000072 | 0.000025 | 0 | 0 | | Opening tables | 0.000021 | 0.000018 | 0.000003 | 0 | 0 | | System lock | 0.000006 | 0.000004 | 0.000001 | 0 | 0 | | Table lock | 0.000009 | 0.000008 | 0.000001 | 0 | 0 | | init | 0.000034 | 0.000033 | 0.000002 | 0 | 0 | | optimizing | 0.000012 | 0.000011 | 0.000001 | 0 | 0 | | statistics | 0.000014 | 0.000012 | 0.000001 | 0 | 0 | | preparing | 0.000013 | 0.000012 | 0.000002 | 0 | 0 | | executing | 0.000005 | 0.000005 | 0.000016 | 0 | 0 | | Sending data | 6.440260 | 7.818553 | 0.178155 | 0 | 0 | | end | 0.000008 | 0.000006 | 0.000011 | 0 | 0 | | query end | 0.000002 | 0.000002 | 0.000003 | 0 | 0 | | freeing items | 0.000030 | 0.000013 | 0.000017 | 0 | 0 | | logging slow query | 0.000001 | 0.000000 | 0.000001 | 0 | 0 | | logging slow query | 0.000035 | 0.000020 | 0.000015 | 0 | 0 | | cleaning up | 0.000003 | 0.000003 | 0.000000 | 0 | 0 | +--------------------+----------+----------+------------+--------------+---------------+ 16 rows in set (0.00 sec) 该profile显示了每一步操作的耗时以及cpu和Block IO的消耗，这样我们就可以更有针对性的优化查询语句了。可以看到，由于这是一次全表扫描，
这里耗时最大是在sending data上。除了这种情况，以下几种情况也可能耗费大量时间：converting HEAP to MyISAM(查询结果太大时,把结果放在磁盘)、
create tmp table(创建临时表,如group时储存中间结果)、Copying to tmp table on disk(把内存临时表复制到磁盘)、locked(被其他查询锁住) 、
logging slow query(记录慢查询)。
