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