MySQL是一個(gè)強(qiáng)大的開源數(shù)據(jù)庫。隨著MySQL上的應(yīng)用越來越多，MySQL逐漸遇到了瓶頸。這里提供 101 條優(yōu)化 MySQL 的建議。有些技巧適合特定的安裝環(huán)境，但是思路是相通的。我已經(jīng)將它們分成了幾類以幫助你理解。MySQL監(jiān)控MySQL服務(wù)器硬件和OS(操作系統(tǒng))調(diào)優(yōu)：1、有足夠的物理內(nèi)存，能將整個(gè)InnoDB文件加載到內(nèi)存里 —— 如果訪問的文件在內(nèi)存里，而不是在磁盤上，InnoDB會(huì)快很多。2、全力避免 Swap 操作 — 交換(swapping)是從磁盤讀取數(shù)據(jù)，所以會(huì)很慢。3、使用電池供電的RAM(Battery-Backed RAM)。4、使用一個(gè)高級(jí)磁盤陣列 — 最好是 RAID10 或者更高。5、避免使用RAID5 — 和校驗(yàn)需要確保完整性，開銷很高。6、將你的操作系統(tǒng)和數(shù)據(jù)分開，不僅僅是邏輯上要分開，物理上也要分開 — 操作系統(tǒng)的讀寫開銷會(huì)影響數(shù)據(jù)庫的性能。7、將臨時(shí)文件和復(fù)制日志與數(shù)據(jù)文件分開 — 后臺(tái)的寫操作影響數(shù)據(jù)庫從磁盤文件的讀寫操作。8、更多的磁盤空間等于更高的速度。9、磁盤速度越快越好。10、SAS優(yōu)于SATA。11、小磁盤的速度比大磁盤的更快，尤其是在 RAID 中。12、使用電池供電的緩存 RAID(Battery-Backed Cache RAID)控制器。13、避免使用軟磁盤陣列。14. 考慮使用固態(tài)IO卡(不是磁盤)來作為數(shù)據(jù)分區(qū) — 幾乎對所有量級(jí)數(shù)據(jù)，這種卡能夠支持 2 GBps 的寫操作。15、在 Linux 系統(tǒng)上，設(shè)置 swappiness 的值為0 — 沒有理由在數(shù)據(jù)庫服務(wù)器上緩存文件，這種方式在Web服務(wù)器或桌面應(yīng)用中用的更多。16、盡可能使用 noatime 和 nodirtime 來掛載文件系統(tǒng) — 沒有必要為每次訪問來更新文件的修改時(shí)間。17、使用 XFS 文件系統(tǒng) — 一個(gè)比ext3更快的、更小的文件系統(tǒng)，擁有更多的日志選項(xiàng)，同時(shí)，MySQL在ext3上存在雙緩沖區(qū)的問題。18、優(yōu)化你的 XFS 文件系統(tǒng)日志和緩沖區(qū)參數(shù) – -為了獲取最大的性能基準(zhǔn)。19、在Linux系統(tǒng)中，使用 NOOP 或 DEADLINE IO 調(diào)度器 — CFQ 和 ANTICIPATORY 調(diào)度器已經(jīng)被證明比 NOOP 和 DEADLINE 慢。20、使用 64 位操作系統(tǒng) — 有更多的內(nèi)存能用于尋址和 MySQL 使用。21、將不用的包和后臺(tái)程序從服務(wù)器上刪除 — 減少資源占用。22、將使用 MySQL 的 host 和 MySQL自身的 host 都配置在一個(gè) host 文件中 — 這樣沒有 DNS 查找。23、永遠(yuǎn)不要強(qiáng)制殺死一個(gè)MySQL進(jìn)程 — 你將損壞數(shù)據(jù)庫，并運(yùn)行備份。24、讓你的服務(wù)器只服務(wù)于MySQL — 后臺(tái)處理程序和其他服務(wù)會(huì)占用數(shù)據(jù)庫的 CPU 時(shí)間。MySQL 配置：25、使用 innodb_flush_method=O_DIRECT 來避免寫的時(shí)候出現(xiàn)雙緩沖區(qū)。26、避免使用 O_DIRECT 和 EXT3 文件系統(tǒng) — 這會(huì)把所有寫入的東西序列化。27、分配足夠 innodb_buffer_pool_size ，來將整個(gè)InnoDB 文件加載到內(nèi)存 — 減少從磁盤上讀。28、不要讓 innodb_log_file_size 太大，這樣能夠更快，也有更多的磁盤空間 — 經(jīng)常刷新有利降低發(fā)生故障時(shí)的恢復(fù)時(shí)間。29、不要同時(shí)使用 innodb_thread_concurrency 和 thread_concurrency 變量 — 這兩個(gè)值不能兼容。30、為 max_connections 指定一個(gè)小的值 — 太多的連接將耗盡你的RAM，導(dǎo)致整個(gè)MySQL服務(wù)器被鎖定。31、保持 thread_cache 在一個(gè)相對較高的數(shù)值，大約是 16 — 防止打開連接時(shí)候速度下降。32、使用 skip-name-resolve — 移除 DNS 查找。33、如果你的查詢重復(fù)率比較高，并且你的數(shù)據(jù)不是經(jīng)常改變，請使用查詢緩存 — 但是，在經(jīng)常改變的數(shù)據(jù)上使用查詢緩存會(huì)對性能有負(fù)面影響。34、增加 temp_table_size — 防止磁盤寫。35、增加 max_heap_table_size — 防止磁盤寫。36、不要將 sort_buffer_size 的值設(shè)置的太高 — 可能導(dǎo)致連接很快耗盡所有內(nèi)存。37、監(jiān)控 key_read_requests 和 key_reads，以便確定 key_buffer 的值 — key 的讀需求應(yīng)該比 key_reads 的值更高，否則使用 key_buffer 就沒有效率了。38、設(shè)置 innodb_flush_log_at_trx_commit = 0 可以提高性能，但是保持默認(rèn)值(1)的話，能保證數(shù)據(jù)的完整性，也能保證復(fù)制不會(huì)滯后。39、有一個(gè)測試環(huán)境，便于測試你的配置，可以經(jīng)常重啟，不會(huì)影響生產(chǎn)環(huán)境。MySQL Schema 優(yōu)化：40、保證你的數(shù)據(jù)庫的整潔性。41、歸檔老數(shù)據(jù) — 刪除查詢中檢索或返回的多余的行42、在數(shù)據(jù)上加上索引。43、不要過度使用索引，評(píng)估你的查詢。44、壓縮 text 和 blob 數(shù)據(jù)類型 — 為了節(jié)省空間，減少從磁盤讀數(shù)據(jù)。45、UTF 8 和 UTF16 比 latin1 慢。46、有節(jié)制的使用觸發(fā)器。47、保持?jǐn)?shù)據(jù)最小量的冗余 — 不要復(fù)制沒必要的數(shù)據(jù).48、使用鏈接表，而不是擴(kuò)展行。49、注意你的數(shù)據(jù)類型，盡可能的使用最小的。50、如果其他數(shù)據(jù)需要經(jīng)常需要查詢，而 blob/text 不需要，則將 blob/text 數(shù)據(jù)域其他數(shù)據(jù)分離。51、經(jīng)常檢查和優(yōu)化表。52、經(jīng)常做重寫 InnoDB 表的優(yōu)化。53、有時(shí)，增加列時(shí)，先刪除索引，之后在加上索引會(huì)更快。54、為不同的需求選擇不同的存儲(chǔ)引擎。55、日志表或?qū)徲?jì)表使用ARCHIVE存儲(chǔ)引擎 — 寫的效率更高。56、將 session 數(shù)據(jù)存儲(chǔ)在 memcache 中，而不是 MySQL 中 — memcache 可以設(shè)置自動(dòng)過期，防止MySQL對臨時(shí)數(shù)據(jù)高成本的讀寫操作。57、如果字符串的長度是可變的，則使用VARCHAR代替CHAR — 節(jié)約空間，因?yàn)镃HAR是固定長度，而VARCHAR不是(utf8 不受這個(gè)影響)。58、逐步對 schema 做修改 — 一個(gè)小的變化將產(chǎn)生的巨大的影響。59、在開發(fā)環(huán)境測試所有 schema 變動(dòng)，而不是在生產(chǎn)環(huán)境的鏡像上去做。60、不要隨意改變你的配置文件，這可能產(chǎn)生非常大的影響。61、有時(shí)候，少量的配置會(huì)更好。62、質(zhì)疑使用通用的MySQL配置文件。

?查詢優(yōu)化：63、使用慢查詢?nèi)罩?#xff0c;找出執(zhí)行慢的查詢。64、使用 EXPLAIN 來決定查詢功能是否合適。65、經(jīng)常測試你的查詢，看是否需要做性能優(yōu)化 — 性能可能會(huì)隨著時(shí)間的變化而變化。66、避免在整個(gè)表上使用count(*) ，它可能會(huì)將整個(gè)表鎖住。67、保持查詢一致，這樣后續(xù)類似的查詢就能使用查詢緩存了。68、如果合適，用 GROUP BY 代替 DISTINCT。69、在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引。70、保證索引簡單，不要在同一列上加多個(gè)索引。71、有時(shí)，MySQL 會(huì)選擇錯(cuò)誤的索引，這種情況使用 USE INDEX。72、使用 SQL_MODE=STRICT 來檢查問題。73、索引字段少于5個(gè)時(shí)，UNION 操作用 LIMIT，而不是 OR。74、使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE 來代替 UPDATE，避免 UPDATE 前需要先 SELECT。75、使用索引字段和 ORDER BY 來代替 MAX。76、避免使用 ORDER BY RAND()。77、LIMIT M,N 在特定場景下會(huì)降低查詢效率，有節(jié)制使用。78、使用 UNION 來代替 WHERE 子句中的子查詢。79、對 UPDATE 來說，使用 SHARE MODE 來防止排他鎖。80、重啟 MySQL 時(shí)，記得預(yù)熱數(shù)據(jù)庫，確保將數(shù)據(jù)加載到內(nèi)存，提高查詢效率。81、使用 DROP TABLE ，然后再 CREATE TABLE ，而不是 DELETE FROM ，以刪除表中所有數(shù)據(jù)。82、最小化你要查詢的數(shù)據(jù)，只獲取你需要的數(shù)據(jù)，通常來說不要使用 *。83、考慮持久連接，而不是多次建立連接，已減少資源的消耗。84、基準(zhǔn)查詢，包括服務(wù)器的負(fù)載，有時(shí)一個(gè)簡單的查詢會(huì)影響其他的查詢。85、當(dāng)服務(wù)器的負(fù)載增加時(shí)，使用SHOW PROCESSLIST來查看慢的/有問題的查詢。86、在存有生產(chǎn)環(huán)境數(shù)據(jù)副本的開發(fā)環(huán)境中，測試所有可疑的查詢。MySQL備份過程：87、在二級(jí)復(fù)制服務(wù)器上進(jìn)行備份。88、備份過程中停止數(shù)據(jù)的復(fù)制，以防止出現(xiàn)數(shù)據(jù)依賴和外鍵約束的不一致。89、徹底停止MySQL之后，再從數(shù)據(jù)文件進(jìn)行備份。90、如果使用MySQL dump進(jìn)行備份，請同時(shí)備份二進(jìn)制日志 — 確保復(fù)制過程不被中斷。91、不要信任 LVM 快照的備份 — 可能會(huì)創(chuàng)建不一致的數(shù)據(jù)，將來會(huì)因此產(chǎn)生問題。92、為每個(gè)表做一個(gè)備份，這樣更容易實(shí)現(xiàn)單表的恢復(fù) — 如果數(shù)據(jù)與其他表是相互獨(dú)立的。93、使用 mysqldump 時(shí)，指定 -opt 參數(shù)。94、備份前檢測和優(yōu)化表。95、臨時(shí)禁用外鍵約束，來提高導(dǎo)入的速度。96、臨時(shí)禁用唯一性檢查，來提高導(dǎo)入的速度。97、每次備份完后，計(jì)算數(shù)據(jù)庫/表數(shù)據(jù)和索引的大小，監(jiān)控其增長。98、使用定時(shí)任務(wù)(cron)腳本，來監(jiān)控從庫復(fù)制的錯(cuò)誤和延遲。99、定期備份數(shù)據(jù)。100、定期測試備份的數(shù)據(jù)。

總結(jié)

以上是生活随笔為你收集整理的MySQL优化调优有没有做过_MySQL 调优/优化的 100 个建议的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。