一些MySQL使用時遵循的規範（初版）

fxm547發表於2018-01-22
MySql

首發於fxm5547的部落格

建表

  • 見名知意，表名和欄位名以下劃線分割

  • 注意單複數，如使用者表為users，而不是user

  • 詳細定義表、欄位和索引的備註

  • 編碼統一為utf8mb4，不需要儲存emoji表情等特殊字元的欄位編碼可單獨改為utf8（varchar utf8建索引的最大長度為255，varchar utf8mb4建索引的最大長度為191），資料連結編碼使用utf8mb4

  • 欄位型別嚴格定義，需要注意型別、長度、是否為空、無符號（確定不會儲存複數的欄位加UNSIGNED）等

  • 主鍵一般用bigint、自增，關聯表 / 無實際意義表的主鍵命名為rec_id，其他表的主鍵要見名知意，如goods_id

  • 欄位（單欄位或多欄位）資料不能重複時，一定要定義Unique索引

  • 每張表一定要包含兩個欄位created_at(記錄生成時間)和updated_at（記錄最近修改時間）；通過資料庫的timestamp欄位型別實現，無須程式碼控制，http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html ALTER TABLE test ADD COLUMN created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , ADD COLUMN updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

    圖片

  • 基於效能的考慮，所有欄位均不能為空，即全部NOT NULL，設定預設值

  • 儲存開關、選項資料的欄位，通常使用tinyint(1)非UNSIGNED型別，通常1為開啟；0為關閉，多型別時，從1開始定義

SQL / PHQL

  • 程式中，SQL的關鍵字一定要大寫

  • 新增SQL時一定要驗證其效能，新增必要的索引，使用EXPLAIN/DESC關鍵字驗證索引是否命中

  • 資料庫SQL語句中，所有資料必須加單引號，無論數值還是字串，以避免可能的注入漏洞和SQL錯誤

  • 儘可能使用資料繫結的方式進行資料查詢

  • 多表操作時，對錶進行定義別名，定義別名的規則：取表中每個單詞的首字母作為別名，如ecs_ayb_content_categories AS acc

  • 只查需要的列，即使表的欄位很少

讀寫分離

  • 訪問資料庫方式讀寫分開。db為寫，rDb為讀。

  • 使用PHQL和表ORM方式會自動識別讀寫資料庫

  • 需要注意情況如下:

SELECT語句使用rDb。rDb不能執行更新、新增、刪除操作。 $app->rDb->query("SELECT * FROM ecs_user_baby WHERE baby_id=2 LIMIT 1") $app->db->query("UPDATE ecs_user_baby SET read_count=read_count WHERE baby_id=2 LIMIT 1");

在Model中讀資料庫也可以使用getReadConnection方法，寫使用getWriteConnection方法。 $userGoodsShelf = new UserGoodsShelf(); $userGoodsShelf->getReadConnection()->execute("SELECT * FROM ecs_user_baby LIMIT 1 ");

索引相關

參考：

基本準則

  • 程式開發時，時刻注意新寫的SQL是否需要增加索引或優化已有索引（聯合索引或單索引），以explain/desc驗證索引是否命中

  • 建立Unique索引，使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...完成一些需要兩條SQL完成的複雜邏輯；禁止使用REPLACE ... INTO ...，它會帶來一些問題

  • 定期檢視慢查詢日記，優化慢SQL，目前閥值時1s

    圖片

B+樹索引

1、最左字首匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

2、=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式

3、儘量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄

4、索引列不能參與計算，保持列“乾淨”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函式才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);

5、儘量的擴充套件索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可

慢查詢優化基本步驟

0、先執行看看是否真的很慢，注意設定SQL_NO_CACHE

1、where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個欄位分別查詢，看哪個欄位的區分度最高

2、explain檢視執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）

3、order by limit 形式的sql語句讓排序的表優先查

全文索引

MySQL 5.6.4版本起InnoDB已經支援全文索引，簡單例項：

  • 新建索引： alter table articles add fulltext index(title,body);

  • 搜尋 title 和 body 包含 database和MySQL關鍵字的記錄： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database,MySQL');

  • 搜尋title和body中包含 MySQL ，但是不能有 YourSQL 的結果： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

是否使用資料庫全文索引視情況而定，資料量大應該使用Elasticsearch

其他

  • 預設圖片應該在程式邏輯中加，不應該存入資料庫

相關文章