多年刪庫(kù)跑路、背鍋經(jīng)驗(yàn)告訴我，MySQL這個(gè)東西參數(shù)真的沒(méi)啥好優(yōu)化的，固定套路走一波 sysbench 不會(huì)太難看的，最后說(shuō)到底還是磁盤(pán)「云上升配、自建么上Pcie」，平時(shí)自己測(cè)試玩都比生產(chǎn)猛「都可以壓出各種異常，MySQL的日志莫非只有在這個(gè)時(shí)候有用？」，還嗶嗶慢「你說(shuō)是誰(shuí)的問(wèn)題」。
優(yōu)化步驟
平時(shí)優(yōu)化基本上按照如下順序來(lái)玩即可
1. SQL優(yōu)化是基礎(chǔ)，Explain是神器
2. 軟件優(yōu)化，傳說(shuō)中的優(yōu)化參數(shù)
3. 硬件優(yōu)化，升配
4. 架構(gòu)優(yōu)化，這個(gè)就麻煩了，可能這個(gè)時(shí)候就要引入各種其他中間件了
所有表結(jié)構(gòu)和SQL決定了80%的性能了，至關(guān)重要，MySQL沒(méi)有銀蛋。
參數(shù)優(yōu)化
innodb_buffer_pool_size 硬件資源的75%~85%「其實(shí)這個(gè)東西不是越大越好，例如大查詢(xún)?yōu)橹饕獔?chǎng)景就要掂量著給了，mysql社區(qū)版是沒(méi)有連接池的，一個(gè)連接就是獨(dú)立的內(nèi)存空間，萬(wàn)一并發(fā)一高查詢(xún)還大直接OOM了，宕機(jī)沒(méi)商量」，如下參數(shù)均是5.6為主「5.7和8.0也沒(méi)好好研究，因?yàn)殛P(guān)鍵的不在一個(gè)數(shù)據(jù)庫(kù)版本上，參數(shù)也較為保守，具體是啥意思自己官方文檔看看，MySQL文檔太全了。」
malloc-lib = libjemalloc.so 「高負(fù)載下可以基于 Jemalloc 提高穩(wěn)定性，提高內(nèi)存管理效率」
innodb_purge_threads 4 「均按24c的cpu為例」
innodb_read_io_threads 8 「均按24c的cpu為例」
innodb_write_io_threads 8 「均按24c的cpu為例」
innodb_io_capacity_max 「8 Raid 10：400；SSD：5000，Pcie：40000+」
innodb_io_capacity? 「8 Raid 10：200；SSD：2000，Pcie：20000+」
innodb_max_dirty_pages_pct 70 可以提高如果寫(xiě)入要求巨大
innodb_flush_log_at_trx_commit = 2 「理論上丟失最后一個(gè)事物的提交」
sync_binlog = 0 「交給磁盤(pán)能跑多快跑多快」
# *** INNODB Specific Options *** 僅供參考
innodb_data_file_path = ibdata1:512M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 1
innodb_log_file_size = 2G
innodb_log_files_in_group = 4
innodb_log_buffer_size = 16M
innodb_undo_logs = 128 # <default: 128 Min:0 Max:128>
innodb_undo_tablespaces = 8
innodb_page_size = 16K # <default: 16K Other: 8K 4K>
innodb_purge_threads = 4 #Page Cleaner Thread
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity_max = 5000
innodb_io_capacity = 2000
innodb_disable_sort_file_cache = ON #Temporary files O_DIRECT
innodb_flush_neighbors = 0? #For table data stored on SSD
innodb_lock_wait_timeout=180
innodb_max_dirty_pages_pct = 75
innodb_file_format = Barracuda
innodb_open_files = 4096
innodb_strict_mode = 1
innodb_print_all_deadlocks = 1 #All deadlocks in InnoDB user transactions is recorded in the mysqld error log
innodb_thread_concurrency = 16
#Faster Restart
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
這邊引入幾個(gè)概念
redo log 寫(xiě)入到 ib_logfile 「多個(gè)循壞寫(xiě)入的」，恢復(fù)提交事務(wù)修改的頁(yè)，物理日志，保證事務(wù)的持久性，為順序?qū)?，innodb_flush_log_at_trx_commit 0 1 2，redo log以512字節(jié)存儲(chǔ)與磁盤(pán)大小一致不需要doublewrite。
undo log 是這樣玩的寫(xiě)入到ibdata表空間中，回滾行記錄到某個(gè)特定的版本，邏輯日志，幫助回滾&MVCC，隨機(jī)讀寫(xiě)，undo log 伴隨著redo log的產(chǎn)生。
下圖很好解釋了innodb_flush_log_at_trx_commit 0 1 2的區(qū)別，決定性能的關(guān)鍵參數(shù)。
引出一個(gè)系統(tǒng)概念：LSN實(shí)際上對(duì)應(yīng)日志文件的偏移量，新的LSN＝舊的LSN + 寫(xiě)入的日志大小
Log sequence number（LSN1）：當(dāng)前系統(tǒng)LSN最大值，新的事務(wù)日志LSN將在此基礎(chǔ)上生成（LSN1+新日志的大小）；
Log flushed up to（LSN2）：當(dāng)前已經(jīng)寫(xiě)入日志文件的LSN；
Oldest modified data log（LSN3）：當(dāng)前最舊的臟頁(yè)數(shù)據(jù)對(duì)應(yīng)的LSN，寫(xiě)Checkpoint的時(shí)候直接將此LSN寫(xiě)入到日志文件；
Last checkpoint at（LSN4）：當(dāng)前已經(jīng)寫(xiě)入Checkpoint的LSN；
因此從理論上來(lái)說(shuō)，只要事務(wù)執(zhí)行速度大于臟頁(yè)刷盤(pán)速度，最終都會(huì)觸發(fā)日志保護(hù)機(jī)制，進(jìn)而將事務(wù)阻塞，導(dǎo)致MySQL操作掛起。
IO很高的原因，因?yàn)檫@個(gè)時(shí)候在不斷的刷臟頁(yè)數(shù)據(jù)到磁盤(pán)。
感悟
MySQL 不適合暴力寫(xiě)入，理論上其實(shí)讀操作都是可以?xún)?yōu)化的，但是寫(xiě)操作除了批量寫(xiě)入，小步快跑方法就不多了「要不這么多分布式數(shù)據(jù)庫(kù)能起來(lái)，OLTP有 Tidb 國(guó)人之光、CockroachDB，OLAP 有 Greenplum、ClickHouse 戰(zhàn)斗名族這個(gè)可以學(xué)習(xí)下的」。MySQL不是萬(wàn)精油別都朝里懟，真不合適。
