覆盖索引对于一些统计问题,如下: Mysql > show create table test1 \G *****************
覆盖索引对于一些统计问题,如下:
Mysql > show create table test1 \G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` bigint(16) NOT NULL AUTO_INCREMENT,
`order_seq` bigint(16) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_id` (`id`),
KEY `idx_id_ordseq` (`id`,`order_seq`)
) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql > explain select count(*) from test1 where id>10000 and id<20000;
+----+-------------+-------+------------+-------+------------------------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------------+--------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test1 | NULL | range | PRIMARY,idx_id,idx_id_ordseq | idx_id | 8 | NULL | 9999 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+------------------------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
innodb存储引擎选择了id字段的辅助索引,而不是聚集索引来统计,更不是联合索引。原因是辅助索引远小于聚集索引,选择辅助索引可以减少IO资源消耗。
而另外一个统计场景:
select count(*) from test1 where order_seq > 1502131212577 and order_seq< 202007080947244761;
test1表建有id和 order_seq 字段的联合索引。
Mysql > show create table test1 \G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` bigint(16) NOT NULL AUTO_INCREMENT,
`order_seq` bigint(16) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_id` (`id`),
KEY `idx_id_ordseq` (`id`,`order_seq`)
) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
MySQL > explain select count(*) from test1 where order_seq > 1502131212577 and order_seq< 202007080947244761;
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+----------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+----------+----------+--------------------------+
| 1 | SIMPLE | test1 | NULL | index | NULL | idx_id_ordseq | 16 | NULL | 15068082 | 11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+----------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
这里使用条件
order_seq 查询,一般情况下使用不了联合索引的,但是这个案例中的查询,利用到覆盖索引的信息。possible_keys依然为null,但是key是idx_id_ordseq,extra里出现Using index,表示为覆盖索引。
--结束END--
本文标题: MySQL count(*)之索引选择
本文链接: https://www.lsjlt.com/news/45789.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0