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