面试题总结-1-mysql

柔情柴少

已于 2022-08-08 14:01:30 修改

阅读量322

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

于 2022-08-07 20:19:46 首次发布

本文链接：https://blog.csdn.net/wuqingchaishao/article/details/126149325

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1、MySQL慢查询会关注哪些返回值？

1-1、慢查询概念，顾名思义，查询时间超过配置参数指定阈值时长的查询语句

long_query_time 默认为10s

1-2、查询慢查询是否开启

show VARIABLES like 'slow_query_log'

结果 slow_query_log   OFF

开启慢查询  set GLOBAL slow_query_log=1/ON

再次查询  slow_query_log   ON

查询未使用索引慢查询是否是否记录

show VARIABLES like 'log_queries_not_using_indexes'

结果 log_queries_not_using_indexes OFF

开启未使用索引记录慢查询

set GLOBAL log_queries_not_using_indexes=1

查询超过多少秒记录到慢查询记录的时间

show VARIABLES like 'long_query_time'

set GLOBAL long_query_time = 1

设置超过1秒就记录慢查询日志

内容输出

show VARIABLES like 'log_output'

log_output FILE

慢查询日志日志输出到表

set GLOBAL log_output='TABLE'

------全局日志 general_log

1-3、explain列的解释：

select_type

1） simple：简单的select，不是用union或子查询

2）primary：最外层select

3) union：第二层，在select之后使用union

4）dependent union：union语句中的第二个select，依赖于外部子查询

5）union result：union的结果

6）subquery：子查询中的第一个select

7）dependent subquery：子查询中的第一个select，取决于外面的查询

8）derived：导出表的select（from子句的子查询）

table

显示这一行的数据是关于哪张表的

type

这是重要的列，显示连接使用了何种类型

从最好到最差的连接类型：const，eq_reg，ref，range，index和all

Type：告诉我们对表使用的访问方式，主要包含如下集中类型

1）all：全表扫描

2）const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须读一次

3）eq_ref：最多只会有一次匹配结果，一般是通过主键或唯一索引来访问

4）fulltext：进行全文索引检索

5）index：全索引扫描

6）index_merge:查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据

7）index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引

8）range：索引范围扫描

9）ref：join语句中被驱动表索引

10）ref_or_null：于ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询

11）system：系统表，表中只有一行数据

12）unique_subquery：子查询中的返回结果字段组合是主键或唯一约束

possible_keys

显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句

key

实际使用的索引。如果为null，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在select语句中使用use index（indexname）来强制使用一个索引或者用Ignore index（indexname）来强制MySQL忽略索引

key_len 使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref 显示索引的哪一列被使用了，如果可能的话，是一个常数

rows MySQL 认为必须检查的用来返回请求数据的行数

extra 关于MySQL如何解析查询的额外信息这里可以看到的例子是 Using temporary 和 Using filesort，意思MySQL根本不能使用索引，结果是检索会很慢

extra字段解释：

extra：查询中每一步实现的额外细节信息，主要会使一下内容：

1）distinct：查找distinct值，当MySQL找到了第一条匹配的结果时，将停止该值得查询，转为后面其他值查询

2）Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用

3）Range checked for each record（index map：N）通过MySQL官方手册描述，当MySQL Query Opitimizer 没有发现好的可以使用的索引时，如果发现前面表的列值已知，部分索引可以使用。对前面表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行

4）select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段时，MySQL Query Optimizer 会通过索引直接一次定位到所需的数据行完成整个查询。当然，前提是在Query中不能有group by 操作。如使用MIN（）或MAX（）的时候。

5）Using filesort：当Query中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer不得不选择相应的排序算法来实现

6）Using index：所需数据只需在Index即可全部获得，不需要再到表中获取数据

7）Using index for group-by：数据访问和Using index 一样，所需数据只需要读取索引，当Query中使用GROUP BY 或 DISTINCT子句时，如果分组字段也在索引中，extra中的信息就会是Using index for group-by

8）Using temporary：当MySQL在某些操作中必须使用临时表时，在Extra信息中就会出现Using temporary。主要常见于GROUP BY 和 ORDER BY 等操作中

9）Using where：如果不读取表的所有数据，或不是仅仅通过索引就可以获取所需要的数据，则会出现Using where信息

10）Using where with pushed condition：这是一个仅仅在NDBCluster存储引擎中才会出现的信息，而且还需要通过打开Condition Pushdown优化功能才可能被使用。控制参数为engine_condition_pushdown。

11）impossible WHERE noticed after reading const tables：MySQL Query Optimizer通过收集到的统计信息判断出不可能存在结果

12）No tables：Query语句中使用FROM DUAL 或不包含任何 FROM 子句

13）Not exists：在某些左连接中，MySQL Query Optimizer 通过改变原有Query的组成而使用的优化方法，可以部分减少数据访问次数。

1-4、type类型查询效率

ref > range > index

2、group by 和 order by 执行顺序

MySQL查询语句执行顺序

from on join where group by (avg、max、min聚合函数) having select distinct order by limit

3、主从模式，什么情况下出现主从延迟的情况？

1、主从同步基本原理
答：主库把写操作SQL写入bin log日志，当slave从库的io现场请求主库bin log日志时，主库会生成一个bin log dump线程，把bin log传给slave从库的io线程，io线程接收到bin log日志，将内容写入到本地relay log中继日志中，slave从库，会生成一个sql线程，把relay log中的操作写入slave库

2、主从模式
2-1   一主一从
2-2   一主多从
2-3   多主一从
2-4   主主复制
2-5   联级复制

3、主从同步 从库延迟问题？
3-1 Master_Log_File          slave从库的io线程正在读取的master主库的二进制日志的文件名称
3-2 Read_Master_Log_Pos      slave从库的io线程已经读取master二进制日志文件的位置
3-3 Relay_Log_File           slave中sql线程读取的中继日志文件名称
3-4 Relay_Log_Pos            slave的中继日志中，SQL线程读取和执行到的位置
3-5 Relay_Master_Log_File    有SQL线程执行的包含多数近期事件的主服务器的二进制文件名称
3-6 Slave_IO_Running         I/O线程是否被启动并且成功的连接到主服务器上
3-7 Slave_SQL_Running        sql线程是否被启动
3-8 Second_Behind_Master     从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计

3-9 mysql的主从复制都是单线程的操作，主库对所有的DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步，就是问题所在
-------->
-------->
slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施，DML和DDL的IO操作是随机的，不是顺序的，成本高很多，还可能导致slave上的其他查询产生lock争用，由于SLave_SQL_Running也是单线程，所以一个DDL卡主，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延迟
-------->
-------->
主库上相同的DDL执行时间也是10分钟，为什么只有slave会延时？
答：master可以并发，Slave_SQL_Running线程却不可以


名次解析：
DDL：(Data Definition Language)数据定义语言 用来定义数据库对象：数据库，表，列等。关键字：create,drop,alter等
DML：(Data Manipulation Language)数据操作语言  关键字：insert,delete,update等
DQL(Data Query Language)数据查询语言(最重要!!!)  用来查询数据库中表的记录(数据)。关键字：select，where等
DCL(Data Control Language)数据控制语言(了解)   用来定义数据库的访问权限和安全级别，及创建用户。关键字GRANT,REVOKE等

3-10、MySQL数据库主从同步延迟是怎么产生的？
答：1)当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，就会产生延时(从库压力过大)
    2)slave的大型query语句产生了锁等待-------->1、业务上读写压力大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高------->读写binlog带来的性能影响，网络传输延迟
    执行大事务，主库必须等事务执行完成才会写入binlog，传给从库，如果一个主库上语句执行10分钟，从库sql线程在执行这个事务的时候，就可能导致后面的DDL执行都会延迟10分钟

    3)从库的机器性能比主库性能差（一般主从数据库，分开放在不同机器，一旦一个机器发生宕机，保证高可用）

名次解析：
IOPS：每秒处理I/O的请求次数,IOPS指存储每秒可接受多少次主机发出的访问,主机的一次IO需要多少访问存储才可以完成，这里提到磁盘读写能力，比如每秒读100M，写50M，这个说明的是数据吞吐量,而IOPS指的则是每秒处理I/O的请求次数
QPS：每秒请求、查询次数  QPS=Questions/Uptime(Uptime换成自己定义的时间单位)
TPS: Transcantion Per Second 即每秒事务数 Mysql不是每个存储引擎都支持事务，所以就拿InnoDB来说,TPS主要设计提交和回滚
计算公式:TPS = (Commit + Rollback)/Seconds

3-11、MySQL数据库主从同步延迟解决方案
1)默认异步线程复制，吞吐量高,但是缺点明显，不能保证主从数据实时一致，也无法控制从库的延迟时间，因此它不适合要求主从数据实时同步的场景，另一个问题是可能会有数据丢失，例如主库宕机时，已经提交的事务可能还没有传到从库上，如果此时强行主从切换，可能导致新主库上的数据不完整
1)半同步复制(mysql5.5)
------>概念：master节点在执行完客户端提交的事务后不是立刻返回结果给客户端，而是等待至少一个slave节点接收并写入relay log中才返回客户端
------>特性：1、从库在连接主库时表明它是否支持半同步复制
            2、如果在主库启用了半同步复制，并且至少有一个支持半同步复制的从库，则主库上执行事务提交的线程将等待，直到至少一个半同步从库确认已收到事务的所有事件(此时从库会向主库发送ACK，Acknowledgement),或者直到发生超时
            3、只有在将事件写入其中继日志并且刷新到磁盘后，从库才会确认收到事务的事件，向主库发送ACK
            4、如果在没有任何从库确认事务的情况下发生超时，则主库将退化为异步复制。当至少有一个半同步从库赶上时，主库恢复半同步复制
            5、必须在主库和从库都启用半同步复制，否则使用异步复制
            6、当主库阻塞(等待来自从库确认)时，他不会返回执行事务的会话。阻塞结束时，主库返回到会话，然后该会话可以继续执行其他语句。此时，事务已在主库提交，并且至少有一个从库确认其事件的接收。在继续之前，主库必须收到的确认从库的数量可使用
------------>rpl_semi_sync_master_wait_for_slave_count系统变量进行配置，默认值为1
            7、不只是事务提交时，事务回滚时主库也会发生同样的阻塞。MySQL同时支持多个数据库引擎，当一个事务中既包含事务表又包含非事务表时，回滚即使对事务表没有影响，二进制日志中也会记录非事务表的事件，因为对非事务表的修改无法回滚并且必须发送到从库
            8、在没以start transaction 或 set autocommit=0开启事务时，每个语句都自动隐式提交。在使用半同步复制时，主库上的这类语句就像显示事务提交一样。

2)异步复制：主库提交事务时，将事件写入他的二进制日志，而从库在准备就绪时请求他们。主库无需等待从库ack恢复，直接提交事务并返回客户端。异步复制不确保所有事件都能到达从库，无法保证数据完整性
3)全同步复制，当主库事务提交时，所有从库也将在主库返回执行事务的回话之前提交事务。这样做的缺点就是完成事务可能会有很大延迟
4)半同步复制，介于异步和完全同步之间，主库仅等待至少一个从库接收并记录事件。他不会等待所有从库确认收到，并且从库只需要确认接收，而不是事件已经在从库完全执行和提交。

------>半同步复制----->缺陷
------>旧版本半同步复制受限于binlog dump线程，因为该线程承担了两份不同且又十分频繁的任务：传送二进制日志事件给从库；接收从库的ack反馈信息，这两个任务串行，binlog dump线程必须等待从库返回之后才会传送下一个事件，binlog dump线程是瓶颈，在高并发业务场景下，这样的机制会影响数据库整体的TPS。
------>解决
------>在5.7.4版本的半同步复制框架中，独立出一个Ack Receiver线程，专门用于接收从库返回的ACK请求，这将之前binlog dump线程的发送和接收工作分为两个线程来处理。这样主库上有两个线程独立工作，可以同时发送二进制日志事件到从库，和接收从库的ACK信息，因此半同步复制的带了极大地性能提升
------>特点 Ack Receiver线程在主库启用半同步复制时创建，并在主库禁用半同步复制时销毁。他是自动创建和销毁，因此不受用户控制。他的状态信息可以在performance_schema中查询得到
        Ack Receiver线程有以下三个状态:
            1、在MySQL5.7.17之前，这个Ack Receiver线程采用了select机制来监听从库返回的结果，然而select机制监控的文件句柄只能是0-1024，当超过1024时，用户在MySQL的错误日志中或许会收到类似如下的报错，更有甚者会导致MySQL发生宕机
            报错如下：semi-sync master failed on net_flush() before waiting for slave reply.
            MySQL5.7.17版本开始，官方修复了这个bug,开始使用poll机制来替换原来的select机制，从而可以避免上面的问题。其实poll调用本质上和select没有区别，只是在I/O句柄数理论上没有上限了，原因是它是基于链表来存储的



4)总结，三者相比,于异步复制相比，半同步复制提供了高进的数据完整性，因为当提交成功返回时，已知数据至少存在于两个位置。但半同步复制确实会对性能产生一些影响，因为需要等待从库，提交速度会变慢，延迟至少是将提交发送到从库并等待从库确认收到的tcp/ip往返时间。这意味着半同步复制最好在低延时的网络中使用

3-12二进制日志的互斥锁改进
旧版本半同步复制在主库提交二进制日志的写会话和binlog dump线程读取二进制日志的操作都会对二进制日志添加binlog lock互斥锁,用于保护二进制日志的读写操作。使用互斥锁，二进制日志读写操作都是安全的，但会导致二进制日志文件的读写串行化。
--------->不仅binlog dump线程和用户会话不能同时读写二进制日志，就连多个binlog dump线程本身也无法同时读取，每当一个会话正在读取或写入二进制日志文件时，所有其他绘画都必须等待，如此顺序读写可能是一个瓶颈，尤其是当读写操作很慢时。
--------->改进
--------->从binlog dump线程移除 bin log 锁，加入了安全边界保证二进制日志的读安全
--------->二进制日志文件看起来好像一个仅追加的日志文件，可以安全地读取没有锁定的二进制事件。因此从binlog dump县城中删除了binlog 锁。不适用binlog锁，而是为活动binlog维护安全读取边界(最大位置)，binlog dump线程永远不会读取超过安全读取边界的位置，当边界到达边界时，他将等待边界更新。用户会话负责在追加了二进制事件后更新安全读取边界。



----------2)一主多从，分摊从库压力
----------3)强制走主库方案(强一致性) 
3)场景一，如果对数据实时性要求不是很高,比如：大V有千万粉丝,发布一条微博,粉丝晚几分钟收到这条信息,并不会有特别大的影响。这时，可以走从库。
  场景二,如果对数据的实时性要求非常高,比如金融类业务,我们可以在客户端代码标记下,让查询强制走主库
  4)并发量不高的场景下，给从库缓冲时间，业务查询可以先执行sleep(1)，并发量大，性能下降严重,不推荐该方案

  5)5-1在从库执行命令  show slave status 查看 seconds_behind_master的值,单位秒,如果为0,表示主备库之间无延迟
    5-2执行show slave status  响应结果里有几个关键参数
    Master_Log_File 读到主库最新文件
    Read_Master_Log_Pos 读到的主库最新文件的坐标位置
    Relay_Master_Log_File 从库执行到的最新文件
    Exec_Master_Log_Pos 从库执行到的最新文件的坐标位置
    两两比较  上面的参数是否相等
    
    5-3 方案 
    Auto_Position 主从之间使用GTID协议
    Retrieved_Gtid_Set 从库收到的所有的binlog日志的GTID集合
    Executed_Gtid_Set 从库已经执行完成的GTID集合
    比较Retrieved_Gtid_Set 和 Executed_Gtid_Set 的值是否相等
    在执行业务SQL操作时,先判断从库是否已经同步最新数据,从而决定是操作主库，还是操作从库

    缺点:无论采用上面哪一种方案,如果主库的写操作频繁不断,那么从库的值永远跟不上主库的值,那么读流量永远是打在了主库上

    所以，不加判断，直接从主库读，解决性能，加上hint标识，刚更新的数据，直接查主库(强制读主库)

    
    从库节点判断主库位点:
    执行 select master_pos_wait(file,pos,timeout)
    返回一个正整数M，表示从库从参数节点开始执行了多少个事务
    
    file和pos表示主库上的文件名和位置
    timeout可选,表示这个函数最多等待N秒


    master_pos_wait 返回结果无法与具体操作的数据行做关联,所以每次接收读请求时,从库还是无法确认是否已经同步数据,方案实用性不高
    
    比较GTIDs


    执行下面的查询命令 阻塞等待,直到从库执行的事务包含gitd_set,返回0
    超时,返回 1
    select wait_for_executed_gtid_set(gtid_set,1)
    MySQL5.7.6版本开始,允许在执行更新类事务后,把这个事务的GTID返回给客户端,具体操作,将参数session_track_gtids 设置为OWN_GTID，调用API接口mysql_session_track_get_first返回结果解析出GTID

    处理流程:
    发起写SQL操作,在主库成功执行后,返回这个事务的GTID
    发起读SQL操作,现在从库执行select_wait_for_executed_gtid_set(gtid,1)
    如果返回0,表示从库已经同步了数据,可以在从库执行查询操作
    否则，在主库执行查询操作

    缺点:
    跟上面的master_pos_wait类似,如果写操作和读操作没有上下文关联,那么GTID无法传递,方案实用性不高

    
    引入缓存中间件
    高并发系统,缓存作为性能优化利器,应用广发,我们可以考虑引入缓存作为缓冲介质
    

    缺点：
    k-v存储,使用一些简单的查询条件场景,如果复杂的查询，还是要查询从库




    数据分片
    分库分表,每次读写都是操作主库的一个分表,从库只用来做数据备份,当主库发生故障时,主从切换,保证集群高可用


    6)从库的并行复制能力，解决从库复制延迟问题
------->TPS较高场景下,会出现严重主从延迟问题,MySQL为了解决这个问题,将SQLthread演化了多个worker的形式,在slave段并行应用relay log 中的事务,从而提高relay log的应用速度,减少复制延迟
------->复制线程是由参数slave_parallel_workers来控制的,通常情况下,在8G、8核CPU的机器上,将该值设置为8比较合适,如果你的CPU核数比较高,可以适当调整为8-16之间的数字

show variables like 'slave_parallel_workers'

Variable_name Value
slave_parallel_workers 8
1 row in set, 1 warning(0.00sec)

并行复制  背景
    在MySQL5.6版本 MySQL并行执行relay log 粒度为数据库级别,不同数据库SQL修改不同行内容,不会产生锁争用,但业务数据都集中在一个热点表,存在锁争用,并行复制会退化为单线程复制

------>对于从库SQL thread 处理relay log 日志的时候，在MariaDB中对并行复制做了一定改进
------>1、主库上能够并行提交的事务,也就是已经进入到了redo log commit阶段的事务,在从库上也一定能够并行提交,所以主库上并行提交的事务,它用一个commit_id对这组事务来进行标识,下一组并行事务的commit_id为本组的commit_id+1
------>2、将所有的事务的commit_id写入binlog中
------>3、在从库上应用binlog的时候,将所有的binlog按照commit_id进行划分到不同的worker上
------>4、本组commit_id的事务全部在从库上提交完成后,再去拿下一批事务

------>优点：这种方法大大增加了从库应用relay log的速度 但是问题是从库在应用前一组事务的时候，后一组事务是处于等待中的，即使前一组的worker有些已经空闲。而在主库上，可能无时无刻不在写入，这样系统的吞吐量上主从节点就不匹配,主库的吞吐量严重高于从库


------>改进
------>MySQL5.7的并行复制在MariaDB基础上做了改进,事务进入到redo log prepare阶段的时候，由于WAL技术,说明此时事务已经过了锁冲突检测阶段,MySQL5.7的并行复制时将所有在主库上处于redo log prepare阶段的事务,和该阶段之后的事务,也就是处于redo log commit 阶段的事务，在从库并行执行,从而减少worker线程不必要的等待


这里，有必要再说两个参数,
binlog_group_commit_sync_delay参数,表示redo log prepare阶段完成之后，延迟多少微妙后才调用fsync(个人理解，这个时间不能设置太长，太长，就失去了redo log 本身存在的意义)
(因为重做日志，要即使)

binlog_group_commit_sync_no_delay_count参数,表示积累多少次redo log prepare: write的操作以后才调用fsync

这两个参数是用于故意拉长binlog 从write到fsync的时间,以此减少binlog的写盘次数。在MySQL5.7的并行复制策略里，它们可以用来制造更多的“同时处于prepare阶段的事务”,这样就增加了备库复制的并行度

它们既可以“故意”让主库提交的慢些，又可以让备库执行得快些。在MySQL5.7处理备库延迟的时候，可以考虑调整这两个参数值，来达到提升备库复制并发度的目的。









MySQL架构：连接器(管理权限 包括权限认证)---->连接池---->管理与服务----->SQL接口---->解析器(词法分析,语法分析)---->优化器(执行计划,选择索引)---->缓存(命中，则直接返回结果)---->存储引擎(存储数据,提供读写接口)----->文件系统

------>server层,涵盖大部分核心业务,并且所有存储引擎的功能都在这一层实现,包括存储过程,触发器,视图等
------>存储引擎:存储引擎有很多,包括myisam，innodb，memory等,最常用的是innodb,也是现在MySQL的默认存储引擎

------>面试----->语法错误，关注的第一个你所需要关注的紧接着use near的内容,它会报出哪个语法附近有错误
------>mysql区别于其他数据库的最重要特点就是其插件式的表存储引擎
------>存储引擎是底层物理结构和实际文件读写的实现，每个存储引擎开发者可以按照自己的意愿来进行开发

------>存储引擎是基于表的，而不是数据库
------>innodb性能出色且具有自动崩溃恢复特性,使得它在非事务型存储的需求中也很流行,除非有非常特别的愿意需要使用其他的存储引擎，否则应优先考虑innodb引擎。


------>myisam有大量特性，包括全文索引,压缩,空间函数(GIS)等,单myisam不支持事务和行级锁,而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复
      myisam针对表加锁，而不是针对行，读取时会对需要读取到的所有表加共享锁,写入时则对表加排它锁,myisam很容易因为表锁的问题导致典型的性能问题
------>其他引擎,Mrg_Myisam,Archive,Blackhole,CSV,Federated  第三方引擎XtraDB,TokuDB

----->memory 不在乎数据丢失，磁盘文件名和表名相同，只存储表结构，而数据存在内存内,速度比myisam快一个量级
    
    


redo log 重做日志
----->解释:循环写入磁盘，顺序写入磁盘,innodb以页为单位,来管理存储空间，任何curd最终会操作完整的一个页,会将整个页加载到buffer pool中，然后对需要修改的记录进行修改，修改完不会立即刷新磁盘,仅仅修改一条记录,刷新一个完整的数据页的话，过于浪费了。但是如果不立即刷新的话,数据此时还在内存中,如果系统发生崩溃，最终数据会丢失的，因此权衡利弊，引入redo log，也就是说，修改完后，不立即刷新,而是记录一条日志,日志内容就是记录哪个页面,多少偏移量，什么数据发生了什么变更，这样即使系统崩溃，再回复后，也可以根据redo日志，进行数据恢复。


fsync (group commit 组提交解决)
WAL技术
并行 串行  并发
GTID协议
tcp握手
链表  红黑树 B+树 B树 poll模型
事务显示提交  隐式提交
锁等待
联合索引关于最左前缀原则改变索引的顺序
MariaDB

在这里插入图片描述

6、为什么写的多了会导致主从延时？

答：

写操作多了，TPS并发量大，主库可以并发执行，可是slave从库在执行SQL线程的时候，是串行执行，容易导致同步阻塞，若是再有大事务，执行时间长达几分钟，十几分钟，导致后面DDL文件阻塞执行，这样，就造成了主从延时

7、4000万数据的分表策略？

7-1垂直分表，例如商品表，把热门字段价格、小logo存一张表，商品简介、大图，单独存放另一张表，冷热数据分开存储

优点:1、为了避免IO争抢并减少锁表的几率，查看详情的用户于商品信息浏览互不影响

2、充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率拖累

7-1 为什么大字段IO效率低?

答：

1、数据本身大，需要更长的读取时间

2、跨页，页是数据存储单位，很多查找及定位操作都是以页为单位，单页内的数据行越来越多，数据库整体性能越来越好，而大字段、富文本，占用空间大，单页内存储行数少，因此IO效率低

3、数据库以行为单位将数据加载到内存中，这样表字段长度较短且访问频率较高，内存能加载更多数据，命中率高，减少了磁盘IO，从而提升了数据库性能

7-2 垂直分库

1)原因/背景：通过垂直分表性能得到一定程度提升，但是还没有达到要求，而且磁盘空间也快不够了，因为数据还是始终限制在一台服务器，库内垂直分表解决了单一表数据量过大的问题，但是没有将表分不到不同的服务器上，因此每个表还是竞争同一个物理机的CPU，内存，网络IO和磁盘

2）如何分？

经过思考，把原有卖家表，分为了商品库和店铺库，并把这两个库分散到不同的服务器，如下图：

由于商品信息于商品描述业务耦合度较高，因此一起被存放在商品库，而店铺信息相对独立，因此单独被放到店铺库-------------->垂直分库

优点：1、解决业务层面的耦合，业务清晰

2、能对不同业务的数据进行分级管理、维护、监控和扩展等

3、高并发场景下，垂直分库一定程度上提升IO，数据库连接和降低单机硬件资源的瓶颈

水平分库？

经过垂直分库后，数据库性能得到了一定程度解决，但随着业务的增长，商品库单库存储数据已经超出预估，单台服务器已经无法支撑

------->水平分库就是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

水平分库：1、解决了单裤大数据，高并发的性能瓶颈

2、提高了系统的稳定性和可用性

水平分库往往能解决单裤存储量及性能瓶颈。但是由于同一个表被分配在不同数据库，需要额外进行数据操作的路由操作，因此大大增加了系统的复杂度

水平分表

按照水平分库的思路对商品库存内的表也进行水平拆分，其目的是为了解决单表数据量大的问题，如下图

水平分表就是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中（对数据的拆分，不影响表结构）

优点：1、优化单一表数据量过大而产生的性能问题

2、避免IO争抢而减少锁表几率

分表总结：

1、垂直分表：访问频率，大字段原则拆表，业务清晰，提高性能，避免联查，否则得不偿失

2、垂直分库：多个表按照业务耦合度分类，放在不同数据库，这些库分布在不同服务器，使访问压力分摊在多个服务器，大大提高性能，同时提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案，还需要解决跨库带来的复杂度

3、水平分库：拆表，放在不同数据库，使访问压力被多个服务器负载，不仅需要解决跨库问题，还需要解决数据路由问题

4、水平分表：一张表拆分，放到同一个数据库，每个表数据只有这个表部分数据，能小幅度提升性能，仅仅作为水平分库的一个补充优化

8、MySQL存储一些不太重要的字段如何设计存储结构（字段数量在变）

两难的境界，不定字段数目的数据库表设计和数据结构

1、动态增加数据库表字段

2、预留足够的空白字段，运行时做动态映射

3、用xml格式保存在单字段里

4、改列为行，用另外一个表存放定制字段

插播一条，B+树和B树详解

https://juejin.cn/post/7116381265300815903

SQL查询慢请看这

https://juejin.cn/post/7117804246069673992

4000万数据的分表策略？

答：比如user表，先按照用户id分，0~2kw存到第一块区域，2kw+1~4kw存到第二块区域，

而第一块区域和第二块区域，再分别按照取模的方式，分布地存在2张表上，这样既可以让服务器均衡地分摊压力，可以满足横向扩展的需求

存储一些不太重要的字段如何设计存储结构（字段数量在变）?

预留空白字段，冷热字段分表存储

如果需要添加新字段，做一个字段映射，如果空白字段预留不够，可以使用osc进行在线不缩表的情况下，添加新字段或者索引

MySQL大表如何加字段(OnlineDDL) - 掘金

mysql --help | grep ‘Default options’ -A 1 查看my.cnf配置文件的读取顺序

9、MySQL中JSON字段如何使用，用MySQL哪个版本？

答：

MySQL5.7

JSON字段，有json数组和json对象

json字段没有默认值不能设置长度

MySQL JSON类型字段的简单使用 - 掘金

比如JSON_OBJECT一般在存储日志的时候使用

JSON_ARRAY一般在存储商品属性的时候使用，数组内含有多个对象