據(jù)庫(kù)基礎(chǔ)實(shí)戰(zhàn)：從建表規(guī)范到索引優(yōu)化與故障排查)
帶過(guò)不少新人發(fā)現(xiàn)一個(gè)很有意思的現(xiàn)象很多人簡(jiǎn)歷上寫著“熟悉 MySQL”可真讓他建一張表、寫一條稍微復(fù)雜一點(diǎn)的查詢往往就開(kāi)始卡殼。這不能全怪誰(shuí)MySQL 基礎(chǔ)這門課看著簡(jiǎn)單實(shí)際新手期踩的坑一點(diǎn)也不少。作為目前應(yīng)用最廣泛的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù)MySQL 不管是做 Web 應(yīng)用、移動(dòng)端后端還是數(shù)據(jù)倉(cāng)庫(kù)的中間層幾乎處處都能看到它的影子。這篇文章不打算堆砌高深理論而是按一條完整的實(shí)操主線帶你把 MySQL 數(shù)據(jù)庫(kù)基礎(chǔ)扎扎實(shí)實(shí)走一遍從環(huán)境搭建開(kāi)始到建庫(kù)建表、增刪改查、索引優(yōu)化再到日常工作里最高頻的故障排查。適合剛?cè)胄邢胂到y(tǒng)補(bǔ)數(shù)據(jù)庫(kù)能力的開(kāi)發(fā)者也適合工作中要寫 SQL 但總碰壁的數(shù)據(jù)分析師準(zhǔn)備面試想鞏固基礎(chǔ)的同學(xué)同樣可以照著過(guò)一遍。1. 內(nèi)容整體設(shè)計(jì)與思路拆解1.1 MySQL 在技術(shù)棧中的位置與價(jià)值先說(shuō)說(shuō)為什么值得花時(shí)間學(xué) MySQL。很多剛接觸編程的朋友會(huì)有個(gè)誤區(qū)覺(jué)得現(xiàn)在 NoSQL 挺火好像什么場(chǎng)景都能用 MongoDB、Redis 頂上。但實(shí)際在業(yè)務(wù)系統(tǒng)里關(guān)系型數(shù)據(jù)庫(kù)依然是絕對(duì)主力。MySQL 的優(yōu)勢(shì)很明確免費(fèi)開(kāi)源、跨平臺(tái)、穩(wěn)定可靠、生態(tài)極其成熟。無(wú)論是個(gè)人獨(dú)立開(kāi)發(fā)的博客系統(tǒng)還是企業(yè)級(jí)的訂單、用戶、權(quán)限模塊MySQL 都是數(shù)據(jù)庫(kù)選型清單上的第一梯隊(duì)。從技術(shù)棧的角度看MySQL 屬于存儲(chǔ)層承擔(dān)著“持久化”這個(gè)核心職責(zé)。程序可以重啟緩存可以失效但用戶數(shù)據(jù)必須躺在數(shù)據(jù)庫(kù)里不能丟。這就決定了我們不僅要會(huì)寫 SQL還得理解數(shù)據(jù)是怎么組織的、查詢是怎么跑的否則哪天線上出個(gè)慢查詢或者數(shù)據(jù)莫名其妙的丟了你連排查方向都沒(méi)有。我之前帶過(guò)一個(gè)新人功能開(kāi)發(fā)沒(méi)問(wèn)題一到數(shù)據(jù)相關(guān)的活就發(fā)怵。后來(lái)我發(fā)現(xiàn)他不是笨而是缺一套系統(tǒng)的基礎(chǔ)框架。MySQL 的基礎(chǔ)知識(shí)點(diǎn)其實(shí)是成體系的東學(xué)一點(diǎn)西學(xué)一點(diǎn)永遠(yuǎn)都是散的。只要把“實(shí)例 → 庫(kù) → 表 → 數(shù)據(jù) → 索引 → 事務(wù)”這條鏈路理清楚后面再接觸任何關(guān)系型數(shù)據(jù)庫(kù)都會(huì)很順暢。1.2 基礎(chǔ)學(xué)習(xí)路徑的設(shè)計(jì)邏輯MySQL 基礎(chǔ)的學(xué)習(xí)我比較推薦一條主線先搞懂 MySQL 是怎么跑起來(lái)的再學(xué)庫(kù)和表的創(chuàng)建然后是 DML 語(yǔ)句增刪改查再往后是索引和事務(wù)。為什么是這條線因?yàn)樗前凑諗?shù)據(jù)在系統(tǒng)中的流轉(zhuǎn)順序來(lái)設(shè)計(jì)的。舉個(gè)例子你寫一個(gè)用戶注冊(cè)功能。用戶點(diǎn)提交按鈕后端收到請(qǐng)求首先要連接上 MySQL 實(shí)例然后往庫(kù)里某張表插入一條記錄。如果表結(jié)構(gòu)設(shè)計(jì)得不對(duì)或者索引沒(méi)建好后續(xù)查詢就會(huì)越用越慢。這個(gè)鏈條非常直觀實(shí)例是地基庫(kù)和表是房間數(shù)據(jù)是房間里擺的東西索引相當(dāng)于給每件東西編了個(gè)檢索目錄事務(wù)則是確保你同時(shí)搬多件東西時(shí)不至于磕碰損壞。很多自學(xué)者容易犯的毛病是上來(lái)就啃 B 樹(shù)原理、隔離級(jí)別這些偏理論的東西結(jié)果連一張表都建不利索。說(shuō)實(shí)話基礎(chǔ)階段你要掌握的 SQL 不超過(guò)二十種核心就四類增、刪、改、查。把這些寫熟練理解 WHERE 的執(zhí)行邏輯再搞清楚 JOIN 是怎么把多張表關(guān)聯(lián)起來(lái)的日常開(kāi)發(fā)里九成以上和數(shù)據(jù)庫(kù)相關(guān)的活你都能應(yīng)付。剩下的問(wèn)題基本都是用多了之后自然遇到的。這篇文章就按這個(gè)思路展開(kāi)保證你照著做完能實(shí)打?qū)嵣鲜帧?. 環(huán)境搭建與準(zhǔn)備2.1 安裝方式選擇與避坑要學(xué) MySQL第一步當(dāng)然是裝環(huán)境。很多人在這一步就被勸退了其實(shí)大可不必。目前主流版本是 MySQL 8.0 和 5.7如果是從零開(kāi)始學(xué)直接選 8.0 就好一方面它性能更好另一方面 8.0 的默認(rèn)字符集是 utf8mb4對(duì)中文和各種生僻字的支持比 5.7 省心很多。具體安裝方式分兩類。在 Windows 上建議直接去官網(wǎng)下載 MySQL Installer它會(huì)幫你把服務(wù)端、命令行客戶端、Workbench 圖形工具一起裝好選默認(rèn)配置即可。在 Linux 服務(wù)器上我一般用系統(tǒng)自帶的包管理工具。比如在 Ubuntu 上執(zhí)行sudo apt install mysql-server就能搞定。CentOS 上則是sudo yum install mysql-server。裝完后先別急著用有幾件事必須確認(rèn)。第一件是服務(wù)是否開(kāi)機(jī)自啟?？梢杂胹udo systemctl enable mysql設(shè)置開(kāi)機(jī)自啟再通過(guò)sudo systemctl status mysql或者 Windows 下的services.msc去檢查服務(wù)運(yùn)行狀態(tài)。第二件是初始密碼。MySQL 8.0 在 Linux 上安裝后root 用戶默認(rèn)用 auth_socket 插件認(rèn)證意思是你只能用系統(tǒng) root 身份登錄這一步很迷惑很多新手會(huì)卡在密碼不對(duì)。解決辦法很簡(jiǎn)單登錄后改一下認(rèn)證方式就行。第三件是測(cè)試連接命令行敲mysql -u root -p能進(jìn)到mysql提示符就說(shuō)明成功。這里要重點(diǎn)提醒一句生產(chǎn)環(huán)境永遠(yuǎn)不要用 root 直接操作業(yè)務(wù)庫(kù)。正確做法是創(chuàng)建一個(gè)業(yè)務(wù)專用賬號(hào)只授予它操作特定庫(kù)的權(quán)限。在練習(xí)階段為了方便可以用 root但心里要繃著這根弦。2.2 配置文件里的關(guān)鍵參數(shù)MySQL 裝好后主要的配置文件在 Linux 下是/etc/mysql/my.cnf部分發(fā)行版是/etc/my.cnfWindows 下是安裝目錄里的my.ini。新手剛開(kāi)始不用動(dòng)太多配置但有幾個(gè)參數(shù)值得了解因?yàn)槟阍缤頃?huì)碰上。第一個(gè)是port默認(rèn)是 3306。如果和本地其他服務(wù)端口沖突你就需要改端口同時(shí)記得同步改防火墻規(guī)則。第二個(gè)是character-set-server建議設(shè)置成utf8mb4。我之前接過(guò)一個(gè)老項(xiàng)目數(shù)據(jù)庫(kù)默認(rèn)字符集是 latin1結(jié)果存中文進(jìn)去直接變成亂碼折騰了一下午。教訓(xùn)就是字符集一開(kāi)始就要設(shè)置對(duì)別等寫入數(shù)據(jù)了再改。第三個(gè)是max_connections控制最大連接數(shù)默認(rèn)是 151如果你的應(yīng)用并發(fā)高這個(gè)值可能要調(diào)大。但要注意連不上數(shù)據(jù)庫(kù)不一定就是連接數(shù)不夠后面我會(huì)細(xì)說(shuō)。還有一個(gè)容易被忽略的是sql_mode。MySQL 8.0 默認(rèn)的 sql_mode 包含ONLY_FULL_GROUP_BY這意味著你寫 GROUP BY 查詢時(shí)SELECT 的字段必須在 GROUP BY 里出現(xiàn)過(guò)否則直接報(bào)錯(cuò)。很多從 5.7 時(shí)代過(guò)來(lái)的人會(huì)被這個(gè)坑到。如果你不想改全局配置可以在本次會(huì)話里執(zhí)行SET SESSION sql_mode ALLOW_INVALID_DATES;臨時(shí)調(diào)整但這只是應(yīng)急徹底解決方案還是把 SQL 寫規(guī)范。2.3 可視化工具還是命令行很多人會(huì)問(wèn)我推薦用哪種方式操作 MySQL。我的看法是命令行客戶端必須得會(huì)因?yàn)榫€上排查問(wèn)題的時(shí)候可能只有命令行可用但日常開(kāi)發(fā)中用可視化工具效率更高。如果你用的是 Windows推薦裝 MySQL Workbench雖然有點(diǎn)重但功能全支持 ER 圖、查詢分析、數(shù)據(jù)導(dǎo)入導(dǎo)出。我自己日常用得比較多的是一款名為 DBeaver 的開(kāi)源工具跨平臺(tái)、免費(fèi)、支持各種數(shù)據(jù)庫(kù)界面也比較友好。不過(guò)有一點(diǎn)得說(shuō)清楚可視化工具再方便也替代不了你對(duì) SQL 本身的理解。你遲早會(huì)需要手動(dòng)寫復(fù)雜的 JOIN、子查詢或者存儲(chǔ)過(guò)程這些在圖形界面里點(diǎn)點(diǎn)點(diǎn)是點(diǎn)不出來(lái)的。所以以下所有實(shí)操我都默認(rèn)你打開(kāi)命令行或者任意一個(gè)工具的 SQL 編輯器窗口跟著敲命令這樣才能形成肌肉記憶。3. 建庫(kù)建表基礎(chǔ)中的基礎(chǔ)3.1 數(shù)據(jù)庫(kù)的創(chuàng)建、修改與刪除登錄 MySQL 之后第一個(gè)操作就是看有多少庫(kù)命令是SHOW DATABASES;。默認(rèn)會(huì)有information_schema、mysql、performance_schema、sys這幾個(gè)系統(tǒng)庫(kù)千萬(wàn)別去動(dòng)它們。自己建庫(kù)的命令很簡(jiǎn)單CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;這里我建議你把IF NOT EXISTS養(yǎng)成習(xí)慣以后跑腳本不會(huì)因?yàn)橹貜?fù)執(zhí)行而報(bào)錯(cuò)。DEFAULT CHARACTER SET直接指定utf8mb4COLLATE選擇utf8mb4_unicode_ci意思是字符串排序和比較的規(guī)則用 Unicode 通用規(guī)則。字符集和排序規(guī)則決定了很多細(xì)節(jié)比如查詢時(shí)是否區(qū)分大小寫、中文排序是否按拼音新手階段直接按我給的這套配置來(lái)穩(wěn)妥。建好之后用USE shop;切換到這個(gè)庫(kù)。想確認(rèn)當(dāng)前在哪個(gè)庫(kù)用SELECT DATABASE();這個(gè)命令在排查問(wèn)題時(shí)會(huì)很實(shí)用。刪除數(shù)據(jù)庫(kù)是DROP DATABASE shop;操作前務(wù)必確認(rèn)你刪的是哪個(gè)庫(kù)。我在實(shí)際工作中看到過(guò)有人一條DROP下去整個(gè)業(yè)務(wù)庫(kù)沒(méi)了當(dāng)時(shí)入職不到一個(gè)月的實(shí)習(xí)生那個(gè)場(chǎng)面至今難忘。所以建議你刪庫(kù)之前先查詢一下庫(kù)里的表數(shù)量做好備份再執(zhí)行刪除。3.2 表的字段設(shè)計(jì)類型和約束怎么選建表是整個(gè) MySQL 基礎(chǔ)里最見(jiàn)功底的部分。表字段設(shè)計(jì)得好不好直接影響后續(xù)的查詢性能和數(shù)據(jù)完整性。先看一個(gè)例子CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主鍵, username VARCHAR(50) NOT NULL COMMENT 用戶名, email VARCHAR(100) DEFAULT NULL COMMENT 郵箱, age TINYINT UNSIGNED DEFAULT 0 COMMENT 年齡, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時(shí)間, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新時(shí)間, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用戶表;這條建表語(yǔ)句包含了幾個(gè)核心知識(shí)點(diǎn)。主鍵我用的BIGINT UNSIGNED AUTO_INCREMENT這是最常見(jiàn)的自增主鍵方案。有人喜歡用 UUID 做主鍵但在 InnoDB 引擎里主鍵是聚簇索引UUID 是隨機(jī)字符串會(huì)導(dǎo)致頁(yè)分裂和碎片增多查詢效率不如自增整數(shù)。所以沒(méi)有特殊理由業(yè)務(wù)表首選自增主鍵。username字段加了NOT NULL和唯一索引這是因?yàn)橛脩裘仨氂星椅ㄒ?。age用TINYINT UNSIGNED因?yàn)槿说哪挲g絕不會(huì)是負(fù)數(shù)用無(wú)符號(hào)小整數(shù)就夠存了。這里想說(shuō)明一個(gè)選擇字段類型的核心原則在滿足需求的前提下用最小的存儲(chǔ)類型。你不需要算到每一比特但要心里有數(shù)。DATETIME存日期時(shí)間8 字節(jié)TIMESTAMP也是 8 字節(jié)5.7 之后但有 2038 年問(wèn)題。業(yè)務(wù)系統(tǒng)里我一般用DATETIME。created_at和updated_at的默認(rèn)值設(shè)置能讓插入和更新時(shí)自動(dòng)維護(hù)時(shí)間省去不少代碼。還有一個(gè)非常容易踩的坑是TEXT/BLOB字段。這類大字段不能有默認(rèn)值而且查詢時(shí)會(huì)占用更多 IO。如果一張表有好幾個(gè)TEXT字段很容易造成表體積巨大影響全表掃描性能。能用VARCHAR就不用TEXT這個(gè)習(xí)慣越早養(yǎng)成越好。3.3 表結(jié)構(gòu)的修改與刪除表建好之后不是一成不變的。業(yè)務(wù)迭代中經(jīng)常要加字段命令是ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT 手機(jī)號(hào) AFTER email;AFTER email的意思是放在 email 字段后面這純粹是為了看著舒服不影響功能。修改字段類型或?qū)傩訟LTER TABLE users MODIFY COLUMN phone VARCHAR(30) NOT NULL COMMENT 手機(jī)號(hào);RENAME 改名ALTER TABLE users RENAME COLUMN phone TO mobile;刪除字段ALTER TABLE users DROP COLUMN mobile;DROP TABLE users;是刪表TRUNCATE TABLE users;是清空表但保留表結(jié)構(gòu)。這兩個(gè)命令從“能不能找回”這個(gè)角度看幾乎是不可逆操作。我的實(shí)操習(xí)慣是在測(cè)試庫(kù)隨便玩但在生產(chǎn)環(huán)境執(zhí)行任何ALTER、DROP、TRUNCATE之前先把庫(kù)表結(jié)構(gòu)和數(shù)據(jù)導(dǎo)出一份備份。走一遍備份幾分鐘的事但能救命的次數(shù)絕對(duì)超乎你想象。4. 增刪改查從會(huì)用 SQL 到寫出像樣的 SQL4.1 INSERT插入數(shù)據(jù)的花式操作增刪改查CRUD是數(shù)據(jù)庫(kù)最核心的操作其中 INSERT 是打底基本功。最簡(jiǎn)單的單條插入INSERT INTO users (username, email, age) VALUES (zhangsan, zsexample.com, 25);注意主鍵 id 你沒(méi)有寫它會(huì)自動(dòng)遞增。第二條語(yǔ)句之前我需要強(qiáng)調(diào)一個(gè)細(xì)節(jié)INSERT時(shí)最好把字段名顯式列出來(lái)而不是直接寫INSERT INTO users VALUES (...)。一旦表結(jié)構(gòu)發(fā)生了變化后面的字段對(duì)不上就全亂套了。顯式列字段名代碼的可讀性和健壯性都會(huì)好很多。批量插入是提高效率的關(guān)鍵。比如你要一次性造 100 條測(cè)試數(shù)據(jù)一條一條插會(huì)很慢INSERT INTO users (username, email, age) VALUES (zhangsan, zsexample.com, 25), (lisi, lsexample.com, 30), (wangwu, wwexample.com, 22);批量插入的執(zhí)行速度比單條逐次插入快得多因?yàn)樗唤馕鲆淮?SQL。但這個(gè) INSERT 的長(zhǎng)度也不是越大越好我一般在單條語(yǔ)句里控制在一千行以內(nèi)否則可能超過(guò) MySQL 的 max_allowed_packet 限制報(bào)錯(cuò)或者超時(shí)。還有一個(gè)高級(jí)技巧叫INSERT ... ON DUPLICATE KEY UPDATE可以在插入時(shí)遇到唯一鍵沖突就自動(dòng)執(zhí)行更新INSERT INTO users (username, email, age) VALUES (zhangsan, zs_newexample.com, 26) ON DUPLICATE KEY UPDATE email VALUES(email), age VALUES(age);這個(gè)命令在做數(shù)據(jù)同步、去重更新時(shí)很有用比先查再插快得多。但我建議新手不要一上來(lái)就寫這種復(fù)雜語(yǔ)句先把基礎(chǔ) INSERT 寫明白再逐步進(jìn)階。4.2 SELECT不僅僅是“讀出來(lái)”SELECT 是 SQL 里出現(xiàn)頻率最高、也最考驗(yàn)功底的語(yǔ)句。很多人停留在SELECT * FROM users;這種級(jí)別這不太夠。查詢優(yōu)化從字段選擇就開(kāi)始了。第一條原則盡量別用SELECT *。原因有兩個(gè)一是如果表字段很多查出來(lái)的數(shù)據(jù)量會(huì)很大白白占用網(wǎng)絡(luò)和內(nèi)存二是當(dāng)你用代碼的 ORM 映射數(shù)據(jù)時(shí)多余字段會(huì)影響序列化和傳輸性能。業(yè)務(wù)代碼里只取你真正需要的字段。比如SELECT username, age FROM users WHERE age 20 ORDER BY age DESC LIMIT 10;這條語(yǔ)句涵蓋了幾個(gè)最重要的子句SELECT選字段WHERE過(guò)濾行ORDER BY排序LIMIT限制條數(shù)。執(zhí)行順序從邏輯上看是FROM→WHERE→SELECT→ORDER BY→LIMIT。理解這個(gè)順序你才能解釋為什么WHERE里不能用別名而ORDER BY里可以。LIKE模糊查詢是新手經(jīng)常玩出問(wèn)題的點(diǎn)。比如查用戶名包含“張”的人寫成WHERE username LIKE %張%。如果用例測(cè)試沒(méi)問(wèn)題等上線數(shù)據(jù)量大之后你才會(huì)發(fā)現(xiàn)前面的通配符會(huì)讓索引失效導(dǎo)致全表掃描。所以能用精確查詢就不用LIKE非要用模糊匹配也盡量寫成張%這種前后綴可預(yù)測(cè)的模式比如只查“張開(kāi)頭”的用戶這樣索引可能還有機(jī)會(huì)用上。分組統(tǒng)計(jì)也是日常需求。比如統(tǒng)計(jì)每個(gè)年齡段的用戶數(shù)SELECT age, COUNT(*) AS cnt FROM users GROUP BY age HAVING cnt 1;我特別想強(qiáng)調(diào)WHERE和HAVING的區(qū)別WHERE在分組前過(guò)濾行HAVING在分組后過(guò)濾組。很多初學(xué)者一開(kāi)始分不清但只要想想執(zhí)行順序就不容易混了。GROUP BY之后SELECT 后面能出現(xiàn)的字段基本就只能是分組字段或聚合函數(shù)了剛才我提到的 sql_mode 里的ONLY_FULL_GROUP_BY管的就是這件事。JOIN 關(guān)聯(lián)查詢是新手最難啃的骨頭。用一個(gè)簡(jiǎn)單的例子訂單表 orders 里有 user_id要查出每個(gè)訂單的用戶名用 INNER JOINSELECT o.order_no, o.amount, u.username FROM orders o INNER JOIN users u ON o.user_id u.id;JOIN的本質(zhì)是笛卡爾積的過(guò)濾ON 條件就是過(guò)濾條件。左連接LEFT JOIN則以左表為主右表沒(méi)有匹配的行就補(bǔ) NULL。初學(xué)的時(shí)候我建議你腦子里把兩張表擺開(kāi)自己“畫(huà)”一遍連接的過(guò)程比死記硬背圖管用。大多數(shù)慢查詢問(wèn)題根源多半是關(guān)聯(lián)字段沒(méi)有索引。所以寫 JOIN 之前先檢查兩表的關(guān)聯(lián)字段是否都建了索引這一點(diǎn)比任何優(yōu)化手段都實(shí)在。4.3 UPDATE 和 DELETE手一抖就是事故UPDATE 和 DELETE 在業(yè)務(wù)里最危險(xiǎn)因?yàn)橐坏┎僮鞑划?dāng)影響的不是一兩行而是整張表。我曾經(jīng)在一個(gè)測(cè)試環(huán)境里一條沒(méi)帶 WHERE 條件的 UPDATE直接把全表的 age 更新成了同一個(gè)值。雖然測(cè)試環(huán)境無(wú)所謂但它讓我養(yǎng)成了一個(gè)條件反射寫 UPDATE 和 DELETE 之前先寫明 WHERE并且先 SELECT 一下確認(rèn)影響范圍。UPDATE users SET age 26 WHERE username zhangsan;如果你想更新多行可以用WHERE id IN (1,2,3)或者WHERE age BETWEEN 20 AND 30。刪數(shù)據(jù)同理DELETE FROM users WHERE id 10086;DELETE 和 UPDATE 一樣事務(wù)里可以回滾但這也依賴你是不是開(kāi)啟了事務(wù)。默認(rèn)情況下MySQL 的 InnoDB 引擎在執(zhí)行單條語(yǔ)句時(shí)會(huì)自動(dòng)提交這意味著一條DELETE下去數(shù)據(jù)立刻生效沒(méi)有后悔的余地。想要更安全可以手動(dòng)控制事務(wù)START TRANSACTION; DELETE FROM users WHERE id 10086; -- 檢查一下結(jié)果如果不對(duì) ROLLBACK; -- 如果沒(méi)問(wèn)題 COMMIT;這個(gè)習(xí)慣在操作生產(chǎn)數(shù)據(jù)時(shí)極其重要。我做線上數(shù)據(jù)處理任務(wù)時(shí)永遠(yuǎn)都是先 SELECT 確認(rèn)目標(biāo)行數(shù)再開(kāi)事務(wù) DELETE查一遍結(jié)果最后才 COMMIT。這套流程看著繁瑣但出過(guò)事的人才知道它的價(jià)值。5. 索引與查詢優(yōu)化基礎(chǔ)5.1 索引到底解決了什么問(wèn)題很多人把索引理解成書(shū)的目錄這個(gè)類比雖然粗淺但方向是對(duì)的只是要更精確一些。MySQL 的 InnoDB 引擎使用的 B 樹(shù)索引本質(zhì)上是一種有序的數(shù)據(jù)結(jié)構(gòu)。沒(méi)有索引的時(shí)候查詢一條記錄只能全表掃描一條一條地匹配時(shí)間復(fù)雜度是 O(N)有了索引通過(guò)樹(shù)形結(jié)構(gòu)查找能迅速定位到目標(biāo)數(shù)據(jù)數(shù)據(jù)量越大差距越明顯。你可以做個(gè)簡(jiǎn)單試驗(yàn)一張沒(méi)有主鍵約束的千萬(wàn)級(jí)別大表做一條等值查詢可能要掃幾百毫秒甚至幾秒建上索引之后同樣的查詢常常是個(gè)位數(shù)毫秒。索引不是優(yōu)化技巧而是關(guān)系型數(shù)據(jù)庫(kù)的基本功。面試題里必問(wèn)的基本都圍繞“哪些場(chǎng)景索引會(huì)失效”展開(kāi)。最經(jīng)典的失效場(chǎng)景是前置通配符。索引是按照值的前綴順序排列的你寫LIKE %abc沒(méi)辦法從前綴開(kāi)始匹配索引自然就沒(méi)用了。還有一個(gè)高頻錯(cuò)誤是在索引列上用函數(shù)。比如對(duì)日期字段使用DATE(created_at) 2025-01-01這時(shí)候 MySQL 無(wú)法直接使用索引。正確寫法是改成范圍查詢created_at 2025-01-01 AND created_at 2025-01-02。隱式的類型轉(zhuǎn)換也會(huì)造成失效。比如索引字段是字符串varchar你卻拿數(shù)字去匹配MySQL 會(huì)自動(dòng)做類型轉(zhuǎn)換索引就用不上了。5.2 聯(lián)合索引與最左前綴原則建索引的兩個(gè)簡(jiǎn)單命令是CREATE INDEX idx_username ON users(username); CREATE INDEX idx_age_username ON users(age, username); DROP INDEX idx_username ON users;第二條命令創(chuàng)建了一個(gè)聯(lián)合索引。這里的核心規(guī)則是“最左前綴原則”聯(lián)合索引 (age, username) 會(huì)先按 age 排序age 相同的再按 username 排序。所以WHERE age 20能用上索引WHERE age 20 AND username zhangsan也能用上但如果只有WHERE username zhangsan索引就用不上了因?yàn)?username 不是索引的最左列。很多人的誤區(qū)是不管查詢是什么先給經(jīng)常查詢的字段都加上索引。這不對(duì)。索引能加速查詢但會(huì)拖慢寫入因?yàn)槊看?INSERT、UPDATE、DELETE 都要同步維護(hù)索引結(jié)構(gòu)。一張表如果索引太多寫性能會(huì)被嚴(yán)重拖累。我見(jiàn)過(guò)的線上事故里有張表被加了十幾個(gè)索引結(jié)果寫入延遲飆升。慎重建索引寧缺毋濫。查看一條查詢是否走了索引最常用的手段是用 EXPLAINEXPLAIN SELECT username, age FROM users WHERE age 20;輸出的結(jié)果里type字段是關(guān)鍵。如果看到ALL說(shuō)明是全表掃描基本意味著沒(méi)走索引如果是index或者range說(shuō)明至少用上了索引的一部分如果是const說(shuō)明是主鍵等值查詢性能最好。key字段會(huì)告訴你實(shí)際用的索引名rows是預(yù)估掃描的行數(shù)。通過(guò)這幾個(gè)字段你基本能定位出一條慢查詢問(wèn)題出在哪。5.3 一次慢查詢的排查復(fù)盤有一次某項(xiàng)目上線后反饋一個(gè)報(bào)表頁(yè)面越來(lái)越慢一開(kāi)始是 1 秒后來(lái)變成 8 秒。我用 EXPLAIN 分析了一下查詢發(fā)現(xiàn)是對(duì)一張訂單明細(xì)表做 GROUP BY 統(tǒng)計(jì)但是type列是ALL即全表掃描。當(dāng)時(shí)這張表沒(méi)有建任何統(tǒng)計(jì)字段的索引。解決辦法也很簡(jiǎn)單在訂單明細(xì)表的 user_id 和 status 字段上建一個(gè)聯(lián)合索引。索引一建立同樣的查詢從 8 秒降到了 0.3 秒。這個(gè)案例我想說(shuō)明一個(gè)排查思路遇到“慢”不要急著改 SQL 結(jié)構(gòu)先用 EXPLAIN 看一下是否走索引、走了哪個(gè)索引、掃描了多少行。很多時(shí)候一條索引的差異比任何 SQL 技巧都管用。當(dāng)然SQL 本身也要規(guī)范比如避免在 WHERE 子句中對(duì)字段做表達(dá)式的運(yùn)算那種寫法屬于“從一開(kāi)始就把索引掐死了”。6. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄6.1 一個(gè)問(wèn)題速查表積累過(guò)程中的坑太多了我把新手最容易遇到的幾種問(wèn)題整理成一張速查表絕對(duì)能幫你省下大量翻論壇的時(shí)間。問(wèn)題現(xiàn)象常見(jiàn)原因排查思路快速解決登錄時(shí)報(bào) Access denied密碼不對(duì)或認(rèn)證插件不匹配確認(rèn)賬號(hào)、密碼查看 user 表 plugin 字段用 root 登錄后重置密碼或改用 mysql_native_password中文亂碼客戶端、連接、表、庫(kù)字符集不一致依次檢查 server、database、table、connection 字符集建庫(kù)建表統(tǒng)一 utf8mb4連接參數(shù)加 characterEncodingutf8端口 3306 被占用本機(jī)其他進(jìn)程占用netstat -ano查看占用進(jìn)程修改 MySQL 端口或停掉占用進(jìn)程Too many connections連接數(shù)超過(guò) max_connectionsSHOW VARIABLES LIKE max_connections;查當(dāng)前設(shè)置臨時(shí)調(diào)大連接數(shù)排查應(yīng)用是否存在連接池泄露查詢特別慢沒(méi)走索引 / 大表全掃執(zhí)行 EXPLAIN 看 type、rows優(yōu)化索引、優(yōu)化 SQL 寫法ERROR 1175在 UPDATE/DELETE 時(shí)安全模式要求帶 WHERE確認(rèn)是否在安全更新模式下寫清楚 WHERE 條件不要盲目關(guān)掉安全模式表數(shù)據(jù)占空間很大歷史數(shù)據(jù)堆積、大字段太多查看表大小information_schema清理歸檔舊數(shù)據(jù)評(píng)估字段是否需要瘦身這張表想表達(dá)的核心觀念是排查問(wèn)題不是靠猜而是靠一步步確認(rèn)。我遇到問(wèn)題時(shí)的順序永遠(yuǎn)是先看錯(cuò)誤日志 → 再查配置文件 → 然后用命令行手工驗(yàn)證 → 最后才去查資料。新手容易反過(guò)來(lái)一報(bào)錯(cuò)就復(fù)制粘貼到搜索引擎結(jié)果越查越暈。6.2 忘記密碼怎么辦忘記 root 密碼應(yīng)該是所有新手都經(jīng)歷過(guò)的坎。MySQL 8.0 的處理思路和舊版略有不同但核心原理一樣跳過(guò)授權(quán)表的驗(yàn)證重啟服務(wù)再修改密碼。在 Linux 下先編輯配置文件在[mysqld]段加上一句skip-grant-tables然后重啟 MySQL。此時(shí)你執(zhí)行mysql -u root不需要密碼就能直接進(jìn)來(lái)然后執(zhí)行FLUSH PRIVILEGES;再執(zhí)行ALTER USER rootlocalhost IDENTIFIED BY 新密碼;。改完密碼之后一定要記得刪掉配置文件里skip-grant-tables這一行再重啟服務(wù)。如果你忘了刪就直接重啟MySQL 會(huì)保持無(wú)密碼登錄狀態(tài)這是一個(gè)非常大的安全隱患。我在實(shí)操中遇到過(guò)有人把這事忘了結(jié)果整臺(tái)服務(wù)器被掃了教訓(xùn)極其深刻。Windows 下的思路一樣只是改配置文件的位置不同。我這里特別提醒skip-grant-tables只是應(yīng)急方案不是日常配置。平時(shí)千萬(wàn)別為了省事長(zhǎng)期開(kāi)著它。6.3 數(shù)據(jù)備份與恢復(fù)基本功里的保命技能最后一個(gè)基礎(chǔ)能力是備份。MySQL 最常用的邏輯備份工具是mysqldump。最基礎(chǔ)的全量備份命令是mysqldump -u root -p shop shop_backup_2026_01_01.sql恢復(fù)也很簡(jiǎn)單mysql -u root -p shop shop_backup_2026_01_01.sql這條命令是把你導(dǎo)出的 SQL 腳本重新執(zhí)行一遍。實(shí)際操作中我一般會(huì)給 mysqldump 加上--single-transaction參數(shù)。這個(gè)參數(shù)的作用是在導(dǎo)出過(guò)程中不鎖表保證線上業(yè)務(wù)能繼續(xù)寫入而導(dǎo)出的數(shù)據(jù)是一致性快照。不加這個(gè)參數(shù)時(shí)InnoDB 表默認(rèn)可能鎖表或者導(dǎo)出的數(shù)據(jù)不一致這在線上環(huán)境是不可接受的。數(shù)據(jù)恢復(fù)還有個(gè)細(xì)節(jié)導(dǎo)入之前要確認(rèn)目標(biāo)庫(kù)存在。如果shop庫(kù)不存在導(dǎo)入會(huì)報(bào)錯(cuò)。所以恢復(fù)流程應(yīng)該是先建庫(kù)CREATE DATABASE shop;然后USE shop;再執(zhí)行導(dǎo)入。備份這件事你練得越勤真出事的時(shí)候越不慌。我有一次幫同事恢復(fù)誤刪的數(shù)據(jù)就是靠一個(gè)每天凌晨自動(dòng)跑的 mysqldump 定時(shí)腳本整個(gè)過(guò)程 20 分鐘搞定同事眼睛都直了。定時(shí)備份我建議大家從第一天就養(yǎng)成習(xí)慣。在 Linux 下可以直接用 crontab 配置一個(gè)每日任務(wù)0 2 * * * mysqldump -u root -p123456 shop /backup/shop_$(date \%F).sql但要注意密碼寫在命令行里是不安全的生產(chǎn)環(huán)境建議用--defaults-extra-file把密碼寫在配置文件里并設(shè)置文件權(quán)限為 600。這些都是細(xì)節(jié)但安全就是靠細(xì)節(jié)堆起來(lái)的。優(yōu)化收藏這一步也很重要。有時(shí)候你會(huì)覺(jué)得自己的 SQL 寫得沒(méi)問(wèn)題還是一直卡那大概率是表結(jié)構(gòu)或者索引設(shè)計(jì)的問(wèn)題。這種時(shí)候別再糾結(jié) SQL回去看建表語(yǔ)句、看字段類型、看查詢頻率甚至重新梳理業(yè)務(wù)邏輯比硬寫一條“神仙 SQL”強(qiáng)得多。MySQL 基礎(chǔ)不深?yuàn)W但每一層都需要你親手敲一遍 SQL才能真正變成自己的東西。遇到一個(gè)報(bào)錯(cuò)就記一個(gè)坑時(shí)間長(zhǎng)了你就是別人眼里的“MySQL 老司機(jī)”。 MySQL數(shù)據(jù)庫(kù)基礎(chǔ)實(shí)戰(zhàn)：從建表規(guī)范到索引優(yōu)化與故障排查
帶過(guò)不少新人發(fā)現(xiàn)一個(gè)很有意思的現(xiàn)象很多人簡(jiǎn)歷上寫著“熟悉 MySQL”可真讓他建一張表、寫一條稍微復(fù)雜一點(diǎn)的查詢往往就開(kāi)始卡殼。這不能全怪誰(shuí)MySQL 基礎(chǔ)這門課看著簡(jiǎn)單實(shí)際新手期踩的坑一點(diǎn)也不少。作為目前應(yīng)用最廣泛的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù)MySQL 不管是做 Web 應(yīng)用、移動(dòng)端后端還是數(shù)據(jù)倉(cāng)庫(kù)的中間層幾乎處處都能看到它的影子。這篇文章不打算堆砌高深理論而是按一條完整的實(shí)操主線帶你把 MySQL 數(shù)據(jù)庫(kù)基礎(chǔ)扎扎實(shí)實(shí)走一遍從環(huán)境搭建開(kāi)始到建庫(kù)建表、增刪改查、索引優(yōu)化再到日常工作里最高頻的故障排查。適合剛?cè)胄邢胂到y(tǒng)補(bǔ)數(shù)據(jù)庫(kù)能力的開(kāi)發(fā)者也適合工作中要寫 SQL 但總碰壁的數(shù)據(jù)分析師準(zhǔn)備面試想鞏固基礎(chǔ)的同學(xué)同樣可以照著過(guò)一遍。1. 內(nèi)容整體設(shè)計(jì)與思路拆解1.1 MySQL 在技術(shù)棧中的位置與價(jià)值先說(shuō)說(shuō)為什么值得花時(shí)間學(xué) MySQL。很多剛接觸編程的朋友會(huì)有個(gè)誤區(qū)覺(jué)得現(xiàn)在 NoSQL 挺火好像什么場(chǎng)景都能用 MongoDB、Redis 頂上。但實(shí)際在業(yè)務(wù)系統(tǒng)里關(guān)系型數(shù)據(jù)庫(kù)依然是絕對(duì)主力。MySQL 的優(yōu)勢(shì)很明確免費(fèi)開(kāi)源、跨平臺(tái)、穩(wěn)定可靠、生態(tài)極其成熟。無(wú)論是個(gè)人獨(dú)立開(kāi)發(fā)的博客系統(tǒng)還是企業(yè)級(jí)的訂單、用戶、權(quán)限模塊MySQL 都是數(shù)據(jù)庫(kù)選型清單上的第一梯隊(duì)。從技術(shù)棧的角度看MySQL 屬于存儲(chǔ)層承擔(dān)著“持久化”這個(gè)核心職責(zé)。程序可以重啟緩存可以失效但用戶數(shù)據(jù)必須躺在數(shù)據(jù)庫(kù)里不能丟。這就決定了我們不僅要會(huì)寫 SQL還得理解數(shù)據(jù)是怎么組織的、查詢是怎么跑的否則哪天線上出個(gè)慢查詢或者數(shù)據(jù)莫名其妙的丟了你連排查方向都沒(méi)有。我之前帶過(guò)一個(gè)新人功能開(kāi)發(fā)沒(méi)問(wèn)題一到數(shù)據(jù)相關(guān)的活就發(fā)怵。后來(lái)我發(fā)現(xiàn)他不是笨而是缺一套系統(tǒng)的基礎(chǔ)框架。MySQL 的基礎(chǔ)知識(shí)點(diǎn)其實(shí)是成體系的東學(xué)一點(diǎn)西學(xué)一點(diǎn)永遠(yuǎn)都是散的。只要把“實(shí)例 → 庫(kù) → 表 → 數(shù)據(jù) → 索引 → 事務(wù)”這條鏈路理清楚后面再接觸任何關(guān)系型數(shù)據(jù)庫(kù)都會(huì)很順暢。1.2 基礎(chǔ)學(xué)習(xí)路徑的設(shè)計(jì)邏輯MySQL 基礎(chǔ)的學(xué)習(xí)我比較推薦一條主線先搞懂 MySQL 是怎么跑起來(lái)的再學(xué)庫(kù)和表的創(chuàng)建然后是 DML 語(yǔ)句增刪改查再往后是索引和事務(wù)。為什么是這條線因?yàn)樗前凑諗?shù)據(jù)在系統(tǒng)中的流轉(zhuǎn)順序來(lái)設(shè)計(jì)的。舉個(gè)例子你寫一個(gè)用戶注冊(cè)功能。用戶點(diǎn)提交按鈕后端收到請(qǐng)求首先要連接上 MySQL 實(shí)例然后往庫(kù)里某張表插入一條記錄。如果表結(jié)構(gòu)設(shè)計(jì)得不對(duì)或者索引沒(méi)建好后續(xù)查詢就會(huì)越用越慢。這個(gè)鏈條非常直觀實(shí)例是地基庫(kù)和表是房間數(shù)據(jù)是房間里擺的東西索引相當(dāng)于給每件東西編了個(gè)檢索目錄事務(wù)則是確保你同時(shí)搬多件東西時(shí)不至于磕碰損壞。很多自學(xué)者容易犯的毛病是上來(lái)就啃 B 樹(shù)原理、隔離級(jí)別這些偏理論的東西結(jié)果連一張表都建不利索。說(shuō)實(shí)話基礎(chǔ)階段你要掌握的 SQL 不超過(guò)二十種核心就四類增、刪、改、查。把這些寫熟練理解 WHERE 的執(zhí)行邏輯再搞清楚 JOIN 是怎么把多張表關(guān)聯(lián)起來(lái)的日常開(kāi)發(fā)里九成以上和數(shù)據(jù)庫(kù)相關(guān)的活你都能應(yīng)付。剩下的問(wèn)題基本都是用多了之后自然遇到的。這篇文章就按這個(gè)思路展開(kāi)保證你照著做完能實(shí)打?qū)嵣鲜帧?. 環(huán)境搭建與準(zhǔn)備2.1 安裝方式選擇與避坑要學(xué) MySQL第一步當(dāng)然是裝環(huán)境。很多人在這一步就被勸退了其實(shí)大可不必。目前主流版本是 MySQL 8.0 和 5.7如果是從零開(kāi)始學(xué)直接選 8.0 就好一方面它性能更好另一方面 8.0 的默認(rèn)字符集是 utf8mb4對(duì)中文和各種生僻字的支持比 5.7 省心很多。具體安裝方式分兩類。在 Windows 上建議直接去官網(wǎng)下載 MySQL Installer它會(huì)幫你把服務(wù)端、命令行客戶端、Workbench 圖形工具一起裝好選默認(rèn)配置即可。在 Linux 服務(wù)器上我一般用系統(tǒng)自帶的包管理工具。比如在 Ubuntu 上執(zhí)行sudo apt install mysql-server就能搞定。CentOS 上則是sudo yum install mysql-server。裝完后先別急著用有幾件事必須確認(rèn)。第一件是服務(wù)是否開(kāi)機(jī)自啟?？梢杂胹udo systemctl enable mysql設(shè)置開(kāi)機(jī)自啟再通過(guò)sudo systemctl status mysql或者 Windows 下的services.msc去檢查服務(wù)運(yùn)行狀態(tài)。第二件是初始密碼。MySQL 8.0 在 Linux 上安裝后root 用戶默認(rèn)用 auth_socket 插件認(rèn)證意思是你只能用系統(tǒng) root 身份登錄這一步很迷惑很多新手會(huì)卡在密碼不對(duì)。解決辦法很簡(jiǎn)單登錄后改一下認(rèn)證方式就行。第三件是測(cè)試連接命令行敲mysql -u root -p能進(jìn)到mysql提示符就說(shuō)明成功。這里要重點(diǎn)提醒一句生產(chǎn)環(huán)境永遠(yuǎn)不要用 root 直接操作業(yè)務(wù)庫(kù)。正確做法是創(chuàng)建一個(gè)業(yè)務(wù)專用賬號(hào)只授予它操作特定庫(kù)的權(quán)限。在練習(xí)階段為了方便可以用 root但心里要繃著這根弦。2.2 配置文件里的關(guān)鍵參數(shù)MySQL 裝好后主要的配置文件在 Linux 下是/etc/mysql/my.cnf部分發(fā)行版是/etc/my.cnfWindows 下是安裝目錄里的my.ini。新手剛開(kāi)始不用動(dòng)太多配置但有幾個(gè)參數(shù)值得了解因?yàn)槟阍缤頃?huì)碰上。第一個(gè)是port默認(rèn)是 3306。如果和本地其他服務(wù)端口沖突你就需要改端口同時(shí)記得同步改防火墻規(guī)則。第二個(gè)是character-set-server建議設(shè)置成utf8mb4。我之前接過(guò)一個(gè)老項(xiàng)目數(shù)據(jù)庫(kù)默認(rèn)字符集是 latin1結(jié)果存中文進(jìn)去直接變成亂碼折騰了一下午。教訓(xùn)就是字符集一開(kāi)始就要設(shè)置對(duì)別等寫入數(shù)據(jù)了再改。第三個(gè)是max_connections控制最大連接數(shù)默認(rèn)是 151如果你的應(yīng)用并發(fā)高這個(gè)值可能要調(diào)大。但要注意連不上數(shù)據(jù)庫(kù)不一定就是連接數(shù)不夠后面我會(huì)細(xì)說(shuō)。還有一個(gè)容易被忽略的是sql_mode。MySQL 8.0 默認(rèn)的 sql_mode 包含ONLY_FULL_GROUP_BY這意味著你寫 GROUP BY 查詢時(shí)SELECT 的字段必須在 GROUP BY 里出現(xiàn)過(guò)否則直接報(bào)錯(cuò)。很多從 5.7 時(shí)代過(guò)來(lái)的人會(huì)被這個(gè)坑到。如果你不想改全局配置可以在本次會(huì)話里執(zhí)行SET SESSION sql_mode ALLOW_INVALID_DATES;臨時(shí)調(diào)整但這只是應(yīng)急徹底解決方案還是把 SQL 寫規(guī)范。2.3 可視化工具還是命令行很多人會(huì)問(wèn)我推薦用哪種方式操作 MySQL。我的看法是命令行客戶端必須得會(huì)因?yàn)榫€上排查問(wèn)題的時(shí)候可能只有命令行可用但日常開(kāi)發(fā)中用可視化工具效率更高。如果你用的是 Windows推薦裝 MySQL Workbench雖然有點(diǎn)重但功能全支持 ER 圖、查詢分析、數(shù)據(jù)導(dǎo)入導(dǎo)出。我自己日常用得比較多的是一款名為 DBeaver 的開(kāi)源工具跨平臺(tái)、免費(fèi)、支持各種數(shù)據(jù)庫(kù)界面也比較友好。不過(guò)有一點(diǎn)得說(shuō)清楚可視化工具再方便也替代不了你對(duì) SQL 本身的理解。你遲早會(huì)需要手動(dòng)寫復(fù)雜的 JOIN、子查詢或者存儲(chǔ)過(guò)程這些在圖形界面里點(diǎn)點(diǎn)點(diǎn)是點(diǎn)不出來(lái)的。所以以下所有實(shí)操我都默認(rèn)你打開(kāi)命令行或者任意一個(gè)工具的 SQL 編輯器窗口跟著敲命令這樣才能形成肌肉記憶。3. 建庫(kù)建表基礎(chǔ)中的基礎(chǔ)3.1 數(shù)據(jù)庫(kù)的創(chuàng)建、修改與刪除登錄 MySQL 之后第一個(gè)操作就是看有多少庫(kù)命令是SHOW DATABASES;。默認(rèn)會(huì)有information_schema、mysql、performance_schema、sys這幾個(gè)系統(tǒng)庫(kù)千萬(wàn)別去動(dòng)它們。自己建庫(kù)的命令很簡(jiǎn)單CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;這里我建議你把IF NOT EXISTS養(yǎng)成習(xí)慣以后跑腳本不會(huì)因?yàn)橹貜?fù)執(zhí)行而報(bào)錯(cuò)。DEFAULT CHARACTER SET直接指定utf8mb4COLLATE選擇utf8mb4_unicode_ci意思是字符串排序和比較的規(guī)則用 Unicode 通用規(guī)則。字符集和排序規(guī)則決定了很多細(xì)節(jié)比如查詢時(shí)是否區(qū)分大小寫、中文排序是否按拼音新手階段直接按我給的這套配置來(lái)穩(wěn)妥。建好之后用USE shop;切換到這個(gè)庫(kù)。想確認(rèn)當(dāng)前在哪個(gè)庫(kù)用SELECT DATABASE();這個(gè)命令在排查問(wèn)題時(shí)會(huì)很實(shí)用。刪除數(shù)據(jù)庫(kù)是DROP DATABASE shop;操作前務(wù)必確認(rèn)你刪的是哪個(gè)庫(kù)。我在實(shí)際工作中看到過(guò)有人一條DROP下去整個(gè)業(yè)務(wù)庫(kù)沒(méi)了當(dāng)時(shí)入職不到一個(gè)月的實(shí)習(xí)生那個(gè)場(chǎng)面至今難忘。所以建議你刪庫(kù)之前先查詢一下庫(kù)里的表數(shù)量做好備份再執(zhí)行刪除。3.2 表的字段設(shè)計(jì)類型和約束怎么選建表是整個(gè) MySQL 基礎(chǔ)里最見(jiàn)功底的部分。表字段設(shè)計(jì)得好不好直接影響后續(xù)的查詢性能和數(shù)據(jù)完整性。先看一個(gè)例子CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主鍵, username VARCHAR(50) NOT NULL COMMENT 用戶名, email VARCHAR(100) DEFAULT NULL COMMENT 郵箱, age TINYINT UNSIGNED DEFAULT 0 COMMENT 年齡, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時(shí)間, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新時(shí)間, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用戶表;這條建表語(yǔ)句包含了幾個(gè)核心知識(shí)點(diǎn)。主鍵我用的BIGINT UNSIGNED AUTO_INCREMENT這是最常見(jiàn)的自增主鍵方案。有人喜歡用 UUID 做主鍵但在 InnoDB 引擎里主鍵是聚簇索引UUID 是隨機(jī)字符串會(huì)導(dǎo)致頁(yè)分裂和碎片增多查詢效率不如自增整數(shù)。所以沒(méi)有特殊理由業(yè)務(wù)表首選自增主鍵。username字段加了NOT NULL和唯一索引這是因?yàn)橛脩裘仨氂星椅ㄒ?。age用TINYINT UNSIGNED因?yàn)槿说哪挲g絕不會(huì)是負(fù)數(shù)用無(wú)符號(hào)小整數(shù)就夠存了。這里想說(shuō)明一個(gè)選擇字段類型的核心原則在滿足需求的前提下用最小的存儲(chǔ)類型。你不需要算到每一比特但要心里有數(shù)。DATETIME存日期時(shí)間8 字節(jié)TIMESTAMP也是 8 字節(jié)5.7 之后但有 2038 年問(wèn)題。業(yè)務(wù)系統(tǒng)里我一般用DATETIME。created_at和updated_at的默認(rèn)值設(shè)置能讓插入和更新時(shí)自動(dòng)維護(hù)時(shí)間省去不少代碼。還有一個(gè)非常容易踩的坑是TEXT/BLOB字段。這類大字段不能有默認(rèn)值而且查詢時(shí)會(huì)占用更多 IO。如果一張表有好幾個(gè)TEXT字段很容易造成表體積巨大影響全表掃描性能。能用VARCHAR就不用TEXT這個(gè)習(xí)慣越早養(yǎng)成越好。3.3 表結(jié)構(gòu)的修改與刪除表建好之后不是一成不變的。業(yè)務(wù)迭代中經(jīng)常要加字段命令是ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT 手機(jī)號(hào) AFTER email;AFTER email的意思是放在 email 字段后面這純粹是為了看著舒服不影響功能。修改字段類型或?qū)傩訟LTER TABLE users MODIFY COLUMN phone VARCHAR(30) NOT NULL COMMENT 手機(jī)號(hào);RENAME 改名ALTER TABLE users RENAME COLUMN phone TO mobile;刪除字段ALTER TABLE users DROP COLUMN mobile;DROP TABLE users;是刪表TRUNCATE TABLE users;是清空表但保留表結(jié)構(gòu)。這兩個(gè)命令從“能不能找回”這個(gè)角度看幾乎是不可逆操作。我的實(shí)操習(xí)慣是在測(cè)試庫(kù)隨便玩但在生產(chǎn)環(huán)境執(zhí)行任何ALTER、DROP、TRUNCATE之前先把庫(kù)表結(jié)構(gòu)和數(shù)據(jù)導(dǎo)出一份備份。走一遍備份幾分鐘的事但能救命的次數(shù)絕對(duì)超乎你想象。4. 增刪改查從會(huì)用 SQL 到寫出像樣的 SQL4.1 INSERT插入數(shù)據(jù)的花式操作增刪改查CRUD是數(shù)據(jù)庫(kù)最核心的操作其中 INSERT 是打底基本功。最簡(jiǎn)單的單條插入INSERT INTO users (username, email, age) VALUES (zhangsan, zsexample.com, 25);注意主鍵 id 你沒(méi)有寫它會(huì)自動(dòng)遞增。第二條語(yǔ)句之前我需要強(qiáng)調(diào)一個(gè)細(xì)節(jié)INSERT時(shí)最好把字段名顯式列出來(lái)而不是直接寫INSERT INTO users VALUES (...)。一旦表結(jié)構(gòu)發(fā)生了變化后面的字段對(duì)不上就全亂套了。顯式列字段名代碼的可讀性和健壯性都會(huì)好很多。批量插入是提高效率的關(guān)鍵。比如你要一次性造 100 條測(cè)試數(shù)據(jù)一條一條插會(huì)很慢INSERT INTO users (username, email, age) VALUES (zhangsan, zsexample.com, 25), (lisi, lsexample.com, 30), (wangwu, wwexample.com, 22);批量插入的執(zhí)行速度比單條逐次插入快得多因?yàn)樗唤馕鲆淮?SQL。但這個(gè) INSERT 的長(zhǎng)度也不是越大越好我一般在單條語(yǔ)句里控制在一千行以內(nèi)否則可能超過(guò) MySQL 的 max_allowed_packet 限制報(bào)錯(cuò)或者超時(shí)。還有一個(gè)高級(jí)技巧叫INSERT ... ON DUPLICATE KEY UPDATE可以在插入時(shí)遇到唯一鍵沖突就自動(dòng)執(zhí)行更新INSERT INTO users (username, email, age) VALUES (zhangsan, zs_newexample.com, 26) ON DUPLICATE KEY UPDATE email VALUES(email), age VALUES(age);這個(gè)命令在做數(shù)據(jù)同步、去重更新時(shí)很有用比先查再插快得多。但我建議新手不要一上來(lái)就寫這種復(fù)雜語(yǔ)句先把基礎(chǔ) INSERT 寫明白再逐步進(jìn)階。4.2 SELECT不僅僅是“讀出來(lái)”SELECT 是 SQL 里出現(xiàn)頻率最高、也最考驗(yàn)功底的語(yǔ)句。很多人停留在SELECT * FROM users;這種級(jí)別這不太夠。查詢優(yōu)化從字段選擇就開(kāi)始了。第一條原則盡量別用SELECT *。原因有兩個(gè)一是如果表字段很多查出來(lái)的數(shù)據(jù)量會(huì)很大白白占用網(wǎng)絡(luò)和內(nèi)存二是當(dāng)你用代碼的 ORM 映射數(shù)據(jù)時(shí)多余字段會(huì)影響序列化和傳輸性能。業(yè)務(wù)代碼里只取你真正需要的字段。比如SELECT username, age FROM users WHERE age 20 ORDER BY age DESC LIMIT 10;這條語(yǔ)句涵蓋了幾個(gè)最重要的子句SELECT選字段WHERE過(guò)濾行ORDER BY排序LIMIT限制條數(shù)。執(zhí)行順序從邏輯上看是FROM→WHERE→SELECT→ORDER BY→LIMIT。理解這個(gè)順序你才能解釋為什么WHERE里不能用別名而ORDER BY里可以。LIKE模糊查詢是新手經(jīng)常玩出問(wèn)題的點(diǎn)。比如查用戶名包含“張”的人寫成WHERE username LIKE %張%。如果用例測(cè)試沒(méi)問(wèn)題等上線數(shù)據(jù)量大之后你才會(huì)發(fā)現(xiàn)前面的通配符會(huì)讓索引失效導(dǎo)致全表掃描。所以能用精確查詢就不用LIKE非要用模糊匹配也盡量寫成張%這種前后綴可預(yù)測(cè)的模式比如只查“張開(kāi)頭”的用戶這樣索引可能還有機(jī)會(huì)用上。分組統(tǒng)計(jì)也是日常需求。比如統(tǒng)計(jì)每個(gè)年齡段的用戶數(shù)SELECT age, COUNT(*) AS cnt FROM users GROUP BY age HAVING cnt 1;我特別想強(qiáng)調(diào)WHERE和HAVING的區(qū)別WHERE在分組前過(guò)濾行HAVING在分組后過(guò)濾組。很多初學(xué)者一開(kāi)始分不清但只要想想執(zhí)行順序就不容易混了。GROUP BY之后SELECT 后面能出現(xiàn)的字段基本就只能是分組字段或聚合函數(shù)了剛才我提到的 sql_mode 里的ONLY_FULL_GROUP_BY管的就是這件事。JOIN 關(guān)聯(lián)查詢是新手最難啃的骨頭。用一個(gè)簡(jiǎn)單的例子訂單表 orders 里有 user_id要查出每個(gè)訂單的用戶名用 INNER JOINSELECT o.order_no, o.amount, u.username FROM orders o INNER JOIN users u ON o.user_id u.id;JOIN的本質(zhì)是笛卡爾積的過(guò)濾ON 條件就是過(guò)濾條件。左連接LEFT JOIN則以左表為主右表沒(méi)有匹配的行就補(bǔ) NULL。初學(xué)的時(shí)候我建議你腦子里把兩張表擺開(kāi)自己“畫(huà)”一遍連接的過(guò)程比死記硬背圖管用。大多數(shù)慢查詢問(wèn)題根源多半是關(guān)聯(lián)字段沒(méi)有索引。所以寫 JOIN 之前先檢查兩表的關(guān)聯(lián)字段是否都建了索引這一點(diǎn)比任何優(yōu)化手段都實(shí)在。4.3 UPDATE 和 DELETE手一抖就是事故UPDATE 和 DELETE 在業(yè)務(wù)里最危險(xiǎn)因?yàn)橐坏┎僮鞑划?dāng)影響的不是一兩行而是整張表。我曾經(jīng)在一個(gè)測(cè)試環(huán)境里一條沒(méi)帶 WHERE 條件的 UPDATE直接把全表的 age 更新成了同一個(gè)值。雖然測(cè)試環(huán)境無(wú)所謂但它讓我養(yǎng)成了一個(gè)條件反射寫 UPDATE 和 DELETE 之前先寫明 WHERE并且先 SELECT 一下確認(rèn)影響范圍。UPDATE users SET age 26 WHERE username zhangsan;如果你想更新多行可以用WHERE id IN (1,2,3)或者WHERE age BETWEEN 20 AND 30。刪數(shù)據(jù)同理DELETE FROM users WHERE id 10086;DELETE 和 UPDATE 一樣事務(wù)里可以回滾但這也依賴你是不是開(kāi)啟了事務(wù)。默認(rèn)情況下MySQL 的 InnoDB 引擎在執(zhí)行單條語(yǔ)句時(shí)會(huì)自動(dòng)提交這意味著一條DELETE下去數(shù)據(jù)立刻生效沒(méi)有后悔的余地。想要更安全可以手動(dòng)控制事務(wù)START TRANSACTION; DELETE FROM users WHERE id 10086; -- 檢查一下結(jié)果如果不對(duì) ROLLBACK; -- 如果沒(méi)問(wèn)題 COMMIT;這個(gè)習(xí)慣在操作生產(chǎn)數(shù)據(jù)時(shí)極其重要。我做線上數(shù)據(jù)處理任務(wù)時(shí)永遠(yuǎn)都是先 SELECT 確認(rèn)目標(biāo)行數(shù)再開(kāi)事務(wù) DELETE查一遍結(jié)果最后才 COMMIT。這套流程看著繁瑣但出過(guò)事的人才知道它的價(jià)值。5. 索引與查詢優(yōu)化基礎(chǔ)5.1 索引到底解決了什么問(wèn)題很多人把索引理解成書(shū)的目錄這個(gè)類比雖然粗淺但方向是對(duì)的只是要更精確一些。MySQL 的 InnoDB 引擎使用的 B 樹(shù)索引本質(zhì)上是一種有序的數(shù)據(jù)結(jié)構(gòu)。沒(méi)有索引的時(shí)候查詢一條記錄只能全表掃描一條一條地匹配時(shí)間復(fù)雜度是 O(N)有了索引通過(guò)樹(shù)形結(jié)構(gòu)查找能迅速定位到目標(biāo)數(shù)據(jù)數(shù)據(jù)量越大差距越明顯。你可以做個(gè)簡(jiǎn)單試驗(yàn)一張沒(méi)有主鍵約束的千萬(wàn)級(jí)別大表做一條等值查詢可能要掃幾百毫秒甚至幾秒建上索引之后同樣的查詢常常是個(gè)位數(shù)毫秒。索引不是優(yōu)化技巧而是關(guān)系型數(shù)據(jù)庫(kù)的基本功。面試題里必問(wèn)的基本都圍繞“哪些場(chǎng)景索引會(huì)失效”展開(kāi)。最經(jīng)典的失效場(chǎng)景是前置通配符。索引是按照值的前綴順序排列的你寫LIKE %abc沒(méi)辦法從前綴開(kāi)始匹配索引自然就沒(méi)用了。還有一個(gè)高頻錯(cuò)誤是在索引列上用函數(shù)。比如對(duì)日期字段使用DATE(created_at) 2025-01-01這時(shí)候 MySQL 無(wú)法直接使用索引。正確寫法是改成范圍查詢created_at 2025-01-01 AND created_at 2025-01-02。隱式的類型轉(zhuǎn)換也會(huì)造成失效。比如索引字段是字符串varchar你卻拿數(shù)字去匹配MySQL 會(huì)自動(dòng)做類型轉(zhuǎn)換索引就用不上了。5.2 聯(lián)合索引與最左前綴原則建索引的兩個(gè)簡(jiǎn)單命令是CREATE INDEX idx_username ON users(username); CREATE INDEX idx_age_username ON users(age, username); DROP INDEX idx_username ON users;第二條命令創(chuàng)建了一個(gè)聯(lián)合索引。這里的核心規(guī)則是“最左前綴原則”聯(lián)合索引 (age, username) 會(huì)先按 age 排序age 相同的再按 username 排序。所以WHERE age 20能用上索引WHERE age 20 AND username zhangsan也能用上但如果只有WHERE username zhangsan索引就用不上了因?yàn)?username 不是索引的最左列。很多人的誤區(qū)是不管查詢是什么先給經(jīng)常查詢的字段都加上索引。這不對(duì)。索引能加速查詢但會(huì)拖慢寫入因?yàn)槊看?INSERT、UPDATE、DELETE 都要同步維護(hù)索引結(jié)構(gòu)。一張表如果索引太多寫性能會(huì)被嚴(yán)重拖累。我見(jiàn)過(guò)的線上事故里有張表被加了十幾個(gè)索引結(jié)果寫入延遲飆升。慎重建索引寧缺毋濫。查看一條查詢是否走了索引最常用的手段是用 EXPLAINEXPLAIN SELECT username, age FROM users WHERE age 20;輸出的結(jié)果里type字段是關(guān)鍵。如果看到ALL說(shuō)明是全表掃描基本意味著沒(méi)走索引如果是index或者range說(shuō)明至少用上了索引的一部分如果是const說(shuō)明是主鍵等值查詢性能最好。key字段會(huì)告訴你實(shí)際用的索引名rows是預(yù)估掃描的行數(shù)。通過(guò)這幾個(gè)字段你基本能定位出一條慢查詢問(wèn)題出在哪。5.3 一次慢查詢的排查復(fù)盤有一次某項(xiàng)目上線后反饋一個(gè)報(bào)表頁(yè)面越來(lái)越慢一開(kāi)始是 1 秒后來(lái)變成 8 秒。我用 EXPLAIN 分析了一下查詢發(fā)現(xiàn)是對(duì)一張訂單明細(xì)表做 GROUP BY 統(tǒng)計(jì)但是type列是ALL即全表掃描。當(dāng)時(shí)這張表沒(méi)有建任何統(tǒng)計(jì)字段的索引。解決辦法也很簡(jiǎn)單在訂單明細(xì)表的 user_id 和 status 字段上建一個(gè)聯(lián)合索引。索引一建立同樣的查詢從 8 秒降到了 0.3 秒。這個(gè)案例我想說(shuō)明一個(gè)排查思路遇到“慢”不要急著改 SQL 結(jié)構(gòu)先用 EXPLAIN 看一下是否走索引、走了哪個(gè)索引、掃描了多少行。很多時(shí)候一條索引的差異比任何 SQL 技巧都管用。當(dāng)然SQL 本身也要規(guī)范比如避免在 WHERE 子句中對(duì)字段做表達(dá)式的運(yùn)算那種寫法屬于“從一開(kāi)始就把索引掐死了”。6. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄6.1 一個(gè)問(wèn)題速查表積累過(guò)程中的坑太多了我把新手最容易遇到的幾種問(wèn)題整理成一張速查表絕對(duì)能幫你省下大量翻論壇的時(shí)間。問(wèn)題現(xiàn)象常見(jiàn)原因排查思路快速解決登錄時(shí)報(bào) Access denied密碼不對(duì)或認(rèn)證插件不匹配確認(rèn)賬號(hào)、密碼查看 user 表 plugin 字段用 root 登錄后重置密碼或改用 mysql_native_password中文亂碼客戶端、連接、表、庫(kù)字符集不一致依次檢查 server、database、table、connection 字符集建庫(kù)建表統(tǒng)一 utf8mb4連接參數(shù)加 characterEncodingutf8端口 3306 被占用本機(jī)其他進(jìn)程占用netstat -ano查看占用進(jìn)程修改 MySQL 端口或停掉占用進(jìn)程Too many connections連接數(shù)超過(guò) max_connectionsSHOW VARIABLES LIKE max_connections;查當(dāng)前設(shè)置臨時(shí)調(diào)大連接數(shù)排查應(yīng)用是否存在連接池泄露查詢特別慢沒(méi)走索引 / 大表全掃執(zhí)行 EXPLAIN 看 type、rows優(yōu)化索引、優(yōu)化 SQL 寫法ERROR 1175在 UPDATE/DELETE 時(shí)安全模式要求帶 WHERE確認(rèn)是否在安全更新模式下寫清楚 WHERE 條件不要盲目關(guān)掉安全模式表數(shù)據(jù)占空間很大歷史數(shù)據(jù)堆積、大字段太多查看表大小information_schema清理歸檔舊數(shù)據(jù)評(píng)估字段是否需要瘦身這張表想表達(dá)的核心觀念是排查問(wèn)題不是靠猜而是靠一步步確認(rèn)。我遇到問(wèn)題時(shí)的順序永遠(yuǎn)是先看錯(cuò)誤日志 → 再查配置文件 → 然后用命令行手工驗(yàn)證 → 最后才去查資料。新手容易反過(guò)來(lái)一報(bào)錯(cuò)就復(fù)制粘貼到搜索引擎結(jié)果越查越暈。6.2 忘記密碼怎么辦忘記 root 密碼應(yīng)該是所有新手都經(jīng)歷過(guò)的坎。MySQL 8.0 的處理思路和舊版略有不同但核心原理一樣跳過(guò)授權(quán)表的驗(yàn)證重啟服務(wù)再修改密碼。在 Linux 下先編輯配置文件在[mysqld]段加上一句skip-grant-tables然后重啟 MySQL。此時(shí)你執(zhí)行mysql -u root不需要密碼就能直接進(jìn)來(lái)然后執(zhí)行FLUSH PRIVILEGES;再執(zhí)行ALTER USER rootlocalhost IDENTIFIED BY 新密碼;。改完密碼之后一定要記得刪掉配置文件里skip-grant-tables這一行再重啟服務(wù)。如果你忘了刪就直接重啟MySQL 會(huì)保持無(wú)密碼登錄狀態(tài)這是一個(gè)非常大的安全隱患。我在實(shí)操中遇到過(guò)有人把這事忘了結(jié)果整臺(tái)服務(wù)器被掃了教訓(xùn)極其深刻。Windows 下的思路一樣只是改配置文件的位置不同。我這里特別提醒skip-grant-tables只是應(yīng)急方案不是日常配置。平時(shí)千萬(wàn)別為了省事長(zhǎng)期開(kāi)著它。6.3 數(shù)據(jù)備份與恢復(fù)基本功里的保命技能最后一個(gè)基礎(chǔ)能力是備份。MySQL 最常用的邏輯備份工具是mysqldump。最基礎(chǔ)的全量備份命令是mysqldump -u root -p shop shop_backup_2026_01_01.sql恢復(fù)也很簡(jiǎn)單mysql -u root -p shop shop_backup_2026_01_01.sql這條命令是把你導(dǎo)出的 SQL 腳本重新執(zhí)行一遍。實(shí)際操作中我一般會(huì)給 mysqldump 加上--single-transaction參數(shù)。這個(gè)參數(shù)的作用是在導(dǎo)出過(guò)程中不鎖表保證線上業(yè)務(wù)能繼續(xù)寫入而導(dǎo)出的數(shù)據(jù)是一致性快照。不加這個(gè)參數(shù)時(shí)InnoDB 表默認(rèn)可能鎖表或者導(dǎo)出的數(shù)據(jù)不一致這在線上環(huán)境是不可接受的。數(shù)據(jù)恢復(fù)還有個(gè)細(xì)節(jié)導(dǎo)入之前要確認(rèn)目標(biāo)庫(kù)存在。如果shop庫(kù)不存在導(dǎo)入會(huì)報(bào)錯(cuò)。所以恢復(fù)流程應(yīng)該是先建庫(kù)CREATE DATABASE shop;然后USE shop;再執(zhí)行導(dǎo)入。備份這件事你練得越勤真出事的時(shí)候越不慌。我有一次幫同事恢復(fù)誤刪的數(shù)據(jù)就是靠一個(gè)每天凌晨自動(dòng)跑的 mysqldump 定時(shí)腳本整個(gè)過(guò)程 20 分鐘搞定同事眼睛都直了。定時(shí)備份我建議大家從第一天就養(yǎng)成習(xí)慣。在 Linux 下可以直接用 crontab 配置一個(gè)每日任務(wù)0 2 * * * mysqldump -u root -p123456 shop /backup/shop_$(date \%F).sql但要注意密碼寫在命令行里是不安全的生產(chǎn)環(huán)境建議用--defaults-extra-file把密碼寫在配置文件里并設(shè)置文件權(quán)限為 600。這些都是細(xì)節(jié)但安全就是靠細(xì)節(jié)堆起來(lái)的。優(yōu)化收藏這一步也很重要。有時(shí)候你會(huì)覺(jué)得自己的 SQL 寫得沒(méi)問(wèn)題還是一直卡那大概率是表結(jié)構(gòu)或者索引設(shè)計(jì)的問(wèn)題。這種時(shí)候別再糾結(jié) SQL回去看建表語(yǔ)句、看字段類型、看查詢頻率甚至重新梳理業(yè)務(wù)邏輯比硬寫一條“神仙 SQL”強(qiáng)得多。MySQL 基礎(chǔ)不深?yuàn)W但每一層都需要你親手敲一遍 SQL才能真正變成自己的東西。遇到一個(gè)報(bào)錯(cuò)就記一個(gè)坑時(shí)間長(zhǎng)了你就是別人眼里的“MySQL 老司機(jī)”。