MySQL 优化篇（一）
本次项目演示基于MySQL 官方提供的测试库
下载到本地后，解压执行：mysql -uroot -p -t < employees.sql 即可！
_测试库表关系_
MySQL 数据库的使用是非常的广泛，稳定性和安全性也非常好，经历了无数大小公司的验证。仅能够安装使用是远远不够的，MySQL 在使用中需要进行不断的调整参数或优化设置，才能够发挥 MySQL 的最大作用。下边的内容是我在观看康师傅《MySQL 系统教学》的总结，也作为自己的学习笔记，如果能够帮助到有需要的同志就更好了。MySQL 的优化可以从个方面来做：
查看系统性能参数
在MySQL中，可以使用SHOW STATUS语句查询一些MySQL数据库服务器的性能参数、执行频率。
SHOW STATUS 语句语法如下：
SHOW [GLOBAL | SESSION] STATUS LIKE '参数';
一些常用的性能参数如下：
- Connections：连接MySQL服务器的次数。
- Uptime：MySQL服务器的上线时间。
- Slow_queries:慢查询的次数。
- Innodb_rows_read:Selecti查询返回的行数
- Innodb_rows_inserted:执行INSERT操作插入的行数
- Innodb_rows_updated:执行JPDATE操作更新的行数
- Innodb_rows_deleted:执行DELETE操作删除的行数
- Com_select:查询操作的次数。
- Com_insert:插入操作的次数。对于批量插入的INSERT操作，只累加一次。
- Com_update:更新操作的次数。
- Com_delete:时除操作的次数。
示例代码：
mysql> show session status like 'Uptime';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Uptime | 4289468 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> show session status like 'Innodb_rows%';
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| Innodb_rows_deleted | 158 |
| Innodb_rows_inserted | 319 |
| Innodb_rows_read | 64554321 |
| Innodb_rows_updated | 10314 |
+----------------------+----------+
4 rows in set (0.00 sec)
统计 SQL 的查询成本：last_query_cost
一条SQL查询语句在执行前需要硒定查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。
如果我们想要查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last-query-cost 变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是 SQL 语句所需要读取的页的数量。
示例：
mysql> select * from employees where emp_no = 20000;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 20000 | 1961-09-14 | Jenwei | Matzke | F | 1990-11-29 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
mysql> show status like 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。
SQL 查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：
- 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行
读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。- 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（差不多10s)，而采用顺
序读取的方式，批量对页进行读取灯平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。所以说，遇到 I/O 并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到
缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。
慢查询日志
MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long-query-time值的 SQL，则会被记录到慢查询日志中。long_quey_time 的默认值为10，意思是运行 10 秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。
它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sq执行超过5秒钟，我们就算慢SQL,希望能收集超过5秒的sql,结合explair进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。
开启慢查询日志参数
开启 slow_query_log
在使用前，我们需要先看下慢查询是否已经开启，使用下面这条命令即可：
SHOW VARIABLES LIKE '%slow_query_log';
mysql> SHOW VARIABLES LIKE '%slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)
默认慢查询日志是关闭的，我们需要通过以下命令打开慢查询日志：
set global slow_query_log = on;
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.01 sec)
然后再次查看慢查询日志是否开启，已经慢查询日志文件的位置：
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+----------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/VM-0-12-centos-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)
修改 long_query_time 阈值
接下来我们来看下慢查询的时间阈值设置，使用如下命令：
show variables like '%long_query_time%';
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
默认的时间是 10 秒，我们要修改的话可以通过以下语句设置：
-- #测试发现：设置global的方式对当前sess1on的long-query._time失效。对新连接的客户端有效。所以可以一并执行下述语句
set global long_query_time = 1;
show global variables like '%long_query_time%';
set long_query_time = 1;
show variables like '%long_query_time%';
补充：配置文件中一并设置参数
如下的方式相较于前面的命令行方式，可以看作是永久设置的方式
修改my.cnf文件，[mysqldi]下增加或修改参数1ong_query_time、slow_query_.log和s1ow_query_1og-fi1e后，然后重启MySQL服务器。
[mysqld]
slow_query_log=ON #开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/可以自定义.log
#慢查询日志的目录和文件名信息
long_query_time=3#设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE
如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为 hostname-slow.log。
除非特殊情况需要开启慢查询日志，否则都是建议把它关闭的。
查看SQL执行成本：SHOW PROFILE
Show Profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.01 sec)
通过下面的指令开启 PROFILE：
set profiling = 'ON';
开启后查询最近的运行结果：
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------+
| 1 | 0.00128450 | show variables like 'profiling' |
| 2 | 0.36276525 | select * from salaries where salary = 76712 |
| 3 | 0.00007850 | show porfiles |
| 4 | 0.00006475 | show porfiles |
| 5 | 0.00155875 | show variables like 'profiling' |
| 6 | 0.32965175 | select * from salaries where salary = 71091 |
| 7 | 0.00008000 | show porfiles |
+----------+------------+---------------------------------------------+
7 rows in set, 1 warning (0.00 sec)
通过 show profile 默认查询最后一条的记录，也可通过 show profile for query number; 查询指定的记录：
mysql> show profile for query 6;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000068 |
| Executing hook on transaction | 0.000003 |
| starting | 0.000007 |
| checking permissions | 0.000006 |
| Opening tables | 0.000034 |
| init | 0.000004 |
| System lock | 0.000008 |
| optimizing | 0.000009 |
| statistics | 0.000017 |
| preparing | 0.000021 |
| executing | 0.329401 |
| end | 0.000014 |
| query end | 0.000005 |
| waiting for handler commit | 0.000010 |
| closing tables | 0.000013 |
| freeing items | 0.000023 |
| cleaning up | 0.000012 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
show profile的常用查询参数：
①ALL：显示所有的开销信息
②BL0CKIO：显示块IO开销.
③CONTEXT SWITCHES：上下文切换开销。
④CPU：显示CPU开销信息.
⑤IPC：显示发送和接收开销信息
⑥MEMORY：显示内存开销信息。
⑦PAGE FAULTS：显示页面错误开销信息
⑧SOURCE：显示和Source_function、Source_file、Source_line 相关的开销信息，
⑨SWAPS：显示交换次数开销信息。
日常开发需要注意的结论：
①_converting HEAP to MyISAM_：查询结果太大，内存不够，数据往磁盘上搬了，
②_Creating tmp table_：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
③_Copying to tmp table on disk_：把内存中临时表复制到磁盘上，警惕！
④_locked_。
如果在show profile 诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。
注意：
不过SHOW PROFILE命令将被弃用，我们可以从 information_schema 中的 profiling 数据表进行查看。
