MySQL

1. Mysql 使用的引擎

• InnoDB 存储引擎

1. 事务和外键
   InnoDB 存储引擎支持事务，支持四个标准的隔离级别，默认级别是可重复读。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
2. 索引
   InnoDB（索引组织表）使用的聚簇索引，索引就是数据，顺序存储，每张表的存储都是按主键的顺序进行存放的，因此能缓存索引，也能缓存数据，索引的数据结构使用的是 B+ 树。
3. 锁
   mysql支持三种锁定级别，行级、页级、表级。
   InnoDB支持行级锁。
4. 并发
   其特点是行锁设计，支持非锁定读，写不锁定全表，高效并发时效率高。对于表中数据的存储，不保存表的行数，执行 select count(*) from table 时要全表扫描。
5. 场景选择
   需要事务支持（具有较好的事务特性）
   行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
   数据更新较为频繁的场景
   数据一致性要求较高
   硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO

• MyISAM 存储引擎

6. 事务和外键
   MyISAM 不支持事务，也不支持外键。
7. 索引
   MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引，索引的数据结构使用的是 B+ 树。
8. 锁
   MyISAM支持表级锁，不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。
9. 并发
   MyISAM读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。
10. 场景选择
    不需要事务支持（不支持）
    并发相对较低（锁定机制问题）
    数据修改相对较少（阻塞问题），以读为主
    数据一致性要求不是非常高

• Memory 存储引擎
  Memory 存储引擎将表中的数据存放在内存中，非常适合存储临时数据的临时表，Memory 默认的存储引擎是哈希索引，而不是我们熟悉的 B+ 树。Memory 存储引擎的速度非常快，Mysql 使用 Memory 作为临时表来存放查询的中间结果。

• Merge 存储引擎
  Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

2. 聚集索引和非聚集索引的区别

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

1. 聚集索引表记录的排列顺序与索引的排列顺序一致

   • 优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。
   • 缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排， 降低了执行速度。
   • 建议使用聚集索引的场合为：
     a. 此列包含有限数目的不同值；
     b. 查询的结果返回一个区间的值；
     c. 查询的结果返回某值相同的大量结果集。

2. 非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致。聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。

3. 聚集索引确定表中数据的物理顺序。

4. 非聚集索引中，数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

5. 聚集索引的顺序就是数据的物理存储顺序，而非聚集索引的顺序和数据物理排列无关。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。在SQL SERVER中，索引是通过二叉树的数据结构来描述的；我们可以如此理解这个两种索引：聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。

3. Mysql 数据库索引的类型

Mysql 数据库索引的类型

1. 全文索引
2. 哈希索引
3. B+ 索引

4. Mysql 数据库索引种类

Mysql 数据库索引种类

1. 普通索引：仅加速查询
2. 唯一索引：加速查询 + 列值唯一
3. 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
4. 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
5. 全文索引：对文本的内容进行分词，进行搜索

5. 数据库事务的四大特性（ACID）

1. 原子性
   原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
2. 一致性
   一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
   拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
3. 隔离性
   隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
   即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
4. 持久性
   持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。使用重做日志来保证持久性。
   例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

数据库隔离级别

1. 未提交读
事务中的修改，即使没有提交，对其它事务也是可见的。
2. 提交读
一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
3. 可重复读
保证在同一个事务中多次读取同样数据的结果是一样的。
4. 可串行化
强制事务串行执行。

多版本并发控制

多版本并发控制（Multi-Version Concurrency Control，MVCC）是 MySQL 的 InnoDB 实现隔离级别的一种具体方法，用于实现提交读和可重复读两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

4. Mysql 联合索引

mysql联合索引
联合索引又叫复合索引，对于联合索引，Mysql 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

如：建立 姓名(name)、年龄(age)、性别(sex)的复合索引。
select * from user where name=’zhangsan’ and age = ‘1’; 使用到索引
select * from user where age=’1’ and sex=’男’;不使用索引

联合索引和单个索引的区别：
例：
联合索引和单个索引的区别：
如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了：
(area,age,salary)，(area,age)、(area)三个索引，这被称为最佳左前缀
特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
例：

select * from test where area='11'
select * from test where area='11' and age=1
select * from test where area='11' and age=1 and salary=2.0
以上有索引
select * from test where age=11
select * from test where age=1 and salary=2.0
以上无索引
-----------------------------------

如果在查询中需要匹配多个字段的条件，可以把这几个字段做个联合索引，效率要比在每个字段上加索引高多了

为什么 Mysql 的联合索引在某些情况下比单列索引效率高？

为什么

1. 由于索引文件以B+树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录!
2. 那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。

5. Mysql 的三大范式（关系数据库的几种设计范式介绍）

1. 第一范式（1NF）：强调列的原子性，列不能再分为其他几列，属性不可分。如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式。例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。
2. 第二范式（2NF）：在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关(一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的部分)。如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式。例如:订单表(订单编号、产品编号、定购日期、价格、……)，"订单编号"为主键，"产品编号"和主键列没有直接的关系，即"产品编号"列不依赖于主键列，应删除该列。
3. 第三范式（3NF）：在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键，不能存在传递依赖)。
   为了理解第三范式，需要根据Armstrong公里之一定义传递依赖。假设A、B和C是关系R的三个属性，如果A-〉B且B-〉C，则从这些函数依赖中，可以得出A-〉C，如上所述，依赖A-〉C是传递依赖。例如:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列"订单编号"相关，再细看你会发现"顾客姓名"和"顾客编号"相关，"顾客编号"和"订单编号"又相关，最后经过传递依赖，"顾客姓名"也和"订单编号"相关。为了满足第三范式，应去掉"顾客姓名"列，放入客户表中。

6. Mysql 存储过程

什么是存储过程？

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

7. 数据库视图

视图的基本概念及作用

1. 视图（子查询）：是从一个或多个表导出的虚拟表，其内容由查询定义。具有普通表的结构，但是不实现数据存储。视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。

2. 通常，视图是可以更新的（即，可以对它们使用 INSERT 、 UPDATE 和 DELETE ）。更新一个视图将更新其基表（可以回忆一下，视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。

3. 作用：
   （1）简化了操作，把经常使用的数据定义为视图。
   （2）安全性，用户只能查看和修改能看到的数据。
   （3）逻辑上独立，屏蔽了真实表的结构带来的影响。

8. 数据库分库分表

分库分表

数据切分

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。

数据库分布式的核心内容无非就是数据切分（sharding），以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的。数据切分根据其切分类型，可以分为两种方式：垂直（纵向）切分和水平（横向）切分。

纵向切分

垂直切分常见有垂直分库和垂直分表两种。

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。如图：

垂直分表是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

垂直切分的优点：

• 解决业务系统层面的耦合，业务清晰
• 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
• 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

• 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
• 分布式事务处理复杂
• 依然存在单表数据量过大的问题（需要水平切分）

水平切分

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据数据库内在的逻辑关系，将同一个表按不同条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。如图所示：

库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。

水平切分的优点：

• 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
• 应用端改造较小，不需要拆分业务模块

缺点：

• 跨分片的事务一致性难以保证
• 跨库的join关联查询性能较差
• 数据多次扩展难度和维护量极大

8. SQL 哪些情况会引起全表扫描

模糊查询

原因1：
like本身效率就比较低，应该尽量避免查询条件使用like。
对于like ‘%…%’（全模糊）这样的条件，是无法使用索引的，全表扫描自然效率很低
由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低

解决办法：
首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的；左模糊like‘%…’无法直接使用索引

原因2:
or 语句使用不当会引起全表扫描
where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。

原因3:
查询条件中使用了不等于操作符（<>、!=）的select语句执行慢
SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引

原因4：
查询条件中含有is null的select语句执行慢，如： select id from t where num is null
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列 就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

原因5:
in 和 not in 也要慎用，否则会导致全表扫描

9. MySQL 集群的主从复制

主要涉及三个线程：binlog 线程、I/O 线程 和 SQL 线程

• binlog 线程：负责将主服务器上的数据更改写入二进制日志（binary log）中
• I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）
• SQL 线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）

10. 读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
读写分离能提高性能的原因在于：

• 主从服务器负责各自的读和写，极大程度缓解了锁的争用
• 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销
• 增加冗余，提高可用性

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器

11. Redis 为什么查询数据这么快？

1. 完全基于内存，绝大部分请求是纯粹的内存操作，非常快速。数据存在内存中，类似于HashMap，HashMap的优势就是查找和操作的时间复杂度都是O(1)
2. 数据结构简单，对数据操作也简单，Redis中的数据结构是专门进行设计的
3. 采用单线程，避免了不必要的上下文切换和竞争条件，也不存在多进程或者多线程导致的切换而消耗 CPU，不用去考虑各种锁的问题，不存在加锁释放锁操作，没有因为可能出现死锁而导致的性能消耗
4. 使用多路I/O复用模型，单个线程通过记录跟踪每一个Sock(I/O流)的状态来同时管理多个I/O流

12. Mysql 索引优化

1. 独立的列
   在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引
2. 多列索引
   在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好
3. 让选择性最强的索引列放在前面
4. 覆盖索引
   索引包含所有需要查询的字段的值，就是 select 的数据列只用从索引中就能够取得，不必从数据表中读取。
   不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

13. Mysql 查询性能的优化

1. 使用 Explain 进行分析
   Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。
   比较重要的字段有：

• select_type : 查询类型，有简单查询、联合查询、子查询等
• key : 使用的索引
• rows : 扫描的行数

2. 优化数据访问

   1. 减少请求的数据量
      只返回必要的列：最好不要使用 SELECT * 语句。
      只返回必要的行：使用 LIMIT 语句来限制返回的数据。
      缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查 询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。
   2. 减少服务器端扫描的行数
      最有效的方式是使用索引来覆盖查询。

3. 分解大连接查询
   如分解成多个单表查询

13. MySQL 锁机制

一、乐观锁 && 悲观锁

2. 乐观锁

---

乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突。所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回错误信息，让用户决定如何去做。

2.1 在数据库中的实现

利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此 version 值+1。当我们提交更新的时候，使用数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

3. 悲观锁

---

悲观锁(Pessimistic Locking)，悲观锁是指在数据处理过程，使数据处于锁定状态，一般使用数据库的锁机制实现。

悲观锁并不是适用于任何场景，它也存在一些不足，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受，这时就需要乐观锁。

二、Mysql 的事务

2.1 事务概述

数据库事务是数据库执行过程中的一个逻辑单位，一个事务通常包含了对数据库的读/写操作。它的存在包含有以下两个目的：

• 为数据库操作序列提供了一个回滚的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
• 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

2.2 事务的特性：ACID

• 原子性 aotmic
  事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
• 一致性 consistent
  事务在完成时，必须使所有的数据都保持一致状态。
• 隔离性 isolaton
  由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据
• 持久性 duration
  事务完成后，它对系统的影响是永久性的

三、排他锁 & 共享锁

2.1 共享锁（shared locks，s锁）

共享锁又叫读锁，如果事务 T1 对行 R 加上 S 锁，则

• 其它事务T2/T3/Tn只能对行R再加S锁，不能加其它锁
• 获得S锁的事务只能读数据，不能写数据（你傻呀，当然也不能删咯）

2.2 排他锁（exclusive locks，x锁）

排它锁又叫写锁，如果事务T1对行R加上X锁，则

• 其它事务T2/T3/Tn都不能对行R加任何类型的锁，直到T1事务在行R上的X锁释放
• 获得X锁的事务既能读数据，又能写数据

四、意向锁

举个例子：
事务A锁住了表中的一行，让这一行只能读，不能写。
之后，事务B申请整个表的写锁。
如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。
数据库为了避免这种冲突，就是说要让B的申请被阻塞，直到 A 释放行锁。

数据库怎么判断这个冲突呢？
step1：判断表是否已被其他事务用表锁锁表
step2：判断表中的每一行是否已被行锁锁住

注意step2中通过遍历查询，这样的判断方法效率实在不高，因为需要遍历整个表，于是就有了意向锁。

在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

在意向锁存在的情况下，上面的判断可以改成

step1：不变

step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

1. 意向锁分为意向读锁(IS)和意向写锁(IX)
2. 意向锁是表级锁，但是却表示事务正在读或写某一行记录，而不是整个表
3. 在给一行记录加锁前，首先要给该表加意向锁，也就是要同时加表意向锁和行锁

五、间隙锁

间隙锁

1. 什么是间隙锁？
   当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(NEXT-KEY)锁。

2. 间隙锁的作用
   保证某个间隙内的数据在锁定情况下不会发生任何变化。