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: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: NULL
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 10
Extra: Using index for group-by


EXPLAIN SELECT DISTINCT user_id 
-> FROM group_message 
-> WHERE group_id = 2\G
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: ref
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: const
rows: 4
Extra: Using WHERE; Using index


EXPLAIN SELECT DISTINCT user_id
-> FROM group_message
-> WHERE group_id > 1 AND group_id < 10\G
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using WHERE; Using index; Using temporary


EXPLAIN SELECT DISTINCT max(user_id)
-> FROM group_message
-> WHERE group_id > 1 AND group_id < 10
-> GROUP BY group_id\G
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using WHERE; Using index; Using temporary; Using filesort

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