一:mysql 聚合索引-mysql教程-PHP中文网

一:mysql 聚合索引

php中文网

发布： 2016-06-07 16:25:56

原创

1604人浏览过

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`)

登录后复制

BrainyAI

BrainyAI—你的智能网页助手，一款聚合AI大模型和搜索引擎的浏览器插件

161

查看详情

查询：

? ?

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