MySQL count 之索引挑选
发布时间：2022-06-28 05:29:06 所属栏目：MySql教程 来源：互联网
导读：覆盖索引对于一些统计问题，如下： MySQL show create table test1 G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `id` bigint(16) NOT NULL AUTO_INCREMENT, `order_seq` bigin
|
覆盖索引对于一些统计问题，如下： 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，表示为覆盖索引。 （编辑：安康站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐