MySQL性能优化

• 慢查询日志
• 慢查询日志的工具
• 查看执行计划
• profile 分析语句
• 系统参数优化

慢查询日志

MySQL 的慢查询日志功能默认是关闭的，需要手动开启。

-- 查询是否开启了慢查询，默认是关闭
show variables like '%slow_query%';
-- slow_query_log：是否开启慢查询日志，1为开启，0为关闭。
-- slow-query-log-file：MySQL数据库慢查询日志存储路径。

-- 慢查询阈值，当查询时间多于设定的阈值时，记录日志，【单位为秒】
show variables like '%long_query_time%';

-- 临时开启慢查询功能重启MySQL的话将失效
set global slow_query_log = ON;
set global long_query_time = 1;
-- 永久开启慢查询功能
vi /etc/my.cnf 中配置如下
[mysqld]
slow_query_log=ON
long_query_time=1

慢查询日志的工具

mysqldumpslow 是 MySQL 自带的慢查询日志工具

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

• -s：是表示按照何种方式排序
  al 平均锁定时间
  ar 平均返回记录时间
  at 平均查询时间（默认）
  c 计数
  l 锁定时间
  r 返回记录
  t 查询时间
  
• -t：是 top n 的意思，即为返回前面多少条的数据
• -g：后边可以写一个正则匹配模式，大小写不敏感的

查看执行计划

mysql> EXPLAIN SELECT t1.* FROM ( SELECT t.name, t.address FROM tuser t WHERE age = ( SELECT age FROM tuser WHERE id = 1 ) ) t1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    5 |    20.00 | Using where |
|  3 | SUBQUERY    | tuser | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

-- id
    SELECT查询的标识符。每个SELECT都会自动分配一个唯一的标识符。
    id相同，执行顺序由上到下，id越大，优先级越高。
-- select_type（重要）
    单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。
    - primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type
    - union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
    - dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
    - union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
    - subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
    - dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
    - derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select
-- table
    如果查询使用了别名，那么这里显示的是别名
    如果不涉及对数据表的操作，那么这显示为null
    如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
    如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。
-- partitions
    使用的哪些分区（对于非分区表值为null）。
-- type（重要）
    显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：
    system > const eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > ALL
    - 除了all之外，其他的type都可以使用到索引
    - 除了index_merge之外，其他的type只可以用到一个索引
    - 最少要使用到range级别
-- possible_keys
    此次查询中可能选用的索引，一个或多个
-- key
    此次查询中确切使用到的索引.
-- key_len
    key_len越小 索引效果越好。
-- ref
    如果是使用的常数等值查询，这里会显示const
    如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
    如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
-- rows
    显示此查询一共扫描了多少行. 这个是一个估计值.
-- filtered
    表示此查询条件所过滤的数据的百分比
-- extra（重要）

profile 分析语句

是 MySQL 自带的一种 query 诊断分析工具，通过它可以分析出一条 SQL 语句的硬件性能瓶颈在什么地方。

默认的情况下，MYSQL 的该功能没有打开，需要自己手动启动。

-- 查看是否开启了Profile功能，两个都行。
select @@profiling;
show variables like '%profil%';
-- 开启profile功能，1是开启、0是关闭
set profiling=1;

-- 显示当前会话的资源使用情况
show profiles;
show profile;
-- 查看`show profiles`结果中第二条语句的执行情况
show profile for query 2;
-- 指定资源类型查询
show profile cpu,swaps for query 2;

系统参数优化

• 缓冲区优化 设置innodb_buffer_pool_size建议为总内存大小的 3/4 或者 4/5。

  -- 查看innodb_buffer_pool_size的使用情况
  show global status like 'innodb_buffer_pool_pages_%';
  -- Innodb_buffer_pool_pages_free为0，则表示buffer_pool已经被用光了。
  

• 降低磁盘写入次数

  • 对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志、慢查询日志、错误日志
  • 使用足够大的写入缓存 innodb_log_file_size，推荐设置为 0.25 * innodb_buffer_pool_size
  • 设置合适的 innodb_flush_log_at_trx_commit，和日志落盘有关系。

• MySQL 数据库配置优化

  • innodb_buffer_pool_size
    缓冲池字节大小推荐值为物理内存的 50%~80%。
  • innodb_flush_log_at_trx_commit=1
    来控制 redo log 刷新到磁盘的策略。
  • sync_binlog=1
    每提交 1 次事务同步写到磁盘中，可以设置为 n。
  • innodb_max_dirty_pages_pct=30
    脏页占 innodb_buffer_pool_size 的比例时，触发刷脏页到磁盘。 推荐值为 25%~50%。
  • innodb_io_capacity=200
    后台进程最大 IO 性能指标。默认 200，如果 SSD，调整为 5000~20000
  • innodb_data_file_path
    指定 innodb 共享表空间文件的大小。
  • long_qurey_time=0.3
    慢查询日志的阈值设置，单位秒。
  • binlog_format=row
    mysql 复制的形式，row 为 MySQL8.0 的默认形式。
  • max_connections=200
    调高该参数则应降低 interactive_timeout、wait_timeout 的值。
  • innodb_log_file_size
    过大，实例恢复时间长；过小，造成日志切换频繁。
  • general_log=0
    全量日志建议关闭。默认关闭。

• 操作系统优化

  • 内核参数优化

    cat > /etc/sysctl.conf << EOF
    # 增加监听队列上限：
    net.core.somaxconn = 65535
    net.core.netdev_max_backlog = 65535
    net.ipv4.tcp_max_syn_backlog = 65535
    
    # 加快TCP连接的回收：
    net.ipv4.tcp_fin_timeout = 10
    net.ipv4.tcp_tw_reuse = 1
    net.ipv4.tcp_tw_recycle = 1
    
    # TCP连接接收和发送缓冲区大小的默认值和最大值:
    net.core.wmem_default = 87380
    net.core.wmem_max = 16777216
    net.core.rmem_default = 87380
    net.core.rmem_max = 16777216
    
    # 减少失效连接所占用的TCP资源的数量，加快资源回收的效率：
    net.ipv4.tcp_keepalive_time = 120
    net.ipv4.tcp_keepalive_intvl = 30
    net.ipv4.tcp_keepalive_probes = 3
    
    # 单个共享内存段的最大值：
    kernel.shmmax = 4294967295
    
    # 控制换出运行时内存的相对权重：
    vm.swappiness = 0
    EOF
    

  • 增加资源限制

    # 配置将可打开的文件数量增加到65535个，以保证可以打开足够多的文件句柄。重启系统才能生效。
    cat > /etc/security/limit.conf << EOF
    # *：表示对所有用户有效
    # soft：表示当前系统生效的设置（soft不能大于hard ）
    # hard：表明系统中所能设定的最大值
    # nofile：表示所限制的资源是打开文件的最大数目
    # 65535：限制的数量
    * soft nofile 65535
    * hard nofile 65535
    EOF
    

  • 磁盘调度策略

  • 硬件优化