MySQL

Sunshine;

于 2021-09-08 10:45:35 发布

阅读量158

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/a962565183/article/details/120069550

版权

1. B树和B+树的区别，为什么Mysql使用B+树

B树特点

节点排序
一个节点了可以存多个元素，多个元素也排序了

B+树特点

拥有B树的特点
叶子节点有指针
非叶子节点上的元素在叶子节点上都冗余了，也就是叶子节点中存储了所有的元素，并且排好顺序
Mysql索引使用的是B+树，因为索引是用来加快查询的，而B+树通过对数据进行排序所以是可以提高查询速度的,然后通过一个节点中可以存储多个元素，从而使得B+树的高度不会太高；并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等sQL语句。

一个Innodb页默认16k，所以一般情况下一颗两层B+树能够存2000万条数据

3. MySQL锁有哪些，如何理解

行锁:锁某行数据，锁粒度最小，并发度高
表锁:锁整张表，锁粒度最大，并发度低
间隙锁:锁的是一个区间

还可以分为

乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现的
悲观锁:上面所的行锁、表锁等都是悲观锁

在事务的隔离级别实现中，就需要利用锁来解决幻读

4. Mysql慢查询如何优化

检查是否走了索引，如果没有则优化SQL利用索引
检查所利用的索引，是否是最优索引
检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
检查表中数据是否过多，是否应该进行分库分表了
检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

5. Explain语句结果中各个字段分表表示什么

在这里插入图片描述

6. 索引覆盖

索引覆盖就是一个SQL在执行时，可以利用索引来快速查找，并且此SQL所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL走完索引后不用回表了，所需要的字段都在当前索引的叶子节点上存在，可以直接作为结果返回了

7. 最左前缀原则

当一个SQL想要利用索引是，就一定要提供该索引所对应的字段中最左边的字段，也就是排在最前面的字段，比如针对a,b,c三个字段建立了一个联合索引，那么在写一个sql时就一定要提供a字段的条件，这样才能用到联合索引，这是由于在建立a,b,c三个字段的联合索引时，底层的B+树是按照a,b,c三个字段从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则

8. Innodb是如何实现事务的

Innodb通过Buffer Pool、LogBuffer、RedoLog、UndoLog来实现事务

以一个update语句为例

Innodb在收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在Buffer Pool中
执行update语句，修改Buffer Pool中的数据，也就是内存中的数据
针对update语句生成一个RedoLog对象，并存入LogBuffer中
针对update语句生成undolog日志，用于事务回滚
如果事务提交，那么则把RedoLog对象进行持久化，后续还有其他机制将Buffer Pool中所修改的数据页持久化到磁盘中如果事务回滚，则利用undolog日志进行回滚

9. Redis和Mysql如何保证数据一致

先删除Redis缓存数据，再更新Mysql，再次查询的时候在将数据添加到缓存中，这种方案能解决1方案的问题，但是在高并发下性能较低，而且仍然会出现数据不一致的问题，比如线程1删除了Redis缓存数据，正在更新Mysql，此时另外一个查询再查询，那么就会把Mysql中老数据又查到Redis中
延时双删，步骤是:先删除Redis缓存数据，再更新Mysql，延迟几百毫秒再删除Redis缓存数据，这样就算在更新Mysql时,有其他线程读了Mysql，把老数据读到了Redis中，那么也会被删除掉，从而把数据保持一致

10. 索引的基本原理

将表中每一个值进行hash算法，将hash值放到一个hash表当中，这个hash表放到内存当中随时都能够找到，这个表是根据hash值顺序，那么做查询就很方便了
在查询的时候，先拿到倒排表的内容，再取出数据地址链，从而拿到具体数据

11. mysql聚簇和非聚簇索引的区别

都是B+树的数据结构
聚簇索引：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的

**非聚簇索引：**叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。

优势

查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高
聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的
聚簇索引适合用在排序的场合，非聚簇索引不适合

劣势

维护索引很昂贵，特别是插入新行或者主键被更新导致分页的时候
表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就哙出现聚簇索引有可能有比全表扫面更慢

在这里插入图片描述

12. mysql索引的数据结构，各自优劣

存储引擎的默认索引实圳为∶B+树索引。对于哈希索引来说，底层的数据结构就是哈布表，因此在绝大多数需灭为单条记录查询的时候，可以选择哈希索引，查询性能最快;其余大部分场景，建议选择BTree索引。

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。
在这里插入图片描述
哈希索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值;前提是键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据; 在这里插入图片描述
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题。

13. 索引设计的原则?

对于数据较少的情况下，就没必要建索引，因为还要维护索引表

不要过度索引，索引需要额外的磁盘空间，

定义有外键的话一定要建立索引

更新频繁的字段不适合创建索引

若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

对于定义为text、image和bit的数据类型的列不要建立索引。

14. 锁的类型有哪些

基于锁的属性分类:共享锁、排他锁。

基于锁的粒度分类:行级锁(INNODB)、表级锁(INNODB、MYISAM)、页级锁(BDB引擎)、记录锁、间隙锁、临键锁。

基于锁的状态分类:意向共享锁、意向排它锁。

共享锁

共享锁又称读锁，简称S锁;当一个事务为数据加上读锁之后，其他事务只能对该数据加读
锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共
享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复
读的问题。

排他锁

排他锁又称写锁，简称X锁;当一个事务为数据加上写锁时，其他请求将不能再为数据加任
何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时
候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务
释放了锁才能进行对表进行访问;

特点:粒度大，加锁简单，容易冲突;

行锁

行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被

锁住的记录不能访问，其他的记录可正常访问;

特点:粒度小，加锁比表锁麻烦，不容易冲究，相比表锁支持的并发要高;

记录锁

记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事
务在加锁后锁住的只是表的某一条记录。

精准条件命中，并且命中的条件字段是唯一索引

加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

页锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突
多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般

间隙锁

属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的
相邻ID之间出现空隙则会形成个区间，遵循左开右闭原则。

临建锁

也属于行锁的一部分，总结来说就是记录锁和间隙锁的组合，临建锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住

如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是意向锁。

意向共享锁

当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

意向排他锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

15. mysq执行计划怎么看

在这里插入图片描述
id:是一个有顺序的编号，是查询的顺序号，id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。

selectType表示查询中每个select子句的类型

key_len:可以根据这个字段了解索引命中的长度

ref:命中索引那么这个索引的字段名字是什么

rows:按照这个执行计划走，会读取多少数据

filtered：参数值是一个百分比，比如rows读取50行，但是根据后面的where条件进行筛选，如果只剩下10行需要返回，那么这是就是20%

extra：对结果集进行排序的时候有没有走索引

using filesort：表示mysql对结果集进行外部排序，表示没有通过索引去进行排序
using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错
using temporary：查询有使用临时表,一般出现于排序，分组和多表join的情况，查询效率不高，建议优化
using where ：sql使用了where过滤，效率较高

type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围:

const:通过一次命中，匹配一行数据 where id=1
system:表中只有一行记录，相当于系统表;
eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref:非唯—性索引扫描,返回匹配某个值的所有
range:只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>;
index:只遍历索引树
all:全表扫描

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

事务基本特性ACID分别是:

原子性指的是一个事务中的操作要么全部成功，要么全部失败。

一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设A只有90块，支付之前我们数据库里的数据都是符合约束的,但是如果事务执行成功了,我们的数据库数据就破坏约束了,因此事务不能成功,这里我们说事务提供了一致性的保证

隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。比如B给A转账，此时不允许C再给A转账，需要隔离开来

持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。

隔离性有4个隔离级别，分别是：

read uncommit：读未提交，可能会读到其他事务未提交的数据，也叫做脏读。
read commit：读已提交，两次读取结果不一致，叫做不可重复读。
- 不可重复读解决了脏读的问题，他只会读取已经提交的事务。
- 用户开启事务读取id=1用户，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。
repeatable read 可重复复读，这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读。read view只针对查询操作，如果是新增操作会影响可重复读
serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

幻读：读取的是一个范围如果读取1-10 如果有5条数据，会产生read view，如果在增加一个数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

17. 关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过

慢查询的优化首先要搞明白慢的原因是什么?是查询条件没有命中索引?是load了需要的数据列?还是数据量太大？

所以优化也是针对这三个方向来的

首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

18. ACID靠什么保证的？

A原子性回滚的日志信息，事务回滚时撤销已经执行成功的sql
C—致性由其他三大特性保证、程序代码要保证业务上的一致性
I隔离性由MVCC来保证
D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redolog恢复

MySQLServer有个binlog日志，MySQL主从同步就是通过这个binlog来实现的，从服务器将这个binlog当中的SQL拿过去执行保证根主服务一致

如何确定事务一定是成功的，那么在redolog日志当中有一个commit记录在这里插入图片描述

19. mysql主从同步原理

mysql主从同步的过程:

Mysql的主从复制中主要有三个线程: master (binlog dump thread), slave (I/o thread . SQLThread）, Master—条线程和Slave中的两条线程。

主节点binlog，主从复制的基础是主库记录数据库的所有变更记录到binlog。binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
主节点log dump线程，当binlog有变动时，log dump线程读取其内容并发送给从节点。
从节点I/o线程接收binlog内容，并将其写入到relay log 文件中。
从节点的SQL线程读取relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注:主从节点使用binglog文件+ position偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从position的位置发起同步。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

20. 简述MyISAM和InnoDB的区别

MyISAM:
不支持事务但具每次查询都具原子的·
支持表级锁，即每次操作是对整个表加锁;
存储表的总行数;
一个MYISAM表有三个文件:索引文件、表结构文件、数据文件;

采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

Innodb
支持ACID的事务，支持事务的四种隔离级别;
支持行级锁及外键约束:因此可以支持写群发;
不存储总行数; 在这里插入图片描述

21. 简述mysql中索引类型及对数据库的性能的影响

普通索引:允许被索引的数据列包含重复的值。
唯一索引:可以保证数据记录的唯一性。
主键:是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY 来创建。
联合索引:索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。
全文索引:通过建立倒排索引,可以极大的提升检索效率解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column;创建全文索引

为什么不能创建很多索引
索引可以极大的提高数据的查询速度。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。