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 可以有约束 同时会有索引
申明:本教程内容由威凡网编辑整理并提供IT程序员分享学习,如文中有侵权行为,请与站长联系(QQ:254677821)!