MySQL索引失效场景

二哈与木鱼
267 阅读2分钟

MySQL索引失效

又到了金三银四的日子，面试八股文必不可少，而数据库索引是基础中的基础，也是重中之重，以下列举了一些InnoDB存储引擎下常见的索引失效场景问题，供各位参考

索引失效的场景

where条件下索引失效

  1. 使用了关键字or
  2. 索引字段使用了关键字like，且以 %开头去匹配数据
  3. 索引字段使用了负向查询，亲测，如：!=、<>、not in
  4. 索引字段使用了表达式/运算，如：select name, age from user where age + 1 = 18;
  5. 索引字段发生了类型的（隐式）转换，如：使用字符串去匹配 int 类型的索引字段

其他

  1. 复合索引中，没有满足最左前缀原则，即匹配时需能匹配最左边开始的索引列的值
  2. 索引字段的数据重复性比较强，也就意味着即使使用索引，对效率的提升也不大
  3. MySQL优化器认为全表扫描更快时，如：数据少的情况下

不建议使用索引的场景

  1. 字段的唯一性差
  2. 随机无序的字段，如：主键索引使用uuid，会造成空间浪费、读写效率的下降、存储的碎片化等问题，详情看不要把随机无序ID作为主键，当然这块根据具体需求业务而定，就其本身而言使用uuid也可以避免开发过程中的问题
  3. 频繁更新的字段，会导致更新索引频繁，消耗性能
  4. where中不用的字段，即查询条件中不会使用到的字段
  5. 仅使用负向查询的字段，某些存储引擎下还会失效，比如：InnoDB
  6. 一个字段索引创建在复合索引中第一个后，不建议再单独建立此字段的索引，因为复合索引建立后的效果是这样的：index(A, B, C) =+ index(A) + index(A, B) + index(A, B, C)

索引优化注意事项

  1. 创建索引的列，不要出现null值，可能会得到不符合预期的结果
  2. 单表索引建议控制在5个以内
  3. 单索引字段数不允许超过5个（组合索引）
  4. 针对blob、text、很长的varchar等类型的字段，可以使用前缀索引，mysql不允许索引这些列的完整长度，使用前缀索引关键在于要选择足够长的前缀来保证较高的选择性，但又不能太长（以便节约空间，提高索引效率），以使得前缀索引的选择性接近于索引整个列。如何建立前缀索引：alter table 表名 add key (索引字段(前缀长度));，关于如何选择前缀长度请看这篇文章：www.cnblogs.com/balfish/p/9…

如何验证索引失效场景

插入数据

-- 1、建表sql
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` varchar(20) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4002 DEFAULT CHARSET=utf8mb4;

-- 2、使用存储过程插入数据，每次批量插入1000条，可多次执行
DROP PROCEDURE
IF
    EXISTS initData;
DELIMITER $
CREATE PROCEDURE initData ( ) BEGIN
    DECLARE
        i INT DEFAULT 1;
    WHILE
        i <= 1000 DO
        INSERT INTO `user` ( `name`, `age`, `gender`, `description`, `create_time`, `update_time` )
        VALUES
                ( concat( '周瑜', i ), 28, '男', "既生瑜何生亮！！", now( ), now( ) );
        SET i = i + 1;
	END WHILE;
        
END $ CALL initData ( );

-- 增删索引
-- 新建一般索引
alter table `user` add index `name`(`name`) ; 
-- 删除一般索引
drop index index_name on `user`;

使用查询计划

-- 查看not in是否使用索引，结果显示未使用索引
explain select id,`name`,age,description,create_time from `user` where `name` not in( '周瑜1');

image-20220227151655210.png 参数说明：

  • SIMPLE：简单的查询，不包含子查询和union（联合查询）等
  • user：正在访问的表名 或 别名
  • ALL：访问类型为ALL，即全表扫描
  • name：显示可能应用在该表中的索引名称，一个或多个，查询涉及到的字段上若存在索引，则该索引会被列出，但不一定被使用了
  • key中存放是的是实际使用的索引，此处为null表示未使用索引
  • key_len表示索引使用的字节长度，如：int为4字节，在不损失精度的情况下长度越短越好
  • 4177：根据表的统计信息、索引使用情况，大致估算出找出所需记录需要读取的行数，直接反映出sql找了多少数据，此值越小越好
  • …… 有兴趣可以研究下这些字段参数（官网没找到这块内容，有知道的小伙伴可以分享一下）：www.cnblogs.com/tufujie/p/9…

参考链接

avatar
文章
阅读
粉丝