MySQL性能优化
MySQL性能优化
MySQL性能优化之explain
在日常的MYSQL优化中我们常常看到这样一个关键词：explain，例如这种：
EXPLAIN SELECT * FROM Cloud_Order WHERE money > 10;
explain是什么呢？使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
首先让我们来看看使用EXPLAIN输入的结果
结果显示输出了结果一堆字段和对应的值，但是这些字段是什么意思？对应的值又是什么呢？如何通过这些字段来分析到SQL的性能并做出优化呢？别急，下面我们就一起来一一分析。
EXPLANIN字段分析
explain实践
说了这么多，实践才能出真知。下面我们通过一个简单的例子来优化我们一些不堪的SQL。
首先我们还是一张数据表举例。表结构如下。
这是一张典型的订单表，其他字段我们可以省略不看，我们可以只看一个money字段，这基本是订单表都会用到的字段。由于时间关系，事先我已经为这个表准备了一堆模拟数据。
从上图可以看出，表中已经有一万条数据，下面我们来写一个根据money条件来查询订单的SQL。
只能说上图的结果不尽人意。让我们回到之前explain字段的分析，其中type字段的值是ALL，按照分析来说，这个表用了全表搜索，我们应尽量避免！！！再看rows字段，值是16242，天啊！！所有记录都去请求了，那慢是有原因的。
好了，通过上面的数据分析，我们可以去想一下，money字段是否能加上索引来提升查询速度呢？因为上述结果中好像是没用到索引的。话不多说，我们来为money字段加上索引
加上索引之后，我们再用刚刚的EXPLAIN语句执行一下，见证奇迹的时候到了！
经过加上索引之后，相同的sql语句，得出的结果完全不一样，type字段变成了range，我们也看到key显示了money，证明了索引值被用上了。更重要的是rows字段变成了785，跟原来相比少了不知道多少，可想而知性能有了多大的提高！
MySQL性能优化之慢查询
1.慢查询的用途
它能记录下所有执行超过long_query_time时间的SQL语句，帮我们找到执行慢的SQL，方便我们对这些SQL进行优化。
2.查看是否开启慢查询
show variables like ‘slow_query%‘;
slow_query_log = off，表示没有开启慢查询
slow_query_log_file 表示慢查询日志存放的目录
3.开启慢查询（需要的时候才开启，因为很耗性能，建议使用即时性的）
方式一：（即时性的，重启mysql之后失效，常用的）
set global slow_query_log=1; 或者 set global slow_query_log=ON;
开启之后 我们会发现 /var/lib/mysql下已经存在 localhost-slow.log了，未开启的时候默认是不存在的。
方式二：（永久性的）
在/etc/my.cfg文件中的[mysqld]中加入：
slow_query_log=ON slow_query_log_file=/var/lib/mysql/localhost-slow.log
4.设置慢查询记录的时间
查询慢查询记录的时间：show variables like ‘long_query%‘，默认是10秒钟，意思是大于10秒才算慢查询。
我们现在设置慢查询记录时间为1秒：set long_query_time=1;
5.执行select count(1) from order o where o.user_id in (select u.id where users);
因为我们开启了慢查询，且设置了超过1秒钟的就为慢查询，此sql执行了24秒，所以属于慢查询。
我们在日志中查看：
more /var/lib/mysql/localhost-slow.log，
我们可以看到查询的时间，用户，花费的时间，使用的数据库，执行的sql语句等信息。在生产上我们就可以使用这种方式来查看 执行慢的sql。
6.查询慢查询的次数：show status like ‘slow_queries‘;
在我们重新执行刚刚的查询sql后，查询慢查询的次数会变为8
当然，用 more /var/lib/mysql/localhost-slow.log 也是可以看到详细结果的。
在生产中，我们会分析查询频率高的，且是慢查询的sql，并不是每一条查询慢的sql都需要分析。
7.慢查询日志分析工具Mysqldumpslow
由于在生产上会有很多慢查询，所以采用上述的方法查看慢查询sql会很麻烦，还好MySQL提供了慢查询日志分析工具Mysqldumpslow。
其功能是, 统计不同慢sql的出现次数(Count)，执行最长时间(Time)，累计总耗费时间(Time)，等待锁的时间(Lock)，发送给客户端的行总数(Rows)，扫描的行总数(Rows)
（1）查询Mysqldumpslow的帮助信息，随便进入一个文件夹下，执行：mysqldumpslow --help
查看mysqldumpslow命令安装在哪个目录：whereis mysqldumpslow
说明：
- -s，是order的顺序，主要有c（按query次数排序）、t（按查询时间排序）、l（按lock的时间排序）、r （按返回的记录数排序）和 at、al、ar，前面加了a的代表平均数
- -t，是top n的意思，即为返回前面多少条的数据
- -g，后边可以写一个正则匹配模式，大小写不敏感的
- -r：倒序
（2）案例：取出耗时最长的两条sql
格式：mysqldumpslow -s t -t 2 慢日志文件
mysqldumpslow -s t -t 2 /var/lib/mysql/localhost-slow.log
参数分析：
- 出现次数(Count),
- 执行最长时间(Time),
- 累计总耗费时间(Time),
- 等待锁的时间(Lock),
- 发送给客户端的行总数(Rows),
- 扫描的行总数(Rows),
- 用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).
这种方式更加方便，更加快捷！
8.show profile
用途：用于分析当前会话中语句执行的资源消耗情况
（1）查看是否开启profile，mysql默认是不开启的，因为开启很耗性能
show variables like ‘profiling%‘;
（2）开启profile（会话级别的，关闭当前会话就会恢复原来的关闭状态）
set profiling=1; 或者 set profiling=ON;
（3）关闭profile
set profiling=0; 或者 set profiling=OFF;
（4）显示当前执行的语句和时间
show profiles;
（5）显示当前查询语句执行的时间和系统资源消耗
show profile cpu,block io for query 4;（分析show profiles中query_id等于4的sql所占的CPU资源和IO操作）
或者直接 ： show profile for query 4;
