mysql使用技巧---2、mysql常用使用規範（二）（總結）

mysql使用技巧---2、mysql常用使用規範（二）（總結）

一、總結

一句話總結：

1、【禁止使用 order by rand()】 進行随機排序？

會把表中所有符合條件的【資料裝載到記憶體】中，然後在記憶體中對所有資料根據随機生成的值進行排序，并且【可能會對每一行都生成一個随機值】，如果滿足條件的資料集非常大，就會消耗大量的 CPU 和 IO 及記憶體資源。

推薦【在程式中擷取一個随機值】，然後從資料庫中擷取資料的方式。

2、避免建立【備援索引】和【重複索引】？

因為這樣會增加【查詢優化器】【生成執行計劃的時間】。

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

備援索引示例：index(a,b,c)、index(a,b)、index(a)

3、盡量避免使用外鍵限制？

不建議使用外鍵限制（foreign key），但一定要在表與表之間的【關聯鍵上建立索引】。

外鍵可用于保證資料的參照完整性，但建議在【業務端實作】。

外鍵會影響父表和子表的寫操作進而【降低性能】。

4、充分利用表上已經存在的索引？

【避免使用雙 % 号的查詢條件】。如a like '%123%'，（如果無前置 %，隻有後置 %，是可以用到列上的索引的）

一個 SQL隻能利用到【複合索引中的一列】進行範圍查詢如：有 a,b,c 列的聯合索引，在查詢條件中有 a 列的範圍查詢，則在 b,c 列上的索引将不會被用到，在定義聯合索引時，如果a列要用到範圍查找的話，就要把 a 列放到【聯合索引的右側】。

使用 left join 或 not exists 來優化 not in 操作，因為 【not in也通常會使用索引失效】。

5、程式連接配接【不同的資料庫使用不同的賬号】，禁止跨庫查詢？

為【資料庫遷移】和【分庫分表】留出餘地

降低業務【耦合度】

避免權限過大而産生的【安全風險】

6、禁止使用 SELECT * 【必須使用 SELECT <字段清單> 】查詢，原因如下？

消耗【更多的CPU】 和 【IO】 以及【網絡帶寬資源】

無法使用【覆寫索引】

可減少【表結構變更帶來的影響】

7、【避免使用子查詢】，可以把子查詢優化為 【JOIN 操作】？

通常子查詢在 【in子句】中，且子查詢中為簡單 SQL ( 不包含 union、group by、order by、limit 從句 ) 時，才可以把子查詢轉化為關聯查詢進行優化。

8、子查詢性能差的原因？

子查詢的結果集【無法使用索引】，通常子【查詢的結果集會被存儲到臨時表】中，不論是記憶體臨時表還是磁盤臨時表都不會存在索引，是以查詢性能會受到一定的影響。 特别是對于傳回結果集比較大的子查詢，其對查詢性能的影響也就越大。

由于子查詢會産生大量的臨時表也沒有索引，是以會【消耗過多的 CPU 和 IO 資源】，産生【大量的慢查詢】。

9、避免使用 JOIN 關聯太多的表？

對于 MySQL 來說，是存在【關聯緩存】的，緩存的大小可以由 【join_buffer_size 參數】進行設定。

在 MySQL 中，【對于同一個 SQL 多關聯（join）一個表，就會多配置設定一個關聯緩存】，如果在一個 SQL 中關聯的表越多，所占用的記憶體也就越大。如果程式中大量的使用了多表關聯的操作，同時 join_buffer_size 設定的也不合理的情況下，就容易造成【伺服器記憶體溢出】的情況，就會影響到伺服器資料庫性能的穩定性。

【關聯操作不超過5個表】：同時對于關聯操作來說，會産生臨時表操作，影響查詢效率 MySQL 最多允許關聯 61 個表，建議不超過 5 個。

10、減少同資料庫的互動次數？

資料庫【更适合處理批量操作 】，合并【多個相同的操作到一起】，可以提高處理效率

11、拆分複雜的大 SQL 為多個小 SQL？

大 SQL：邏輯上比較複雜，需要【占用大量 CPU】進行計算的SQL 。

MySQL：一個 SQL 隻能使用【一個CPU】 進行計算。

SQL 拆分後可以通過【并行執行】來【提高處理效率】。

12、對大表資料結構的修改一定要謹慎？

對大表資料結構的修改一定要謹慎，會造成【嚴重的鎖表操作】，尤其是生産環境，是不能容忍的。

 ​