MySQL 開發準則

命名規範

【強制】對象名稱必須用小寫或者小寫、下劃線、數字組成。

​

​name;user_name;​

​

【強制】對象名稱禁止使用 MySQL 保留關鍵字。

如 ​

​ORDER​

​ 等

【強制】對象名稱要見名知其意，不要超過 32 個字元。

​

​nick_name​

​​,​

​student_age​

​​,​

​create_time​

​

【強制】臨時表要以 tmp 為字首，日期為字尾。

​

​tmp_export_user_20200721​

​

【強制】備份表要以 bak 為字首，日期為字尾。

​

​bak_user_20200721​

​

【強制】表名不使用負數名詞。

【強制】表字段表示是否概念，用 is_xxx 表示。

​

​is_default​

​

【強制】索引名稱，用特定_字段表示。

普通索引：​

​idx_xxx​

​

聯合索引：​

​un_xxx_xxx​

​

唯一索引：​

​uk_xxx​

​

表設計規範

【強制】如無特殊需求，存儲引擎使用 InnoDB。

1. 支援事務
2. 行級鎖
3. 并發性能好

【強制】資料庫和表的字元集統一使用 utf8 或者 utf8mb4。

不同字元集轉化可能會産生亂碼。

不同字元集比較前會進行字元轉換，索引失效。

UTF8 每個字元占用3位元組，占用空間小，但是不能存儲 emoj，emoj 占用4位元組。

UTF8MB4 每個字元占用4位元組，是真正的 UTF8，推薦使用。

【強制】資料庫表和字段都需要添加備注，更好了解建表思路。

【建議】資料庫表考慮分庫分表細節，推薦使用snowflake作為ID主鍵。

單表設計存儲資料少于 500 萬條或單表容量超過 2G。

不建議使用分區表，容易造成全表死鎖，跨分區查詢效率低。

【強制】表每一行中的每列資料大小相加不能大于 65535 byte。

【強制】不要設定預留字段，更改會鎖表。

【強制】不要儲存檔案等大的二進制資料。應放到檔案伺服器中。

【建議】InnoDB 字元集預設排序使用 _general_ci 和 _unicode_ci，推薦使用 _general_ci。

ci不區分大小寫

cs區分大小寫

general速度更快，準确性稍低

【建議】表必備三個字段，id、create_time、modify_time。

id：unsigned bigint。單表時主鍵單表時自增1，需要分表使用snowflake。

create_time：datatime。建立時間。

modify_time：datatime。修改資料更新時間。

【強制】存儲過程設計要合理，盡量少用。

過度複制邏輯容易死鎖。

可以替換為在後端業務層或者腳本實作。

【強制】不要使用觸發器。

高并發情況下不理想。

可以用事務替代。

【強制】資料量大的表要使用pt工具修改表結構。

pt-online-schema-change。

原理是建立一張表并複制原表結構與資料，最終删除原表，可以有效避免行鎖及表鎖。

字段設計規範

【強制】表字段表示是否概念，即is_xxx。

1表示是，0表示否

使用 unsigned tinyint

【強制】小數類型都使用decimal型。

decimal精确。

如果超出decimal範圍建議分兩個字段存儲。

【強制】ip及手機号類固定長度字段，要用char。

【強制】選擇合适的存儲長度。

可以減少表存儲空間。

可以減少索引長度，增加索引效率。

【建議】避免使用TEXT、BLOB資料類型。

記憶體臨時表不支援TEXT和BLOB。會使用磁盤臨時表，降低查詢速度。

TEXT和BLOB需要單獨成表，提高查詢效率。

【建議】避免使用ENUM資料類型。

枚舉類型order by效率低。

禁止使用數字作為枚舉值。

在與php使用上1和’1’差别大，PHP是弱引用很容易把’1’寫為1，1為key，’1’為内容。

【建議】盡可能把列定義設定為 NOT NULL。

索引NULL列，會額外增加開銷，占用更多表空間。

要做計算或者比較時，會對 NULL 做特别處理。

在 SQL 中對 NULL 進行判斷會全表掃描。

【強制】時間類型不要使用 varchar 或 int 等。

使用 timestamp，占用4位元組與int相同，查詢計算比int快。

timestamp 取值範圍，1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。

使用 datatime，占用8位元組，明确日期時間，超出timestamp用datatime。

索引設計規範

【強制】不要使用外鍵和級聯，應放在應用層去做。

外鍵與級聯更适合單機及低并發，不适合分布式和高并發叢集。

外鍵即外鍵限制，會影響寫操作（插入速度），降低性能。

級聯更新是強阻塞，存在更新風暴的風險。

【強制】一張表不要超過5個索引。

索引過多會降低性能。

合理配置設定索引會提高性能。

【強制】聯合索引的左側原則可以減少每個字段單獨建立索引。

避免每個字段都建立索引。

聯合索引區分度高的放在最左邊。

聯合索引如果存在非等号和等号混合時，把等号的索引放在最左邊。

聯合索引左側原則，一定要注意順序。

【強制】InnoDB 必須有主鍵。

結合建表四個必要字段，id作為主鍵。

InnoDB 屬于索引組織表，邏輯順序和索引的順序相同。

單表時主鍵自增1。

預計三年内達到500萬條，需要使用 snowflake 等分布式id生成主鍵。

不要使用 uuid、hash、md5 等作為主鍵，要有順序概念。

【建議】查詢想走特定索引時可以用force index。

MySQL的 optimizer 會執行它認為最優索引，但是往往不是我們需要或者最優的。

使用 force index 可以強制使用索引，結合 explain 使用，确認為最優。

【強制】有唯一索引需求，該字段就應設定唯一索引。

即使該字段是在聯合索引内，也要單獨設定唯一索引。

唯一索引對insert速度影響可以忽略，但是提高查詢速度和唯一性是明顯的。

應用層也建議做校驗控制，但是根據墨菲定律，隻要有可能就會出現髒資料。

【強制】varchar型設定索引要設定索引長度。

不設定預設是全部長度。

建議索引長度為20，區分度可以達到90%。

區分度計算公式：select count(distinct left(列名, 索引長度))/count(*) FROM 表名。可以查出區分度百分比。

【強制】模糊查詢最好用搜尋引擎。

禁止使用like %str和like %str%。因為不走索引。

可以使用like str%。走索引。

也可以走全文索引，但是需要看配置，還是推薦搜尋引擎。

【強制】order by 需要注意索引的有序性。

order by後接索引應該索引的一部分，如果是聯合索引，應該是聯合索引的最後，避免出現file_sort，影響查詢性能。where a=? and b=? order by c那麼索引是（a,b,c）。

file_sort出現是沒有走索引或者聯合索引。出現情況：where a=? order by b索引是a。改進優化：where a=? order by b索引是（a,b）。

【強制】避免備援索引。

重複索引：primary key(id)、index(id)、unique index(id)。

備援索引：key(a,b,c)、key(a,b)、key(a)。

【強制】查詢頻率較高的sql語句，應該使用覆寫索引。

覆寫索引不是真正的索引，是一種使用索引方式。

原理是從索引中查詢出想要内容，而不用回表查詢，提高查詢效率。

表現是explain的extra為Using index。

例如select user_no from user order user_age = 28索引為user_no時效率低，索引為（user_no,user_age）時為覆寫索引，查詢效率高。

【強制】避免隐式類型轉換。

定義和使用不同資料會造成隐式轉換。

隐式轉換會不走索引，降低查詢效率。

如select user_age from user where user_no='111'

【強制】避免在字段位置寫表達式，不走索引。

反例：​

​select user_no from user where user_age*2 = 36​

​。

正例：​

​select user_no from user where user_age = 36/2​

​。

查詢優化

【強制】SQL性能優化目标，由高到低。

const。基本是隻有一行比對。

ref。基本是走普通索引。

range。基本是走範圍索引。

index。走索引最差，和全表查詢相似。

NULL。不走索引，全表查詢。

【強制】不适用索引的幾種情況。

不等式：!=、<>。

null判斷：is null、is not null。

like模糊查詢：like %a、like %a%

not in。

【建議】避免使用IN操作，如果避免不了，需小于1000條。

多表查詢IN會影響查詢效率。

可以用between替代。

IN(select * from)索引會失效，可以使用join（left、right、inner、full）來實作。

【建議】join優化。

最好在三張表之内，最多不要超過5個，理論可以61個。

on關聯字段類型要相同。

每關聯一個表就會多配置設定一個關聯緩存，和join_buffer_size設定相關。占用記憶體過大會形成溢出，影響性能和穩定性。

left join的驅動表是左側表。

inner join的驅動表是資料少的表。

right join的驅動表是右側表。

MySQL沒有full join，可以用SQL實作。例如：​

​select * from A left join B on B.name = A.name where B.name is null union all select * from B​

​。

盡量利用小表驅動大表，可以減少循環嵌套次數。

straight join的使用。前提是inner join内連接配接。inner join優先查詢小表，但有group by、order by等file_sort,Using temporary時會想改變優先查詢表順序，這時可以使用straight join。straight join強制優先查詢表為左側表。

一定要是内連接配接才能使用straight join，否則資料可能不準确。

【強制】禁止select * 出現。

select * 增加額外解析成本。

增減字段對前端映射不一緻。

無用字段增加網絡消耗。

無法使用覆寫索引。

【強制】禁止使用不帶字段的insert出現。

正例：insert into user(user_no,user_age) values (123,18)

反例：insert into user values (123,18)

【強制】盡量避免子查詢。

子查詢一般在IN中。

子查詢會建立臨時表，不會存在索引。

結果集大的子查詢，性能越差。

可以使用join替代。

【強制】查詢一條或者是否有資料時，要使用limit 1。

索引效率最高。

explain的type為const。

【建議】order by字段沒有索引就不要排序。

order by字段有索引會按索引排序。沒有索引影響效率。

可以設定索引，或者覆寫索引。

【建議】盡量不使用or。

同一字段用IN、between等替代or，因為很多情況不會走索引。

多字段下or兩邊都需要是索引且其他條件也是索引，才會走索引。

最好使用union、union all來替換。

【建議】盡量用union all替代union。

union會集合後進行唯一性去重，涉及到排序，加大資源開銷。

在沒有重複資料情況強制使用union all。

【建議】拆分大且複雜的SQL。

一條SQL隻會使用一個CPU。

拆成多個小SQL可以通過并行提高查詢效率。

【強制】禁止使用ORDER BY RAND()

随機排序性能差。

可以用其他SQL替換。

原：​

​select id from 'dynamic' order by rand() limit 1;，​

​

新：​

​select id from 'dynamic' t1 join (select rand() * (select max(id) from 'dynamic') as nid) t2 on t1.id > t2.nid limit 1​

​;注意，此查詢隻能随機一條id，并連續查詢該id的順序條數，具體情況具體分析，适用随機取一條，不應用随機取多條。

随機取多條解決方案：先查詢所有id->在後端業務層做随機id->IN該id組。

rand()取值範圍：[ 0 , 1 )。

【強制】禁止對where條件字段進行函數轉換。

不走索引。

正例：​

​select user_age from user where create_time>'20190320'​

​

反例：​

​select user_age from user where date(create_time)>'20190320'​

​

【建議】in、exists、not in、not exists。

in是子查詢，優先查詢驅動表為内表，是以适合内表資料小的情況。

exists優先查詢驅動表為外表，适合外表資料小的情況。

不建議使用not in和not exists，不走索引且容易混淆。

建議用其他SQL替代。

反例：​

​select a.user_age from user a where a.user_no not in (select b.user_no from user_info b)​

​。

正例：​

​select a.user_age from user a left user_info b on a.user_no = b.user_no where b.user_no is null。​

​

【建議】offset偏移量、分頁。

分頁資料量大的情況會影響查詢效率，因為不是跳過offset行，而是查詢offset+N行，然後抛棄offset行。

優化舉例1：​

​select user_age from user where user_no > 13333 limit 20。​

​

優化舉例2：​

​select a.user_age from user a,(select user_no from user limit 13333,20)b where a.user_no = b.user_no。​

​

【強制】範圍查詢注意。

between、>、<，查詢時，如果是走聯合索引，那麼範圍查詢後的索引失效。

【強制】count（）相關。

統計行數要使用count（*），不要使用count（列名）。

count（*）會統計NULL資料，count（列名）不會統計NULL資料。