1:mysql 聚合索引

1：

  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`,`task_id`)

 

查询：

   

mysql> explain select * from user_task where uid = 232;
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | user_task | ref  | uid           | uid  | 4       | const |  206 |       |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------+

 

   

mysql> explain select * from user_task where task_id = 1454;
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | user_task | ALL  | NULL          | NULL | NULL    | NULL | 55917 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

 

   

mysql> explain select * from user_task where task_id = 1454 and uid = 232;
+----+-------------+-----------+-------+---------------+------+---------+-------------+------+-------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref         | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+-------------+------+-------+
|  1 | SIMPLE      | user_task | const | uid           | uid  | 8       | const,const |    1 |       |
+----+-------------+-----------+-------+---------------+------+---------+-------------+------+-------+
1 row in set (0.00 sec)

 

如果（a， b, c）

查询a ； a b ； a b c 会用到索引

 

所以建立索引的时候 如果要查询 b c  那么可以 key(b, c)

 

KEY 和index 不同的是 key 可以有约束 同时会有索引