Mysql

关系型数据库和非关系型数据库的区别

1. 数据存储结构：
   首先关系型数据库一般都有固定的表结构，并且需要通过DDL语句来修改表结构，不是很容易进行扩展，而非关系型数据库的存储机制就有很多了，比如基于文档的，K-V键值对的，还有基于图的等，对于数据的格式十分灵活没有固定的表结构，方便扩展，因此如果业务的数据结构并不是固定的或者经常变动比较大的，那么非关系型数据库是个好的选择

   关系型数据库存储结构比较固定,不容易拓展

   非关系型数据库结构不固定，容易拓展

2. 可扩展性
   传统的关系型数据库给人一种横向扩展难，不好对数据进行分片等，而一些非关系型数据库则原生就支持数据的水平扩展(比如mongodb的sharding机制)，并且这可能也是很多NoSQL的一大卖点，其实象Mysql这种关系型数据库的水平扩展也并不是难，即使NoSQL水平扩展容易但对于向跨分片进行joins这种场景都没有什么太好的解决办法，不管是关系型还是非关系型数据库，解决水平扩展或者跨分片Joins这种场景，在应用层和数据库层中间加一层中间件来做数据处理也许是个好的办法

3. 数据一致性
   关系型数据库一般强调的是数据最终一致性，而不没有像ACID一样强调数据的强一致性，从非关系型数据库中读到的有可能还是处于一个中间态的数据，因此如果你的业务对于数据的一致性要求很高，那么非关系型数据库并不一个很好的选择，非关系型数据库可能更多的偏向于OLAP场景，而关系型数据库更多偏向于OLTP场景

   关系型数据库强调数据的一致性

   非关系数据库不是很强调

三大范式

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

数据库第二范式：关系模式必须满足第一范式，并且所有非主属性都完全依赖于主码。注意，符合第二范式的关系模型可能还存在数据冗余、更新异常等问题。关系模型（学号，姓名，专业编号，专业名称）中，姓名依赖于学号，而专业名称依赖于专业编号，不满足数据库第二范式

数据库第三范式：关系模型满足第二范式，所有非主属性对任何候选关键字都不存在传递依赖。即每个属性都跟主键有直接关系而不是间接关系。接着以学生表举例，对于关系模型（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）院校地址，院校电话和学号不存在直接关系，因此不满足第三范式。

第一范式：数据具有原子性，不可再分。

第二范式：不允许出现部分依赖，即不允许出现复合主键。

第三范式：不存在传递依赖，即不允许出现某个字段依赖非主键。

MySQL的架构

MySQL可以分为应用层,逻辑层,数据库引擎层,物理层。

应用层：负责和客户端，响应客户端请求，建立连接，返回数据。

逻辑层：包括SQK接口，解析器，优化器，Cache与buffer。

数据库引擎层：有常见的MyISAM,InnoDB等等。

物理层：负责文件存储，日志等等。

存储引擎

⼀些常⽤命令

查看MySQL提供的所有存储引擎

show engines

查看MySQL当前默认的存储引擎

show variables like '%storage_engine

查看表的存储引擎

show table status like "tablename"

MyISAM和InnoDB区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，⽽且提供了⼤量的特性，包括全⽂索引、压缩、空间函数等，但MyISAM不⽀持事务和⾏级锁，⽽且最⼤的缺陷就是崩溃后⽆法安全恢复。不过，5.5版本之后，MySQL引⼊了InnoDB（事务性数据库引擎），MySQL5.5版本后默认的存储引擎为InnoDB。⼤多数时候我们使⽤的都是 InnoDB 存储引擎，但是在某些情况下使⽤ MyISAM 也是合适的⽐如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）。

两者对比

1. 是否⽀持⾏级锁 : MyISAM 只有表级锁(table-level locking)，⽽InnoDB ⽀持⾏级锁(rowlevel locking)和表级锁,默认为⾏级锁。

   InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

2. 是否⽀持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原⼦性,其执⾏速度⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crashrecoverycapabilities)的事务安全(transaction-safe (ACID compliant))型表。

3. 是否⽀持外键： MyISAM不⽀持，⽽InnoDB⽀持

4. 是否⽀持MVCC ：仅 InnoDB ⽀持。应对⾼并发事务, MVCC⽐单纯的加锁更⾼效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作;MVCC可以使⽤ 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统⼀。

5. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

   MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

   也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

​ 6.Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

​ 7.MyISAM表格可以被压缩后进行查询操作

​ 8.InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

​ 9.Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

​ Innodb：frm是表定义文件，ibd是数据文件

​ Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

如何在两者之间选择

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；
4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

InnoDB为什么推荐使用自增ID作为主键？

答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

innodb引擎的4大特性

• 插入缓冲（insert buffer)
• 二次写(double write)
• 自适应哈希索引(ahi)
• 预读(read ahead)

InnoDB如何保证事务的原子性、持久性和一致性？

• undo log保障原子性。该log保存了事务发生之前的数据的一个版本，可以用于回滚，从而保证事务原子性。

• redo log保证事务的持久性，该log关注于事务的恢复.在重启mysql服务的时候，根据redo log进行重做，从而使事务有持久性。

• undo log+redo log保障一致性。事务中的执行需要redo log，如果执行失败，需要undo log 回滚。

Hash索引和B+树索引

首先要知道Hash索引和B+树索引的底层实现原理:

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.

B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.

不同点:

• hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.

因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.

• hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性.
• hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
• hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.

因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.

为什么使用B+树

• 虽然哈希索引是O(1)，树索引是O(log(n))，但SQL有很多“有序”需求，故数据库使用树型索引
• 很适合磁盘存储，能够充分利用局部性原理，磁盘预读
  • 局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO
  • 数据预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，以便未来减少磁盘IO
• 很低的树高度，能够存储大量数据，同时能有效的减少IO操作。
• 索引本身占用的内存很小
• 能够很好的支持单点查询，范围查询，有序性查询
• 查询效率稳定
• B+树存放数据数计算（https://blog.csdn.net/csdnlijingran/article/details/102309593）
• 不用B树的原因是因为B树不支持范围查找，B+树的范围查找只需要遍历叶子节点就行，而B树则需要中序遍历

一颗B+树能存多少数据

https://blog.csdn.net/weixin_51867896/article/details/122799836

字符集及校对规则

字符集指的是⼀种从⼆进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。MySQL中每⼀种字符集都会对应⼀系列的校对规则。

MySQL采⽤的是类似继承的⽅式指定字符集的默认值，每个数据库以及每张数据表都有⾃⼰的默认值，他们逐层继承。⽐如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采⽤默认字符集）

查询缓存的使⽤

执⾏查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实⽤

my.cnf加⼊以下配置，重启MySQL开启查询缓存

query_cache_size = 30M

query_cache_type=1

MySQL执⾏以下命令也可以开启查询缓存

开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。

这⾥的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等⼀些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。

此外，如果查询中包含任何⽤户⾃定义函数、存储函数、⽤户变量、临时表、MySQL库中的系统表，其查询结果也不会被缓存。

缓存建⽴之后，MySQL的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发⽣变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做⼀次缓存操作，失效后还要销毁。

因此，开启缓存查询要谨慎，尤其对于写密集的应⽤来说更是如此。如果开启，要注意合理控制缓存空间⼤⼩，⼀般来说其⼤⼩设置为⼏⼗MB比较合适。

此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存

事务

什么是事务?

事务是逻辑上的⼀组操作，要么都执⾏，要么都不执⾏。

事物的四⼤特性(ACID)

1**.原⼦性（Atomicity**）： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么 全部完成，要么完全不起作⽤；

2. ⼀致性（Consistency）： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；
3. 隔离性（Isolation）： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
4. 持久性（Durability）： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

并发事务带来哪些问题?

• 脏读(Dirty read): 当一个事务正在访问数据并且对数据进行了修改,而这种修改还没有提 交到数据库中,这时另外一个事务也访问了这个数据,然后使用了这个数据。因为这个数据 是还没有提交的数据,那么另外一个事务读到的这个数据是“脏数据”,依据“脏数据”所做的 操作可能是不正确的。一改一读
• 丢失修改(Lost to modify): 指在一个事务读取一个数据时,另外一个事务也访问了该数 据,那么在第一个事务中修改了这个数据后,第二个事务也修改了这个数据。这样第一个事 务内的修改结果就被丢失,因此称为丢失修改。 例如:事务1读取某表中的数据A=20,事 务2也读取A=20,事务1修改A=A-1,事务2也修改A=A-1,最终结果A=19,事务1的修改被丢失。两个改
• 不可重复读(Unrepeatable read): 指在一个事务内多次读同一数据。在这个事务还没有结 束时,另一个事务也访问该数据。那么,在第一个事务中的两次读数据之间,由于第二个事 务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到 的数据是不一样的情况,因此称为不可重复读。一改一读
• 幻读(Phantom read): 幻读与不可重复读类似。它发 生在一个事务(T1)读取了几行数 据,接着另一个并发事务(T2)插入了一些数据时。在随后的查询中,第一个事务(T1) 就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所以称为幻读。一改一读
  • 不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改,幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。
  • 幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题

事务隔离级别有哪些?MySQL的默认隔离级别是?

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

  • 一个事务还没提交，它做的变更就能被别的事务看到

• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。

  • 一个事务提交后，它做的变更才能被别的事务看到

• REPEATABLE-READ(可重复读)： 对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣

  • 一个事务执行过程中看到的数据总是和事务启动时看到的数据是一致的。在这个级别下事务未提交，做出的变更其它事务也看不到。

• SERIALIZABLE(可串⾏化)： 最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读。

  • 对于同一行记录进行读写会分别加读写锁，当发生读写锁冲突，后面执行的事务需等前面执行的事务完成才能继续执行。
    

MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）.

SELECT @@tx_isolation查看支持的默认的隔离级别

这⾥需要注意的是：与 SQL 标准不同的地⽅在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读）

事务隔离级别下使⽤的是Next-Key Lock 锁算法，因此可以避免幻读的产⽣，这与其他数据库系统(如 SQL Server)是不同的。

所以说InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了SQL标准的 SERIALIZABLE(可串⾏化) 隔离级别。

因为隔离级别越低，事务请求的锁越少，所以⼤部分数据库系统的隔离级别都READCOMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使⽤ REPEAaTABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下⼀般会⽤到 SERIALIZABLE(可串⾏化) 隔离级别。

可重复读的实现原理

Repeatable Read（可重复读）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录（读已经提交的，其实是读早于本事务开始且已经提交的），但是不能看到其他事务对已有记录的更新（即晚于本事务开始的），并且，该事务不要求与其他事务是“可串行化”的。

简单的来说就是当前事务在堆当前的某条数据在其他事务的影响下重读的结果是一样的

解决方法

使用MVCC（多版本并发控制）。InnoDB为每行记录添加了一个版本号（系统版本号），每当修改数据时，版本号加一。
在读取事务开始时，系统会给事务一个当前版本号，事务会读取版本号<=当前版本号的数据，这时就算另一个事务插入一个数据，并立马提交，新插入这条数据的版本号会比读取事务的版本号高，因此读取事务读的数据还是不会变。

InnoDB 的可重复读的实现，利用了实现 MVCC 技术的快照技术。这是 MVCC 和基于封锁技术这两个并非控制技术的结合之处。

在RR隔离级别下为事务设置了一个“一致性读视图（即快照）”，之后读取数据，就是根据这个快照来获取，这样，就不能看到他晚于本事务的事务对已有记录的更新（更新生成新版本，必然不在旧的快照所限定的范围内）。

根据隔离级别判断是不是要使用一个新的快照，如果是可重复读，则不使用新快照，沿用老的快照，这样就能保证所有的读操作看到的是同一个数据状态；同时也确保了读已提交隔离级别下一个事务块内的不同语句的读操作看到的不是同一个数据状态。

总结：

参考 https://blog.csdn.net/huanghanqian/article/details/79517480

Mysql锁

按锁粒度分类：
1. ⾏锁：锁某⾏数据，锁粒度最⼩，并发度⾼
2. 表锁：锁整张表，锁粒度最⼤，并发度低
3. 间隙锁：锁的是⼀个区间

还可以分为：
1. 共享锁：也就是读锁，⼀个事务给某⾏数据加了读锁，其他事务也可以读，但是不能写
2. 排它锁：也就是写锁，⼀个事务给某⾏数据加了写锁，其他事务不能读，也不能写

还可以分为：
1. 乐观锁：并不会真正的去锁某⾏记录，⽽是通过⼀个版本号来实现的
2. 悲观锁：上⾯所的⾏锁、表锁等都是悲观锁

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

锁机制与InnoDB锁算法

MyISAM和InnoDB存储引擎使⽤的锁：

• MyISAM采⽤表级锁(table-level locking)。
• InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁

表级锁和⾏级锁对⽐：

• 表级锁： MySQL中锁定 粒度最⼤ 的⼀种锁，对当前操作的整张表加锁，实现简单，资源消
  耗也比较少，加锁快，不会出现死锁。其锁定粒度最⼤，触发锁冲突的概率最⾼，并发度最
  低，MyISAM和 InnoDB引擎都⽀持表级锁。
• ⾏级锁： MySQL中锁定 粒度最⼩ 的⼀种锁，只针对当前操作的⾏进⾏加锁。 ⾏级锁能⼤
  ⼤减少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会
  出现死锁。

InnoDB存储引擎的锁的算法有三种：

• Record lock：单个⾏记录上的锁
• Gap lock：间隙锁，锁定⼀个范围，不包括记录本身 目的是为了防止同一个事物的两次当前读，出现幻读的情况
• Next-key lock：record+gap 锁定⼀个范围，包含记录本身

相关知识点

1. innodb对于⾏的查询使⽤next-key lock
2. Next-locking keying为了解决Phantom Problem幻读问题
3. 当查询的索引含有唯⼀属性时，将next-key lock降级为record key
4. Gap锁设计的⽬的是为了阻⽌多个事务将记录插⼊到同⼀范围内，⽽这会导致幻读问题的产⽣
5. 有两种⽅式显式关闭gap锁：（除了外键约束和唯⼀性检查外，其余情况仅使⽤recordlock） 不推荐
   1. 将事务隔离级别设置为RC(可重复读)
   2. 将参数innodb_locks_unsafe_for_binlog设置为1

间隙锁死锁

https://blog.csdn.net/java_beautiful/article/details/125572280

MySQL中的按粒度的锁分类

• 表级锁: 对当前操作的整张表加锁,实现简单，加锁快，但并发能力低。

• 行锁: 锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，那么 InnoDB 会创建一个隐藏的聚簇索引加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

• Gap 锁：也称为间隙锁: 锁定一个范围但不包括记录本身。其目的是为了防止同一事物的两次当前读出现幻读的情况。

• Next-key Lock： 行锁+gap锁。

如何解决数据库死锁

1. 预先检测到死锁的循环依赖，并立即返回一个错误。
2. 当查询的时间达到锁等待超时的设定后放弃锁请求。

解释⼀下什么是池化设计思想 什么是数据库连接池 为什么需要数据库连接池

池化设计应该不是⼀个新名词。我们常⻅的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。

这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。就好⽐你去⻝堂打饭，打饭的⼤妈会先把饭盛好⼏份放那⾥，你来了就直接拿着饭盒加菜即可，不⽤再临时⼜盛饭⼜打菜，效率就⾼了。

除了初始化资源，池化设计还包括如下这些特征：池⼦的初始值、池⼦的活跃值、池⼦的最⼤值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。

数据库连接本质就是⼀个 socket 的连接。数据库服务端还要维护⼀些缓存和⽤户权限信息之类的 所以占⽤了⼀些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重⽤这些连接。为每个⽤户打开和维护数据库连接，尤其是对动态数据库驱动的⽹站应⽤程序的请求，既昂贵⼜浪费资源。

在连接池中，创建连接后，将其放置在池中，并再次使⽤它，因此不必建⽴新的连接。如果使⽤了所有连接，则会建⽴⼀个新连接并将其添加到池中。 连接池还减少了⽤户必须等待建⽴与数据库的连接的时间。

分库分表之后,id 主键如何处理

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要⼀个全局唯⼀的id 来⽀持。
⽣成全局 id 有下⾯这⼏种⽅式：

• UUID：不适合作为主键，因为太⻓了，并且⽆序不可读，查询效率低。比较适合⽤于⽣成唯⼀的名字的标示⽐如⽂件的名字。

• 数据库⾃增 id : 两台数据库分别设置不同步⻓，⽣成不重复ID的策略来实现⾼可⽤。这种⽅式⽣成的 id 有序，但是需要独⽴部署数据库实例，成本⾼，还会有性能瓶颈。

• 利⽤ redis ⽣成 id : 性能比较好，灵活⽅便，不依赖于数据库。但是，引⼊了新的组件造成系统更加复杂，可⽤性降低，编码更加复杂，增加了系统成本。

• Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。

• 美团的Leaf分布式ID⽣成系统 ：Leaf 是美团开源的分布式ID⽣成器，能保证全局唯⼀性、趋势递增、单调递增、信息安全，⾥⾯也提到了⼏种分布式⽅案的对⽐，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。

  • 美团技术团队的⼀篇⽂章：https://tech.meituan.com/2017/04/21/mt-leaf.html 。

⼀条SQL语句执⾏得很慢的原因有哪些

参考连接：https://www.cnblogs.com/kubidemanong/p/10734045.html

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

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

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

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

(1)、没有用上索引：例如该字段没有索引导致走全表扫描；由于对字段进行运算（select * from t where c - 1 = 1000;）、函数操作（pow(c,2) = 1000;）导致无法用索引。

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

索引

Mysql有哪些常见索引类型？

• 数据结构角度

  B-Tree索引

  哈希索引

  R-Tree索引

  全文索引

• 物理存储角度

  主键索引（聚簇索引）：叶子节点存的是整行的数据

  非主键索引（二级索引）：叶子节点存的主键的值

在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?

• 使用不等于查询,

• 不匹配最左前缀原则

• 列参与了数学运算或者函数

• 在字符串like时左边是通配符.类似于'%aaa'.

• 当mysql分析全表扫描比使用索引快的时候不使用索引

• 当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引

  参考https://blog.csdn.net/c1776167012/article/details/120788728

简述Hash索引

哈希索引对于每一行数据计算一个哈希码，并将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。只有 Memory 引擎显式支持哈希索引。

Hash索引不支持范围查询，无法用于排序，也不支持部分索引列匹配查找。

简述自适应Hash索引

InnoDB对于频繁使用的某些索引值，会在内存中基于 B-Tree 索引之上再创键一个哈希索引，这也被称为自适应Hash索引。

简述聚集索引和稀疏索引

聚集索引按每张表的主键构建一棵B+树，数据库中的每个搜索键值都有一个索引记录，每个数据页通过双向链表连接。表数据访问更快，但表更新代价高。

稀疏索引不会为每个搜索关键字创建索引记录。搜索过程需要，我们首先按索引记录进行操作，并按顺序搜索，直到找到所需的数据为止。

简述辅助索引与回表查询

辅助索引是非聚集索引，叶子节点不包含记录的全部数据，包含了一个书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。

通过辅助索引查询，先通过书签查到聚集索引，再根据聚集索引查对应的值，需要两次，也称为回表查询。

简述联合索引和最左匹配原则

联合索引是指对表上的多个列的关键词进行索引。

对于联合索引的查询，如果精确匹配联合索引的左边连续一列或者多列，则mysql会一直向右匹配直到遇到范围查询（<,between,like）就停止匹配。Mysql会对第一个索引字段数据进行排序，在第一个字段基础上，再对第二个字段排序。

参考：https://blog.csdn.net/sinat_41917109/article/details/88944290

简述覆盖索引

**覆盖索引指一个索引包含或覆盖了所有需要查询的字段的值，**不需要回表查询，即索引本身存了对应的值。

基于主键索引的查询和非主键索引的查询有什么区别？

对于select * from 主键=XX，基于主键的普通查询仅查找主键这棵树

对于select * from 非主键=XX，基于非主键的查询有可能存在回表过程（回到主键索引树搜索的过程称为回表），因为非主键索引叶子节点仅存主键值，无整行全部信息。

非主键索引的查询一定会回表吗？

不一定，当查询语句的要求字段全部命中索引，不用回表查询。如select 主键 from 非主键=XX，此时非主键索引叶子节点即可拿到主键信息，不用回表。

为什么数据库不用红黑树 用B+树

红黑树的出度为 2，而 B Tree 的出度一般都非常大**。红黑树的树高 h 很明显比 B Tree 大非常多，IO次数很多，导致会比较慢，因此检索的次数也就更多**。

B+Tree 相比于 B-Tree 更适合外存索引，拥有更大的出度，IO次数较少，检索效率会更高。

创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度.

主键索引和唯一索引的区别

1.主键为一种约束，唯一索引为一种索引，本质上就不同；

2.主键创建后一定包含唯一性索引，而唯一索引不一定就是主键；

3.主键不允许空值，唯一索引可以为空；

4.主键可以被其他表引用，而唯一索引不可以；

5.一个表最多只能创建一个主键，而可以创建多个唯一索引；

6.主键和索引都是键，主键是逻辑键，索引为物理键，即主键不实际存在。

简述MySQL使用EXPLAIN 的关键字段

explain关键字用于分析sql语句的执行情况，可以通过他进行sql语句的性能分析。

type：表示连接类型，从好到差的类型排序为

• system：系统表，数据已经加载到内存里。
• const：常量连接，通过索引一次就找到。
• eq_ref：唯一性索引扫描，返回所有匹配某个单独值的行。
• ref：非主键非唯一索引等值扫描，const或eq_ref改为普通非唯一索引。
• range：范围扫描，在索引上扫码特定范围内的值。
• index：索引树扫描，扫描索引上的全部数据。
• all：全表扫描。

key：显示MySQL实际决定使用的键。

key_len：显示MySQL决定使用的键长度，长度越短越好

Extra：额外信息

• Using filesort：MySQL使用外部的索引排序，很慢需要优化。
• Using temporary：使用了临时表保存中间结果，很慢需要优化。
• Using index：使用了覆盖索引。
• Using where：使用了where。

MySQL插入数据的流程

https://blog.csdn.net/zhaoliang831214/article/details/82711350

https://blog.csdn.net/weixin_40581617/article/details/80623276

1. 会话状态转换为update
2. 激活事物状态由 not_active 变为 active
3. 查找定位数据
4. 进行乐观插入

Mysql语句执行流程

• 流程：

  • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。

  • 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

  • 分析器:

    没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

    • 第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

• 第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

  • 优化器：

    按照 MySQL 认为最优的方案去执行。

    • https://zhuanlan.zhihu.com/p/192707721

  • MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划。

    • 创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面, IO 成本和 CPU 成本
      • IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关
      • CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。

  • 执行器: 执行语句，然后从存储引擎返回数据。

总结

1. 客户端首先通过连接器进行身份认证和权限相关
2. 如果是执行查询语句的时候，会先查询缓存，但MySQL 8.0 版本后该步骤移除。
3. 没有命中缓存的话，SQL 语句就会经过解析器，分析语句，包括语法检查等等。
4. 通过优化器，将用户的SQL语句按照 MySQL 认为最优的方案去执行。
5. 执行语句，并从存储引擎返回数据。

简述MySQL优化流程

1. 通过慢日志定位执行较慢的SQL语句 HOW VARIABLES LIKE '%query%' 查询慢日志相关信息
2. 利用explain对这些关键字段进行分析
3. 根据分析结果进行优化

SQL执行顺序

-- 语法顺序
select distinct 
        <select_list>
from
    <left_table><join_type>
join <right_table> on <join_condition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit number>


-- 执行顺序
1、from <left_table><join_type>
2、on <join_condition>
3、<join_type> join <right_table>
4、where <where_condition>
5、group by <group_by_list>
6、having <having_condition>
7、select
8、distinct <select_list>
9、order by <order_by_condition>
10、limit <limit_number>

基本优化

参考

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485117&idx=1&sn=92361755b7c3de488b415ec4c5f46d73&chksm=cea24976f9d5c060babe50c3747616cce63df5d50947903a262704988143c2eeb4069ae45420&token=79317275&lang=zh_CN%23rd

• 数据类型选择：选择能够满足业务需求的最小数据类型。同时对于数值类型，如果没有负数，则使用unsigned

• 如果只查询一条记录，如是否存在，需要加上limit阻断后续的查找。

• 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询统计所有 count（*）会统计值为NULL的行，而count（列名）不会统计值为NULL的行。

• 主键外键约束在应用层解决。

• OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内

• 尽量避免在WHERE子句中使用!=或< >操作符，否则将引擎放弃使用索引而进行全表扫描 <>等价于!=

• 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5

• 单表数据保持在500w以内，如果大了就进行分表

• 禁止数据库中存储图片，图片等二进制数据

• 尽可能把所有列定义为 NOT NULL：索引 NULL 列需要额外的空间来保存，所以要占用更多的空间进行比较和计算时要对 NULL 值做特别的处理

• 财务相关数据使用decimal类型

  • 单表索引不要超过5个

• 禁止给表的每一列建立单独的索引。使用联合索引代替

• 建立联合索引：

  • 将区分度高的放在最左侧。（区分度=列中不同值的数量/列的总行数）
  • 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
  • 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

• 避免使用子查询，可以把子查询优化为 join 操作：通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。

  • 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

• 避免使用 JOIN 关联太多的表

• WHERE 从句中禁止对列进行函数转换和计算

• 超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作

  • 大批量操作可能会造成严重的主从延迟：主从环境中,大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间， 而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况
  • binlog 日志为 row 格式时会产生大量的日志：大批量写操作会产生大量日志，特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因
  • 避免产生大事务操作：大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对 MySQL 的性能产生非常大的影响。特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批

• 将or条件改写成union all

• 将xx is not null改写成ifnull((xx,0) > 0)

• 少用内联子查询（select后有子查询），因为Sql返回多少行，内联子查询就要执行多少次

• 基于成本的优化器CBO对子查询的处理能力比较弱，可以使用inner join

Limit

• 原因：Limit分页时，如limit offset, size会扫描前offset + size行，然后去掉前面的offset行，返回最后的size行。当offset过大的时候，或者有where条件的时候会走全表查询。效率极低。

• 解决方案：

  • 利用覆盖索引进行优化：

    • SELECT * FROM tableName
      WHERE id >= (SELECT id FROM tableName ORDER BY id LIMIT 500000 , 1)
      LIMIT 2;
      -- 其中，id为主键，在子查询中，只查询了id，因此可以利用覆盖索引查询出第500000的id的位置（不用回表查询）。然后通过id走索引会表查询出数据
      

Like

• 原因：因为B+树的最左前缀匹配。导致全模糊查询以及左模糊查询无法生效。会触发全表查询。

• 解决方案：

  • 利用覆盖索引优化（同上，在要模糊的字段上加索引，减少回表查询）：

    • explain select * from tableName where id in (select id FROM tableName where name like "%searchKey%");
      

  • 干掉左边的模糊匹配：参考

  • 使用全文索引（建立全文索引 + ES分词）：

    • SELECT * FROM tableName WHERE MATCH(`name`) AGAINST('searchKey')
      

大表优化

当MySQL单表记录数过⼤时，数据库的CRUD性能会明显下降，⼀些常⻅的优化措施如下

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

• 读写分离：经典的数据库拆分方案,主库负责写,从库负责读;

• 垂直分区：简单来说垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表。例如,用户表中既有用户的登录信息又有用户的基本信息,可以将用户表拆分成两个单独的表,甚至放到单独的库做分库。（数据库范式）

​ 垂直拆分的优点： 可以使得列数据变⼩，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

​ 垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应⽤层进⾏Join来解决。此外，垂直分区会让事务变得更加复杂；

• 保持数据表结构不变,通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中,达到了分布式的目的。 水平拆分可以支撑非常大的数据量。（分表）

• ⽔平分区 ：保持数据表结构不变，通过某种策略存储数据分⽚。这样每⼀⽚数据分散到不同的表或者库中，达到了分布式的⽬的。 ⽔平拆分可以⽀撑⾮常⼤的数据量。⽔平拆分是指数据表⾏的拆分，表的⾏数超过200万⾏时，就会变慢，这时可以把⼀张的表的数
  据拆成多张表来存放。举个例⼦：我们可以将⽤户信息表拆分成多个⽤户信息表，这样就可以避免单⼀表数据量过⼤对性能造成影响。

⽔平拆分可以⽀持⾮常⼤的数据量。需要注意的⼀点是：分表仅仅是解决了单⼀表数据过⼤的问题，但由于表的数据还是在同⼀台机器上，其实对于提升MySQL并发能⼒没有什么意义，所以
⽔平拆分最好分库 。
⽔平拆分能够 ⽀持⾮常⼤的数据量存储，应⽤端改造也少，但 分⽚事务难以解决 ，跨节点Join性能差，逻辑复杂。尽量不要对数据进⾏分⽚，因为拆分会带来逻辑、部署、运维的各种复杂度 ，⼀般的数据表在优化得当的情况下⽀撑千万以下的数据量是没有太⼤问题的。如果实在要分⽚，尽量选择客户端分⽚架构，这样可以减少⼀次和中间件的⽹络I/O。
下⾯补充⼀下数据库分⽚的两种常⻅⽅案：

​ 客户端代理： 分⽚逻辑在应⽤端，封装在jar包中，通过修改或者封装JDBC层来实现。 当
当⽹的 Sharding-JDBC 、阿⾥的TDDL是两种⽐᫾常⽤的实现。

​ 中间件代理： 在应⽤和数据中间加了⼀个代理层。分⽚逻辑统⼀维护在中间件服务中。 我
们现在谈的 Mycat 、360的Atlas、⽹易的DDB等等都是这种架构的实现。

清理表碎片

Mysql中删除

• Mysql可以通过drop、truncate、delete删除数据
  • drop table table_name：不管是innoDB还是MyISAM都会立刻释放空间
  • truncate table table_name：不管是innoDB还是MyISAM都会立刻释放空间
  • delete from table_name：删除表的全部数据，对于MyISAM会立刻释放磁盘空间，对于InnoDB不会释放磁盘空间
  • delete from table_name where xxx：带条件的删除，不管是InnoDB还是MyISAM都不会释放磁盘空间
  • delete 操作后，使用optimize table table_name会立即释放磁盘空间，不管是InnoDB还是MyISAM
• 对于delete的数据虽然没有释放磁盘空间，但是下次插入数据时，仍然可以使用这部分空间。

Mysql表碎片

• 产生原因：删除的原因导致的
  • 删除一行内容后，该段空间就会变为空白、被留空，在一段时间内的大量删除操作，会是的留空的空间比存储列表内容所使用的空间更大。
  • 执行插入操作时，MySql会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，就会产生碎片
• 当碎片多的时候，将会影响b+树查询性能（如果是SSD的话则不会）。

Mysql读取

• Mysql预读：Mysql的预读分为线性预读（单位为extend）和随机预读（单位为页）
  • 线性预读（linear read-ahead）：线性预读的读取单位为extend，可以通过innodb_read_ahead_threshold来指定大小，默认为56。如果一个extend中顺序读取的page大于等于该变量时，MySQL就会把下一个extend预读到buffer pool中（注意MySql buffer pool的变种LRU机制）
  • 随机预读（randomread-ahead）：随机预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，Innodb会将该extent中的剩余page一并读到buffer pool中。
    • 由于随机预读方式给innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃，默认是OFF。若要启用此功能，即将配置变量设置innodb_random_read_ahead为ON。

MVCC

参考https://www.jianshu.com/p/8845ddca3b23

• MVCC（Multi-Version Concurrency Control）多版本并发控制，MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，它在不同的数据库引擎中有不同的实现。MySQL中MVCC只能在Repeatable Read（读可重复读）、Read Committed（读可提交）这两个隔离级别下工作。

  • 读提交总是读取数据的最新行，而不是复合当前事务版本的数据行
  • 序列化会对所有的读取操作加锁

• 在MVCC中，删除语句并不会导致要删除的记录真正被删除，而是在丢弃相应的undo log时，才会删除相应的行及索引记录。

实现原理

• 数据库隐藏字段：
  • DB_TRX_ID：6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
  • DB_ROLL_PTR：7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
  • DB_ROW_ID：6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

MySQL MVCC具体实现方式是哪一种

参考:https://blog.csdn.net/SnailMann/article/details/94724197

隐式字段

undo日志

Read View(读视图)

MVCC能解决什么问题，好处是？

数据库并发场景有三种，分别为：

• 读-读：不存在任何问题，也不需要并发控制
• 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
• 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC带来的好处是？

**多版本并发控制（MVCC）**是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题

• 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

• 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

小结一下咯

总之，MVCC就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案，所以在数据库中，因为有了MVCC，所以我们可以形成两个组合：

• MVCC + 悲观锁
  MVCC解决读写冲突，悲观锁解决写写冲突
• MVCC + 乐观锁
  MVCC解决读写冲突，乐观锁解决写写冲突
  这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题

读提交和可重复读都基于MVCC实现，有什么区别？

在可重复读级别下，只会在事务开始前创建视图，事务中后续的查询共用一个视图。

而读提交级别下每个语句执行前都会创建新的视图。因此对于可重复读，查询只能看到事务创建前就已经提交的数据。

简述MySQL中的日志log

redo log: 存储引擎级别的log（InnoDB有，MyISAM没有），该log关注于事务的恢复.在重启mysql服务的时候，根据redo log进行重做，从而使事务有持久性。

undo log：是存储引擎级别的log（InnoDB有，MyISAM没有）保证数据的原子性，该log保存了事务发生之前的数据的一个版本，可以用于回滚，是MVCC的重要实现方法之一。

bin log：数据库级别的log，关注恢复数据库的数据。

MySQL是如何保证主备一致的

MySQL通过bin log（二进制日志）实现主备一致。bin log记录了所有修改了数据库或可能修改数据库的语句，而不会记录select、show这种不会修改数据库的语句。

在备份的过程中，主库A会有一个专门的线程将主库A的bin log发送给 备库B进行备份。其中bin log有三种记录格式：

• statement:记录对数据库进行修改的语句本身，有可能会记录一些额外的相关信息。
  • 优点是binlog日志量少，IO压力小，性能较高。
  • 缺点是由于记录的信息相对较少，在不同库执行时由于上下文的环境不同可能导致主备不一致。
• row:记录对数据库做出修改的语句所影响到的数据行以及对这些行的修改。比如当修改涉及多行数据，会把涉及的每行数据都记录到bin log。
  • 优点是能够完全的还原或者复制日志被记录时的操作。
  • 缺点是日志量占用空间较大，IO压力大，性能消耗较大。
• mixed:混合使用上述两种模式，一般的语句使用statment方式进行保存，如果遇到一些特殊的函数，则使用row模式进行记录。MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式， 否则就用statement格式。但是在生产环境中，一般会使用row模式。

redo log与bin log的区别？

1. redo log是InnoDB引擎特有的，只记录该引擎中表的修改记录。bin log是MySQL的Server层实现的，会记录所有引擎对数据库的修改。
2. redo log是物理日志，记录的是在具体某个数据页上做了什么修改；bin log是逻辑日志，记录的是这个语句的原始逻辑。
3. redo log是循环写的，空间固定会用完；bin log是可以追加写入的，bin log文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

什么时候redo log会触发写磁盘

https://blog.csdn.net/wuzhenwei0419/article/details/105258902/

crash-safe能力是什么

InnoDB通过redo log保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

WAL技术是什么

WAL的全称是Write-Ahead Logging，它的关键点就是**先写日志，再写磁盘。**事务在提交写入磁盘前，会先写到redo log里面去。如果直接写入磁盘涉及磁盘的随机I/O访问，涉及磁盘随机I/O访问是非常消耗时间的一个过程，相比之下先写入redo log，后面再找合适的时机批量刷盘能提升性能。

两阶段提交是什么

为了保证binl og和redo log两份日志的逻辑一致，最终保证恢复到主备数据库的数据是一致的，采用两阶段提交的机制。

1. 执行器调用存储引擎接口，存储引擎将修改更新到内存中后，将修改操作记录redo log中，此时redo log处于prepare状态。
2. 存储引擎告知执行器执行完毕，执行器生成这个操作对应的bin log，并把binlog写入磁盘。
3. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交commit状态，更新完成。

只靠bin log可以支持数据库崩溃恢复吗

不可以。 历史原因：

1. InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。InnoDB接入了MySQL后，发现既然bin log没有崩溃恢复的能力，那引入InnoDB原有的redo log来保证崩溃恢复能力。 实现原因：
2. bin log没有记录数据页修改的详细信息，不具备恢复数据页的能力。binlog记录着数据行的增删改，但是不记录事务对数据页的改动，这样细致的改动只记录在redo log中。当一个事务做增删改时，其实涉及到的数据页改动非常细致和复杂，包括行的字段改动以及行头部以及数据页头部的改动，甚至b+tree会因为插入一行而发生若干次页面分裂，那么事务也会把所有这些改动记录下来到redo log中。因为数据库系统进程crash时刻，磁盘上面页面镜像可以非常混乱，其中有些页面含有一些正在运行着的事务的改动，而一些已提交的事务的改动并没有刷上磁盘。事务恢复过程可以理解为是要把没有提交的事务的页面改动都去掉，并把已经提交的事务的页面改动都加上去这样一个过程。这些信息，都是binlog中没有记录的，只记录在了存储引擎的redo log中。
3. 操作写入binlog可细分为write和fsync两个过程，write指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘,fsync才是将数据持久化到磁盘的操作。通过参数设置sync_binlog为0的时候，表示每次提交事务都只write，不fsync。此时数据库崩溃可能导致部分提交的事务以及binlog日志由于没有持久化而丢失。

简述MySQL主从复制

MySQL提供主从复制功能，可以方便的实现数据的多处自动备份，不仅能增加数据库的安全性，还能进行读写分离，提升数据库负载性能。

主从复制流程：

1. 在事务完成之前，主库在bin log上记录这些改变，完成bin log写入过程后，主库通知存储引擎提交事物
2. 从库将主库的bin log复制到对应的中继日志，即开辟一个I/O工作线程，I/O线程在主库上打开一个普通的连接，然后开始bin log dump process，将这些事件写入中继日志。从主库的bin log中读取事件，如果已经读到最新了，线程进入睡眠并等待ma主库产生新的事件。

读写分离：即只在MySQL主库上写，只在MySQL从库上读，以减少数据库压力，提高性能。

SQL truncate 、delete和drop的异同

https://blog.csdn.net/biglxl/article/details/73301965

相同点

1.truncate和不带where子句的delete、以及drop都会删除表内的数据。

2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

不同点

1.truncate 和 delete 只删除数据不删除表的结构(定义)
drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态（无效状态）。

2.delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger（触发器），执行的时候将被触发。

truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger（触发器）。
（rollback segment可以理解为是一个存储状态的区域，它相当于保存操作前数据库的状态信息，当操作执行成功后继续存储当前状态，当操作失败时回滚即恢复到操作之前保存的状态）

3.速度，一般来说: drop> truncate > delete
原因：
drop 删除了表的数据及表的结构即从数据库中删除该表
truncate 只删除数据
delete 有条件的删除表中数据（没有条件即没有where语句即删除表中所有数据）

数据模型描述

数据模型的组成要素有：

1**.数据结构**，描述数据库的组成对象以及对象之间的联系，数据结构是所描述的对象类型的集合，是对系统静态特征的描述

2.数据操作，是指对数据库中各种对象的实例允许执行的操作的集合，主要有查询和更新。

3.数据的完整性约束条件，是一组完整性规则的集合。完整性规则是给定的数据模型中数据及其联系所具有的之约和依存规则，用以限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效、相容。

异常

删除操作异常是指不该删除的数据被删除；

插入操作异常是指应该插入的数据未被插入；

Having条件

having和group by 区别

having是对于group by分组后结果进行筛选，group by是进行分组

where 子句和 having子句区别

where是对查询进行限制条件，having只是对于group by后的结果进行限制

所以用having就一定要和group by连用，且是先group by XXX 再having XXX，用group by不一有having（它只是一个筛选条件用的）

where后不可以加聚合函数，但是having可以添加聚合函数

都是实际的比预期操作的少3

select执行顺序

写法顺序：select--from--where--group by--having--order by

执行顺序：from--where--group by--having--select--order by 就是select要放后面，如果有order by，则order by放最后，因为order by 是对结果进行排序

用于调用存储过程的对象

CallableStatemet 用于调用存储过程的对象

Statement和PreparedStatement都是用来发送和执行SQL语句的

ResultSet是结果集对象

SQL注入和解决方法

什么是SQL注入？

SQL注入就是在系统登陆窗口或其他一切可输入文本中输入一段SQL语句，由于“SQL注入”是利用未过滤/未审核用户输入的攻击方法，其实就是让应用运行本不应该运行的SQL代码，如果应用毫无防备地创建了SQL字符串并且运行了它们，就会造成一些出人意料的结果。

简单的sql注入语句

String sql = "select * from user_table where username=

' "+userName+" ' and password=' "+password+" '";

改写成

SELECT * FROM user_table WHERE username=

'’or 1 = 1 -- and password='’

SQL注入解决方法
解决SQL注入问题的关键是对所有可能来自用户输入的数据进行严格的检查、对数据库配置使用最小权限原则。

1、所有的查询语句都使用数据库提供的参数化查询接口，参数化的语句使用参数而不是将用户输入变量嵌入到SQL语句中。当前几乎所有的数据库系统都提供了参数化SQL语句执行接口，使用此接口可以非常有效的防止SQL注入攻击。

​ MyBatis提供了两种支持动态 sql 的语法 #{} 和 ${}，其中${} 是简单的字符串替换，而 #{} 在预处理时，会把参数部分用一个占位符 ? 代替，可以有效的防止sql的注入

2、对进入数据库的特殊字符（’”\尖括号&*;等）进行转义处理，或编码转换。

3、严格限制变量类型，比如整型变量就采用intval()函数过滤，数据库中的存储字段必须对应为int型。

4、数据长度应该严格规定，能在一定程度上防止比较长的SQL注入语句无法正确执行。

5、网站每个数据层的编码统一，建议全部使用UTF-8编码，上下层编码不一致有可能导致一些过滤模型被绕过。

6、严格限制网站用户的数据库的操作权限，给此用户提供仅仅能够满足其工作的权限，从而最大限度的减少注入攻击对数据库的危害。

7、避免网站显示SQL错误信息，比如类型错误、字段不匹配等，防止攻击者利用这些错误信息进行一些判断。

8、在网站发布之前建议使用一些专业的SQL注入检测工具进行检测，及时修补这些SQL注入漏洞。

9、确认PHP配置文件中的magicquotesgpc选项保持开启

log日志有几个级别

https://blog.csdn.net/jYF_666/article/details/102776645

log4j定义了8个级别的log（除去OFF和ALL，可以说分为6个级别），优先级从高到低依次为：OFF、FATAL、ERROR、WARN、INFO、DEBUG、TRACE、 ALL。
Log4j建议只使用四个级别，优先级从高到低分别是 ERROR、WARN、INFO、DEBUG。

Mysql常用函数

https://www.bilibili.com/read/cv3838568/

1.聚合函数
聚合函数是平时比较常用的一类函数，这里列举如下：

COUNT(col) 统计查询结果的行数

MIN(col) 查询指定列的最小值

MAX(col) 查询指定列的最大值

SUM(col) 求和，返回指定列的总和

AVG(col) 求平均值，返回指定列数据的平均值

2.数值型函数
数值型函数主要是对数值型数据进行处理，得到我们想要的结果，常用的几个列举如下，具体使用方法大家可以试试看。

ABS(x) 返回x的绝对值

BIN(x) 返回x的二进制

CEILING(x) 返回大于x的最小整数值

EXP(x) 返回值e（自然对数的底）的x次方

FLOOR(x) 返回小于x的最大整数值

GREATEST(x1,x2,...,xn) 返回集合中最大的值

LEAST(x1,x2,...,xn) 返回集合中最小的值

LN(x) 返回x的自然对数

LOG(x,y) 返回x的以y为底的对数

MOD(x,y) 返回x/y的模（余数）