5.盡量不用外鍵

線上OLTP系統盡量不用外鍵：

• 外鍵可節省開發量
• 有額外開銷
• 逐行操作
• 可「到達」其他表，意味著鎖
• 高並發時容易死鎖

建議由程序保證約束

比如我們原來建表語句是這樣的：

CREATE TABLE `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵,
`user_name` varchar(50) NOT NULL DEFAULT COMMENT 用戶名,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵,
`total_price` decimal(10,2) NOT NULL DEFAULT 0.00,
`user_id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `for_indx_user_id` (`user_id`),
CONSTRAINT `for_indx_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

不使用外鍵約束後：

CREATE TABLE `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵,
`user_name` varchar(50) NOT NULL DEFAULT COMMENT 用戶名,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵,
`total_price` decimal(10,2) NOT NULL DEFAULT 0.00,
`user_id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

不適用外鍵約束後，為了加快查詢我們通常會給不建立外鍵約束的欄位添加一個索引。

CREATE TABLE `order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵,
`total_price` decimal(10,2) NOT NULL DEFAULT 0.00,
`user_id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

實際開發中，一般不會建立外鍵約束。

四、SQL類原則

1.SQL語句儘可能簡單

在開發過程中，我們盡量要保持SQL語句的簡單性，我們對比一下大SQL和多個簡單SQL

• 傳統設計思想
• BUG MySQL NOT
• 一條SQL只能在一個CPU運算
• 5000+ QPS的高並發中，1秒大SQL意味著？
• 可能一條大SQL就把整個資料庫堵死

拒絕大SQL，拆解成多條簡單SQL

• 簡單SQL緩存命中率更高
• 減少鎖表時間，特別是MyISAM
• 用上多CPU

2.保持事務（連接）短小

• 事務/連接使用原則：即開即用，用完即關
• 與事務無關操作都放到事務外面，減少鎖資源的佔用
• 不破壞一致性前提下，使用多個短事務代替長事務

舉例：

1）發帖時的圖片上傳等待

2）大量的sleep連接

3.儘可能避免使用SP/TRIG/FUNC

線上OLTP系統中，我們應當：

• 儘可能少用存儲過程
• 儘可能少用觸發器
• 減少使用MySQL函數對結果進行處理

將上述這些事情都交給客戶端程序負責

4.盡量不用SELECT *

用SELECT * 時，將會更多的消耗CPU、內存、IO以及網路帶寬

我們在寫查詢語句時，應當盡量不用SELECT * ,只取需要的數據列：

• 更安全的設計：減少表變化帶來的影響
• 為使用covering index提供可能性
• Select/JOIN 減少硬碟臨時表生成，特別是有TEXT/BLOB時

舉例：

不推薦：

SELECT * FROM tag
WHERE id = 999148

推薦：

SELECT keyword FROM tag
WHERE id = 999148

5.改寫OR為IN()

同一欄位，將or改寫為in()

OR效率：O(n)

IN效率：O(Log n）

當n很大時，OR會慢很多

注意控制IN的個數，建議n小於200

舉例：

不推薦：

Select * from opp WHERE phone=12347856 or phone=42242233

推薦：

Select * from opp WHERE phone in (12347856 , 42242233)

6.改寫OR為UNION

不同欄位，將or改為union

• 減少對不同欄位進行 "or" 查詢
• Merge index往往很弱智
• 如果有足夠信心：set global optimizer_switch=index_merge=off;

舉例：

不推薦：

Select * from opp
WHERE phone=010-88886666
or
cellPhone=13800138000;

推薦：

Select * from opp
WHERE phone=010-88886666
union
Select * from opp
WHERE cellPhone=13800138000;

7.避免負向查詢和%前綴模糊查詢

在實際開發中，我們要盡量避免負向查詢，那什麼是負向查詢呢，主要有以下：

NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

同時，我們還要避免%前綴模糊查詢，因為這樣會使用B+ Tree，同時會造成使用不了索引，並且會導致全表掃描，性能和效率可想而知

舉例：

8.減少COUNT(*)

在開發中我們經常會使用COUNT(*)，殊不知這種用法會造成大量的資源浪費，因為COUNT(*)資源開銷大，所以我們能不用盡量少用

對於計數類統計，我們推薦：

• 實時統計：用memcache，雙向更新，凌晨跑基準
• 非實時統計：盡量用單獨統計表，定期重算

來對比一下COUNT(*)和其他幾個COUNT吧：

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT 公司的id,
`sale_id` int(10) unsigned DEFAULT NULL,

結論：

COUNT(*)=COUNT(1)

COUNT(0)=COUNT(1)

COUNT(1)=COUNT(100)

COUNT(*)!=COUNT(col)

9.LIMIT高效分頁

傳統分頁：

Select * from table limit 10000,10;

LIMIT原理：

• Limit 10000,10
• 偏移量越大則越慢

推薦分頁：

Select * from table WHERE id>=23423 limit 11;
#10+1 (每頁10條)
select * from table WHERE id>=23434 limit 11;

分頁方式二：

Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;

分頁方式三：

SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id) ;

分頁方式四：

#先使用程序獲取ID：
select id from table limit 10000,10;
#再用in獲取ID對應的記錄
Select * from table WHERE id in (123,456…) ;

具體需要根據實際的場景分析並重組索引

示例：

10.用UNION ALL 而非UNION

如果無需對結果進行去重，僅僅是對多表進行聯合查詢並展示，則用UNION ALL，因為UNION有去重開銷

舉例：

MySQL>SELECT * FROM detail20091128 UNION ALL
SELECT * FROM detail20110427 UNION ALL
SELECT * FROM detail20110426 UNION ALL
SELECT * FROM detail20110425 UNION ALL
SELECT * FROM detail20110424 UNION ALL
SELECT * FROM detail20110423;

11.分解聯接保證高並發

高並發DB不建議進行兩個表以上的JOIN

適當分解聯接保證高並發：

• 可緩存大量早期數據
• 使用了多個MyISAM表
• 對大表的小ID IN()
• 聯接引用同一個表多次

舉例：

原SQL：

MySQL> Select * from tag
JOIN tag_post
on tag_post.tag_id=tag.id
JOIN post
on tag_post.post_id=post.id
WHERE tag.tag=『二手玩具』;

分解SQL：

MySQL> Select * from tag WHERE tag=『二手玩具』;
MySQL> Select * from tag_post WHERE tag_id=1321;
MySQL> Select * from post WHERE post.id in (123,456,314,141)

12.GROUP BY 去除排序

使用GROUP BY可以實現分組和自動排序

無需排序：Order by NULL

特定排序：Group by DESC/ASC

舉例：

13.同數據類型的列值比較

原則：數字對數字，字元對字元

數值列與字元類型比較：同時轉換為雙精度進行比對

字元列與數值類型比較：字元列整列轉數值，不會使用索引查詢

舉例：

欄位：`remark` varchar(50) NOT NULL COMMENT 備註,默認為空,

MySQL>SELECT `id`, `gift_code` FROM gift
WHERE `deal_id` = 640 AND remark=115127;
1 row in set (0.14 sec)

MySQL>SELECT `id`, `gift_code` FROM pool_gift
WHERE `deal_id` = 640 AND remark=115127;
1 row in set (0.005 sec)

14.Load data 導數據

批量數據快導入：

• 成批裝載比單行裝載更快，不需要每次刷新緩存
• 無索引時裝載比索引裝載更快
• Insert values ,values，values 減少索引刷新
• Load data比insert快約20倍

盡量不用INSERT ... SELECT，一個是有延遲，另外就是會同步出錯

15.打散大批量更新

• 大批量更新盡量凌晨操作，避開高峰
• 凌晨不限制
• 白天上線默認為100條/秒（特殊再議）

舉例：

update post set tag=1 WHERE id in (1,2,3);
sleep 0.01;
update post set tag=1 WHERE id in (4,5,6);
sleep 0.01;
……

16.Know Every SQL

作為DBA乃至資料庫開發人員，我們必須對資料庫的每條SQL都非常了解，常見的命令有：

• SHOW PROFILE
• MYSQLsla
• MySQLdumpslow
• explain
• Show Slow Log
• Show Processlist
• SHOW QUERY_RESPONSE_TIME(Percona）

五、約定類原則

1.隔離線上線下

構建資料庫的生態環境，確保開發無線上庫操作許可權

原則：線上連線上，線下連線下

• 生產數據用pro庫
• 預生產環境用pre庫
• 測試用test庫
• 開發用dev庫

2.禁止未經DBA確認的子查詢

• 大部分情況優化較差
• 特別WHERE中使用IN id的子查詢
• 一般可用JOIN改寫

舉例：

MySQL> select * from table1 where id in (select id from table2);
MySQL> insert into table1 (select * from table2); //可能導致複製異常

3.永遠不在程序端顯式加鎖

• 外部鎖對資料庫丌可控
• 高幵發時是災難
• 極難調試和排查

對於類似並發扣款等一致性問題，我們採用事務來處理，Commit前進行二次校驗衝突

4.統一字符集為UTF8

5.統一命名規範

1）庫表等名稱統一用小寫

2）索引命名默認為「idx_欄位名"

3）庫名用縮寫，盡量在2~7個字母

DataSharing ==> ds

4）注意避免用保留字命名

以上所有坑，建議資料庫開發人員都要銘記於心。

更多精彩內容，請滑至頂部點擊右上角關注小宅哦~

---

來源：華為雲社區原創 作者：真愛無敵

推薦閱讀：