Jex’s Note

MySQL Index 解說

為什麼要建立索引:

適當的設定index可以有效的降低資料庫查詢的時間, 但這是以空間爭取時間的做法, 所以建立索引必須先考量到常被查詢的欄位

如果查詢時判斷多個欄位, 建立多組欄位的索引也要考量優先順序, 才能有效降低索引所佔用的資料庫空間

索引的規則:

假設有一組索引是uid+gid+ctime

  • where條件不分先後順序, 假設ctime先再來是uid及gid, 也是符合的
  • 這組索引的重要順序也是依你設定的順序為主, 假設你的索引是uid+ctime, 那麼這組索引只有uid對你有效
  • 如果條件是gid+ctime這組索引是沒有效用的, 因為這組索引是由uid先開始排
  • 換句話說條件為uid+uname的話, 這組索引至少也可以幫你減少搜尋uid的時間n 但uname沒在索引上, 所以mysql還是得一行一行找

使用 EXPLAIN 來檢查執行命令後索引的使用狀況

EXPLAIN SELECT *
FROM  `user_log`
WHERE uid =2
AND gid =2
AND ctime >0

結果:(只列出重要的)

key : uid+gid+ctime  (使用到的索引)
raw : 4              (查詢筆數)

參考來源:夯哥

排序(ORDER BY)

mysql 排序是按照該欄位的編碼, 例如 utf8_unicode_ci 就是按照 UTF8(unicode) 的順序去排 數 > 英 > 日 > 中 > 韓

也可以用 COLLATE 指定排序的編碼

utf8(unicode) table

不會使用到 index 的狀況 (innodb)

如果 where 多個已經有建 index 的欄位, 不會使用到 index

user_id = 'A01' or user_id = 'A02'
IN('A01','A02')

單個才會使用到 index

user_id = 'A01'
IN('A01')

欄位優化

IP 用 int unsigned 存

> echo ip2long('10.0.115.80');
> 167801680

修改大 TABLE (Innno DB)

新增欄位

目前測試的量沒有很多, 但可以參考看看

環境: AWS RDS Aurora MySQL 5.6.10a (db.t2.small)

MySQL 5.6 以前不支援 ALTER table without locking table, 如果是 5.6 又是 InnoDB 的話,可以放心 alter,TABLE 不會被 lock

130 萬筆資料庫的檔案大約需要 67s,cpu 會吃 35%,一直 insert 也很順, 不會影響到服務

不建議建立另一個沒 data 的 TABLE, 先 ALTER 後再 copy 舊 TABLE 到新的, 因為 cpu 會吃到 80% 以上, 操作也略為複雜

修改欄位屬性 (ALTER TABLE)

這個動作會 lock table, INSERT / UPDATE / SELECT 都會, 它會先執行 copy to tmp table, 所以會整個會卡死

300 萬筆就足夠 lock 30 分以上

加欄位跟index

不會 lock table

  • 新開一個 m3.medium RDS (與 prod 等級相同) 來測試新增一個 index (只 index 一個欄位), 將近 580 萬筆的 table, 需 10 min 40.88 sec CPU 會上升 20%, 但在 prod 實際執行時只需 2 min 33.05 sec CPU 一樣上升 20% (目前還不清楚為什麼 prod 需要的時間短很多)

新增 index

這個動作不會 lock table

對大 table 做 CRUD 的效能參考

860 萬的 table

SELECT

SELECT count(*) FROM `call_cv_history` WHERE event_at <= '2018-03-19 23:59:59';
  • event_at 範圍是1天的話是 1 row in set (0.50 sec)
  • event_at 範圍是10天的話是 1 row in set (5.14 sec)

event_at 有設 idnex, type 為 datetime

DELETE

DELETE FROM `call_cv_history` WHERE event_at <= '2018-03-20 23:59:59' LIMIT 50000;
  • LIMIT 是 10,000 的話是 Query OK, 10000 rows affected (0.91 sec)
  • LIMIT 是 50,000 的話是 Query OK, 50000 rows affected (13.26 sec)

最後採取每次刪 10,000 每 2~3 秒刪一次, 避免 cpu 飆高

INSERT 非常快無需擔心

COUNT(*) & COUNT(1) 初淺效能比較

先給結論: 無差別

測試環境: AWS RDS MySQL db.t2.small

Table 筆數: 近 570 萬

執行 query 所花時間差不多都是 0.75 秒

Comments