我接手過太多數(shù)據(jù)庫一團(tuán)亂的項目十有八九都是MySQL。說實話MySQL這個東西入門門檻確實低裝完就能跑但真正能玩明白的人真不多。很多人用了好幾年還停留在會敲幾條增刪改查的層面一問到索引為什么失效、事務(wù)隔離級別怎么選、主從延遲怎么處理就支支吾吾了。這篇東西我就把MySQL從安裝到日常操作、再到工程化實踐的核心內(nèi)容系統(tǒng)過一遍既有基礎(chǔ)語法也有實戰(zhàn)踩坑適合剛?cè)腴T的同學(xué)照著操作也能給有一定經(jīng)驗但沒系統(tǒng)整理過的朋友查漏補缺。1. 為什么繞不開MySQL選型與生態(tài)1.1 MySQL解決了什么問題先搞清楚一個本質(zhì)問題我們?yōu)槭裁匆脭?shù)據(jù)庫直接寫文件不行嗎本地文件讀寫確實能存數(shù)據(jù)但一旦涉及并發(fā)訪問、數(shù)據(jù)一致性、快速檢索、權(quán)限控制純文件方案就徹底崩潰了。MySQL本質(zhì)上就是一個幫你管理數(shù)據(jù)的管家數(shù)據(jù)放在磁盤上但通過內(nèi)存緩存、索引結(jié)構(gòu)、日志系統(tǒng)把讀寫效率做到極致同時用事務(wù)和鎖機制保證多用戶操作下數(shù)據(jù)不亂套。用一個生活化的類比文件系統(tǒng)就像你隨手往抽屜里塞衣服找的時候得翻半天MySQL就像一個分門別類的衣柜每件衣服都有固定位置放進(jìn)取出都非?？於夷憷掀欧憧诖覗|西的時候你還能鎖上幾個抽屜。1.2 和其他數(shù)據(jù)庫的差異市面上數(shù)據(jù)庫不少Oracle、PostgreSQL、SQL Server加上各種NoSQL。MySQL能長期霸占中小型互聯(lián)網(wǎng)公司的主流地位靠的是幾個核心優(yōu)勢開源免費社區(qū)極其活躍遇到問題一搜一大堆解決方案。性能足夠強在正確的索引和配置下單庫支撐幾百上千并發(fā)完全沒問題。生態(tài)非常完善從ORM框架到同步工具、中間件能覆蓋絕大多數(shù)業(yè)務(wù)場景。上手成本低語法直觀不像Oracle那樣有大量需要DBA才能搞明白的復(fù)雜概念。當(dāng)然MySQL也有它的短板復(fù)雜分析查詢不如PostgreSQL靈活超大并發(fā)場景需要借助分庫分表或中間件擴展某些高級特性如窗口函數(shù)、遞歸CTE直到較新版本才逐步完善。如果你是純學(xué)習(xí)者或者中小項目MySQL絕對是最合理的選擇。2. 安裝與基礎(chǔ)環(huán)境搭建從零跑通2.1 下載與安裝要點很多初學(xué)者卡在第一步的下載上。去官網(wǎng)下載包時你會看到好多版本到底選哪個除非有特定業(yè)務(wù)需求否則直接選最新的社區(qū)版Community Server就好目前主流是8.x系列5.7雖然還在用但已經(jīng)逐漸退出歷史舞臺。選安裝包時注意區(qū)分安裝包類型適用場景備注MSI InstallerWindows圖形化安裝適合新手一鍵配置ZIP ArchiveWindows免安裝版手動配置適合熟悉命令行的人RPM / DEBCentOS / UbuntuLinux最常用Docker鏡像開發(fā)和測試環(huán)境一鍵拉起環(huán)境隔離Windows下用MSI安裝時有幾個細(xì)節(jié)值得注意Character Set選擇utf8mb4這是MySQL 8.0的默認(rèn)值務(wù)必保留Authentication Method里8.0默認(rèn)使用caching_sha2_password如果你還要用老版本的Navicat或舊代碼連庫可能報認(rèn)證插件不兼容這時選擇Legacy認(rèn)證更省事。Linux以Ubuntu為例下安裝就簡單了sudo apt update sudo apt install mysql-server sudo systemctl status mysql安裝完成后默認(rèn)會創(chuàng)建一個root用戶認(rèn)證方式是auth_socket也就是說只有系統(tǒng)root用戶才能登錄直接用mysql -u root -p反而連不上。先切到root權(quán)限執(zhí)行登錄然后把root的密碼重置成需要的認(rèn)證方式。這個坑我見太多人踩過。2.2 初始化、登錄與error 2002排查安裝完成后MySQL默認(rèn)的root密碼是空的但很多發(fā)行版會要求你初始化。手動執(zhí)行更新root密碼的標(biāo)準(zhǔn)操作如下ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密碼; FLUSH PRIVILEGES;然后就能正常登錄了mysql -u root -p實際運維中最常見的連接問題就是明明MySQL跑得好好的怎么連不上了高頻報錯場景與原因?qū)φ請箦e信息原因解決辦法ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sockMySQL服務(wù)根本沒啟動systemctl start mysql / service mysql startERROR 1045 (28000): Access denied for user rootlocalhost密碼錯誤通過skip-grant-tables重置密碼ERROR 1040 (HY000): Too many connections連接數(shù)被占滿調(diào)大max_connections排查長連接泄漏那個2002錯誤重點看一眼MySQL進(jìn)程是否存活。確認(rèn)進(jìn)程存在卻依然報socket錯誤大概率是socket文件路徑不對。查看你的my.cnf里socket的配置路徑grep socket /etc/mysql/my.cnf然后用-S參數(shù)手動指定socket文件連接即可。3. 從建庫到增刪改查核心操作手把手3.1 數(shù)據(jù)庫和表的基本操作進(jìn)入MySQL后第一步就是建庫建表。我通常會按這樣的習(xí)慣去做數(shù)據(jù)庫名用小寫下劃線表名用業(yè)務(wù)相關(guān)的完整單詞字段名不用保留字。創(chuàng)建數(shù)據(jù)庫和表的示例CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE school; CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主鍵ID, stu_no VARCHAR(20) NOT NULL COMMENT 學(xué)號, name VARCHAR(50) NOT NULL COMMENT 姓名, gender TINYINT NOT NULL DEFAULT 0 COMMENT 性別 0未知 1男 2女, birthday DATE DEFAULT NULL COMMENT 出生日期, phone VARCHAR(20) DEFAULT NULL COMMENT 聯(lián)系電話, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時間, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新時間, PRIMARY KEY (id), UNIQUE KEY uk_stu_no (stu_no), KEY idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT學(xué)生表;建表有幾個關(guān)鍵細(xì)節(jié)InnoDB引擎要保留支持事務(wù)和行級鎖主鍵用自增整型性能好且innodb的聚簇索引結(jié)構(gòu)最友好每個字段盡量設(shè)計好默認(rèn)值避免業(yè)務(wù)端大量判空邏輯varchar的長度不要隨手寫255根據(jù)實際業(yè)務(wù)長度約束就好過長的varchar在索引和臨時表排序時都會帶來額外開銷。查看表結(jié)構(gòu)和索引的命令日常調(diào)試和排查問題會用得很頻繁DESC student; SHOW CREATE TABLE student; SHOW INDEX FROM student;3.2 INSERT與SELECT查詢的完整語法數(shù)據(jù)寫入是日常操作中最頻繁的。INSERT語法比較簡單但有一個技巧在實際開發(fā)里特別常用批量插入。INSERT INTO student (stu_no, name, gender, birthday, phone) VALUES (2024001, 張三, 1, 2000-01-15, 13800000001), (2024002, 李四, 2, 2001-05-20, 13800000002), (2024003, 王五, 1, 1999-12-03, 13800000003);相比一條條插入批量插入的速度提升非常明顯因為減少了客戶端和MySQL之間的網(wǎng)絡(luò)往返次數(shù)另外一條INSERT語句在InnoDB下只要一個事務(wù)提交而多條INSERT需要多次提交落盤和日志刷寫的開銷差距懸殊。SELECT是MySQL里最核心也最復(fù)雜的操作。先看完整的查詢語法格式SELECT 字段列表 FROM 表名 [WHERE 條件] [GROUP BY 分組字段] [HAVING 分組后的過濾條件] [ORDER BY 排序字段] [LIMIT 偏移量, 行數(shù)];ORDER BY是熱搜詞里反復(fù)出現(xiàn)的操作。單字段排序很簡單但多字段排序時你只需要記住排序優(yōu)先級是嚴(yán)格按照ORDER BY后面的字段順序來的前面的權(quán)重更高。-- 先按性別分組排序性別相同的再按生日從早到晚排列 SELECT * FROM student ORDER BY gender ASC, birthday ASC;對于分頁場景LIMIT的偏移量越大查詢越慢因為MySQL需要掃描并跳過前面所有的行。假如你要翻到第100000頁LIMIT 999990, 10會非常慢正確做法是用游標(biāo)方式記錄上一頁最后一條記錄的id然后SELECT * FROM student WHERE id 999990 ORDER BY id LIMIT 10;這個優(yōu)化在千萬級數(shù)據(jù)量時的性能差距可以到幾十倍甚至百倍細(xì)節(jié)決定成敗。WHERE條件里有很多新手容易忽略的坑。函數(shù)包裹字段會讓索引失效比如WHERE YEAR(birthday) 2000就沒辦法用birthday上的索引前導(dǎo)通配符LIKE %張也一樣隱式類型轉(zhuǎn)換更要命字符串字段和數(shù)字比較時索引直接失效。這些底層原因在講索引時再展開。3.3 UPDATE與DELETE必須小心的高危操作先看UPDATE的官方語法官方文檔中的基礎(chǔ)框架是UPDATE 表名 SET 字段1 值1, 字段2 值2, ... [WHERE 條件];關(guān)鍵點永遠(yuǎn)是那句不帶WHERE的UPDATE就是全表更新。我親眼見過同事在測試環(huán)境手滑執(zhí)行了不帶條件的UPDATE導(dǎo)致整個表數(shù)據(jù)被覆蓋成同一個值最后只能從備份恢復(fù)。這句話說了無數(shù)遍但每天還是有人中招。實際開發(fā)中更常見的隱患是在批量更新時你以為的合理條件可能覆蓋了不該更新的行。比如UPDATE student SET phone 13900000000 WHERE name 張三;如果表里有多個叫張三的學(xué)生這一下就把所有人的號碼全改了。所以UPDATE前先用同條件的SELECT確認(rèn)影響行數(shù)是個成本極低卻極其有效的自我保護(hù)動作。DELETE的語法框架DELETE FROM 表名 [WHERE 條件];注意DELETE和TRUNCATE的區(qū)別DELETE是逐行刪除走事務(wù)可以回滾但不會重置自增IDTRUNCATE是直接重建表速度極快但不可回滾自增ID會重置。小表無所謂幾千萬行的大表用DELETE清理數(shù)據(jù)是一個災(zāi)難級的慢操作用TRUNCATE則瞬間完成但前提是你確認(rèn)不需要回滾且表中沒有外鍵引用。還有DELETE的另一個經(jīng)典優(yōu)化場景刪除大量數(shù)據(jù)時一次DELETE大批量行會造成長事務(wù)、大量undo日志和長時間的鎖持有。正確做法是循環(huán)批次刪除DELETE FROM logs WHERE created_at 2023-01-01 LIMIT 1000;反復(fù)執(zhí)行該語句直到影響行數(shù)為0。每批只刪除1000行事務(wù)短暫鎖持有時間短對主從拉取和在線業(yè)務(wù)的影響最小。4. 讓數(shù)據(jù)庫真正能用索引、事務(wù)與進(jìn)階機制4.1 索引的本質(zhì)與最左前綴索引是MySQL性能的第一要素。為什么加索引后查詢能快幾個數(shù)量級因為MySQL底層用B樹來存儲索引查找的時間復(fù)雜度是O(log n)幾百萬行數(shù)據(jù)只要二十多次磁盤比較就能定位到目標(biāo)而全表掃描要做幾百萬次。但不是說索引越多越好每個索引在寫入時都要額外維護(hù)一棵B樹插入、更新、刪除時都會變慢磁盤空間也會增大。建立索引前要考慮你的查詢模式最頻繁的WHERE條件字段、排序字段、關(guān)聯(lián)字段適合建索引頻繁更新的字段不適合建索引。復(fù)合索引多列索引是最容易用錯的東西。索引(a, b, c)生效規(guī)則是最左前綴查詢中只有包含a或者同時包含a和b或者a、b、c同時存在索引才會被利用。只有b和c的查詢完全用不到這個索引。這個規(guī)則很多人聽說過但沒真正理解面試?yán)镆矌缀跏潜乜碱}。覆蓋索引是性能優(yōu)化中收益最顯著的手段如果查詢需要的字段全部包含在索引中MySQL就不需要回表查聚簇索引直接在索引樹上就拿到全部數(shù)據(jù)。比如CREATE INDEX idx_name_age ON student(name, age); SELECT name, age FROM student WHERE name 張三;這個SQL就實現(xiàn)了覆蓋索引的訪問路徑查詢速度比SELECT *快得多。在編寫高頻查詢時把需要的字段盡量都放進(jìn)復(fù)合索引里這是最簡單有效又不加額外開銷的優(yōu)化方式。4.2 事務(wù)、隔離級別與日常影響InnoDB是事務(wù)型存儲引擎事務(wù)的ACID特性是MySQL能保證數(shù)據(jù)可靠性的基石。事務(wù)以下四個特性原子性一個事務(wù)的所有操作要么全部成功要么全部回滾。一致性事務(wù)執(zhí)行前后數(shù)據(jù)完整性約束不被破壞。隔離性多個事務(wù)并發(fā)執(zhí)行時相互之間不會產(chǎn)生干擾。持久性事務(wù)一旦提交數(shù)據(jù)修改就是永久的。MySQL提供的事務(wù)隔離級別有四種默認(rèn)是可重復(fù)讀隔離級別臟讀不可重復(fù)讀幻讀讀未提交可能可能可能讀已提交不會可能可能可重復(fù)讀不會不會可能InnoDB通過間隙鎖解決串行化不會不會不會設(shè)置事務(wù)隔離級別的方式SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; -- 你的SQL操作 COMMIT; -- 或者 ROLLBACK;日常開發(fā)中最常見的事務(wù)問題就是事務(wù)里混入了慢查詢導(dǎo)致事務(wù)長時間持有連接和鎖拖垮整個數(shù)據(jù)庫連接池。事務(wù)應(yīng)該是短小精悍的一個事務(wù)只處理一個業(yè)務(wù)操作把耗時的外部接口調(diào)用、文件讀寫、休眠放在事務(wù)外面。這條準(zhǔn)則幾乎可以解決90%的開發(fā)期事務(wù)性能問題。4.3 存儲過程與觸發(fā)器存儲過程在當(dāng)年大火過一陣子現(xiàn)在企業(yè)開發(fā)里用的少了。原因很簡單業(yè)務(wù)邏輯放在數(shù)據(jù)庫里版本管理困難調(diào)試麻煩數(shù)據(jù)庫本身也不擅長處理復(fù)雜邏輯。但某些場景下存儲過程仍然十分合適比如定時統(tǒng)計報表、周期性的數(shù)據(jù)清理任務(wù)、批量數(shù)據(jù)處理。聲明一個存儲過程的基本框架DELIMITER $$ CREATE PROCEDURE sp_get_students_by_gender(IN p_gender TINYINT) BEGIN SELECT id, stu_no, name, birthday FROM student WHERE gender p_gender; END$$ DELIMITER ;注意DELIMITER的使用MySQL默認(rèn)用分號作為語句分隔符而存儲過程內(nèi)部也有分號為了讓整個存儲過程正確交付給MySQL服務(wù)端解析需要用DELIMITER臨時把分隔符改成其他符號。很多人就卡在這個細(xì)節(jié)上直接導(dǎo)致創(chuàng)建語句報語法錯誤。調(diào)用方式CALL sp_get_students_by_gender(1); DROP PROCEDURE IF EXISTS sp_get_students_by_gender;觸發(fā)器是一種更特殊的存儲程序在INSERT、UPDATE、DELETE操作發(fā)生時自動執(zhí)行。比如需要記錄操作日志、自動更新某些匯總字段可以用觸發(fā)器。但觸發(fā)器的問題也同樣很多隱式執(zhí)行、難以排查、嵌套過多會鎖疊加所以在業(yè)務(wù)系統(tǒng)里我通常不推薦大量使用能用應(yīng)用層邏輯解決的盡量不用觸發(fā)器。4.4 必須掌握的視圖與常用函數(shù)視圖是一個虛擬表本質(zhì)是把一條SELECT查詢打包成一個命名對象。它的價值在于給上層應(yīng)用提供屏蔽表結(jié)構(gòu)變化的能力、隱藏敏感字段、簡化復(fù)雜查詢的調(diào)用。CREATE VIEW v_student_info AS SELECT id, stu_no, name, gender, birthday FROM student; SELECT * FROM v_student_info WHERE gender 1;當(dāng)你改了基礎(chǔ)表結(jié)構(gòu)比如把name拆分成了first_name和last_name只要視圖的定義不變上層應(yīng)用就不需要改任何SQL。這個解耦能力在維護(hù)老系統(tǒng)時特別省心。MySQL內(nèi)置的常用函數(shù)也需要熟悉。字符串函數(shù)里CONCAT、SUBSTRING、REPLACE出鏡率最高日期函數(shù)里DATE_FORMAT、YEAR、MONTH、DATEDIFF最常出現(xiàn)在統(tǒng)計報表中聚合函數(shù)里COUNT、SUM、AVG、MAX、MIN是GROUP BY語句的標(biāo)配。比如按年份統(tǒng)計學(xué)生出生分布SELECT YEAR(birthday) AS birth_year, COUNT(*) AS cnt FROM student GROUP BY YEAR(birthday) ORDER BY birth_year;如果需要對分組結(jié)果再過濾記住WHERE是在分組前過濾的HAVING是在分組后過濾的。這個區(qū)別面試中出現(xiàn)頻率極高。5. 連接池、同步與備份恢復(fù)工程化必備5.1 連接池原理與常見參數(shù)每次連接MySQL都要經(jīng)過TCP握手、認(rèn)證、權(quán)限檢查、分配資源這些操作加起來往往要幾十甚至幾百毫秒。在頻繁請求的場景下每次都新建連接只會把性能拖垮。連接池就是把已經(jīng)創(chuàng)建好的連接緩存起來請求需要時直接復(fù)用。以Java的HikariCP為例常見參數(shù)是參數(shù)推薦值說明maximumPoolSize10-50最大連接數(shù)不是越大越好minimumIdle5-10最小空閑連接數(shù)connectionTimeout30000獲取連接的超時時間毫秒maxLifetime1800000連接最大存活時間毫秒連接數(shù)不是越大越好。每個MySQL連接本身就要消耗內(nèi)存而且受限于MySQL的max_connections設(shè)置。連接池大小設(shè)定有一個經(jīng)典的參考公式核心數(shù)乘以2再加磁盤IO系數(shù)大多數(shù)業(yè)務(wù)系統(tǒng)10-20個連接就足夠用了。連接池過大導(dǎo)致線程切換頻繁、CPU空轉(zhuǎn)性能反而下降。5.2 主從復(fù)制與數(shù)據(jù)同步工具生產(chǎn)環(huán)境中單庫MySQL再強也有極限讀寫分離是最常見的擴展手段主庫負(fù)責(zé)寫入從庫負(fù)責(zé)讀從庫通過復(fù)制機制同步主庫的binlog日志。MySQL主從復(fù)制的底層原理不復(fù)雜主庫將更改記錄到二進(jìn)制日志binlog從庫的IO線程拉取主庫binlog寫進(jìn)中繼日志relay log從庫的SQL線程讀取中繼日志在從庫上按順序重放執(zhí)行。配置主從的核心步驟-- 主庫 CREATE USER repl% IDENTIFIED BY repl_password; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES; SHOW MASTER STATUS; -- 記下File和Position -- 從庫 CHANGE MASTER TO MASTER_HOST主庫IP, MASTER_USERrepl, MASTER_PASSWORDrepl_password, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS157; START SLAVE; SHOW SLAVE STATUS\G; -- 檢查Slave_IO_Running和Slave_SQL_Running除了手動搭主從現(xiàn)在也有很多成熟的同步工具能簡化操作。市面上主流的有Canal阿里開源偽裝成從庫拉取binlog常用于異構(gòu)同步、DataX做離線批量數(shù)據(jù)同步、Maxwell以JSON格式輸出binlog變更事件。選型的核心依據(jù)是同步場景實時變更捕獲用Canal離線大數(shù)據(jù)同步用DataX簡單的事件流輸出用Maxwell。不要把工具當(dāng)作萬能的binlog格式要設(shè)為ROW模式才能正確解析到變更前后的數(shù)據(jù)這是做數(shù)據(jù)同步項目里最容易被忽略的前提。5.3 備份恢復(fù)實戰(zhàn)數(shù)據(jù)備份是DBA和開發(fā)者都逃不掉的責(zé)任尤其是誤操作刪數(shù)據(jù)之后你會發(fā)現(xiàn)備份有多重要。mysqldump是官方最常用的邏輯備份工具# 單庫備份 mysqldump -u root -p school school_backup.sql # 多庫備份 mysqldump -u root -p --databases school hr multi_db_backup.sql # 全庫備份 mysqldump -u root -p --all-databases all_databases.sql恢復(fù)備份mysql -u root -p school school_backup.sql幾個實用參數(shù)值得記住--single-transaction可以在備份InnoDB表時不鎖表對在線業(yè)務(wù)友好--set-gtid-purgedOFF在導(dǎo)入GTID模式實例時需要設(shè)置壓縮備份文件用gzip一條管道命令搞定mysqldump -u root -p school | gzip school_backup.sql.gz備份不是只執(zhí)行一次就完事還要定期驗證備份文件能不能正常導(dǎo)入到測試實例。我處理過的數(shù)據(jù)事故里最慘痛的從來不是沒備份而是有備份但恢復(fù)不了那比沒備份更讓人崩潰。6. 常見問題排查與避坑實錄6.1 高頻報錯速查表把這些年遇到過的高頻異常整理一下方便直接參考報錯或異?，F(xiàn)象根本原因處理方法Cant connect to MySQL server (10061)服務(wù)未啟動或端口被防火墻攔截先查服務(wù)狀態(tài)再查3306端口通沒通ERROR 1045 Access denied用戶名密碼錯誤或權(quán)限不足核對賬號檢查user表host匹配ERROR 1064 syntax errorSQL語法錯誤重點看保留字是否沒加反引號、引號是否匹配ERROR 1175 safe update modeSQL_SAFE_UPDATES安全模式未關(guān)SET SQL_SAFE_UPDATES0; 或者補WHERE條件ERROR 1205 Lock wait timeout exceeded事務(wù)持鎖時間過長其他事務(wù)等待超時查INNODB_TRX干掉超時事務(wù)優(yōu)化慢SQL字符亂碼客戶端、連接、表、字段字符集不一致統(tǒng)一用utf8mb4SET NAMES utf8mb4鎖等待超時是生產(chǎn)環(huán)境最坑的一個問題?，F(xiàn)象是應(yīng)用偶爾報錯重啟后又正常過一陣又犯。排查思路如下SELECT * FROM information_schema.innodb_trx\G SELECT * FROM sys.innodb_lock_waits\G; KILL 事務(wù)ID;拿不到鎖多數(shù)是某個事務(wù)開了但不提交導(dǎo)致行鎖一直不釋放。定位到持鎖事務(wù)后確認(rèn)對應(yīng)代碼邏輯要么補齊事務(wù)提交要么引入超時重試機制。這里有一個關(guān)鍵經(jīng)驗數(shù)據(jù)庫層面設(shè)innodb_lock_wait_timeout的默認(rèn)值50秒太長了等它在線上超時業(yè)務(wù)早就雪崩了。在業(yè)務(wù)容忍范圍內(nèi)把這個參數(shù)調(diào)小比如5秒快速失敗比無限等待更健康。6.2 易踩坑點之UPDATE與DELETE的實戰(zhàn)教訓(xùn)上面雖然提到了UPDATE和DELETE的注意事項但這里要單獨拉出來再說一次因為生產(chǎn)事故十有八九都出在這兩個操作上。親身踩過的一個大坑是這樣某個活動上線前運營需要批量把用戶等級整體提升一級SQL大致是UPDATE user SET level level 1 WHERE active 1;看起來沒毛病。結(jié)果執(zhí)行完發(fā)現(xiàn)銷售歸類到等級5的部分用戶被錯誤地提升到了等級6因為level字段定義的是TINYINT部分用戶已經(jīng)在等級5而WHERE條件里沒有限定歷史等級導(dǎo)致不應(yīng)升級的用戶被帶上去了。那一次事故讓我們熬了一個通宵從備份里把數(shù)據(jù)撈回來。教訓(xùn)總結(jié)為三條鐵律UPDATE或DELETE之前先執(zhí)行同條件的SELECT確認(rèn)影響行數(shù)確認(rèn)目標(biāo)無誤。重要表提前開啟binlog且設(shè)為ROW格式誤操作用binlog2sql等工具可以反向生成回滾SQL。線上高危操作盡量放在業(yè)務(wù)低峰期并且分批小量執(zhí)行而非一把梭。6.3 面試與學(xué)習(xí)準(zhǔn)備常用題目與知識框架MySQL面試題是熱搜里的大頭。把這個知識點體系理清楚面試和自測都很實用。常考的核心問題我歸為四類第一類索引相關(guān)索引的數(shù)據(jù)結(jié)構(gòu)為什么選B樹而不是B樹或紅黑樹答核心點B樹非葉子節(jié)點不存數(shù)據(jù)相同大小數(shù)據(jù)頁能容納更多索引項樹高更矮葉子節(jié)點用雙向鏈表串聯(lián)范圍查詢效率極高。還有最左前綴原則和索引失效的常見場景函數(shù)包裹字段、隱式類型轉(zhuǎn)換、LIKE的前導(dǎo)通配符、OR連接的非索引列、聯(lián)合索引中跳過中間列。第二類事務(wù)與鎖四種隔離級別分別能解決什么問題InnoDB的可重復(fù)讀是默認(rèn)級別為什么答InnoDB通過MVCC實現(xiàn)快照讀配合間隙鎖解決幻讀保證默認(rèn)隔離級別下的高并發(fā)讀性能。行鎖、表鎖、間隙鎖、臨鍵鎖的區(qū)別也要能說清楚。第三類高可用與復(fù)制主從延遲的原因和解決方案從庫同步慢時先看硬件和網(wǎng)絡(luò)再看從庫是否有大量并發(fā)查詢爭搶資源最后看大事務(wù)導(dǎo)致binlog積累過多。解決方案是多從分層、增加并行復(fù)制線程、把大事務(wù)拆小。第四類優(yōu)化思路一條慢SQL你怎么排查和優(yōu)化標(biāo)準(zhǔn)的回答路徑是慢查詢?nèi)罩径ㄎ籗QLEXPLAIN分析執(zhí)行計劃看type列全表掃描為ALL索引掃描為range/ref/const看key是否走索引綜合OPTIMIZER_TRACE判斷走了哪個索引然后按需添加聯(lián)合索引、改寫SQL、分解復(fù)雜查詢、引入緩存或讀寫分離。6.4 關(guān)于數(shù)據(jù)庫課程設(shè)計的一點提醒數(shù)據(jù)庫課程設(shè)計也是熱搜詞里的高頻詞。很多人上來就寫代碼建表和業(yè)務(wù)邏輯全混在一起最后呈現(xiàn)出來不知所云。我給個建議做課程設(shè)計時先畫三張圖再做其他E-R圖實體、屬性、聯(lián)系畫清楚這是數(shù)據(jù)庫設(shè)計的第一性原理關(guān)系搞錯后面全白做。關(guān)系模式圖把E-R圖轉(zhuǎn)成表結(jié)構(gòu)標(biāo)出主鍵外鍵。這里要注意規(guī)范化到第三范式3NF每個非主屬性既不部分依賴也不傳遞依賴于主鍵。系統(tǒng)架構(gòu)圖明確前后端、數(shù)據(jù)庫之間如何交互請求從哪進(jìn)、數(shù)據(jù)從哪出。比如做一個學(xué)生選課系統(tǒng)先定學(xué)生、課程、教師、選課記錄四張核心表。其中學(xué)生和課程是多對多關(guān)系必須拆出選課記錄表來建立聯(lián)系選課記錄表里要有學(xué)生ID、課程ID、成績、選課時間這是典型的多對多轉(zhuǎn)三張表的建模。順帶說一句設(shè)計表時預(yù)留created_at和updated_at字段雖然課程設(shè)計里不一定用得上但這能讓你從代碼思維切換到工程思維未來實際工作中受益。7. 最后想說的MySQL這塊內(nèi)容其實沒有太多玄學(xué)核心就三塊會安裝會用懂語法會優(yōu)化知道原理能排查。安裝配置和基礎(chǔ)增刪改查花不了多少功夫重點是修煉索引和事務(wù)這兩條主線再把主從復(fù)制、備份恢復(fù)這些工程能力補齊。我見過太多人學(xué)了幾個月還在各種基礎(chǔ)報錯里打轉(zhuǎn)其實不少問題用一臺本地實例加幾條日志命令就能定位清楚。動手試錯永遠(yuǎn)比看一百篇教程好用。至于更進(jìn)階的優(yōu)化器原理、內(nèi)核源碼之類等你在固定場景里遇到真實的性能瓶頸再帶著問題去研究那會兒的效率最高。