mysql

一、关系型数据库和非关系型数据库

1. 关系型数据库

关系型数据库是一种建立在关系模型的基础上的数据库。

优点如下：

• 容易理解，二维表的结构非常贴近现实世界

• 支持事务的 ACID 特性，可以保证数据的一致性

• 由于以标准为前提，数据更新的开销小

• 支持复杂查询（带where子句的查询）

缺点如下：

• 海量数据处理弱和读写性能差，特别是对于海量数据的高效率读写

• 数据模型死板，存储数据后结构难以修改

• 事务机制也会影响系统整体性能

2. 非关系型数据库

优点如下：

• 不需要经过SQL层的解析，读写效率高

• 基于键值对，数据的扩展性很好

• 可以支持多种类型数据的存储，如图片，文档等等

缺点如下：

• 缺乏标准话，不同的NoSQL数据库采用不同的数据模型和查询语言

• 难以实现事务的强一致性，数据完整性难以保证

• 技术成熟度较低

非关系型数据库可分为内存型数据库和文档型数据库。适合场景：

• 日志系统

• 地理位置存储

• 数据量巨大

• 高可用

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

MySQL内部构造可以分为服务器和存储引擎：

服务层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认的存储引擎。

执行 SQL 的步骤为：

1. 客户端请求 ->

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

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

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

5. 优化器（对执行的 SQL 优化选择最优的执行方案方法） ->

6. 执行器（执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错） ->

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

三、InnoDB 自增 id 作为主键

1. 为什么使用自增 id 作为主键

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面。

2. 自增 id 达到最大值了，用完了怎么办？

一旦自增 id 达到最大值，此时数据继续插入是会报一个主键冲突异常。

MySQL 使用 Int 整型存储主键，4 个字节，范围是（2^31 - 1）。当一个表数据量达到这么大的时候，应该考虑分库分表，一旦分库分表了，就不能依赖于每个表的自增ID来全局唯一标识这些数据了。此时，就需要提供一个全局唯一的 ID 号生成策略来支持分库分表的环境。因此在实际中，根本等不到自增主键用完到情形！

四、SQL 语法

1. Delete、Truncate 和 Drop

（1） 方法介绍

Delete

用来删除表的全部或者一部分数据行，执行 Delete 之后，用户需要提交（commmit）或者回滚（rollback）来执行删除或者撤销删除，会触发这个表上所有的 Delete 触发器。

Truncate

删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，Truncate 比 Delete 更快，占用的空间更小。

Drop

从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的 DML 触发器也不会被触发，这个命令也不能回滚。

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

速度：Drop > Truncate > Delete

（2）具体解析

• DELETE 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

• 表和索引所占空间。当表被 TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE 操作不会减少表或索引所占用的空间。DROP 语句将表所占用的空间全释放掉。

• TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

• TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

2. SQL中的NOW()和CURRENT_DATE()两个函数有什么区别

• NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒

• CURRENT_DATE（）仅显示当前年份，月份和日期。

3. MySQL中CHAR和VARCHAR的区别

• char 的长度是不可变的，用空格填充到指定长度大小，而 varchar 的长度是可变的。

• char 的存取数度还是要比 varchar 要快得多

• char 的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用2个字节。 varchar 的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

五、MySQL 优化

• 为搜索字段创建索引

• 避免使用 Select *，列出需要查询的字段

• 垂直分割分表

• 选择正确的存储引擎

1. 数据库结构优化的手段

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

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

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

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

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

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

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

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

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

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

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

六、视图

1. 视图的作用是什么？可以更改吗？

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的 sql 操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。

视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有 order by 则对视图再次 order by将被覆盖。

创建视图：create view xxx as xxxx

对于某些视图比如未使用联结子查询分组聚集函数 Distinct Union 等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

2. 听说过视图吗？那游标呢？

视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能。游标是对查询出来的结果集作为一个单元来有效的处理。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

七、InnoDB 与 MyISAM

1. InnoDB

• MySQL 默认的事务型存储引擎，只有需要它不支持的特性时，才考虑使用其它存储引擎

• 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+间隙锁（Next-Key Locking）防止幻读。

• 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

• 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读，能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

• 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

2. MyISAM

• 设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

• 提供了大量的特性，包括压缩表、空间数据索引等。

• 不支持事务。

• 不支持行级锁，只能对整张表加锁。读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

3. InnoDB 和 MyISAM 的区别

（1）是否支持行级锁

MyISAM 只有表级锁（table-level locking），而 InnoDB 支持行级锁（row-level locking）和表级锁，默认为行级锁。

（2）是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交（commit）和回滚（rollback）事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

（3）是否支持外键

MyISAM 不支持，而 InnoDB 支持。

通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

（4）是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

（5）索引实现不一样

虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

• MyISAM 的索引文件和数据文件是分离的。B+Tree 叶节点的 data 域存放的是数据记录的地址，在索引检索的时候，首先找到对应 key 的 data 域的值（地址），再去根据这个地址去文件中读取相应的数据记录，这被称为“非聚簇索引”。

• InnoDB 其数据文件本身就是索引文件。树的节点 data 域保存了完整的数据记录。这个索引的key是数据表的主键，因此 InnoDB 表数据文件本身就是主索引，这被称为“聚簇索引”。

在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。

（6）性能有差别

InnoDB 的性能比 MyISAM 更强大，不管是在读写混合模式下还是只读模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发，它的处理能力跟核数没关系。

4. MyISAM 和 InnoDB 如何选择

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点。

《MySQL 高性能》上面有一句话这样写到:

不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。

因此，对于咱们日常开发的业务系统来说，你几乎找不到什么理由再使用 MyISAM 作为自己的 MySQL 数据库的存储引擎。

5.总结

• InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。

• MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。

• MyISAM 不支持外键，而 InnoDB 支持。

• MyISAM 不支持 MVCC，而 InnoDB 支持。

• 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。

• InnoDB 的性能比 MyISAM 更强大。

八、数据库为什么要进行分库分表呢？

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

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

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

水平分表：取模分表就属于随机分表，而时间维度分表则属于连续分表。对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。

垂直分表：① 将不常用的字段单独拆分到另外一张扩展表；② 将大文本的字段单独拆分到另外一张扩展表；③ 将不经常修改的字段放在同一张表中；④ 将经常改变的字段放在另一张表中。

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

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

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

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

九、场景题：MySQL一天五万条以上的增量，预计运维三年，你有哪些优化手段？

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

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

• MySQL 库主从读写分离。

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

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

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

• 书写高效率的 SQL。① 对应同一列进行 or 判断时，使用 in 代替 or；② WHERE 从句中禁止对列进行函数转换和计算；③ 拆分复杂的大 SQL 为多个小 SQL

十、数据库基础知识

1. 数据库三大范式

• 第一范式（1NF）：字段不可分。原子性， 字段不可再分，否则就不是关系数据库。

• 第二范式（2NF）：有主键，非主键字段依赖主键。唯一性 ，一个表只说明一个事物。

• 第三范式（3NF）：非主键字段不能相互依赖。每列都与主键有直接关系，不存在传递依赖。

2. 关系型数据库的四大特性

ACID，原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

（1）原子性

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成。

（2）一致性

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

（3）隔离性

当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

（4）持久性

一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。遇到故障的情况下也不会丢失提交事务的操作。

3. 数据库如何保证一致性的

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

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

4. 数据库如何保证原子性的

主要是利用 InnoDB 的 undo log。 undo log 名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的 SQL语句，他需要记录你要回滚的相应日志信息。 例如：

• 当你 delete 一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert 这条旧数据

• 当你 update 一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行 update 操作

• 当年 insert 一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行 delete 操作

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

5. 数据库如何保证持久性的

主要是利用 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来的快。

6. SQL 语法中的内连接、自连接、外连接（左、右、全）、交叉连接的区别

• 内连接：只有两个元素表相匹配的才能在结果集中显示。

• 左外连接：左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。

• 右外连接：右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。

• 全外连接：连接的表中不匹配的数据全部会显示出来。

• 交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。

7. 数据库中的主键、超键、候选键、外键是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。(学号，性别)，(学号，年龄)

• 候选键：不含有多余属性的超键称为候选键。(学号)，(身份证号)

• 主键：用户选作元组标识的一个候选键程序主键。(学号)

• 外键：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

十一、索引

1. 为什么使用索引？

• 创建索引最主要的原因就是提高检索速度。

  每次在磁盘读取数据靠的是机械运动，寻道时间、旋转延迟、传输时间三个部分，是非常慢的。那么如何提高呢：① 计算机操作系统做一些优化，当一次 IO 的时候，把相邻的数据也读到内存中；② 每次查找数据时把磁盘 IO 次数控制在一个很小的数量级

• 帮助服务器避免排序和临时表。

  使用 order by 语句实现返回值是有序的，而 order by 是使用到了临时表的，会带来时间和空间损失。其实使用联合索引，就可以避免临时表的排序操作。

  alter table t add index city_user(city,name);

  查询流程变为： 1、从索引（city,name）找到第一个满足 city = '杭州' 条件的主键 id; 2、到主键 id 索引取出整行，取 name、city、age 三个字段值，作为结果集的一部分直接返回 3、从索引（city,name）取下一个记录主键 id； 4、重复 step2、3 直到查到第 1000 条记录，或者不满足 city = '杭州' 条件时循环结束。

  可以使用覆盖索引继续优化查询的执行流程： 覆盖索引指，索引上的信息足够满足查询请求，不需要再回到主键索引上取数据。

  alter table t add index city_user_age(city,name,age);

  1、从索引（city,name,age）找到第一个满足 city = '杭州' 条件的记录，取出其中的 city、name 和 age 三个字段值，作为结果集的一部分直接返回 2、从索引（city,name,age）取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回 3、重复步骤2，直到查到第 1000 条记录，或者是不满足 city = '杭州' 条件时循环结束。

• 将随机 IO 变为顺序 IO

• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

2. 什么时候建立数据库索引

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

不宜建立索引的情况：

① 对于查询中很少涉及的列或者重复值比较多的列不宜建立索引；

② 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等。

创建索引需要注意什么？

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

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

索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。 唯一、不为空、经常被查询的字段 的字段适合建索引

3. 索引的优缺点

优点：

• 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。

• 索引需要使用物理文件存储，也会耗费一定空间。

4. 数据库索引为什么使用B+树

B+ 树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而 B 树只能中序遍历所有节点，效率太低。

文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此 B+ 树相比 B 树更为合适。数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，而红黑树这种结构，高度明显要深的多，并且由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性。

红黑树：最长子树不超过最短子树的2倍即可

性质1：根结点是黑色的

性质2：每个红色结点的两个子节点都是黑色的

性质3：从任一结点到每个叶子结点的所有路径包含相同数目的黑色结点

（1）B 树& B+树两者有何异同

• B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。B 树的所有节点都存放 key 和 data

• B 树的叶子节点都是独立的；B+ 树的叶子节点有一条引用链指向与它相邻的叶子节点。

• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+ 树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

（2）B+ 树相比 B 树的优势

• B+ 树方便扫库：B 树必须用中序遍历的方法按序扫库，而 B+ 树直接从叶子结点挨个扫一遍就完了，B+ 树范围查询非常方便，而 B 树不支持，这是数据库选用 B+ 树的最主要原因。

• B+ 树查找效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

• B+ 树的磁盘读写代价更低：B+ 树的内部结点只有 key 没有 data，因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一块盘中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说 IO 读写次数也就降低了。

5. 索引的分类

• 按「索引类型」分类：B+tree 索引、Hash 索引、Full-text 索引。

• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。

• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。

• 按「字段个数」分类：单列索引、联合索引。

（1）B+tree 索引、Hash 索引、Full-text 索引

B+Tree索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree。

哈希索引：类似键值对的形式，一次即可定位。

RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。需要注意的是MySQL5.6以后支持全文索引了，5.6之前是不支持的。

为什么 MySQL 没有使用Hash 索引作为索引的数据结构呢？

主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。

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

B+Tree 和 Hash 表

（2）聚簇索引和二级索引

聚簇索引（主键索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引，一个表只能有一个聚簇索引。

非聚簇索引（也叫辅助索引或二级索引）：索引结构和数据分开存放的索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

（3）主键索引、唯一索引、普通索引、前缀索引

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。

普通索引：仅加速查询。

唯一索引：加速查询 + 列值唯一（可以有 NULL）。

覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。

联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

前缀索引：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

（4）单列索引、联合索引

单列索引：建立在单列上的索引，比如主键索引

联合索引：建立在多列上的索引

6. MySQL 索引使用的注意事项

（1）不要在列上使用函数或者进行运算，这将导致索引失效而进行全表扫描

select * from news where year(publish_time) < 2017
# 为了使用索引，防止执行全表扫描，可以进行改造
select * from news where publish_time < '2017-01-01'

select * from news where id / 100 = 1
# 为了使用索引，防止执行全表扫描，可以进行改造
select * from news where id = 1 * 100

（2）尽量避免使用 != 或 not in或 <> 等否定操作符

应该尽量避免在 where 子句中使用 != 或 not in 或 <> 操作符，因为这几个操作符都会导致索引失效而进行全表扫描。尽量避免使用 or 来连接条件，应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描。

（3）多个单列索引并不是最佳选择

MySQL 只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。因此，为多个列创建单列索引，并不能提高 MySQL 的查询性能。 假设，有两个单列索引，分别为 news_year_idx(news_year) 和 news_month_idx(news_month)。现在，有一个场景需要针对资讯的年份和月份进行查询，那么，SQL 语句可以写成：

select * from news where news_year = 2017 and news_month = 1

事实上，MySQL 只能使用一个单列索引。为了提高性能，可以使用联合索引 news_year_month_idx(news_year, news_month) 保证 news_year 和 news_month 两个列都被索引覆盖。

（4）联合索引的最左前缀原则

联合索引遵守“最左前缀”原则，即在查询条件中使用了联合索引的第一个字段，索引才会被使用。因此，在联合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。 假设，有一个场景只需要针对资讯的月份进行查询，那么，SQL 语句可以写成：

select * from news where news_month = 1

此时，无法使用 news_year_month_idx(news_year, news_month)索引，因为遵守“最左前缀”原则，在查询条件中没有使用联合索引的第一个字段，索引是不会被使用的。

（5）覆盖索引的好处

如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

（6）范围查询对多列的影响

查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。 举个例子，假设有一个场景需要查询本周发布的资讯文章，其中的条件是必须是启用状态，且发布时间在这周内。那么，SQL 语句可以写成：

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

这种情况下，因为范围查询对多列查询的影响，将导致 news_publish_idx(publish_time, enable) 索引中 publish_time 右边所有列都无法使用索引优化查找。换句话说，news_publish_idx(publish_time, enable) 索引等价于 news_publish_idx(publish_time) 。

对于范围查询，务必要注意它带来的副作用，并且尽量少用范围查询，可以通过曲线救国的方式满足业务场景。 例如，上面案例的需求是查询本周发布的资讯文章，因此可以创建一个news_weekth 字段用来存储资讯文章的周信息，使得范围查询变成普通的查询，改写成：

select * from news where news_weekth = 1 and enable = 1

然而，并不是所有的范围查询都可以进行改造，对于必须使用范围查询但无法改造的情况，不必试图用 SQL 来解决所有问题，可以使用其他数据存储技术控制时间轴，例如 Redis 的 SortedSet 有序集合保存时间，或者通过缓存方式缓存查询结果从而提高性能。

（7）索引不会包含有NULL值的列

只要列中包含有 NULL 值都将不会被包含在索引中，联合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。 因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。

（8）隐式转换的影响

当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。下面的案例中，date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换。

select * from news where date_str = 201701

要谨记隐式转换的危害，时刻注意通过同类型进行比较。

（9）like 语句的索引失效问题

like 的方式进行查询，在 like "value%" 可以使用索引，但是对于 like "%value%" 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情。所以，根据业务需求，考虑使用 ElasticSearch 或 Solr 是个不错的方案。

7. 既然索引有那么多优点，为什么不对表总的每一列创建一个索引呢

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立簇索引，那么需要的空间就会更大。

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

十二、事务

事务必须遵守4个特性：原子性、一致性、隔离性、持久性。

1. 数据库并发事务会带来哪些问题

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

• 脏读：读到其他事务未提交的数据；

• 不可重复读：前后读取的数据不一致；

• 幻读：前后读取的记录数量不一致。

（1）脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。 

（2）不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

（3）幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

幻读和不可重复读的区别：

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

2. 事务的隔离级别

SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

• 读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；

• 读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；

• 可重复读（repeatable read），就是对一个记录读取多次的记录是相同的，比如对于一个数A读取的话一直是A，前后两次读取的A是一致的，MySQL InnoDB 引擎的默认隔离级别；

• 串行化（serializable）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了，，解决的方案有两种：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

• 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

十三、锁

1. 数据库悲观锁和乐观锁的原理和应用场景分别有什么？

悲观锁：先获取锁，再进行业务操作，一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。 当数据库执行 SELECT … FOR UPDATE 时会获取被 select 中的数据行的行锁，select for update 获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

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

共享锁和排他锁都是悲观锁。乐观锁不存在于 MySQL 中，只是一种代码的逻辑实现，所以 MySQL 的锁都是悲观锁。

2. 说一下数据库的表锁和行锁

（1）表锁

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

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

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

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

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

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

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

（2）行锁

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

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

行锁的实现需要注意：

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

• 如果是共享锁，两个事务可以锁同一个索引，排它锁则不能。

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

行锁的适用场景：

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

否则，B 用户在 A 用户查询后消费前先一步将 A 用户账号上的钱转走，而此时 A 用户已经进行了用户余额是否足够的判断，则可能会出现余额已经不足但却扣款成功的情况。

为了避免此情况，需要在 A 用户操作该记录的时候进行 for update 加锁