Mysql夺命连环50问

zhangtq2023年8月13日大约 17 分钟

Mysql夺命连环50问

B树和B+树的区别是什么

B树的特点
- 多路，非二叉树
- 每个节点既保存索引又保存数据
- 查找时相当于二分查找
B+树的特点
- 多路，非二叉树
- 只有叶子节点保存数据
- 搜索时相当于二分查找
- 叶子节点增加了指针
B树和B+树的区别
- 关键字的数量不同；B+树中分支结点有m个关键字，其叶子结点也有m个，其关键字只是起到了一个索引的作用，但是B树虽然也有m个子结点，但是其只拥有m-1个关键字。
- 存储的位置不同；B+树中的数据都存储在叶子结点上，也就是其所有叶子结点的数据组合起来就是完整的数据，但是B树的数据存储在每一个结点中，并不仅仅存储在叶子结点上。
- 分支结点的构造不同；B+树的分支结点仅仅存储着关键字信息和儿子的指针（这里的指针指的是磁盘块的偏移量），也就是说内部结点仅仅包含着索引信息。
- 查询不同；B树在找到具体的数值以后，则结束，而B+树则需要通过索引找到叶子结点中的数据才结束，也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径。

范围查找导致索引失效原理

假设对与student表来说，存在索引age
select * from Student   where age > 40

失效原因：索引是按照顺序存储的，而范围查询需要跳过一些记录，因此无法利用索引的顺序性进行优化
更深层次点：针对范围查询，如果走索引，需要扫描所有索引找到 age> 40的id，然后回表取查询，然后再聚簇索引里，通过id再去叶子节点遍历查找，那么mysql针对这种情况认为，这种情况先去查索引，再取全表扫描时一种绕路，不如直接去全表扫描
解决办法：
- 使用覆盖索引：select age from Student where age > 40
- 使用联合索引：将需要的字段建为联合索引
- 将范围查询放在最后

覆盖索引原理

索引里的字段正好是select需要的字段

order by为什么会导致索引失效

select * from books order by title
select title from books order by title

原因分析
- 造成这种情况出现主要是因为索引树的原因：当查询字段为 '*' 的时候，虽然可以直接通过 title 字段找到对应的叶子结点数据，但是在返回之前还得拿着这个去进行回表查询，表中有五个字段那么就需要进行五次回表查询，但是由于数据量并不是很大，所以 Mysql就会直接把所有的数据扫描一遍然后拿到内存中进行排序，这样就不用进行回表的操作。
- 当查询字段为对应索引字段时候，可以直接通过索引拿到对应的返回字段，不需要进行回表操作，少了这一步操作速度就很快了，再加上 b+ 树的叶子结点是已经排好序的了，也不需要进行排序的操作。少了这两部操作 mysql就会去走索引了。
总结
- 需要判断查询的返回字段是否被索引覆盖，如果全部被索引覆盖了那么就会走索引，如果没有全部覆盖mysql 就会根据数据量的大小来判断是否进行全表扫描或者先走索引再

覆盖索引底层原理

- 针对非聚簇索引，叶子节点保存的是id值，非数据，如果select的字段不是主键，需要根据id一条一条回表查询数据，但是如果查询的字段刚好是建立索引的字段时，其实在索引里就能找拿到响应的数据，不需要回表查询

Mysql有哪些存储引擎

InnoDB
- 默认数据引擎，支持事务，支持行级锁和表级锁，支持外键，保存行数，时聚簇索引
MyISAM
- 5.7之前的数据引擎，不支持事务，保存行数，只支持表锁，非聚簇索引
Merory
- 数据文件在内存中
- 重启后表结构还在，但是数据会丢失

数据库字段应该如何选择

int > date,time > enume, char> varchar > blob,text
可以选择整形就不要选择字符串
够用就行，不用慷慨
尽量避免使用null

说下事务的基本特性

原子性：一个事务时一个不可分割的单位，要么全部成功，要么全部失败回滚
一致性：事务是从一个一致性状态到另一个一致性状态的改变（有一个合法数据到另一个合法数据的转变，），事务结束之后，数据要符合预设规则
隔离性：不同事务之间相互独立。这里时说的事务在执行期间对数据的修改能力
持久性：事务一旦提交，所做的修改永久保存在数据库中

事务并发可能引发什么问题

脏读：B事务可以读到A事务没有提交的数据，这时候A撤销对数据修改，那么B就是读到了脏数据
不可重复读：由于其他事务对某条数据做了修改，事务A在多次查询某个数据的结果不同
幻读：事务B前后两次读取一个范围内的数据，但事务B在第二次读取时，事务A新增了数据，导致两次读取到的行数不一致
注
幻读和不可重复度有些相似，但是幻读强调的是集合的增减，而不是单条数据的更新

简单描述下mysql的各种索引

普通索引
- 建立在普通字段上的索引为普通索引
主键索引
- 建立在主键上的索引，一张表只有一个主键索引，索引值不能为空
唯一索引
- 建立在unique字段上的索引，可以有多个唯一索引，索引列允许为空
联合索引
- 在多个字段上建立的索引，查询的时候遵循最左前缀原则

什么是三星索引

如果使用三星索引，一次查询通常只是需要一次磁盘IO以及一次窄索引片的扫描
三星
- 一个查询相关的索引行是相邻或者距离足够近获得一星
- 索引中的数据顺序和查找中的排列顺序一致获得一星
- 索引中的列包含了查询列获得一星

InnoDB一颗B+树可以存放多少数据

计算一页大概是16K, 一个bigint的大小为8字节，一个指针的大小大概为6字节，如果假设叶子节点的每行数据大小为1k 那么，一颗高度为3的树存放的数据大概为 1000 * 1000 * 16 大概有两千多万

如何提高插入顺序

合并多条insert
- insert into t values(a, b, c),(a1, b1, c1,)....
修改批量插入的缓存
设置innodb_flush_log_at_trx_commit =0
- 0: buffer pool中的数据将以每秒一次的频率写入到log file中，同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何buffer pool 到log file的写入，也不会出发文件系统到磁盘的刷新操作
- 1：事务每次提交的时候出发buffer pool 到log file，以及文件系统到磁盘的刷新操作
- 2：事务提交会触发biffer pool 到log file的刷新，但是不会触发磁盘文件系统到磁盘的同步
手动使用事务
- 因为Mysql是默认autocommit的这样每提交一次，会触发一次事务，为了减小事务的创建与销毁，手动使用事务
- 即start tracsaction ..... commit;

什么是全局锁、共享锁、排他锁

全局锁：对整个数据库实例加锁，应用场景是做全库逻辑备份
共享锁：又叫读锁，其他事务可以并发读取数据和加读锁，但是不能对数据进行修改
排他锁：又叫写锁，某个事务加了排他锁，只能这个事务进行读写，在这个事务结束之前，其他事务不能对这个数据加任何锁，其他事务可以读，但是不能进行写

谈一谈mysql中的死锁

死锁是指多个线程在执行过程中，由于争夺资源造成一种相互等待的现象
使用命令show engine innodb status 查看最近的一次死锁
解决办法
- 设置超时时间

如何进行读写分离

利用主从复制，主库负责写，从库负责读
主从原理：
- 主库开启binlog，所有新建、更新、删除操作都会在binlog生成记录
- 从库定期检测主库是否有变化
- 主库开启一个dump log线程
- 从库开启一个IO线程，将数据写入relay log中
- 从库开启一个sql线程，将relay log中的sql进行重放

mysql 分库分表

垂直分库分表
- image-20230814015240307
- 将不同业务的表放到不同的数据库
- 不能完全解决性能瓶颈，不如单个数据表变大 2千万或者更大，还是会变慢
水平分库分表
- 解决单个表过大的问题
- 可以按照uuid 取模来进行分表，或者按照插入时间来进行分表，通过路由函数确定数据在哪个库里

索引的原理

将无序的数据变成有序的查询

将创建了索引列的数据进行排序
对排序结果生成倒排索引
在倒排内容上拼上数据地址链
在查询时，先查找倒排索表内容，在去除数据地址链，从而拿到具体数据

聚簇索引和非聚簇索引的区别

什么是聚簇索引

- 在innoDB中，索引时按照B+树的数据结构存储的，聚簇索引就是每张表按照主键索引生成一张B+树，叶子节点存储就是每行数据，称为数据页。这个特性决定了索引组织表中的数据也是索引的一部分

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式
我们日常工作中，自行添加的索引都是辅助索引，辅助索引为了找主键的二级索引，需要先找到主键，然后在通过主键索引找数据
一张表只有一个聚簇索引

什么是非聚簇索引

- 在**聚簇索引之上创建的索引称之为辅助索引**，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
- 在InnoDB中非聚簇索引需要回表查询，因为非聚簇索引保存的是主键
- 在MyISAM中非聚簇索引不需要回表查询，因为MyISAM叶子节点保存的时数据的指针，根据指针可以直接找到数据

两者区别

存储方式不同
- 聚簇索引是索引和数据混合在一起存储的，而非聚簇索引是分开存储的
唯一性不同
- 聚簇索引是唯一的，非聚簇索引可以有多个
查询效率不同
- 聚簇索引由于不用回表查询，效率比非聚簇索引高
插入效率不同
- 对于聚簇索引来说，由于数据按照索引顺序存储，因此在插入新数据时，可能需要移动已有的数据，因此插入数据的效率较低；而对于非聚簇索引来说，插入数据时只需要更新索引，因此效率相对较高。

MySQL索引各自优势

在Mysql中使用较多的有B+树结构的索引和哈希结构的索引
B+ 树结构的索引和hash索引的对比
B+ 树优点
- B+树是一个平衡多叉树，从根节点到叶子节点的高度差不超过1，而且同层之间有指针相互连接
- 所以从根节点到叶子节点搜索效率比较稳定，而且在叶子节点也可以利用双指针快读移动查找
B+ 树缺点
- 相对于hash索引，搜索效率低下
Hash索引的优点
- hash索引是通过将key通过hash函数将key和数据指针映射在哈希表中，通过hash函数，可以在
- O(1)的时间复杂读内查询数据，查询速率高
Hash索引缺点
image-20230814120558494
- 当初先哈希冲突时，多个key 映射到表中的同一个部位，需要遍历key对应的链表，搜索效率会退化
- 不支持范围查找
- 没法利用索引排序

Mysql 锁类型有哪些

按照使用方式划分：乐观锁、悲观锁
按照锁级别划分：共享锁、排他锁
按照锁粒度划分：表级锁、行级锁、页级锁
按照操作划分：DML锁、DDL锁
按照加锁方式划分：自动锁、显示锁

事务的基本特性和隔离级别

四大特性
- 原子性
- 有一致性
- 隔离性
- 持久性
隔离级别
- 取未提交
- 读已提交
- 不可重复读
- 序列化

隔离级别	说明
读未提交	一个事务还没提交时，它做的变更就能被别的事务看到
读提交	一个事务提交之后，它做的变更才会被其他事务看到
可重复读	一个事务中，对同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。InnoDB默认级别。
串行化	事务串行化执行，每次读都需要获得表级共享锁，读写相互都会阻塞，隔离级别最高，牺牲系统并发性。

不同的隔离级别是为了解决不同的问题。也就是脏读、幻读、不可重复读。

隔离级别	脏读	不可重复读	幻读
读未提交	可以出现	可以出现	可以出现
读提交	不允许出现	可以出现	可以出现
可重复读	不允许出现	不允许出现	可以出现
序列化	不允许出现	不允许出现	不允许出现

MVCC是什么

概念
- 多版本并发控制，读取数据时通过类似一种快照的方式将数据保存下来，这样读锁和写锁就不会冲突了
工作范围
- MVCC只在read commited 和repeatable read两个隔离级别下工作。其他两个隔离级别和mvc不兼容。
- 这是因为read uncommited 总是会读取最新的数据行，而不是符合当前事务的数据行
- 而serialazable对于所有读取数据加锁

ACID是怎么保证的

原子性是通过undo log来实现的
- 当事务对数据库进行修改时，InnoDB会生成对应的 undo log；如果事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。undo log 属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB 会根据 undo log 的内容做与之前相反的工作
持久性是通过redo log来实现的
- InnoDB提供了缓存，Buffer Pool 中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：
  - 当读取数据时，会先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；
  - 当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中。
- Buffer Pool 的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时 Buffer Pool 中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。
- 所以加入的redo log 当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作
- 当事务提交时，会调用fsync接口对redo log进行刷盘
- redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。而且这样做还有两个优点：
  - 刷脏页是随机 IO，redo log 顺序 IO
  - 刷脏页以Page为单位，一个Page上的修改整页都要写；而redo log 只包含真正需要写入的，无效 IO 减少。
- 对于redo log 是有两阶段的：commit 和 prepare 如果不使用“两阶段提交”，数据库的状态就有可能和用它的日志恢复出来的库的状态不一致
隔离性是通过锁和mvcc

DDL和DML的区别

本质区别：DDL代表数据定义语言，是一种有助于创建数据库模式的SQL命令。而，DML代表数据操作语言，是一种有助于检索和管理关系数据库中数据的SQL命令。
命令上的区别：DDL中常用的命令有：create，drop，alter，truncate和rename等等。而，DML中常用的命令有：insert，update，delete和select等等。

Mysql集群如何搭建

读写分离集群架构

适用于读多写少的应用，可以分摊数据库的压力
可以配合MHA实现高可用
如果数据量很大的话，单机压力会很大
image-20230815111534409
可以配合MHA中间件方案实现高可用

分库分表集群架构

适用于十几亿数据总量的大型应用，不具备高可用
某一台服务器挂掉之后，导致数据查询失败
image-20230815111636193
分库分表的分片算法有
- 范围法：按照范围进行分片，比如时间
  - 有点：结构简单，扩展容易
  - 缺点：数据分布不均衡，数据的局部负载压力大
- hash法：
  - image-20230815113956437
  - 优点：数据分配均衡
  - 缺点：节点扩展复杂，数据迁移难度比较大
  - 如果采用hash算法，一般建议部署足够多的节点

主流MySQL集群架构

采用读写分离和分库分表相结合的架构
image-20230815111656663

自带MySQL Cluster集群架构

管理节点
- 全局管理者，起到联系并管理整个集群的作用，整个集群只有一个管理节点，控制其他节点启停
数据节点
- 每个节点都会存储所有数据，当一个节点宕机后，其他节点可以正常工作
sql节点
- 负责与web应用程序交互，承接来自上层的sql命令·

Mysql如何保证数据的一致性

半同步复制，等从库复制成功才返回写成功
设一个key记录着一次写的数据,然后设置一个同步时间，如果在这个时间内，有一个读请求,看看对应的key有没有相关数据,有的话,说明数据近期发生过写事件，这样key的数据就继续读主库，否则就读从库

Mysql默认连接数和超时时间

通常MySQL的最大连接数默认是151 最大可以达到16384
Mysql 默认8小时连接超时