MySQL 调优

Mysql 内部参数调优

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
表示缓冲池字节大小。
推荐值为物理内存的50%~80%。
innodb_buffer_pool_size

用来控制redo log刷新到磁盘的策略。
innodb_flush_log_at_trx_commit=1

每提交1次事务同步写到磁盘中，可以设置为n。
sync_binlog=1

脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。
innodb_max_dirty_pages_pct=30

后台进程最大IO性能指标。
默认200，如果SSD，调整为5000~20000
innodb_io_capacity=200

指定innodb共享表空间文件的大小。
innodb_data_file_path

慢查询日志的阈值设置，单位秒。
long_qurey_time=0.3

mysql复制的形式，row为MySQL8.0的默认形式。
binlog_format=row

调高该参数则应降低interactive_timeout、wait_timeout的值。
max_connections=200

过大，实例恢复时间长；过小，造成日志切换频繁。
innodb_log_file_size

全量日志建议关闭。
默认关闭。
general_log=0

开发规范制定

1
2
3
4
5
6
创建数据库表，走工单系统完成，需要审核。 如果在创建表时检测到没有创建索引，那就会提示 warning
规范上来说，只要有查询需求，都应该建立索引

1、利用自查询优化超多分页的场景。比如 limit offset，n 在 Mysql 是获取 offset + n 的记录，在返回 n 条。而利用自查询则是查出 n 条，通过 ID 检索对应的记录出来，提高查询效率
2、通过explain命令来查看SQL的执行计划，看看自己写的SQL是否走了索引，走了什么索引。通过show profile 来查看SQL对系统资源的损耗情况（不过一般还是比较少用到的）
3.在开启事务后，在事务内尽可能只操作数据库，并有意识地减少锁的持有时间（比如在事务内需要插入&&修改数据，那可以先插入后修改。因为修改是更新操作，会加行锁。如果先更新，那并发下可能会导致多个事务的请求等待行锁释放）

数据库索引

1
2
3
1、是否能使用 [覆盖索引] ，减少 [回表] 所消耗的时间，这意味着我们在 select 的时候不能使用 * ，一定要指明对应的列
2、考虑是否组建 [联合索引] ，如果组建 [联合索引] ，尽量将区分度最高的放在最左边，并且考虑 [最左匹配原则]
3、对索引进行函数操作或者表达式计算会导致索引失效

隔离级别

总结性概述

1
2
3
4
可以优化的点有很多，比如说：
1、mysql硬件层面的使用多核性能优的cpu，足够的memory，disk使用ssd
2、还有针对mysql的参数调优：设置max_connections mysql最大连接数，每个用户的最大连接数，设置连接超时时间，设置innodb引擎，InnoDB引擎数据数据和索引尽量都放在内存中配置值为物理内存的70%,innodb_log_buffer_size 事务提交的缓存区,可以提高该参数值.
3、增加慢sql查询，对慢sql优化，大表添加索引或采取同步es 用es查大表数据，如果读写量级都很大，可以做读写分离，在分库分表这块也可以做优化，等等...