Kenger`s Blog
所有文章 标签 分类 关于
Kenger`s Blog

目录

数据库索引

kenger 收录于 Find JOB
   约 5560 字   预计阅读 12 分钟 

索引介绍分类

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

我们可以按照四个角度来分类索引。

  • 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
  • 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
  • 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
  • 按「字段个数」分类：单列索引、联合索引。

索引分类

数据结构分类

B-树索引

B-树索引又称为 BTREE 索引

哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

逻辑区分

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，少数任务就是加快系统对数据的访问速度。

主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。

全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。从MySQL 5.6版本开始，InnoDB存储引擎开始支持全文索引

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

唯一索引 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

少数索引

实际列数区分

  • 单列索引
  • 多列索引（组合索引）

聚簇索引

  • 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

聚簇索引需要具有唯一性，一般要根据这个表最常用的 SQL 查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

非聚簇索引

  • 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，（相当于只是该列的索引，然后通过该索引找到主键的可以，再去聚簇索引找到目标数据行），myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

聚簇二者区别

  • 聚簇数据访问更快 ，因为索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。

MySQL如何实现的索引机制？

MySQL中索引分三类：B+树索引、Hash索引、全文索引

B+ 树和 B 树的差异：

  • B+树中非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大值（或最小）。
  • B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中， 非叶子节点既保存索引，也保存数据记录 。
  • B+树中所有关键字都在叶子节点出现，叶子节点构成一个有序链表，支持顺序查找，而且叶子节点本身按照关键字的大小从小到大顺序链接。

索引数据结构

平衡二叉树

  • 基础数据结构
  • 它是一棵空树或它的左右两个子树的高度差的绝对值不超过1
  • 并且左右两个子树都是一棵平衡二叉树。
  • 每个节点记录一个数据

AVL的缺点

如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。

https://raw.githubusercontent.com/kengerlwl/kengerlwl.github.io/refs/heads/master/image/7c8ae45e99bc3c1baa93d33fce13dd28/0cf7b2a3e96aec18e6e6e9b8ee153c9c.png为了提高查询效率，就需要 减少磁盘IO数 。为了减少磁盘IO的次数，就需要尽量降低树的高度 ，需要把原来“瘦高”的树结构变的“矮胖”，

红黑树

  • hashmap存储
  • 两次旋转达到平衡
  • 分为红黑节点

B+ 树和 B 树的差异

  • B+树中非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大值（或最小）。
  • B+树中非叶子节点仅用于索引，不保存数据记录，（因此可以存储更多节点，让树更矮胖，提高效率），跟记录有关的信息都放在叶子节点中。而B树中， 非叶子节点既保存索引，也保存数据记录 。
  • B+树中所有关键字都在叶子节点出现，叶子节点构成一个有序链表（适用于范围查询），而且叶子节点本身按照关键字的大小从小到大顺序链接。

什么是2-3树 2-3-4树？

多叉树（multiway tree）允许每个节点可以有更多的数据项和更多的子节点。2-3树，2-3-4树就是多叉树，多叉树通过重新组织节点，减少节点数量，增加分叉，减少树的高度，能对二叉树进行优化

为什么官方建议使用自增长主键作为索引？（说一下自增主键和字符串类型主键的区别和影响）

  • 自增主键能够维持底层数据顺序写入
  • 读取可以由b+树的二分查找定位
  • 支持范围查找，范围数据自带顺序

索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

  • 空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

  • 时间上的代价

每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位、页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

B 树和 B+ 树都可以作为索引的数据结构，在 MySQL 中采用的是 B+ 树

什么是回表操作？

通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 MySQL 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

什么是覆盖索引？

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

explain的输出结果Extra字段为Using index时，能够触发索引覆盖

  • 覆盖索引包含查询中涉及的所有列，可以直接从索引中获取查询结果。

实现覆盖索引：常见的方法是将被查询的字段，建立到联合索引里去

示例：

1

select * from user where age > 20 ;

第一次 取回id，第二次（回表）根据id拿到完整数据

使用联合索引

1
2
3

age,name -> index
#执行下面语句
select age from user where age >20 and name like"张%" ;

单列索引升级成了联合索引后，索引的叶子节点存储了节点值，都能够命中，不会回表查询，查询效率也是比较高的

索引下推

如果查询条件涉及到了索引字段，数据库系统可以将这些条件下推到存储引擎层面进行处理。这样，存储引擎就可以利用索引来快速定位符合条件的数据，而不必将所有数据加载到内存中再进行过滤。

就是先在where那边把能筛选的就筛选了，减少查询的结果，进而减少回表的次数

什么是联合索引，组合索引，复合索引？

最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

最左前缀原则

注意：假设有（a,b,c）索引，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。例如

where b = ? and a = ?也可以。

此外，对于b和c列，在索引里面，是全局无序，局部有序

https://raw.githubusercontent.com/kengerlwl/kengerlwl.github.io/refs/heads/master/image/7c8ae45e99bc3c1baa93d33fce13dd28/ada0e57b41d4e50e45f7dd7ae0d12bed.png

索引区分度

另外，建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

区分度越大，代表该索引能够被分的节点更多，更细，定位更加精准

区分度就是某个字段 column 不同值的个数「除以」表的总行数，计算公式如下：

https://raw.githubusercontent.com/kengerlwl/kengerlwl.github.io/refs/heads/master/image/7c8ae45e99bc3c1baa93d33fce13dd28/34967c1d775620a8fa4b421c64311030.png

比如，性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。

唯一索引

唯一索引，一种索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。

什么时候使用唯一索引？

业务需求唯一字段的时候，一般不考虑性能问题

什么时候适合创建索引，什么时候不适合创建索引？

适合创建索引

  • 频繁作为where条件语句查询字段
  • 关联字段需要建立索引
  • 排序字段可以建立索引
  • 分组字段可以建立索引(因为分组前提是排序)
  • 统计字段可以建立索引（如.count(),max()）

不适合创建索引

  • 频繁更新的字段不适合建立索引
  • where，分组，排序中用不到的字段不必要建立索引
  • 可以确定表数据非常少不需要建立索引
  • 参与MySQL函数计算的列不适合建索引
  • 字段中存在大量重复数据，不需要创建索引

有哪些情况会导致索引失效？

  • **计算、函数导致索引失效（ where length(name)=6;） **
1
2
3

-- 显示查询分析
EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';
EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效
  • LIKE以%，_ 开头索引失效（左模糊开始like）

拓展：Alibaba《Java开发手册》

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

1

EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效
  • 不等于(!= 或者<>)索引失效（表达式计算：where id + 1 = 10;）
1
2

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name = 'abc' ;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc' ; --索引失效
  • 使用or，又想索引生效，只能将or条件中的每个列都加上索引（如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。）
  • IS NOT NULL 失效 和 IS NULL
1
2

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

**注意：**当数据库中的数据的索引列的NULL值达到比较高的比例的时候，即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引，此时type的值是range（范围查询）

1
2
3
4
5
6

-- 将 id>20000 的数据的 name 值改为 NULL
UPDATE emp SET `name` = NULL WHERE `id` > 20000;

-- 执行查询分析，可以发现 IS NOT NULL 使用了索引
-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效，由查询优化器的算法决定
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL
  • 类型转换导致索引失效（WHERE name= 123）
1
2

EXPLAIN SELECT * FROM emp WHERE name='123'; 
EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效
  • 复合索引未用左列字段失效
  • 如果MySQL觉得全表扫描更快时（数据少

为什么LIKE以%开头索引会失效？

没有高效使用索引是因为字符串索引会逐个转换成accii码，生成b+树时按首个字符串顺序排序，类似复合索引未用左列字段失效一样，跳过开始部分也就无法使用生成的b+树了

一个表有多个索引的时候，能否手动选择使用哪个索引？

不可用手动直接干预，只能通过MySQL优化器自动选择

多个索引优先级是如何匹配的？

  1. 主键（唯一索引）匹配
  2. 全值匹配（单值匹配）
  3. 最左前缀匹配
  4. 范围匹配
  5. 索引扫描
  6. 全表扫描

group order 与索引

使用Order By时能否通过索引排序？

没有过滤条件不走索引

group by 分组和order by在索引使用上有什么区别？

group by 使用索引的原则几乎跟order by一致 ，唯一区别：

  • group by 先排序再分组，遵照索引建的最佳左前缀法则
  • group by没有过滤条件，也可以用上索引。Order By 必须有过滤条件才能使用上索引。

有字段为null索引是否会失效？

不一定会失效，每一条sql具体有没有使用索引 可以通过trace追踪一下

最好还是给上默认值

数字类型的给0，字符串给个空串“”

数据页与索引

因此，InnoDB 的数据是按「数据页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

数据库的 I/O 操作的最小单位是页，InnoDB 数据页的默认大小是 16KB

我们在数据库中要做的，就是尽量减少读取IO的次数，进而实现优化的目的。

InnoDB 里的 B+ 树中的每个节点都是一个数据页，结构示意图如下：

https://raw.githubusercontent.com/kengerlwl/kengerlwl.github.io/refs/heads/master/image/7c8ae45e99bc3c1baa93d33fce13dd28/7c1854e4e1fe0aaebe1f3ce9c68a12e3.png

innodb的B+树和普通B+树的区别：

  • B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。
  • B+ 树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

我们再看看 B+ 树如何实现快速查找主键为 6 的记录，以上图为例子：

  • 从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项；
  • 在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；
  • 接着，在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

MySQL 单表不要超过 2000W 行，靠谱吗？

2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

count效率问题

小结

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

再来，就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。

慢查询优化问题

启用慢查询日志

判断一个sql是慢查询

可通过 show variables like 'long_query_time'; 查看当前的 long_query_time 值。

开启慢查询日志（修改my.ini配置文件）：开启 MySQL 慢查询日志，再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志，找出问题的根源。

explain查看执行流程

如何优化慢查询

数据库层面

  • 索引优化
  • 分页优化
  • select避免不必要的列
  • 减少join连接
  • 减少排序。

业务架构层面

  • 将复杂的业务看能不能拆分，考虑其中哪些部分是可以不需要的字段。
  • 引入缓存。
  • 分库分表，扩展

https://raw.githubusercontent.com/kengerlwl/kengerlwl.github.io/refs/heads/master/image/7c8ae45e99bc3c1baa93d33fce13dd28/e52c9e9ef5be34f3e437f85698de6bb1.png

更新于 2024-03-26
阅读原始文档
 数据库
返回 | 主页