mysql下
select * from log group by Id order by LoginTime
这个语句需要建立索引
我建立2个索引 分别是id 和 LoginTime
有人说建立复合索引 (id,LoginTime)我仍选择2个索引 只是不知道明确的原因???
select * from log group by Id order by LoginTime
这个语句需要建立索引
我建立2个索引 分别是id 和 LoginTime
有人说建立复合索引 (id,LoginTime)我仍选择2个索引 只是不知道明确的原因???
请注意是
group by Id order by LoginTime 不是 where id=? and LoginTime =?好像说不通?
在按loginTime排序数据量大的时候和数据量小的时候 建立索引两种方式效率是不同的
也走了索引的
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| a | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | |
| a | 1 | ids_ab | 1 | a | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | ids_ab | 2 | id | A | 2 | NULL | NULL | | BTREE | |
| a | 1 | idx_id | 1 | id | A | 2 | NULL | NULL | | BTREE | |
| a | 1 | idx_a | 1 | a | A | 2 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set
mysql> explain
select * from a group by id order by a;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------+
| 1 | SIMPLE | a | index | NULL | ids_ab | 9 | NULL | 12 | Using temporary |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------+
1 row in set