MySQL/TDSQL 各個(gè)規(guī)格能支持的tps/qps
該結(jié)果是使用TPCC-C性能測(cè)試工具測(cè)出來(lái)的, 具體可能會(huì)和實(shí)際的有縮差別. 最好新業(yè)務(wù)上線進(jìn)行壓測(cè).
MySQL的一個(gè)查詢只能使用到1個(gè)cpu, 所以, 要想數(shù)據(jù)庫(kù)性能達(dá)到比較優(yōu)情況, 則cpu核數(shù)并發(fā)查詢數(shù)有關(guān)(表現(xiàn)到數(shù)據(jù)庫(kù)層面是活躍回話數(shù) thread running和cpu的關(guān)系)
一般來(lái)說(shuō)計(jì)算公式為: cpu核數(shù) * 2 = 活躍會(huì)話數(shù)
一般情況下數(shù)據(jù)庫(kù)的cpu保持在 15%~20%是相對(duì)安全的, 不能把數(shù)據(jù)庫(kù)cpu打的太滿, 如果打的太滿的話, 在故障切換會(huì)有雪崩的情況。
當(dāng)數(shù)據(jù)庫(kù)掛掉, 切換, 這時(shí)候切換的目標(biāo)實(shí)例會(huì)瞬時(shí)承擔(dān)流量, 會(huì)有大量建立鏈接和數(shù)據(jù)庫(kù)預(yù)熱過(guò)程, 這時(shí)候數(shù)據(jù)導(dǎo)致數(shù)據(jù)庫(kù)壓力瞬間變大
也有可能目標(biāo)庫(kù)就承載了一些流量
表大小DDL時(shí)間
注意:
如果執(zhí)行DDL的表的tps > 600的時(shí)候DDL會(huì)停止不動(dòng)(這和DDL原理有關(guān)系)
DDL速度和每行數(shù)據(jù)大小有關(guān), 如果每行的數(shù)據(jù)比較大, DDL數(shù)據(jù)也會(huì)有所下降
一個(gè)實(shí)例的DDL只能串行, 如: 一個(gè)實(shí)例中有10張表需要做DDL, 沒(méi)張表數(shù)據(jù)量在1000w, 要完成10張表的變更需要10小時(shí)
分庫(kù)分表
- 單表行數(shù)超過(guò) 500 萬(wàn)行或者單表容量超過(guò) 2GB网沾，才推薦進(jìn)行分庫(kù)分表
如果預(yù)計(jì)2年后的數(shù)據(jù)量根本達(dá)不到這個(gè)級(jí)別，請(qǐng)不要在創(chuàng)建表時(shí)就分庫(kù)分表
- 不單單只分庫(kù), 還需要做到分表
主從復(fù)制是表級(jí)別的, 如果拆分到表, 能提高主從復(fù)制性能
如果只分庫(kù), 會(huì)導(dǎo)致數(shù)據(jù)庫(kù)的數(shù)量增大, 從而導(dǎo)致不同對(duì)實(shí)例的連接數(shù)會(huì)成倍數(shù)增加, 最終導(dǎo)致鏈接數(shù)用滿.
每行數(shù)據(jù)大小最好不要超過(guò)1k, 讓一個(gè)數(shù)據(jù)頁(yè)中存的行數(shù)越多越好
MySQL從磁盤(pán)獲取數(shù)據(jù)是一頁(yè)一頁(yè)獲取的, 如果一頁(yè)中存的數(shù)據(jù)行數(shù)越多, 范圍掃描的速度就越快
數(shù)據(jù)庫(kù)發(fā)布
數(shù)據(jù)庫(kù)發(fā)布 和 業(yè)務(wù)應(yīng)用發(fā)布需要區(qū)分開(kāi). 數(shù)據(jù)庫(kù)發(fā)布是有狀態(tài)的, 不確定性高. 而且發(fā)布不是100%都能成功的, 特別對(duì)于大表, 和tps高的表, 數(shù)據(jù)庫(kù)發(fā)布失敗率很大, 而且會(huì)導(dǎo)致主從延時(shí).
數(shù)據(jù)庫(kù)發(fā)布至少提前一個(gè)發(fā)布周期進(jìn)行, 如: 周四要發(fā)布應(yīng)用上線, 在周二就需要將數(shù)據(jù)庫(kù)發(fā)布執(zhí)行了, 這樣數(shù)據(jù)庫(kù)發(fā)布如果有問(wèn)題, 可以提前發(fā)現(xiàn). 并且給應(yīng)用發(fā)布提供足夠的buffer
容量使用規(guī)范
僅供參考
| 項(xiàng) | 健康上線值(不能操過(guò)這個(gè)值) |
|---|---|
| 機(jī)器CPU | 20% |
| 機(jī)器寫(xiě)入iops | 1.5w, 600 |
| 機(jī)器io使用率 | 20% |
| 數(shù)據(jù)庫(kù)活躍回話數(shù) | 50 |
DDL規(guī)范
- 使用ALTER添加字段不能使用AFTER關(guān)鍵字, 默認(rèn)字段都在表的最后, 并且對(duì)于同一個(gè)表的DDL操作放在一個(gè)語(yǔ)句中
- 反例
-- 使用了 after
ALTER TABLE yh_member
ADD name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名' AFTER id;
-- 同一個(gè)表DDL使用多條SQL
ALTER TABLE yh_user
ADD name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名';
ALTER TABLE yh_user
ADD is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '是否刪除: 0:否, 1:是';
ALTER TABLE yh_user
ADD INDEX idx_created_at(created_at);
- 正例
-- 不能使用AFTER
ALTER TABLE tbl_xxx
ADD name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名';
-- 對(duì)同一個(gè)表的DDL合并成一條SQL
ALTER TABLE yh_user
ADD name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
ADD is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '是否刪除: 0:否, 1:是',
ADD INDEX idx_created_at(created_at);
- 庫(kù), 表字符集必須utf8mb4
在MySQL中只有utf8mb4才是真正的UTF-8, 只有utf8mb4才能支持類似emoji等4字節(jié)字符
- 字段允許適當(dāng)冗余，以提高查詢性能，但必須考慮數(shù)據(jù)一致肉迫。冗余字段應(yīng)遵循:
不是頻繁修改的字段
不是 varchar 超長(zhǎng)字段夕晓，更不能是 text 字段
- 每個(gè)表必須要有id字段為主鍵, 在沒(méi)有特殊要求的情況下id是bigint類型, 禁止使用int
- 方便對(duì)接外部系統(tǒng)凶硅，還有可能產(chǎn)生很多廢數(shù)據(jù)
- 避免廢棄數(shù)據(jù)對(duì)系統(tǒng)id的影響
? 1. 如果預(yù)期內(nèi)表的數(shù)據(jù)量很大, 并且會(huì)有拆分的可能性id的值最好讓程序生成. 不要使用MySQL中的自增
? 2.程序生成的id保證相對(duì)的自增, 這樣能保證, 數(shù)據(jù)庫(kù)的索引數(shù)據(jù)總在一部分是相對(duì)的熱, 這塊數(shù)據(jù)能駐留在內(nèi)存, 查詢就不需要再回磁盤(pán)
每個(gè)表必須要有is_deleted字段標(biāo)記數(shù)據(jù)是否已經(jīng)刪除, 并且該字段需要有索引, 方便后期的一些維護(hù)性操作. is_deleted兩個(gè)選項(xiàng): 0:否, 1:是
每個(gè)表必須要有created_at和updated_at字段, 并且字段類型為DATETIME, 且值的生成應(yīng)該讓數(shù)據(jù)庫(kù)自己生成, 業(yè)務(wù)程序不應(yīng)該主動(dòng)修改這兩個(gè)字段的值. 如:
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
TIMESTAMP可以表達(dá)1970-2038年厢破，而且TIMESTAMP需要4字節(jié)存儲(chǔ)空間荣瑟，而DATETIME需要5字節(jié)，存儲(chǔ)1001-9999年 TIMESTAMP默認(rèn)值, 會(huì)隨著sql_mode參數(shù)不一樣, 表現(xiàn)不同, 對(duì)程序的兼容性要求高.
- 時(shí)間字段格式統(tǒng)一存儲(chǔ)成YYYY-MM-DD HH:MM:SS
- 表和字段必須要有清晰明確的注釋, 為了后期更好的維護(hù). 如:
CREATE TABLE user(
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增id',
name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '用戶名稱',
.....
) COMMENT='用戶表';
- 字段盡量設(shè)置為 NOT NULL, 為字段提供默認(rèn)值. 如:
- 字符型的默認(rèn)值為一個(gè)空字符值串
name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '用戶名稱'
- 數(shù)值型默認(rèn)值為數(shù)值 0
cnt DECIMAL(8, 2) NOT NULL DEFAULT 0 COMMENT '數(shù)量'
- 邏輯型的默認(rèn)值為數(shù)值 0
is_deleted TINYINT NOT NULL DEFAULT '' COMMENT '是否刪除: 0:否, 1:是'
NULL需要更多的存儲(chǔ)空摩泪，無(wú)論是表還是索引中每行中的NULL的列都需要額外的空間來(lái)標(biāo)識(shí)笆焰。NULL這種類型需要MySQL內(nèi)部進(jìn)行特殊處理，增加了數(shù)據(jù)庫(kù)處理記錄的復(fù)雜性见坑，同等條件下嚷掠，表中較多NULL字段會(huì)導(dǎo)致數(shù)據(jù)庫(kù)處理性能下降.
- 小數(shù)類型為decimal，禁止使用 float 和 double
float 和 double 在存儲(chǔ)的時(shí)候荞驴，存在精度損失的問(wèn)題不皆，很可能在值的比較時(shí)，得不到正確的結(jié)果熊楼。
- 用盡量少的存儲(chǔ)空間來(lái)存數(shù)一個(gè)字段的數(shù)據(jù), 不要使用char類型, 杜絕和核心出現(xiàn)大字段定義如: VARCHAR(2000), TEXT
- 能使用 tinyint/int/bigint的就不要使用 varchar
- 能使用 varchar(20) 的就不要 varchar(255)
- 能使用varchar(255)就不要使用varchar(256)
- 如果一個(gè)表有varchar(2000)需要把varchar(2000)變成text, 并且獨(dú)立出另外一張表出來(lái)
- varchar(1)~varcahr(255)需而外1字節(jié)的存儲(chǔ)位,
- varchar(256)~varchar(65535)需要額外2字節(jié)存儲(chǔ)位
- 表的一行的數(shù)據(jù)定義大小不能超過(guò)16382個(gè)字符(varchar((65535-4)/4=16382)), text字段數(shù)據(jù)存儲(chǔ)方式, 小部分存儲(chǔ)在原表中, 大部分?jǐn)?shù)據(jù)以指針的方式存儲(chǔ)在另外空間.
- 合理將varchar或text作為json字符串使用, 并且json字符串是相對(duì)可解析的. 這樣的字段不能出現(xiàn)在核心表中, 需要而外剝離開(kāi).
- 不要使用'null'字符串去表示空值
- 對(duì)于自動(dòng)生成的schema不要太過(guò)信任霹娄，最好自己手動(dòng)寫(xiě)
自動(dòng)生成的schema, 會(huì)帶很為而外的信息, 如: 字符集, after 等等
- 在設(shè)計(jì)階段, 核心業(yè)務(wù)表所在的庫(kù), 需要和非核心表所在庫(kù)需要區(qū)分開(kāi)
- 需要做分庫(kù)分表的, 在設(shè)計(jì)階段就需要考慮到分布不均到問(wèn)題, 盡量要讓數(shù)據(jù)分布均勻.
命名規(guī)范
- 庫(kù), 表, 字段 命名需要 需見(jiàn)名知意, 并且需要有長(zhǎng)度限制
- 庫(kù)名: 不能超過(guò)15個(gè)字符
- 表名: 不能超過(guò)30個(gè)字符
- 字段:不能超過(guò)30個(gè)字符
- 表名、字段名必須使用小寫(xiě)字母或數(shù)字鲫骗，禁止出現(xiàn)數(shù)字開(kāi)頭犬耻，禁止兩個(gè)下劃線中間只 出現(xiàn)數(shù)字。數(shù)據(jù)庫(kù)字段名的修改代價(jià)很大执泰，因?yàn)闊o(wú)法進(jìn)行預(yù)發(fā)布枕磁，所以字段名稱需要慎重考慮。 因此坦胶，數(shù)據(jù)庫(kù)名透典、表名、字段名顿苇，都不允許出現(xiàn)任何大寫(xiě)字母峭咒，避免節(jié)外生枝
- 正例:health_user, rdc_config, level3_name
- 反例:HealthUser, rdcConfig, level_3_name
- 表名不使用復(fù)數(shù)名詞
- 正例: rdc_config, user_class, candy
- 反例: rdc_configs, user_classes, candies
說(shuō)明:表名應(yīng)該僅僅表示表里面的實(shí)體內(nèi)容，不應(yīng)該表示實(shí)體數(shù)量纪岁，對(duì)應(yīng)于DO類名也是單數(shù)形式凑队，符合表達(dá)習(xí)慣。
- 庫(kù)名與應(yīng)用名稱盡量一致幔翰。如: health
- 表的命名最好是加上業(yè)務(wù)名稱_表的作用
正例: health_user, trade_config - 禁用保留字漩氨，如:desc、range遗增、match叫惊、delayed 等，請(qǐng)參考 MySQL 官方保留字
MySQL關(guān)鍵字官網(wǎng): https://dev.mysql.com/doc/refman/8.0/en/keywords.html
- 索引命名規(guī)則:
- 主鍵: 使用默認(rèn)的名字PRIMARY KEY就行
- 唯一索引: uk_字段名
- 普通索引: idx_字段名
e.g:
CREATE TABLE `emp` (
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增id',
`emp_no` int NOT NULL COMMENT '員工編號(hào)',
`first_name` varchar(14) NOT NULL COMMENT '第一名',
...
PRIMARY KEY (`id`),
UNIQUE INDEX uk_emp_no(`emp_no`),
INDEX idx_first_name(`first_name`)
) COMMENT='員工表';
說(shuō)明: uk_ 即 unique key; idx_ 即 index 的簡(jiǎn)稱
- 所有命名必須使用全名做修，有默認(rèn)約定的除外霍狰，如果超過(guò) 30 個(gè)字符抡草，使用縮寫(xiě)，請(qǐng)盡量名字易懂簡(jiǎn)短蔗坯，如:information -> info; address -> addr; connection -> conn 等
- 表達(dá)是與否概念的字段康震，必須使用 is_xxx 的方式命名，數(shù)據(jù)類型是 tinyint ( 1表示是宾濒，0表示否)
- 正例: 表達(dá)邏輯刪除的字段名 is_deleted, 1表示刪除腿短，0表示未刪除
10 .如果修改字段含義或?qū)ψ侄伪硎镜臓顟B(tài)追加時(shí)，需要及時(shí)更新字段注釋
索引規(guī)范
- 索引個(gè)數(shù)最好不要超過(guò)6個(gè)
- 組合索引字段最好不好超過(guò)3個(gè), 讀多的情況下如果3個(gè)字段索引能唯一定位一條數(shù)據(jù), 就可以創(chuàng)建唯一索引
不要以為唯一索引影響了insert速度绘梦，這個(gè)速度損耗可以忽略橘忱，但提高查找速度是明顯的; 另外，即使在應(yīng)用層做了非常完善的校驗(yàn)控制谚咬，只要沒(méi)有唯一索引鹦付，根據(jù)墨菲定律，必 然有臟數(shù)據(jù)產(chǎn)生
- 超過(guò)三個(gè)表禁止 join. 需要 join 的字段, 數(shù)據(jù)類型必須絕對(duì)一致. 多表關(guān)聯(lián)查詢時(shí), 保證被關(guān)聯(lián)的字段需要有索引. 如:
SELECT *
FROM tbl_a AS a
LEFT JOIN tbl_b AS b
ON a.member_id = b.member_id
WHERE a.name = 'xxx'
上面查詢語(yǔ)句中關(guān)聯(lián)條件ON a.member_id = b.member_id, 其中b.member_id需要有索引.
- 組合索引區(qū)分度高的放在最左邊區(qū)分度最高的放左邊择卦，能夠在一開(kāi)始過(guò)濾掉很多無(wú)用數(shù)據(jù)，提高索引的效率郎嫁。需要注意的是各個(gè)條件的順序盡量和索引的順序一致, 如下示例:
CREATE TABLE rdc_config (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增id',
rule_name VARCHAR(50) NOT NULL COMMENT '規(guī)則名稱',
wid BIGINT NOT NULL COMMENT '倉(cāng)庫(kù)id',
goods_category_id BIGINT NOT NULL COMMENT '商品類別id'
...
PRIMARY KEY(id),
...
) COMMENT='物流中心配置表'
- 假設(shè)rdc_config表有1000w數(shù)據(jù)
- 字段rule_name的區(qū)分度是300w
- 字段wid的區(qū)分度為20w
- 字段goods_category_id 的區(qū)分度為50w
查看字段區(qū)分度方法: SELECT COUNT(DISTINCT rule_name)
錯(cuò)誤索引
idx_rule_name_wid_gcid(rule_name, wid, goods_category_id)
idx_rule_wid_name_gcid(wid, rule_name, goods_category_id)
idx_rule_gcid_wid_name(goods_category_id, wid, rule_name)
正確索引
idx_rule_name_gcid_wid(rule_name, goods_category_id, wid)
不能使用外鍵約束
created_at,updated_at 字段必須有索引(為了避免索引歸檔還需要再次做DDL添加索引)
在varchar字段建索引時(shí), 盡可能的指定長(zhǎng)度, 沒(méi)必要對(duì)全字段都建立索引, 根據(jù)實(shí)際的前綴的區(qū)分度長(zhǎng)度建立索引即可
索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體, 一般對(duì)字符串類型數(shù)據(jù), 長(zhǎng)度為20的索引, 區(qū)分度會(huì)高達(dá)90%以上, 可以使用 count(distinct left(列名, 索引長(zhǎng)度))/count(*)的區(qū)分度來(lái)確定
例子:
-- 表結(jié)構(gòu)
CREATE TABLE rdc_config (
...
rule_name VARCHAR(500) NOT NULL DEFAULT '' COMMENT '規(guī)則名稱',
...
) COMMENT='物流中心配置表'
創(chuàng)建索引
ALTER TABLE rdc_config ADD INDEX idx_rule_name(rule_name(20))
查詢區(qū)分度
SELECT COUNT(DISTINCT LEFT(rule_name, 20)) / COUNT(*) FROM rdc_config
- 如果有order by的場(chǎng)景, 請(qǐng)注意利用索引的有序性. order by 最后的字段是組合索引的一部分, 并且放在索引組合順序的最后, 避免出現(xiàn) file_sort 的情況, 影響查詢性能.
錯(cuò)誤示例:
索引中有范圍查找, 則索引有序性無(wú)法利用
-- WHERE 條件
WHERE a>10 ORDER BY b
-- 創(chuàng)建索引(排序無(wú)法使用到索引)
INDEX idx_xxx(a, b)
正確示例:
-- WHERE條件
WHERE a=? AND b=? ORDER BY c
-- 需要?jiǎng)?chuàng)建的索引
INDEX idx_xxx(a, b, c)
- 利用覆蓋索引來(lái)進(jìn)行查詢操作, 避免回表.
在只需要獲取少數(shù)量字段的數(shù)據(jù)時(shí), 可以考慮使用索引覆蓋.
能夠建立索引的種類: 主鍵索引, 唯一索引, 普通索引, 而覆蓋索引是一種查詢的效果秉继，用explain的結(jié)果，extra列會(huì)出現(xiàn):using index
- 利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁(yè)場(chǎng)景
錯(cuò)誤示例:
SELECT * FROM tbl LIMIT 0, 1000;
SELECT * FROM tbl LIMIT 1000, 1000;
...
SELECT * FROM tbl LIMIT 10000000, 1000;
正確示例:
-- 示例1 (建議)
SELECT * FROM tbl WHERE id > 0 LIMIT 1000;
SELECT * FROM tbl WHERE id > 1000 LIMIT 1000;
...
SELECT * FROM tbl WHERE id > 1000000 LIMIT 1000;
-- 示例2 (性能沒(méi)1好)
SELECT a.*
FROM tbl AS a, (
SELECT id
FROM tbl
WHERE col_01 = 'xxx'
LIMIT 100000, 20
) AS b
WHERE a.id = b.id
MySQL并不是跳過(guò) offset 行泽铛，而是取 offset+N 行尚辑，然后返回放棄前 offset 行，返回 N 行盔腔，那當(dāng) offset 特別大的時(shí)候杠茬，效率就非常的低下，要么控制返回的總頁(yè)數(shù)弛随，要么對(duì)超過(guò)特定閾值的頁(yè)數(shù)進(jìn)行 SQL 改寫(xiě)
- SQL性能優(yōu)化的目標(biāo): 至少要達(dá)到range級(jí)別, 要求是ref級(jí)別, 如果可以是const最好.
EXPLAIN SELECT * FROM tbl WHERE c='xxx';
----+-------------+-------+-------+- ...
| id | select_type | table | type | ...
+----+-------------+-------+-------+ ...
| 1 | SIMPLE | S | range | ...
+----+-------------+-------+-------+ ...
- const: 單表中最多只有一個(gè)匹配行(主鍵或者唯一索引), 在優(yōu)化階段即可讀取到數(shù)據(jù)
- ref: 指的是使用普通的索引(normal index)
- range: 對(duì)索引進(jìn)行范圍檢索
- index: 全索引掃描
- all: 全表掃描
性能順序(好->差): const -> ref -> range -> index -> all
- 防止因字段類型不同造成的隱式轉(zhuǎn)換, 導(dǎo)致索引失效.
如: 字段code類型為:VARCHAR(20), 字段值為: '1239909238098'
錯(cuò)誤示例: WHERE code = 1239909238098
正確示例: WHERE code = '1239909238098'
sql使用規(guī)范
- 不要使用 count(列名)或 count(常量)來(lái)替代 count(), count()是SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)法, 跟數(shù)據(jù)庫(kù)無(wú)關(guān), 跟NULL和非NULL無(wú)關(guān)
錯(cuò)誤示例:
SELECT COUNT(name) FROM tbl;
SELECT COUNT(1) FROM tbl;
正確示例:
SELECT COUNT(*) FROM tbl;
count(*)會(huì)統(tǒng)計(jì)包括值為NULL的行, 而count(name), count(1)不會(huì)統(tǒng)計(jì)該列為NULL值的行瓢喉。 也可以變通的實(shí)現(xiàn)count效果，即查詢數(shù)據(jù)到應(yīng)用系統(tǒng)中舀透，然后計(jì)算count.
- 在使用聚合函數(shù)的時(shí)候, 需要注意NULL值. 當(dāng)字段值有NULL聚合函數(shù)的值也是NULL.
假設(shè)字段 money 字段中有NULL值
錯(cuò)誤示例:
SELECT SUM(money), AVG(money) FROM tbl;
+------------+------------+
| SUM(money) | AVG(money) |
+------------+------------+
| NULL | NULL |
+------------+------------+
1 row in set (0.03 sec)
建議示例:
SELECT IF(ISNULL(SUM(money)), 0, SUM(money)),
IF(ISNULL(AVG(money)), 0, AVG(money))
FROM tbl;
+---------------------------------------+---------------------------------------+
| IF(ISNULL(SUM(money)), 0, SUM(money)) | IF(ISNULL(AVG(money)), 0, AVG(money)) |
+---------------------------------------+---------------------------------------+
| 1 | 1.0000 |
+---------------------------------------+---------------------------------------+
1 row in set (0.02 sec)
MySQL 8.0不存在這種情況
- 使用IS NULL來(lái)判斷值為NULL, 使用IS NOT NULL來(lái)判斷值不為NULL
錯(cuò)誤示例:
SELECT * FROM tbl WHERE name = NULL;
SELECT * FROM tbl WHERE name <> NULL;
正確示例:
SELECT * FROM tbl WHERE name IS NULL;
SELECT * FROM tbl WHERE name IS NOT NULL;
- NULL <> NULL的返回結(jié)果是NULL栓票，而不是false。
- NULL = NULL的返回結(jié)果是NULL愕够，而不是true走贪。
- NULL <> 1的返回結(jié)果是NULL，而不是true惑芭。
- 程序鏈接默認(rèn)使用auto_commit=1, 并且單獨(dú)select查詢不需要開(kāi)啟事物
錯(cuò)誤示例:
BEGIN;SELECT * FROM tbl;COMMIT;
正確示例:
鏈接打開(kāi) auto_commit=1;
SELECT * FROM tbl;
- 拒絕使用大事務(wù), 大批量.
錯(cuò)誤示例:
-- 大批量insert
INSERT INTO tbl VALUES(), (), (), ... ();
-- 沒(méi)有索引全表update
UPDATE tbl set age = 10;
正確示例:
-- 批量拆分成單條
INSERT INTO tbl VALUES();
INSERT INTO tbl VALUES();
INSERT INTO tbl VALUES();
...
INSERT INTO tbl VALUES();
-- 通過(guò)區(qū)分度高的 索引/主鍵更新數(shù)據(jù)
UPDATE tbl set age = 10 WHERE id = 1;
1.大事務(wù), 大批量, 會(huì)有長(zhǎng)時(shí)間的鎖
2.容易出現(xiàn)主從延時(shí)
3.sql盡可能簡(jiǎn)單(一條sql只能在一個(gè)cpu運(yùn)算,大語(yǔ)句拆小語(yǔ)句,減少鎖時(shí)間,一條大sql可以堵死整個(gè)庫(kù))
6.條件過(guò)濾字段不允許使用函數(shù)包裹
錯(cuò)誤示例:
SELECT * FROM tbl WHERE SUBSTRING(created_at, 9) >= '2021-11-11';
正確示例:
SELECT * FROM tbl WHERE created_at >= '2021-11-11 00:00:00'
MySQL不支持函數(shù)索引, 使用函數(shù), 索引就失效了
- 頁(yè)面搜索嚴(yán)禁左模糊或者全模糊坠狡，如果需要請(qǐng)走搜索引擎來(lái)解決
錯(cuò)誤示例:
SELECT * FROM tbl WHERE name LIKE '%xxx%';
SELECT * FROM tbl WHERE name LIKE '%xxx';
SELECT * FROM tbl WHERE name REGEXP '^[aeiou]|ok$';
正確示例
在非要使用模糊匹配的情況下, 需要使用前綴模糊匹配
SELECT * FROM tbl WHERE name LIKE 'xxx%';
索引文件具有 B-Tree 的最左前綴匹配特性, 如果左邊的值未確定, 那么無(wú)法使用此索引
- 不允許使用自定義方法和觸發(fā)器
1.觸發(fā)器和自定義方法, 不可控, 難以維護(hù). 相關(guān)業(yè)務(wù)邏輯應(yīng)該通過(guò)應(yīng)用代碼來(lái)解決. 不應(yīng)該使用自定義方法和觸發(fā)器來(lái)解決
2.自定義方法和觸發(fā)器不主從復(fù)制. 當(dāng)主從切換容易帶來(lái)災(zāi)難性事故
- 不使用 select * (消耗cpu, io, 內(nèi)存, 帶寬, 這種程序不具有擴(kuò)展性)
錯(cuò)誤用法:
SELECT * FORM tbl WHERE id = 1;
正確用法:
SELECT id, name, age FROM tbl WHERE id = 1
- 條件不應(yīng)該使用OR, OR使用不到索引
- OR 改寫(xiě)成IN. (OR的效率是n級(jí)別，IN的效率是log(n)級(jí)別) 如:
錯(cuò)誤示例:
SELECT id FROM t WHERE id = 1 OR id = 2 OR id = 3
正確示例:
SELECT id FROM t WHERE id IN(1, 2, 3);
IN里面最好不要超過(guò)50個(gè)
- OR 改寫(xiě)成 UNION ALL(為了更好的使用索引), 如:
錯(cuò)誤示例:
SELECT id FROM t WHERE phone = '159' OR name = 'john';
正確示例:
SELECT id FROM t WHERE phone = '159'
UNION ALL
SELECT id FROM t WHERE name = 'john'
1.UNION ALL最好不要超過(guò)5個(gè)
2.不要使用UNION, UNION ALL 不會(huì)去重復(fù)
- sql 索引掃描行數(shù)不能超過(guò)200(explain 掃描行數(shù)不能超過(guò)200)
范圍掃描物理頁(yè)數(shù)盡量不要超過(guò)10個(gè)頁(yè), MySQL1個(gè)頁(yè)16kb,其中只有15k用于存儲(chǔ)數(shù)據(jù), 假設(shè)沒(méi)行數(shù)據(jù)大小800B, 則10個(gè)頁(yè)大概就是200行數(shù)據(jù)
一次性獲取數(shù)據(jù), 盡量不要超過(guò)20條記錄(最好在數(shù)據(jù)庫(kù)掃描一個(gè)page就能返回所有需要的數(shù)據(jù))
UPDATE, REPLACE INTO, INSERT IGNORE INTO 不能同時(shí)并發(fā)操作同一行記錄, 更新需要打散.
同時(shí)并發(fā)更新同一行數(shù)據(jù)會(huì)導(dǎo)致鎖等待增加, 導(dǎo)致鏈接hang住, 從而導(dǎo)致應(yīng)用鏈接用盡, 觸發(fā)瞬間創(chuàng)建大量鏈接, 導(dǎo)致MySQL cpu瞬間增高, 導(dǎo)致MySQL活躍會(huì)話數(shù)增高, 導(dǎo)致MySQL處理能力下降.
- 不得使用外鍵與級(jí)聯(lián)遂跟，一切外鍵概念必須在應(yīng)用層解決
1.以學(xué)生和成績(jī)的關(guān)系為例, 學(xué)生表中的 student_id 是主鍵, 那么成績(jī)表中的 student_id 則為外鍵逃沿。如果更新學(xué)生表中的 student_id, 同時(shí)觸發(fā)成績(jī)表中的 student_id 更新, 即為級(jí)聯(lián)更新
2.外鍵與級(jí)聯(lián)更新適用于單機(jī)低并發(fā), 不適合分布式, 高并發(fā)集群; 級(jí)聯(lián)更新是強(qiáng)阻塞, 存在數(shù)據(jù)庫(kù)更新風(fēng)暴的風(fēng)險(xiǎn); 外鍵影響數(shù)據(jù)庫(kù)的插入速度
- 修正數(shù)據(jù)必須明確修正的數(shù)據(jù)并且需要明確的知道影響行數(shù), 避免一次性修正多行
1.必須先將數(shù)據(jù)SELECT出來(lái), 再通過(guò)主鍵/唯一鍵進(jìn)行修正
2.修正的值必須是明確的, 不能在修正的字段上進(jìn)行計(jì)算, 修正sql是冪等的
錯(cuò)誤示例:
-- 一次性批量修正
UPDATE tbl SET money = 1 WHERE created_at >= '2022-01-01 00:00:00'
-- 修正數(shù)據(jù)不能冪等操作
UPDATE tbl SET money = money + 1 WHERE id = 1
正確示例:
-- 先select, 在update, 并且使用冪等操作
SELECT id FROM tbl WHERE created_at >= '2022-01-01 00:00:00';
UPDATE tbl SET money = 1 WHERE id = 1;
UPDATE tbl SET money = 1 WHERE id = 2;
...
UPDATE tbl SET money = 1 WHERE id = 100;
- 如果需要計(jì)算字符串的字符長(zhǎng)度使用CHARACTER_LENGTH而不是LENGTH
SELECT LENGTH('中國(guó)');
+------------------+
| LENGTH('中國(guó)') |
+------------------+
| 6 |
+------------------+
1 row in set (0.01 sec)
SELECT CHARACTER_LENGTH('中國(guó)');
+----------------------------+
| CHARACTER_LENGTH('中國(guó)') |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.00 sec)
17.大表中清空表不能使用DELETE語(yǔ)句, 應(yīng)該聯(lián)系DBA在非高峰期進(jìn)行操作.
以上是公司中多個(gè)dba根據(jù)多年經(jīng)驗(yàn)總結(jié)而成婴渡，本人將與公司有關(guān)的敏感信息去掉后，將公共的部分共享出來(lái)感挥，希望大家的系統(tǒng)越來(lái)越穩(wěn)定缩搅。
- sql語(yǔ)句中 order by id 會(huì)造成全表掃描,可以通過(guò)order by (id + 0) 讓id列參與計(jì)算,失效索引,這樣就會(huì)讓優(yōu)化器找到適合的索引.
番外：
表數(shù)據(jù)量太大造成的問(wèn)題
1. ddl成本高,主從復(fù)制也很困難
2. 查詢需要走索引
大表解決方案:
1. 歸檔
2. 盡量減少表的使用
