很多朋友催我整理一份MySQL學(xué)習(xí)筆記說市面上教程要么太散、要么太淺照著做總在細(xì)節(jié)上卡住。我從最早裝MySQL都裝不明白到后來在生產(chǎn)環(huán)境處理慢查詢、調(diào)事務(wù)、配主從這些年踩過的坑確實不少。這篇筆記我就把從入門到實戰(zhàn)的核心路線、常用命令、容易忽略的細(xì)節(jié)和排查經(jīng)驗一次性整理出來重點覆蓋安裝配置、常用SQL、索引、事務(wù)、鎖、存儲過程、連接查詢還有面試高頻考點和典型報錯排查方向。文章會盡量按實際操作的順序來寫新手可以照著一步步走有基礎(chǔ)的也可以直接跳到后面看問題和心得部分。1. 學(xué)習(xí)前必須想清楚的兩件事1.1 MySQL到底解決什么問題你的場景真的需要它嗎MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)核心就是把數(shù)據(jù)按照行和列的結(jié)構(gòu)存起來通過SQL語言做增刪改查。很多人一上來就背命令其實先搞清楚它解決什么問題更重要。簡單說只要有“多個人同時讀寫同一批結(jié)構(gòu)化數(shù)據(jù)”的需求MySQL就是很穩(wěn)妥的選型。它內(nèi)部有事務(wù)機制保證數(shù)據(jù)一致性有索引機制加速查詢有權(quán)限體系控制訪問范圍還有各種備份、復(fù)制、集群方案支撐業(yè)務(wù)增長。選型方面我要多說一句。如果你的項目只是單機小工具SQLite反而更輕量如果是海量非結(jié)構(gòu)化數(shù)據(jù)MongoDB或者對象存儲可能更合適如果只是簡單的內(nèi)存緩存Redis就夠了。MySQL適合的是“數(shù)據(jù)結(jié)構(gòu)穩(wěn)定、關(guān)系復(fù)雜、要求強一致性”的業(yè)務(wù)系統(tǒng)比如電商訂單、用戶賬號、內(nèi)容管理、進(jìn)銷存這類。學(xué)它之前先明確自己的實際場景才不會學(xué)了用不上也不會為了用而用。1.2 學(xué)習(xí)路線的建議別急著看源碼先把這三層打通我見過太多人一上來就鉆研InnoDB源碼或者B樹的分裂過程結(jié)果連基本的SQL都寫不利索反而打擊信心。合理的路線一般分三層走。第一層是“跑起來”完成安裝、能用命令行連上數(shù)據(jù)庫、能執(zhí)行簡單的建表和查詢先建立體感。第二層是“寫正確”把增刪改查、條件過濾、排序分組、多表連接這些語法練熟能獨立完成一個小項目的數(shù)據(jù)庫設(shè)計。第三層是“寫高效”這時候才去研究索引、事務(wù)隔離級別、死鎖排查、執(zhí)行計劃優(yōu)化這些進(jìn)階內(nèi)容。我個人的建議是前兩層不用花太多時間兩周左右就能覆蓋重點是盡早接觸真實數(shù)據(jù)。比如把手頭某個Excel表格搬到MySQL里模擬日常查詢需求這個過程比刷一百道練習(xí)題都有用。到第三層時再回到原理上深挖你會發(fā)現(xiàn)之前遇到的問題全都能對號入座學(xué)習(xí)效率高很多。2. 環(huán)境搭建Windows、Linux、Docker三種方式實測記錄2.1 Windows安裝MSI安裝器的一些細(xì)節(jié)Windows下最省事的是用官方MSI安裝包。下載時注意選擇MySQL Community Server版本不要下成Cluster或者其他的變體。安裝過程中有幾個選擇容易讓人懵我逐個說一下。端口默認(rèn)3306除非本機默認(rèn)端口已經(jīng)被占用否則不用改。認(rèn)證方式這里要特別留意MySQL 8.0默認(rèn)用caching_sha2_password而很多舊客戶端、舊驅(qū)動只支持mysql_native_password。如果后續(xù)用Navicat、Delphi的Firedac或者其他老驅(qū)動連接時報“does not support authentication protocol”問題多半出在這里后面常見問題部分我會詳細(xì)講解決辦法。配置密碼時一定要記好忘記root密碼是后續(xù)很多事故的開端。安裝完服務(wù)之后Windows服務(wù)里能看到“MySQL80”之類的服務(wù)名稱默認(rèn)啟動類型是自動。我第一次裝完總是啟動失敗后來發(fā)現(xiàn)是安裝目錄和數(shù)據(jù)目錄的權(quán)限問題用管理員身份運行安裝器基本能避免。還需要確認(rèn)一件事安裝器默認(rèn)會創(chuàng)建C:\ProgramData\MySQL下的數(shù)據(jù)目錄如果這個目錄被安全軟件攔截后續(xù)初始化也會失敗。2.2 Linux安裝apt和yum兩條路線Ubuntu/Debian系列用apt最簡單先更新索引然后安裝mysql-server。裝完默認(rèn)沒有設(shè)密碼直接sudo mysql進(jìn)入本地root再手動ALTER USER設(shè)置密碼。CentOS/RHEL系列用yum或者dnf默認(rèn)倉庫里的版本可能比較舊建議先配置官方倉庫再install mysql-community-server。裝完第一次啟動會生成臨時密碼在/var/log/mysqld.log里grep一下就能看到首次登錄會強制讓你改密碼。Linux下裝完千萬別急著跑先檢查兩件事。一是防火墻如果3306端口沒放行遠(yuǎn)程連接必掛二是bind-address默認(rèn)監(jiān)聽127.0.0.1要在配置里改成實際的監(jiān)聽地址或者用注釋的方式讓它監(jiān)聽所有地址。很多時候下載、安裝都成功就是連不上基本都是這兩處沒配。2.3 Docker安裝適合快速起測試環(huán)境Docker方式最推薦給做開發(fā)和測試的人它最大的優(yōu)勢是干凈、可重復(fù)、刪了不心疼。我自己常用的啟動命令是這樣的docker run -d \ --name mysql-test \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD123456 \ -e MYSQL_DATABASEdemo \ -e MYSQL_USERdemo_user \ -e MYSQL_PASSWORDdemo_pass \ mysql:8.0這里需要解釋幾點。MYSQL_ROOT_PASSWORD是root賬號的初始密碼MYSQL_DATABASE會自動創(chuàng)建一個數(shù)據(jù)庫MYSQL_USER和MYSQL_PASSWORD會創(chuàng)建普通用戶并授權(quán)給這個庫。端口映射-p 3306:3306表示把容器里的3306映射到宿主機的3306。生產(chǎn)環(huán)境一定要加-v參數(shù)把數(shù)據(jù)目錄掛載出來不然容器一刪數(shù)據(jù)全部消失這個坑我踩過教訓(xùn)很深刻。2.4 安裝后的驗證清單環(huán)境搭好后別急著進(jìn)入下一步先跑一組最小驗證命令確認(rèn)安裝沒問題mysql -u root -p SHOW VARIABLES LIKE version%; SHOW DATABASES; SELECT 1 5 AS result;這樣能確認(rèn)服務(wù)啟動正常、賬號權(quán)限正常、基礎(chǔ)表達(dá)式計算正常。我第一次裝完就是只執(zhí)行了mysql -V看到版本號就覺得行了結(jié)果過了一天重新連接才發(fā)現(xiàn)密碼策略導(dǎo)致的連接問題。老老實實走一遍清單后面少很多幺蛾子。3. 核心SQL從建庫到增刪改查的完整認(rèn)知3.1 庫和表的操作邏輯數(shù)據(jù)庫的概念就是一個邏輯上的容器里面放著各種表、視圖、存儲過程等對象。建庫時字符集一定要想清楚。我最推薦的組合是utf8mb4 utf8mb4_unicode_ciutf8mb4不是老舊的utf8它能存emoji表情也能存生僻字。很多項目上線之后出現(xiàn)亂碼問題源頭就是建庫時用了默認(rèn)的latin1。表的設(shè)計聚焦在字段類型上。字符串類型要考慮長度和排序規(guī)則數(shù)字類型要區(qū)分整數(shù)和浮點日期類型要看用DATE還是DATETIME。我在設(shè)計表時有一條經(jīng)驗?zāi)苡谜麛?shù)ID作為主鍵就不要用字符串IP地址可以存成整數(shù)用INET_ATON轉(zhuǎn)換查詢效率提升明顯金額字段一定要用DECIMAL不能用FLOAT因為浮點運算有精度問題涉及錢的時候絕不能省這個事。3.2 INSERT、UPDATE、DELETE的容易踩的點INSERT語句我踩過最多的坑是字符集和自增主鍵。字符集混亂會導(dǎo)致中文變問號自增主鍵要看清楚當(dāng)前值是多少備份恢復(fù)后容易出現(xiàn)主鍵沖突。批量插入百萬級數(shù)據(jù)時盡量用一個INSERT攜帶多條VALUES比一條條循環(huán)執(zhí)行快很多。UPDATE是初學(xué)者翻車率最高的語句。SQL里UPDATE沒有WHERE就更新整張表這幾乎是新手最容易造成的生產(chǎn)事故。我還記得自己第一次把線上用戶表的昵稱全部改成同一個值就是因為漏寫WHERE。所以現(xiàn)在我寫UPDATE的習(xí)慣是先寫SELECT把WHERE條件查一遍確認(rèn)影響行數(shù)再改成UPDATE語句。這個習(xí)慣雖然多一步但價值極大。另外MySQL默認(rèn)在事務(wù)里執(zhí)行UPDATE加的是行鎖如果條件沒走索引InnoDB會退化成鎖表高并發(fā)下極易產(chǎn)生鎖等待和死鎖。DELETE同樣要留意MySQL沒有撤銷刪除的功能誤刪只能靠備份恢復(fù)。所以刪除大表數(shù)據(jù)之前我習(xí)慣先查SELECT COUNT(*)再確認(rèn)WHERE條件最后用事務(wù)包起來執(zhí)行確認(rèn)沒問題再提交。但這里再補一句如果表數(shù)據(jù)量特別大一次性DELETE會留下大量碎片和長事務(wù)更合適的方式是分批刪除比如每次刪除1000條循環(huán)執(zhí)行這樣能減少鎖持有時間和日志壓力。3.3 SELECT查詢的骨架WHERE、ORDER BY、LIMITSELECT是使用頻率最高的語句它的執(zhí)行邏輯理解到位后面優(yōu)化才能上手。WHERE負(fù)責(zé)過濾行GROUP BY做分組聚合HAVING過濾分組結(jié)果ORDER BY排序LIMIT限制返回行數(shù)。關(guān)鍵點是執(zhí)行順序和書寫的順序不一致實際執(zhí)行順序大致是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。這個順序每多理解一分SQL優(yōu)化就多一分把握。比如很多人問“為什么WHERE里不能用SELECT里起的別名”就是因為WHERE比SELECT先執(zhí)行別名在WHERE階段根本還不存在。ORDER BY也一樣所以排序的分組字段一定要是源表真實字段。LIMIT的坑主要在分頁查詢。偏移量大的時候比如LIMIT 1000000, 20MySQL會把前100萬行都掃出來再扔掉性能極差。更好的方案是記錄上一頁最后一條記錄的主鍵或唯一索引用WHERE id ? LIMIT 20來實現(xiàn)“基于游標(biāo)”的分頁這樣能走索引不加掃描量。3.4 數(shù)據(jù)類型的選擇策略選字段類型不能只看“能不能存”還要看“效率怎么樣”。整數(shù)類型按范圍分TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT長度設(shè)置并不限制數(shù)值范圍只是影響顯示寬度這是非常多人的誤解。字符串里VARCHAR適合長度可變的字段CHAR適合定長字段但大量使用VARCHAR會導(dǎo)致行溢出具體性能問題要看實際場景。日期時間類型中DATETIME不帶時區(qū)信息TIMESTAMP帶有時區(qū)轉(zhuǎn)換國內(nèi)場景通常用DATETIME更直白。還有一個高頻概念MySQL里int5這樣的表達(dá)式運算只要兩邊是數(shù)字類型就能直接相加但如果字段是字符串型且插入了非數(shù)字內(nèi)容轉(zhuǎn)換會得到0這是一個容易造成數(shù)據(jù)錯誤的地方查數(shù)據(jù)前一定要先確認(rèn)字段類型。4. 進(jìn)階機制索引、事務(wù)、鎖與存儲過程4.1 索引到底是怎么工作的索引可以理解成書的目錄沒有目錄就要一頁頁翻有了目錄就能直接定位到章節(jié)。MySQL里最常用的索引底層是B樹它讓查詢只需要從根節(jié)點沿路徑找到葉子節(jié)點復(fù)雜度大概是O(log n)。相比B樹B樹把所有數(shù)據(jù)都存在葉子節(jié)點葉子節(jié)點之間還有鏈?zhǔn)竭B接做范圍查詢和排序非常高效。建索引的幾條原則我提一下頻繁出現(xiàn)在WHERE和JOIN條件里的字段適合建索引區(qū)分度高的字段建索引收益更明顯比如性別這種只有兩個值的字段建了索引意義不大聯(lián)合索引要遵守最左前綴原則查詢條件從左往右能匹配上的時候才會用到索引。如果你有(a,b)聯(lián)合索引查詢條件只有b的時候索引是用不上的。為了找到慢SQL的根源我用EXPLAIN的次數(shù)最多?？碋XPLAIN時重點看type列性能從好到差大致是const、eq_ref、ref、range、index、ALL。ALL代表全表掃描屬于需要重點優(yōu)化的對象。還有rows列和Extra列rows是預(yù)估掃描行數(shù)Extra里出現(xiàn)Using filesort或Using temporary時通常意味著查詢還有優(yōu)化空間常見辦法就是調(diào)整索引或者改SQL寫法。4.2 事務(wù)的ACID和隔離級別事務(wù)就是一組要么全部成功、要么全部回滾的操作。MySQL的InnoDB引擎默認(rèn)支持事務(wù)MyISAM不支持這也是為什么現(xiàn)在生產(chǎn)環(huán)境幾乎都用InnoDB。每次開啟事務(wù)后執(zhí)行的寫操作會先記錄到undo log里任何一步失敗都可以回滾到事務(wù)開始前的狀態(tài)。ACID是事務(wù)的四大特性原子性保證不可分割一致性保證數(shù)據(jù)狀態(tài)合法隔離性防止事務(wù)之間互相干擾持久性保證提交后不丟失。理解ACID時把原子性和一致性分開會更容易原子性解決“做不做完”的問題一致性解決“做了之后對不對”的問題。隔離級別是事務(wù)進(jìn)階里的重要內(nèi)容。MySQL默認(rèn)級別是REPEATABLE READ可重復(fù)讀。四個級別從低到高分別是READ UNCOMMITTED可能出現(xiàn)臟讀READ COMMITTED避免臟讀但可能出現(xiàn)不可重復(fù)讀REPEATABLE READ避免不可重復(fù)讀但可能出現(xiàn)幻讀SERIALIZABLE全部解決但性能代價極大。InnoDB在REPEATABLE READ下通過間隙鎖可以很大程度抑制幻讀所以很多場景下這個默認(rèn)級別是夠用的。事務(wù)開啟后要盡快提交這是我很想強調(diào)的經(jīng)驗。有的同事把整個業(yè)務(wù)邏輯都放進(jìn)一個事務(wù)一個接口跑好幾秒事務(wù)長時間不提交會導(dǎo)致鎖持有時間過長影響并發(fā)量不說還容易造成死鎖。事務(wù)里盡量只放必要的讀寫操作外部接口調(diào)用和耗時計算不要放在事務(wù)內(nèi)。4.3 鎖機制概述鎖是保證并發(fā)安全的底層手段。InnoDB支持行級鎖也支持表級鎖還有意向鎖這種偏底層的鎖類型。行級鎖并發(fā)性能高但管理和排查復(fù)雜表級鎖實現(xiàn)簡單但并發(fā)能力弱。最常見的行鎖分共享鎖和排他鎖共享鎖允許其他事務(wù)讀但不允許寫排他鎖不允許其他事務(wù)讀寫。普通SELECT默認(rèn)不加任何鎖只有顯式加FOR UPDATE或LOCK IN SHARE MODE才會產(chǎn)生鎖等待。死鎖是并發(fā)場景下的經(jīng)典問題。兩個事務(wù)分別持有對方需要的鎖互相等待MySQL檢測到死鎖后會自動回滾代價較小的事務(wù)。避免死鎖的幾個常用思路固定程序的訪問順序盡量讓所有事務(wù)都按相同的表順序去操作控制每個事務(wù)的鎖數(shù)量減少長事務(wù)保持查詢條件能用到索引避免鎖升級為表鎖。查看當(dāng)前鎖狀態(tài)的命令是SHOW ENGINE INNODB STATUS里面會打印最近一次死鎖的相關(guān)信息。4.4 存儲過程與觸發(fā)器用好它們但別濫用存儲過程是把一段SQL邏輯保存在數(shù)據(jù)庫端可以帶輸入輸出參數(shù)支持流程控制。好處是減少網(wǎng)絡(luò)傳輸、代碼集中管理壞處是維護(hù)成本高、調(diào)試驗證不方便。以我的經(jīng)驗看業(yè)務(wù)簡單的場景沒必要用存儲過程復(fù)雜的統(tǒng)計任務(wù)適合把存儲過程當(dāng)作定時批處理工具來用。如果團(tuán)隊里不只一個人負(fù)責(zé)數(shù)據(jù)庫存儲過程的版本管理一定要重視不然上線出問題很麻煩。觸發(fā)器的機制是表上發(fā)生INSERT、UPDATE、DELETE時自動執(zhí)行一段邏輯。最常用的場景是審計日志、更新時間戳自動維護(hù)、庫存聯(lián)動。真實開發(fā)里我不建議放重要業(yè)務(wù)邏輯在觸發(fā)器里因為它的執(zhí)行是隱式的出問題很難排查而且會影響主流程的寫入性能。我曾經(jīng)排查過一個訂單重復(fù)日志的問題最后發(fā)現(xiàn)是每個字段更新都觸發(fā)了兩次觸發(fā)器邏輯場面很尷尬。所以觸發(fā)器能做但盡量讓它“小且透明”。4.5 常用函數(shù)讓SQL少一點、快一點字符串函數(shù)里CONCAT、SUBSTRING、REPLACE、UPPER、LOWER用得最多。數(shù)字函數(shù)ROUND、CEIL、FLOOR處理精度問題很好用。日期函數(shù)DATE_FORMAT、DATEDIFF、NOW、DATE_ADD在統(tǒng)計報表里出現(xiàn)頻率很高。判斷函數(shù)IF、IFNULL、CASE WHEN讓查詢邏輯更緊湊。但函數(shù)用的時候要注意索引失效問題。比如在WHERE里寫了YEAR(create_time)2024把字段包進(jìn)函數(shù)之后索引通常就用不上了。正確寫法是create_time 2024-01-01 AND create_time 2025-01-01這樣既能走索引邏輯也更清晰。類似的情況還有對字段做計算、字符串拼接后再比較都是索引失效的高危寫法。5. 數(shù)據(jù)庫設(shè)計與連接查詢實戰(zhàn)5.1 設(shè)計規(guī)范三范式與反范式數(shù)據(jù)庫設(shè)計最基本的是范式理論。第一范式要求字段不可再分第二范式要求非主鍵字段完全依賴主鍵第三范式要求非主鍵字段之間不能有傳遞依賴。實際項目中完全遵守第三范式很容易讓查詢需要大量JOIN所以資深的做法是在工程里適度反范式比如冗余一些熱門查詢字段犧牲一點存儲換查詢性能。以學(xué)生課程成績系統(tǒng)為例通常至少要設(shè)計學(xué)生表、課程表、成績表三張表。學(xué)生表存學(xué)生基本信息課程表存課程信息成績表用student_id和course_id做聯(lián)合外鍵再存分?jǐn)?shù)和考試日期。這種設(shè)計避免了把成績字段塞進(jìn)學(xué)生表導(dǎo)致重復(fù)存儲的問題也符合業(yè)務(wù)邏輯自然延伸的方向。很多實際項目就是因為早期表設(shè)計不規(guī)范后面業(yè)務(wù)擴展時改起來痛苦不堪這個案例值得新手親手做一遍。5.2 連接查詢的9種組合別再把JOIN搞混了兩表連接查詢的場景非常多網(wǎng)上流傳的“9種組合”本質(zhì)上就是兩張表JOIN時按保留哪一側(cè)數(shù)據(jù)來劃分的典型寫法。覆蓋這幾種寫法后絕大多數(shù)連接需求都能表達(dá)清楚。INNER JOIN只返回兩表都匹配的行這是最常見的類型。LEFT JOIN返回左表全部行右表沒有匹配就補NULL。RIGHT JOIN反過來。FULL OUTER JOIN在MySQL里沒有直接實現(xiàn)需要用LEFT JOIN和RIGHT JOIN的結(jié)果做UNION合并。除了這三種基礎(chǔ)組合還有LEFT JOIN排除右表已匹配行的寫法也就是WHERE右表主鍵IS NULL對應(yīng)“只在左表不在右表”的數(shù)據(jù)RIGHT JOIN排除左表已匹配行同理。再加上UNION合并形成“兩表并集”“兩表對稱差集”以及CROSS JOIN交叉連接統(tǒng)計組合數(shù)加起來就湊成了大家說的9種組合。注意寫JOIN時一定要明確ON后面的關(guān)聯(lián)條件。漏掉ON會導(dǎo)致交叉連接結(jié)果行數(shù)是兩表行數(shù)的乘積數(shù)據(jù)量一旦大起來查詢會直接卡死這是新手特別容易踩的坑。5.3 子查詢和UNION什么時候用合適子查詢可以用在WHERE、FROM和SELECT里。WHERE里的子查詢適合過濾條件FROM里的子查詢相當(dāng)于臨時表SELECT里的子查詢一般用來做標(biāo)量計算。不過子查詢不是萬能的某些場景改成JOIN或窗口函數(shù)性能更好。比如“查詢每門課最高分的學(xué)生”這種問題用窗口函數(shù)ROW_NUMBER() OVER(PARTITION BY course_id ORDER BY score DESC)就能一次搞定往往比自連接和子查詢組合更清晰。UNION用來合并多個結(jié)果集注意UNION默認(rèn)去重UNION ALL不去重。如果不需要去重一定用UNION ALL因為去重會有額外排序代價。我在早期寫報表時經(jīng)常圖省事用UNION后來才發(fā)現(xiàn)其實很多地方用OR也能實現(xiàn)但兩者語義有區(qū)別OR不會自動去重返回行UNION會根據(jù)列值去重。這就是為什么有人問“MySQL的OR能去重嗎”時答案是否定的——去重要用DISTINCT或UNION。6. 運維與錯誤排查實戰(zhàn)中的典型問題快查6.1 認(rèn)證協(xié)議問題客戶端不支持caching_sha2_passwordMySQL 8.0默認(rèn)的認(rèn)證插件是caching_sha2_password很多老版本的客戶端和驅(qū)動不認(rèn)識它。使用Navicat 15以前的版本、某些版本的JDBC驅(qū)動、Delphi的Firedac組件時都會報類似“phys mysql client does not support authentication protocol requested”或“Authentication plugin caching_sha2_password cannot be loaded”的錯誤。解決辦法有兩種。如果你能升級客戶端就升級到支持MySQL 8的版本如果不能升級把對應(yīng)用戶改回mysql_native_passwordALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密碼; FLUSH PRIVILEGES;這里需要注意改了之后安全性弱于新插件但很多企業(yè)內(nèi)部用的老工具不得不這么處理。第二種做法是新建一個給老客戶端專用的用戶用mysql_native_password插件。這樣既不影響root賬號的安全策略也不用改全局配置。6.2 安裝配置卡住Configuration of MySQL Server is taking longWindows安裝MySQL 8過程中經(jīng)常出現(xiàn)安裝進(jìn)度卡在Configuration這一步的情況。多數(shù)原因是安裝器需要執(zhí)行初始化數(shù)據(jù)目錄、創(chuàng)建服務(wù)、啟動服務(wù)等動作如果電腦上安裝了安全軟件或者服務(wù)權(quán)限不足就會長時間卡住。我處理過的典型場景是用戶之前裝過MySQL但卸載不干凈殘留的服務(wù)或數(shù)據(jù)目錄導(dǎo)致新裝的實例無法初始化。建議先卸載干凈把C:\ProgramData\MySQL和C:\Program Files\MySQL下的殘留目錄手動清理再用管理員身份重新安裝。如果還卡著可以看安裝日志一般在%TEMP%\mysql_installer_*.log里里面會寫明具體卡在哪一步。另一個比較隱蔽的原因是機器名或用戶名包含中文字符某些安裝階段會處理不好這個遇到的時候很難排查但把當(dāng)前用戶換成純英文的Windows用戶就能過。6.3 端口、服務(wù)啟動、連接超時這類基礎(chǔ)問題端口3306被占用是最容易發(fā)現(xiàn)的錯誤netstat -ano | findstr 3306能看占用進(jìn)程。但還有個坑是MySQL自己配的端口和客戶端輸入的端口不一致比如改了配置的端口值但沒重啟服務(wù)。每次改完my.cnf或my.ini都要重啟服務(wù)才能生效很多人改完配置文件直接連不上就一臉懵。連接超時先分清楚是網(wǎng)絡(luò)問題、防火墻問題還是賬號權(quán)限問題。網(wǎng)絡(luò)層用ping驗證IP通不通防火墻在Windows端檢查入站規(guī)則在Linux端檢查firewalld或iptables賬號權(quán)限問題是user表里的host字段限制比如rootlocalhost就只能本機登錄遠(yuǎn)程登錄必須用root%之類的記錄。user表改完之后一定要FLUSH PRIVILEGES不然不生效。6.4 給已有重復(fù)數(shù)據(jù)的表加唯一約束業(yè)務(wù)表運行久了想給某個字段加唯一索引結(jié)果提示字段里有重復(fù)值這是很常見的問題。比如我接手過一個客戶表email字段里有多條重復(fù)記錄想建唯一約束直接報錯。正確的順序是先把重復(fù)數(shù)據(jù)清理掉再加約束。先分組找出重復(fù)項SELECT email, COUNT(*) AS cnt FROM customer GROUP BY email HAVING cnt 1;留下的通常是業(yè)務(wù)上那個最新的記錄所以可以先保留每組里id最小的一條然后把其他重復(fù)記錄刪除或合并。數(shù)據(jù)清理完后再ALTER TABLE ADD UNIQUE INDEX。這條經(jīng)驗在數(shù)據(jù)治理和報表項目里非常實用。還要提醒一點清理重復(fù)數(shù)據(jù)前要備份或者先跑通事務(wù)不要一上來就DELETE。6.5 數(shù)據(jù)庫同步與遷移DataX、Kettle、Sqoop的使用思路跨數(shù)據(jù)庫同步和數(shù)據(jù)遷移我接觸到的工具有DataX、Kettle、Sqoop等。DataX是阿里開源的數(shù)據(jù)同步工具適合MySQL、SQLServer、PostgreSQL、HDFS、TDengine等多個數(shù)據(jù)源之間的批量同步。它的核心是配置JSON格式的job描述文件把reader和writer參數(shù)寫清楚比如連接地址、用戶名、密碼、表名、切分鍵、并發(fā)數(shù)。我習(xí)慣在同步前先用較小的行數(shù)做試運行確認(rèn)數(shù)據(jù)一致后再跑全量。Kettle是圖形化ETL工具拖拽式操作對非開發(fā)者很友好做復(fù)雜的轉(zhuǎn)換流程時能省很多代碼但性能瓶頸通常集中在內(nèi)存和數(shù)據(jù)庫端大批量同步要控制好提交批次大小。Sqoop主要是在關(guān)系型數(shù)據(jù)庫和Hadoop生態(tài)之間做導(dǎo)入導(dǎo)出遇到SQLServer或者PostgreSQL要記得把對應(yīng)JDBC驅(qū)動放到Sqoop的lib目錄下否則連接就失敗。不管是哪個工具遷移后一定要做一致性校驗比如對比行數(shù)、對比關(guān)鍵字段的SUM值計算MD5等。我吃過一次虧同步工具返回成功但目標(biāo)表行數(shù)少了2萬原因是一個字段的轉(zhuǎn)換規(guī)則寫錯了所以任何工具都不能完全替代校驗這一步。7. 面試高頻知識點速記7.1 索引與執(zhí)行計劃面試環(huán)節(jié)問索引相關(guān)的概率非常高。比如為什么InnoDB用B樹而不是哈希表或者紅黑樹。哈希表適合等值查詢但不適合范圍查詢紅黑樹在數(shù)據(jù)量增大時層數(shù)會變深磁盤IO次數(shù)變多。B樹的葉子節(jié)點連成鏈表范圍查詢和順序讀取都很快同時所有數(shù)據(jù)都在葉子節(jié)點非葉子節(jié)點只存索引鍵可以容納更多節(jié)點把樹的高度控制得很低?；卮饡r結(jié)合磁盤IO和范圍查詢兩個角度會比死背結(jié)論有說服力。執(zhí)行計劃EXPLAIN里通常還會追問type等級重點關(guān)注possible_keys、key、rows、Extra幾列Extra里出現(xiàn)Using filesort或Using temporary要能解釋出原因和優(yōu)化手段。我之前被問過“索引為什么會失效”常見場景包括對索引列做函數(shù)運算、隱式類型轉(zhuǎn)換、LIKE前置通配符、JOIN時字符集不一致、OR條件里有非索引列。這些都能答出來面試基本就過關(guān)了。7.2 事務(wù)與隔離級別事務(wù)相關(guān)的高頻問題集中在隔離級別、臟讀、不可重復(fù)讀、幻讀的區(qū)別以及MySQL默認(rèn)為什么是REPEATABLE READ。一個經(jīng)典的追問是“REPEATABLE READ下幻讀不存在了嗎”答案要結(jié)合InnoDB的當(dāng)前讀和快照讀機制來講快照讀在事務(wù)首次讀取時建立快照后續(xù)讀到的是快照里的數(shù)據(jù)所以看不到新插入的行但當(dāng)前讀SELECT ... FOR UPDATE會通過間隙鎖來限制插入范圍避免幻讀。能說到“當(dāng)前讀”和“快照讀”這兩個詞面試官通常就會認(rèn)為你有實戰(zhàn)理解。MVCC多版本并發(fā)控制也常被提起。簡單說InnoDB通過undo log保存行的多個版本配合ReadView判斷當(dāng)前事務(wù)可以看到哪個版本從而在鎖開銷很小的情況下實現(xiàn)隔離。理解MVCC對排查線上數(shù)據(jù)不一致問題也有幫助不只是面試考點。7.3 SQL優(yōu)化與慢查詢排查優(yōu)化類問題一般會給一條慢SQL讓分析原因和優(yōu)化方案。我的固定思路是先看WHERE條件和JOIN條件是否都有合適的索引再看返回字段是否只是必要的列最后看是否有文件排序和臨時表。舉例來說如果發(fā)現(xiàn)一條統(tǒng)計報表的SQL慢可以先打開慢查詢?nèi)罩究磮?zhí)行時間再用EXPLAIN看掃描行數(shù)。遇到大表查詢第一反應(yīng)不是加內(nèi)存而是檢查是否缺少復(fù)合索引或者是否能在應(yīng)用層做緩存。SHOW PROFILE也可以用來定位查詢耗時所在階段但更常用的還是performance_schema里的events_statements_summary_by_digest它能按SQL模板聚合統(tǒng)計執(zhí)行次數(shù)、平均耗時、最大耗時。線上慢SQL的排查必須有這樣的工具支撐光靠看業(yè)務(wù)日志效率太低。8. 工具鏈與個人體會8.1 客戶端工具怎么選MySQL官方提供了MySQL Workbench功能全支持表設(shè)計、SQL開發(fā)、服務(wù)器狀態(tài)檢查適合剛?cè)腴T時熟悉數(shù)據(jù)庫對象但界面某些交互偏重。Navicat是很流行的商業(yè)工具功能很順手用的人多網(wǎng)上教程也多不過版本和MySQL 8的認(rèn)證插件兼容性問題要注意。DBeaver是免費開源的通用數(shù)據(jù)庫客戶端支持多種數(shù)據(jù)庫Linux和Windows都有版本本身基于JDBC所以只要能配好驅(qū)動連接問題和驅(qū)動版本問題會少很多。命令行工具mysql也別丟下。腳本化操作、服務(wù)器上沒有圖形界面時命令行才是真正可靠的手段。建議至少熟練使用mysql -u xxx -p -h xxx -P xxx、source filename.sql導(dǎo)入、mysqldump導(dǎo)出這些基礎(chǔ)命令。我見過不少開發(fā)者圖形界面用得飛起一到線上環(huán)境就手足無措這個基本功還是得扎實。8.2 JDBC驅(qū)動與連接池Java連接MySQL時JDBC驅(qū)動版本要和數(shù)據(jù)庫版本匹配。MySQL 8.0對應(yīng)的驅(qū)動是mysql-connector-java 8.x連接URL建議加上serverTimezoneAsia/Shanghai和useSSLfalse避免時區(qū)異常和SSL握手警告。驅(qū)動類名也變了老的是com.mysql.jdbc.Driver新的是com.mysql.cj.jdbc.Driver寫錯會直接報ClassNotFoundException。連接池方面HikariCP是當(dāng)前Spring Boot默認(rèn)的連接池性能好配置簡單。Druid是阿里的連接池有監(jiān)控SQL和Web頁面國內(nèi)項目中使用很廣。連接池的核心參數(shù)包括最大連接數(shù)、最小空閑連接數(shù)、連接超時時間、空閑回收時間。大流量項目里最大連接數(shù)不是越大越好因為每個連接都占用數(shù)據(jù)庫端的線程和內(nèi)存盲目調(diào)大會拖垮數(shù)據(jù)庫。具體要結(jié)合壓測數(shù)據(jù)和業(yè)務(wù)QPS來定。8.3 我這些年養(yǎng)成的學(xué)習(xí)習(xí)慣最后分享幾個在實際工作中幫助很大的習(xí)慣。第一每學(xué)一個新命令都用一個臨時數(shù)據(jù)庫做實驗別在生產(chǎn)庫里試。我自己的電腦上常年跑著一個Docker MySQL容器專門用來折騰。第二SQL寫完之后先看EXPLAIN不看執(zhí)行計劃的SQL都不算寫完。第三建表時把注釋寫全。字段注釋就是給別人和自我未來的文檔團(tuán)隊協(xié)作時沒有注釋的表維護(hù)起來痛苦程度非常高。第四定期看慢查詢?nèi)罩竞湾e誤日志這兩份日志就是數(shù)據(jù)庫的體檢報告。第五做任何批量更新和刪除之前把WHERE條件原樣套到SELECT上先查一遍這個習(xí)慣我已經(jīng)堅持了很多年確實幫我擋掉了好幾次事故。MySQL是個很老牌的工具表面看起來簡單實際深入后會發(fā)現(xiàn)查詢優(yōu)化、事務(wù)并發(fā)、運維備份的話題根本沒有盡頭。這篇筆記是我個人學(xué)習(xí)路線和實戰(zhàn)經(jīng)驗的整理不一定適合所有人但如果你照著走一遍從安裝到面試再到排查問題至少能有一條清晰的路不會像無頭蒼蠅一樣亂撞。