美文网首页
mysql范围查询索引失效的情况演示

mysql范围查询索引失效的情况演示

作者: 不知不怪 | 来源:发表于2022-02-24 01:15 被阅读0次
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` int NOT NULL,
      `name` varchar(40),
      `age` int,
      `address` varchar(50),
      `deleted` tinyint,
      `created` datetime,
      PRIMARY KEY (`id`),
      INDEX `idx_name_age_address`(`name` ASC, `age` ASC, `address` ASC) 
    ) ENGINE = InnoDB;
    
    INSERT INTO `user` VALUES (1, '5', 1000, '花果山', 0, '2022-02-24 00:42:01');
    INSERT INTO `user` VALUES (2, '8', 800, '高老庄', 0, '2022-02-24 00:42:04');
    INSERT INTO `user` VALUES (3, '3', 25, '长安', 0, '2022-02-24 00:42:06');
    INSERT INTO `user` VALUES (4, '白龙马', 700, '龙宫', 0, '2022-02-24 00:42:09');
    
    -- SELECT * FROM user; 
    -- ;SHOW INDEX FROM user
    

    分别执行如下语句观察所引长度字段即可得出结论


    image.png
    EXPLAIN SELECT * FROM user WHERE name = '5' AND age = 1 AND address = 'nihao';
    EXPLAIN SELECT * FROM user WHERE name = '5';
    EXPLAIN SELECT * FROM user WHERE name = '5' AND age = 1;
    EXPLAIN SELECT * FROM user WHERE name = '5' AND age = 1 AND address = '天津';
    EXPLAIN SELECT * FROM user WHERE age = 1 AND address = 'nihao';
    EXPLAIN SELECT * FROM user WHERE name = '5' AND address = 'nihao';
    EXPLAIN SELECT * FROM user WHERE name = '5' AND age > 1 AND address = '天津';
    EXPLAIN SELECT * FROM user WHERE name = '5' AND age > 1 AND age < 5 AND address = '天津';
    EXPLAIN SELECT * FROM user WHERE name LIKE '5%' AND age = 1 AND address = '天津';
    EXPLAIN SELECT * FROM user WHERE name LIKE '5' AND age = 1 AND address = '天津';
    EXPLAIN SELECT * FROM user WHERE name LIKE '%5' AND age = 1 AND address = '天津';
    EXPLAIN SELECT * FROM user WHERE name LIKE '%5%' AND age = 1 AND address = '天津';
    

    相关文章

      网友评论

          本文标题:mysql范围查询索引失效的情况演示

          本文链接:https://www.haomeiwen.com/subject/ynaalrtx.html