mysql explain扫描行数_mysql,_MySQL 关于查询时扫描行数与索引的疑问，mysql - phpStudy...

MySQL 关于查询时扫描行数与索引的疑问

测试表：

CREATE TABLE `table_1` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`title` text NOT NULL,

`category_id` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

其中 id 字段是自增主键

插入 30 行用于测试的数据：

insert into table_1 (`category_id`)values(1);

insert into table_1 (`category_id`)values(1);

insert into table_1 (`category_id`)values(1);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(2);

insert into table_1 (`category_id`)values(3);

insert into table_1 (`category_id`)values(3);

insert into table_1 (`category_id`)values(3);

insert into table_1 (`category_id`)values(4);

insert into table_1 (`category_id`)values(4);

insert into table_1 (`category_id`)values(4);

insert into table_1 (`category_id`)values(5);

insert into table_1 (`category_id`)values(5);

insert into table_1 (`category_id`)values(5);

执行查询：

mysql> explain select * from `table_1` order by `id` DESC limit 0,5;

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

| 1 | SIMPLE | table_1 | index | NULL | PRIMARY | 4 | NULL | 5 | |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

1 row in set

这个很好理解，因为 id 是主键，查询中只使用了 order by id ，查询涉及记录行数 rows 5，因为 limit 0,5

mysql> explain select * from `table_1` where `category_id`=2 order by `id` DESC limit 0,5;

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | table_1 | index | NULL | PRIMARY | 4 | NULL | 5 | Using where |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

1 row in set

这个就无法理解了，为什么使用了 where category_id=2 ，用一个非索引字段 where ，该查询涉及的记录数仍然是 5 ？将 category_id=2 改为任何数字，rows 都为 5，实际记录前几条并不是 category_id=2 ，按理应该先跳过 category_id!=2 的然后筛选出符合的结果返回，这样涉及的行数应该大于 5 啊

更无法理解的是，如果使用该表 category_id 建立索引，同样该 SQL 执行结果：

mysql> explain select * from `table_1` where `category_id`=2 order by `id` DESC limit 0,5;

+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+

| 1 | SIMPLE | table_1 | ref | category_id | category_id | 4 | const | 18 | Using where |

+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+

1 row in set

也就是 where category_id=2 涉及行数成了 category_id=2 记录的总数！也就是 18 条

那么如果数据库中有1千万条数据，均分至 category_id 1-10 的话，这时候需要执行：

select * from `table_1` where `category_id`=2 order by `id` DESC limit 0,5;

是否需要建立 category_id 索引呢？如果建立每次都要扫描 100 万条索引记录吗？如果不建立任何索引，该 SQL 是否会存在性能问题？

相关阅读:

lumen controller 控制器问题

在U盘上安装Linux系统，存在多个分区如何正确引导？

td右对齐，然后整列如何设置居中对齐？

python 无法导入nltk

关于不同类型数据库之间同步数据问题，谢谢！

怎么样手机上的页面的主体高度根据屏幕高度自适应呢？

uiscrollview上子视图的约束问题

php在服务器端组合静态页面时，多个html的组合会影响效率吗？

Win 下 Gvim 的 nerd_tree 树，怎么切换盘符？

svn迁移到git，分支没了？

drupal设置主菜单路径不成功

两个不同的页面之间的window对象之间如何通信

angularjs什么时候渲染model变量？

求大神 输入in时无反应 Do_command()中有毒啊

spring中的interceptor和aop中的advice 有啥关系？

c++中关于列表初始化vector的问题

ios开发 如何在应用内获取当前周围wifi列表

关于js的问题

vue表单验证问题

python处理excel的问题