mysql总结归纳

Mysql
CHAR和VARCHAR的区别？

CHAR和VARCHAR类型在存储和检索方面有所不同

CHAR列长度固定为创建表时声明的长度，长度值范围是1到255

当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格

MySQL 里记录货币用什么字段类型好

NUMERIC和DECIMAL类型被Mysql实现为同样的类型，这在SQL92标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。

当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。

例如：

salary DECIMAL(9,2)

在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而 2(scale)代表将被用于存储小数点后的位数。

因此，在这种情况下，能被存储在salary列中的值的范围是从-9999999.99到9999999.99。

什么是存储过程？有哪些优缺点？

存储过程，就是一些编译好了的 SQL 语句，这些 SQL 语句代码像一个方法一

样实现一些功能（对单表或多表的增删改查），然后给这些代码块取一个名字，

在用到这个功能的时候调用即可。

优点：

1.存储过程是一个预编译的代码块，执行效率比较高

2.存储过程在服务器端运行，减少客户端的压力

3.允许模块化程序设计，只需要创建一次过程，以后在程序中就可以调用该过程任意

次，类似方法的复用

4.一个存储过程替代大量 T_SQL 语句 ，可以降低网络通信量，提高通信速率

5.可以一定程度上确保数据安全

缺点：

1.调试麻烦

2.可移植性不灵活

3.重新编译问题

一条sql的执行顺序：

mysql执行sql的顺序从 From 开始，以下是执行的顺序流程

1、FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1

2、JOIN table2 所以先是确定表，再确定关联条件

3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2

4、WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3

5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4

6、HAVING 对分组后的记录进行聚合 产生中间表Temp5

7、SELECT 对中间表Temp5进行列筛选，产生中间表 Temp6

8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7

9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8

10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9

MySQL数据库常见的两个瓶颈是：CPU和I/O的瓶颈。

CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。

磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上。

我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能。

MySQL优化方案

Mysql的优化，大体可以分为三部分：索引的优化，sql语句的优化，表的优化

索引优化

1. 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

2. 尽量使用短索引，如果可以，应该制定一个前缀长度

3. 对于经常在where子句使用的列，最好设置索引，这样会加快查找速度

4. 对于有多个列where或者order by子句的，应该建立复合索引

5. 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引

6. 尽量不要在列上进行运算（函数操作和表达式操作）

7. 尽量不要使用not in和<>操作

SQL慢查询的优化

优化原则

 查询时，能不要就不用，尽量写全字段名

 大部分情况连接效率远大于子查询

 多使用explain和profile分析查询语句

 查看慢查询日志，找出执行时间长的sql语句优化

 多表连接时，尽量小表驱动大表，即小表 join 大表

 在千万级分页时使用limit

 对于经常使用的查询，可以开启缓存

数据库表优化

 表的字段尽可能用NOT NULL

 字段长度固定的表查询会更快

 把数据库的大表按时间或一些标志分成小表

 将表拆分

数据库表设计

（时间字段选取，int，bigint和integer的类型的选择以及字段长度的设置，表，索引，存储过程的命名规范，索引的选择，对null空值的处理可以存储默认值

或空字符串，因为null不走索引）

分区表的构建，为什么要构建分区表

☆☆☆mysql事务（ACID，原子性，一致性，隔离性，持久性）

mysql事务隔离级别以及各种隔离级别导致的问题（读未提交，读已提交，可重复读，可串行化读）

读未提交导致脏读，不可重复读，幻读

读已提交导致不可重复读，幻读，可以避免脏读

可重复读，可以避免脏读和不可重复读，但幻读仍有可能发生

可串行化读，在并发情况下，可串行化的读取的结果是一致的，没有什么不同，比如不会发生脏读和幻读；该级别可以防止脏读、不可重复读以及幻读；

mysql默认级别为可重复读，在可重复读隔离级别下，通过多版本并发控制（MVCC）+间隙锁（Next-key Locking）防止幻读

不可重复读的重点是修改，幻读的重点在于新增或者删除，强调范围

数据库并发会带来脏读、幻读、丢弃更改、不可重复读这四个常见问题
mysql大事务：

大事务标准为单次DML事务执行修改数据量超过60000行或一次load data源文件大小超过128M

mysql锁，表锁 ，行锁，间隙锁

说一下数据库表锁和行锁吧

表锁: 不会出现死锁，发生锁冲突几率高，并发低。

MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：表共享读锁和表独占写锁。

读锁会阻塞写，写锁会阻塞读和写

对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

行锁: 会出现死锁，发生锁冲突几率低，并发高。

MySQL的InnoDB引擎支持行锁，与Oracle不同，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，

则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。

行锁的实现需要注意：

行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

两个事务不能锁同一个索引。

insert，delete，update在事务中都会自动默认加上排它锁。

mysql行锁，间隙锁，临间锁：

行锁 也叫记录锁 锁定的是某一行一级

间隙锁 锁定的是记录与记录之间的空隙，间隙锁只阻塞插入操作，解决幻读问题

临键锁 nextkeylock 是行锁与间隙锁的并集，是mysql加锁的基本单位

原则1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。

原则2：查找过程中访问到的对象才会加锁。

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

案例：一张表t id(主键)、c(普通索引)、d 字段 插入数据(0,0,0),(5,5,5),(10,10,10),(15,15,15)

update t set d=1 where id = 7 主键索引上的 (5,10)间隙锁

update t set d=1 where id = 5 主键索引上的 5行锁

update t set d=1 where c = 7 普通索引上的 (5,10)间隙锁

update t set d=1 where c = 5 普通索引上的 (0,5]临键锁 (5,10)间隙锁

update t set d=1 where c <11 普通索引上的 (0,15]临键锁

update t set d=1 where c >=10 普通索引上的 (5,10]临键锁 (10,~]的临键锁

update t set d=1 where c >=10 and c <11 普通索引上的 (5,15]临键锁

update t set d=1 where id >=10 and id <11 主键索引上的 10行锁 (10,15)间隙锁

mysql死锁产生的原因：https://mp.weixin.qq.com/s/K1Rggaw5WvVfItBb9wrpTg

order_no是普通索引

select id from t_order where order_no = ‘10086’ for update;

因为 order_no 不是唯一索引，所以行锁的类型是间隙锁，于是间隙锁的范围是（1006, +∞）。

那么，当事务 B 往间隙锁里插入 id = 1008 的记录就会被锁住。因为当我们执行以下插入语句时，会在插入间隙上再次获取插入意向锁。

Insert into t_order(order_no,create_date)values(1008,now());

插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。

而间隙锁与间隙锁之间是兼容的，所以所以两个事务中 select … for update 语句并不会相互影响。

案例中的事务 A 和事务 B 在执行完后 select … for update 语句后都持有范围为(1006,+∞）的间隙锁，而接下来的插入操作为了获取到插入意向锁，

都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。

为什么间隙锁与间隙锁之间是兼容的？

查询mysql官方文档表明间隙锁在本质上是不区分共享间隙锁或互斥间隙锁的，而且间隙锁是不互斥的，即两个事务可以同时持有包含共同间隙的间隙锁。

这里的共同间隙包括两种场景：其一是两个间隙锁的间隙区间完全一样；其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。

间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。

也就是说间隙锁的应用场景包括并发读取、并发更新、并发删除和并发插入。

插入意向锁是什么？

注意！插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁。

这段话表明尽管插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：

尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁

区间内则是可以的）。另外，我补充一点，插入意向锁的生成时机：每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，

如果已加间隙锁，那 Insert 语句应该被阻塞，并生成一个插入意向锁
如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。

只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

1.设置事务等待锁的超时时间。

当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。

在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

当发生超时后，就出现下面这个提示：

ERROR 1205 (HY000):Lock wait timeout exceeded;try restarting transaction;

2.开启主动死锁检测。

主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

当检测到死锁后，就会出现下面这个提示：

ERROR 12123 (40001):Deadlock found when trying to get lock;try restarting transaction;

上面两种策略是「当有死锁发生时」的避免方式。

我们可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，

利用它的唯一下来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。

MySQL遇到过死锁问题吗，你是如何解决的？

我排查死锁的一般步骤是酱紫的：

1.查看死锁日志 show engine innodb status;

2.找出死锁 Sql

3.分析sql加锁情况

4.模拟死锁案发

5.分析死锁日志

6.分析死锁结果

mysql日志（redolog,undolog,binlog）

MySQL六大日志详解

MySQL 中有六种日志文件，分别是：

一般查询日志general log（记录所有操作）

慢查询日志slow query log（记录所有执行超过long_query_time秒的所有查询）

二进制日志binlog（记录所有更改数据的语句(DML，新增，删除，修改)，还用于复制，恢复数据库，审计）

错误日志errorlog(记录启动，运行，停止mysql时出现的信息)

重做日志redo log

回滚日志undo log

中继日志relay log

默认关闭了通用日志和二进制日志，可以在my.ini文件中进行配置开启。

其中

重做日志和回滚日志与事务操作息息相关，

二进制日志也与中继日志通常用于主从复制

错误日志是记录数据库故常

慢查询日志经常用于数据库性能调优

一、主从复制的过程

1.在每个事务更新数据完成之前，master 在二进制日志记录这些改变。写入二进制日志完成后，master 通知存储引擎提交事务。

2.Slave 将 master 的 binary log 复制到其中继日志。首先 slave 开始一个工作线程（I/O），I/O 线程在 master 上打开一个普通的连接，

然后开始 binlog dump process。binlog dump process 从 master 的二进制日志中读取事件，如果已经跟上 master，它会睡眠并等待 master

产生新的事件，I/O 线程将这些事件写入中继日志。

3.Sql slave thread（sql从线程）处理该过程的最后一步，sql线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与master中的数据一致，

只要该线程与 I/O 线程保持一致，中继日志通常会位于 os 缓存中，所以中继日志的开销很小。

二、重做日志（redo log）

1.作用：

确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

2.内容：

物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

3.什么时候产生：

事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

4.什么时候释放：

当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

三、回滚日志（undo log）

1.作用：

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

2.内容：

逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log 的。

3.什么时候产生：

事务开始之前，将当前事务的版本生成undo log，undo也会产生redo来保证undo log的可靠性

4.什么时候释放：

当事务提交之后，undo log并不能立马被删除，

而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

四、慢查询日志

1.定义：

MySQL 默认设置 10s 没有返回结果的，属于慢查询，并存到日志中 (在 my.ini 可以指定慢查询日志目录).

2.开启慢查询

slow_query_log 慢查询开启状态。

slow_query_log_file 慢查询日志存放的位置（这个目录需要 MySQL 的运行帐号的可写权限，一般设置为 MySQL 的数据存放目录）。

long_query_time 查询超过多少秒才记录。

以上三个参数可以在数据库的配置文件中设定开启，也可以在在 mysql命令行通过set命令开启。当在配置文件中开启慢查询日志记录之后，就会在指定的存放

目录生成日志文件。

3.分析慢查询—explain

当我们获得慢查询的日志之后，查看日志，观察那些语句执行是慢查询，在该语句之前加上explain再次执行，explain 会在查询上设置一个标志，当执行查询时，

这个标志会使其返回关于在执行计划中每一步的信息，而不是执行该语句。它会返回一行或多行信息，显示出执行该计划中的每一部分和执行次序
binlog日志相关知识点

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。

binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

binlog使用场景

在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复。

主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。

数据恢复：通过使用mysqlbinlog工具来恢复数据。

对于InnoDB存储引擎而言，只有在事务提交时才会记录binlog，下面以一条Update语句来介绍 binlog 是如何记录的。

update T set c=c+1 where ID=2;

经历如下几个步骤：

1.取得 ID=2 这一行（通过内存或磁盘读取）

2.这行的 c 值加1

3.更新到内存

4.写入redo log（处于prepare 阶段）

5.写 binlog

6.提交事务（处于commit 状态）

在进行事务的过程中，首先会把binlog写入到binlog cache中（因为写入到cache中会比较快，一个事务通常会有多个操作，避免每个操作都直接写磁盘导致性能降低）。

事务最终提交的时候再把binlog写入到磁盘中。当然事务在最终commit的时候binlog是否马上写入到磁盘中是由参数sync_binlog 配置来决定的。

1、sync_binlog=0 的时候，表示每次提交事务binlog不会马上写入到磁盘，而是先写到page cache,相对于磁盘写入来说写page cache要快得多,不过在

Mysql崩溃的时候会有丢失日志的风险。

2、sync_binlog=1 的时候，表示每次提交事务都会执行fsync写入到磁盘 ；

3、sync_binlog的值大于1的时候，表示每次提交事务都 先写到page cach，只有等到积累了N个事务之后才fsync写入到磁盘，同样在此设置下Mysql崩溃的

时候会有丢失N个事务日志的风险。

很显然三种模式下，sync_binlog=1是强一致的选择，也是5.7.7之后版本的默认值，选择0或者N的情况下在极端情况下就会有丢失日志的风险，具体选择什么模

式还是得看系统对于一致性的要求。

binlog日志格式

binlog日志有三种格式，分别为STATMENT、ROW和MIXED。

STATMENT 基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能；

缺点：在某些情况下会导致主从数据不一致，比如函数、存储过程等。

ROW 基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；

缺点：会产生大量的日志

MIXED 基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的语句使用STATEMENT模式保存binlog，

对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

事务是如何通过日志来实现的

1. 因为事务在修改页时，要先记undo，在记undo之前要记undo的redo， 然后

修改数据页，再记数据页修改的redo。Redo（里面包括 undo 的修改） 一定要

比数据页先持久化到磁盘。

2.当事务需要回滚时，因为有undo，可以把数据页回滚到前镜像的 状态，崩溃恢复

时，如果redo log 中事务没有对应的commit记录，那么需要用undo把该事务

的修改回滚到事务开始之前。

3.如果有commit记录，就用redo前滚到该事务完成时并提交掉

什么时候需要建立数据库索引呢？

在最频繁使用的、用以缩小查询范围的、需要排序的字段上建立索引。

索引如何提高查询速度的

将无序的数据变成相对有序的数据（就像查有目的一样）

覆盖索引是什么？

SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据

聚集索引与非聚集索引的区别是什么?

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值， 再使用主键的值通过聚集索引

查找到需要的数据。

聚集索引和非聚集索引的根本区别是：表记录的排列顺序和与索引的排列顺序是否一致。

聚集索引（Innodb）的叶节点就是数据节点，而非聚集索引(MyISAM)的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针
什么是最左前缀原则？什么是最左匹配原则

顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。

最左前缀匹配原则，非常重要的原则，mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，

比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d 是用不到索引的，

如果建立(a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。= 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，

mysql 的查询优化器会帮你优化成索引可以识别的形式

增加B+树的路数可以降低树的高度，那么无限增加树的路数是不是可以有最优的查找效率？

不可以。因为这样会形成一个有序数组，文件系统和数据库的索引都是存在硬盘上的，并且如果数据量大的话，不一定能一次性加载到内存中。

有序数组没法一次性加载进内存，这时候B+树的多路存储威力就体现不出来了。

索引的数据结构B+树，B树和B+树的区别（平衡二叉树的前序遍历，中序遍历，后序遍历）

数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，

而B树只能中序遍历所有节点，效率太低。聚集索引（Innodb）的叶节点就是数据节点，而非聚集索引(MyISAM)的叶节点仍然是索引节点，

只不过其包含一个指向对应数据块的指针。

B+树的特点

所有关键字都出现在叶子结点的链表中(稠密索引)，且链表中的关键字恰好是有序的;

不可能在非叶子结点命中;

非叶子结点相当于是叶子结点的索引(稀疏索引)，叶子结点相当于是存储(关键字)数据的数据层;

为什么MySQL索引使用B+树而不用hash表和B树？

利用Hash需要把数据全部加载到内存中，如果数据量大，是一件很消耗内存的事，而采用B+树，是基于按照节点分段加载，由此减少内存消耗。

和业务场景有关，对于唯一查找（查找一个值），Hash确实更快，但数据库中经常查询多条数据，这时候由于B+树索引有序，并且又有链表相连，

它的查询效率比hash就快很多了。

b+树的非叶子节点不保存数据，所以同样大小的节点， b+树相对于b树能够有更多的分支，使得这棵树更加矮胖，查询时做的IO操作次数也更少。

索引为什么采用B+树，而不用B—树，红黑树？

答案：提升查询速度，首先要减少磁盘IO次数，也就是要降低树的高度。

1.平衡二叉树，红黑树，都属于二叉树。时间复杂度为O(n),当表的数据量千万时，树的深度很深，mysql读取时消耗大量IO，另外，

InnoDB引擎采用页为单位读取，每个节点一页，但是二叉树每个节点存储一个关键词，导致空间浪费

2.B-树，非叶子节点存储数据，占用较多空间，导致每个节点指针少很多，无形增加树的深度

3.B+树数据都存储在叶子节点，非叶子节点值存储键值+指针，索引树更加扁平，三层深度可以支持千万级表存储，同时叶子节点之间通过链表关联，范围查找更快

日常工作中，MySQL如何优化？

1.分页优化。比如电梯直达，limit 10000, 10 先查找起始的主键id，再通过id>#{value}往后取10条

2.尽量使用覆盖索引，索引的叶子节点中已经包含了要查询的字段，减少回表查询

3.SQL优化（索引优化，小表驱动大表，虚拟列，适当增加冗余字段减少连表查询，联合索引，排序优化，慢查询日志explain分析执行计划）

4.设计优化（避免使用NULL,用简单数据类型如int，减少text类型，分库分表）

5.硬件优化（使用SSD减少I/O时间，足够大的网络带宽，尽量大的内存）

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,

这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建

的索引数量是成正比的。 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）然后删除其中无用数据（此过程需要不到两分钟）

删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

☆☆☆你知道哪些数据库结构优化的手段？

范式优化： 比如消除冗余（节省空间）

反范式优化：比如适当加冗余等（减少join）

限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。

读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；

拆分表：分区将数据在物理上分隔开，不同分区的数据可以指定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，

而不必进行全表扫描，明显缩短了查询时间。

另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表

可采取此方法。可按月自动建表分区。
数据量大的情况下怎么优化查询(大数据优化)：

1.对查询条件建立索引

2.使用redis或者本地new HashMap进行缓存数据，对一些不经常修改的数据进行初始化缓存

3.分区，分库，分表，主从复制，读写分离，冷热数据处理，硬件方面就是增加数据库的内存

分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。

通过分表，可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同表上的数据量少了，起到提高查询性能、缩短查询时间的作用，此外，

可以很大的缓解表锁的问题。

分库与分表带来的分布式困境与应对之策 ：

数据迁移与扩容问题----一般做法是通过程序先读出数据，然后按照指定的分表策略再将数据写入到各个分表中。

分页与排序问题----需要在不同的分表中将数据进行排序并返回，并将不同分表返回的结果集进行汇总和再次排序，最后再返回给用户。

一道场景题：假如你所在的公司选择MySQL数据库作数据存储，一天五万条以上的增量，预计运维三年，你有哪些优化手段(数据量大的问题)？

设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。

选择合适的表字段数据类型和存储引擎，适当的添加索引。

MySQL数据库主从复制读写分离。

分表，减少单表中的数据量，提高查询速度。

添加缓存机制，比如Memcached，Apc等。

不经常改动的页面，生成静态页面。

书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。

数据库高并发是我们经常会遇到的，你有什么好的解决方案吗？

在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中， 减少数据库的读取负担。

增加数据库索引，进而提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）

主从读写分离，让主服务器负责写，从服务器负责读。

将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。

使用分布式架构，分散计算压力。

一条sql执行很慢，可能是因为什么? 怎么优化？(慢查询优化)

一个 SQL执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因

(1)、数据库在刷新脏页，例如redo log写满了需要同步到磁盘。

(2)、执行的时候，遇到锁，如表锁、行锁。

2、这条SQL语句一直执行的很慢，则有如下原因。

(1)、没有用上索引：例如该字段没有索引；

(2)、由于对字段进行运算、函数操作导致索引失效，无法用索引。

(3)、数据库选错了索引。

mysql慢查询的12个原因：（程序员田螺）https://mp.weixin.qq.com/s/gz-wQPsaerf4k7ymG8DiDA

1.sql没有加索引

2.sql索引不生效

3.limit深分页问题

4.单表数据量太大

5.join或者子查询过多

6.in元素过多

7.数据库存在脏页

8.order by走文件排序

9、达不到锁

10.delete+ in子查询不走索引

11.group by使用临时表和文件排序

12.系统或网络资源不够

索引失效的几种情况

1.复合索引不按“左前缀”原则，会导致索引失效，例如：index(a,b) where b = 1 and a = 2;

范围后的索引列失效，例如: where a > 2 and b = 1 这就导致b索引列的索引失效

2.like %123 或者%123% 这会导致索引失效；可以改为 like 123% ,或者使用覆盖索引，就是select的字段在索引列中 (因为索引B+树是按照索引值

有序排列的，只能根据前缀进行比较)

3.对索引列进行运算，会导致索引失效，例如：where a+1 = 2; 可以改为 where a=1; 还有where round(a)=1,可以创建函数索引index(round(a))

因为索引B+树保存的索引原始值，而不是计算后的值，故不能直接查询比较。若对处理后的结果建立索引，则可以正常使用该索引。

4.索引类型的隐式转换，会导致索引失效，例如：索引age的类型 为varchar比较值为int，如 where age > 10 不会走索引，因为MySQL在遇到字符串和数字

比较的时候，会自动把字符串转为数字，然后再进行比较。相当于为索引age套了个转换函数where CAST(ageAS signed int) > 10，故索引失效。

反之，比较的值为字符串where age > ‘10’ ，则不会出现索引失效。

5.or的左右列不都是索引列，会导致索引失效，例如：where a=1 or b=2 ,只有当a和b都是索引列时才可以让索引有效（如果a、b是复合索引也会失效）

(所以一般使用in)，or两边为‘>’和‘<’范围查询时，索引也会失效

6.is null 和 is not null 会导致索引失效，因为索引列规定是not null的，所以is null 和 is not null 都没有可比行，会全表扫描

7.in()括号里面的数量超过一定数量的时候也会导致索引失效，主键临界值是7个，唯一索引和普通索引临界值是4个

8.!=、<>、not in会导致索引失效

9.当全表扫描比索引速度快时，索引会失效

10.select * from A where A.id in (select B.id from B);

select * from A where exist(select 1 from B where A.id = B.id)

区别：第一个是先把A和B的所有id查出来，然后外层循环是A的id，内层循环是B的id，找到匹配的存到结果集中；循环次数相当于 A.length * B.length；

适用于B的数据量比较小时

第二个是循环A的id，每次用A.id去B中查询看是否存在，循环次数相当于 A.length；适用于B的数据量比较大时

Mysql2
谈谈 SQL 优化的经验

查询语句无论是使用哪种判断条件等于、小于、大于，WHERE 左侧的条件查询字段不要使用函数或者表达式

使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql来分析这条sql语句，这样方便我们分析，进行优化。

当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1。不要直接使用 SELECT *，而应该使用具体需要查询的表字段，因为使用 EXPLAIN 进行分析时，

SELECT * 使用的是全表扫描，也 就是 type =all 。

为每一张表设置一个 ID 属性。

避免在 MHERE 字句中对字段进行 NULL

判断避免在 WHERE 中使用!或>操作符

使用 BETWEEN AND 替代 IN

为搜索字段创建索引

选择正确的存储引擎，InnoDB、MyISAM、MEMORY 等

使用 LIKE%abc%不会走索引，而使用 LIKE abc%会走索引。

对于枚举类型的字段(即有固定罗列值的字段)，建议使用 ENUM 而不是 VARCHAR，如性别、星期、类型、类别等。

拆分大的 DELETE 或 INSERT 语句，进行批量插入或者批量删除操作，避免一下插入太多数据或者删除太多数据，分批插入，每批插入1000条，事务处理，

插入成功的批次数据不会回滚，失败的批次才会回滚，提升插入大数据的效率

选择合适的字段类型，选择标准是尽可能小、尽可能定长、尽可能使用整数。

字段设计尽可能使用 NOT NULL

进行水平切割或者垂直分割

一条sql执行过长的时间，你如何优化，从哪些方面入手？

1.查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等

2.优化索引结构，看是否可以适当添加索引

3.数量大的表，可以考虑进行分离/分表（如交易流水表）

4.数据库主从分离，读写分离

5.explain分析sql语句，查看执行计划，优化sql

6.查看mysql执行日志，分析是否有其他方面的问题

MySQL是如何执行一条SQL的？具体步骤有哪些？

Server层按顺序执行sql的步骤为：

客户端请求->

连接器（验证用户身份，给予权限） ->

查询缓存（存在缓存则直接返回，不存在则执行后续操作）->

分析器（对SQL进行词法分析和语法分析操作） ->

优化器（主要对执行的sql优化选择最优的执行方案方法） ->

执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）->

去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

简单概括：

连接器：管理连接、权限验证；

查询缓存：命中缓存则直接返回结果；

分析器：对SQL进行词法分析、语法分析；（判断查询的SQL字段是否存在也是在这步）

优化器：执行计划生成、选择索引；

执行器：操作引擎、返回结果；

存储引擎：存储数据、提供读写接口。

你了解MySQL的内部构造吗？请说下你对MySQL架构的了解？

大体来说，MySQL可以分为Server层和存储引擎层两部分，其中：

Server层包括：连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），

所有跨存储引擎的功能都在这一层实现，比如：存储过程、触发器、视图等。

存储引擎层负责：数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，

它从MySQL 5.5.5版本开始成为了默认的存储引擎。

mysql多版本并发控制MVCC

MVCC是什么

•MVCC(multi-version-concurrent-control)

•MVCC即多版本并并发控制，MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

•MVCC在MySQL InnoDB中的实现主要是为了提高数据库的并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

当前读和快照读

当前读(有锁查询)

像select lock in share mode（共享锁），select for update；update，insert，delete（排他锁）；这些操作都是一种当前读，为什么叫当前读？

因为它读取的记录都是目前数据库中最新的版本，读取时还要保证其它并发事务不能修改当前记录，所以会对读取数据加锁

快照读(无锁查询)

像不加锁的select操作就是快照读，即不加锁的非阻塞读，快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。之所以出现快照读的情况，

是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制(MVCC)。所以我们可以认为MVCC是行锁的一个变种，但MVCC在很多情况下它避免了加锁，

降低了开销，既然是基于多版本的，所以快照读不一定读到的就是最新版本的记录，而是可能为之前的历史版本

当前读、快照读和MVCC关系

•MVCC并发控制：维护一条数据的多个版本，使得读写没有冲突

•当前读：悲观锁的一种实现

•快照读：MVCC理想模型读数据的一种实现

数据库并发场景

读-读：不存在线程安全问题

读-写：存在线程安全问题，脏读、幻读、不可重复读

写-写：存在线程安全问题，可能会丢失数据

为什么要MVCC

•MVCC解决读-写，读操作不阻塞写操作，写操作不阻塞读操作

•悲观锁/乐观锁解决写-写

MVCC实现

隐匿字段：DB_TRXID, DB_ROLL, DB_ROW_ID, DELETED_BIT

DB_ROW_ID：隐匿的自增

IDDB_TRXID：指针，最近修改这条数据的事物

IDDB_ROLL：回滚指针，指向记录的上一个版本

DELETED_BIT：删除表示flag，相当于逻辑删除

UNDO日志

•insert undo log：插入一条数据时，将数据信息记录(主键)，回滚时删除

•update undo log：修改一条数据时，将数据修改前的值记录下来

•delete undo log：记录该条数据的完整数据，DELETED_BIT之为true。Purge线程清除DELETED_BIT为true的数据

新增一条记录，假设事务ID和回滚指针为null

事务1：name修改为Tom1.数据库加排他锁

2.将该行数据拷贝到undolog中

3.修改该行name为Tom，修改事务ID为1，回滚指针指向undolog的副本记录

4.提交事务

将age修改为30

Read View读视图

•什么是Read View？说白了Read View 就是事务进行快照读操作的时候生产的读视图，在当前事务执行快照读的那一刻，会生成数据库系统当前的一个快照，

记录并维护系统当前活跃事务的ID（当每个事务开启时，都会被分配一个ID，这个ID是默认递增的，所以事务越新，ID越大）。

•当我们某个事物执行快照读的时候，对读取的该记录创建一个Read View 视图，把它当作条件，用来判断当前事务能够看到哪个版本的数据，

既可能是当前最新的数据（也就是该快照），也可能是该行记录的undo log 日志里的某个版本的数据

可见性算法

将被修改的数据的最新记录中的DB_TRX_ID（当前事务ID）取出来，与系统当前其它活跃事务的ID去对比（由Read View 维护）

•若符合可见性，取出该条记录

•若不符合可见性，那就通过DB_ROLL_PRT 回滚指针去取出undo log 中的DB_TRX_ID 再比较，也就是说遍历undo log 链表DB_TRX_ID 找

到特定条件的事务ID的版本，那么这个DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本

Read view三个关键属性

•trx_list：未提交事务ID列表，用来维护Read View生成时刻系统正处于活跃状态的事务ID。

•up_limit_id：记录trx_list事务ID列表中最小的ID，也就是最初修改该记录的事务

•low_limit_id：Read View生成时刻系统尚未分配的下一个事务ID，也就是等于目前出现过的最大事务ID + 1。

可见性判断规则

•首先判断DB_TRX_ID < up_limit_id ：

大于：进入下一个判断。

小于：则当前事务能看到DB_TRX_ID 所在记录。

•判断DB_TRX_ID >= low_limit_id：

大于：代表DB_TRX_ID 所在的记录是在Read View 生成之后才出现的，那对当前事务肯定不可见。

小于：进入下一个判断。

•判断DB_TRX_ID 是否在活跃事务中trx_list.contains(DB_TRX_ID)：

在：代表Read View 生成的时候，你这个事务还在活跃状态，并没有commit，你修改的数据，我当前的事务是看不见的（RR隔离级别）。

不在：说明你这个事务在Read View 生成之前就已经commit 了，你修改的结果，我当前事务是看得见的

RC,RR级别下的InnoDB快照读

结论：

在RR下，事务中快照读的结果非常依赖事务首次出现快照读的地方，即某个事务中首次出现快照读的地方十分的关键，它可以决定该事务后续快照读结果的能力。

在RC下，事务中每次快照都都会生成一个新的Read View 和最新快照。这也是为什么能够读到已提交事务的原因

RR隔离级别下间隙锁才有效，RC隔离级别下没有间隙锁；

RR隔离级别下为了解决“幻读”问题，“快照读”依靠MVCC控制，“当前读”通过间隙锁控制

间隙锁和行锁合称next-key lock，每个next-key lock 是前开后闭区间

间隙锁的引入，可能会导致同样语句锁住更大的范围，影响比并发

mysql解决幻读的方式：https://mp.weixin.qq.com/s/Pm6PQEYwStww6htOQAlDIg

innoDB的repeatable read可重复读这种隔离级别通过间隙锁+MVCC解决了大部分幻读问题，只有一种特殊的幻读情况无法解决

想要解决幻读，可以使用serializable这种隔离级别，或者使用RR也能解决大部分的幻读问题。

在RR级别下，为了避免幻读的发生，要么就是使用快照读，要么就是在事务一开始就加锁

数据库架构

1、数据库的架构原则

（1）高可用

（2）高性能

（3）可拓展

（4）一致性

2、数据库常用的架构

（1）一主多从：当我们客户端发起读写请求的时候，我们会从mysql服务进行读写数据。假设我们目前有三台mysql服务，其中一台作为master服务，另外两台

作为salve。master拥有读写的权限，主要承担了写的工作，salve只有读的权限，主要承担了读的操作。当客户端发起请求时，它会将请求分流，实现读写分离。

优点：读写分离，分担了单台数据库或者单台服务器的压力。

缺点：主从延迟，可能会导致数据的不一致性。

（2）双机热备：双机备热，其实刚好就是处理了一主多从的主从延迟问题，避免了主从不一致的情况。并且双击热备是读和写都在一台服务上进行操作。

优点：①数据马上就写入，没有主从延时。②当master挂掉的时候，它会从vip（虚拟ip）就会指向slave上，然后slave就会变成称为master，

所有的写入操作就会到第二台服务器上，确定系统的高可用性。

缺点：当master宕机的时候，slave会变成主，那么期间哪怕master服务恢复正常，原先的master会变成从，slave变成主，如果想要恢复的话，

就需要从Keepalived进行调整。一主多从和双机热备的区别：双机热备弥补了一主多从，主从延时的缺点；而双机热备它在服务器上的压力并没有被分担，

他主要是靠硬件往上扛。

主从复制原理和机制

什么是主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是准实时的业务数据库。一般在数据量较大的情况下，为了实现负载均衡，

避免增删改查压力过大，将数据的增删和查分开。

mysql主从同步延迟问题，数据一致性问题如何解决？

在实际的生产环境中，由单台MySQL作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面

因此，一般来说都是通过集群主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力进行部署与实施。

主从复制的作用：

1.提高数据库负载能力，主库执行读写任务（增删改），备库仅做查询。

2.提高系统读写性能、可扩展性和高可用性。

3.数据备份与容灾，备库在异地，主库不存在了，备库可以立即接管，无须恢复时间。

4.读写分离，使数据库能支持更大的并发。在报表中尤其重要。由于部分报表的sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，

那么报表sql将不会造成前台锁，保证了前台速度。

（数据库为什么要读写分离：消除读写锁冲突从而提高数据的写性能。主从只负责各自的写和读，极大程度的缓解排他锁(x锁)和共享锁(s锁)的争用）

主从复制的优点：

1.实现服务器负载均衡

通过服务器复制功能，可以在主服务器和从服务器之间实现负载均衡。即可以通过在主服务器和从服务器之间切分处理客户查询的负荷，从而得到更好地客户相应时间。

通常情况下，数据库管理员会有两种思路。 一是在主服务器上只实现数据的更新操作。包括数据记录的更新、删除、新建等等作业。而不关心数据的查询作业。

数据库管理员将数据的查询请求全部 转发到从服务器中。这在某些应用中会比较有用。如某些应用，像基金净值预测的网站。其数据的更新都是有管理员更新的，

即更新的用户比较少。而查询的用户数 量会非常的多。此时就可以设置一台主服务器，专门用来数据的更新。同时设置多台从服务器，用来负责用户信息的查询。

将数据更新与查询分别放在不同的服务器 上进行，即可以提高数据的安全性，同时也缩短应用程序的响应时间、提高系统的性能。

二是在主服务器上与从服务器切分查询的作业。在这种思路下，主服务器不单单要完成数据的更新、删除、插入等作业，同时也需要负担一部分查询作 业。

而从服务器的话，只负责数据的查询。当主服务器比较忙时，部分查询请求会自动发送到从服务器重，以降低主服务器的工作负荷。当然，

像修改数据、插入数 据、删除数据等语句仍然会发送到主服务器中，以便主服务器和从服务器数据的同步。

2.通过复制实现数据的异地备份

可以定期的将数据从主服务器上复制到从服务器上，这无疑是先了数据的异地备份。在传统的备份体制下，是将数据备份在本地。此时备份作业与数据库服务器运行

在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时，将备份数据存放在本地，也不是很安全。

如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业

带来比较大的损失。而如果使用复制来实现对数据的备份，就可以在从服务器上对数据进行备份。此时不仅不会干扰主服务气的正常运行，

而且在备份过程中主服务器可以继续处理相关的更新作业。同时在数据复制的同时，也实现了对数据的异地备份。除非主服务器和从服务器的两块硬盘同时损坏了，

否则的话数据库管理员就可以在最短时间内恢复数据，减少企业的由此带来的损失。

3.提高数据库系统的可用性

数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，

用来数据的更新与查询服务。然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员也会采用两种手段。

一是主服务器故障之后，虽然从服务器取代了主服务器的位置，但是对于主服务器可以采取的操作仍然做了一些限制。如仍然只能够进行数据的查询，而

不能够进行数据的更新、删除等操作。这主要是从数据的安全性考虑。如现在一些银行系统的升级，在升级的过程中，只能够查询余额而不能够取钱。这是同样的道理。

二是从服务器真正变成了主服务器。当从服务器切换为主服务器之后，其地位完全与原先的主服务器相同。此时可以实现对数据的查询、更新、删除等操

作。为此就需要做好数据的安全性工作。即数据的安全策略，要与原先的主服务器完全相同。否则的话，就可能会留下一定的安全隐患。

存在问题：

从数据库具有读log文件的延迟，如何解决？

由于从数据库存在更新从库中SQL数据的延迟，万一主库在从库读取binlog的时候宕机，那么数据可能丢失,主要是由于从库只有一个sql Thread去更新从库数据，

但是主库写压力大，也就是主库会有很多写任务，同时还要有IO线程与从库进行binlog输出，所以复制很可能延时

解决方法：

1.半同步复制—解决数据丢失的问题

半同步复制，可以看到当主库进行更新时，在binlog写的过程中，会主动通知Dump进程(输出IO进程)开启，与从库进行数据的同步更新，然后从库会返回一个

ack信号给主库的Dump进程，收到ack确认后，会给用户提交的修改进程发送信号，让其继续执行，当然当从库比较多时，这种方法不能保证全部的从库都进行更新，

如果网络异常或从库宕机，主库压力过大等，都会造成超时，影响客户响应，并行复制可以一定程度上解决类似问题

2.并行复制—-解决从库复制延迟的问题

正常主从复制(异步复制)的方式，也就是主库直接更新数据，但是主从的复制是在主库更新后或者过程中进行，这样显然容易使数据出问题，比如会丢失修改数据等

主从复制的几种架构方式

1、一主一从，基础的主从结构。

2、主主复制（两个主机在同一等级上，没有主从之分）

3、一主多从，适用于增删改少，查询多的业务。

4、多主一从（Mysql 5.7开始支持），适用于增删改较多，查询少的业务。

5、联级复制

mysql数据库高可用架构：

mysql高可用解决方案都有哪些？

https://mp.weixin.qq.com/s/8Yje-5ReYXhEdLQoV8ypdw

主从复制原理

可以看到mysql主从复制需要三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread）

binlog dump线程： 主库中有数据更新时，根据设置的binlog格式，将更新的事件类型写入到主库的binlog文件中，并创建log dump线程通知slave有数据更新。

当I/O线程请求日志内容时，将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。

I/O线程： 该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中。

SQL线程： 该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步。

说到主从同步，离不开binlog这个东西，先介绍下binlog吧

binlog是什么？有什么作用？

用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。可以简单理解为记录的就是sql语句

binlog 是 mysql 的逻辑日志，并且由 Server层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志

在实际应用中， binlog 的主要使用场景有两个：

用于主从复制，在主从结构中，binlog 作为操作记录从 master 被发送到 slave，slave服务器从 master 接收到的日志保存到 relay log 中。

用于数据备份，在数据库备份文件生成后，binlog保存了数据库备份后的详细信息，以便下一次备份能从备份点开始。

日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW

日志格式通过 binlog-format 指定。

STATMENT ：基于 SQL 语句的复制，每一条会修改数据的sql语句会记录到 binlog 中

ROW ：基于行的复制

MIXED ：基于 STATMENT 和 ROW 两种模式的混合复制，比如一般的数据操作使用 row 格式保存，有些表结构的变更语句，使用 statement 来记录

主从延迟

主从延迟是怎么回事？

根据前面主从复制的原理可以看出，两者之间是存在一定时间的数据不一致，也就是所谓的主从延迟。

我们来看下导致主从延迟的时间点：

主库 A 执行完成一个事务，写入 binlog，该时刻记为T1.

传给从库B，从库接受完这个binlog的时刻记为T2.

从库B执行完这个事务，该时刻记为T3.

那么所谓主从延迟，就是同一个事务，从库执行完成的时间和主库执行完成的时间之间的差值，即T3-T1。

为什么会主从延迟？

正常情况下，如果网络不延迟，那么日志从主库传给从库的时间是相当短，所以T2-T1可以基本忽略。

最直接的影响就是从库消费中转日志（relaylog）的时间段，而造成原因一般是以下几种：

1、从库的机器性能比主库要差

2、从库的压力大

按照正常的策略，读写分离，主库提供写能力，从库提供读能力。将进行大量查询放在从库上，结果导致从库上耗费了大量的CPU资源，进而影响了同步速度，造成主从延迟。

3、大事务的执行

一旦执行大事务，那么主库必须要等到事务完成之后才会写入binlog。

4、主库的DDL(alter、drop、create)

1）只读节点与主库的DDL同步是串行进行，如果DDL操作在主库执行时间很长，那么从库也会消耗同样的时间，比如在主库对一张500W的表添加一个字段耗费了10分钟，

那么从节点上也会耗费10分钟。

2）从节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，表被锁，直到查询结束为止，进而导致了从节点的数据延迟。

怎么减少主从延迟

主从同步问题永远都是一致性和性能的权衡，得看实际的应用场景，若想要减少主从延迟的时间，可以采取下面的办法：

1.降低多线程大事务并发的概率，优化业务逻辑

2.优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。

3.提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。

4。尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。

5.实时性要求的业务读强制走主库，从库只做灾备，备份。（在实际应用场景中，对于一些非核心的场景，比如库存，支付订单等，

需要直接查询主库，其他非核心场景，就不要去查主库了）

MySQL生产环境从库延迟高问题总结:

生产环境上MySQL 主从复制的延迟问题一直是个问题，基本上每天都能收到从库延迟时间的告警。延迟时间过大，会导致我们部分从只读库读取数据的业务。

如果延迟时间超过1天，甚至会影响数据同步，出报表。

下面总结下容易导致从库延迟比较大的场景。

场景1：主库DML请求频繁

业务高峰期间，特别是对于数据库主库有大量的写请求操作，即大量insert、delete、update等并发操作的情况下，会出现主从复制延时问题。

原因分析

由于主库大量的写请求操作，在短时间产生了大量的binlog。这些操作需要全部同步到从库，并且执行，因此产生了主从的数据复制延时。

从深层次分析原因，是因为在业务高峰期间的主库写入数据是并发写入的，而从库SQL Thread为单线程回放binlog日志，很容易造成relaylog堆积，产生延时。

解决思路

如果是MySQL 5.7以下的版本，可以做分片(sharding)，通过水平扩展(scale out)的方法打散写请求，提升写请求写入binlog的并行度。

如果是MySQL 5.7以上的版本，在MySQL 5.7，使用了基于逻辑时钟(Group Commit)的并行复制。而在MySQL 8.0，使用了基于Write Set的并行复制。

这两种方案都能够提升回放binlog的性能，减少延时。目前生产环境前期申请的数据库是 MySQL 5.6的，最近新申请的接触数据库是MySQL 5.7的。

目前一直在报从库延迟告警的是老的MySQL 5.6库，新的MySQL 5.7倒是一直没有，后期是否可以考虑按照业务模块

重新申请数据库分批将数据迁移出来？

场景2：主库执行大事务

大事务指一个事务的执行，耗时非常长。常见产生大事务的语句有：

使用了大量速度很慢的导入数据语句，比如：LOAD DATA INFILE等；使用了UPDATE、DELETE语句，对于一个很大的表进行全表的UPDATE和DELETE等。

当这个事务在从库执行回放执行操作时，就有可能会产生主从复制延时。

原因分析

当大事务记录入binlog并同步到从库之后，从库执行这个事务的操作耗时也非常长，这段时间，就会产生主从复制延时。

举个例子，假如主库花费200s更新了一张大表，在主从库配置相近的情况下，从库也需要花几乎同样的时间更新这张大表，此时从库延时开始堆积，后续的事件就无法更新。

解决思路

对于这种情况引起的主从复制延时，改进方法是：拆分大事务语句到若干小事务中，这样能够进行及时提交，减小主从复制延时。

特别注意 update delete 语句一定要命中索引，不要对整个表操作。如果命中条件的记录会很多，可以分批次执行，每次只执行其中部分记录。

场景3：主库对大表执行DDL语句

DDL全称为 Data Definition Language ，指一些对表结构进行修改操作的语句，比如，对表加一个字段或者加一个索引等等。当DDL对主库大表执行DDL语句

的情况下，可能会产生主从复制延时。

原因分析

DDL导致的主从复制延时的原因和大事务类似，也是因为从库执行DDL的binlog较慢而产生了主从复制延时。

解决思路

DDL本身造成的延时难以避免，建议考虑：避免业务高峰，尽量安排在业务低峰期执行 ；并且执行完要观察从库是否同步完成，等待从库延迟恢复在正常范围内，

实在不行联系值班DBA 重建从库了。

场景4：表缺乏主键或合适索引

如果数据库的表缺少主键或者合适索引，在主从复制的binlog_format设置为’row’的情况下，可能会产生主从复制延时。

原因分析

在主从复制的binlog_format设置为’row’的情况下，比如有这样的一个场景，主库更新一张500万表中的20万行数据。binlog在row格式下，记录到binlog的

为20万次update操作，也就是每次操作更新1条记录。如果这条语句恰好有不好的执行计划，如发生全表扫描，那么每一条update语句需要全表扫描。

此时SQL Thread重放将特别慢，造成严重的主从复制延时。

解决思路

检查表结构，保证每个表都有显式自增主键，并建立合适索引。具体的可以联系DBA定时捞一份数据库的slow sql，针对这些 sql 要结合具体情况优化。

场景5：主库与从库配置不一致

如果主库和从库使用了不同的计算资源和存储资源，或者使用了不同的内核调教参数，可能会造成主从不一致。

原因分析

各种硬件或者资源的配置差异都有可能导致主从的性能差异，从而导致主从复制延时发生：

硬件上：比如，主库实例服务器使用SSD磁盘，而从库实例服务器使用普通SAS盘，那么主库产生的写入操作在从库上不能马上消化掉，就产生了主从复制延时；

配置上：比如，RAID卡写策略不一致、OS内核参数设置不一致、MySQL落盘策略不一致等，都是可能的原因。

解决思路

考虑尽量统一DB机器的配置（包括硬件及选项参数）。甚至对于某些OLAP业务，从库实例硬件配置需要略高于主库。 配置问题只能申请资源的时候注意了，

不能贪图节省资源！
实操篇

1.生产环境每次人工sql 操作

考虑到我们上线的时候需要做sql变更以及生产上排查问题时需要手工执行sql，注意点如下:

1.1 insert

正常情况下生产环境手工操作，不会大批量插入记录，忽略。

1.2 delete

涉及到的记录数据不要太多，一定要加where 条件并且命中索引，如果是删除整个表，直接使用truncate。

1.3 update

涉及到的记录数据不要太多，一定要加where 条件并且命中索引。

1.4 select

建议在只读库操作，加where条件并且命中索引。

1.5 alter table

增加表字段，选择业务低峰期，硬抗吧。执行完观察同步延迟，有问题及时联系值班DBA。

1.6 create table

如果是临时表，强制使用 temporary 建表，避免因为临时表的大量操作导致只读库同步慢。

如果表的数据量很大，强制加上自增主键id 。

2.代码里的sql

2.1 定时捞慢SQL分析，解决慢 SQL。

2.2 写代码的时候考虑 SQL 命中索引， explain + sql 可以check 执行计划。

explain 用法： Explain用法

2.3 尽量不要联表查询

读写分离的场景下,怎么保证从数据库读到最新的数据?

数据库读写分离，主要解决高并发时，提高系统的吞吐量。

来看下读写分离数据库模型：

1.写请求是直接写主库，然后同步数据到从库

2.读请求一般直接读从库，除飞强制读主库

在高并发场景或者网络不佳的场景，如果存在较大的主从同步数据延迟，这时候读请求去读从库，就会读到旧数据。

这时候最简单暴力的方法，就是强制读主库。

实际上可以使用缓存标记法。

1.A发起写请求，更新主库数据，并在缓存中设置一个标记，表示数据已更新，标记格式为：userId+业务Id。

2.设置此标记，设置过期时间（估值为主库和从库同步延迟的时间）

3.B发起读请求，先判断此请求，在缓存中有没有更新标记。

4.如果存在标记，走主库；如果没有，请求走从库。

这个方案，解决了数据不一致问题，但是每次请求都要先跟缓存打交道，会影响系统吞吐。

A发起写请求，更新主库数据，并在缓存中设置一个标记，表示数据已更新，标记格式为：userId+业务Id。

设置此标记，设置过期时间（估值为主库和从库同步延迟的时间） B发起读请求，先判断此请求，在缓存中有没有更新标记。

如果存在标记，走主库；如果没有，请求走从库。

一个 6 亿的表 a，一个 3 亿的表 b，通过外间 tid 关联，

你如何最快的查询出满足条件的第 50000 到第 50200 中的

这 200 条数据记录。

1、如果 A 表 TID 是自增长,并且是连续的,B 表的 ID 为索引 select * from a,b

where a.tid = b.id and a.tid>500000 limit 200;

2、如果 A 表的 TID 不是连续的,那么就需要使用覆盖索引.TID 要么是主键,要

么是辅助索引,B 表 ID 也需要有索引。 select * from b , (select tid from a

limit 50000,200) a where b.id = a .tid;

说说分库与分表的设计

分库分表方案，分库分表中间件，分库分表可能遇到的问题

分库分表方案:

 水平分库：以字段为依据，按照一定策略（hash、range 等），将一个库中的数

据拆分到多个库中。

 水平分表：以字段为依据，按照一定策略（hash、range 等），将一个表中的数

据拆分到多个表中。

 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和

扩展表）中。

常用的分库分表中间件：

 sharding-jdbc（当当）

 Mycat

 TDDL（淘宝）

 Oceanus(58 同城数据库中间件)

 vitess（谷歌开发的数据库中间件）

 Atlas(Qihoo 360)

分库分表可能遇到的问题(分库分表经典15连问(田螺))

1.我们为什么需要分库分表

2.什么时候考虑分库分表

3.如何选择分表键

4.非分表键如何查询

5.分表策略如何选择

6.如何避免热点问题数据倾斜

7.分库后，事务问题如何解决：需要用分布式事务啦

8.跨节点join关联问题：解决这一问题可以分两次查询实现

9.跨节点的 count,order by,group by 以及聚合函数问题：分别在各个节点上得到

结果后在应用程序端进行合并

10.分库分表后的分页问题(跨分片的排序分页问题（后台加大 pagesize 处理？）)

11.分布式id:数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID

12.分库分表选择哪种中间件

13.如何评估分库数量(容量规划)

14.垂直分库，水平分库，垂直分表，水平分表的区别

15.分表要停服吗？不停服怎么做？

16.旧数据迁移

17.扩容问题

https://mp.weixin.qq.com/s/cZ7mUsCvBxmo1dzsPA3WjQ

mysql分库分表与分区的区别和思考：

https://mp.weixin.qq.com/s/rdLDSicqmEeEEUQr6RuEcw

B+树，B树，红黑树的数据结构理解

mysql数据索引解析：

https://www.cnblogs.com/yanze/p/9908138.html

https://blog.csdn.net/qq_36098284/article/details/80178336

https://tech.meituan.com/2017/05/19/about-desk-io.html

https://blog.csdn.net/white_ice/article/details/115478367

MySQL数据库之互联网常用架构方案 blog.csdn.net

58怎么玩数据库架构 mp.weixin.qq.com