Mysql知识点

Database InterviewPoint
program
发布日期:   2023-05-24
更新日期:   2024-07-04
文章字数:   7.3k
阅读时长:   26 分
阅读次数:  

Mysql知识点

一、MySQL索引

MySQL如何实现的索引机制

InnoDB 索引与 MyISAM 索引实现的区别是什么

MyISAM

MyISAM 索引文件和数据文件是分离的，使用 B+ 树实现，主键索引和辅助索引实现一致，索引文件进保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行。

所以和数据是分开存储的

InnoDB

辅助索引 data 阈存储相应记录主键的值而不是地址；

InnoDB 的数据文件本身就是主索引文件；

  1. InnoDB 索引存储时与数据是放在同一个文件里，MyISAM 是非聚簇索引的, 他的 b+ 数里面磁盘块放的式指针和数据行的地址
  2. 在索引的 B+ 树，叶子节点指向的数据，InnoDB 指向的 id，MyISAM指向的是指针（物理地址）
一个表中如果没有创建索引，那么还会创建B+树吗？

会

如果有主键会创建聚簇索引；

如果没有主键会生成 rowid 作为隐式主键。

B+树索引实现原理(数据结构)

叶子节点数据结构

数据结构 说明
record_type 表示记录的类型， 0是普通记录、 2是最小记录、 3 是最大记录、1是B+树非叶子节点记录
next_record 表示下一条记录的相对位置，我们用箭头来表明下一条记录。
各个列的值 这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。
其他信息 除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

示意图

这里一页就是一个磁盘块，代表一次IO

聚簇索引

特点

  • 索引和数据保存在同一个B+树中
  • 页内的记录是按照主键的大小顺序排成一个单项链表
  • 页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表
  • 非叶子节点存储的是巨鹿的主键+页号
  • 叶子节点存储的是完整的用户记录

优点

  • 数据访问更快 ，因为 索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
  • 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快。
  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库可以从更少的数据块中提取数据，节省了大量的IO操作 。

缺点

  • 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的ID列为主键。
  • 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

限制

  • 只 有 InnoDB 引擎支持聚簇索引，MyISAM不支持聚簇索引。
  • 由于数据的物理存储排序方式只能有一种，所以 每个 MySQL 的表只能有一个聚簇索引。
  • 如果没有为表定义主键，InnoDB 会选择 非空的唯一索引列代替。如果没有这样的列，InnoDB 会 隐式的定义一个主键 作为聚簇索引。
  • 为了充分利用聚簇索引的聚簇特性，InnoDB 中表的 主键应选择有序的id，不建议使用无序的 id，比如 UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。
非聚簇索引(二级索引、辅助索引)

聚簇索引，只能在搜索条件是主键值时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建非聚簇索引。

什么时候适合创建索引

where、join、order、group、count()max()…

什么是索引下推
  • 如果没有索引下推（ICP），那么MySQL在存储引擎层找到满足content1 > 'z'条件的第一条二级索引记录。主键值进行回表，返回完整的记录给server层，server层再判断其他的搜索条件是否成立。如果成立则保留该记录，否则跳过该记录，然后向存储引擎层要下一条记录。
  • 如果使用了索引下推（ICP），那么MySQL在存储引擎层找到满足content1 > 'z'条件的第一条二级索引记录。不着急执行回表，而是在这条记录上先判断一下所有关于idx_content1索引中包含的条件是否成立，也就是content1 > 'z' AND content1 LIKE '%a'是否成立。如果这些条件不成立，则直接跳过该二级索引记录，去找下一条二级索引记录；如果这些条件成立，则执行回表操作，返回完整的记录给server层。
单路排序和双路排序

关键配置:

  • sort_buffer 可供排序的内存缓冲区大小
  • max_length_for_sort_data 单行所有字段总和限制, 超过这个大小启动双路排序
  1. 通过索引检过滤筛选条件索到需要排序的字段+其他字段（如果是符合索引）
  2. 判断索引内容是否覆盖 select 的字段
    1. 如果覆盖索引，select 的字段和排序都在索引上，那么在内存中进行排序，排序后输出结果
    2. 如果索引没有覆盖查询字段，接下来计算 select 的字段是否超过 max_length_for_sort_data 限制，如果超过，启动双路排序，否则使用单路

单路排序发生在内存，排序时一次性取出全部查询的字段，双路排序先去需要排序的字段先存到磁盘，再对剩余字段进行查询，实质上是因为单条记录字节过大无法在内存排序的一种妥协方法。

二、MySQL内部技术

Mysql内部支持缓存查询吗

MySQL5.7支持内部缓存，8.0之后废弃掉

Mysql8之后为何废弃掉查询缓存
  1. mysql内部管理缓存不够领活，尤其针对更新频繁的表
  2. 类似Redis的键值对，必须要SQL完全一直才会命中cache，哪怕是一个空格
  3. 如果结果集太大无法被cache
  4. 分库分表不起作用
  5. 触发器、自定义函数、存储过程不起作用
  6. 表结构一改变也失效
替代方案

Redis，应用层cached

Mysql内部有哪些核心模块组成，作用是什么？

连接管理(处理连接) >>> 解析与优化(查询缓存 -> 语法解析 -> 查询优化) >>> 存储引擎(MyISAM、InnoDB、Memory…)

一条sql内部是如何执行的?(内部执行过程)

  1. 首先, MySQL 客户端通过协议与 MySQL 服务器建立连接，通过 SQL 接口发送 SQL 语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析(8.0已经废除缓存)。
  2. 然后，MySQL解析器通过关键字将SQL语句进行解析，并生成一棵对应的解析树，解析器使用 MySQL 语法规则验证和解析 SQL 语句。例如：它讲验证是否用错了关键字，或者关键字顺序是否正确，引号能不能前后匹配等；预处理器则根据 MySQL 规则进一步检查解析树是否合法。例如：这里会检测数据表和数据列是不是存在，还会解析名字和别名，看是不是有歧义等。然后预处理器再进行查询重写，生成一颗新解析树。
  3. 接下来，查询优化器将解析树转化成执行计划。MySQL 优化程序会对我们的语句做优化，如：子查询转换为连接、表达式简化等等。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引执行查询，表的连接顺序是啥样等等。这一步可以通过 EXPLAIN 语句来查看句子的执行计划。
  4. 最后，进入执行器阶段。完成查询优化之后，查询执行引擎会按照生成的执行计划调用存储引擎提供的接口执行 SQL 查询并将结果返回给客户端。在 MySQL8 以下的版本，如果设置了查询缓存，会先将结果进行缓存，再返回给客户端。

MySQL 提示“不存在此列”是执行到哪个节点报出的？

Parser 解析器

如果一张表创建了多个索引，在哪个阶段或模块进行的索引选择？

Optimizer 查询优化器

MySQL存储引擎

-- 查看MySQL提供什么存储引擎
SHOW ENGINES;

-- 查看默认存储引擎
SHOW VARIABLES LIKE '%default_storage_engine%';
存储引擎介绍

  1. InnoDB

    • InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。

    • 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

    • 数据文件结构：

      • 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

      • 表名.ibd 存储数据和索引

    • InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响。

  2. MyISAM

    • MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

    • 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用。

    • 数据文件结构：

      • 表名.frm 存储表结构

      • 表名.MYD 存储数据

      • 表名.MYI 存储索引

    • MyISAM只缓存索引，不缓存真实数据。

  3. Blackhole

    • Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。
    • 但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

  4. CSV

    • CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
    • CSV引擎可以作为一种数据交换的机制，非常有用。
    • CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

  5. 剩下的均不常用

设置存储引擎
-- 方法一
SET DEFAULT_STORAGE_ENGINE=MyISAM;

-- 方法二
或者修改 my.cnf 文件：vim /etc/my.cnf
新增一行：default-storage-engine=MyISAM 
重启MySQL：systemctl restart mysqld

-- 单独为一张表设置存储引擎
CREATE TABLE 表名( 建表语句; ) ENGINE = 存储引擎名称;
ALTER TABLE 表名 ENGINE = 存储引擎名称;
MyISAM 和 InnoDB 的区别是什么
对比项 MyISAM InnoDB
外键 不支持 支持
事务 不支持 支持
行表锁 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点 并发查询，节省资源、消耗少、简单业务 并发写、事务、多表关系、更大资源
默认安装 Y Y
默认使用 N Y
自带系统表使用 Y N

三、MySQL事务

什么是数据库事务？事务的特性是什么？

  • 数据库操作的最小工作单元
  • 要么同时成功，要么同时失败，不可分割

什么是ACID？

  • 原子性 atomicity

    只做一个步骤

    1. 给钱
    2. 去买
    3. 给我

    事务是数据库的逻辑工作单位，事务中包含的各操作要么都做要么都不做

  • 一致性 consistency

    结果保证，保证要吃完

    事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

  • 隔离性 isolation

    并发事务互相干扰 各吃各的不被干扰

    一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

  • 持久性 durability

    吃到肚子里

    也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

脏读、丢失修改、不可重复读、幻读

脏读（Dirty read）

是一个事务在处理过程中读取了另外一个事务未提交的数据

当一个事务正在访问数据并且对其进行了修改，但是还没提交事务，这时另外一个事务也访问了这个数据，然后使用了这个数据，因为这个数据的修改还没提交到数据库，所以另外一个事务读取的数据就是“脏数据”，这种行为就是“脏读”，依据“脏数据”所做的操作可能是会出现问题的。

sequenceDiagram
actor a as Client1
participant s as Server
actor b as Clienct2
a ->> +s: start trans
a ->> s:  update t set xx=1 where id = 1
b ->> s: select xx from t where id = 1
s -->> b: xx = 1
a ->> a: error
a ->> s: rollback
s -->> -a: success
Note over b: 刚才读到的xx=1便是脏数据

修改丢失（Lost of modify）

是指一个事务读取一个数据时，另外一个数据也访问了该数据，那么在第一个事务修改了这个数据之后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，这种情况就被称为修改丢失

sequenceDiagram
actor a as Client1
participant s as Server
actor b as Clienct2
a ->> +s: start trans
b ->> s: start trans
a ->> s: update t set a = a+3 where id = 1
b ->> s: update t set a = a+5 where id = 1
a ->> s: commit
s -->> -a: success
b ->> s: commit
s -->> b: success
note over s: 此时 a 并不是 a+8, 而是 a+5

不可重复读（Unrepeatableread）

指在一个事务内多次读取同一数据，在这个事务还没结束时，另外一个事务也访问了这个数据并对这个数据进行了修改，那么就可能造成第一个事务两次读取的数据不一致，这种情况就被称为不可重复读。

sequenceDiagram
actor a as Client1
participant s as Server
actor b as Clienct2
a ->> s: start trans
a ->> s: select xx from t where id = 1
s -->> a: xx=1
b ->> s: start trans
b ->> s: update t set xx=2 where id = 1
b ->> s: commit
s -->> b: success
a ->> s: select xx from t where id = 1
s -->> a: xx=2
note over a,s: 在同一个事物中读取到两种结果
a ->> s: commit
s -->> a: success

幻读（Phantom read）

是指同一个事务内多次查询返回的结果集总数不一样（比如增加了或者减少了行记录）。

幻读与不可重复读类似，幻读是指一个事务读取了几行数据，这个事务还没结束，接着另外一个事务插入了一些数据，在随后的查询中，第一个事务读取到的数据就会比原本读取到的多，就好像发生了幻觉一样，所以称为幻读。

sequenceDiagram
actor a as Client1
participant s as Server
actor b as Clienct2
a ->> s: start trans
a ->> s: select count(id) from t
s -->> a: count(id) = 5
b ->> s: start trans
b ->> s: insert into t values(xx)
b ->> s: commit
s -->> b: success
a ->> s: select count(id) from t
s -->> a: count(id) = 6
note over a,s: 在同一个事物中读取到两个结果集数量
a ->> s: commit
s -->> a: success
不可重复读和幻读有什么区别？

不可重复读 针对的是一份数据的修改

幻读 针对的是行数修改

MySQS是如何避免事务并发问题的

与分布式系统类似(CAP定理及base理论), 为了保证一致性就要相应牺牲性能

事务隔离级别
事务隔离级别 脏读 不可重复读(被修改) 幻读(删减)
读未提交(read-uncomitted) 是 是 是
读已提交(read-commited) 否 是 是
可重复度(repeatable-read) 否 否 是
串行化(serializable) 否 否 否
默认的级别是什么

MySQL InnoDB存储引擎默认的事务隔离级别是可重复读（REPEATABLE-READ）

SELECT @@tx_isolation; -- MySQL 5.7
SELECT @@transaction_isolation; -- MySQL 8.0

MySQL事务隔离是如何实现的

主要是读写锁和MVCC

锁定读

类似Redis分布式锁一样, 读要获取共享(读)锁, 写要获取写锁. 共享锁之间不互斥, 共享锁和写锁互斥, 写锁和写锁互斥. 锁竞争时要等其他释放完

  • 行锁（记录锁）：解决的就是多个事务同时更新一行数据
  • 间隙锁：解决的就是多个事务同时更新多行数据
select ... lock in share mode;
select ... for update;
insert, update, delete
非读定锁

MVCC (Multi-Version Concurrency Control) 多版本并发控制

说一下MVCC内部细节

MVCC 的实现依赖于：隐藏字段、Read View、undo log

隐藏字段

事务标识符、回滚指针、聚簇索引ROW_ID

Read View

不同事务隔离级别, 要判断版本链中的哪些版本时当前事务可见的.

undo log

除了用来回滚数据，还可以读取可见版本的数据。以此实现非锁定读

Mysql事务一致性，原子性是如何实现的？

过程中式靠读定锁和MVCC

灾备方面通过Redo log实现

Mysql事务的持久性是如何实现的？

使用Redo log保证了事务的持久性

事务提交先写日志, 就是常说的WAL(write ahead log)机制. 如果断电重启就从 redolog 中回复, 如果redolog失败就意味着修改失败, 事务直接回滚

表级锁和行级锁有什么区别？

  • 表级锁：

    串行化（serializable）时，整表加锁，事务访问表数据时需要申请锁，虽然可分为读锁和写锁，但毕竟是锁住整张表，会导致并发能力下降，一般是做 DDL 处理时使用

  • 行级锁：

    除了串行化（serializable）时 InnoDB 使用的都是行级锁，只锁一行数据，其他行数据不影响，并发能力强。

什么是行级锁？Mysql如何完成的？

行级锁实现比较复杂不是单纯锁住一行数据，是由mvcc完成的。

什么是共享锁（读锁）？

共享锁或S锁，其它事务可以继续加共享锁，但不能加排它锁

什么是排它锁（写锁/独占锁）？

排它锁或X锁，在进行写操作之前要申请并获得，其它事务不能再获得任何锁。

什么是意向锁？

它分为意向共享锁（IS）和意向排他锁（IX）

一个事务对一张表的某行添加共享锁前，必须获得对该表一个IS锁或者优先级更高的锁。
一个事务对一张表的某行添加排他锁之前，它必须对该表获取一个IX锁。

意向锁属于表锁，它不与innodb中的行锁冲突，任意两个意向锁之间也不会产生冲突，但是会与表锁（S锁和X锁）产生冲突

InnoDB支持哪几种锁？

表锁，行锁，间隙锁，Next-Key锁等

在Serializable中读加共享锁，写加排他锁，读写互斥

两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

当前读和快照读分别是什么？

当前读 ：在锁定读（使用锁隔离事物）的时候读到的是最新版本的数据

快照读：可重复读（repeatable-read）下 mvcc生效读取的是数据的快照，并不是最新版本的数据（未提交事物的数据）

什么是XA协议

说一下mysql死锁的原因和处理方法
事务 a
update t ... where id=100 更新  加行锁
update t ... where id=200 更新  已加锁

事务 b

update t ... where id=200 更新 加行锁
update t ... where id=100 更新 已加锁

排查

  • 正在运行的任务
    • show full processlist; 找到卡主的进程
  • 解开死锁
    • UNLOCK TABLES ；
  • 查看当前运行的事务
    • SELECT * FROM information_schema.INNODB_TRX;
  • 当前出现的锁
    • SELECT * FROM information_schema.INNODB_LOCKS;
  • 观察错误日志
  • 查看InnoDB锁状态
    • show status like "innodb_row_lock%";

MySQL日志

MySQL会产生几种日志?

  • 错误日志(error log)
  • 慢查询日志(slow query log)
  • 一般查询日志(general log)
  • 重写日志(redo log)
  • 回滚日志(undo log)
  • 二进制日志(bin log)
bin log作用是什么?
---
title: update t set xx=20 where id = ? 的流程
---
flowchart TB
subgraph undo
	direction LR
	a1[undo log age=18]
	a2[数据修改之前的值]
	a3[事务失败后的回滚 \n mvcc访问老版本的数据]
	a1 --- a2 --- a3
end
subgraph redo
	direction LR
	b1[redo log age=20]
	b2[数据修改之后的值]
	b1 --- b2
end
subgraph binlog
	direction LR
	c1[bin log sql]
	c2[恢复, 主从同步]
	c1 ---c2
end
undo --> redo --> binlog
bin log作用是什么？

bin log最大的用处就是进行主从复制，以及数据库的恢复。

redo log作用是什么？

redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据。

undo log作用是什么？

undo log主要用来回滚到某一个版本，是一种逻辑日志。

undo log记录的是修改之前的数据，比如：当 delete 一条记录时，undolog 中会记录一条对应的 insert 记录，从而保证能恢复到数据修改之前。在执行事务回滚的时候，就可以通过 undo log 中的记录内容并以此进行回滚。

undo log 还可以提供多版本并发控制下的读取（MVCC）。

MySQL日志是否实时写入磁盘

bin log刷盘机制是如何实现的?

通过 sync_binlog 参数配置

redo log刷盘机制是如何实现的?
undo log刷盘机制是如何实现的?

MySQL集群同步时为什么使用binlog? 优缺点是什么?

  • binlog 是 mysql 提供的日志，所有存储引擎都可用。
  • 支持增量同步
  • binlog 还可以供其他中间件读取，比如同步到 hdfs 中
  • 如果复制表数据：
    • 不支持某个阶段回放
    • 直接复制数据过程中一旦中断复制（比如断网），很难确定复制的offset

MySQL开发

可以使用MySQL直接存储文件吗？

可以使用 BLOB (binary large object)，用来存储二进制大对象的字段类型。

Emoji乱码怎么办？

使用utf8mb4

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，一般情况下使用utf8也就够了。

如何存储ip地址？

  • 使用无符号整型

    • 4个字节即解决问题

    • 可以支持范围查询

    • INET_ATON() 和 INET_NTOA() ipv6 使用 INET6_ATON() 和 INET6_NTOA()

长文本如何存储?

可以使用 Text 存储

char与varchar的区别？如何选择？

  1. char 的优点是存储空间固定（最大255），没有碎片，尤其更新比较频繁的时候，方便数据文件指针的操作，所以存储读取速度快。缺点是空间冗余，对于数据量大的表，非固定长度属性使用 char 字段，空间浪费。

  2. varchar 字段，存储的空间根据存储的内容变化，空间长度为 L+size，存储内容长度加描述存储内容长度信息，优点就是空间节约，缺点就是读取和存储时候，需要读取信息计算下标，才能获取完整内容。

是否有过mysql调优经验？

  1. 根据业务进行表(结构)设计调优
  2. 索引调优
  3. 通过慢查询进行SQL调优
  4. 数据库参数调优

线上排查慢SQL

查看及开启

-- 默认关闭
SHOW VARIABLES LIKE '%slow_query_log%';

-- 开启
set global slow_query_log=1; -- 只对窗口生效，重启服务失效

-- 慢查询日志记录long_query_time时间
SHOW VARIABLES LIKE '%long_query_time%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

分库分表

  1. 能部分就不分
  2. 单机性能下降明显的时候
  3. 增加缓存（通常查询量比较大），细分业务
  4. 首先尝试主被集群，独写分离
  5. 尝试分库
  6. 尝试分表 => 冷热数据分离
  7. 大数据量下配合es完成高效查询

千万数据量, 如何优化

  1. 前端优化, 减少查询次数
    1. 合并请求
    2. 会话保存
    3. 控制幂等, 避免无效刷新
  2. 多级缓存
  3. 使用合适的字段, 例如varchar换成char
  4. 高效使用索引
    1. explain查看索引使用情况
    2. 最好索引覆盖
    3. 符合索引观察key_len索引使用情况
    4. 有分组, 排序, 注意file sort, 合理配置相应的buffer
  5. 多表联查是否可以设置冗余字段
  6. 分而知之：把服务拆成更小力度的服务
  7. 独写分离
  8. 冷热数据分库存储（按时间或热度分库）
  9. 等

超大分页怎么处理？

-- 优化前
select name from user limit 10000,10;

-- 通过索引优化
select name from user where id > 10000 limit 10;

-- 需要order by的时候, 一定要先过滤条件, 再进行排序, 避免全表排序

-- 延迟关联或子查询优化
select a.* from t1 as a,
(select id from t1 where $condition limit 100000, 20) as b where a.id = b.id

写在后面

聚簇索引

存储引擎表示不同的数据在磁盘的不同组织形式, 但是底层数据结构都是B+树

是否是聚簇索引取决于数据是否跟索引放在一起

他只有一个聚簇索引, 向 innodb 插入数据的时候, 必须要包含一个索引的 key 值, 这个索引可以是主键, 可以是唯一键, 如果都没有, 那么就是一个自生成6字节的 rowid

但是如果他有多个聚簇索引, 那数据要放很多份, 因此只能有一个聚簇索引, 但是可以有很多非聚簇索引

myisam是非聚簇索引的, 他的b+数里面磁盘块放的式指针和数据行的地址

回表

假设有一张表, 有 id , name, age, gender 四个字段, id 式主键, name 是索引列
select * from class_a where name='zhangsan'

先根据 name 查询 id, 再根据 id 查询整行的记录, 走了2棵 B+ 树, 此时这种现象叫做回表

当根据普通索引查询到聚簇索引的key值之后,再根据key值再根据聚簇索引获取所有行记录

索引覆盖

select id,name from class_a where name='zhangsan'

根据 name 可以直接查询到 id , name 两个列的值, 直接返回即可, 不需要从聚簇索引查询任何数据, 此时叫做索引覆盖

最左匹配

主键一般是一个列, 可能还有联合主键, 索引也会有组合索引

假设有一张表, 有 id , name, age, gender 四个字段, id 式主键, name, age是组合索引

组合索引使用的时候必须先匹配 name, 然后匹配 age
select * from table where name=? and age=? 用
select * from table where name=? 用
select * from table where age=? 不用
select * from table where name=? and age=? 用 mysql内部有优化器, 会调整对应的顺序

脏读、幻读、不可重复读

  • 脏读(Dirty Read): 是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致。
    解决方案: 修改时加排他锁(写锁),直到事务提交后再释放,读取时加共享锁(读锁),其他事务只能读不能更.
  • 幻读(Phantom Read): 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功。
  • 不可重复度(Non-repeatable read):
文章作者: 👑Dee👑
文章链接: https://www.saddyfire.cn/2023/05/24/inte-point-01/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 👑Dee👑 !
Database InterviewPoint
  目录