作者简介和专栏内容见专栏介绍：https://www.nowcoder.com/creation/manager/columnDetail/0eL5bM

麻烦看到贴子的伙伴点点赞大家点赞订阅支持下，提前祝各位offer多多，有问题评论区见~~

图解版

文字版

MySql

关系型和非关系型数据库

先讲定义

关系型数据库是指使用关系模型（二维表格模型）来组织数据的数据库，而非关系型数据库则不适合存储在数据表的行和列中，而是大块组合在一起，通常存储在数据集中，就像文档、键值对或者图结构。

• 非关系型数据库也叫NOSQL，采用键值对的形式进行存储。 它的读写性能很高，易于扩展，可分为内存性数据库以及文档型数据库，比如 Redis，Mongodb，HBase等等。
• 适合使用非关系型数据库的场景：
• 关系型数据库的优点
• 非关系型数据库的优点

说一说Drop、Delete与Truncate的共同点和区别

Drop、Delete、Truncate都表示删除，但是三者有一些差别：

• Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚 (rollback)来执行删除或者撤销删除，会触发这个表上所有的delete触发器。
• Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小。
• Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在不再需要一张表的时候，用Drop；在想删除部分数据行时候，用Delete；在保留表而删除所有

数据的时候用Truncate。

各个关键字的执行顺序

• where：在执行其他操作之前，先对数据进行过滤，只保留符合条件的记录。
• join：在过滤后的数据中，根据指定的连接条件，将不同的表进行关联，形成一个临时的结果集。
• groupby：在连接后的结果集中，根据指定的字段或表达式，将数据分成若干组，每组包含相同的值。
• select：在分组后的结果集中，选择需要显示的字段或表达式，如果有聚合函数（如sum,count等），则对每组数据进行计算。
• having：在选择后的结果集中，对分组后的数据进行再次过滤，只保留符合条件的组。
• orderby：在过滤后的结果集中，根据指定的字段或表达式，对数据进行排序，可以指定升序或降序。
• delete或update：在排序后的结果集中，根据指定的条件，删除或修改相应的记录。

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

• 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。
• 增加数据库索引，进而提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
• 主从读写分离，让主服务器负责写，从服务器负责读。
• 将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。
• 使用分布式架构，分散计算压力。

悲观锁和乐观锁的原理和应用场景分别有什么？

悲观锁，先获取锁，再进行业务操作，一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。

当数据库执行SELECT … FOR UPDATE时会获取被select中的数据行的行锁，select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

乐观锁，先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过。Java 并发包中的AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。

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

表锁

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

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

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

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

MyISAM不适合做写为主表的引擎，因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

行锁

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

在MySQL的InnoDB引擎支持行锁，与Oracle不同，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。

行锁的实现需要注意：

• 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
• 两个事务不能锁同一个索引。
• insert，delete，update在事务中都会自动默认加上排它锁。

行锁的适用场景：

A用户消费，service层先查询该用户的账户余额，若余额足够，则进行后续的扣款操作；这种情况查询的时候应该对该记录进行加锁。

否则，B用户在A用户查询后消费前先一步将A用户账号上的钱转走，而此时A用户已经进行了用户余额是否足够的判断，则可能会出现余额已经不足但却扣款成功的情况。为了避免此情况，需要在A用户操作该记录的时候进行for update加锁

全局锁：

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

• 对数据的增删改操作，比如 insert、delete、update等语句；
• 对表结构的更改操作，比如 alter table、drop table 等语句。

Record Lock

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的

Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

update没加索引会怎么样？

有一点要注意的是，在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

mysql死锁怎么办？

为什么产生死锁

可重复读隔离级别下，是存在幻读的问题。

mysql的存储

• MySQL 的表数据是以页的形式存放的，页在磁盘中不一定是连续的。
• 页的空间是 16K, 并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等。
• 在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。
• 索引结构不会影响单表最大行数，2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

mysql日志

更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志） 、binlog （归档日志）这三种日志：

为什么需要undo log（回滚日志）

是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。

• 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
• 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。
• binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

为什么需要 Buffer Pool？

有了 Buffer Poo 后：

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
• 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

为什么需要redo log（重做日志）

是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；

Buffer Pool 是提高了读写效率没错，但是问题来了，Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。

为什么需要 binlog ？

前面介绍的 undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

主从复制如何实现

MySQL 集群的主从复制过程梳理成 3 个阶段：

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
• 回放 Binlog：回放 binlog，并更新存储引擎中的数据。

事务

事务四大特性（ACID）原子性、一致性、隔离性、持久性？

我们以从A账户转账50元到B账户为例进行说明一下ACID这四大特性。

原子性（Atomicity）

• 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
• 如果无法保证原子性会怎么样？

OK，就会出现数据不一致的情形，A账户减去50元，而B账户增加50元操作失败。系统将无故丢失50元~

一致性（Consistency）

• 事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
• 如果无法保证一致性会怎么样？

例一:A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现了此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须大于0。

例二:A账户200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道此时数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的余额必须不变。

隔离性（Isolation）

• 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不 能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

持久性（Durability）

• 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
• 如果无法保证持久性会怎么样？

在MySQL中，为了解决CPU和磁盘速度不一致问题，MySQL是将磁盘上的数据加载到内存，对内存进行操作，然后再回写磁盘。好，假设此时宕机了，在内存中修改的数据全部丢失了，持久性就无法保证。

设想一下，系统提示你转账成功。但是你发现金额没有发生任何改变，此时数据出现了不合法的数据状态，我们将这种状态认为是数据不一致的情形。

数据库如何保证一致性、原子性、持久性

• 一致性

分为两个层面来说。

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。

从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

• 原子性

主要是利用 Innodb 的undo log。 undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销

所有已经成功执行的 SQL语句，他需要记录你要回滚的相应日志信息。 例如

• 当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
• 当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
• 当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

• 持久性

主要是利用Innodb的redo log。重写日志，正如之前说的，MySQL是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再写回到磁盘上。如果此时突然宕机，内存中的数据就会丢失。 怎么解决这个问题？

简单啊，事务提交前直接把数据写入磁盘就行啊。 这么做有什么问题？

• 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
• 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog决定回滚数据还是提交数据。

采用redo log的好处？

其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下：

• redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
• redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的

MySQL中为什么要有事务回滚机制

在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个 回滚日志中，然后在对数据库中的对应行进行写入。 当事务已经被提交之后，就无法再次回滚了。

回滚日志作用：

1)能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息

2) 在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。

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

脏读：在第一个修改事务和读取事务进行的时候，读取事务读到的数据为100，这是修改之后的数据，但是之后该事务满足一致性等特性而做了回滚操作，那么读取事务得到的结果就是脏数据了。

不可重复读：T2 读取一个数据，然后T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

幻读：一般是T1在某个范围内进行修改操作（增加或者删除），而T2读取该范围导致读到的数据是修改之间的了，强调范围。

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

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记 录就变为了5条，这样就导致了幻读。

丢弃修改：两个写事务T1 T2同时对A=0进行递增操作，结果T2覆盖T1，导致最终结果是1 而不是2，事务被覆盖。

数据库隔离级别

• 未提交读，事务中发生了修改，即使没有提交，其他事务也是可见的，比如对于一个数A原来50修改 为100，但是我还没有提交修改，另一个事务看到这个修改，而这个时候原事务发生了回滚，这时候A还是50，但是另一个事务看到的A是100.可能会导致脏读、幻读或不可重复读
• 提交读，对于一个事务从开始直到提交之前，所做的任何修改是其他事务不可见的，举例就是对于一个数A原来是50，然后提交修改成100，这个时候另一个事务在A提交修改之前，读取的A是50，刚读取完，A就被修改成100，这个时候另一个事务再进行读取发现A就突然变成100了；可以阻止脏读，但是幻读或不可重复读仍有可能发生
• 重复读，就是对一个记录读取多次的记录是相同的，比如对于一个数A读取的话一直是A，前后两次读取的A是一致的；可以阻止脏读和不可重复读，但幻读仍有可能发生
• 可串行化读，在并发情况下，和串行化的读取的结果是一致的，没有什么不同，比如不会发生脏读和幻读；该级别可以防止脏读、不可重复读以及幻读

这四种隔离级别具体是如何实现的呢？

• 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
• 对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
• 对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

MySQL 是怎么解决幻读的？

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了，详见这篇文章(opens new window)），解决的方案有两种：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

MVCC是多版本并发控制 Multi-Version Concurrent Contrl。

它是MySQL中的提高性能的一种方式，配合Undo log 和版本链，替代锁，让不同事物的读-写、写-读操作可以并发的执行，从而提升系统的性能。

MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能。一般是在使用读已提交（PEAD COMMITTED）和可重复读（REPEATABLE READ）隔离级别的事务中实现。

用自己的话说就是：

多版本意思是指数据库中一条数据有多个版本同时存在，在某个事务对其进行具体操作的时候，是需要查看这一条记录的隐藏列事务版本的id，比对事务id并根据事物的隔离级别从而去判断是哪个版本的数据。

准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。

MySQL优化

• 为搜索字段创建索引
• 避免使用 Select *，列出需要查询的字段
• 垂直分割分表
• 选择正确的存储引擎

数据库优化的思路

个我借鉴了慕课上关于数据库优化的课程。

1.SQL语句优化

• 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0liueleven 的评论：不是非我杠精，关于null,isNull,isNotNull其实是要看成本的，是否回表等因素总和考虑，才会决定是要走索引还是走全表扫描。也给大家找了一个作者的博文（MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！），仅供参考！！！[zhiyong0804d的意见]之所以未把第二条删除还是考虑可能很多人都被误导了。那这样的组织能让大家兼听则明。
• 很多时候用 exists 代替 in 是一个好的选择。
• 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤。

2.索引优化

看上文索引

3.数据库结构优化

• 范式优化： 比如消除冗余（节省空间。。）
• 反范式优化：比如适当加冗余等（减少join）
• 拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
• 拆分其实又分垂直拆分和水平拆分：案例： 简单购物系统暂设涉及如下表：1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势）3.用户表 （数据量100w，且有增长趋势）以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分：解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺方案：用户表 通过性别拆分为男用户表和女用户表，订单表 通过已完成和完成中拆分为已完成订单和未完成订单，产品表 未完成订单放一个server上，已完成订单表盒男用户表放一个server上，女用户表放一个server上(女的爱购物 哈哈)。

4.服务器硬件优化

这个么多花钱咯！

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

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

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

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

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

拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

数据库优化中有一个比较常用的手段就是把数据表进行拆分，关于拆分数据表你了解哪些？

数据表垂直拆分是指将一个数据表按照列（字段）进行划分，将不同的列存储在不同的数据库或者服务器上。这样可以减少数据冗余，提高查询效率，降低IO消耗1。

数据表水平拆分是指将一个数据表按照行进行划分，将不同的行存储在不同的数据库或者服务器上。这样可以解决单机存储容量和并发访问压力的问题，提高系统可用性和稳定性1。

拆分其实又分垂直拆分和水平拆分

案例：

简单购物系统暂设涉及如下表：

1.产品表（数据量10w，稳定）

2.订单表（数据量200w，且有增长趋势）

3.用户表 （数据量100w，且有增长趋势）

以 MySQL 为例讲述下水平拆分和垂直拆分，MySQL能容忍的数量级在百万静态数据可以到千万

垂直拆分

解决问题：表与表之间的io竞争

不解决问题：单表中数据量增长出现的压力

方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上

水平拆分

解决问题：单表中数据量增长出现的压力

不解决问题：表与表之间的io争夺

方案：用户表 通过性别拆分为男用户表和女用户表，订单表 通过已完成和完成中拆分为已完成订单和

未完成订单，产品表 未完成订单放一个server上，已完成订单表盒男用户表放一个server上，女用户表放

一个server上(女的爱购物 哈哈)

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

• 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
• 选择合适的表字段数据类型和存储引擎，适当的添加索引。
• MySQL库主从读写分离。
• 找规律分表，减少单表中的数据量提高查询速度。
• 添加缓存机制，比如Memcached，Apc等。
• 不经常改动的页面，生成静态页面。
• 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。

数据库为什么要进行分库和分表

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

通过分表，可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同的表上的数据量少

了，起到提高查询性能，缩短查询时间的作用，此外，可以很大的缓解表锁的问题。

分表策略可以归纳为垂直拆分和水平拆分：

水平分表：取模分表就属于随机分表，而时间维度分表则属于连续分表。

如何设计好垂直拆分，我的建议：将不常用的字段单独拆分到另外一张扩展表. 将大文本的字段单独拆

分到另外一张扩展表, 将不经常修改的字段放在同一张表中，将经常改变的字段放在另一张表中。

对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。

库内分表，仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并 不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘 IO、网络带宽等。

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

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

个分表中。

分页与排序问题----需要在不同的分表中将数据进行排序并返回，并将不同分表返回的结果集进行汇总

和再次排序，最后再返回给用户。

MySQL内部构造+

MySQL是如何执行一条SQL的？

先讲架构，再讲过程

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层，

• Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
• 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB

过程如下：

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行 SQL：执行 SQL 共有三个阶段：

索引

先讲什么是索引，再讲为什么要有索引，什么时候需要索引，创建和使用时注意什么，讲索引的结构，一般采用B+树，典型的代表Innodb。

mysql索引是一种对数据库表中的一列或多列的值进行排序和存储的结构，形象的说就是索引是数据的目录。通过将无序的数据变成相对有序的数据（就像查有目的一样），帮助mysql高效地获取数据

为什么使用索引？

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 帮助服务器避免排序和临时表
• 将随机IO变为顺序IO。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

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

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

创建索引时需要注意什么？

设置非空字段：应该指定列为NOT NULL，除非你想存储NULL。在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；

取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；

索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

唯一、不为空、经常被查询的字段 的字段适合建索引

索引使用的注意事项

MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，在索引的使用过程中，存在一些使用细节和注意事项。

• 在经常需要搜索的列上，可以加快搜索的速度；
• 在经常使用在where子句中的列上面创建索引，加快条件的判断速度。
• 将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
• 避免where子句中对字段施加函数，这会造成无法命中索引
• 在中到大型表索引都是非常有效的，但是特大型表的维护开销会很大，不适合建索引，建立用逻辑索 引
• 在经常用到连续的列上，这些列主要是由一些外键，可以加快连接的速度
• 与业务无关时多使用逻辑主键，也就是自增主键在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
• 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗
• 在使用limit offset查询缓存时，可以借助索引来提高性能

索引失效场景

今天给大家介绍了 6 种会发生索引失效的情况：

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列使用函数，就会导致索引失效。
• 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
• MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

MySQL索引主要使用的两种数据结构是什么？

哈希索引，对于哈希索引来说，底层的数据结构肯定是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引

BTree索引，Mysql的BTree索引使用的是B树中的B+Tree，BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。

但对于主要的两种存储引擎（MyISAM和InnoDB）的实现方式是不同的。

数据库索引采用B+树而不是B树/红黑树/哈希表的原因

回顾数据库使用B+树的由来，存储在磁盘，为了减少IO操作，

• 用数组+二分来实现线性排序的数据虽然简单好用，但是插入新元素的时候性能太低。
• 二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，这样我们在查询数据时，不需要计算中间节点的位置了，只需将查找的数据与节点的数据进行比较。插入也很方便
• 为了解决二叉查找树会在极端情况下退化成链表的问题，后面就有人提出平衡二叉查找树（AVL 树）。
• 自平衡二叉树虽然能保持查询操作的时间复杂度在O(logn)，但是因为它本质上是一个二叉树，每个节点只能有 2 个子节点，那么当节点个数越多的时候，树的高度也会相应变高，这样就会增加磁盘的 I/O 次数，从而影响数据查询的效率。所以引入B树。
• B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

最后总结出下边的结论，

• 主要原因：方便扫库。 B+树只要遍历叶子节点就可以实现整棵树的遍历，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。这是数据库选用B+树的最主要原因。
• 文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适。数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于