MySQL面试篇章——MySQL索引

于 2024-07-21 14:43:17 发布
阅读量382 收藏 6
点赞数 20
分类专栏： MySQL面试篇章 文章标签： mysql 面试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_65508929/article/details/140587884
版权

文章目录

MySQL 索引

当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。

由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及到磁盘I/O操作。如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，因此需要掌握良好的索引创建原则！

索引分类

索引是创建在表上的，是对数据库表中的一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度！

物理上（聚集索引 & 非聚集索引）/ 逻辑上（…）

索引的优缺点：

优点：提高查询效率

缺点：索引并非越多越好，过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O会造成CPU负荷太重

  • 普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引（创建新表 & 已创建表，数量是不限的，一张表的一次SQL查询只能用一个索引 where a = 1 and b = ‘M’）
  • 唯一性索引：使用 UNIQUE 修饰的字段，值不能够重复，主键索引就隶属于唯一性索引
  • 主键索引：使用 PRIMARY KEY 修饰的字段会自动创建索引（MyISAM、InnoDB）
  • 单列索引：在一个字段上创建索引
  • 多列索引：在表的多个字段上创建索引（uid + cid，多列索引必须使用到第一列，才能够用到多列索引，否则索引用不上）
  • 全文索引：使用 FULLTEXT 参数可以设置全文索引，只支持 CHAR、VARCHAR、TEXT 类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度（线上项目支持专门的搜索功能，给后台服务器增加专门的搜索引擎支持快速高效的搜索 elesticsearch 简称 es；C++开源的搜索引擎；搜狗的workflow）

索引创建和删除

  • 创建表的时候指定索引字段
CREATE TABLE index1(
	id INT,
    name VARCHAR(20),
    sex ENUM('male', 'female'),
    INDEX(id, name),
    INDEX `index name` (sex) # 没有名字，会默认会生成
    # show create table 进行查看创建SQL语句
)

# Create Table: CREATE TABLE `student` (
#  `uid` int unsigned NOT NULL AUTO_INCREMENT,
#  `name` varchar(50) NOT NULL,
#  `age` tinyint unsigned NOT NULL,
#  `sex` enum('M','W') NOT NULL,
#  PRIMARY KEY (`uid`),
#  KEY `nameidx` (`name`)
#) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 #COLLATE=utf8mb4_0900_ai_ci
  • 在已经创建的表上添加索引
CREATE [UNIQUE] INDEX 索引名 ON 表明 (属性名 (length) [ASC | DESC]);

# create index nameidx on student(name); // 默认btree索引

# 添加 hash 索引
# create index ageidx on studetn(age) using hash;
  • 删除索引
DROP INDEX 索引名 ON 表名;
  • 查看所有索引
show indexes from 表明;

1、经常作为 where 条件过滤的字段考虑添加索引

2、字符串列创建索引时，尽量规定索引的长度，而不能让索引值的长度 key_len 过长

3、索引字段涉及类型强转、mysql函数调用、表达式计算等，索引就用不上了（索引失效）

在这里插入图片描述

索引的执行过程

explain 查看执行计划

使用explain查看SQL的执行计划，分析索引的执行过程，mysql的user权限示例如下：
在这里插入图片描述

可以看到使用了主键索引，共扫描了1行，Using index 表示直接从索引树上查询到结果，不需要回表

在这里插入图片描述

  • 由于Host和User设立了联合索引，也就是多列索引，而多列索引只有第一列的索引使用了，才能进行索引查询，不然不会进行索引查询

在这里插入图片描述

explain 结果字段分析

  • select_type

    simple：表示不需要union操作或者不包含子查询的简单select语句。有连接查询时，外层的查询为simple且只有一个

    primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary且只有一个

    union：union连接的两个select查询，除了第一个表外，第二个以后的表的select_type都是union

    union result：包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id字段为null

  • table

    显示查询的表明；

    如果不涉及对数据库的操作，这里就显示null

    如果显示为尖括号就表示这是个临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生的

    如果是尖括号括起来<union M, N>也是一个临时表，表示这个结果来自于union查询的id为M，N的结果集

  • type

    const：使用唯一索引或者主键，返回记录一定是1行已经的等值where条件时，通常type就是const

    ref：常见于辅助索引的等值查找，或者多列主键，唯一索引中，使用第一个列之外的列作为等值查找会出现；返回数据不唯一的等值查找也会出现

    range：索引范围扫描，常见于使用<，>，is null，between，in，like等运算符的查询中

    index：索引全表扫描，把索引从头到尾扫一遍；常见于使用索引列就可以处理不需要读取数据文件的查询，可以使用索引排序或者分组的查询

    all：全表扫描数据文件，然后在server层进行过滤返回符合要求的记录

  • ref

    如果使用常数等值查询，这里显示const

    如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段

  • Extra

    • using filesort：排序时无法用到索引，常见于 order by 和 group by 语句中
    • using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据

索引的底层实现原理

数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘块（对应索引树的节点），索引树越低，越”矮胖“，磁盘I/O次数就越少

MySQL支持两种索引，一种是B-树索引，一种是哈希索引，两者在数据查询的效率是非常高的

主要对于InnoDB存储引擎进行讨论，基于B-树的索引结构（MySQL实际采用的是B+树结构）

B-树

B-树是一种m阶平衡树（一般来说m=300~500），叶子节点都在同一层，由于每一个节点存储的数据量比较大，索引整个B-树的层数是比较低的，基本上不超时三层。

二分查找：时间复杂度O(logn)

由于磁盘的读取也是按block块操作的（内存是按page页面操作的），因此B-树的节点大小一般设置为和磁盘块大小一致，这样一个B-树节点，就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来，所以当使用B-树存储索引的时候，磁盘I/O的操作次数是最少的（MySQL的读写效率，主要集中在磁盘I/O上）

select * from student where uid = 5（uid添加了索引）;

uid 有索引 ==> 存储引擎 ==> kernel（操作系统） > 磁盘I/O（读索引文件）> 内存上 ==> 用索引的数据构建B-树加速搜索

在这里插入图片描述

一次磁盘I/O读取的磁盘块的内容，刚好存储在B树的一个节点中

因为非叶子节点都有左右子树，所以指针域 = 数据域 + 1

假设有2000W条数据，用AVL存储2000W条数据，构建下来有25层（计算器以log10为底，所以得这么除）

在这里插入图片描述

如果 m = 500，最多三层，也就是最多花费三次的磁盘I/O，大大减少了速率

扩展：B-树的缺点

  • 每个节点中有key，也有data，但是每一个节点的存储空间是有限的，如果data数据较大时会导致每个节点能存储的key的数据很小
  • 当存储的数据量很大时同样会导致B-树的高度较大，磁盘I/O次数花费增大，导致效率降低

面试考点：为什么MySQL（MyISAM和InnoDB）索引底层采用B+树，而不是B-树？

1、索引 + 数据内容分散在不同的节点上，离根节点近的，搜索就快；离根节点远的，搜索就慢！

2、每一个非叶子节点上，不仅仅要存储索引（key），还要存储索引值所在的那一行的data数据。一个节点所能存放的索引key的个数，比只存储key值的节点的个数要少得多。（例如，索引key要存4个字节，对应data数据也要4个字节，那就是8个字节，每一块数据，但是如果只存索引key，那就可以存两个，数据最后去叶子节点上找）

3、这棵树不方便做范围搜索，整表遍历看起来也不方便

例如：where age > 11 and age < 18，相当于17的左右子树都要去查，然后12的左右子树也要去查。很不方便（看上图）

因此由于以上的三个原因 ==> MySQL采用B+树来构建索引树

B+树

一句话：B+树最后其实就是一个循环链表，串起来了，所以说最后的查询就是在循环链表中进行查询

特点：

  • 每一个非叶子几点，只存放索引key，不存放数据data，好处就是一个节点存放的索引key更多，B+树在理论上来说，层数会更低一些，搜索的效率会更好一些
  • 叶子节点上存储了所有的索引值（数据data），搜索每一个索引对应的值data，都需要在叶子节点上，这样子每一行记录搜索的时间是平均的
  • 叶子节点被串在一个链表当中，形成一个有序的链表，如果要进行索引树的搜索&整表搜索，直接遍历叶子节点的有序链表即可！或者做范围查询的时候，直接遍历叶子节点的有序链表即可

在这里插入图片描述

面试考点：B-树和B+树在存储结构上有什么不同？

1、B-树的非叶子节点，存了索引key和对应的数据地址，而B+树的非叶子节点只存了索引key，不存数据data，因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的，因此，从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，因此查询的会快一些

2、B-树由于每个节点都存储了索引key和对应的数据，因此离根节点近的数据，查询的就快，离根节点远的数据，查询的就慢；而B+树所有的数据都存储在叶子节点上，因此在B+树上搜索索引key，找到对应数据的时间是比较平均的，没有快慢之分

3、在B-树上如果做区间查找，遍历的节点是非常多的；B+树所有的叶子节点被连结成了有序链表结构，因此做整表遍历和区间查找是非常容易的

哈希索引

哈希索引由哈希表实现的，哈希表对数据并不排序，因此不适合做区间查找，效率非常低，需要搜索整个哈希表结构

时间复杂度O（1），链式哈希表

优点：搜索的效率更好、磁盘I/O花费要少

hash(name) = hashkey % bucket_num （哈希冲突）

在这里插入图片描述

哈希表中的元素没有任何顺序可言！只能进行等值比较

select * from student where name = 'zhangsan';

select * from student where name like 'zhang%'; error
  • 范围搜索，前缀搜索，order by 排序这些操作，哈希索引都不适合！！！
  • 它没办法处理磁盘上的数据，加载到内存上构建高效的搜索数据结构，因为它没有办法减少磁盘I/O的次数

聚集和非聚集索引

MyISAM（*.MYD，*.MYI）

select * from student where name = 'zhangsan';

没有索引：直接会在整张表中进行全查（整表搜索）

有索引：进行*.MYI的索引文件的查询，生成B树，然后进行二分查找

主键索引
  • MyISAM引擎使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址（通过这个地址去查找数据data）。

在这里插入图片描述

  • 即主键对应的data是数据的地址（具体的数据都需要去寻址，在一个data数据表中）
辅助索引（二级索引）
  • 在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯 一的，而辅助索引的key可以重复，如果给其它字段创建辅助索引，结构图如下：

在这里插入图片描述

总结：MyISAM存储引擎，索引结构叶子节点存储关键字和数据地址，也就是说索引关键字和数据 没有在一起存放，体现在磁盘上，就是索引在一个文件存储，数据在另一个文件存储，例如一个user 表，会在磁盘上存储三个文件 user.frm（表结构文件） user.MYD（表的数据文件） user.MYI（表的索引文件）。

MyISAM的索引方式也叫做非聚集索引

InnoDB（*.IBD）

  • InnoDB存储引擎的索引关键字和数据是存放在一起的
select * from student where name = 'zhangsan';

没有索引：会自动生成B树

有索引：会根据索引数据生成一张新的B树

主键索引
  • uid 是主键
  • 主键索引树：

在这里插入图片描述

  • 每个索引下面对应的是对应的数据（例如：15 => 34 Bob）
辅助索引（二级索引）
  • InnoDB的辅助索引，叶子节点上存放的是索引关键字和对应的主键（即如果要获取数据，需要通过主键进行回表查询主键索引树，进行获取到数据）

在这里插入图片描述

select * from student where name = 'Alice';

1、先搜索name的二级索引树，找到Alice对应的主键uid=18

2、再拿uid=18回表在书简索引树搜索uid那一行记录

问题：select * from student where age = 20 order by name；如果只给age添加索引，行不行？还有什么没有考虑到？

不行，如果只有age键索引，会有using filesort！，因此需要创建age、name的多列索引

先按照age进行排序，再按照name进行排序；age相同，按name进行排序

在这里插入图片描述

总结：辅助索引的B+树，先根据关键字找到对应的主键，再去主键索引树上找到对应的行记录数据。从索引树上可以看到，InnoDB的索引关键字和数据都是在一起存放的，体现在磁盘存储上，例如创建一个user 表，在磁盘上只存储两种文件，user.frm（存储表的结构），user.ibd（存储索引和数据）。

InnoDB的索引树叶节点包含了完整的数据记录，这种索引叫做聚集索引。

两者的区别：MyISAM比InnoDB多了磁盘I/O的使用，它需要读取*.MYI文件获取索引数据，进行生成B树（相当于多了一部I/O操作，两个文件）

自适应哈希索引

InnoDB存储引擎监测到同样的二级索引不断被使用；那么它会根据这个二级索引，在内存上根据二级索引树（B+树）上的二级索引值，在内存上构建一个哈希索引，来进行加索引搜索

在这里插入图片描述

  • 自适应哈希索引本身的数据维护也是要耗费性能的，并不是说自适应哈希索引在任何情况下都会提升二级索引的查询性能！我们需要根据参数指标，来具体分析是否打开或者关闭自适应哈希索引
show engine innodb status\G

1、RW-latch等待的线程数量（自适应哈希索引默认分配了8个分区），同一个分区等待的线程数量过多
2、走自适应哈希索引的频率（低）和二级索引树搜索的频率（高）

面试考点：SQL和索引的优化问题

通过explain分析SQL

项目 ==> 业务 ==> 千条、万条SQL

流程：从什么地方能够获取哪些运行时间长，耗性能的SQL，然后再通过explain去分析它

itzzan
关注
  • 20
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
面试准备——MySql基础篇
12-15
总的来说，面试准备中MySQL基础篇应涵盖客户端连接、SQL语法、数据库设计、索引优化以及事务处理等方面。熟悉这些知识点不仅能帮助面试者在技术面试中表现出色，也是日常数据库管理工作中不可或缺的技能。
大厂Mysql面试题在这里
03-25
1、MySQL的复制原理以及流程 基本原理流程，3个线程以及之间的关联； 1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中； 2. 3. 从：io线程——在使用start slave 之后，负责从...
MySQL面试篇章——MySQL存储引擎
itzzan的博客
07-17 365
MEMORY 类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用 HASH 索引（不适合做范围查询），但是一旦服务关闭，表中的数据就会丢失掉。索引缓存和数据缓存：和 MySQL Server 的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘I/O（数据库的性能提升，目的是为了减少磁盘I/O操作来提升数据库访问效率），读取上一次内存中查询的缓存就可以了。事务：多个SQL语句，保证它们共同执行的原子操作，要么成功，要么失败，不能只成功一部分，失败需要回滚事务。
MySQL面试题 —— 索引篇
m0_56602092的博客
05-09 1390
一、索引的分类 1. 按数据结构：B+tree索引、Hash索引、Full-text索引 2. 按物理存储：聚簇索引（主键索引）、辅助索引（二级索引） 3. 按字段特性：主键索引、唯一索引、普通索引、前缀索引 4. 按字段个数：单列索引、联合索引 二、优化索引的方法 1. 前缀索引 2. 覆盖索引 3. 主键索引最好是自增的 4. 索引列最好设置为 NOT NULL 三、索引失效的六种情况 1. 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效
MySQL 面试题——MySQL 基础
代码星辰的博客
09-17 1万+
本文介绍了 MySQL 基础相关的面试题。
mysql面试题——索引创建与设计原则
weixin_44582492的博客
12-05 989
一：哪些情况适合创建索引？
MySQL高频基本面试问题整理
热门推荐
张彦峰的博客
04-07 170万+
总结MySQL高频基本面试问题整理，用于快速查缺补漏
面试八股之MySQL篇2——索引篇
做一个善良的人，做好自己的事，如果愿意，再发一份光。
05-17 759
MySQL索引的知识，聚簇索引、覆盖索引、回表查询、索引创建规则、索引失效情况。面试必备
MySQL高级篇——索引简介
qq_43499848的博客
10-22 5183
介绍MySQL中的索引内容，助推小伙伴们在面试中通关！
MySQL高级篇——索引失效的11种情况
vincewm的博客
05-21 1万+
索引优化思路、要尽量满足全值匹配、最佳左前缀法则、主键插入顺序尽量自增、计算、函数导致索引失效、类型转换（手动或自动）导致索引失效、范围条件右边的列索引失效、不等于符号导致索引失效、is not null、not like无法使用索引、左模糊查询导致索引失效、“OR”前后存在非索引列，导致索引失效、不同字符集导致索引失败，建议utf8mb4
mysql面试题——存储引擎相关
weixin_44582492的博客
11-20 154
MySQL支持多种存储引擎，比如InnoDB，MyISAM， MySQL大于等于5.5之后，默认存储引擎是InnoDB。
MySQL面试题——in是否使用【索引】
红目香薰
06-05 6053
MySQL中in到底走不走索引，本文做了认证，虽然截图不全，但是结果是认真的确认过的。回答面试题肯定是没有问题的呢。祝愿大家面试成功。
mysql面试题.zipmysql面试题.zip
01-29
以下是一些可能在MySQL面试中遇到的关键知识点： 1. 数据库概念： - 数据库：存储数据的集合，通过SQL进行操作。 - 表：数据库中的数据结构，类似电子表格。 2. SQL基础： - CRUD操作：创建(Create)、读取...
大厂MySQL精选50道面试题.docx
05-22
以下是一些精选的MySQL面试题及其详细解答： 1. **自增主键与删除记录**： 当删除自增主键表的记录后，MySQL不会重新利用已删除的ID。如果删除了第15、16、17条记录，再重启MySQL并插入新记录，新的ID将是18，而...
计算机考研面试——数据库
12-14
索引有多种类型，如聚集索引（数据与索引存储在一起）、非聚集索引（索引与数据分开存储）、唯一索引（不允许重复值）、视图索引等。虽然索引可以加速查询，但过多的索引会影响数据更新和插入速度，需要合理设计。 ...
MySQL：SELECT 语句
最新发布
Zachyy的博客
07-20 223
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 441
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 648
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
Mysql----内置函数
m0_74979625的博客
07-18 256
Mysql--内置函数

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

itzzan

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值