Mysql索引知识整理

最新推荐文章于 2024-04-04 04:36:20 发布

自东向西

最新推荐文章于 2024-04-04 04:36:20 发布

阅读量225

点赞数 1

分类专栏： Mysql 知识整理文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/zidongxiangxi/article/details/113389587

版权

知识整理同时被 2 个专栏收录

11 篇文章 1 订阅

订阅专栏

Mysql

3 篇文章 0 订阅

订阅专栏

背景

说道mysql，大家第一个想到的就是它的索引，基本也都知道索引的结构是B+Tree，但是并没有把它的结构和我们看到的原则关联起来。

例如最左匹配原则，不要使用uuid作为主键，哪些查询条件无法使用索引…

B+Tree

B+Tree在这里就不做介绍了，直接上图：
在这里插入图片描述
其实就是一个“多路平衡树”，底层叶子节点存储了行数据，叶子节点之间串联起来，形成一个链表。

关于索引的介绍，可以看这篇文章《深入理解MySQL索引之B+Tree》，内容适合刚接触mysql索引的同学，少有的把MyISAM和InnoDB引擎都讲了的文章。上面的图也是从这个文章抄的。

需要注意：标准的B+Tree，叶子节点形成的链表是单向的，但是在mysql中是双向的！
原因可以看一下这篇文章《MySQL数据库中innodb引擎的B+Tree的底部到底时单向链表还是双向链表？》

大概的意思就是：

mysql存储用的是以页为单位的，页是有两个指针，一个指向上一页（Previous Page），一个指向下一页（Next Page）。
叶子节点也是由N个页组成的，节点的第一个页的Previous Page指向上一个叶子节点的最后一个页；节点的最后一个页的Next Page指向的是下一个叶子节点的第一个页。
双向链表，在范围查询的时候，更方便，例如<=查找的时候，可以找到相等的值的叶子节点，然后从向前遍历。

提供一个可以体验B+Tree插入删除过程的工具：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

聚簇索引&辅助索引

在《深入理解MySQL索引之B+Tree》有讲解这两者，在这里只是偷一下图，用于接下来的实例分析。
在这里插入图片描述

只关注InnoDB的话，可以总结：
主键索引为聚集索引，聚集索引的而叶子节点存放的是真实的行数据，主键查询可以直接通过叶子节点直接拿到行数据；辅助索引叶子节点存储的只是主键值，需要使用主键去聚集索引查询真正的行数据，所以也被叫做“二级索引”。

InnoDB索引失效

我们平时说到的索引失效，都是指“辅助索引”。对于主键索引——聚集索引，索引即数据，全表扫描扫可以说扫的就是聚集索引，没有所谓的失效。
个人觉得索引失效的原因有两个：

mysql优化器认为使用索引的成本比全表扫描高——大多数情况下是返表数据量大
无法在索引中比较
这有点像在说废话哈，这一点大家都知道。重点是怎样，才会认为使用辅助索引的成本比全表扫描高！！！

从上一节，我们可以知道，辅助索引的叶子节点存储的只是行数据的主键，需要查询行的完整数据，得再用主键去聚集索引中查询——这个过程也就是“返表”。这一步是一个耗性能的操作，如果查完辅助索引之后，需要返表的数据很多，那么总的开销可能就会超过全表扫描。

接下来，我们用公司的一张表bis_mapping_customer来对一些网上文章常提到的失效场景来做验证，上贴提下表的建表语句：

CREATE TABLE `bis_mapping_customer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关系id',
  `app_id` varchar(50) NOT NULL COMMENT '第三方应用id.',
  `developer_brand_id` bigint(20) DEFAULT NULL COMMENT '开发商品牌id',
  `identity_id` varchar(50) DEFAULT NULL COMMENT '系统身份id',
  `app_project_id` varchar(100) NOT NULL COMMENT '第三方项目id',
  `app_customer_id` varchar(100) DEFAULT NULL COMMENT '第三方客户id, 如果报备失败，客户id为NULL',
  `agent_id` bigint(20) unsigned NOT NULL COMMENT '经纪人ID',
  `project_id` bigint(20) unsigned NOT NULL COMMENT '项目ID',
  `estate_id` bigint(20) unsigned NOT NULL COMMENT '楼盘ID',
  `referral_id` bigint(20) unsigned NOT NULL COMMENT '报备ID',
  `customer_phone` varchar(20) NOT NULL COMMENT '客户电话',
  `status` int(11) NOT NULL COMMENT '业务状态',
  `remark` varchar(1000) DEFAULT NULL COMMENT '备注信息',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `IDX_REFERRAL_ID` (`referral_id`),
  KEY `IDX_CUSTOMER_ID` (`app_customer_id`),
  KEY `IDX_APP_CUSTOMER` (`app_id`,`app_customer_id`),
  KEY `IDX_PHONE` (`customer_phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户映射关系表'

bis_mapping_customer的数据数量是2358。
在这里插入图片描述

模糊like

关于like走不走索引的问题，很多人应该会这样回答，“%在前，不走索引；%号在后，走索引”。让我们用customer_phone字段来做实验。
explain select * from bis_mapping_customer where customer_phone like ‘158%’;
在这里插入图片描述
explain select * from bis_mapping_customer where customer_phone like ‘1%’;

当like的内容是’158%'的时候，possible_keys是IDX_PHONE，最终也选择了IDX_PHONE索引。
当like的内容是’1%'的时候，possible_keys是IDX_PHONE，最终没有选择任何索引，而是直接全表扫描，然后where过滤。
为什么呢？
重点在rows那里，我们知道bis_mapping_customer总的数据量是2358，符合‘158%’格式的数据只有119，而符合‘1%’格式的有2317。
我从辅助索引过滤剩下2137条数据，再把这2137条数据返表去查询数据，这消耗肯定比直接全表扫描来的大！

这两个例子，验证了mysql优化器认为使用索引的成本比全表扫描高的失效原因。

select * from bis_mapping_customer where customer_phone like ‘%56789’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210129152132486.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ppZG9uZ3hpYW5neGk=,size_16,color_FFFFFF,t_70
可以看到，like '%56789’条件查询出来的数据只会有一行，绝对不属于“返表数据量大”。但是它的执行计划，依然是走全表。
在这里插入图片描述
从这个执行计划看，IDX_PHONE连在possible_keys都没出现。
为什么呢？
使用索引，没法比较，没法找目标数据。
我们在一个有序的平衡树里，找数据，是怎么找的？拿着目标值，从根节点开始比较，比根节点小，进入左子树比较；比跟节点大，进入右子树比较；相同，则结束查找；一直这样递归，知道命中或者到达叶子节点。
like ‘%56789’，%在前面，不是确定的内容，所以无法进行比较。
like ‘158%’，这个查询，158是固定的。可以用158在IDX_PHONE索引中找到以158开头且最小的叶子节点，然后通过叶子节点的链表往后扫描，直到第一个不是158开通的地方停止。

这个例子，验证了无法在索引中比较的失效原因。

范围>、>=、<、<=

explain select * from bis_mapping_customer where referral_id>1000;
在这里插入图片描述
explain select * from bis_mapping_customer where referral_id>1000000;

当条件是>1000的时候，不会使用IDX_REFERRAL_ID，因为比1000大的数量预计有2317个，直接走全表扫描划算。
当条件是>1000000的时候，使用了IDX_REFERRAL_ID，因为比1000000大的，只有8个，找到这8个主键，再返回回去拿数据，更快。
依然是mysql优化器认为使用索引的成本比全表扫描高。
其他的查询条件>=、<、<=道理一样

这里引入一个思考，如果我把语句改成这样：select * from bis_mapping_customer where referral_id>1000 order by referral_id asc limit 20;
这样的语句能不能走索引？这个语句目标数据量只是20行，是不是就不会出现返表数据量过大的问题？可以思考下，答案是不能走，原因在 sql语句中各子部分的执行顺序 中。

空IS NULL、IS NOT NULL

大部分人，都觉得null值，是不会被放到索引中的。所以IS NULL和IS NOT NULL都不会走索引。猜测可能的想法是这样：

null值没放到索引里，那么IS NULL根本就没办法在索引里找到对应的行的主键
索引里放的都是非空的值，那么IS NOT NULL相当于把整个服务索引里的主键都再回表一次，比直接全表扫描更耗性能
首先，需要强调一点，NULL值是会放到索引里的，会被保存在索引的最左端，所以IS NULL可以走索引，优化器走不走，就得数据量来判断。参考的文章《MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！》

IS NOT NULL的话，也是看数量，如果你有一个表2000个数据，1900行中的字段是null，那么IS NOT NULL是能走索引的。
同一个字段的索引，IS NULL和IS NOT NULL走索引是互斥的。你的NULL数据少了，那NOT NULL的数据就多，二选一。

不等<>、!=

绝大多数情况下，!=是不能走索引的，但不是绝对，只要重复的数据够多，是能走索引的，举个生产数据的例子：
select app_id, count(*) from bis_mapping_customer group by app_id;
在这里插入图片描述
可以看到，大部分数据都是集中在1001和1004。
用这一条语句 select * from bis_mapping_customer where app_id!=‘1001’ and app_id!=‘1004’;

从执行计划可以看到，是走了IDX_APP_CUSTOMER。
大致的原因就是，!=，其实也是一种比较，它可以按=来查找数据，找到目标所在的叶子节点了，再往两边遍历，所以不符合“无法在索引中比较”，只要数据量小，是能走的。

联合索引——最左匹配原则

例如bis_mapping_customer中的IDX_APP_CUSTOMER索引，就是app_id和app_customer_id的联合索引。如果有没IDX_CUSTOMER_ID索引，我们执行
select * from bis_mapping_customer where app_customer_id=xxx的查询，是不会走索引的。（在oracle里，情况会不一样，oracle支持跳跃扫描）
可以粗暴的把联合索引IDX_APP_CUSTOMER看成‘app_id+app_customer_id‘，我们直接查app_customer_id=xxx，跟我们用like '%xxx’查询一个字段的性质差不多，前缀不确定，所以不会在索引中进行比较。

数据类型不匹配

explain select * from bis_mapping_customer where app_id=‘1002’;
在这里插入图片描述
explain select * from bis_mapping_customer where app_id=1002;

app_id的类型是varchar，我们传数字的1002，虽然能隐式的进行类型转换，出来正确的结果，但没有走索引，而是全表扫描。
这也比较好理解，int没办法和varchar的内容进行比较，所以不会都索引。

或查询OR

or的两端的条件都要是能走索引的，整个or查询才能走索引！原因是mysql优化器认为使用索引的成本比全表扫描高。
或，其实就是两边条件能筛选出来的数据的并集。
如果有一遍的条件不能走索引，那么mysql就至少要走一次全表扫描。都已经需要扫描全表了，为什么还要一遍走索引，再来返表，直接全表扫描，where过滤不是更支持和省事。

总结

在判断一个查询语句能不能走索引的时候，想一下索引的结构，先判断查询是不是能在索引中进行比较，如果能，再判断走索引是不是会比全表扫描成本低——走索引后，有大量的数据需要返表。

其他知识

sql语句中各子部分的执行顺序

在这里插入图片描述
有兴趣了解的同学，可以看看《sql语句中各子部分的执行顺序》和《MySQL 基础 —— SQL语句的执行顺序与 LIMIT 子句》，第一个里没有提到limit，所以找多一篇有LIMIT的，补充下。

不推荐UUID和雪花算法作为主键，建议使用自增

可以用https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html这个工具去体验一下插入非自增的主键的过程。
不断的插入数据，会导致某个节点的数据超过了最大值，这个时候就会产生节点的分裂。
节点的分裂可能会造成一种连锁反应，我分裂了，导致我的父节点也得分裂，我的父节点的父节点也跟着分裂…这种情况，容易发生在往索引的中间插入数据的情况。
使用uuid做为主键，插入的位置是随机的，这就出现在索引中间插入数据的情况。
使用自增主键的话，所有的插入动作都只发生在索引的最右边，降低了节点分裂的影响。

题外话：在分布式数据库，像HBASE里，建议使用UUID，把流量分散到各个机器，提高整个集群的吞吐量。

一个表不要建太多索引

插入和修改数据的时候，需要插入和修改索引数据，索引太多的话，会影响性能，尽量是拓展索引，而不是新增索引。
例如：
KEY IDX_CUSTOMER_ID (app_customer_id),
KEY IDX_APP_CUSTOMER (app_id,app_customer_id),
如果联合索引建成KEY IDX_CUSTOMER_APP (app_customer_id,app_id)，那么IDX_CUSTOMER_ID就没必要建了。根据最左匹配原则，IDX_CUSTOMER_APP能代替IDX_CUSTOMER_ID。

索引覆盖

不是啥高大上的东西，一句话概括：索引里包含了select语句中查询的字段，不需要返回表。
explain select id, app_customer_id from bis_mapping_customer where app_customer_id=‘222’;
在这里插入图片描述
这个的重点是辅助索引上，都存了什么信息，像IDX_CUSTOMER_ID，索引字段本身的值肯定是有的，需要返表，所以叶子节点还存了主键。如果select的字段只有这两者，就只需要扫描索引。

左旋右旋

这个东西，在mysql里是不存在的。因为对mysql来说，索引只有插入，没有修改和删除。
删除一行数据的时候，对应辅助索引中的叶子节点只是会被标记为删除，而不是真正的删除对应的数据。
修改加了索引的字段的值，会把旧的叶子节点标记为删除，再插入新的索引项。（这也是不建议在易变的字段上加索引的原因）
所以，mysql的索引只会越来越大，太大了，就分裂~
这里还涉及到一个知识点，如果索引太稀疏，mysql是可能会去重建的，重建之后节省空间，查询效率更高。

自东向西

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql索引知识整理

背景说道mysql，大家第一个想到的就是它的索引，基本也都知道索引的结构是B+Tree，但是并没有把它的结构和我们看到的原则关联起来。例如最左匹配原则，不要使用uuid作为主键，哪些查询条件无法使用索引…B+TreeB+Tree在这里就不做介绍了，直接上图：其实就是一个“多路平衡树”，底层叶子节点存储了行数据，叶子节点之间串联起来，形成一个链表。关于索引的介绍，可以看这篇文章《深入理解MySQL索引之B+Tree》，内容适合刚接触mysql索引的同学，少有的把MyISAM和InnoDB引擎都讲
复制链接

扫一扫