1.基础知识

端口号:3306
支持事务的引擎:InnoDB

2. MyISAM与InnoDB的区别

简单来说：

MyISAM是5.5版本之前的默认引擎，不支持事务；不支持行级锁；崩溃后无法安全恢复；适合读密集的场景；
InnoDB是5.5版本之后的默认引擎，支持事务；支持行级锁；崩溃后可以安全恢复(crash-safe)。

详细介绍

是否支持行级锁？
MyISAM只支持表级锁，InnoDB支持表级锁和行级锁，默认为行级锁；
是否支持事务和奔溃后的安全恢复？
MyISAM强调的是性能，每次查询都是原子操作，其执行速度比Innodb更快，但是不支持事务。
InnoDB支持事务和奔溃后的修复能力；
是否支持外键？
MyISAM不支持外键；InnoDB支持外键；
是否支持MVCC？
仅InnoDB支持。
MyISAM一定比InnoDB快吗？
不一定，某些使用了聚簇索引或者访问的数据都在内存中的时候，InnoDB比MyISAM要快。

3.事务

3.1ACID

原子性(A):事务执行的最小单位，不允许分割；要么全部执行，要么都不执行；
一致性(C):执行数据前后，数据保持一致；事务应确保数据库的状态从一个一致状态转变为另一个一致状态。
隔离性(I):并发访问数据库的时候，一个用户的事务不被其他务所干扰，各并发事务之间的数据库是独立的。
持久性(D):一个事务提交以后，对数据库的影响是持久的，即使数据库发生故障也不会对其有影响。

3.2 并发事务带来的问题

脏读(Dirty read )：一个事务读到了另外一个事务还没有提交的事务；
不可重复读(Unrepeatableread):一个事务内多次读取某个数据，但是另外一个事务修改了数据，导致第一个事务前后两次读到的数据不一致。

不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

幻读(Phantom read):T1读取了几行数据后，T2插入了几条数据，T1在后续的查询中就会发现多了原本不存在的数据，就好像发生了幻觉一样。

幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.一般解决幻读的方法是增加范围锁RangeS，锁定检锁范围为只读，这样就避免了幻读。

在并发访问情况下，可能会出现脏读、不可重复读和幻读等读现象，为了应对这些问题，主流数据库都提供了锁机制，并引入了事务隔离级别的概念。
数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

3.3 事务隔离级别

读未提交(Read-Uncommitted)
读已提交(Read-Committed)，避免脏读，无法免不可重复读和幻读
可重复读(Repeatable-Read)，避免脏读、不可重复读，无法避免幻读
可串行化(Serializable),避免脏读、不可重复读，和幻读。所有事务依次执行；
MySQL的隔离级别是可重复读，但是对于InnoDB引擎在可重复读的隔离级别下，使用的算法是Next-Key Lock锁算法，避免了幻读，且不会有任何的性能损失。

4.锁

当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。锁就是其中的一种机制。在计算机科学中，锁是在执行多线程时用于强行限制资源访问的同步机制，即用于在并发控制中保证对互斥的要求。

MyISAM采用为表级锁；
InnoDB支持表级锁和行级锁，默认为行级锁；

4.1表级锁与行级锁的对比：

表级锁：粒度最大的锁，实现简单，资源消耗比较小，不会出现死锁；但是并发度最低，不适合高并发场景。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
行级锁：粒度最小的锁，只对当前操作的行进行加锁，适合并发场景，降低冲突的概率；加锁开销大，加锁慢，容易出现死锁。行级锁分为共享锁和排他锁。

4.1.1行级锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

行级锁的算法有三种:

Record Lock:对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
Gap Lock：间隙锁，锁定一个范围，不包括记录本身；
Next-key Lock:行锁+间隙锁，包含记录本身，解决了幻读；

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。
有多种方法可以避免死锁，这里介绍常见的三种
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

4.1.3锁的小结

InnoDB查询使用的是Next-key Lock；
Next-Key Lock为了解决幻读的问题；
当查询的索引有唯一属性时，Next-key 降级为Record Key
GAP 锁的目的就是为了防止幻读，阻止多个事务将数据插入到同一范围内；

4.1.4 表级锁使用场景

事务更新大表中的大部分数据直接使用表级锁效率更高；
事务比较复杂，使用行级索很可能引起死锁导致回滚。

4.2 共享锁（s）和排他锁（X）-->行锁

行级锁可以进一步划分为共享锁（s）和排他锁（X）。

共享锁(Share Lock)

共享锁又称读锁，是读取操作创建的锁。其他事务可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
用法
SELECT ... LOCK IN SHARE MODE;
在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁（eXclusive Lock）

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。其它用户只能查询但不能更新被加锁的数据行。
用法
SELECT ... FOR UPDATE;
在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。
对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；对于一般的Select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。
共享锁：SELECT ... LOCK IN SHARE MODE;
排他锁：SELECT ... FOR UPDATE;

读锁和写锁--》表锁

读锁：共享锁
写锁：排它锁，排它锁，互斥锁
本文提到的读锁和写锁都是MySQL数据库的MyISAM引擎支持的表锁的。（这里打个小问号，InnoDb也是支持表锁的吧？）

MyISAM 存储引擎只支持表锁，MySQL 的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。

对于读操作，可以增加读锁，一旦数据表被加上读锁，其他请求可以对该表再次增加读锁，但是不能增加写锁。（当一个请求在读数据时，其他请求也可以读，但是不能写，因为一旦另外一个线程写了数据，就会导致当前线程读取到的数据不是最新的了。这就是不可重复读现象）

对于写操作，可以增加写锁，一旦数据表被加上写锁，其他请求无法在对该表增加读锁和写锁。（当一个请求在写数据时，其他请求不能执行任何操作，因为在当前事务提交之前，其他的请求无法看到本次修改的内容。否则就可能产生脏读、不可重复读和幻读）

读锁和写锁都是阻塞锁。
如果t1对数据表增加了写锁，这时t2请求对数据表增加写锁，这时候t2并不会直接返回，而是会一直处于阻塞状态，直到t1释放了对表的锁，这时t2便有可能加锁成功，获取到结果。

表锁的加锁/解锁方式
MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作 (UPDATE、DELETE、INSERT 等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

另外两个表级锁：IS和IX（InnoDB）

意向锁:当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以在需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

InnoDB另外的两个表级锁：
意向共享锁（IS）：表示事务准备给数据行记入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

注意：

这里的意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预。
IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。

4.3 悲观锁和乐观锁

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

乐观锁与悲观锁只是人们定义出来的概念。针对于不同的业务场景，应该选用不同的并发控制方式。在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

4.3.1 悲观锁

这种借助数据库锁机制在修改数据之前先锁定，再修改的方式被称之为悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）。
加锁流程:

在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。

如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

4.3.2乐观锁

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。
版本号的实现:

数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

//修改商品库存 
update item set quantity=quantity - 1 where id = 1 and quantity - 1 > 0

如何选择

在乐观锁与悲观锁的选择上面，主要看下两者的区别以及适用场景就可以了。
1、乐观锁并未真正加锁，效率高。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。
2、悲观锁依赖数据库锁，效率低。更新失败的概率比较低。
随着互联网三高架构（高并发、高性能、高可用）的提出，悲观锁已经越来越少的被使用到生产环境中了，尤其是并发量比较大的业务场景。

5.大表优化

限定数据的范围
务必禁止不带任何限制数据范围条件的查询语句；

5.1 读写分离

主库写，从库读

image.png

一主多从，读写分离，主动同步，是一种常见的数据库架构，一般来说：

主库，提供数据库写服务
从库，提供数据库读服务
主从之间，通过某种机制同步数据，例如mysql的binlog
本质上解决的问题:
大部分互联网业务读多写少，数据库的读往往最先成为性能瓶颈，从而可以线性提升数据库读性能；
通过消除读写锁冲突提升数据库写性能；

小结

一句话，主要解决“数据库读性能瓶颈”问题，在数据库扛不住读的时候，通常读写分离，通过增加从库线性提升系统读性能。

缺点

如果数据库读写分离：

数据库连接池需要区分：读连接池，写连接池
如果要保证读高可用，读连接池要实现故障自动转移
有潜在的主库从库一致性问题

优化建议

如果面临的是“读性能瓶颈”问题，增加缓存可能来得更直接，更容易一点
关于成本，从库的成本比缓存高不少

5.2垂直划分

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
优点：优化表的结构，易于维护；可以使列的数据变小，查询时候的block块减少，减少I/O数；
缺点：主键冗余；引起Join操作(应用层完成)；垂直分区让事务更加复杂；

5.3水平划分

image.png

水平切分，也是一种常见的数据库架构，一般来说：

每个数据库之间没有数据重合，没有类似binlog同步的关联；
所有数据并集，组成全部数据；
会用算法，来完成数据分割，例如“取模”，或者根据业务标识；

水平切分架构究竟解决什么问题？

大部分互联网业务数据量很大，单库容量容易成为瓶颈，如果希望：

线性降低单库数据容量
线性提升数据库写性能

小结:

一句话总结，水平切分主要解决“数据库数据量大”问题，在数据库容量扛不住的时候，通常水平切分。
一个水平切分集群中的每一个数据库，通常称为一个“分片”。

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

水平拆分能够支持非常大的数据量存储，拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。
数据库分片的两种常见方案：
客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。
中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

7.池化思想

核心：预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。
数据库连接本质就是一个 socket 的连接。在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。

10.一条SQL语句执行得很慢的原因有哪些？

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485185&idx=1&sn=66ef08b4ab6af5757792223a83fc0d45&chksm=cea248caf9d5c1dc72ec8a281ec16aa3ec3e8066dbb252e27362438a26c33fbe842b0e0adf47&token=79317275&lang=zh_CN#rd

11.全局锁

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

你一定在疑惑，有了这个功能，为什么还需要 FTWRL 呢？
single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。

12.表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

12.1 表锁

表锁的语法是lock tables … read/write。
与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。
需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。
（这个挺有意思的）

12.2 元数据锁

MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。
当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。
因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

面试题整理