mysql的一些配置操作
一、背景
记录使用mysql过程中遇到的问题，及一些配置
二、mysql配置
[client]
#服务端口号 默认3306
port = 3306
socket = /tmp/mysql.sock
[mysqld]
# 设置socke文件所在目录
socket = /tmp/mysql.sock
# mysql的安装根路径
basedir = /usr/local/mysql-5.7.25-macos10.14-x86_64/
# 数据目录
datadir = /usr/local/mysql/data/
# 临时目录
tmpdir = /var/tmp
# MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个，
# 当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。
open_files_limit = 65535
# MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64
# 假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；
# 当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上
table_open_cache = 2000
#事务隔离级别，默认为可重复读，mysql默认可重复读级别
transaction_isolation = REPEATABLE-READ
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 128M
#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 28800
wait_timeout = 28800
# 数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server = utf8mb4
# 数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci
# 设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
# 是否对sql语句大小写敏感，1表示不敏感，8.0需要在初始化时候设置
lower_case_table_names = 1
# 最大连接数,上限为16384
max_connections = 1000
# 存放执行请求的堆栈大小，默认值是50，设置为最大链接数的20%或30%
back_log=300
#最大错误连接数
max_connect_errors = 1200
# 代表并发线程数，默认是0，即不限制。推荐设置为CPU核心数的2倍。
innodb_thread_concurrency=64
# 设置InnoDB的缓存容量，默认是128M，推荐为主机内存的70%
innodb_buffer_pool_size=1024M
# 开启慢查询日志
slow_query_log = ON
# 慢查询日志位置
slow_query_log_file = /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 慢查询sql语句执行时间
long_query_time = 3
#检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit = 0
# 设置中继日志的路径
relay_log=/usr/local/var/mysql/relaylog/mysql-relay
# 开启binlog
#开启binlog日志
#binlog日志的基本文件名，需要注意的是启动mysql的用户需要对这个目录(/usr/local/var/mysql/binlog)有写入的权限
log_bin_basename=/usr/local/var/mysql/binlog/mysql-bin
#配置serverid
server-id=1
# 配置binlog日志的格式
binlog_format = ROW
# 设置中继日志的路径
relay_log=/usr/local/var/mysql/relaylog/mysql-relay
# 如果当前服务器是从服务器，但是又需要作为别的服务器的主服务器，那么需要打开此参数
log_slave_updates = OFF
# 如果此服务器做为一个从服务器，那么建议设置为on,让其只读
read_only = on
# 记录日志的大小，也可以理解为详细程度 FULL、minimal、
binlog_row_image = FULL
#每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认
innodb_flush_log_at_trx_commit=1
#默认:0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新,如果这期间服务器crash，则会丢失部分数据，性能最高，
#如果配置为1，则表示每次事务提交都会刷新到磁盘，最安全，性能损耗较高
sync_binlog=1
# 配置binlog保存多久时间，看实际情况填写此值
expire_logs_days=3
#binlog每个日志文件大小
max_binlog_size = 1G
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小，建议就使用默认值（18446744073709547520），不要修改
# max_binlog_cache_size = 18446744073709547520
# 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
innodb_deadlock_detect = on
# 在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
innodb_lock_wait_timeout=50s
# 磁盘的io能力，根据实际情况设置，可以使用 fio 这个工具测试得出
innodb_io_capacity=1000
三、慢查询日志
1、命令行临时生效
重启mysql服务器之后，命令行配置不生效。
# 1、检查是否开启慢查询日志
show variables like '%slow_query_log%';
# 检查 slow_query_log 的值是否是 ON,如果是，则说明是打开的。
# 2、启用慢查询日志
set global slow_query_log=on;
# 3、查看慢查询日志的路径
show variables like '%slow_query_log_file%';
# 4、查看慢查询日志的时间,默认是10s。
show variables like '%long_query_time%';
# 5、修改慢查询日志的时间为5s
# 执行完以下命令后，需要在另外的一个终端上查看，即执行 show variables like '%long_query_time%' 这个命令才会看到修改后的值。
set global long_query_time=5;
# 6、查看慢查询的条数
show global status like '%slow_queries%';
2、配置文件修改永久生效
修改 my.cnf
mac 上获取my.cnf文件的位置： mysql --verbose --help | grep my.cnf
/etc/mysql/conf.d/mysql.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/instance-1-slow.log
# sql语句超过这个时间为慢查询sql，单位为秒
long_query_time = 2
3、慢查询日志解释
慢查询日志示例
# Time: 2021-05-25T13:43:05.359523Z（sql语句执行时间）
# User@Host: root[root] @ localhost [] Id: 32
# Query_time: 12.001026（sql执行总耗时） Lock_time: 0.000000（等待表锁的时间） Rows_sent: 1 （查询返回的行数）Rows_examined: 0（查询检查的行数，值越大越耗时）
SET timestamp=1621950185;
select sleep(12);（具体执行的sql语句）
4、mysqldumpdlow查看慢查询日志
# 查看慢查询日志
sudo mysqldumpslow -v /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 按照总耗时排序
sudo mysqldumpslow -v t /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 按照平均耗时排序
sudo mysqldumpslow -v at /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 查看平均时间最耗时的5条
sudo mysqldumpslow -v at -t 5 /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 得到返回记录集最多的10个sql
sudo mysqldumpslow -s r -t 10 /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 得到访问次数最多的10个sql
sudo mysqldumpslow -s c -t 10 /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
sudo mysqldumpslow -s t -t 10 -g "left join" /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 一般执行上面的语句都要和 | more 一起使用，否则日志太多
sudo mysqldumpslow -s r -t 10 /usr/local/mysql/data/huandeMacBook-Pro-slow.log | more
# 慢查询日志分析统计
pt-query-digest /usr/local/mysql/data/huandeMacBook-Pro-slow.log
四、查看索引为何不生效
1、explain分析
-- 1、查看执行计划
explain EXTENDED
select * from t_department a LEFT JOIN t_employees b on a.de_no = b.de_no where a.id = 4019;
-- 2、使用 show warnings
show warnings;
注意： explain 后面跟的关键字 EXTENDED（扩展信息） 在MySQL5.7及之后的版本中废弃了，但是该语法仍被识别为向后兼容，所以在5.7版本及后续版本中，可以不用在 explain 后面添加 EXTENDED 了；
2、optimizer_trace分析
show variables like '%optimizer_trace%';
set optimizer_trace = "enabled=on";
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;
set optimizer_trace_max_mem_size = 163840;
执行查询语句，增加语句等
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
set optimizer_trace = "enabled=off";
2.1 案例
*************************** 1. row ***************************
# 分析的查询语句是什么
QUERY: SELECT * FROM s1 WHERE key1 > 'z' AND key2 < 1000000 AND key3 IN ('aa', 'bb', 'cb') AND common_field = 'abc'
# 优化的具体过程
TRACE: {
"steps": [
{
"join_preparation": { # prepare阶段
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{
"expanded_query": "/* select#1 */ select `s1`.`id` AS `id`,`s1`.`key1` AS `key1`,`s1`.`key2` AS `key2`,`s1`.`key3` AS `key3`,`s1`.`key_part1` AS `key_part1`,`s1`.`key_part2` AS `key_part2`,`s1`.`key_part3` AS `key_part3`,`s1`.`common_field` AS `common_field` from `s1` where ((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')) and (`s1`.`common_field` = 'abc'))"
}
]
}
},
{
"join_optimization": { # optimize阶段
"select#": 1,
"steps": [
{
"condition_processing": { # 处理搜索条件
"condition": "WHERE",
# 原始搜索条件
"original_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')) and (`s1`.`common_field` = 'abc'))",
"steps": [
{
# 等值传递转换
"transformation": "equality_propagation",
"resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')) and multiple equal('abc', `s1`.`common_field`))"
},
{
# 常量传递转换
"transformation": "constant_propagation",
"resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')) and multiple equal('abc', `s1`.`common_field`))"
},
{
# 去除没用的条件
"transformation": "trivial_condition_removal",
"resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')) and multiple equal('abc', `s1`.`common_field`))"
}
]
}
},
{
# 替换虚拟生成列
"substitute_generated_columns": {
}
},
{
# 表的依赖信息
"table_dependencies": [
{
"table": "`s1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
# 预估不同单表访问方法的访问成本
"rows_estimation": [
{
"table": "`s1`",
"range_analysis": {
"table_scan": {
"rows": 20250,
"cost": 2051.35
},
# 分析可能使用的索引
"potential_range_indexes": [
{
"index": "PRIMARY", # 主键不可用
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_key2",# idx_key2可能被使用
"usable": true,
"key_parts": [
"key2"
]
},
{
"index": "idx_key1", # idx_key1可能被使用
"usable": true,
"key_parts": [
"key1",
"id"
]
},
{
"index": "idx_key3", # idx_key3可能被使用
"usable": true,
"key_parts": [
"key3",
"id"
]
},
{
"index": "idx_key_part", # idx_key_part不可用
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "idx_key2",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_key1",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_key3",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
# 分析各种可能使用的索引的成本
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
# 使用idx_key2的成本分析
"index": "idx_key2",
# 使用idx_key2的范围区间
"ranges": [
"NULL < key2 < 1000000"
],
"index_dives_for_eq_ranges": true,# 是否使用index dive
"rowid_ordered": false,# 使用该索引获取的记录是否按照主键排序
"using_mrr": false, # 是否使用mrr
"index_only": false, # 是否是索引覆盖访问
"in_memory": 1,
"rows": 10125,# 使用该索引获取的记录条数
"cost": 3544.01,# 使用该索引的成本
"chosen": false, # 使用该索引的成本
"cause": "cost" # 因为成本太大所以不选择该索引
},
{
# 使用idx_key1的成本分析
"index": "idx_key1",
# 使用idx_key1的范围区间
"ranges": [
"'z' < key1"
],
"index_dives_for_eq_ranges": true,# 同上
"rowid_ordered": false,# 同上
"using_mrr": false,# 同上
"index_only": false,# 同上
"in_memory": 1,
"rows": 1,# 同上
"cost": 0.61,# 同上
"chosen": true# 是否选择该索引
},
{
# 使用idx_key3的成本分析
"index": "idx_key3",
# 使用idx_key3的范围区间
"ranges": [
"key3 = 'aa'",
"key3 = 'bb'",
"key3 = 'cb'"
],
"index_dives_for_eq_ranges": true,# 同上
"rowid_ordered": false,# 同上
"using_mrr": false,# 同上
"index_only": false,# 同上
"in_memory": 1,
"rows": 3,# 同上
"cost": 1.81,# 同上
"chosen": false,# 同上
"cause": "cost"# 同上
}
],
# 分析使用索引合并的成本
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
# 对于上述单表查询s1最优的访问方法
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_key1",
"rows": 1,
"ranges": [
"'z' < key1"
]
},
"rows_for_plan": 1,
"cost_for_plan": 0.61,
"chosen": true
}
}
}
]
},
{
# 分析各种可能的执行计划
#（对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选取idx_key1就好）
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`s1`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 1,
"access_type": "range",
"range_details": {
"used_index": "idx_key1"
},
"resulting_rows": 1,
"cost": 0.71,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 0.71,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`s1`.`common_field` = 'abc') and (`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`s1`",
"attached": "((`s1`.`common_field` = 'abc') and (`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')))"
}
]
}
},
{
# 尝试给查询添加一些其他的查询条件
"finalizing_table_conditions": [
{
"table": "`s1`",
"original_table_condition": "((`s1`.`common_field` = 'abc') and (`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')))",
"final_table_condition ": "((`s1`.`common_field` = 'abc') and (`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')))"
}
]
},
{
# 再稍稍的改进一下执行计划
"refine_plan": [
{
"table": "`s1`",
"pushed_index_condition": "(`s1`.`key1` > 'z')",
"table_condition_attached": "((`s1`.`common_field` = 'abc') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('aa','bb','cb')))"
}
]
}
]
}
},
{
"join_execution": { # execute阶段
"select#": 1,
"steps": [
]
}
}
]
}
# 因优化过程文本太多而丢弃的文本字节大小，值为0时表示并没有丢弃
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
# 权限字段
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)
ERROR:
No query specified
3、重建索引
alter table table_name engine=innodb;
五、排查某个执行时间很长的语句是否在等待锁
1、查看Mysql中有哪些进程在运行
select * from information_schema.PROCESSLIST t;
2、排查系统中锁等待的情况
select * from sys.innodb_lock_waits;，从下图中可以看到，当前执行的delete语句需要的锁被进程16持有了。
3、获取进程中有哪些线程
select * from performance_schema.threads where PROCESSLIST_ID = '16';
4、查看线程在执行哪个SQL
select * from performance_schema.events_statements_history where THREAD_ID = '56' order by TIMER_START desc;
2842
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
