4.调整索引，A表优化采用覆盖索引：
| mysql>alter table test_log drop index ix_test_log_userid,add index ix_test_log_userid(userid,practicetime) |
5.查看执行计划：
| mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows FROM test_log a,USER b WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 GROUP BY DATE(practicetime)G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: index possible_keys: ix_test_log_userid key: ix_test_log_userid key_len: 105 ref: NULL rows: 388451 Extra: Using index; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 96 ref: user_app_test.a.userid rows: 1 Extra: Using where 2 rows in set (0.00 sec) |
调整后执行稍有效果，但是还不明显，还没有找到要害：
| SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows FROM test_log a,USER b WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 GROUP BY DATE(practicetime); ………………. 143 rows in set (1 min 12.62 sec) |
6.执行时间仍然需要很长，时间的消耗主要耗费在Using filesort中，参与排序的数据量有38W之多，所以需要转换驱动表；尝试采用user表做驱动表：使用straight_join强制连接顺序：
| mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows FROM USER b straight_join test_log a WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 GROUP BY DATE(practicetime)G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 42806 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: ix_test_log_userid key: ix_test_log_userid key_len: 96 ref: user_app_test.b.userid rows: 38 Extra: Using index 2 rows in set (0.00 sec) |
执行时间已经有了质的变化，降低到了2.56秒；
| mysql>SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows FROM USER b straight_join test_log a WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 GROUP BY DATE(practicetime); …….. 143 rows in set (2.56 sec) |
7.在分析执行计划的第一步：Using where; Using temporary; Using filesort，user表其实也可以采用覆盖索引来避免using where的出现，所以继续调整索引：
| mysql> show index from user; +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ | user | 0 | PRIMARY | 1 | userid | A | 43412 | NULL | NULL | | BTREE | | | user | 0 | ix_user_email | 1 | email | A | 43412 | NULL | NULL | | BTREE | | | user | 1 | ix_user_username | 1 | username | A | 202 | NULL | NULL | | BTREE | | +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ 3 rows in set (0.01 sec) mysql>alter table user drop index ix_user_username,add index ix_user_username(username,isfree); Query OK, 42722 rows affected (0.73 sec) Records: 42722 Duplicates: 0 Warnings: 0 mysql>explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows FROM USER b straight_join test_log a WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 GROUP BY DATE(practicetime); *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b type: index possible_keys: PRIMARY key: ix_user_username key_len: 125 ref: NULL rows: 42466 Extra: Using where; Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: ix_test_log_userid key: ix_test_log_userid key_len: 96 ref: user_app_test.b.userid rows: 38 Extra: Using index 2 rows in set (0.00 sec) |
