MySQL 中最容易踩的 15 個坑 ！
在我們日常工作中，可能會經常使用MySQL數據庫，因為它是開源免費的，而且性能還不錯。
在國內的很多公司中，經常被使用。
但我們在MySQL使用過程中，也非常容易踩坑，不信繼續往下看。
今天這篇文章重點跟大家一起聊一聊使用 MySQL 的15個坑，希望對你會有所幫助。
1.查詢不加where條件
有些小伙伴，希望在代碼中，一次性把表中的所有數據都查出來，然后在內存中處理業務邏輯，認為代碼性能更好。
反例：
SELECT * FROM users;在查詢數據的時候不加where條件。
這種情況下數據量小還好。
但如果數據量很大，每個業務操作，都需要查出表中的所有數據，可能會導致程序出現OOM問題。
如果數據太多，處理速度也會集聚下降。
正例：
SELECT * FROM users WHERE code = '1001';使用具體的where查詢條件，比如code字段，先過濾數據，再做處理。
2.沒有使用索引
有時候，我們的程序，在剛上線的時候，數據比較少，沒有加索引，問題不大。
但隨著用戶量越來越多，表中數據在呈指數級的增加。
突然有一天發現，查詢數據變慢了。
例如：
SELECT * FROM orders WHERE customer_id = 123;我們可以給customer_id字段加個索引：
CREATE INDEX idx_customer ON orders(customer_id);這能大大提升速度！
3.不處理 NULL 值
問題描述：統計時忘了 NULL 的影響，以為結果準確，結果卻大相徑庭。
反例：
SELECT COUNT(name) FROM users;這些只能統計name字段非NULL的數量。
其實，沒有統計完全。
如果想統計所有的記錄行數，我們可以使用COUNT(*)。
正例：
SELECT COUNT(*) FROM users;這樣就能統計所有行數。
4.數據類型選錯
有些小伙伴，在創建表時，隨意使用 VARCHAR(255)，會導致性能低下，還浪費存儲。
反例：
CREATE TABLE products (
id INT,
status VARCHAR(255)
);這種情況的性能不佳。
我們可以將status字段該成tinyint類型：
CREATE TABLE products (
id INT,
status tinyint(1) DEFAULT '0' COMMENT '狀態 1：有效 0：無效'
);更節省空間。
5.深分頁問題
我們在日常工作中，經常會遇到需要分頁查詢數據的場景。
我們一般會使用limit關鍵字。
例如：
SELECT * FROM users LIMIT 0,10;如果數據多的時候，第一頁、第二頁、第三頁可能查詢性能還OK。
但如果查詢到第10萬頁，可能查詢性能，就會變得非常差。
這就出現了深分頁問題。
如何解決深分頁問題？
5.1 記錄上一次的id
我們現在的主要問題是，在分頁的查詢過程中，假如要查詢第10萬頁的數據，要先掃描第9萬9999頁的數據。
但如果我們把上一次查詢的位置記錄下，后面再查詢下一頁的時候，就可以直接從之前的位置開始，往后查詢。
例如下面這樣的：
select id,name where order
where id>1000000 limit 100000,10上一次查詢獲取到的最大的id是1000000，那么本次查詢直接從1000000的下一個位置開始查詢。
這樣就可以不用查詢前面的數據，提升不少的查詢效率。
但這套方案有兩個需要注意的地方：
需要記錄上一次的查詢出的id，適合上一頁或下一頁的場景，不適合隨機查詢到某一頁。需要id字段是自增的。
5.2 使用子查詢
先用子查詢查詢出符合條件的主鍵，再用主鍵id做條件查出所有字段。
select * from order where id in (
select id from (
select id from order where time>'2024-08-11' limit 100000, 10
) t
)這樣子查詢中，可以走覆蓋索引。
我們之前的SQL，查詢10條數據，但需要回表100010次。
實際上，我們只需要查詢10條數據，也就是我們只需要10次回表其實就夠了。
通過子查詢的方式，能夠減少回表的次數。
因此，我們可以通過減少回表次數來優化深分頁的問題。
5.3 使用inner join關聯查詢
根據子查詢類似：
select * from order o1
inner join (
select id from order
where create_time>'2024-08-11'
limit 100000,10
) as o2 on o1.id=o2.id;在inner join子語句中，也是先通過查詢條件和分頁條件過濾數據，返回id。
然后再通過id做關聯查詢。
可以減少回表的次數，從而提升查詢速度。
6 沒有用explain分析查詢
有些現在sql語句，查詢慢，卻不去分析執行計劃，結果就只能盲目優化。
正例：
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';EXPLAIN 會告訴你查詢是怎么執行的，幫助你找到瓶頸。
如果大家想進一步了解explain關鍵字，可以看看我的另一篇文章《SQL性能優化神器》，里面有非常詳細的介紹。
7 字符集設置不當
有些小伙伴，喜歡將MySQL的字符集設置成utf8。
我幾年之前也喜歡這干。
但后面出現問題了，比如在用戶評價輸入框中，用戶輸入了表情符合，可能會直接導致程序保存。
字符集設置錯誤，也可能會導致漢字變亂碼，用戶體驗直線下滑。
正例：
CREATE TABLE messages (
id INT,
content TEXT
) CHARACTER SET utf8mb4;建議大家在建表時，將字符集設置成使utf8mb4，它能夠支持更多的字符，包括：常用中文漢字和一些表情符號。
8 SQL注入風險
用拼接 SQL 的方式，容易被 SQL 注入攻擊，安全隱患大。
在一些自定義排序規則，使用order by 動態拼接用戶選擇的排序字段，或者排序方式，比如：升序或降序時，如果處理不好，就可能會出現SQL注入問題。
反例：
String query = "SELECT * FROM users WHERE email = '" + userInput + "';";盡量少在sql中直接拼接字符串，而應該使用PreparedStatement預編譯的方式。
正例：
PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE email = ?");
stmt.setString(1, userInput);在MyBatis中在使用$符號賦值時要注意，最好使用#符號賦值。
如果大家對sql注入問題比較感興趣，可以看看我的另一篇文章《臥槽，sql注入竟然把我們的系統搞掛了》，里面有非常詳細的介紹。
9.事務問題
有些小伙伴，在日常工作中，寫代碼時可能會忘掉事務。
特別是在更新多個表時不使用事務，數據容易出現不一致的情況。
反例：
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;用戶1給用戶2轉賬100元，如果不用事務，可能會出現用戶1轉出了100，用戶2卻沒收到的情況。
我們使用使用START TRANSACTION命令開啟事務，使用COMMIT命令提交事務。
正例：
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;這樣如果用戶1轉出100成功了，但用戶2轉入100失敗了，則用戶1的數據會回滾。
在Spring中可以使用@Transactional注解聲明式事務，或者使用TransactionTemplate類這種編程式事務。
建議優先使用TransactionTemplate這種編程式事務。
10.校對規則問題
我們的表和字段上，有個COLLATE參數，可以配置校對規則。
它主要包含了三種：
- 以_ci結尾的。
- 以_bin結尾的。
- 以_cs結尾的。
ci是case insensitive的縮寫，意思是大小寫不敏感，即忽略大小寫。
cs是case sensitive的縮寫，意思是大小寫敏感，即區分大小寫。
還有一種是bin，它是將字符串中的每一個字符用二進制數據存儲，區分大小寫。
使用最多的是 utf8mb4_general_ci（默認的）和 utf8mb4_bin。
我們的brand表在創建表的時候，使用的COLLATE是utf8mb4_general_ci，它不區分大小寫。
CREATE TABLE `brand` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(30) NOT NULL COMMENT '品牌名稱',
`create_user_id` bigint NOT NULL COMMENT '創建人ID',
`create_user_name` varchar(30) NOT NULL COMMENT '創建人名稱',
`create_time` datetime(3) DEFAULT NULL COMMENT '創建日期',
`update_user_id` bigint DEFAULT NULL COMMENT '修改人ID',
`update_user_name` varchar(30) DEFAULT NULL COMMENT '修改人名稱',
`update_time` datetime(3) DEFAULT NULL COMMENT '修改時間',
`is_del` tinyint(1) DEFAULT '0' COMMENT '是否刪除 1：已刪除 0：未刪除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='品牌表';這樣在使用下面sql語句查詢數據時：
select * from brand where `name`='yoyo';就能把大寫的YOYO查出來。
如果我們的表中設置的COLLATE是不區分大小寫，但是業務代碼中，卻區分了大小寫，二者不一致，就可能會出問題。
這時候，在業務代碼中，就不能直接使用equals方法判斷字符串是否相同，而應該改成equalsIgnoreCase方法了。
11.使用過多的 SELECT *
有些小伙伴，在寫的sql語句中，習慣性使用select *，一次性查詢所有的字段。
反例：
SELECT * FROM orders;這種做法每次都會查出很多沒用的字段，不僅浪費帶寬，也增加了查詢開銷。
好的做法是，每次只查詢要用到的字段。
正例：
SELECT id, total FROM orders;我們的業務中，只需要用到id和total字段的數據，其他的字段就可以無需查詢。
12.索引失效
不知道你有沒有遇到過，生成環境明明創建了索引，但數據庫在執行SQL的過程中，索引竟然失效了。
由于索引失效，讓之前原本很快的操作，一下子變得很慢，影響了接口的性能。
我們可以通過explain關鍵字，查看sql的執行計劃，可以確認索引是否失效。
如果索引失效了，可能是哪些原因導致的問題呢？
下面這張圖給大家列舉了常見原因：
圖片
想進一步了解索引失效問題的小伙伴，可以看一下我的另一篇文章《聊聊索引失效的10種場景，太坑了》，里面有非常詳細的介紹。
13.頻繁修改表或數據
在高并發場景下，頻繁添加、修改字段，或者批量更新數據，導致系統性能下降。
我們在使用alter添加或者修改表字段，或者使用update批量更新，或者使用delete批量刪除數據時，都可能會鎖表。
如果此時正好有大量的用戶請求過來了，會導致系統響應變慢。
在高并發場景下，update或者delete的數據量，不要太多，可以分批，多次執行。
對于一些alter或drop修改表結構的操作，應該避免在用戶高峰期執行，最好選擇在凌晨，用戶少的時候執行。
此外，可以使用Percona Toolkit、gh-ost等在線工具，可以在不鎖表的情況下，進行alter操作。
14 沒有定期備份
在工作中，最怕遇到豬隊友誤刪數據。
我遇到過好幾次。
將測試環境的表中的數據全刪了。
數據全沒了就后悔，太晚了。
建議定期備份，使用mysqldump：
mysqldump -u root -p database_name > backup.sql我們可以寫一個定時任務，每個一段時間，比如：一天或，備份一次數據。
后面如果哪天又被誤刪數據了，可以直接通過mysql命令，將數據還原。
15.忘了歸檔歷史數據
有些小伙伴，經常吐槽，表中的歷史數據太多，查詢速度像蝸牛一樣慢。
這時候，我們需要將歷史數據歸檔了。
用戶一般最關心的是最近：一個月、三個月、半年或者一年的數據。
他們極少會去查詢一年以上的數據。
因此，建議將歷史數據做歸檔。
在MySQL中只保存最新的數據，歷史數據可以遷移到歸檔庫中。
