MySQL 相關(guān)學(xué)習(xí)筆記
- MySQL 索引學(xué)習(xí)筆記
- MySQL 存儲(chǔ)引擎學(xué)習(xí)筆記
- MySQL 靜態(tài) (固定長(zhǎng)度) 數(shù)據(jù)表 特性
- MySQL 性能優(yōu)化學(xué)習(xí)筆記
摘要： 阿里巴巴集團(tuán)推出的《阿里巴巴Java開發(fā)手冊(cè)（正式版）》是阿里巴巴近萬(wàn)名開發(fā)同學(xué)集體智慧的結(jié)晶，以開發(fā)視角為中心，詳細(xì)列舉如何開發(fā)更加高效、更加容錯(cuò)、更加有協(xié)作性，力求知其然，更知其不然，結(jié)合正反例，讓Java開發(fā)者能夠提升協(xié)作效率、提高代碼質(zhì)量。
作為一個(gè) Java 開發(fā)人員，我花了一天時(shí)間閱讀了這個(gè)開發(fā)手冊(cè)，摘要了一些對(duì)于我有用的知識(shí)點(diǎn)。記錄如下，僅供參考。
MySQL
1. 建表規(guī)約
- 表達(dá)是與否概念的字段，必須使用
is_xxx的方式命名，數(shù)據(jù)類型是unsigned tinyint（ 1 表示是，0 表示否）。
說(shuō)明：任何字段如果為非負(fù)數(shù)，必須是unsigned。
-
表名、字段名必須使用小寫字母或數(shù)字；禁止出現(xiàn)數(shù)字開頭，禁止兩個(gè)下劃線中間只出現(xiàn)數(shù)字。
正例：getter_admin，task_config，level3_name
反例：GetterAdmin，taskConfig，level_3_name -
表名不使用復(fù)數(shù)名詞。
說(shuō)明：表名應(yīng)該僅僅表示表里面的實(shí)體內(nèi)容，不應(yīng)該表示實(shí)體數(shù)量，對(duì)應(yīng)于 DO 類名也是單數(shù)形式，符合表達(dá)習(xí)慣。 - 唯一索引名為
uk_xxx字段名；普通索引名則為idx_xxx字段名。
說(shuō)明：uk_xxx即 unique key；idx_xxx即 index 的簡(jiǎn)稱。 - 小數(shù)類型為
decimal，禁止使用float和double。
說(shuō)明：float和double在存儲(chǔ)的時(shí)候，存在精度損失的問(wèn)題，很可能在值的比較時(shí)，得到不正確的結(jié)果。如果存儲(chǔ)的數(shù)據(jù)范圍超過(guò)decimal的范圍，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)分開存儲(chǔ)。 - 如果存儲(chǔ)的字符串長(zhǎng)度幾乎相等，使用
char定長(zhǎng)字符串類型。 -
varchar是可變長(zhǎng)字符串，不預(yù)先分配存儲(chǔ)空間，長(zhǎng)度不要超過(guò) 5000，如果存儲(chǔ)長(zhǎng)度大于此值，定義字段類型為text，獨(dú)立出來(lái)一張表，用主鍵來(lái)對(duì)應(yīng)，避免影響其它字段索引效率。 - 字段允許適當(dāng)冗余，以提高性能，但是必須考慮數(shù)據(jù)同步的情況。冗余字段應(yīng)遵循：
1）不是頻繁修改的字段。
2）不是varchar超長(zhǎng)字段，更不能是text字段。
正例：商品類目名稱使用頻率高，字段長(zhǎng)度短，名稱基本一成不變，可在相關(guān)聯(lián)的表中冗余存儲(chǔ)類目名稱，避免關(guān)聯(lián)查詢。
2. 索引規(guī)約
-
業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。
說(shuō)明：不要以為唯一索引影響了 insert 速度，這個(gè)速度損耗可以忽略，但提高查找速度是明顯的；另外，即使在應(yīng)用層做了非常完善的校驗(yàn)和控制，只要沒有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。
-
在
varchar字段上建立索引時(shí)，必須指定索引長(zhǎng)度，沒必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度。
說(shuō)明：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類型數(shù)據(jù)，長(zhǎng)度為 20 的索引，區(qū)分度會(huì)高達(dá) 90%以上，可以使用count(distinct left(列名, 索引長(zhǎng)度))/count(*)的區(qū)分度來(lái)確定。 -
頁(yè)面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)走搜索引擎來(lái)解決。
說(shuō)明：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無(wú)法使用此索引。 -
如果有
order by的場(chǎng)景，請(qǐng)注意利用索引的有序性。order by最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況，影響查詢性能。
正例：where a=? and b=? order by c;索引：a_b_c -
利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁(yè)場(chǎng)景。
說(shuō)明：MySQL 并不是跳過(guò) offset 行，而是取 offset+N 行，然后返回放棄前 offset 行，返回 N 行，那當(dāng) offset 特別大的時(shí)候，效率就非常的低下，要么控制返回的總頁(yè)數(shù)，要么對(duì)超過(guò)特定閾值的頁(yè)數(shù)進(jìn)行 SQL 改寫。
正例：先快速定位需要獲取的 id 段，然后再關(guān)聯(lián)：
SELECT a.* FROM 表 1 a, (select id from 表 1 where 條件 LIMIT 100000,20 ) b where a.id=b.id - 建組合索引的時(shí)候，區(qū)分度最高的在最左邊。
3. 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)。
說(shuō)明：count(*)會(huì)統(tǒng)計(jì)值為NULL的行，而count(列名)不會(huì)統(tǒng)計(jì)此列為NULL值的行。
-
count(distinct col)計(jì)算該列除NULL之外的不重復(fù)數(shù)量。
注意count(distinct col1, col2)如果其中一列全為NULL，那么即使另一列有不同的值，也返回為 0。 - 使用
ISNULL()來(lái)判斷是否為NULL值。注意：NULL與任何值的直接比較都為NULL。
說(shuō)明：
1）NULL<>NULL的返回結(jié)果是NULL，而不是false。
2）NULL=NULL的返回結(jié)果是NULL，而不是true。
3）NULL<>1的返回結(jié)果是NULL，而不是true。 - 如果有全球化需要，所有的字符存儲(chǔ)與表示，均以 utf-8 編碼，那么字符計(jì)數(shù)方法注意：
說(shuō)明：
SELECT LENGTH("輕松工作");返回為 12
SELECT CHARACTER_LENGTH("輕松工作");返回為 4
如果要使用表情，那么使用 utfmb4 來(lái)進(jìn)行存儲(chǔ)，注意它與 utf-8 編碼的區(qū)別。 -
TRUNCATE TABLE比DELETE速度快，且使用的系統(tǒng)和事務(wù)日志資源少，但TRUNCATE無(wú)事務(wù)且不觸發(fā) trigger，有可能造成事故，故不建議在開發(fā)代碼中使用此語(yǔ)句。
4. ORM 規(guī)約
- 在表查詢中，一律不要使用
*作為查詢的字段列表，需要哪些字段必須明確寫明。
說(shuō)明：1）增加查詢分析器解析成本。2）增減字段容易與 resultMap 配置不一致。
- POJO 類的
boolean屬性不能加is，而數(shù)據(jù)庫(kù)字段必須加is_，要求在 resultMap 中進(jìn)行字段與屬性之間的映射。 - xml 配置中參數(shù)注意使用：
#{}，#param#不要使用${}此種方式容易出現(xiàn) SQL 注入。 - 不要寫一個(gè)大而全的數(shù)據(jù)更新接口，傳入為 POJO 類，不管是不是自己的目標(biāo)更新字段，都進(jìn)行
update table set c1=value1,c2=value2,c3=value3;這是不對(duì)的。執(zhí)行 SQL 時(shí)，盡量不要更新無(wú)改動(dòng)的字段，一是易出錯(cuò)；二是效率低；三是 binlog 增加存儲(chǔ)。
