一. 數(shù)據(jù)庫設(shè)計優(yōu)化

1. 選擇合適的字段類型

設(shè)計表時，盡量選擇存儲空間小的字段類型：

• 整型字段：從TINYINT、SMALLINT、INT到BIGINT。
• 小數(shù)類型：對于金額等需精確計算的數(shù)值使用DECIMAL，避免使用FLOAT和DOUBLE。
• 字符串：根據(jù)實際長度選擇CHAR（定長）或VARCHAR（變長）。VARCHAR不宜超過5000字符；長度需求大的數(shù)據(jù)建議使用TEXT，并將大字段拆分到單獨的表中

2. 確定字段的合理長度

字段長度表示字符數(shù)或字節(jié)數(shù)，例如VARCHAR(32)適用于用戶名字段（通常為5到20個字符）。建議字段長度設(shè)為2的冪，如32、64、128等。

3. 控制表的字段數(shù)量

一張表的字段數(shù)量盡量控制在20個以內(nèi)，以避免數(shù)據(jù)量過大導(dǎo)致查詢效率低。如果字段較多，建議分拆為多張表。

4. 盡量定義字段為 NOT NULL

為防止空指針問題，并提升查詢性能，除非有特殊需求，字段都應(yīng)定義為NOT NULL，可以通過默認值或常量來填充字段。

5. 使用數(shù)值類型代替字符串

數(shù)值類型占用存儲空間小、比較速度更快。

例子： 性別字段建議用數(shù)值（如0代表女生，1代表男生）而非字符串（如"WOMEN"、“MAN”）。

6. 評估并添加必要的索引

根據(jù)表的數(shù)據(jù)量和查詢需求設(shè)置索引：

• 索引數(shù)量不宜過多（單表索引個數(shù)不超過5個）。

• 區(qū)分度低的字段（如性別）不適合建立索引。

• 可通過聯(lián)合索引優(yōu)化多列條件查詢。

• 定期分析和優(yōu)化索引

  ANALYZE TABLE user_info_tab;
  

7. 避免使用MySQL保留字

避免庫名、表名或字段名使用MySQL的保留字（如SELECT、DESC等），否則需要使用反引號引用，會增加代碼復(fù)雜性。

8. 統(tǒng)一字符集的選擇

字符集推薦使用utf8或utf8mb4以支持中英文及emoji。其他字符集如GBK僅適合中文環(huán)境，latin1適合僅支持英文的場景。

9. 數(shù)據(jù)冗余

在某些場景下，適當(dāng)?shù)臄?shù)據(jù)冗余可以提高查詢效率，例如在讀多寫少的應(yīng)用中。

10. 使用分區(qū)表

對于大數(shù)據(jù)量的表，使用分區(qū)表可以提高查詢性能。

示例：

• 范圍分區(qū)（Range Partitioning）：

  • 根據(jù)某個字段的值范圍進行分區(qū)。適用于時間戳、日期等有序字段。

  CREATE TABLE sales (sale_id INT,sale_date DATE,amount DECIMAL(10,2)
  ) PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
  );
  

• 列表分區(qū)（List Partitioning）：

  • 根據(jù)某個字段的特定值列表進行分區(qū)。適用于類別、地區(qū)等離散字段。

  CREATE TABLE employees (emp_id INT,department VARCHAR(20)
  ) PARTITION BY LIST (department) (PARTITION p_sales VALUES IN ('Sales'),PARTITION p_marketing VALUES IN ('Marketing'),PARTITION p_it VALUES IN ('IT')
  );
  

• 哈希分區(qū)（Hash Partitioning）：

  • 根據(jù)某個字段的哈希值進行分區(qū)。適用于均勻分布的數(shù)據(jù)。

  CREATE TABLE customers (cust_id INT,name VARCHAR(50)
  ) PARTITION BY HASH (cust_id) PARTITIONS 4;
  

• 復(fù)合分區(qū)（Composite Partitioning）：

  • 結(jié)合多種分區(qū)策略，先按一種策略分區(qū)，再在每個子分區(qū)中按另一種策略分區(qū)。適用于復(fù)雜的數(shù)據(jù)分布。

  CREATE TABLE sales (sale_id INT,sale_date DATE,region VARCHAR(20)
  ) PARTITION BY RANGE (YEAR(sale_date))
  SUBPARTITION BY LIST (region) (PARTITION p2019 VALUES LESS THAN (2020) (SUBPARTITION p2019_north VALUES IN ('North'),SUBPARTITION p2019_south VALUES IN ('South')),PARTITION p2020 VALUES LESS THAN (2021) (SUBPARTITION p2020_north VALUES IN ('North'),SUBPARTITION p2020_south VALUES IN ('South'))
  );
  

二. 分析與調(diào)優(yōu)

1. 使用 EXPLAIN 分析查詢計劃

EXPLAIN 可以幫助你了解查詢的執(zhí)行計劃，找出潛在的性能瓶頸。

例子：

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

2. 使用 PARTITION PRUNING 優(yōu)化分區(qū)表查詢

PARTITION PRUNING 可以顯著提高分區(qū)表的查詢性能，因為它可以跳過不需要的分區(qū)。

例子：

SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

3. 使用 EXPLAIN ANALYZE 深入分析查詢性能

EXPLAIN ANALYZE 可以提供詳細的查詢執(zhí)行計劃和實際執(zhí)行時間，幫助你更好地優(yōu)化查詢。

例子：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

4. 使用 OPTIMIZE TABLE 優(yōu)化表性能

OPTIMIZE TABLE 可以回收未使用的空間，提高表的性能。

OPTIMIZE TABLE users;

5. 使用 ANALYZE TABLE 更新統(tǒng)計信息

ANALYZE TABLE 可以更新表的統(tǒng)計信息，幫助優(yōu)化器生成更高效的查詢計劃。

例子：

ANALYZE TABLE users;

三. 查詢語句優(yōu)化

1. 避免使用 SELECT *，使用具體字段

反例：

SELECT * FROM employee;

正例：

SELECT id, name, age FROM employee;

原因： 使用具體字段可以節(jié)省資源、減少網(wǎng)絡(luò)開銷，且能避免回表查詢。

2. 避免在 WHERE 子句中使用 OR

反例：

SELECT * FROM user WHERE userid=1 OR age=18;

正例：

-- 使用 UNION ALL
SELECT * FROM user WHERE userid=1
UNION ALL
SELECT * FROM user WHERE age=18;

原因：當(dāng) OR 操作符連接的條件涉及多個列時，數(shù)據(jù)庫優(yōu)化器可能無法有效地使用索引。特別是當(dāng)這些列上有不同的索引時，優(yōu)化器可能無法選擇最優(yōu)的索引組合。

3. 避免在 WHERE 子句中使用函數(shù)

反例：

SELECT * FROM users WHERE UPPER(username) = 'JOHN';

正例：

SELECT * FROM users WHERE username = 'JOHN';

原因： 在 WHERE 子句中使用函數(shù)會導(dǎo)致索引失效。

4. 避免在 WHERE 子句中對字段進行表達式操作

反例：

SELECT * FROM user WHERE age - 1 = 10;

正例：

SELECT * FROM user WHERE age = 11;

原因 : 表達式操作會增加額外的計算開銷。數(shù)據(jù)庫引擎需要對每一行數(shù)據(jù)進行表達式計算，然后再進行過濾，這會顯著增加查詢的執(zhí)行時間。

5. 使用 LIMIT 避免不必要的數(shù)據(jù)返回

反例：

SELECT id, order_date FROM order_tab WHERE user_id=666 ORDER BY create_date DESC;

正例：

SELECT id, order_date FROM order_tab WHERE user_id=666 ORDER BY create_date DESC LIMIT 1;

原因： LIMIT 提升查詢效率，避免多余的數(shù)據(jù)返回。

6. 批量操作（插入、刪除、查詢）

反例：

for(User u : list) {INSERT INTO user(name, age) VALUES(#name#, #age#);
}

正例：

INSERT INTO user(name, age) VALUES
<foreach collection="list" item="item" index="index" separator=",">(#{item.name}, #{item.age})
</foreach>

原因： 批量插入性能更優(yōu)。

7. 使用 UNION ALL 替換 UNION（無重復(fù)記錄時）

反例：

SELECT * FROM user WHERE userid=1
UNION
SELECT * FROM user WHERE age=10;

正例：

SELECT * FROM user WHERE userid=1
UNION ALL
SELECT * FROM user WHERE age=10;

原因： UNION 會排序和合并，UNION ALL 則省去這一步。

8. 避免在索引列上使用內(nèi)置函數(shù)

反例：

SELECT * FROM orders WHERE MONTH(order_date) = 10 AND YEAR(order_date) = 2023;

正例：

SELECT * FROM orders WHERE order_date >= '2023-10-01' AND order_date < '2023-11-01';

原因： 索引列上使用函數(shù)會導(dǎo)致索引失效。

9. 在 GROUP BY 前進行條件過濾

反例：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING city = '北京';

正例：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE city = '北京'
GROUP BY user_id;

10. 優(yōu)化 LIKE 語句

反例：

SELECT userId, name FROM user WHERE userId LIKE '%123';

正例：

SELECT userId, name FROM user WHERE userId LIKE '123%';

原因： % 放在前面會導(dǎo)致索引失效。

11. 使用小表驅(qū)動大表

例子： 假設(shè)我們有個客戶表和一個訂單表。其中訂單表有10萬記錄，客戶表只有1000行記錄?，F(xiàn)在要查詢下單過的客戶信息，可以這樣寫：

正例：

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

使用 IN 實現(xiàn)：

SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders
);

原因： EXISTS 會逐行掃描 customers 表（即小表），對每一行 c.id，在 orders 表（大表）中檢查是否有 customer_id = c.id 的記錄。

12. IN 查詢的元素不宜太多

反例：

SELECT user_id, name FROM user WHERE user_id IN (1,2,3...1000000);

正例： 分批進行，比如每批200個：

SELECT user_id, name FROM user WHERE user_id IN (1,2,3...200);

原因： 如果 IN 后面的元素過多，即使后面的條件加了索引，還是會影響性能。

13. 優(yōu)化 LIMIT 分頁

反例：

SELECT id, name, balance FROM account WHERE create_time > '2020-09-19' LIMIT 100000, 10;

正例： 使用標(biāo)簽記錄法：

SELECT id, name, balance FROM account WHERE id > 100000 LIMIT 10;

延遲關(guān)聯(lián)法：

SELECT acct1.id, acct1.name, acct1.balance 
FROM account acct1 
INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' LIMIT 100000, 10
) AS acct2 
ON acct1.id = acct2.id;

14. 避免返回過多數(shù)據(jù)量

反例：

SELECT * FROM LivingInfo WHERE watchId = userId AND watchTime >= DATE_SUB(NOW(), INTERVAL 1 YEAR);

正例：

-- 分頁查詢
SELECT * FROM LivingInfo WHERE watchId = userId AND watchTime >= DATE_SUB(NOW(), INTERVAL 1 YEAR) LIMIT offset, pageSize;

原因：

• 查詢效率： 當(dāng)返回的數(shù)據(jù)量過大時，查詢所需的時間會顯著增加，導(dǎo)致數(shù)據(jù)庫性能下降。
• 網(wǎng)絡(luò)傳輸： 大量數(shù)據(jù)的傳輸會占用網(wǎng)絡(luò)帶寬，可能導(dǎo)致網(wǎng)絡(luò)擁堵和延遲。
• 減少返回的數(shù)據(jù)量可以降低網(wǎng)絡(luò)傳輸?shù)呢摀?dān)，提高數(shù)據(jù)傳輸效率。

15. 優(yōu)先使用連接查詢而非子查詢

反例：

SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

正例：

SELECT DISTINCT c.* 
FROM customers c 
JOIN orders o ON c.id = o.customer_id;

原因： 使用子查詢可能會創(chuàng)建臨時表。

16. INNER JOIN、LEFT JOIN、RIGHT JOIN，優(yōu)先使用 INNER JOIN，如果是 LEFT JOIN，左邊表結(jié)果盡量小

反例：

SELECT * FROM tab1 t1 LEFT JOIN tab2 t2 ON t1.size = t2.size WHERE t1.id > 2;

正例：

SELECT * FROM (SELECT * FROM tab1 WHERE id > 2) t1 
LEFT JOIN tab2 t2 ON t1.size = t2.size;

原因： 如果 INNER JOIN 是等值連接，返回的行數(shù)可能較少，性能更好。使用 LEFT JOIN 時，左邊表數(shù)據(jù)結(jié)果盡量小，條件盡量放在左邊處理。

17. 避免 != 或 <> 操作符

反例：

SELECT age, name FROM user WHERE age <> 18;

正例： 分為兩個查詢：

SELECT age, name FROM user WHERE age < 18;
SELECT age, name FROM user WHERE age > 18;

原因： 某些情況下，使用 != 或 <> 操作符可能導(dǎo)致索引失效，從而影響查詢性能。這是因為 != 和 <> 操作符通常會導(dǎo)致數(shù)據(jù)庫引擎無法高效地利用索引。

18. 使用聯(lián)合索引時遵循最左匹配原則

例子： 聯(lián)合索引 (userId, age)，查詢 userId 和 age 時優(yōu)先使用 userId。

表結(jié)構(gòu)：

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` int(11) NOT NULL,`age` int(11) DEFAULT NULL,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`),KEY `idx_userid_age` (`userId`, `age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

反例：

SELECT * FROM user WHERE age = 10;

正例：

SELECT * FROM user WHERE userId = 10 AND age = 10;

正例：

SELECT * FROM user WHERE userId = 10;

原因： 使用聯(lián)合索引時遵循最左匹配原則是非常重要的，這有助于數(shù)據(jù)庫引擎高效地利用索引，從而提高查詢性能。最左匹配原則意味著在查詢條件中，從聯(lián)合索引的最左邊開始匹配索引列，直到遇到不匹配的列為止。

19. 對 WHERE 和 ORDER BY 涉及的列建索引

反例：

SELECT * FROM user WHERE address = '深圳' ORDER BY age;

正例：

ALTER TABLE user ADD INDEX idx_address_age (address, age);

20. 使用覆蓋索引

正例：

SELECT id, name FROM user WHERE userId LIKE '123%';

21. 刪除冗余索引

反例：

KEY `idx_userId` (`userId`)
KEY `idx_userId_age` (`userId`, `age`)

正例：

-- 刪除 `userId` 索引，因為組合索引（A，B）相當(dāng)于創(chuàng)建了（A）和（A，B）索引
KEY `idx_userId_age` (`userId`, `age`)

原因： 重復(fù)的索引需要維護，并且優(yōu)化器在優(yōu)化查詢的時候也需要逐個地進行考慮，這會影響性能。

22. 使用 INDEX HINTS 強制使用特定索引

例子：

SELECT * FROM users USE INDEX (idx_username) WHERE username = 'john';

原因： 在某些情況下，優(yōu)化器可能選擇錯誤的索引，使用 INDEX HINTS 可以強制使用特定索引。

23. 使用 FULLTEXT 索引進行全文搜索

例子：

CREATE FULLTEXT INDEX idx_fulltext ON articles (content);SELECT * FROM articles WHERE MATCH(content) AGAINST('search term');

原因： FULLTEXT 索引可以提高全文搜索的性能。

24. 避免在 ORDER BY 子句中使用表達式

反例：

SELECT * FROM users ORDER BY LENGTH(username);

正例：

SELECT * FROM users ORDER BY username;

原因： 在 ORDER BY 子句中使用表達式會導(dǎo)致索引失效。

25. 避免超過3個以上的表連接

原因： 連接表越多，編譯的時間和開銷也就越大。把連接表拆開成較小的幾個執(zhí)行，可讀性更高。

26. 使用 CASE 語句替代復(fù)雜的 IF 條件

例子：

SELECT id, CASE WHEN age < 18 THEN 'Minor'WHEN age BETWEEN 18 AND 60 THEN 'Adult'ELSE 'Senior'END AS age_group
FROM users;

原因： CASE 語句可以使邏輯更清晰，提高可讀性和維護性。

27. 使用 WITH 子句（Common Table Expressions, CTE）

例子：

WITH active_users AS (SELECT id FROM users WHERE status = 'active'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);

原因： CTE 可以使查詢結(jié)構(gòu)更清晰，便于理解和維護。

28. 避免使用 DISTINCT 除非必要

反例：

SELECT DISTINCT user_id FROM orders;

正例：

SELECT user_id FROM orders GROUP BY user_id;

原因： DISTINCT 會進行額外的排序和去重操作，影響性能。如果只需要去重，可以使用 GROUP BY。

29. 使用 PARTITION PRUNING 優(yōu)化分區(qū)表查詢

例子：

SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

原因： PARTITION PRUNING 可以顯著提高分區(qū)表的查詢性能，因為它可以跳過不需要的分區(qū)。

四. 補充

1. 合理利用視圖（View）進行復(fù)雜查詢

正例：

CREATE VIEW view_user_orders AS
SELECT u.id, u.name, o.order_id, o.amount
FROM user u JOIN orders o ON u.id = o.user_id;-- 使用視圖查詢
SELECT * FROM view_user_orders WHERE amount > 100;

2. 使用表分區(qū)（Partitioning）優(yōu)化大表性能

正例：

CREATE TABLE sales (sale_id INT,sale_date DATE,amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);

3. 合理使用存儲過程（Stored Procedure）來減少多次 SQL 交互

正例：

CREATE PROCEDURE update_and_select(IN user_id INT)
BEGINUPDATE users SET last_login = NOW() WHERE id = user_id;SELECT * FROM users WHERE id = user_id;
END;

4. 使用臨時表（Temporary Tables）處理復(fù)雜查詢

正例：

CREATE TEMPORARY TABLE temp_users AS
SELECT id FROM users WHERE status = 'active';SELECT * FROM orders WHERE user_id IN (SELECT id FROM temp_users);

原因： 臨時表可以在處理復(fù)雜查詢時提高性能，特別是在多次使用相同子查詢結(jié)果的情況下。

5. 使用適當(dāng)?shù)母綦x級別

原因： 在高并發(fā)環(huán)境中選擇適當(dāng)?shù)氖聞?wù)隔離級別（如 READ COMMITTED），可以避免不必要的鎖競爭和阻塞，提升并發(fā)效率。

6. 避免在事務(wù)中執(zhí)行非必要的操作

原因： 在事務(wù)中應(yīng)避免執(zhí)行耗時操作，比如網(wǎng)絡(luò)請求或復(fù)雜計算，以減少鎖的持有時間。優(yōu)先確保事務(wù)操作集中在必要的數(shù)據(jù)變更上。

7. 使用批量更新或刪除

正例：

-- 分批刪除
DELETE FROM orders WHERE status = 'obsolete' LIMIT 1000;