当前位置:Gxlcms > mysql > 一:mysql聚合索引

一:mysql聚合索引

时间:2021-07-01 10:21:17 帮助过:68人阅读

1:mysql 聚合索引 1: PRIMARY KEY (`id`), UNIQUE KEY `uid` (`uid`,`task_id`) ? 查询: ? ? mysql explain select * from user_task where uid = 232;+----+-------------+-----------+------+---------------+------+---------+-------+------+-------+|

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 可以有约束 同时会有索引

人气教程排行