MySQL性能优化学习笔记

...

Posted by 呆贝斯 on November 22, 2022

MySQL性能优化

优化查询

启用慢查询日志

执行如下语句看是否启用了慢查询日志,ON为启用,OFF为未启用,默认为OFF。

SHOW VARIABLES LIKE '%slow_query_log%';

如果没有开启,使用如下两种方式来开启慢查询。

  1. 修改配置文件 修改配置文件 vim /etc/my.cnf,在 [mysqld] 段落在加入如下配置:
     [mysqld] 
     slow_query_log=1  # 是否启用慢查询日志,ON 为启用,OFF 为未启用,默认为 OFF。开启会影响性能,MySQL 重启会失效。
     slow_query_log_file=/var/lib/mysql/data/slow.log  # 指定慢查询日志文件的路径和名字,缺省文件名 host_name-slow.log。
     long_query_time=3  # 执行时间超过该值才记录到慢查询日志,单位为秒,默认为 10。
     log_output=FILE,TABLE  # 日志输出位置,默认为 FILE,即保存为文件,若设置为 TABLE,则将日志记录到 mysql.show_log 表中,支持设置多种格式。
    

    需要重启 MySQL 才可以生效,命令为 service mysqld restart。

  2. 设置全局变量 如下打开慢查询日志,设置超时时间为 3 秒,并且将日志记录到文件以及 mysql.show_log 表中。
     SET GLOBAL slow_query_log = 1;
     SET GLOBAL slow_query_log_file='/var/lib/mysql/data/slow.log';
     SET GLOBAL long_query_time=3;
     SET GLOBAL log_output='FILE,TABLE';
    

    想要永久生效得用上面那个配置文件里配置,否则数据库重启后,这些配置失效。

  3. 获取慢SQL信息 查看慢查询日志记录数
     SHOW GLOBAL STATUS LIKE '%Slow_queries%';
    

    模拟语句

     select sleep(5);
    

    查看日志

     cat /var/lib/mysql/data/slow.log
    

分析慢查询日志

  1. explain执行计划分析慢SQL
     EXPLAIN 查询语句;  # EXPLAIN 语句的语法结构
    

    explain 执行后输出的结果集包含 12 列,分别是 id、select_type、table、partitions、type、possible_keys、key、 key_len、ref、rows、filtered 和 Extra,下面对这些字段进行解释。

字段 含义
id Query Optimizer 所选定的执行计划中查询的序列号
select_type 显示本行是简单或复杂 select,如果查询有任何复杂的子查询,则最外层标记为PRIMARY、DERIVED.、UNION、UNION RESUIT 等
table 显示这一步所访问的数据库中的表的名称
partitions 查询时匹配到的分区信息,对于非分区表值为 NULL,当查询的是分区表时,partitions 显示分区表命中的分区情况
type 数据访问、读取操作类型(ALL、index、range、ref、eq_ref、const、system)等
possible_keys 该查询可以利用的索引,如果没有任何索引可以使用,就会显示成 null,这一项内容对于优化时候索引的调整非常重要
key MySQL Query Optimizer 从 possible_keys 中所选择使用的索引
key_len 被选中使用索引的索引键长度
ref 列出是通过常量(const),还是某个表的某个字段(如果是 join)来过滤(通过 key)的。
rows MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数
filtered 表示存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例
Extra 查询中每一步实现的额外细节信息,如 Using filesort、index 等
  1. mysqldumpslow MySQL 内置了 mysqldumpslow 这个工具来帮我们分析慢查询日志。
     #得到返回记录集最多的10个SQL
     mysqldumpslow -s r -t 10 /var/lib/mysql/data/slow.log
     #得到访问次数最多的10个SQL
     mysqldumpslow -s c -t 10 /var/lib/mysql/data/slow.log
     #得到按照时间排序的前10条里面含有左连接的查询语句
     mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/data/slow.log
     #结合| more使用,防止爆屏情况
     mysqldumpslow -s r -t 10 /var/lib/mysql/data/slow.log | more
        
     s:表示按何种方式排序
     c:访问次数
     l:锁定时间
     r:返回记录
     t:查询时间
     al:平均锁定时间
     ar:平均返回记录数
     at:平均查询时间
     t:返回前面多少条的数据
     g:后边搭配一个正则匹配模式,大小写不敏感
    
  2. Show Profile 分析慢SQL Show Profile 也可以分析慢 SQL,比 explain 获取的信息更详细,比如能分析当前会话中语句执行的资源消耗情况, 能分析这条 SQL 整个生命周期的耗时。但没有上面 pt-query-digest 那款慢查询日志分析工具强大, 但 pt-query-digest 是外置的需要单独下载,如果你想用内置的话,能够满足你的需求的话,选择 Show Profile 就行。

  3. pt-query-digest pt-query-digest 是一款很强大的慢查询日志分析工具,可以分析 MySQL 数据库的 binary log 、 general log 日志, 同时也可以使用 show processlist 或从 tcpdump 抓取的 MySQL 协议数据来进行分析。

使用索引查询

  1. 应用关键字 LIKE 优化索引查询
  2. 查询语句中优化多列索引查询
  3. 查询语句中使用关键字 OR 优化索引查询

优化多表查询

优化插入记录速度

禁用索引

禁用唯一性检查

优化 INSERT 语句

优化数据库结构

分析表

检查表

优化表设计

优化表

将字段多的表分解成多个表

增加中间表

使用高速缓存

查询高速缓存状态

开启高速缓存