在我們日常工作中，可能會經常使用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中只保存最新的數據，歷史數據可以遷移到歸檔庫中。