在我們?nèi)粘９ぷ髦校赡軙?huì)經(jīng)常使用MySQL數(shù)據(jù)庫，因?yàn)樗情_源免費(fèi)的，而且性能還不錯(cuò)。

在國內(nèi)的很多公司中，經(jīng)常被使用。

但我們在MySQL使用過程中，也非常容易踩坑，不信繼續(xù)往下看。

今天這篇文章重點(diǎn)跟大家一起聊一聊使用 MySQL 的15個(gè)坑，希望對你會(huì)有所幫助。

1.查詢不加where條件

有些小伙伴，希望在代碼中，一次性把表中的所有數(shù)據(jù)都查出來，然后在內(nèi)存中處理業(yè)務(wù)邏輯，認(rèn)為代碼性能更好。

反例：

SELECT * FROM users;

在查詢數(shù)據(jù)的時(shí)候不加where條件。

這種情況下數(shù)據(jù)量小還好。

但如果數(shù)據(jù)量很大，每個(gè)業(yè)務(wù)操作，都需要查出表中的所有數(shù)據(jù)，可能會(huì)導(dǎo)致程序出現(xiàn)OOM問題。

如果數(shù)據(jù)太多，處理速度也會(huì)集聚下降。

正例：

SELECT * FROM users WHERE code = '1001';

使用具體的where查詢條件，比如code字段，先過濾數(shù)據(jù)，再做處理。

2.沒有使用索引

有時(shí)候，我們的程序，在剛上線的時(shí)候，數(shù)據(jù)比較少，沒有加索引，問題不大。

但隨著用戶量越來越多，表中數(shù)據(jù)在呈指數(shù)級(jí)的增加。

突然有一天發(fā)現(xiàn)，查詢數(shù)據(jù)變慢了。

例如：

SELECT * FROM orders WHERE customer_id = 123;

我們可以給customer_id字段加個(gè)索引：

CREATE INDEX idx_customer ON orders(customer_id);

這能大大提升速度！

3.不處理 NULL 值

問題描述：統(tǒng)計(jì)時(shí)忘了 NULL 的影響，以為結(jié)果準(zhǔn)確，結(jié)果卻大相徑庭。

反例：

SELECT COUNT(name) FROM users;

這些只能統(tǒng)計(jì)name字段非NULL的數(shù)量。

其實(shí)，沒有統(tǒng)計(jì)完全。

如果想統(tǒng)計(jì)所有的記錄行數(shù)，我們可以使用COUNT(*)。

正例：

SELECT COUNT(*) FROM users;

這樣就能統(tǒng)計(jì)所有行數(shù)。

4.數(shù)據(jù)類型選錯(cuò)

有些小伙伴，在創(chuàng)建表時(shí)，隨意使用 VARCHAR(255)，會(huì)導(dǎo)致性能低下，還浪費(fèi)存儲(chǔ)。

反例：

CREATE TABLE products (
    id INT,
    status VARCHAR(255) 
);

這種情況的性能不佳。

我們可以將status字段該成tinyint類型：

CREATE TABLE products (
    id INT,
    status tinyint(1) DEFAULT '0' COMMENT '狀態(tài) 1：有效 0：無效'
);

更節(jié)省空間。

5.深分頁問題

我們在日常工作中，經(jīng)常會(huì)遇到需要分頁查詢數(shù)據(jù)的場景。

我們一般會(huì)使用limit關(guān)鍵字。

例如：

SELECT * FROM users LIMIT 0,10;

如果數(shù)據(jù)多的時(shí)候，第一頁、第二頁、第三頁可能查詢性能還OK。

但如果查詢到第10萬頁，可能查詢性能，就會(huì)變得非常差。

這就出現(xiàn)了深分頁問題。

如何解決深分頁問題？

5.1 記錄上一次的id

我們現(xiàn)在的主要問題是，在分頁的查詢過程中，假如要查詢第10萬頁的數(shù)據(jù)，要先掃描第9萬9999頁的數(shù)據(jù)。

但如果我們把上一次查詢的位置記錄下，后面再查詢下一頁的時(shí)候，就可以直接從之前的位置開始，往后查詢。

例如下面這樣的：

select id,name where order 
where id>1000000 limit 100000,10

上一次查詢獲取到的最大的id是1000000，那么本次查詢直接從1000000的下一個(gè)位置開始查詢。

這樣就可以不用查詢前面的數(shù)據(jù)，提升不少的查詢效率。

但這套方案有兩個(gè)需要注意的地方：

需要記錄上一次的查詢出的id，適合上一頁或下一頁的場景，不適合隨機(jī)查詢到某一頁。需要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條數(shù)據(jù)，但需要回表100010次。

實(shí)際上，我們只需要查詢10條數(shù)據(jù)，也就是我們只需要10次回表其實(shí)就夠了。

通過子查詢的方式，能夠減少回表的次數(shù)。

因此，我們可以通過減少回表次數(shù)來優(yōu)化深分頁的問題。

5.3 使用inner join關(guān)聯(lián)查詢

根據(jù)子查詢類似：

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子語句中，也是先通過查詢條件和分頁條件過濾數(shù)據(jù)，返回id。

然后再通過id做關(guān)聯(lián)查詢。

可以減少回表的次數(shù)，從而提升查詢速度。

6 沒有用explain分析查詢

有些現(xiàn)在sql語句，查詢慢，卻不去分析執(zhí)行計(jì)劃，結(jié)果就只能盲目優(yōu)化。

正例：

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN 會(huì)告訴你查詢是怎么執(zhí)行的，幫助你找到瓶頸。

如果大家想進(jìn)一步了解explain關(guān)鍵字，可以看看我的另一篇文章《SQL性能優(yōu)化神器》，里面有非常詳細(xì)的介紹。

7 字符集設(shè)置不當(dāng)

有些小伙伴，喜歡將MySQL的字符集設(shè)置成utf8。

我?guī)啄曛耙蚕矚g這干。

但后面出現(xiàn)問題了，比如在用戶評(píng)價(jià)輸入框中，用戶輸入了表情符合，可能會(huì)直接導(dǎo)致程序保存。

字符集設(shè)置錯(cuò)誤，也可能會(huì)導(dǎo)致漢字變亂碼，用戶體驗(yàn)直線下滑。

正例：

CREATE TABLE messages (
    id INT,
    content TEXT
) CHARACTER SET utf8mb4;

建議大家在建表時(shí)，將字符集設(shè)置成使utf8mb4，它能夠支持更多的字符，包括：常用中文漢字和一些表情符號(hào)。

8 SQL注入風(fēng)險(xiǎn)

用拼接 SQL 的方式，容易被 SQL 注入攻擊，安全隱患大。

在一些自定義排序規(guī)則，使用order by 動(dòng)態(tài)拼接用戶選擇的排序字段，或者排序方式，比如：升序或降序時(shí)，如果處理不好，就可能會(huì)出現(xiàn)SQL注入問題。

反例：

String query = "SELECT * FROM users WHERE email = '" + userInput + "';";

盡量少在sql中直接拼接字符串，而應(yīng)該使用PreparedStatement預(yù)編譯的方式。

正例：

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE email = ?");
stmt.setString(1, userInput);

在MyBatis中在使用$符號(hào)賦值時(shí)要注意，最好使用#符號(hào)賦值。

如果大家對sql注入問題比較感興趣，可以看看我的另一篇文章《臥槽，sql注入竟然把我們的系統(tǒng)搞掛了》，里面有非常詳細(xì)的介紹。

9.事務(wù)問題

有些小伙伴，在日常工作中，寫代碼時(shí)可能會(huì)忘掉事務(wù)。

特別是在更新多個(gè)表時(shí)不使用事務(wù)，數(shù)據(jù)容易出現(xiàn)不一致的情況。

反例：

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

用戶1給用戶2轉(zhuǎn)賬100元，如果不用事務(wù)，可能會(huì)出現(xiàn)用戶1轉(zhuǎn)出了100，用戶2卻沒收到的情況。

我們使用使用START TRANSACTION命令開啟事務(wù)，使用COMMIT命令提交事務(wù)。

正例：

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

這樣如果用戶1轉(zhuǎn)出100成功了，但用戶2轉(zhuǎn)入100失敗了，則用戶1的數(shù)據(jù)會(huì)回滾。

在Spring中可以使用@Transactional注解聲明式事務(wù)，或者使用TransactionTemplate類這種編程式事務(wù)。

建議優(yōu)先使用TransactionTemplate這種編程式事務(wù)。

10.校對規(guī)則問題

我們的表和字段上，有個(gè)COLLATE參數(shù)，可以配置校對規(guī)則。

它主要包含了三種：

• 以_ci結(jié)尾的。
• 以_bin結(jié)尾的。
• 以_cs結(jié)尾的。

ci是case insensitive的縮寫，意思是大小寫不敏感，即忽略大小寫。

cs是case sensitive的縮寫，意思是大小寫敏感，即區(qū)分大小寫。

還有一種是bin，它是將字符串中的每一個(gè)字符用二進(jìn)制數(shù)據(jù)存儲(chǔ)，區(qū)分大小寫。

使用最多的是 utf8mb4_general_ci（默認(rèn)的）和 utf8mb4_bin。

我們的brand表在創(chuàng)建表的時(shí)候，使用的COLLATE是utf8mb4_general_ci，它不區(qū)分大小寫。

CREATE TABLE `brand` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(30) NOT NULL COMMENT '品牌名稱',
  `create_user_id` bigint NOT NULL COMMENT '創(chuàng)建人ID',
  `create_user_name` varchar(30) NOT NULL COMMENT '創(chuàng)建人名稱',
  `create_time` datetime(3) DEFAULT NULL COMMENT '創(chuàng)建日期',
  `update_user_id` bigint DEFAULT NULL COMMENT '修改人ID',
  `update_user_name` varchar(30)  DEFAULT NULL COMMENT '修改人名稱',
  `update_time` datetime(3) DEFAULT NULL COMMENT '修改時(shí)間',
  `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語句查詢數(shù)據(jù)時(shí)：

select * from brand where `name`='yoyo';

就能把大寫的YOYO查出來。

如果我們的表中設(shè)置的COLLATE是不區(qū)分大小寫，但是業(yè)務(wù)代碼中，卻區(qū)分了大小寫，二者不一致，就可能會(huì)出問題。

這時(shí)候，在業(yè)務(wù)代碼中，就不能直接使用equals方法判斷字符串是否相同，而應(yīng)該改成equalsIgnoreCase方法了。

11.使用過多的 SELECT *

有些小伙伴，在寫的sql語句中，習(xí)慣性使用select *，一次性查詢所有的字段。

反例：

SELECT * FROM orders;

這種做法每次都會(huì)查出很多沒用的字段，不僅浪費(fèi)帶寬，也增加了查詢開銷。

好的做法是，每次只查詢要用到的字段。

正例：

SELECT id, total FROM orders;

我們的業(yè)務(wù)中，只需要用到id和total字段的數(shù)據(jù)，其他的字段就可以無需查詢。

12.索引失效

不知道你有沒有遇到過，生成環(huán)境明明創(chuàng)建了索引，但數(shù)據(jù)庫在執(zhí)行SQL的過程中，索引竟然失效了。

由于索引失效，讓之前原本很快的操作，一下子變得很慢，影響了接口的性能。

我們可以通過explain關(guān)鍵字，查看sql的執(zhí)行計(jì)劃，可以確認(rèn)索引是否失效。

如果索引失效了，可能是哪些原因?qū)е碌膯栴}呢？

下面這張圖給大家列舉了常見原因：

圖片

想進(jìn)一步了解索引失效問題的小伙伴，可以看一下我的另一篇文章《聊聊索引失效的10種場景，太坑了》，里面有非常詳細(xì)的介紹。

13.頻繁修改表或數(shù)據(jù)

在高并發(fā)場景下，頻繁添加、修改字段，或者批量更新數(shù)據(jù)，導(dǎo)致系統(tǒng)性能下降。

我們在使用alter添加或者修改表字段，或者使用update批量更新，或者使用delete批量刪除數(shù)據(jù)時(shí)，都可能會(huì)鎖表。

如果此時(shí)正好有大量的用戶請求過來了，會(huì)導(dǎo)致系統(tǒng)響應(yīng)變慢。

在高并發(fā)場景下，update或者delete的數(shù)據(jù)量，不要太多，可以分批，多次執(zhí)行。

對于一些alter或drop修改表結(jié)構(gòu)的操作，應(yīng)該避免在用戶高峰期執(zhí)行，最好選擇在凌晨，用戶少的時(shí)候執(zhí)行。

此外，可以使用Percona Toolkit、gh-ost等在線工具，可以在不鎖表的情況下，進(jìn)行alter操作。

14 沒有定期備份

在工作中，最怕遇到豬隊(duì)友誤刪數(shù)據(jù)。

我遇到過好幾次。

將測試環(huán)境的表中的數(shù)據(jù)全刪了。

數(shù)據(jù)全沒了就后悔，太晚了。

建議定期備份，使用mysqldump：

mysqldump -u root -p database_name > backup.sql

我們可以寫一個(gè)定時(shí)任務(wù)，每個(gè)一段時(shí)間，比如：一天或，備份一次數(shù)據(jù)。

后面如果哪天又被誤刪數(shù)據(jù)了，可以直接通過mysql命令，將數(shù)據(jù)還原。

15.忘了歸檔歷史數(shù)據(jù)

有些小伙伴，經(jīng)常吐槽，表中的歷史數(shù)據(jù)太多，查詢速度像蝸牛一樣慢。

這時(shí)候，我們需要將歷史數(shù)據(jù)歸檔了。

用戶一般最關(guān)心的是最近：一個(gè)月、三個(gè)月、半年或者一年的數(shù)據(jù)。

他們極少會(huì)去查詢一年以上的數(shù)據(jù)。

因此，建議將歷史數(shù)據(jù)做歸檔。

在MySQL中只保存最新的數(shù)據(jù)，歷史數(shù)據(jù)可以遷移到歸檔庫中。