一、建庫(kù)建表優(yōu)化

1、核心規(guī)范（推薦）

• 表字符集選擇UTF8 （“表情”字段單獨(dú)設(shè)置為其他字符集）
• 存儲(chǔ)引擎使用INNODB
• 不在庫(kù)中存儲(chǔ)圖片、文件等
• 使用可變長(zhǎng)字符串（varchar）
• 每張表數(shù)據(jù)量控制在5000W以下

2、字段命名規(guī)范（建議）

• 庫(kù)名、表名、字段名、索引名使用小寫字母，以下劃線分割
• 非唯一索引按照“idx_字段名[_字段名]”進(jìn)行命名
• 唯一索引按照“uniq_字段名[_字段名]”進(jìn)行命名（不要直接采用字段名稱定義索引名稱。防止刪除索引時(shí)，誤刪除字段）

3、字段屬性規(guī)則（建議）

• 所有字段均定義為 NOT NULL（null會(huì)降低索引效果;索引會(huì)產(chǎn)生額外的空間）
• ·使用unsigned存儲(chǔ)非負(fù)整數(shù)
• ·使用timestamp存儲(chǔ)時(shí)間（可利用該類型的默認(rèn)值，進(jìn)行查詢優(yōu)化）

4、字段類型規(guī)則（推薦）

• 使用tinyint來(lái)代替enum類型
• 盡可能不用text、blob類型
• 將字符轉(zhuǎn)化為數(shù)字
• 存儲(chǔ) “abcd” 時(shí) varchar(5) 比 varchar(10) 更優(yōu)

5、索引規(guī)則（推薦）

• 選擇自增列作為主鍵
• 單表索引數(shù)不超過(guò)5個(gè)、單個(gè)索引字段數(shù)不超過(guò)5個(gè)
• 字符串可使用前綴索引，前綴長(zhǎng)度控制在5-8個(gè)字符
• 不在低基數(shù)列上建立索引，如：性別、是否刪除、是否發(fā)布
• 不使用select * 優(yōu)化成 select id,name,age……..
• 不在索引列進(jìn)行數(shù)學(xué)運(yùn)算、函數(shù)

6、SQL規(guī)范

• 避免隱式轉(zhuǎn)換
• 避免使用存儲(chǔ)過(guò)程、觸發(fā)器、函數(shù)
• 避免進(jìn)行數(shù)學(xué)運(yùn)算
• 盡可能拆分大SQL

二、建立高效索引

目的：加速查詢、加速排序、覆蓋索引（只需要在索引中完成查詢，不需要回到表中）

1、主鍵：和數(shù)據(jù)存儲(chǔ)在一起。

• 通常選擇自增列作為主鍵
• 優(yōu)點(diǎn)：
  • a 順序插入，不會(huì)出現(xiàn)數(shù)據(jù)頁(yè)內(nèi)數(shù)據(jù)移動(dòng)的情況發(fā)生（插入更快）
  • b 數(shù)據(jù)存儲(chǔ)更緊湊（查詢更快）
• 缺點(diǎn)：
  • 多出4至8字節(jié)無(wú)意義的數(shù)據(jù)

2、二級(jí)索引：和數(shù)據(jù)分開(kāi)存儲(chǔ)

• 二級(jí)索引中是按照索引列+主鍵的對(duì)應(yīng)關(guān)系進(jìn)行存儲(chǔ)的，每多一個(gè)索引就會(huì)多一個(gè)這樣的對(duì)應(yīng)關(guān)系。所以索引的個(gè)數(shù)越多，占用空間越大，在插入、刪除的時(shí)候會(huì)越慢。

3、什么樣的字段適合加索引？

• 首先，要滿足主要功能的查詢條件。
• 其次，要看該字段的唯一值多少。
• 唯一值： select count(distinct uid)/count(1) from table;?值越大，索引效果越好。

　　　type ?：建議優(yōu)化的類型

?

• • • system ? ? ? ? ? 表只有一行
    • const ? ? ? ? ? ? ?用到的是主鍵或唯一索引 eq_ref ? ? ? ? ? ?多表查詢時(shí)，匹配到了1行，并且利用的是主鍵或唯一索引
    • ref ? ? ? ? ? ? ? ? ? 匹配到了多行，通常是利用的普通索引（如果是聯(lián)合唯一索引，只用到了其中1個(gè)也是這個(gè)類型）
    • ref_or_null ? ? 與ref類似，條件中用到了 null 的搜索

• • • all ? ? ? ? ? ? ? ? ? 沒(méi)有用到索引
    • 出現(xiàn)上面所列之外的類型時(shí)，如range、index等說(shuō)明用到的索引性能很差

　　　 rows：

• • • 查詢影響的行數(shù)，值越小越優(yōu)。

　　 ? ?extra：　

• • • 查詢的詳細(xì)信息，類型包括：
    • using where、using index、using filesort等都是正常查詢過(guò)程
    • using temporary 出現(xiàn)時(shí)，說(shuō)明需要對(duì)sql或索引進(jìn)行優(yōu)化

二、優(yōu)化SQL

• 需要多表查詢時(shí)，內(nèi)（外）連接查詢不一定是最佳的方案，適當(dāng)?shù)牟捎米硬樵?#xff0c;會(huì)是更好的選擇。
• 把 select * 換成部分字段，可少許降低查詢時(shí)間
• 垃圾索引只會(huì)影響插入、刪除效率，對(duì)查詢速度影響較小。
• 字段唯一性太低，索引效率不高。
• 字段唯一性非常高，索引的性能會(huì)很優(yōu)秀。
• 時(shí)間范圍很大時(shí)，用不到索引。盡可能讓時(shí)間范圍有開(kāi)口和閉口，區(qū)間也不易過(guò)大，根據(jù)數(shù)據(jù)量及最早時(shí)間來(lái)決定。

?

轉(zhuǎn)載于:https://www.cnblogs.com/sunchong/p/8521882.html

總結(jié)

以上是生活随笔為你收集整理的MySQL 规范及优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。