一、背景
记录应用mysql过程中遇到的问题，及一些配置
二、mysql配置
[client]#服务端口号 默认3306port = 3306socket = /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对象倡议批改成1Gmax_allowed_packet = 128M#MySQL连贯闲置超过肯定工夫后(单位：秒)将会被强行敞开#MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数须要同时配置能力失效interactive_timeout = 28800wait_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# 最大连接数,下限为16384max_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#配置serveridserver-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 = ONslow_query_log_file = /var/lib/mysql/instance-1-slow.log# sql语句超过这个工夫为慢查问sql，单位为秒long_query_time = 23、慢查问日志解释
慢查问日志示例
# 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个sqlsudo mysqldumpslow -s r -t 10 /usr/local/mysql/data/huandeMacBook-Pro-slow.log# 失去拜访次数最多的10个sqlsudo 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 EXTENDEDselect * from t_department a LEFT JOIN t_employees b on a.de_no = b.de_no where a.id = 4019;-- 2、应用 show warningsshow 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";