做后端開發(fā)、運(yùn)維的朋友幾乎都遇到過(guò)MySQL數(shù)據(jù)庫(kù)變慢的問(wèn)題：頁(yè)面加載轉(zhuǎn)圈、接口頻繁超時(shí)、數(shù)據(jù)庫(kù)CPU占用居高不下，甚至小數(shù)據(jù)量的查詢都要等好幾秒。很多人第一反應(yīng)是加索引，結(jié)果索引越建越多，查詢反而更慢，要么索引失效、要么冗余索引拖慢寫入，根本找不到問(wèn)題根源。其實(shí)MySQL性能問(wèn)題90%都出在慢查詢、索引設(shè)計(jì)不合理、參數(shù)配置不匹配這三個(gè)點(diǎn)上，只要用對(duì)方法，不用升級(jí)硬件就能讓數(shù)據(jù)庫(kù)性能翻倍。本文用最通俗的語(yǔ)言，從慢查詢定位、索引優(yōu)化、系統(tǒng)調(diào)優(yōu)三個(gè)核心維度，講透MySQL性能優(yōu)化的全流程，全是生產(chǎn)環(huán)境能直接落地的實(shí)戰(zhàn)技巧。

## 一、先找準(zhǔn)病根：MySQL慢查詢快速定位方法

優(yōu)化慢查詢的第一步不是改SQL、加索引，而是精準(zhǔn)找到到底哪條語(yǔ)句慢，憑感覺(jué)優(yōu)化只會(huì)越改越亂。MySQL自帶了完整的慢查詢監(jiān)控工具，不用額外裝插件，幾分鐘就能開啟并定位問(wèn)題。

首先開啟慢查詢?nèi)罩?，這是定位慢SQL的基礎(chǔ)，在MySQL配置文件my.cnf中添加以下配置，重啟后生效：

slow_query_log = 1（開啟慢查詢?nèi)罩荆?/p>

long_query_time = 1（閾值設(shè)為1秒，超過(guò)1秒的查詢都會(huì)被記錄）

log_queries_not_using_indexes = 1（記錄未使用索引的查詢，重點(diǎn)排查）

log_output = FILE（日志輸出到文件，方便查看）

開啟后，慢查詢?nèi)罩緯?huì)默認(rèn)存放在MySQL數(shù)據(jù)目錄下，文件名格式為主機(jī)名-slow.log。直接用tail -f 命令實(shí)時(shí)查看，就能看到所有超時(shí)的SQL語(yǔ)句，包括執(zhí)行時(shí)間、掃描行數(shù)、訪問(wèn)的表，一目了然。除了看日志，還可以用mysqldumpslow工具分析日志，快速篩選出執(zhí)行次數(shù)最多、耗時(shí)最長(zhǎng)的SQL，優(yōu)先優(yōu)化高頻慢查詢，性價(jià)比最高。

找到慢SQL后，下一步用EXPLAIN執(zhí)行計(jì)劃分析瓶頸，這是MySQL優(yōu)化的核心工具，只要在SQL前加EXPLAIN就能看到執(zhí)行細(xì)節(jié)。重點(diǎn)看這4個(gè)字段：type、key、rows、Extra。type代表查詢類型，從優(yōu)到劣依次是const、eq_ref、ref、range、index、ALL，ALL代表全表掃描，必須優(yōu)化；key顯示實(shí)際使用的索引，為空就是索引失效；rows是掃描的行數(shù)，數(shù)值越大越慢；Extra里出現(xiàn)Using filesort、Using temporary，說(shuō)明出現(xiàn)了文件排序和臨時(shí)表，會(huì)嚴(yán)重拖慢性能。

比如一條聯(lián)表查詢出現(xiàn)Using filesort，就是排序字段沒(méi)有用到索引，導(dǎo)致MySQL在內(nèi)存或磁盤里重新排序；出現(xiàn)Using temporary，是分組或去重時(shí)生成了臨時(shí)表，這兩個(gè)問(wèn)題都是優(yōu)化的重點(diǎn)。另外還可以用SHOW PROFILE查看SQL執(zhí)行的每個(gè)階段耗時(shí)，精準(zhǔn)定位是耗時(shí)在IO、排序還是鎖等待，讓優(yōu)化更有針對(duì)性。

## 二、核心突破口：MySQL索引優(yōu)化全攻略（避坑+實(shí)戰(zhàn)）

索引是MySQL優(yōu)化的性價(jià)比最高手段，用對(duì)了查詢速度能提升幾十倍，用錯(cuò)了反而會(huì)增加寫入、更新的開銷，還會(huì)占用大量存儲(chǔ)空間。很多人優(yōu)化失敗，都是因?yàn)橄萑肓恕竵y建索引、索引失效、冗余索引」三大誤區(qū)，下面講透索引設(shè)計(jì)的核心原則和失效場(chǎng)景。

首先明確索引設(shè)計(jì)的4個(gè)基礎(chǔ)原則：第一，只給高頻查詢字段建索引，別給所有字段都加索引，插入、更新時(shí)MySQL需要同步維護(hù)索引，索引越多寫入越慢；第二，優(yōu)先選高區(qū)分度字段，比如用戶ID、訂單號(hào)，區(qū)分度低的字段（如狀態(tài)、性別）單獨(dú)建索引沒(méi)用，只能配合高區(qū)分度字段做聯(lián)合索引；第三，聯(lián)合索引遵循最左前綴原則，聯(lián)合索引（a,b,c）只能命中a、a+b、a+b+c的查詢，跳過(guò)a直接查b、c會(huì)直接失效；第四，巧用覆蓋索引避免回表，把查詢的字段全部包含在索引里，MySQL不用回表查原數(shù)據(jù)，IO開銷大幅降低。

接下來(lái)講最常見的索引失效場(chǎng)景，這是生產(chǎn)環(huán)境踩坑最多的地方：一是索引列上做運(yùn)算、函數(shù)、類型轉(zhuǎn)換，比如where date(create_time) = '2024-01-01'，索引直接失效，改成create_time between '2024-01-01 00:00:00' and '2024-01-01 23:59:59'就能命中索引；二是使用like '%關(guān)鍵詞%'，左模糊匹配會(huì)導(dǎo)致全表掃描，只能用右模糊like '關(guān)鍵詞%'；三是使用or連接條件，一邊有索引一邊沒(méi)索引，整個(gè)查詢都會(huì)走全表掃描；四是聯(lián)合索引跳過(guò)最左列，比如索引（user_id,status），直接查status=1會(huì)失效；五是字段類型不匹配，比如字符串類型的手機(jī)號(hào)用數(shù)字查詢，索引失效。

然后是聯(lián)合索引的優(yōu)化技巧，等值查詢?cè)谇?，范圍查詢?cè)诤螅判蜃侄卧谧詈?。比如查詢where user_id=1 and status>0 order by create_time，聯(lián)合索引應(yīng)該建（user_id,status,create_time），這樣既能快速過(guò)濾數(shù)據(jù)，又能利用索引有序性避免文件排序。對(duì)于長(zhǎng)字符串字段，不用建全列索引，用前綴索引（如索引前10個(gè)字符），既能提升查詢速度，又能節(jié)省存儲(chǔ)空間。還要定期清理冗余索引和無(wú)用索引，用show index from 表名查看索引，刪除重復(fù)、從未使用的索引，減少維護(hù)開銷。

## 三、全面提升：MySQL數(shù)據(jù)庫(kù)性能調(diào)優(yōu)實(shí)戰(zhàn)

除了慢查詢和索引，SQL語(yǔ)句寫法、表結(jié)構(gòu)設(shè)計(jì)、服務(wù)器參數(shù)配置，都會(huì)直接影響MySQL性能，這部分優(yōu)化能解決索引覆蓋不到的性能瓶頸，讓數(shù)據(jù)庫(kù)在高并發(fā)、大數(shù)據(jù)量下穩(wěn)定運(yùn)行。

首先是SQL語(yǔ)句優(yōu)化，這是最容易落地的改動(dòng)：第一，禁止使用select *，只查詢需要的字段，減少數(shù)據(jù)傳輸和內(nèi)存消耗，尤其是大字段（文本、圖片）不要隨意查詢；第二，避免使用子查詢，改用join聯(lián)表，子查詢會(huì)生成臨時(shí)表，聯(lián)表查詢效率更高；第三，優(yōu)化分頁(yè)查詢，大數(shù)據(jù)量分頁(yè)用limit offset會(huì)掃描前面所有行，改成where id>上一頁(yè)最后ID limit 10的鍵集分頁(yè)，千萬(wàn)級(jí)數(shù)據(jù)分頁(yè)也能毫秒級(jí)響應(yīng)；第四，group by提前過(guò)濾數(shù)據(jù)，把where條件放在group by前面，減少分組的數(shù)據(jù)量，避免Using temporary；第五，批量操作替代循環(huán)單條執(zhí)行，批量插入、更新能大幅減少數(shù)據(jù)庫(kù)交互次數(shù)，比如插入1萬(wàn)條數(shù)據(jù)，循環(huán)單條插入要幾十秒，批量插入只要幾百毫秒。

然后是表結(jié)構(gòu)優(yōu)化，遵循小而精的原則：第一，合理選擇字段類型，能用int不用bigint，能用varchar(20)不用varchar(255)，日期用datetime或timestamp，不用字符串存儲(chǔ)，減少磁盤占用和IO開銷；第二，單表數(shù)據(jù)量控制在1000萬(wàn)行以內(nèi)，超過(guò)后分表拆分，避免大表查詢卡頓；第三，給每張表設(shè)自增主鍵，InnoDB引擎按主鍵聚簇存儲(chǔ)，自增主鍵能避免頁(yè)分裂，提升寫入性能；第四，減少join聯(lián)表數(shù)量，聯(lián)表超過(guò)3張效率會(huì)急劇下降，可通過(guò)冗余字段、中間表簡(jiǎn)化查詢。

最后是MySQL服務(wù)器參數(shù)調(diào)優(yōu)，重點(diǎn)優(yōu)化InnoDB引擎參數(shù)，因?yàn)楝F(xiàn)在主流業(yè)務(wù)都用InnoDB：第一，innodb_buffer_pool_size，這是InnoDB最重要的參數(shù)，負(fù)責(zé)緩存數(shù)據(jù)和索引，建議設(shè)為物理內(nèi)存的70%-80%，比如32G內(nèi)存的服務(wù)器設(shè)為24G，能大幅提升緩存命中率，減少磁盤IO；第二，innodb_log_file_size， redo日志文件大小，設(shè)為512M-1G，平衡寫入性能和崩潰恢復(fù)速度；第三，innodb_flush_log_at_trx_commit，控制日志刷新策略，追求高并發(fā)設(shè)為2，追求數(shù)據(jù)安全設(shè)為1；第四，max_connections，最大連接數(shù)，根據(jù)業(yè)務(wù)并發(fā)量調(diào)整，避免連接數(shù)不足導(dǎo)致業(yè)務(wù)報(bào)錯(cuò)；第五，query_cache，MySQL8.0已移除，5.7及以下版本建議關(guān)閉，高并發(fā)下緩存失效頻繁，反而影響性能。

高并發(fā)場(chǎng)景下，還可以做讀寫分離，主庫(kù)負(fù)責(zé)寫入，從庫(kù)負(fù)責(zé)讀取，分?jǐn)倲?shù)據(jù)庫(kù)壓力；熱點(diǎn)數(shù)據(jù)用Redis做緩存，減少M(fèi)ySQL的查詢壓力，避免緩存穿透、緩存擊穿問(wèn)題。另外，定期優(yōu)化表、分析表，清理表碎片，提升數(shù)據(jù)存儲(chǔ)的連續(xù)性，也能小幅提升查詢性能。