mysql ：select查询字段会导致索引失效吗？

Question

我有一条sql ，不知道为什么会导致索引失效

SELECT  dl.`user_type` AS userType,  dl.`user_name` AS userName,  dl.`phone` AS userPhone,  pc.`name` AS courtName,  dl.`build_unit` AS buildName,  dl.`dev_name` AS deviceName,  dl.`dev_no` AS deviceNo,  dl.`create_time` AS openTime,  dl.`status`FROM  dev_device_log dl  LEFT JOIN base_project_court pc    ON dl.`court_id` = pc.`id`ORDER BY dl.`id` DESCLIMIT 0, 100;

当我注释掉 select 后面的 pc.name 字段就可以使用索引。

不知道为什么，有没有大佬给我解惑一下。

测试环境中dev_device_log表中只有几百条数据的时候可以走索引，但是我从生产环境随意导出了几万条数据后就无法使用索引了。
不太理解为什么select 后面的字段会影响到索引情况
表结构：

CREATE TABLE `base_project_court` (  `id` varchar(32) NOT NULL COMMENT '主键id',  `community_id` varchar(32) NOT NULL COMMENT '项目id',  `name` varchar(30) NOT NULL COMMENT '小区名',  `py_name` varchar(120) NOT NULL COMMENT '拼音名',  `logo` varchar(300) DEFAULT NULL COMMENT '小区图标',  `address` varchar(255) DEFAULT NULL COMMENT '小区地址',  `longitude` decimal(10,6) DEFAULT NULL COMMENT '经度（-180,180）',  `latitude` decimal(9,6) DEFAULT NULL COMMENT '纬度（-90,90）',  `status` char(1) NOT NULL DEFAULT '1' COMMENT '状态（1.启用 0.禁用）',  `service_phone` varchar(20) DEFAULT NULL COMMENT '联系电话',  `order_no` smallint(4) NOT NULL DEFAULT '1' COMMENT '排序',  `del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '是否删除',  `create_user` varchar(32) NOT NULL COMMENT '创建人',  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  `update_user` varchar(32) NOT NULL COMMENT '更新人',  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目小区';CREATE TABLE `dev_device_log` (  `id` bigint(20) NOT NULL COMMENT 'id',  `community_id` varchar(32) NOT NULL DEFAULT '' COMMENT '项目id',  `court_id` varchar(32) NOT NULL DEFAULT '' COMMENT '小区id',  `build_id` varchar(32) NOT NULL DEFAULT '' COMMENT '楼栋单元id',  `build_unit` varchar(30) NOT NULL DEFAULT '' COMMENT '楼栋单元',  `user_id` varchar(32) NOT NULL DEFAULT '' COMMENT '用户id',  `user_name` varchar(32) NOT NULL DEFAULT '' COMMENT '用户名',  `phone` varchar(15) NOT NULL DEFAULT '' COMMENT '手机号',  `user_type` tinyint(2) NOT NULL DEFAULT '1' COMMENT '用户类型（1.住户 2.物业）',  `device_id` varchar(32) NOT NULL DEFAULT '' COMMENT '设备id',  `dev_name` varchar(32) NOT NULL DEFAULT '' COMMENT '门禁名称',  `dev_no` varchar(10) NOT NULL DEFAULT '' COMMENT '设备编号',  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态（1.成功 0.失败）',  `failure_description` varchar(120) NOT NULL DEFAULT '' COMMENT '失败说明',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我导出了一份几万条脱敏后的数据，但是不知道怎么传上来

我把sql传蓝奏云了，由于蓝奏云不知道上传.sql文件，我先压缩了一个zip包
sql文件

Answer 1

后面这个 Using index 是表示是用了到了 “覆盖索引”。

从 key 字段可以看出来，使用到了主键索引，然后你的主键是 varchar(32) ，字符集是 utf8mb4，也就是一个字符占用 4 字节。 32 * 4+ varchar 的前缀长度，也就成了 key_len 里面的 130 了。

在 MySQL 查询数据时，因为每次获取数据都有一个 回表的过程（先找到主键，再去找具体的数据行，你这里就是要去找 pc.name ），这样 MySQL 的优化器就会认为，这样效率太低了，还不如直接全表查询，免得先查询一次主键。

所以当你不获取 pc.name 时，仅仅使用主键索引就足以找到数据了（但是这样在这里的 left join，没有其他筛选的情况下，就有没啥意义了）

我看你是用的 MySQL 5.7，我这里 8.0 ，给 id 和 name 添加一个索引后，也是可以走到索引覆盖的，虽然显示是走了主键。

alter table base_project_court add index idx_id_name (id, name);

Answer 2

答案

在SQL查询中，SELECT后面列出的字段本身并不会直接导致索引失效。索引是否被使用取决于多个因素，包括查询的写法、表的统计信息、索引的选择性、数据库优化器的决策等。

在你提供的例子中，dev_device_log表通过LEFT JOIN与base_project_court表连接，连接条件是dl.court_id = pc.id。当你包含pc.name字段在SELECT语句中时，数据库优化器可能会认为通过连接获取这个字段的代价较高，特别是当pc.name字段不是base_project_court表上的索引列时。因此，优化器可能选择不使用dev_device_log表上的索引，而是进行全表扫描，然后再与base_project_court表进行连接操作。

然而，在你提到的测试环境中，当dev_device_log表中只有几百条数据时，优化器可能认为全表扫描的开销并不大，所以即使连接了base_project_court表，仍然可以使用索引。但在生产环境中，当数据量增加到几万条时，优化器可能做出了不同的决策，认为全表扫描加上连接操作的成本更低，因此不使用索引。

此外，即使pc.name字段被包含在SELECT语句中，也不意味着dev_device_log表上的索引一定不会被使用。这取决于具体的查询计划，而查询计划可能会随着数据量的变化、统计信息的更新以及数据库版本的差异而有所不同。

为了确定为什么索引没有被使用，你可以查看查询的执行计划（在MySQL中可以使用EXPLAIN语句）。执行计划会显示数据库优化器如何决定执行查询，包括是否使用了索引、扫描了多少行数据等信息。通过比较不同情况下的执行计划，你可以更好地理解为什么在某些情况下索引会失效。

最后，要注意的是，即使执行计划显示没有使用索引，也不一定意味着索引本身有问题或者没有用处。在某些情况下，全表扫描可能比使用索引更高效。因此，在优化查询性能时，需要综合考虑多种因素，并可能需要进行多次测试和调整。