mysql 索引不命中

奔波儿霸
98 阅读2分钟
  • 有一张表如下
 CREATE TABLE `ap_sign_up` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `activity_id` bigint NOT NULL COMMENT '活动id',
  `user_id` bigint NOT NULL COMMENT '用户id',
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_activity_id_user_id` (`activity_id`,`user_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_activity_id_id` (`activity_id`,`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=20966 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='报名信息';
  • 执行如下计划，并传入不同的活动id,命中的索引有的时候是primary，有的时候是idx_activity_id_id
explain  select user_id,sign_up_time,create_time 
from  sign_up_time
where  activity_id = 5002230 
order by id asc 
limit 100
解决方案
  • force index 强制索引
  • 走覆盖索引
索引不能命中的场景
  • 索引字段的数据类型不一致
  • 模糊匹配使用前置通配符 %xxx
  • 太宽的索引
  • 数据分布不均匀，mysql可能使用全表扫描
mysql优化器在选择索引的时候的规则

  • 索引成本估算(访问的行数、扫描索引的代价、排序、连接)

    假设有一个简单的查询：SELECT * FROM users WHERE age > 30，并且有两个索引可供选择：一个是在 age 列上的索引，另一个是在 (age, name) 列上的复合索引。如果用户表中的大多数行都满足 age > 30，那么优化器可能会选择不使用索引，因为全表扫描的成本可能比使用索引更低

  • 索引的区分度 不同值的数量与表总行数的比例

    假设有一个包含性别列的索引，而表中有 10000 条记录，其中 9000 条记录是男性，1000 条记录是女性。这个索引的选择性很低，因为大部分记录都具有相同的值。相比之下，如果索引列是用户的唯一 ID，那么每个值都是唯一的，选择性就很高。

  • 索引的覆盖能力 减少回表

    考虑一个查询：SELECT age FROM users WHERE age > 30，并且有一个仅包含 age 列的索引。如果这个索引足够覆盖查询的所有需要，MySQL 就可以直接使用索引来执行查询，而不需要访问实际的表数据。

  • 索引的大小和内存占用

    假设有两个索引可供选择，一个是在一个整数列上的索引，另一个是在一个大的文本列上的索引。由于文本索引的大小通常比整数索引大得多，因此整数索引可能更容易加载到内存中，从而更有可能被选择。

总结：索引能否命中还要看优化器的索引选择

avatar
文章
阅读
粉丝