一、数据库服务器的优化步骤:
二、查看系统性能参数:
SHOW GLOBAL|SESSION STATUS LIKE '参数';| 参数名称 | 对应含义 |
| Com_select | 执行select操作的次数，一次查询只会累加1 |
| Com_insert | 执行insert操作的次数，对于批量插入的INSERT操作，只会累加1 |
| Com_update | 执行UPDATE操作的次数 |
| Com_delete | 执行DELETE操作的次数 |
| Innodb_rows_read | select语句返回的行数 |
| Innodb_rows_inserted | 执行INSERT操作插入的行数 |
| Innodb_rows_updated | 执行UPDATE操作更新的行数 |
| Innodb_rows_deleted | 执行DELETE操作删除的行数 |
| Connections | 试图连接MySQL服务器的次数 |
| Uptime | 服务器工作时间 |
| Slow_queries | 慢查询的次数 |
三、统计SQL的查询成本:last_query_cost
一条SQL查询语句在执行前需要确定查询的执行计划，如果存在多种执行计划的话会从中选择成本最小的一个作为最终执行的执行计划。如果想要查看某条SQL语句的查询成本，可以在执行完毕这条SQL语句之后通过查看当前会话中的last_query_cost变量值得到当前查询的成本。它通常也是评价一个查询的执行效率的一个常用指标，对应的是SQL语句需要读取页的数量。
SHOW GLOBAL|SESSION STATUS LIKE 'last_query_cost';两点结论:
1.位置决定效率：如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
2.批量决定效率：如果从磁盘中对单一页进行随机读，那么效率是很低的(差不多10ms)，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取
四、定位执行慢的SQL:慢查询日志
MySOL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long_query_time值的SQL，则会被记录到慢查询日志中。 long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了最大忍耐时间值。它的主要作用是帮助发现那些执行时间特别长的SQL 查询，并且有针对性地进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。
默认情况下，MySQL数据库没有开启慢查询日志，需要手动来设置这个参数。如果不是调优需要的话一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响,慢查询日志支持将日志记录写入文件。
1.开启慢查询日志参数:
查看慢查询日志是否开启:SHOW VARIABLES LIKE '%slow_query_log';
开启慢查询日志:SET GLOBAL slow_query_log = 'ON';
2.修改long_query_time阈值:
查看慢查询时间阈值:SHOW VARIABLES LIKE '%long_query_time%';
修改慢查询时间阈值(global和session都需要修改):SET GLOBAL long_query_time = 1;+SET long_query_time = 1;
3.补充说明:
除了上述变量，控制慢查询日志的还有一个系统变量:min_examined_row_limit,这个变量是指查询扫描过的最少记录数。这个变量和查询执行时间共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time的值，那么这个查询就被记录到慢查询日志中;反之，则不被记录到慢查询日志中。
4.关闭慢查询日志:
方式一:永久性关闭，修改my.cnf或者my.ini文件，把[mysqld]组下的slow_query_log的值设置为OFF，修改保存后，再重启MySQL服务。
方式二:临时性方式
查看慢查询日志是否开启:SHOW VARIABLES LIKE '%slow_query_log';
关闭慢查询日志:SET GLOBAL slow_query_log = 'OFF';
5.删除慢查询日志:
使用SHOW语句查看慢查询日志信息:SHOW VARIABLES LIKE 'slow_query_log%';
在该目录下手动删除慢查询日志文件即可
可以使用命令mysqladmin flush-logs来重新生成查询日志文件:
mysqladmin -uroot -p flush-logs slow五、查看SQL执行成本:SHOW PROFILE
SHOW PROFILE是MySQL提供的可以用来分析当前会话中SQL都做了什么，执行的资源消耗情况的工具，可用于SQL调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。
SHOW PROFILES;
SHOW PROFILE CPU|BLOCK|IO FOR QUERY QUERY_ID;SHOW PROFILE常用的查询参数:
| 参数名称 | 参数说明 |
| ALL | 显示所有的开销信息 |
| BLOCK IO | 显示块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'中的任何一条，表明当前这条SQL语句需要进行优化。
六、分析查询语句Explain
定位了查询慢的SQL之后，可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。MySQL中有专门负责优化SELECT语句的优化器模块，主要通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划。能够获得:表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询。
1.基本语法:
EXPLAIN SELECT select_option;
DESCRIBE SELECT select_option;EXPLAIN语句输出的各个列的作用:
| 列名 | 说明 |
| id | 在一个大的查询语句中每一个SELECT都对应一个惟一的id |
| select_type | SELECT关键字对应的那个查询类型 |
| table | 表名 |
| possible_keys | 可能用到的索引 |
| key | 实际用到的索引 |
| key_len | 实际用到的索引长度 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数 |
| partitions | 匹配的分区信息 |
| type | 针对单表的访问方法 |
| filtered | 某个表经过条件过滤后剩余记录条数的百分比 |
| Extra | 一些额外的信息 |
2.EXPLAIN语句中各列的作用:
(1)table:不论查询语句有多复杂，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。s1为驱动表，s2为被驱动表。
(2)id:在一个大的查询语句中每一个SELECT关键字都对应一个惟一的id，查询优化器可能对涉及子查询的查询语句进行重写，转变为多表查询的操作。如果id相同，可以认为是同一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行;一个sql的查询趟数越少越好
(3)select_type:MySQL为每一个SELECT关键字代表的查询定义了一个称为select_type的属性，可以知道这个小查询在大查询中扮演的角色
| 名称 | 描述 |
| SIMPLE | Simple SELECT (not using UNlON or subqueries) |
| PRIMARY | Outermost SELECT |
| UNION | Second or later SELECT statement in a UNION |
| UNION RESULT | Result of a UNION |
| SUBQUERY | First SELECT in the subquery. 包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是 SUBQUERY |
| DEPENDENT SUBQUERY | First SELECT in a subquery, dependent on the outer query |
| DEPENDENT UNION | Second or later SELECT statement in a UNlON, dependent on the outer query |
| DERIVED | Derived table |
| MATERIALIZED | Materialized subquery |
| UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
| UNCACHEABLE UNION | The second or later select in a UNlON that belongs to an uncacheable subquery (see UNCACHEABLE SUBOUERY) |
(4)partitions:代表分区的命中情况，非分区表，该项为NULL
(5)type:执行计划的一条记录代表着MySQL对某个表的执行查询时的访问方法。性能从上往下依次降低，SQL优化至少要达到range级别，最好是consts级别
| 类型 | 说明 |
| system | 表中只有一条记录并且该表使用的存储引擎的统计数据是精确的 |
| const | 根据主键或者唯一二级索引列与常数进行等值匹配 |
| eq_ref | 连接查询时被驱动表通过主键或者唯一二级索引列等值匹配的方式进行访问 |
| ref | 通过普通的二级索引列与常量进行等值匹配时 |
| fulltext | |
| ref_or_null | 通过普通的二级索引列与常量进行等值匹配查询，该索引列的值可以为NULL时 |
| index_merge | 单表访问方法时在某些情况下可以使用intersection，union，sort-union这三种索引合并的方式 |
| unique_subquery | 在一些包含IN子查询的查询语句中，如果查询优化决定将IN子查询转化为EXISTS子查询而且该子查询可以使用到主键进行等值匹配 |
| index_subquery | |
| range | 索引获取某些范围区间的记录 |
| index | 使用索引覆盖但是需要扫描全部的索引记录 |
| ALL | 全表扫描 |
(6)possible_keys和key:possible_keys列表示在某个查询语句中对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。 key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。
(7)key_len:实际使用到的索引长度，判断是否充分利用到索引，主要针对于联合索引
(8)ref:当使用索引列等值查询时，与索引列进行等值匹配的对象信息
(9)rows:预估需要读取的记录条数，这个值越小越好
(10)filtered:某个表经过搜索条件过滤后剩余记录条数的百分比，如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件记录有多少条。更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数。
(11)Extra:一些额外信息
a.No tables used:当查询语句没有FROM子句时将会提示该额外信息
b.Impossible where:查询语句中的WHERE子句永远为FALSE时将会提示该信息
c.Using where:当使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件或者当使用索引访问来执行对某个表的查询，并且该语句的WHERE子句中有除了该索引包含的列之外的其他条件
d.No matching min/max row:当查询列表有MIN或者MAX聚合函数，但是没有符合WHERE子句中的搜索条件的记录
e.Using index:查询列表以及搜索条件中只包含属于某个索引的列
f.Using index condition:有些搜索条件中虽然出现了索引列但是不能使用索引
g.Using join buffer:在连接查询执行过程中，当被驱动的表不能有效利用索引加快访问速度，MySQL一般会为其分配一块叫做join buffer的内存块加快查询速度
h.Not exists:使用左外连接时如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件而且那个列又不允许存储NULL值
i.Using intersect(...)/union(...)/sort_union(...):准备使用Intersect/Union/Sort-Union索引合并的方式查询，...表示需要进行索引合并的名称
j.Zero Limit:当LIMIT子句的参数为0,表示不读取任何数据
k.Using filesort:进行排序时在内存中或者磁盘上进行排序
l.Using temporary:不能利用索引完成查询，通过建立内部临时表来执行查询
3.注意:
(1).EXPLAIN不考虑各种Cache
(2).EXPLAIN不能显示MySQL在执行查询时所作的优化工作
(3).EXPLAIN不会告知关于触发器、存储过程的信息或者用户自定义函数对查询的影响情况
七、分析优化器执行计划:trace
OPTIMIZER_TRACE是MySQL5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策，并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。此功能默认关闭，开启trace，并设置格式为JSON，同时设置trace最大能够使用的内存大小，避免解析过程中因为内存过小而不能够完整展示。
SET optimizer_trace = 'enabled=on',end_markers_in_json=on;
SET optimizer_trace_max_mem_size = 1000000;开启后可分析如下语句:
SELECT,INSERT,REPLACE,UPDATE,DELETE,DECLARE,CASE,IF,RETURN,CALL
可以查询information_schema.optimizer_trace知道是如何执行MySQL的:
SELECT * FROM information_schema.optimizer_trace;查询结果:
| 部分次序 | 说明 |
| 第一部分 | 查询语句 |
| 第二部分 | QUERY字段对应语句的跟踪信息 |
| 第三部分 | 跟踪信息过长时被截断的跟踪信息的字节数 |
| 第四部分 | 执行跟踪语句的用户是否具有查看对象的权限，当不具有权限时，该列信息为1且TRACE字段为空 |
八、MySQL监控分析视图-sys schema
Sys schema视图摘要
| 相关内容 | 说明 |
| 主机相关 | 以host_summary开头,主要汇总了IO延迟信息 |
| InnoDB相关 | 以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息 |
| I/O相关 | 以io开头，汇总了等待I/O、I/O使用量的情况 |
| 内存使用情况 | 以memory开头，从主机、线程、事件等角度展示内存的使用情况 |
| 连接与会话信息 | processlist和session相关视图，总结了会话相关信息 |
| 表相关 | 以schema_table开头的视图，展示了表的统计信息 |
| 索引信息 | 统计了索引的使用情况，包含冗余索引和未使用的索引情况 |
| 语句相关 | 以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息 |
| 用户相关 | 以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息 |
| 等待事件相关信息 | 以wait开头，展示等待事件的延迟情况。 |
1173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
