MySQL--性能优化总结篇

59 阅读27分钟

本文正在参加「金石计划 . 瓜分6万现金大奖」

👨‍🎓作者:Java学术趴

🏦仓库:GithubGitee

✏️博客:CSDN掘金InfoQ云+社区

💌公众号:Java学术趴

🚫特别声明:原创不易,未经授权不得转载或抄袭,如需转载可联系小编授权。

🙏版权声明:文章里的部分文字或者图片来自于互联网以及百度百科,如有侵权请尽快联系小编。微信搜索公众号Java学术趴联系小编。

☠️每日毒鸡汤:堕落的一天

MySQL高级--性能优化

1. MySQL

  • MySQL是一个关系型数据库,由瑞典MySQL AB公司开发,目前属于Oracle公司。
  • MySQL可以定制的,采用了GPL协议,修改源码可以定制属于自己的MySQL系统。

2. MySQL逻辑架构

2.1 MySQL底层架构

  • 和其它数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上。
  • 插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。

  • 链接层: 最上层是一些客户端和链接服务,包括本地sock通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的通信。主要完成一些类似于链接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • 服务层: 第二层架构主要完成大多少的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及内部内置函数的执行。
  • 引擎层: 存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。

2.2 MyISAM和InnoDB引擎

3. 索引优化分析

3.1 手写SQL和机读SQL

机器读的SQL和我们写的SQL是不一样的。

几种表关联方式

3.2 索引

3.2.1 索引概念

  • 索引(index)是帮助 MySQL高效查询获取数据的数据结构。
  • 索引的本质:索引是数据结构,是一种排好序的快速查找数据结构。
  • 索引的目的在于提高查询效率,可以类比字典,如果要查”mysql“这个单词,我们肯定需要定位到 m 字母,然后从上往下找y字母,在找到剩下的 sql。如果没有索引,那么你可能需要 a---z 依次查询。
  • 索引之所以查的快是因为已经排序好了,所以查询的速度比较快。
  • 在数据之外, 数据库系统还维护着满足特定查找算法的数据结构, 这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。

左边是数据表,一共两列七条记录,最左边的是数据记录的物理地址。

3.2.2 索引的分类

  • 单一索引: 即一个索引只包含单个列,一个表可以有多个单列索引。
  • 唯一索引: 索引列的值必须唯一,但允许为空。
  • 复合索引: 即一个索引包含多个列。

3.2.3 MySQL索引结构

  • BTree索引: 检索过程

  • Hash索引
  • full-text
  • R-Tree索引

3.3.4 适合创建索引的场景

  • 主键自动建立一个唯一索引。
  • 频繁作为查询条件的字段应该创建索引。
  • 查询中与其他表关联的字段,外键关系建立索引。
  • 频繁更新的字段不适合创建索引(因为每次更新不单单的更新表中的数据,还要更新索引)
  • where条件里用不到的字段不创建索引。
  • 单键/组合索引的选择问题?(一般在高并发的情况下倾向创建组合索引)
  • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度(原始数据已经排序好了,不用在使用索引进行排序)
  • 查询中统计或者分组字段(分组的前提是数据已经排序好了,既然排序就与索引息息相关)

3.3.5 不适合创建索引的场景

  • 表记录少:原始数据很少,不需要索引查询的速度也非常快。
  • 经常增、删、改的表:提高查询速度的同时,在进行数据增删改的操作时, 操作原始数据的同时还需要操作索引文件,会占用大量的性能。
  • 数据重复且分布平均的表字段:索引一般使用在经常查询和经常排序的字段。如果某个字段存在大量重复的元素,为它建立索引就没有太大的意义。

3.3 MySQL优化器

  • MySQL中有专门负责优化SELECT语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供它认为最优的执行计划(他认为最优的数据检索方式,但不见得是DBA认为最优的,这部分最消耗时间。)
  • 当客户端向MySQL请求一条Query,命令解析器模块完成请求分类,区别出是 SELECT 并转发给MySQL Query OPtimizer时,MySQL Query OPtimizer首先会对整条Query进行优化,处理掉了一些常量表达式的预算,直接换算成常量值。并且Query中的查询条件进行简化和转换,如去掉一些无用或者显而易见的条件、结构调整等。然后分析 Query 中Hint信息(如果有),看显示的Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计算,则会读取所涉及对象的统计信息,根据Query进行写响应的计算分析,然后再得出最后的执行计划。

3.4 MySQL常见瓶颈

  • CPU: CPU在饱和的时候一般会发生在数据装入内存或者从磁盘上读取数据的时候。
  • IO: 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。
  • 服务器硬件的性能瓶颈:top、free、iostat和vmstat来查看系统的性能状态。

3.5 Explain查看执行计划

  • Explain + SQL : 查看执行计划包含的信息。(在正常的SQL语句之间加Explain查看执行计划信息)

3.5.1 执行计划包含的查询信息

不加\G横向显示

加\G纵向展示

3.5.2 表的读取顺序

id: select查询的序列号(是一组数字),表示查询中执行select子句或操作的顺序。分为三种情况

  • id相同,执行顺序由上至下。
  • id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。
  • id相同不同,同时存在。

3.5.3 数据读取操作的操作类型

select_type : 查询的类型。主要用于区别普通查询、联合查询、子查询等的复杂查询。

  • SIMPLE :简单的select查询,查询中不包含子查询或者UNION。
  • PRIMARY : 查询中若包含任何复杂的子查询部分,这个类型代表最外层的。
  • SUBQUERY: 在SELECT或WHERE列表中包含了子查询。
  • DERIVED: 在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里。
  • UNION: 若第二个SELECT出现在UNION之后,则被标记为UNION。若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED。
  • UNION RESULT: 从UNION表获取结果的SELECT。

3.5.4 当前数据表名

table: 查询当前这一行数据是关于哪个表的。

3.5.5 访问类型排序

type: 显示查询使用的是何种类型。

从最好到最差一次是:system>const>eq_ref>ref>range>index>ALL

一般来说,得保证查询至少达到range级别,最好能达到ref级别。

  • system: 表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计。
  • const: 表通过索引一次就能找到了,const用于比较 primary keyunique 索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转换为一个常量。
  • eq_ref: 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。 常见于主键或唯一索引扫描。
  • ref: 非唯一性索引扫描,返回匹配某个单独值的所有行。 本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该数据查询和扫描的混合体。
  • range: 只检索给定范围的行,使用一个索来选择行。 key列显示使用了哪个索引。一般就是在你的where语句中出现了 between、<、>、in 等的查询。这种范围扫描索引比全表扫描要好,因为是从某一个节点开始,而结束于某一个节点,不用全局扫描。
  • index: Full Index Scan ,index和ALL区别为index类型只遍历所引树。这通常比ALL快,因为索引文件通常比数据文件小。(虽然 ALLindex都是全读,但是ALL是从磁盘上读取原始数据,而index是读取全部的索引。)
  • all: Full Table Scan ,将遍历源数据的全表以找到匹配的数据。

3.5.6 显示可能应用在这张表中的索引

  • possible_keys :查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。如果没有则为NULL。

3.5.7 实际用到的索引

  • key: 实际使用的索引,如果为NULL,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中。

我们真正使用的是key中存在的索引。

3.5.8 索引长度

  • key_len : 显示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好。
  • key_len显示的值为索引字段的最大可能长度, 并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出来的。

3.5.9 索引对应的列

  • ref: 显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些劣或常量被用于查找查找索引列上的值。

3.5.10 花费行数

  • rows: 根据表统计信息及索引选用情况,大概估算出找出所需记录需要读取的行数。

2.5.10 额外数据

  • Extra: 不适合在其他列显示但十分重要的额外信息。

额外属性

  • Using filesort: 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称之为“文件排序”。

这里创建的是一个聚合索引(col1,col2,col3),第二个SQL没有提示使用文件内部排序是因为使用列按照了索引的顺序(col1->col2->col3),但是第一个SQL没有使用到col2,产生了一个断层,此时就需要MySQL内部自己进行一次文件排序。在使用 OEDER BY 排序的时候一定要符合聚合索引的顺序。

  • Using temporary: 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序ORDER BY 和 分组查询 GROUP BY

这里创建的是一个聚合索引(col1,col2),第二个SQL在 GROUP BY 的时候没有按照聚合索引的顺序,导致排序和分组都会提示相应的错误,一定要按照索引的顺序进行分组和排序。

  • Using index: 表示相应的select操作中使用了覆盖索引(Covering Index) ,避免访问了表的数据行,效果不错。如果同时出现了 using where,表示索引被用来执行索引键值的查找。如果没有同时出现 using where ,表示索引用来读取数据而非执行查找动作。

覆盖索引(Covering Index) : 就是 select 的查询的列从索引中就可以获取到,而不必去读取表中的原始数据,MySQL可以利用索引返回 select 列表中的字段,而不必根据索引再次读取数据文件,换句话说: 查询列要被索引覆盖。

// t1表中存在 index_col1_col2 聚合索引
select col1,clo2 from t1;
// 我们只查询 col1和col2,并且这两个字段的数据都可以从索引中获取,此时叫做索引覆盖
select col1 from t1;
// 查部分字段也是可以的
  • Using where: 说明使用了where过滤。
  • Using join buffer: 说明使用了表连接缓存。
  • impossible where: where子句中的值总是false,不能用来获取任何元组。(就比如找一个人,给定的查询条件,性别既是男性又是女性)

3.6 关联表如何加索引

  • 单表: 可以任意加索引,不用区分表。
  • 两张表关联: 左关联索引加在右表,右关联加在左表。当进行左右关联时为了不修改表的索引,可以对调两个表的关联位置,使存在索引表的索引生效。
  • 多张表关联: 多张表的连接方式和两张表的连接方式一致。都是左连接在右表建索引....

3.7 索引失效

  • 全值匹配

查询条件越加的细致所要付出的代价就要大一些,长度会变长等等...

  • 最佳左前前缀法则

如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最前列并且不跳过索引中的列。

生效原则:带头大哥不能死,中间兄弟不能断

使用聚合索引的时候,起始的查询字段必须使用索引对应的第一个字段,否则索引失效。

当聚合索引出现断层的时候,这个时候只有部分索引生效。

  • 不在索引列上做任何操作(计算、函数、类型转换等) ,会导致索引失效而转为全表扫描。

在存在索引的字段上使用函数之后索引会失效。

  • 存储引擎不能使用索引中范围条件右边的列。

范围索引之前的索引生效(包括范围的索引),范围之后的索引会失效。

  • 尽量使用覆盖索引(只使用索引中存在的字段,减少 SELECT * 的使用)。
  • MySQL在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。
  • IS NULL ,IS NOT NULL 也无法使用索引。
  • LIKE以通配符开头('%abc...')MySQL索引失效会变成全表扫描的操作。

只有模糊查询的占位符放到右边的时候索引才会生效。

当给定的需求就是模糊查询左右都需要占位符(like '%张三%')的时候该如何让索引生效?????

这个时候就需要使用覆盖索引来弥补占位符的失效,也就是查询的字段都是创建索引的字段,不能包含没有创建索引的字段,并且聚合索引需要满足最左匹配原则。

  • 字符串不加单引号索引失效。

  • 少用 or,用它来连接时会索引失效。

3.8 索引使用列题

例题1:全值匹配我最爱

假设:index(c1,c2,c3,c4)

在MySQL中存在优化器,他会自动把我们输入 4,3,2,1 的顺序转换为 1,2,3,4

此时生效的索引:c1,c2,c3,c4

例题2:范围查询

如果按照聚合索引的顺序查询,范围查询前的字段(包括范围字段)索引会生效,范围后边的索引失效

此时生效的索引:c1,c2,c3

如果不按照聚合索引的顺序查询,MySQL会使用自带的优化器把查询条件按照索引顺序进行排序,之后在进行筛选查询。

此时生效的索引:c1,c2,c3,c4

例题3:排序查询

排序时索引也用到了,只不过此时该索引不是用来查询,而是用来排序,explain没有展示出来

此时生效的索引:c1,c2

c1,c2索引都用到了,直接使用c3进行排序,此时和c4没有什么关系

此时生效的索引:c1,c2

c1,c2索引都用到了,此时直接使用c4排序,导致c3出现断层,MySQL优化器不能直接进行排序,在内部进行了一次filesort内排序 ,使SQL的性能下降。

c1索引用到了,c2,c3是按照顺序进行排序的,没有发生内排序,c2,c3索引也用到了,只不过用在了排序上,而不是用在查询上。

c1索引用到了,但是排序的索引顺序反了,发生了内排序。

第一个因为在条件中已经声明了索引c2,所以在排序的时候会忽略这个c2索引,不会发生内排序现象。

第二个因为在条件中没有声明c2,所以直接在排序中使用c2不会被忽略,c2、c3索引顺序相反会发生内排序现象。

排序默认是升序排序,但是此时非要实现降序排序,这就会导致MySQL发生内排序(filesort)

结论:

  • MySQL支持两种方式的排序,FileSort以及Index,Index效率高,它指MySQL扫描索引本身完成排序。FoleSort方式效率较低。

ORDER BY 满足两种情况,会使用Index排序

  • Order by 语句使用索引最左前列。
  • 使用Where子句与Order by 子句条件列组合满足索引最左前列。
  • 尽可能在索引列上完成排序操作,遵照索引建的最左前缀。

如果不在索引列上,filesort有两种算法:

  • 单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对他们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中。
  • 双路排序:MySQL4.1之前使用的是双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段,在Buffer进行排序,再从磁盘取其他字段。

例题:分组查询

  • 索引的作用是用于查询和排序,而不用于分组。但是分组的前提是排序,所以说分组和排序使用索引的法则差不多。
  • group by 基本上都要进行排序,会有临时表的产生。

只用到了c1索引

以下虽然使用带了 c1 索引,但是分组出现了非常大的错误,既发生了内排序,还产生了临时表,此时的性能非常的差。

例题:模糊查询

c1,c2,c3索引使用到了,like也属于范围查询,但是他区别于大于...查询的优势在于,如果like后面的条件是按照索引顺序来的,那么这个索引也会生效(前提是%通配符在左侧)

c1索引使用到了,模糊匹配以通配符开头,后边的索引全部失效。

c1索引被使用了

c1,c2,c3都使用到了,因为不是使用通配符开头的。


【索引优化口诀】
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写星;

3.9 索引优化总结

  • 对于单键索引,尽量选择针对当前查询过滤性更好的索引。
  • 在选择组合索引的时候,当前查询中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
  • 在选择组合索引的时候,尽量选择可以包含当前查询中的where中更多字段的索引。
  • 尽量通过分析统计西悉尼和调整查询的写法来达到选择合适索引的目的。

4. 查询截取分析

4.1 优化步骤

  • 慢查询的开启并捕获。
  • explain + 慢SQL分析。
  • show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况。
  • SQL数据库服务器的参数调优。

4.2 小表驱动大表

4.2.1 优化原则

优化原则:小表驱动大表,即小的数据集驱动大的数据集。

4.2.2 EXISTS

语法格式:


SELECT * FROM table WHERE EXISTS(subquery[子查询])

EXISTS特点:

  • EXISTS(subquery)只返回TRUE或者FALSE,因为资产寻中的 SELECT * 也可以是 SELECT 1 或者 SELECT 'X',官方说法是实际执行时会忽略 SELECT 清单,因此没有区别。
  • EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比。
  • EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代,何种最优需要根据场景具体分析。

4.2.3 IN 和 EXISTS 的区别

适用表的类型不同

  • in语句:是子查询为驱动表,外面的表为被驱动表,故适用于子查询结果集小而外面的表结果集大的情况。
  • exists语句:是外面的表为驱动表,子查询里面的表为被驱动表,故适用于外面结果集小而子查询结果集大的情况。

子查询关联不同

  • exists语句:一般都是关联子查询。对于关联子查询,必须先执行外层查询,接着对所有通过过滤条件的记录,执行内存查询。外层查询的内层查询相互依赖,一位外层查询会把数据传递给内层查询。
  • in语句:一般都是非关联子查询,非关联子查询查询则必须先完成内层查询,在执行外层查询。

执行次数不同

  • in语句:只执行一次,确定给定的值是否与子查询或列表中的值相匹配。in子查询的时候,首先查询子查询的表,然后将内标和外表做一个笛卡尔积,然后按照条件进行筛选,所以相对内表比较小的时候,in的速度较快。
  • exists语句:执行次数根据表的长度而定。只当一个子查询,检测行的存在,遍历循环外表,然后看外表中的记录有没有和内表的数据是一样的,匹配上的就将结果放入到结果集中。

4.3 ORDER BY 排序优化

4.3.1 ORDER BY 排序示例

排序时索引也用到了,只不过此时该索引不是用来查询,而是用来排序,explain没有展示出来

此时生效的索引:c1,c2

c1,c2索引都用到了,直接使用c3进行排序,此时和c4没有什么关系

此时生效的索引:c1,c2

c1,c2索引都用到了,此时直接使用c4排序,导致c3出现断层,MySQL优化器不能直接进行排序,在内部进行了一次filesort内排序 ,使SQL的性能下降。

c1索引用到了,c2,c3是按照顺序进行排序的,没有发生内排序,c2,c3索引也用到了,只不过用在了排序上,而不是用在查询上。

c1索引用到了,但是排序的索引顺序反了,发生了内排序。

第一个因为在条件中已经声明了索引c2,所以在排序的时候会忽略这个c2索引,不会发生内排序现象。

第二个因为在条件中没有声明c2,所以直接在排序中使用c2不会被忽略,c2、c3索引顺序相反会发生内排序现象。

排序默认是升序排序,但是此时非要实现降序排序,这就会导致MySQL发生内排序(filesort)

ORDER BY a DESC,b DESC : 此时索引生效,此时都是降序。

ORDER BY a ASC,b ASC: 此时索引生效,此时都是升序。

ORDER BY a DESC,b ASC: 此时索引不生效,既存在升序又存在降序。

4.3.2 效率声明

  • MySQL支持两种方式的排序,FileSort以及Index,Index效率高,它指MySQL扫描索引本身完成排序。FoleSort方式效率较低。

4.3.3 ORDER BY 满足两种情况,会使用Index排序

  • Order by 语句使用索引最左前列。
  • 使用Where子句与Order by 子句条件列组合满足索引最左前列。

4.3.4 如果不在索引列上,filesort有两种算法

  • 单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对他们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中。
  • 双路排序:MySQL4.1之前使用的是双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段,在Buffer进行排序,再从磁盘取其他字段。

4.3.5 提高ODERY BY 的速度

  • 存在order by 时使用 select * 是一个大忌,我们只将自己想要的字段查出来即可。(存在两个问题)

    1. 当查询的字段大小总和小于 max_length_for_sort_data,而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法——单路排序,否则用老算法——多路排序。
    2. 两种算法的数据都有可能超出 sort_buffer 的容量,超出之后,会创建 tmp 临时文件进行合并排序,导致多次 I/O ,但是使用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size
  • 尝试提高 sort_buffer_size

    1. 不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的。
  • 尝试提高 max_length_for_sort_data

    1. 提高这个参数,会增加用改进算法的概率 。但是如果设的太高,数据总容量超出 sort_buffer_size 的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率。

4.4 GROUP BY 分组优化

  • GROUP BY 实质是先排序后进行分组,遵照索引建的最佳左前缀。
  • 当无法使用索引列,增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size 参数的设置。
  • where高于having,能写在where限定的条件就不要去having限定了。
  • 其他的性质和ORDER BY 一致。

4.5 慢查询日志

4.5.1 慢查询日志定义

  • MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的SQL,则会被记录到慢查询日志中。
  • 具体指运行时间超过 long_query_time值的SQL,则会记录到慢查询日志中。 long_query_time的默认值为10,意思是运行10秒以上的语句。
  • 由他来查看哪些SQL超出了我们的最大忍耐时间值,比如一条SQL执行超过5秒,我们就算慢SQL,希望能收集超过5秒的SQL,集合之前explain进行全面分析。

4.5.2 慢查询

  • 默认情况下,MySQL数据库是没有开启慢查询日志, 需要我们手动来设置这个参数。
  • 如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看慢查询的状态


show variables like '%slow_query_log%';

默认情况下 slow_query_name = OFF,表示慢查询日志是禁用的。

默认情况下会给一个缺省的文件host_name-slow.log,当我们指定路径之后就会覆盖这个默认的缺省文件路径

开启/关闭慢查询日志


// 开启
set global slow_query_log = 1;
// 关闭
set global slow_query_log = 0;

slow_query_log = NO,此时代表慢查询日志开启。

以上这种配置方式只是临时生效,如果想要永久生效,就必须修改配置文件 my.cnf(其他系统变量也是如此)


修改my.cnf文件,在[mysqld]下增加或修改参数
slow_query_log = 1
slow_query_log_file = 慢查询日志文件存储地址

查看等待时间阈值


show variables like '%long_query_time%'l

这个是由参数long_query_time控制的,默认情况下long_query_time的值为10秒。

假设运行时间正好等于 long_query_time 的情况,并不会被记录下来。也就是说:在MySQL源码里是 判断大于 long_query_time,而非大于等于。

设置等待时间阈值


set global long_query_time = 3;
  • 即使我们修改了阈值之后,再次查询发现阈值没有发生变化,其实已经变化了并且已经生效了,此时需要重启MySQL服务。

  • 修改完等待阈值,如果不想重启服务器查询等待阈值,可以使用全局命令查看。

show global variables like '%long_query_time%';

4.6 Show Profile

4.6.1 show profile定义

  • show profile提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优测量。
  • 默认情况下,参数处于关闭状态,并保存最近15次的运行结果。

4.6.2 show profile 分析步骤

  1. 是否支持,查看当前版本的MySQL是否支持show profile 功能。

show variables like 'profiling';

当前版本的MySQL支持show profile 功能并且是关闭状态。

  1. 开启功能,默认是关闭,使用前需要开启。

// 开启
set profiling = ON;
// 关闭
set profiling = OFF;

  1. 运行SQL。

以下运行的SQL都会在show profile 中进行记录。


// 第一个SQL
select * from whale_fall;
// 第二个SQL
select * from whale_fall;
// 第三个SQL
select sleep(4);
  1. 查看结果。

show profiles;

  1. 诊断SQL。

查询不同的性能参数

  • ALL :显示所有的开销信息。
  • BLOCK IO : 显示块IO相关开销。
  • CONTEXT SWITCHES : 上下文切换相关开销。
  • CPU : 显示CPU相关开销信息。
  • IPC : 显示发送和接收相关开销信息。
  • MEMORY : 显示内存相关开销信息。
  • PAGE FAULTS : 显示页面错误相关开销信息。
  • SOURCE:显示和Source_function,Source_file,Souce_line相关的开销信息。
  • SWAPS:显示交换次数相关开销信息。

show profile cpu,block io for query 3;

  1. 日常开发需要注意的结论。

我们可以通过上述查询出来的状态分析此时SQL出现异常的原因

  • converting HEAP to MyISAM : 查询结果太大,内存都不够用了往磁盘上搬。
  • Creating tmp table : 创建临时表。
  • Copying to tmp table on disk : 把内存中临时表复制到磁盘,危险!!!
  • locked : 锁。