mysql 联合索引失效的问题?

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL DEFAULT '',
  `balance` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`),
  KEY `idx_name_balance` (`name`,`balance`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

INSERT INTO `users` VALUES (1, '张三', 7031151, 5500, '2019-07-18 11:03:13', '2019-08-12 16:27:23');
INSERT INTO `users` VALUES (2, '李四', 7031151, 1000, '2019-07-18 11:03:24', '2019-07-18 11:08:52');
INSERT INTO `users` VALUES (3, 'ccc', 7031151, 4561, '2019-08-09 18:27:03', '2019-08-09 18:27:03');
INSERT INTO `users` VALUES (6, 'ccc', 7031151, 4561, '2019-08-12 15:19:01', '2019-08-12 15:19:01');
  1. 如下会用到联合索引的两个字段
EXPLAIN SELECT
    name,balance
FROM
    users 
WHERE
    `name` = "haha" 
and 
    balance=123

image.png

  1. 但是执行如下sql, 只用到了联合索引中的第一个字段, 第二个字段却没有用到, 这是咋回事儿
EXPLAIN SELECT
    name,balance
FROM
    users 
WHERE
    `name` = "haha" 
order by  
    balance desc

image.png

阅读 2.2k
1 个回答

都是ref,key都是对应的索引,哪来的组合索引失效?

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题