一、背景

記錄使用mysql過程中遇到的問題，及一些配置

二、mysql配置

[client]
#服務(wù)端口號 默認(rèn)3306
port = 3306
socket = /tmp/mysql.sock

[mysqld]
# 設(shè)置socke文件所在目錄
socket = /tmp/mysql.sock
# mysql的安裝根路徑
basedir = /usr/local/mysql-5.7.25-macos10.14-x86_64/
# 數(shù)據(jù)目錄
datadir = /usr/local/mysql/data/
# 臨時目錄
tmpdir = /var/tmp
# MySQL打開的文件描述符限制，默認(rèn)最小1024;當(dāng)open_files_limit沒有被配置的時候，比較max_connections*5和ulimit -n的值，哪個大用哪個，
# 當(dāng)open_file_limit被配置的時候，比較open_files_limit和max_connections*5的值，哪個大用哪個。
open_files_limit = 65535
# MySQL每打開一個表，都會讀入一些數(shù)據(jù)到table_open_cache緩存中，當(dāng)MySQL在這個緩存中找不到相應(yīng)信息時，才會去磁盤上讀取。默認(rèn)值64
# 假定系統(tǒng)有200個并發(fā)連接，則需將此參數(shù)設(shè)置為200*N(N為每個連接所需的文件描述符數(shù)目)；
# 當(dāng)把table_open_cache設(shè)置為很大時，如果系統(tǒng)處理不了那么多文件描述符，那么就會出現(xiàn)客戶端失效，連接不上
table_open_cache = 2000

#事務(wù)隔離級別，默認(rèn)為可重復(fù)讀，mysql默認(rèn)可重復(fù)讀級別
transaction_isolation = REPEATABLE-READ
#SQL數(shù)據(jù)包發(fā)送的大小，如果有BLOB對象建議修改成1G
max_allowed_packet = 128M
#MySQL連接閑置超過一定時間后(單位：秒)將會被強(qiáng)行關(guān)閉
#MySQL默認(rèn)的wait_timeout  值為8個小時, interactive_timeout參數(shù)需要同時配置才能生效
interactive_timeout = 28800
wait_timeout = 28800

# 數(shù)據(jù)庫默認(rèn)字符集,主流字符集支持一些特殊表情符號（特殊表情符占用4個字節(jié)）
character-set-server = utf8mb4
# 數(shù)據(jù)庫字符集對應(yīng)一些排序等規(guī)則，注意要和character-set-server對應(yīng)
collation-server = utf8mb4_general_ci
# 設(shè)置client連接mysql時的字符集,防止亂碼
init_connect='SET NAMES utf8mb4'
# 是否對sql語句大小寫敏感，1表示不敏感，8.0需要在初始化時候設(shè)置
lower_case_table_names = 1
# 最大連接數(shù),上限為16384
max_connections = 1000
# 存放執(zhí)行請求的堆棧大小，默認(rèn)值是50，設(shè)置為最大鏈接數(shù)的20%或30%
back_log=300
#最大錯誤連接數(shù)
max_connect_errors = 1200
# 代表并發(fā)線程數(shù)，默認(rèn)是0，即不限制。推薦設(shè)置為CPU核心數(shù)的2倍。
innodb_thread_concurrency=64
# 設(shè)置InnoDB的緩存容量，默認(rèn)是128M，推薦為主機(jī)內(nèi)存的70%
innodb_buffer_pool_size=1024M

# 開啟慢查詢?nèi)罩?slow_query_log = ON
# 慢查詢?nèi)罩疚恢?slow_query_log_file = /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 慢查詢sql語句執(zhí)行時間
long_query_time = 3
#檢索的行數(shù)必須達(dá)到此值才可被記為慢查詢
min_examined_row_limit = 0

# 設(shè)置中繼日志的路徑
relay_log=/usr/local/var/mysql/relaylog/mysql-relay

# 開啟binlog
#開啟binlog日志
#binlog日志的基本文件名，需要注意的是啟動mysql的用戶需要對這個目錄(/usr/local/var/mysql/binlog)有寫入的權(quán)限
log_bin_basename=/usr/local/var/mysql/binlog/mysql-bin
#配置serverid
server-id=1
# 配置binlog日志的格式
binlog_format = ROW
# 設(shè)置中繼日志的路徑
relay_log=/usr/local/var/mysql/relaylog/mysql-relay
# 如果當(dāng)前服務(wù)器是從服務(wù)器，但是又需要作為別的服務(wù)器的主服務(wù)器，那么需要打開此參數(shù)
log_slave_updates = OFF
# 如果此服務(wù)器做為一個從服務(wù)器，那么建議設(shè)置為on,讓其只讀
read_only = on
# 記錄日志的大小，也可以理解為詳細(xì)程度 FULL、minimal、
binlog_row_image = FULL
#每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file，并且flush(刷到磁盤)中去，該模式為系統(tǒng)默認(rèn)
innodb_flush_log_at_trx_commit=1
#默認(rèn):0，表示MySQL不控制binlog的刷新，由文件系統(tǒng)自己控制它的緩存的刷新,如果這期間服務(wù)器crash，則會丟失部分?jǐn)?shù)據(jù)，性能最高，
#如果配置為1，則表示每次事務(wù)提交都會刷新到磁盤，最安全，性能損耗較高
sync_binlog=1
# 配置binlog保存多久時間，看實際情況填寫此值
expire_logs_days=3
#binlog每個日志文件大小
max_binlog_size = 1G
#binlog緩存大小
binlog_cache_size = 4m
#最大binlog緩存大小，建議就使用默認(rèn)值（18446744073709547520），不要修改
# max_binlog_cache_size = 18446744073709547520

# 發(fā)起死鎖檢測，發(fā)現(xiàn)死鎖后，主動回滾死鎖鏈條中的某一個事務(wù)，讓其他事務(wù)得以繼續(xù)執(zhí)行。將參數(shù) innodb_deadlock_detect 設(shè)置為 on，表示開啟這個邏輯。
innodb_deadlock_detect = on

# 在 InnoDB 中，innodb_lock_wait_timeout 的默認(rèn)值是 50s，意味著如果采用第一個策略，當(dāng)出現(xiàn)死鎖以后，第一個被鎖住的線程要過 50s 才會超時退出，然后其他線程才有可能繼續(xù)執(zhí)行。對于在線服務(wù)來說，這個等待時間往往是無法接受的。
innodb_lock_wait_timeout=50s

# 磁盤的io能力，根據(jù)實際情況設(shè)置，可以使用 fio 這個工具測試得出
innodb_io_capacity=1000

三、慢查詢?nèi)罩?/h1>

1、命令行臨時生效

重啟mysql服務(wù)器之后，命令行配置不生效。

# 1、檢查是否開啟慢查詢?nèi)罩?show variables like '%slow_query_log%';
# 檢查 slow_query_log 的值是否是 ON,如果是，則說明是打開的。

# 2、啟用慢查詢?nèi)罩?set global slow_query_log=on;

# 3、查看慢查詢?nèi)罩镜穆窂?show variables like '%slow_query_log_file%';

# 4、查看慢查詢?nèi)罩镜臅r間,默認(rèn)是10s。
show variables like '%long_query_time%';

# 5、修改慢查詢?nèi)罩镜臅r間為5s
# 執(zhí)行完以下命令后，需要在另外的一個終端上查看，即執(zhí)行 show variables like '%long_query_time%' 這個命令才會看到修改后的值。
set global long_query_time=5;

# 6、查看慢查詢的條數(shù)
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、慢查詢?nèi)罩窘忉?/h2>

慢查詢?nèi)罩臼纠?/p>

# Time: 2021-05-25T13:43:05.359523Z（sql語句執(zhí)行時間）
# User@Host: root[root] @ localhost []  Id:    32
# Query_time: 12.001026（sql執(zhí)行總耗時）  Lock_time: 0.000000（等待表鎖的時間） Rows_sent: 1  （查詢返回的行數(shù)）Rows_examined: 0（查詢檢查的行數(shù)，值越大越耗時）
SET timestamp=1621950185;
select sleep(12);（具體執(zhí)行的sql語句）

4、mysqldumpdlow查看慢查詢?nèi)罩?/h2>

# 查看慢查詢?nèi)罩?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
# 得到訪問次數(shù)最多的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
# 一般執(zhí)行上面的語句都要和 | more 一起使用，否則日志太多
sudo mysqldumpslow -s r -t 10 /usr/local/mysql/data/huandeMacBook-Pro-slow.log | more
# 慢查詢?nèi)罩痉治鼋y(tǒng)計
pt-query-digest /usr/local/mysql/data/huandeMacBook-Pro-slow.log

四、查看索引為何不生效

1、explain分析

-- 1、查看執(zhí)行計劃
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 后面跟的關(guān)鍵字 EXTENDED（擴(kuò)展信息） 在MySQL5.7及之后的版本中廢棄了，但是該語法仍被識別為向后兼容，所以在5.7版本及后續(xù)版本中，可以不用在 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;

執(zhí)行查詢語句，增加語句等

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
set optimizer_trace = "enabled=off";