mysql 最佳化學習

ORDER BY  | GROUP
BY  |  DISTINCT

------------------------------------------------------------------------------------------------------------------------------

mysql  GROUP BY

KEY `accountId` (`ifPersonal`,`createdUser`,`accountUser`)
sql1:
SELECT createdUser FROM t_account WHERE ifPersonal=1 AND createdUser>90000081 GROUP BY ifPersonal, createdUser, accountUser
Extra: Using where; Using index for group-by(就是所謂的鬆散索引了)
注意要求：
1. group by 後的欄位為索引欄位，且為索引順序出現，且都得出現（不都出現就是‘緊湊’索引了）
2. where 語句中出現的條件得是索引中的欄位，順序、是否為常量莫有關係
3. select 返回欄位必須得是 索引欄位
4. 如果使用聚集合函式，只能對索引欄位操作

sql2:
SELECT createdUser FROM t_account WHERE ifPersonal = 1 AND createdUser = 90000081 GROUP BY accountUser
SELECT `accountAdBalance` FROM t_account WHERE ifPersonal = 1 GROUP BY createdUser, accountUser

注意要求：
使用到‘緊湊’索引
1. 使用索引，索引按順序出現，從 WHERE 開始到 group by
2. WHERE 條件中，必須是常量

------------------------------------------------------------------------------------------------------------------------------

mysql DISTINCT

Using index for distinct【最佳】
Using index for group-by

sql:
SELECT DISTINCT ifPersonal FROM t_account
SELECT DISTINCT ifPersonal FROM t_account WHERE `createdUser`> 90000013

同 GROUP BY 可以通過鬆散索引掃描或者是緊湊索引掃描來實現
同GROUP BY 有一點差別的是，DISTINCT 並不需要進行排序。
如果還使用了GROUP BY 並進行了分組，並使用了類似於MAX 之類的彙總函式操作，就無法避免filesort 了。
注意點同 group by

EXPLAIN SELECT DISTINCT group_id -> FROM group_message\G*************************** 1. row ***************************id: 1SELECT_type: SIMPLEtable: group_messagetype: rangepossible_keys: NULLkey: idx_gid_uid_gckey_len: 4ref: NULLrows: 10Extra: Using index for group-byEXPLAIN SELECT DISTINCT user_id -> FROM group_message -> WHERE group_id = 2\G*************************** 1. row ***************************id: 1SELECT_type: SIMPLEtable: group_messagetype: refpossible_keys: idx_gid_uid_gckey: idx_gid_uid_gckey_len: 4ref: constrows: 4Extra: Using WHERE; Using indexEXPLAIN SELECT DISTINCT user_id-> FROM group_message-> WHERE group_id > 1 AND group_id < 10\G*************************** 1. row ***************************id: 1SELECT_type: SIMPLEtable: group_messagetype: rangepossible_keys: idx_gid_uid_gckey: idx_gid_uid_gckey_len: 4ref: NULLrows: 32Extra: Using WHERE; Using index; Using temporaryEXPLAIN SELECT DISTINCT max(user_id)-> FROM group_message-> WHERE group_id > 1 AND group_id < 10-> GROUP BY group_id\G*************************** 1. row ***************************id: 1SELECT_type: SIMPLEtable: group_messagetype: rangepossible_keys: idx_gid_uid_gckey: idx_gid_uid_gckey_len: 4ref: NULLrows: 32Extra: Using WHERE; Using index; Using temporary; Using filesort

------------------------------------------------------------------------------------------------------------------------------