MySQL索引和explain学习(二)

107 阅读2分钟

MySQL索引和explain学习(二)

在上一次学习mysql索引和explain后,又观看了一些大佬的视频,补充之前一些遗忘的内容和可能有误的知识点

表结构

CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

为什么有时候select * from demo会用到index_age这个索引呢?

image-20210818223219986

image-20210818223445218

1.为什么会这样呢,第一张图选择走了age这列索引呢?先申明一点2级索引的大小是远小于主键索引树的大小的。又因为index_age这个索引包含整个表的数据,
id和age嘛,所以才有了explain后key这列和type这列的情况。
2.第二张图中的表在图一的基础上了name一列,索引没改变。那为啥它全表扫描了呢,因为单纯的index_age这个索引树上没有name列的值,你终究还得回表,
所以干脆直接到主键索引树的叶子节点一个一个吧遍历数据了。

之前说的索引失效情况太绝对了,大多数情况是那样的,在数据量大或者小可能是俩种不同的表现。

image-20210818224908793

1.比如说的not nullnullinnot in他在数据量大或者小都有可能有不同的表现,涉及到mysql内部的优化和判断。
null的数据会找个地方放在一起(看视频得到的消息,待验证)

在查询中非要用前置like的模糊查询呢,应该如何优化呢?

image-20210818225103687

image-20210818225240863

答案:索引覆盖

范围查询索引失效排查方法

明明where后是有索引列>1 and 索引列<100000000这样正常使索引生效的语句,但在用explain执行时,却发现没有走索引,此时可以缩小范围逐步验证索引生效的
情况,最有可能就是因为数据量太大导致mysql认为直接全部扫描可能比走索引快些

where总结图

image-20210818225310326

key_len的长度计算

image-20210819073639518

视频笔记

欢迎大家访问我的个人小站www.chenmx.net