如果你正在讀這篇文章大概率你現(xiàn)在就處在“第一次碰MySQL”的狀態(tài)。我到現(xiàn)在都記得自己第一次裝MySQL時(shí)的狼狽從官網(wǎng)下載了zip包解壓完不知道下一步該干嘛照著網(wǎng)上一篇文章配了my.ini結(jié)果net start mysql直接報(bào)“服務(wù)無法啟動(dòng)”我盯著黑窗口愣了半小時(shí)后來才發(fā)現(xiàn)是data目錄沒初始化的原因。這篇文章不是把官方文檔換個(gè)說法念給你聽而是把我從下載、初始化、啟動(dòng)、建表、寫事務(wù)、再把MySQL接進(jìn)項(xiàng)目這條線上踩過的坑按“第一次會(huì)遇到什么”的順序完整捋一遍。不管你是為了上課、寫畢業(yè)設(shè)計(jì)還是剛?cè)肼氁邮忠粋€(gè)JavaWeb項(xiàng)目照著這個(gè)順序走能少走很多冤枉路。1. 第一次裝MySQL版本、安裝包、環(huán)境準(zhǔn)備一次到位1.1 版本與發(fā)行包的選擇8.0不是盲目跟風(fēng)第一次接觸MySQL的人很容易在版本上糾結(jié)。網(wǎng)上教程經(jīng)常互相打架有人讓你裝5.7因?yàn)椤胺€(wěn)定”有人讓你裝8.0因?yàn)椤靶隆?。我的建議很直接全新項(xiàng)目一律裝MySQL 8.0除非公司老系統(tǒng)明確寫了只能用5.7。為什么8.0相比5.7有幾個(gè)不可忽視的優(yōu)勢默認(rèn)字符集是utf8mb4對中文和表情符號更友好窗口函數(shù)、CTE公共表表達(dá)式這些寫復(fù)雜查詢很好用InnoDB引擎進(jìn)一步強(qiáng)化性能和數(shù)據(jù)安全都更好。更關(guān)鍵的是很多新工具和云服務(wù)已經(jīng)默認(rèn)按8.0的協(xié)議來對接你裝個(gè)5.7反而可能遇到兼容問題。還有一個(gè)容易被忽略的點(diǎn)網(wǎng)卡之前先搞清楚自己機(jī)器是什么架構(gòu)。普通Windows電腦基本是x86_64Linux服務(wù)器可能是x86_64也可能是arm64。比如你在ARM架構(gòu)的Linux服務(wù)器上裝MySQL 5.7能找到的安裝包和依賴往往非常老裝起來全是坑。遇到這種環(huán)境優(yōu)先選8.0的官方ARM包或者干脆用Docker跑官方鏡像。1.2 Windows下zip包安裝my.ini、初始化、注冊服務(wù)Windows上安裝MySQL有兩條路一是用官方圖形安裝包msi全程點(diǎn)下一步二是下載解壓版zip包自己手動(dòng)配置。我強(qiáng)烈建議新手至少手動(dòng)走一遍zip包安裝因?yàn)橹挥惺謩?dòng)配置過你才能真正知道MySQL的服務(wù)、數(shù)據(jù)目錄、配置文件之間是什么關(guān)系以后出問題排查才有方向。具體步驟很簡單但每一步都有坑。第一步去官網(wǎng)下載mysql-8.0.x-winx64.zip別去各種軟件站下瘋狂捆綁的綠色版。解壓到一個(gè)路徑里不要帶中文、不要帶空格比如D:\mysql-8.0.44-winx64。第二步在解壓目錄下新建my.ini配置文件。最小可用配置長這樣[mysqld] basedirD:/mysql-8.0.44-winx64 datadirD:/mysql-8.0.44-winx64/data port3306 character-set-serverutf8mb4 collation-serverutf8mb4_general_ci這里最容易踩的坑有兩個(gè)一是basedir和datadir的寫法很多教程用反斜杠\結(jié)果字符串被轉(zhuǎn)義后路徑識(shí)別不了建議統(tǒng)一用正斜杠二是只寫了配置卻沒執(zhí)行初始化就直接啟動(dòng)服務(wù)結(jié)果服務(wù)起來就崩。第三步以管理員身份打開命令提示符進(jìn)入MySQL解壓目錄的bin目錄執(zhí)行mysqld --initialize-insecure --console這條命令會(huì)生成data數(shù)據(jù)目錄。--initialize-insecure表示初始化后root用戶沒有密碼方便第一次登錄如果去掉insecureMySQL會(huì)自動(dòng)生成一串隨機(jī)密碼寫在日志里新手很容易找不到它。第四步注冊Windows服務(wù)mysqld --install MySQL net start mysql到這一步如果你前面都做對了服務(wù)能正常啟動(dòng)說明MySQL已經(jīng)跑起來了。注意net start mysql里的服務(wù)名要跟你mysqld --install后面寫的名字一致不然Windows會(huì)提示“服務(wù)名無效”。1.3 Linux和Docker環(huán)境下的安裝路線Linux服務(wù)器上的第一次安裝最常用的是rpm包方式。CentOS、Rocky Linux這類系統(tǒng)上裝MySQL最大的前提是先處理掉系統(tǒng)自帶的MariaDB兩個(gè)一起裝會(huì)搶占3306端口和/usr/lib64/libmysql*庫文件。簡單說下rpm安裝鏈路# 先查一下有沒有裝了mariadb rpm -qa | grep mariadb # 有就卸載 yum remove -y mariadb-libs # 安裝官方rpm包版本號按實(shí)際下載來 rpm -ivh mysql-community-server-8.0.44-1.el9.x86_64.rpm # 啟動(dòng)服務(wù) systemctl start mysqld systemctl status mysqldLinux上用rpm方式裝MySQL有一個(gè)特殊點(diǎn)服務(wù)啟動(dòng)時(shí)MySQL會(huì)自動(dòng)做數(shù)據(jù)目錄初始化并把臨時(shí)密碼寫到/var/log/mysqld.log里。第一次登錄要用grep temporary password /var/log/mysqld.log把臨時(shí)密碼撈出來。如果服務(wù)器不能聯(lián)網(wǎng)就得走離線安裝。提前把rpm包和所有依賴包下載到內(nèi)網(wǎng)機(jī)器上按順序rpm -ivh裝依賴用rpm -Uvh --nodeps強(qiáng)行裝容易出問題實(shí)在缺哪個(gè)就單獨(dú)補(bǔ)哪個(gè)。Docker也是一種很主流的方式特別適合本地開發(fā)docker run -d --name mysql8 \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD你的密碼 \ -e TZAsia/Shanghai \ -v mysql-data:/var/lib/mysql \ mysql:8.0Docker安裝失敗最常見的原因是端口被占用宿主機(jī)上已經(jīng)有別的進(jìn)程占了3306端口。先在docker run之前用netstat -ano | findstr :3306檢查一下別等啟動(dòng)失敗再去排查。還有一個(gè)坑是數(shù)據(jù)目錄的權(quán)限容器里的mysql用戶對掛載目錄沒寫權(quán)限會(huì)直接退出chmod 777不優(yōu)雅但能快速驗(yàn)證生產(chǎn)環(huán)境建議用--user參數(shù)配合命名卷來解決。2. 服務(wù)起不來、密碼連不上第一次啟動(dòng)最常見的兩座山2.1 “net start mysql”服務(wù)無法啟動(dòng)的排查鏈路注意下面所有內(nèi)容圍繞解決net start mysql 系統(tǒng)發(fā)生錯(cuò)誤 2/5/1067這類問題。每次看到“服務(wù)無法啟動(dòng)”我的第一反應(yīng)不是你配置文件寫錯(cuò)了而是先去找錯(cuò)誤日志。MySQL的錯(cuò)誤日志路徑寫在my.ini的log-error參數(shù)里如果沒寫默認(rèn)在datadir目錄下文件名類似.err。Windows下也可以用事件查看器Linux下看journalctl -u mysqld。把日志打開之后重點(diǎn)關(guān)注三種情況。第一種日志里報(bào)[ERROR] Cant find message-file或者路徑找不到基本就是my.ini里的basedir和datadir寫錯(cuò)了路徑不存在。Windows的路徑分隔符要小心最穩(wěn)妥的是正斜杠。第二種日志里報(bào)[ERROR] InnoDB... Permission denied這是權(quán)限問題。Windows上多半是數(shù)據(jù)目錄的權(quán)限不夠比如把MySQL裝在C:\Program Files下而data目錄沒有給mysql用戶寫權(quán)限Linux下則要chown -R mysql:mysql /var/lib/mysql。第三種端口被占用。日志里會(huì)有[ERROR] Could not open TCP port 3306。這個(gè)需要在命令行執(zhí)行netstat -ano | findstr :3306看PID是多少然后去任務(wù)管理器里結(jié)束對應(yīng)進(jìn)程。有可能占用3306的是另一個(gè)mysqld實(shí)例也可能是你用Docker起了一個(gè)MySQL把宿主機(jī)的3306占了。我遇到過一個(gè)特別“詭異”的情況Windows服務(wù)起不來事件查看器里報(bào)了一個(gè)托管異常碼e0434352。我當(dāng)時(shí)沒有在my.ini里深挖而是把服務(wù)刪了重新注冊一遍又把殺毒軟件對MySQL整個(gè)目錄的實(shí)時(shí)掃描關(guān)掉服務(wù)就正常了。這個(gè)經(jīng)驗(yàn)就是服務(wù)起不來別猜先看日志看日志解決不了往系統(tǒng)環(huán)境、殺毒軟件、運(yùn)行庫方向想最后再考慮重裝。2.2 root密碼不是忘了是根本還沒設(shè)置第一次登錄MySQL最讓人懵的就是root密碼。如果你用的是mysqld --initialize-insecure那root初始沒有密碼直接回車就能登錄mysql -uroot -p如果用的是rpm方式安裝或者mysqld --initialize初始密碼是臨時(shí)生成的。用下面命令找回grep temporary password /var/log/mysqld.log登錄進(jìn)去之后第一件事就是改root密碼同時(shí)創(chuàng)建一個(gè)平時(shí)開發(fā)用的普通賬號。這樣做的原因是永遠(yuǎn)不要用root干業(yè)務(wù)活萬一連接串泄露root權(quán)限等于把整個(gè)庫都暴露了。改密碼和建賬號的SQL如下ALTER USER rootlocalhost IDENTIFIED BY 新密碼; CREATE USER dev% IDENTIFIED BY dev密碼; GRANT ALL PRIVILEGES ON *.* TO dev%; FLUSH PRIVILEGES;注意MySQL 8.0默認(rèn)的認(rèn)證插件是caching_sha2_password如果你的客戶端特別老登錄時(shí)可能報(bào)Authentication plugin caching_sha2_password cannot be loaded。解決辦法有兩個(gè)升級客戶端驅(qū)動(dòng)或者給那個(gè)用戶指定舊插件CREATE USER oldclient% IDENTIFIED WITH mysql_native_password BY 密碼;現(xiàn)在基本都在用新驅(qū)動(dòng)了但還是要知道這個(gè)坑因?yàn)槟憬永享?xiàng)目時(shí)隨時(shí)可能碰上。2.3 客戶端連接報(bào)SSL錯(cuò)誤隨手加上useSSLfalse就行很多教程里JDBC連接串隨手就是useSSLfalse新手照抄之后SSL錯(cuò)誤確實(shí)消失了但壓根不知道自己在做什么。先說結(jié)論這個(gè)報(bào)錯(cuò)“SSL connection error: protocol version mismatch”絕大多數(shù)情況不是MySQL配錯(cuò)了而是客戶端和MySQL支持的TLS協(xié)議版本對不上。MySQL 8.0默認(rèn)開啟SSL要求客戶端使用TLS 1.2以上老版本驅(qū)動(dòng)用TLS 1.0去握手自然失敗。如果只是本地開發(fā)、沒有涉及敏感數(shù)據(jù)useSSLfalse是可以接受的臨時(shí)方案。但一旦部署到公網(wǎng)或者公司安全審計(jì)要求加密傳輸就必須把SSL配好。實(shí)現(xiàn)方式是讓客戶端信任MySQL服務(wù)器的CA證書在驅(qū)動(dòng)里指上sslModeVERIFY_CA和serverSslCert參數(shù)。另一個(gè)因?yàn)镾SL連帶出現(xiàn)的報(bào)錯(cuò)是Public Key Retrieval is not allowed。這是MySQL 8.0的caching_sha2_password插件在非SSL連接下需要先把服務(wù)器的公鑰拉下來。解決辦法是連接串加allowPublicKeyRetrievaltrue。這個(gè)參數(shù)的意義就是告訴驅(qū)動(dòng)“我允許你獲取公鑰”本地開發(fā)可以開生產(chǎn)環(huán)境還是要優(yōu)先走SSL別把這個(gè)參數(shù)當(dāng)成萬能解藥。3. 從建庫到建第一張業(yè)務(wù)表schema、字段、索引一起說清3.1 為什么MySQL特別強(qiáng)調(diào)database、schema和table三個(gè)概念很多人一開始不理解為什么MySQL的官方文檔把database和schema經(jīng)?；熘?。在MySQL里這兩個(gè)基本是同一個(gè)東西CREATE SCHEMA等價(jià)于CREATE DATABASE。但在SQL Server、PostgreSQL里schema和database完全是兩個(gè)層級。你可以把這個(gè)關(guān)系想成一個(gè)小區(qū)MySQL實(shí)例是整片小區(qū)database是其中一棟樓table是樓里的房間。你在MySQL里常做的操作流程是先SHOW DATABASES看有哪些樓然后USE 你選中的數(shù)據(jù)庫;進(jìn)樓再SHOW TABLES;看這棟樓里有哪些房間。實(shí)際項(xiàng)目中一個(gè)應(yīng)用通常對應(yīng)一個(gè)獨(dú)立database多個(gè)表放在里面。不要把不同業(yè)務(wù)的數(shù)據(jù)強(qiáng)行塞進(jìn)同一個(gè)庫也別給一個(gè)應(yīng)用建十幾個(gè)庫后期權(quán)限管理和備份都很痛苦。這是我見過很多“第一次”項(xiàng)目里最常見的混亂。3.2 一張用戶表的建表實(shí)操與字段默認(rèn)值講完概念直接上實(shí)操。假設(shè)我在寫一個(gè)商城項(xiàng)目第一張表多半是用戶表。先建庫再建表CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE shop; CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL COMMENT 登錄名, password_hash VARCHAR(255) NOT NULL COMMENT 加密后的密碼, age INT NOT NULL DEFAULT 0 COMMENT 年齡默認(rèn)0, email VARCHAR(100) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB;這里有幾個(gè)第一次建表時(shí)一定要弄明白的點(diǎn)。第一為什么主鍵用BIGINT AUTO_INCREMENT而不是INT業(yè)務(wù)小的時(shí)候INT也夠用但用戶萬一真的做大了INT上限不到21億看著很安全可一旦要遷移數(shù)據(jù)、合并分片BIGINT能省掉很多事。這個(gè)選擇成本幾乎為零為什么不直接選大一點(diǎn)。第二字段默認(rèn)值是怎么設(shè)置的。DEFAULT 0就是給age字段設(shè)置默認(rèn)值為0如果插入時(shí)不給值MySQL自動(dòng)填0。注意把字段定義為NOT NULL DEFAULT 0時(shí)你讓這個(gè)字段“不能為空但給了默認(rèn)值”這跟“允許NULL但沒有默認(rèn)值”是兩種完全不同的語義。搜過“mysql設(shè)置默認(rèn)值為0”這個(gè)關(guān)鍵詞的朋友十有八九就是在糾結(jié)這種細(xì)節(jié)。第三COMMENT寫不寫我見過太多第一次建表的人完全不加注釋過一個(gè)月自己都忘了字段是什么含義。生產(chǎn)環(huán)境里字段注釋比代碼注釋還重要建表時(shí)一定要寫。3.3 索引和排序別等數(shù)據(jù)多到查詢變慢再補(bǔ)課第一次建表的人容易犯一個(gè)毛病把所有可能查詢的字段都加上索引。結(jié)果表不大性能沒提升寫入倒是慢了。索引的本質(zhì)是復(fù)制一份字段數(shù)據(jù)并排好序讓查詢不用全表掃描。一旦你明白這個(gè)原理就會(huì)知道頻繁作為查詢條件、并且區(qū)分度高的字段適合建索引比如username區(qū)分度極低的字段比如性別、狀態(tài)建索引反而浪費(fèi)空間。實(shí)際開發(fā)里最常用的查詢性能排查手段是EXPLAINEXPLAIN SELECT id, username FROM user WHERE username 張三;看到typeconst或ref就說明用上索引了看到typeALL就是全表掃描。全表掃描在小表上問題不大到百萬級數(shù)據(jù)就明顯卡頓。關(guān)于排序中西文環(huán)境經(jīng)常會(huì)遇到一個(gè)意外看起來明明建了索引但ORDER BY就是不走索引性能很差。原因多半是字符集排序規(guī)則問題。MySQL的utf8mb4有utf8mb4_general_ci和utf8mb4_unicode_ci等不同排序規(guī)則索引的排序規(guī)則和查詢要求不一致時(shí)優(yōu)化器只能放棄索引。中文場景用utf8mb4_general_ci排序就夠了遇到特殊拼音排序需求再單獨(dú)處理。3.4 修改表結(jié)構(gòu)ALTER TABLE不只是加個(gè)字段而已第一次接線上項(xiàng)目總會(huì)碰到“表結(jié)構(gòu)要改了”的需求。最常用的就是加字段ALTER TABLE user ADD COLUMN phone VARCHAR(20) DEFAULT NULL;就這么一句在小表上秒回但如果是一張幾千萬行的表執(zhí)行這句ALTER可能會(huì)鎖住整張表讀寫很久。MySQL 8.0的在線DDL比5.7更成熟大部分加字段操作可以在線執(zhí)行不再阻塞寫入但這不意味著你可以隨便在業(yè)務(wù)高峰期亂改大表。改列類型或者重命名就更謹(jǐn)慎比如把INT改成BIGINT這個(gè)操作會(huì)把整表重寫耗時(shí)和磁盤空間消耗都很高。我第一次操作線上表的時(shí)候只改了一個(gè)字段類型結(jié)果跑了快一上午業(yè)務(wù)側(cè)不得不做了只讀維護(hù)。之后養(yǎng)成了習(xí)慣大表結(jié)構(gòu)變更之前先用SELECT COUNT(*)和SHOW TABLE STATUS評估行數(shù)和表大小再?zèng)Q定是直接改還是用專門的改表工具。順帶提一句如果你做物聯(lián)網(wǎng)數(shù)據(jù)采集想把MySQL的表結(jié)構(gòu)自動(dòng)轉(zhuǎn)成TDengine的超級表和子表思路也是一樣的先用information_schema.columns讀MySQL的字段定義再映射到TDengine的tag和列字段類型、默認(rèn)值、注釋都得一一對上本質(zhì)還是先吃透MySQL這套元數(shù)據(jù)模型。4. 事務(wù)、存儲(chǔ)過程、觸發(fā)器和鎖第一次寫“批量邏輯”的心理準(zhǔn)備4.1 事務(wù)的四個(gè)特性和兩個(gè)實(shí)操動(dòng)作MySQL最值得花時(shí)間理解的就是事務(wù)。事務(wù)解決的核心問題非常樸素一條轉(zhuǎn)賬操作要同時(shí)扣A的錢、加B的錢如果只扣了A沒加成B那整個(gè)賬本都亂了。事務(wù)的四個(gè)特性就是ACID原子性整個(gè)事務(wù)要么全成功要么全失敗。一致性事務(wù)執(zhí)行前后數(shù)據(jù)都滿足約束。隔離性兩個(gè)事務(wù)同時(shí)操作同一條數(shù)據(jù)時(shí)互相不干擾。持久性事務(wù)提交后即使斷電也不會(huì)丟。在SQL里體現(xiàn)為START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;如果中間任何一步失敗你在出錯(cuò)后執(zhí)行ROLLBACK;兩條UPDATE都會(huì)被撤銷。新手最容易忽略的是默認(rèn)情況下你隨便執(zhí)行的每條SQL語句都是自動(dòng)提交的。只有在多語句需要被看成一個(gè)整體時(shí)才手動(dòng)START TRANSACTION。另外一個(gè)重要前提是只有InnoDB引擎支持事務(wù)。如果你的表是MyISAM哪怕寫了事務(wù)也不生效MySQL會(huì)悄悄當(dāng)單條SQL執(zhí)行。隔離級別也是個(gè)繞不過去的話題。MySQL默認(rèn)隔離級別是REPEATABLE READ也就是同一事務(wù)里你重復(fù)查同一條件結(jié)果始終一樣。不同隔離級別會(huì)帶來臟讀、不可重復(fù)讀、幻讀等不同問題。第一次接觸不用背太熟但要能說出四種隔離級別由低到高是讀未提交、讀已提交、可重復(fù)讀、串行化能說出MySQL默認(rèn)是可重復(fù)讀就夠應(yīng)付多數(shù)面試和日常開發(fā)了。4.2 存儲(chǔ)過程的DELIMITER到底解決什么問題很多新人第一次在MySQL里寫存儲(chǔ)過程明明照著教程抄卻總是報(bào)語法錯(cuò)誤。別慌多半是你沒理解DELIMITER這個(gè)命令的意義。MySQL默認(rèn)用分號作為一條語句的結(jié)束符。你寫一個(gè)存儲(chǔ)過程CREATE PROCEDURE p_test() BEGIN SELECT 1; SELECT 2; END;MySQL客戶端讀到第一個(gè)分號就把語句發(fā)送到服務(wù)器而實(shí)際上這個(gè)CREATE PROCEDURE還沒寫完服務(wù)器根本無法解析。DELIMITER的作用就是臨時(shí)告訴MySQL客戶端“你別拿分號當(dāng)結(jié)束了用別的符號”。正確寫法DELIMITER // CREATE PROCEDURE p_test() BEGIN SELECT 1; SELECT 2; END// DELIMITER ;寫完記得把分隔符改回來否則你后面所有正常的SQL語句都會(huì)因?yàn)檎也坏浇Y(jié)束符而一直等待。存儲(chǔ)過程里還經(jīng)常要處理錯(cuò)誤。比如插入數(shù)據(jù)時(shí)遇到主鍵沖突你可以捕獲信號DECLARE EXIT HANDLER FOR 1062 BEGIN SELECT 主鍵沖突了; END;這里的1062就是MySQL的錯(cuò)誤碼對應(yīng)主鍵重復(fù)。熟悉這些錯(cuò)誤碼對處理后面的“錯(cuò)誤信息”類排查很有幫助。4.3 觸發(fā)器里的分隔符與錯(cuò)誤處理觸發(fā)器和存儲(chǔ)過程一樣也需要理解分隔符問題。我第一次建觸發(fā)器時(shí)因?yàn)槁┝薉ELIMITER被奇怪的報(bào)錯(cuò)卡了很久。觸發(fā)器一個(gè)典型的用途是自動(dòng)維護(hù)審計(jì)字段DELIMITER $$ CREATE TRIGGER trg_user_before_insert BEFORE INSERT ON user FOR EACH ROW BEGIN SET NEW.created_at NOW(); END$$ DELIMITER ;觸發(fā)器的BEFORE、AFTER分別表示在事件之前還是之后觸達(dá)事件通常是INSERT、UPDATE、DELETE三種。觸發(fā)器的邏輯里不允許返回結(jié)果集也不能直接調(diào)用存儲(chǔ)過程帶返回值的部分新手最容易在這上面翻車。我要提醒的是業(yè)務(wù)邏輯能寫在應(yīng)用層就盡量寫在應(yīng)用層觸發(fā)器能不用就不用。原因是觸發(fā)器是隱式的排查問題時(shí)你根本想不到某個(gè)字段被自動(dòng)修改是觸發(fā)器干的。尤其在一個(gè)多人維護(hù)的項(xiàng)目里一個(gè)隱藏的觸發(fā)器可能讓所有人排查到崩潰。只有在做強(qiáng)制審計(jì)、多表同步這類必須靠數(shù)據(jù)庫自身保證的約束場景觸發(fā)器才值得用。4.4 鎖表與死鎖真實(shí)項(xiàng)目里第一次被“卡住”的經(jīng)歷第一次在真實(shí)項(xiàng)目里遇到“數(shù)據(jù)庫卡死”大概率就是鎖的問題。最經(jīng)典的一條SQL會(huì)阻塞別人UPDATE user SET age 10 WHERE id 1;這條語句執(zhí)行時(shí)會(huì)給id1的行加排他鎖。如果事務(wù)一直沒提交其他任何對該行的UPDATE、DELETE都會(huì)等待。你在應(yīng)用里看到的現(xiàn)象就是點(diǎn)擊按鈕一直轉(zhuǎn)圈代碼沒有報(bào)錯(cuò)數(shù)據(jù)庫連接被占滿日志里全是“鎖等待超時(shí)”。排錯(cuò)思路要清晰。先SHOW PROCESSLIST;看有哪些會(huì)話長時(shí)間處于Waiting for table metadata lock或者updating狀態(tài)再用SHOW ENGINE INNODB STATUS;看最近一次死鎖的具體SQL最后找到造成阻塞的源頭連接用KILL 進(jìn)程ID;殺掉它。死鎖是更嚴(yán)重的情況事務(wù)A鎖了行1想等行2事務(wù)B鎖了行2想等行1誰也等不到誰。InnoDB會(huì)自動(dòng)檢測死鎖并回滾其中一個(gè)事務(wù)但你的應(yīng)用必須處理“操作失敗、可能導(dǎo)致數(shù)據(jù)未提交”的回滾邏輯。預(yù)防死鎖有個(gè)非常實(shí)用的習(xí)慣多個(gè)事務(wù)如果需要更新多行數(shù)據(jù)盡量按固定的順序操作。比如訂單一類的業(yè)務(wù)永遠(yuǎn)先更新主表再更新明細(xì)表死鎖概率會(huì)大大降低。另外InnoDB的行鎖是建立在索引上的如果查詢條件沒走索引InnoDB就會(huì)鎖整張表這是另一個(gè)常見鎖表原因。5. 第一次把MySQL接入真實(shí)項(xiàng)目連接池、驅(qū)動(dòng)和客戶端得配齊5.1 JDBC連接串參數(shù)SSL、時(shí)區(qū)、公鑰獲取Java項(xiàng)目接MySQL配置JDBC連接串是繞不開的也是第一次最容易出問題的點(diǎn)。先用一個(gè)標(biāo)準(zhǔn)的MySQL 8連接串舉例jdbc:mysql://127.0.0.1:3306/shop?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/ShanghaicharacterEncodingutf8每個(gè)參數(shù)都有故事。serverTimezone必須顯式指定因?yàn)镸ySQL驅(qū)動(dòng)去讀服務(wù)器時(shí)區(qū)時(shí)如果機(jī)器時(shí)區(qū)是UTC而你的業(yè)務(wù)在東八區(qū)時(shí)間就會(huì)差8小時(shí)。第一次做完查詢發(fā)現(xiàn)時(shí)間不對頭兩個(gè)月我都沒意識(shí)到來連接串加時(shí)區(qū)。characterEncodingutf8是保證中文不亂碼的基礎(chǔ)。注意這里推薦用utf8mb4時(shí)庫表級別要把字符集統(tǒng)一設(shè)成utf8mb4連接串里寫utf8是為了兼容驅(qū)動(dòng)實(shí)際取了連接后驅(qū)動(dòng)會(huì)自動(dòng)處理。之前說的SSL錯(cuò)誤在JDBC里還涉及到useSSL和allowPublicKeyRetrieval的組合。本地開發(fā)useSSLfalse綽綽有余生產(chǎn)環(huán)境必須把證書鏈配好。這個(gè)權(quán)衡反復(fù)出現(xiàn)但每一次都要讓人理解它的含義而不是復(fù)制粘貼。5.2 C程序連接MySQL的編譯鏈接要點(diǎn)不是所有項(xiàng)目都是Java。C接MySQL也是老牌需求很多工具軟件、桌面客戶端都靠它連庫。C連接MySQL主要用官方提供的mysql.h接口Linux環(huán)境上需要安裝開發(fā)包一般叫l(wèi)ibmysqlclient-dev或mysql-community-devel。最簡單的連接代碼骨架#include mysql.h #include stdio.h int main() { MYSQL *conn mysql_init(NULL); if (conn NULL) { fprintf(stderr, 初始化失敗\n); return -1; } if (mysql_real_connect(conn, 127.0.0.1, user, password, shop, 3306, NULL, 0) NULL) { fprintf(stderr, 連接失敗: %s\n, mysql_error(conn)); mysql_close(conn); return -1; } mysql_query(conn, SELECT id, username FROM user LIMIT 10); MYSQL_RES *res mysql_store_result(conn); MYSQL_ROW row; while ((row mysql_fetch_row(res)) ! NULL) { printf(%s %s\n, row[0], row[1]); } mysql_free_result(res); mysql_close(conn); return 0; }編譯時(shí)注意三個(gè)點(diǎn)頭文件路徑、庫文件路徑、鏈接庫名g -o demo demo.cpp -lmysqlclient -I/usr/include/mysql如果連接后中文亂碼要在mysql_real_connect之后執(zhí)行mysql_set_character_set(conn, utf8mb4);。C程序里很多“連接失敗”其實(shí)是網(wǎng)絡(luò)不通或者防火墻沒放開3306端口別一股腦怪MySQL。5.3 連接池到底是什么HikariCP / Druid參數(shù)怎么配新手第一次寫JavaWeb完整項(xiàng)目時(shí)通常會(huì)在DAO層用DriverManager.getConnection()直接連數(shù)據(jù)庫。這么做功能是沒問題但性能很差。因?yàn)槊看谓?shù)據(jù)庫連接都要走網(wǎng)絡(luò)握手、認(rèn)證耗時(shí)往往在百毫秒量級用戶一多連接一開一關(guān)就吃虧。連接池的原理很簡單程序啟動(dòng)時(shí)提前創(chuàng)建一批數(shù)據(jù)庫連接放在池子里用的時(shí)候取出一個(gè)用完了放回去。它解決的是“連接復(fù)用”不是“把連接變快”。Spring Boot時(shí)代最常用的是HikariCP配置核心參數(shù)spring: datasource: url: jdbc:mysql://127.0.0.1:3306/shop?... username: dev password: xxx hikari: maximum-pool-size: 10 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000老一點(diǎn)的JavaWeb項(xiàng)目里Druid也很常見參數(shù)叫法不太一樣initialSize2 maxActive20 minIdle2 maxWait60000 validationQuerySELECT 1maximum-pool-size不是越大越好。連接數(shù)過高數(shù)據(jù)庫本身的線程調(diào)度、鎖競爭都會(huì)變嚴(yán)重。一個(gè)常見的經(jīng)驗(yàn)值是普通業(yè)務(wù)4核8G的機(jī)器上MySQL最大連接數(shù)200-300單應(yīng)用連接池給10-20就夠別一上來就設(shè)個(gè)100。5.4 Navicat、DBeaver、Workbench的選擇以及離線驅(qū)動(dòng)下載第一次會(huì)習(xí)慣下載Navicat但網(wǎng)上搜“Navicat破解”的搜索結(jié)果很危險(xiǎn)里面帶的激活工具十有八九有問題。我的看法是能用官方免費(fèi)工具的就別碰破解版。官方MySQL Workbench是免費(fèi)且持續(xù)維護(hù)的DBeaver社區(qū)版也很強(qiáng)都支持Windows、Linux和macOS。DBeaver連MySQL時(shí)也常會(huì)遇到一個(gè)離線環(huán)境問題默認(rèn)驅(qū)動(dòng)管理器會(huì)從網(wǎng)上下載驅(qū)動(dòng)jar包。公司內(nèi)網(wǎng)沒有外網(wǎng)權(quán)限下載會(huì)一直卡住這時(shí)候就需要“離線驅(qū)動(dòng)”。做法是從官網(wǎng)或網(wǎng)友的倉庫里找到對應(yīng)版本的mysql-connector-java.jar然后打開DBeaver的“數(shù)據(jù)庫 - 驅(qū)動(dòng)管理器 - MySQL”編輯驅(qū)動(dòng)庫把你的本地jar文件加進(jìn)去重啟即可。DBeaver里連接MySQL 8時(shí)如果報(bào)“Public Key Retrieval is not allowed”就在連接設(shè)置的“驅(qū)動(dòng)屬性”里把a(bǔ)llowPublicKeyRetrieval改成true。這個(gè)思路跟JDBC連接串完全一樣。理解了這層邏輯不管換什么客戶端你都難不住。5.5 大數(shù)據(jù)組件連不上MySQL的常見癥狀現(xiàn)在做數(shù)據(jù)開發(fā)的人早晚要遇到Sqoop、Kettle、Flink這些組件去連MySQL。最典型的就是Sqoop導(dǎo)入報(bào)錯(cuò)癥狀全是連接超時(shí)、認(rèn)證失敗、驅(qū)動(dòng)類找不到。sqoop連接MySQL有一個(gè)很隱蔽的坑驅(qū)動(dòng)jar包放錯(cuò)位置。Sqoop官方目錄只加載lib目錄下的驅(qū)動(dòng)你需要把mysql-connector-java.jar復(fù)制到$SQOOP_HOME/lib下并且版本要和MySQL匹配。低版本的Java驅(qū)動(dòng)連接MySQL 8時(shí)會(huì)因?yàn)镾SL握手失敗直接報(bào)Exception: SSL connection error。解決方式同樣是useSSLfalse或者直接升級驅(qū)動(dòng)到8.x。另一個(gè)癥狀是Access denied for user xxxip。這多半是MySQL的用戶授權(quán)問題。MySQL授權(quán)是按“用戶來源IP”區(qū)分的不要以為你是所有主機(jī)都能訪問。建用戶時(shí)要顯式指定CREATE USER dataworker% IDENTIFIED BY 密碼; GRANT ALL PRIVILEGES ON *.* TO dataworker%;如果只需要業(yè)務(wù)庫權(quán)限別直接給*.*按實(shí)際庫名授權(quán)更安全。6. 上了線才知道的坑慢查詢、SQL超時(shí)和主鍵設(shè)計(jì)6.1 執(zhí)行SQL超時(shí)先看鎖等待再看網(wǎng)絡(luò)第一次在生產(chǎn)環(huán)境執(zhí)行一條UPDATE等了半天沒反應(yīng)最后超時(shí)。很多人的第一反應(yīng)是網(wǎng)絡(luò)不好或者調(diào)整SQL語句但真正原因大部分時(shí)候是鎖等待。一條正常的更新語句被另一個(gè)事務(wù)阻塞會(huì)話狀態(tài)會(huì)顯示W(wǎng)aiting for lock。這種時(shí)候光優(yōu)化自己這端SQL沒用要把阻塞別人事務(wù)的連接找出來殺掉。排查命令三件套SHOW PROCESSLIST;這個(gè)命令能看到所有數(shù)據(jù)庫會(huì)話重點(diǎn)看Time列和State列比如Waiting for table metadata lock說明有DDL在等表結(jié)構(gòu)鎖。SELECT * FROM performance_schema.data_lock_waits\G這個(gè)可以看誰在等誰的鎖。SHOW ENGINE INNODB STATUS;最近一次死鎖信息基本都在這里包括事務(wù)持有的鎖、等著的鎖和對應(yīng)的SQL。我們項(xiàng)目第一次出這個(gè)問題是一個(gè)后臺(tái)定時(shí)任務(wù)開啟了事務(wù)處理過程中拋了異常但沒有ROLLBACK事務(wù)一直沒關(guān)閉導(dǎo)致整個(gè)表更新全部堵住。所以另一個(gè)習(xí)慣也非常重要應(yīng)用里開啟事務(wù)后一定要寫try-finally或者try-with-resources哪怕出錯(cuò)也要保證ROLLBACK執(zhí)行。6.2 性能調(diào)優(yōu)EXPLAIN與慢查詢?nèi)罩镜谝淮斡^察MySQL性能問題我不建議一上來就堆各種配置參數(shù)先開慢查詢?nèi)罩咀寯?shù)據(jù)庫自己告訴你哪些SQL慢。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;這樣設(shè)置之后超過1秒的SQL會(huì)記錄到慢日志文件。分析時(shí)可以用mysqldumpslow工具mysqldumpslow /var/log/mysql/slow.log慢查詢?nèi)罩灸鼙┞逗芏唷氨砻鏇]問題但實(shí)際低效”的SQL。我見過最典型的一個(gè)案例列表頁查詢用戶訂單因?yàn)閃HERE條件里對索引字段做了函數(shù)處理導(dǎo)致索引失效。比如在created_at上寫WHERE DATE(created_at) 2024-01-01函數(shù)會(huì)讓優(yōu)化器放棄索引改成WHERE created_at 2024-01-01 AND created_at 2024-01-02就恢復(fù)索引了。還要學(xué)會(huì)看EXPLAIN的type列ALL最差是全表掃描index表示把索引全部掃了一遍range是范圍掃描還不錯(cuò)ref和const是高效的等值命中。優(yōu)化方向就是讓慢SQL盡量從ALL進(jìn)化到range或ref。6.3 字段類型、默認(rèn)值和int5的小坑MySQL是弱類型數(shù)據(jù)庫這既是方便也是坑。新人寫SQL時(shí)經(jīng)常寫出類似SELECT age 5 FROM user;這在MySQL里就是普通的數(shù)值計(jì)算age列的每個(gè)值加5。如果你數(shù)據(jù)里age被存成了字符串18也沒問題MySQL會(huì)隱式轉(zhuǎn)成數(shù)字。但一旦字符串不是純數(shù)字比如18aMySQL會(huì)轉(zhuǎn)出前導(dǎo)數(shù)字18a18就轉(zhuǎn)成0。這個(gè)隱式轉(zhuǎn)換的規(guī)則不熟寫出來的結(jié)果可能就是錯(cuò)的排查起來極其難受。字段默認(rèn)值0也是一個(gè)典型案例。很多人把某個(gè)字段設(shè)成NOT NULL DEFAULT 0想著“沒值就填0”這沒問題。但如果你用ALTER TABLE把一個(gè)已有非空數(shù)據(jù)的列改成NOT NULL DEFAULT 0MySQL可能需要重建表耗時(shí)很長。而且如果表里之前有NULL值A(chǔ)LTER還可能要給你一個(gè)“非法默認(rèn)值”的報(bào)錯(cuò)因?yàn)閲?yán)格模式下把NULL轉(zhuǎn)成DEFAULT 0并不是MySQL能自動(dòng)決定的行為。所以改表之前先查一下現(xiàn)有數(shù)據(jù)SELECT COUNT(*) FROM user WHERE age IS NULL;6.4 上線前后的常用巡檢命令第一次把項(xiàng)目部署到服務(wù)器之前最好把下面這些命令挨個(gè)執(zhí)行一遍給自己吃個(gè)定心丸。-- 看版本和字符集 SELECT VERSION(); SHOW VARIABLES LIKE character_set_server; -- 看當(dāng)前連接數(shù)確認(rèn)連接池沒有把連接數(shù)打滿 SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections; -- 看慢查詢是否開啟 SHOW VARIABLES LIKE slow_query_log; -- 看當(dāng)前所有會(huì)話排查異常連接 SHOW PROCESSLIST;還有一個(gè)每天都要做的事就是備份。第一次做備份我推薦直接用mysqldump簡單可靠mysqldump -uroot -p --single-transaction shop shop_backup.sql--single-transaction是基于InnoDB的一致性快照備份備份過程中不會(huì)鎖死業(yè)務(wù)寫入。這個(gè)參數(shù)很關(guān)鍵我見過有人備份時(shí)不加它結(jié)果備份開始后所有寫操作全部被阻塞被同事罵了一早上。如果你做的是JavaWeb項(xiàng)目上線前還應(yīng)該確認(rèn)應(yīng)用層連接池的maxActive或者maximum-pool-size小于MySQL的max_connections否則一旦應(yīng)用連接數(shù)漲上來數(shù)據(jù)庫會(huì)被連接打爆。排查一下就一條命令的事卻能在上線高峰期救你一命。第一次使用MySQL的經(jīng)歷本質(zhì)上是“一邊抄、一邊踩、一邊懂”的過程。安裝也好建表也好遇到報(bào)錯(cuò)也罷關(guān)鍵不是死記命令而是理解背后那套關(guān)于數(shù)據(jù)存儲(chǔ)、連接、索引和事務(wù)的模型。把這套模型折騰明白了以后無論換成RDS還是云數(shù)據(jù)庫你都能很快適應(yīng)。如果你正在經(jīng)歷這個(gè)階段記住一個(gè)原則每次動(dòng)手前先備份每次改完表先看影響行數(shù)每次出了鎖問題先看SHOW PROCESSLIST這三條能幫你擋住大部分生產(chǎn)事故。