运维首页 > 运维资讯 > MySQL索引相关大合集

MySQL索引相关大合集

2023年 12月 11日 187.2k 0

前言

索引是一种用于快速查询检索数据的一种数据结构，可以认为是一种排好序的解决结构，任何事情只要安排好顺序，那么对于查找都是事半功倍的。索引也是一种思想，我们是生活中的很多方面都是用到了索引的思想，比如我们查字典，a-z，图书馆找书也是，所以索引对于 MySQL 来说是十分重要的。

索引的优缺点

虽然索引十分重要，但是我们也是要明白它的一些优缺点。

优点： 首当其冲的就是大大加快检索速度，同时如果创建唯一索引也可以保证数据的唯一性。

缺点： 在数据量很少的情况下，索引的效率不一定高，同时创建索引和维护索引都会带来负担，修改数据的同时也要维护索引，所以索引不是越多越好，贵精不贵多。

索引的数据结构分析

那么选择什么样的索引数据结构就是非常重要的了，而我们所熟知的那些数据结构中有很多是可以排除的，比如数组，链表这种线性的数据结构，和逐行查找没什么区别，下面我来介绍，然后分析一下不同数据结构的索引会有什么样的优缺点，以及 MySQL 选择索引数据结构的原因。

hash

hash 是我们非常常见的一个数据结构，通过 hash 算法计算坐标，然后进行链式存储，或者红黑树结构，当然这个是 HashMap 中的方式，在 InnoDB 中默认是不支持 hash 结构的，但是支持一种自适应 hash 索引，也就是将每个 hash 桶存储一个小型 B+ 树，让 B+ 树存储多个键值对，从而提升索引效率，但是为什么 MySQL 不使用 hash 作为索引的数据结构呢？

很关键的一点，索引需要有序！！hash 是没办法保证顺序的，取 1 亿个数据难道要计算 1 亿次hash？ 这显然不是我们想要看到的。

二叉树

二叉树有很多中，我们一个一个看。有很多的特性我之前在我的 HashMap 的文章中已经说过了，给大家个链接吧。HashMap很细的分析--令人发指

二叉查找树

二叉查找树，也是最普通的二叉树，极端情况下就是瘸腿树，和链表没啥区别，肯定是不选的。

AVL 树

自平衡二叉查找树，特点是保证任何节点的左右子树的高度差不超过 1，否则就进行平衡算法修正，所以也称之为高度平衡二叉树，所以带来的问题就是需要大量的平衡运算，会很大程度的降低服务性能，而且每次查询都只能查到一个节点，如果需要多次，那么就进行多次磁盘 IO ，所以我们对索引的要求要顺序，不能频繁计算维护索引，还要最小程度的减少磁盘 IO。

红黑树

红黑树相对于 AVL 树来说不需要绝对的平衡，所以会减少很多平衡运算，也正因为这一点，所以会导致红黑树较高，那么就代表着磁盘 IO 次数的增加，但是对于内存来说是无所屌谓的，所以在 HashMap 的数据结构中大放异彩，但是对于磁盘来说，显然也不是最理想的。

B 树和 B+树

那么基于上面几种数据结构的介绍，这两个树就出来了。所以我们一定知道这两个树的特点就是磁盘IO少，维护索引的运算少。

B 树也叫做 B-树，称之为多路平衡搜索树，那么也就意味着不是二叉结构，一个节点的子节点可以是很多，在 B 树中，每一个节点既存放key，也存放数据，给大家来个图，不要被我的图误导，不是二叉结构

5a4239265147382819d00dae6823159a.jpeg

再来一个 B+树的图

101d5429261b2e9da7175692d1442f76.png

从两个图就能很清晰的看到区别。

  • B 树的每个节点都存放数据，B+树只有叶子节点存放数据，所以B+树的非叶子节点就能存更多的数据。
  • B 树的每个节点都是独立的，而 B+ 树 的叶子节点都有指向下一个相邻节点的指针，这对于顺序的查找是非常有利的。所以范围查找来说，B 树必须找到下限的节点，然后进行中序遍历。但是 B+树就可以通过链表进行遍历查找。

    • 推算B+树数据量

    以主键为例子，在B+ 树中每一个非叶子节点存放的是主键键值对和指针，指针大小为 6 个字节，主键我们以 8 个字节为例，在 InnoDB 中每一个数据页是 16 kb，那么大约一个非叶子节点就能存16*1024/14 = 1170 个，假设三层树高，那么第二层就是 1170*1170 = 1368900，而第三层也就差不多这么多页，一页 16kb，假设一个数据 1kb，那么一个数据页有 16 行数据，那么总共就是 1368900*16=21902400，大约是 2000万的数据，这就是估算，而再多可能就涉及到增加树高，一旦增加树高就代表着需要增加一次磁盘 IO，而且这个是成指数级增加的数据量，经过实践，这已经可以算是峰值了。

    索引类型介绍

    从上面的讲解已经清楚了索引的数据结构，那么接下来就说一下索引的一些类型。

    主键索引&聚簇索引

    主键索引，也就是以主键构建的索引，属于聚簇索引的一种，聚簇索引是必然存在的，他是 MySQL 构建数据的基础，当一张表没有主键的时候会使用唯一索引构建聚簇索引，当唯一索引也没有的时候，会使用一个 6 字节大小的整型字段作为主键然后构建聚簇索引。

    主键是不能 null 的，也不能重复的，所以主键所以是查询效率最高的，而且会保证数据的顺序，对于范围查找是非常友好的，也正是因为这个原因，所以我们对主键数据的选择就非常的考究，整型的字段来说相对友好，如果是那种无规则的 uuid 就非常的痛苦，需要对数据进行排序，然后插入，甚至还会涉及到数据的调整，这无疑对索引维护带来负担。所以很多情况下都选择自增整型数或者严格算法的雪花数。

    二级索引&非聚簇索引

    二级索引也称之为辅助索引，或者称之为非主键索引，是非聚簇索引的一种，比如我们常见的唯一索引，普通索引，联合索引等等，这种索引非常显著的特征就是它们的叶子节点不存放数据，存放的是数据对应的主键，通过二级索引可以找到数据对应的主键，然后根据主键到聚簇索引找到位置，这个过程也称之为回表。这也是它最大的缺点，回表意味着更多的查询步骤，影响查询性能。

    覆盖索引

    但是并不是所有的二级索引查询都需要回表，这就有一个叫覆盖索引的概念，如果你查询的字段正好就是索引字段，那么在二级索引中就可以查到了，是不需要回表的，这样的查询效率更高！

    最左匹配原则

    在联合索引中有这样的一个原则，最左匹配原则，联合索引意味着依据多个字段构建索引，而且严格按照索引的字段顺序进行构建的，这里面就涉及到索引失效的问题，我会放在下面的模块中详细的介绍各种索引失效的场景。

    索引下推

    索引下推是 MySQL5.6 提供的一种索引优化的手段，主要目的就是为了帮助过滤，减少回表次数，主要操作就是根据索引列的数据然后比对 where 条件，如果条件在索引列上，会在这个索引上就先进行过滤，而不是拿到主键回表，通过 MySQL 服务进行过滤的了。对应的执行计划会显示 Using index condition

    建索引的一些建议

    说完了索引的一些概念性的东西，那么我在说一说关于索引的一些建议。我们创建索引的目的就是一个帮助快速搜索。
    哪些字段适合创建索引？
    不为 null 的字段，null 这个字段很恶心，它是存到每一个行的空值列表中，如果你查 is null 这种都是到空值列表中找的，对于查询效率大大影响的，尽量别 null，给个默认值就好；

    频繁被查询的字段，经常被作为条件的字段，频繁被使用排序的字段，经常用于 join 关联的字段

  • 建议 1： 被频繁更新的字段要慎重创建索引，一旦更新就代表着索引要更新，维护起来很影响性能。
  • 建议 2： 索引的数量要控制，建议不超过 5 个，如果索引创建过多就需要更多的资源去维护，而且可能会影响 MySQL 优化器对索引的选择。
  • 建议 3： 尽可能用联合索引代替单列索引，这样也能减少索引的冗余，同时还能减少空间，何乐而不为呢。
  • 建议 4： 字符串的索引尽量使用前缀索引，控制索引大小。
  • 建议 5： 如果索引长期不被使用，赶紧删除吧，可以通过 sys 库的 schema_unuserd_indexed 视图查询哪些索引从未被使用。

    • 执行计划分析

    我们下面的执行计划分析和索引失效的分析都是基于这个表来看，请大家耐心往下看完，绝对受益良多。

    CREATE TABLE `player` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `player_id` varchar(256) NOT NULL COMMENT '运动员编号',
  `player_name` varchar(256) NOT NULL COMMENT '运动员名称',
  `height` int(11) NOT NULL COMMENT '身高',
  `weight` int(11) NOT NULL COMMENT '体重',
  `type` varchar(256) DEFAULT '0' COMMENT '球员类型',
  PRIMARY KEY (`id`),
  KEY `idx_player_id` (`player_id`),
  KEY `idx_name_height_weight` (`player_name`,`height`,`weight`),
  KEY `idx_type` (`type`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    这个就是执行计划的图
    image-20231208103144624.png

    select_type

    查询类型，常见类型比如SIMPLE，PRIMARY，SUBQUERY，UNION，其实就是查询的类型，如果多表查询的话，id是相同的，是一组查询。里面比如单表查询，子查询，union之类的。

    table

    查询涉及到的表

    partitions

    当前操作设计到的分区

    type（很重要）

    这个很重要，表示当前查询所使用到的索引类型

    all 全表

    上面的截图就是我explain select * from player; 的结果，就是全表查询了,使用非索引字段过滤也是全表查询，比如explain select * from player where player_name ='player_0';

    eq_ref 连接唯一索引

    对于主键或者唯一索引的查询进行关联查询的时候，会使用这个类型，所以也就是为什么我们推荐关联表操作的时候最好使用唯一索引进行关联，这样能很大的提升查询效率

    ref非唯一索引扫描

    比如explain select * from player where player_id ='9af3e820-b49d-439d-aa8d-c5636159e7ec';得到的执行计划如下图

    image-20231208133802929.png

    range使用索引进行范围查询

    比如explain select * from player where id>1 and id<10;得到的执行计划就是

    image-20231208134113591.png

    index 全索引扫描

    比如不符合最左匹配原则的查询，explain select player_name from player where weight =88;查询的列还在索引数中，就会用index，遍历整个联合索引数进行查询。

    image-20231208134544080.png

    const 常量

    使用唯一索引进行常量查询的时候使用比如：explain select player_name from player where id =1;
    image-20231208134853834.png
    还有一种是system，系统表，少量数据，往往不需要磁盘io。

    所以各个类型的执行效率的排行就是：system>const>eq_ref>rel>range>index>all

    possible_keys可能使用的字段

    MySQL 进行计算执行计划的时候会得到可能使用的索引，但是不一定执行，实际上还是要看SQL优化器的选择。

    key实际使用的索引

    执行SQL的时候实际使用到的索引就会放在这里

    key_len索引长度

    使用到的索引长度，对于联合索引很重要，对于联合索引来说，这个数越大，说明索引利用率越高

    ref

    这个主要就是当我们使用索引列等值运算的时候，会标识出值的类型，比如常量。

    rows

    预估执行需要遍历的条数，最优结果就是和查询结果条数相等。

    filtered

    结合rows分析，这个就是经过条件过滤之后，剩余记录条数的百分比，这个值最大100，越大越好。

    Extra额外信息（非常重要）

    using where

    当使用非查询列进行过滤的时候，会用到where去过滤结果，这个也是效率很低的一种体现

    explain select * from player where weight =88;

    image-20231208140647453.png

    using index

    使用了覆盖索引进行过滤，不需要进行回表然后过滤数据。

    explain select player_name from player where weight =88;

    使用了覆盖索引player_name,height,weight查询player_name，同时因为过滤条件没有命中索引，所以是对整个索引树进行的遍历

    image-20231208140955479.png
    下面这种情况就是非常好的覆盖索引的使用

    explain select player_name from player where player_name = 'player_0';

    image-20231208141107227.png

    不需要回表，直接在索引树上查询。

    Using temporary

    使用了临时表，一般发生在非索引字段的排序或者分组中

    比如explain select weight,count(1) from player group by weight;

    image-20231208143515428.png

    Using filesort

    使用文件排序，也就是没有用所以字段排序的时候发生的。

    比如explain select weight from player order by weight;

    image-20231208144724899.png

    还有一些其他的，比如Using index for group by ,Using filesort ffor group by 等等都是一些描述查询的信息。我们要做的就是分析这些信息然后得出索引的效率，然后进行针对性的优化。

    一旦使用了filesort 就说明索引的排序没有生效，依赖于文件排序的性能是很慢的，这是需要优化的；

    一旦使用temporary说明还用临时表了，那么就产生了很大的维护成本，多数情况下也是需要维护的。

    如果rows遍历的行很多，但是实际查询的行很少，多半是索引失效了，那就想办法优化吧。

    索引失效的场景

    类型错误导致索引失效

    explain select * from player where type=0;

    type是varchar类型，传入是整型，索引失效

    image-20231208151031686.png
    但是这种explain select * from player where height ='188';

    height是整型，我传入字符型

    image-20231208151202036.png
    索引生效，所以这里面有一个隐式类型转换的事。

    当字段为int型，传入为字符串型，对效率影响不大，但是不推荐这么做，因为转换有问题，比如0000123，字符串转为整型就是123，在某些情况是不可用的。

    当字段为字符，传入为整型，会直接导致索引失效。

    索引列参与运算

    explain select * from player where height-1 >188;类似的还有给索引列加函数等

    image-20231208151521061.png
    索引直接失效，对于这种需要将计算移动到另一边

    explain select * from player where height>189;

    MySQL放弃使用索引

    一般这种情况就是MySQL推测使用索引和全表没啥区别，比如

    explain select height from player where height>55;

    image-20231208151726053.png
    我表中的数据都是都是大于55的，所以就和全表一样

    通配符问题

    比如like的使用 ，我们以索引idx_name_height_weight为例

    explain select * from player where player_name like '%er_0';

    索引字段直接模糊左边，索引就会失效。同理explain select * from player where player_name like '%er_0%';也一样

    image-20231208152123469.png
    对于这种最好模糊加在右边

    explain select * from player where player_name like 'er_0%';

    image-20231208152202576.png

    使用or

    比如explain select height from player where height=188 or type =0;

    两个字段都有索引，这样的结果就是索引都失效

    image-20231208152341397.png
    对于这种最好是分别查，然后自己组合

    联合索引失效，索引中断

    我先正常查一个使用完整索引的情况，得到使用索引的长度为778

    explain select * from player where player_name ='player_0' and height =188 and weight =88;

    image-20231208152632982.png
    我再来一个使用两个字段的索引情况，得到使用索引的长度为774

    image-20231208152712847.png

    1、索引中断了，使用player_name和weight查询

    explain select * from player where player_name ='player_0' and weight =88;

    image-20231208152819591.png
    得到使用索引长度为770，其实也就是只用player_name使用了索引，后面Using index condition代表的是回表的意思，因为我select *

    2、最左索引缺失

    explain select * from player where weight =88;

    image-20231208153230324.png
    因为索引就是根据player_name开始构建的，所以必须在，这也是最左匹配原则的体现

    3、索引中使用了>,<这种

    explain select * from player where player_name ='player_0' and height >188 and weight =88;

    image-20231208153121396.png
    索引长度就是player_name 和height 的长度，weight就失效了

    需要仔细看下面几个

    ①：>= <=的情况

    explain select * from player where player_name ='player_0' and height >=188 and weight =88;

    image-20231208153428833.png
    所以长度是三个字段的长度，这个和>,<是不同的。

    差别就在于=， 这个很关键，在使用 >,< 等情况下，因为联合索引的排序是根据字段的顺序来的，height >188的范围内，weight是无序的，就好比在height<188的时候height也可能等于88，所以weight的索引就不能生效

    但是=不同，因为是=，在height 排序之后索引字段weight也会排序，所以在等于的情况下，weight是有序的，所以索引是生效的。

    ②：between and 的情况

    在MySQL中between and类似>=,<=所以和上面的一样
    explain select * from player where player_name ='player_0' and height between 188 and 190 and weight =88;

    image-20231208161011821.png

    ③：like右边模糊的情况

    explain select * from player where player_name ='player_1%' and height =188;

    image-20231208161205081.png
    两个字段的索引都使用了

    这个和等值很类似，就是部分相等，排序很重要，在like里面，就会要求前缀等于player_1，然后里面的height就是有序的。

    张二河
    运维资讯
    0 0

    相关文章

    塑造我成为 CTO 之路的“秘诀”
    “人工智能教母”的公司估值达 10 亿美金
    教授吐槽：985 高校成高级蓝翔！研究生基本废了，只为房子、票子……
    Windows 蓝屏中断提醒开发者：Rust 比 C/C++ 更好
    Claude 3.5 Sonnet 在伽利略幻觉指数中名列前茅
    上海新增 11 款已完成登记生成式 AI 服务

    发布评论

    张二河

    这个人很懒，什么都没有留下～

    文章8551 评论0