面试题总结-1-mysql

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

16736e9b2ec5525795bc392107dea755.png

 在这里插入图片描述

 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一般在存储商品属性的时候使用,数组内含有多个对象

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

打赏作者

柔情柴少

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

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

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

打赏作者

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

抵扣说明:

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

余额充值