MySQL

MySQL知识点

关系型和非关系型数据库？

概念

关系型数据库：。关系数据库（或 SQL 数据库）以包含行和列的表格格式存储数据。列包含数据属性，行包含数据值。您可以链接关系数据库中的表，以更深入地了解不同数据点之间的相互关系。主要代表有SQL Server，Oracle,Mysql,PostgreSQL。

非关系型数据库：非关系数据库（或 NoSQL 数据库）使用各种数据模型来访问和管理数据。这些数据库专门针对需要大数据量、低延迟和灵活数据模型的应用程序进行了优化，这是通过放宽其他数据库的某些数据一致性限制来实现的。主要代表MongoDB，Redis、CouchDB。

数据存储

关系型数据库

关系数据库将数据存储在包含列和行的表中。每列代表一个特定的数据属性，每行代表该数据的一个实例。您为每个表指定一个主键，即唯一标识表的标识符列。您可以使用主键在表之间建立关系。您可以使用它作为领个表中的外键，在两个表的行之间建立关联。

例如，假设一家零售商创建了一张包含所有产品的表。在此表中，您可以为产品名称、描述和价格设置列。另一张表包含有关客户、客户姓名以及客户所购商品的数据。

1
2
3
4
5
6
7
8
9
Product_id（主键）  Product_name  Product_cost
P1				   Product_A	 100 USD
P2				   Product_B	 50 USD
P3				   Product_C	 80 USD

Customer_id Customer_name Item_purchased（外键）
C1			Customer_A	  P2
C2			Customer_B	  P1
C3			Customer_C	  P3

非关系型数据库

由于管理和存储无架构数据的方式不同，因此存在几种不同的非关系数据库系统。无架构数据是指在不受关系数据库要求的限制的情况下存储的数据。

键值数据库

键值数据库将数据存储为键值对的集合。在一个键值对中，键用作唯一标识符。键和值都可以是从简单对象到复杂复合对象的任何内容。

键值数据库

文档数据库

面向文档的数据库的文档模型格式与开发人员在其应用程序代码中使用的格式相同。它们将数据存储为 JSON 对象，这些对象具有灵活、半结构化和分层的性质。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
{

  company_name: "AnyCompany",

  address: {street: "1212 Main Street", city: "Anytown"},

  phone_number: "1-800-555-0101",

  industry: ["food processing", "appliances"]

  type: "private",

  number_of_employees: 987

}

图形数据库

图形数据库专门用于存储和导航关系。它们使用节点来存储数据实体，并使用边缘来存储实体之间的关系。

边缘总是有起始节点、终止节点、类型和方向。例如，它可以描述父子关系、操作和所有权。

图形数据库

区别

结构

关系数据库以表格形式存储数据，并遵循有关数据变体和表关系的严格规则。

非关系数据库则更加灵活，适用于需求不断变化的数据。

存储

Sql通常以数据库表的形式存储，例如存储用户信息，SQL中增加外部关系的话，需要在原表中增加一个外键，来关联外部数据表。

NoSql采用key-value的形式存储

事务

关系数据库模型遵循严格的 ACID 属性。这意味着一组后续操作将始终一起完成。如果单个操作失败，则整组操作都会失败。这样可以始终保证数据的准确性。

ACID属性

ACID属性指的是原子性、一致性、隔离性和持久性是指数据库在数据处理中出现错误或中断的情况下保持数据完整性的能力。

非关系数据库提供了一种更灵活的模型，即基本可用、软状态和最终一致性（BASE）。非关系数据库可以保证可用性，但不能保证即时一致性。数据库状态可能会随着时间的推移而发生变化，并最终保持一致。一些非关系数据库可能会在性能或其他方面做出妥协，以实现 ACID 合规性。

数据表 VS 数据集

关系型是表格型的，存储在数据表的行和列中。彼此关联，容易提取。而非关系型是大块存储在一起。

预定义结构 VS 动态结构

在sql中，必须定义好地段和表结构之后，才能够添加数据，例如定义表的主键、索引、外键等。表结构可以在定义之后更新，但是如果有比较大的结构变更，就会变的比较复杂。

在Nosql数据库中，数据可以在任何时候任何地方添加。不需要预先定义。

为什么使用索引？

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

建立索引的原则

  • 尽量在经常被用于查询的列上建立索引，包括WHERE 子句、JOIN 条件和排序的列。
  • 为经常需要排序、分组和联合操作的字段建立索引
  • 避免在小表建立索引：小表遍历全表可能就很快，甚至可能比索引要快。
  • 避免在频繁更新的列上建立索引： 索引的维护需要额外的开销，频繁更新的列可能导致索引失效，影响性能。
  • 限制索引的数目：索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。

Innodb为什么要用自增id作为主键？

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

MyISAM和InnoDB实现B树索引方式的区别是什么？

  • MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引”

  • InnoDB，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，这被称为“聚簇索引”或者“聚集索引”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。

    在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

info

InnoDB和MyISAM是MySQL的两个存储引擎

你了解MySQL的内部构造吗？一般可以分为哪两个部分？

可以分为服务层和存储引擎层两部分，其中：

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

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

SQL执行的全部过程

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

SQL执行的全部过程

Server层按顺序执行sql的步骤为：

  1. 连接器（验证用户身份，给予权限）
  2. 查询缓存（存在缓存则直接返回，不存在则执行后续操作）
  3. 分析器（对SQL进行词法分析和语法分析操作）
  4. 优化器（主要对执行的sql优化选择最优的执行方案方法）
  5. 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）
  6. 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

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

  • Drop直接删掉表;
  • Truncate删除表中数据，再插入时自增长id又从1开始 ;
  • Delete删除表中数据，可以加where字句。

具体解析

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

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

  3. 一般而言，drop > truncate > delete

  4. TRUNCATE 只能对TABLE；DELETE可以是table和view

  5. TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

  6. truncate与不带where的delete只删除数据，而不删除表的结构（定义）；drop语句将删除表的结构，包括被依赖的约束（constrain),触发器（trigger)索引（index)，依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

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

  8. 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

​

MySQL优化了解吗？说一下从哪些方面可以做到性能优化？

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

MySQL的两个存储引擎

MySQL的两个存储引擎为InnoDB和MyISAM,它们各有特点和使用场景。

  1. InnoDB
  • 支持事务，通过MVCC（并发版本控制）来实现
  • 默认的锁粒度为行级锁，可以支持更高的并发
  • 支持外键约束
  • 可以通过自动增长列
  • 配合一些热备工具可以支持在线热备份
  • 在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
  • 对于InnoDB类型的表，其数据的物理组织形式是聚簇表
  1. MyISAM
  • 不支持事务
  • MyISAM的索引和数据是分开的，并且索引是有压缩的
  • 每张MyISAM表在磁盘上会对应三个文件：.frm文件（存储表的定义数据）、.MYD文件（存放表具体记录的数据）、.MYI文件（存储索引）
  • MyISAM表的select count (*) 是非常快的

数据库隔离级别

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

info

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）,与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的SERIALIZABLE(可串行化)隔离级别。

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

都知道数据库索引采用B+树而不是B树，原因也有很多，主要原因是什么？

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

文件索引和数据库索引为什么使用B+树而不是B树?（上个问题的详细回答）

  • 遍历更方便：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
  • IO次数更少：由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率
  • 磁盘寻址加载次数更少：B+tree的内部结点并没有指向关键字具体信息的指针(红色部分)，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一块盘中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了
  • 查找效率稳定：B+树查找效率更加稳定，B树有可能在中间节点找到数据，稳定性不够。

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

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

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

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

回滚日志作用：

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

数据库引擎InnoDB与MyISAM的区别

InnoDB

  • 是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
  • 实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。
  • 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
  • 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
  • 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

### MyISAM

  • 设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。
  • 提供了大量的特性，包括压缩表、空间数据索引等。
  • 不支持事务。
  • 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。

总结

  • 事务: InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
  • 并发: MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
  • 外键: InnoDB 支持外键。
  • 备份: InnoDB 支持在线热备份。
  • 崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
  • 其它特性: MyISAM 支持压缩表和空间数据索引。

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

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

  • 脏读：在第一个修改事务和读取事务进行的时候，读取事务读到的数据为100，这是修改之后的数据，但是之后该事务满足一致性等特性而做了回滚操作，那么读取事务得到的结果就是脏数据了。
  • 幻读：一般是T1在某个范围内进行修改操作（增加或者删除），而T2读取该范围导致读到的数据是修改之前的了，强调范围。
  • 丢弃修改：两个写事务T1 T2同时对A=0进行递增操作，结果T2覆盖T1，导致最终结果是1 而不是2，事务被覆盖
  • 不可重复读：T2 读取一个数据，然后T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

脏读

脏读

第一个事务首先读取var变量为50，接着准备更新为100的时，并未提交，第二个事务已经读取var为100，此时第一个事务做了回滚。最终第二个事务读取的var和数据库的var不一样。

幻读（幻影读）

幻读

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

丢弃修改

丢弃修改

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。例如：事务1读取某表中的数据A=50，事务2也读取A=50，事务1修改A=A+50，事务2也修改A=A+50，最终结果A=100，事务1的修改被丢失。

不可重复读

不可重复读

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

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

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

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

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

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

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

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

数据库为什么要进行分库和分表呢？都放在一个库或者一张表中不可以吗？

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

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

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

  • 水平拆分：将同一个表的数据按一定规则拆到不同的数据库中。当表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放
  • 垂直拆分：将一个表按照字段分成多表，每个表存储其中一部分字段

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

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

  • 数据迁移与扩容问题：一般做法是通过程序先读出数据，然后按照指定的分表策略再将数据写入到各个分表中。
  • 分页与排序问题：需要在不同的分表中将数据进行排序并返回，并将不同分表返回的结果集进行汇总和再次排序，最后再返回给用户。

不可重复读和幻读区别是什么？可以举个例子吗？

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

  • 例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。
  • 例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记 录就变为了5条，这样就导致了幻读。

MySQL中有四种索引类型，可以简单说说吗？

  • FULLTEXT ：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引，需要注意的是MySQL5.6以后支持全文索引了，5.6之前是不支持的。
  • HASH ：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。 HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
  • BTREE ：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
  • RTREE ：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。 相对于BTREE，RTREE的优势在于范围查找。

MySQL的索引种类有哪些？

  • 普通索引：最基本的索引，没有任何限制。

  • 唯一索引：索引列的值必须唯一，但允许有空值。

  • 主键索引：一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

  • 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

info

索引类型主要指的是索引的数据结构，如B-Tree、Hash、R-Tree等。不同类型的索引有不同的适用场景和性能特性。例如，B-Tree索引适用于全键值、键值范围或键前缀查找，而Hash索引主要适用于等值查询。

​ 索引种类则是根据索引的特性和约束来分类的，包括普通索引、唯一索引、主键索引和全文索引。

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

视图是一种虚拟表，其内容由查询定义。与真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图的主要作用包括：

  • 简化用户对数据的理解和操作：视图可以简化复杂的SQL查询，使得用户不必为以后的操作每次指定全部的条件。
  • 增加数据的安全性：通过视图，用户只能查询和修改指定的数据，防止敏感信息被未授权的用户查看，增强机密信息的安全性。
  • 提高表的逻辑独立性：视图可以屏蔽原有表结构变化带来的影响。

视图可以被修改，MySQL中，你可以使用ALTER VIEW语句来修改已存在的视图。此外，视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据，也就是说，更新视图会作用在原表上。

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

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

info

Memcached是一个高性能的分布式内存对象缓存系统。它通过在内存中维护一个统一的巨大的hash表，能够用来存储各种格式的数据，包括图像、视频、文件以及数据库检索的结果等。简单的说就是将数据调用到内存中，然后从内存中读取，从而大大提高读取速度345。

APC，全称是Alternative PHP Cache，是PHP的一个扩展。它除了可以将PHP代码解释成OPCode保存在内存中之外，还能在PHP的进程之间使用共享内存（系统内核的数据结构）来保存数据，而且完全透明。

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

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

不宜建索引的情况：

  1. 对于查询中很少涉及的列或者重复值比较多的列
  2. 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）

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

  • 超键：在关系中能唯一标识元组的属性集称为关系模式的超键
  • 候选键：不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！
  • 主键：用户选作元组标识的一个候选键程序主键
  • 外键：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

举例：

学号 姓名 性别 年龄 系别 专业
20020612 李辉 男 20 计算机 软件开发
20060613 张明 男 18 计算机 软件开发
20060614 王小玉 女 19 物理 力学
20060615 李淑华 女 17 生物 动物学
20060616 赵静 男 21 化学 食品化学
20060617 赵静 女 20 生物 植物学
  1. 超键：于是我们从例子中可以发现 学号是标识学生实体的唯一标识。那么该元组的超键就为学号。除此之外我们还可以把它跟其他属性组合起来，比如：(学号，性别)，(学号，年龄)
  2. 候选键：根据例子可知，学号是一个可以唯一标识元组的唯一标识，因此学号是一个候选键，实际上，候选键是超键的子集，比如 （学号，年龄）是超键，但是它不是候选键。因为它还有了额外的属性。
  3. 主键：简单的说，例子中的元组的候选键为学号，但是我们选定他作为该元组的唯一标识，那么学号就为主键。
  4. 外键是相对于主键的，比如在学生记录里，主键为学号，在成绩单表中也有学号字段，因此学号为成绩单表的外键，为学生表的主键。

主键为候选键的子集，候选键为超键的子集，而外键的确定是相对于主键的。

主键和候选键的区别

比较基础 主键 候选键
定义 它是一个唯一且非空的键，用于唯一地标识模式中每个表的记录。 它也是一个唯一键，用于唯一地标识关系或表中的记录。
基本 一个表或关系只能包含一个主键。 一个表或关系可以有多个候选键。
NULL 主键的任何列都不能为NULL。 候选键的列可以包含NULL值。
目标 它是表或关系的基本组成部分。 它表示哪个键可以用作主键。
用途 它可以用作候选键。 它可能或可能不用作主键。
指定 不需要为任何关系指定主键。 在不指定候选键的情况下无法建立关系。
示例 考虑一个名为“student”的表，具有列（roll_no., name, class, DOB, email, mobile）。这里， roll_no**列可以作为关系的主键，因为它可以唯一标识学生的记录。 给定表中的roll_no, mobile和email列可以作为候选键，因为它们可以唯一标识学生的记录。

数据库三大范式

第一范式

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。 所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。

如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。

简而言之，第一范式就是无重复的列。

第二范式

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。

为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。 第二范式（2NF）要求实体的属性完全依赖于主关键字。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。

简而言之，第二范式就是非主属性非部分依赖于主关键字。

第三范式

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。

简而言之，第三范式就是属性不依赖于其它非主属性。

总结

  • 第一范式（1NF）：字段不可分； 　　
  • 第二范式（2NF）：有主键，非主键字段依赖主键； 　
  • 第三范式（3NF）：非主键字段不能相互依赖。

解释：1NF：原子性。 字段不可再分,否则就不是关系数据库;； 　　2NF：唯一性 。一个表只说明一个事物； 　　3NF：每列都与主键有直接关系，不存在传递依赖。

MySQL常见的存储引擎InnoDB、MyISAM的区别？适用场景分别是？

  1. 事务：MyISAM不支持，InnoDB支持
  2. 锁级别： MyISAM 表级锁，InnoDB 行级锁及外键约束
  3. MyISAM存储表的总行数；InnoDB不存储总行数；
  4. MyISAM采用非聚集索引，B+树叶子存储指向数据文件的指针。InnoDB主键索引采用聚集索引，B+树叶子存储数据

适用场景： MyISAM适合： 插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择， 没有事务。 InnoDB适合： 可靠性要求比较高，或者要求事务；表更新和查询都相当的频繁， 大量的INSERT或UPDATE。

聚集索引和非聚集索引

以字典为例，聚集索引就是按照拼音查询，非聚集索引就是按照偏旁等来进行查询。

聚集索引

我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查"安"字，就会很自然地翻开字典的前几页，因为"安"的拼音是"an"，而按照拼音排序 汉字的字典是以英文字母"a"开头并以"z"结尾的，那么"安"字就自然地排在字典的前部。如果你翻完了所有以"a"开头的部分仍然找不到这个字，那么就说明你的字典中没有这个字；也就是说，字典的正文部分本身就是一个目录，你不需要再去查其他目录来找到你需要找的内容。

我们把这种正文内容本身就是一种按照一定规则排列的目录称为"聚集索引"。

非聚集索引

仍然以字典为例，如果你遇到不认识的字，不知道它的发音，这时候，你就不能按照聚集索引的方法找到你要查的字，而需要去根据"偏旁部首"查到你要找的字，然后根据这个字后的页码直接翻到某页来找到你要找的字。

但是结合"部首目录"和"检字表"而查到的字的排序并不是 真正的正文的排序方法，比如你查"张"字，我们可以看到在查部首之后的检字表中"张"的页码是672页，检字表中"张"的上面是"驰"字，但页码却是63 页，"张"的下面是"弩"字，页面是390页。

我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为"非聚集索引"。

区别

聚集索引和非聚集索引的区别在于，通过聚集索引可以直接查到需要查找的数据， 而通过非聚集索引只能查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

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

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。 。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

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

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

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

  • 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
  • 取值离散大的字段：变量各个取值之间的差异程度大的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
  • 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。 唯一、不为空、经常被查询的字段的字段适合建索引

MySQL中CHAR和VARCHAR的区别有哪些？

  • char的长度是不可变的，用空格填充到指定长度大小，而varchar的长度是可变的。
  • char的存取速度还是要比varchar要快得多
  • char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

MySQL 索引使用的注意事项

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

不要在列上使用函数，这将导致索引失效而进行全表扫描

1
select * from news where year(publish_time) < 2017

为了使用索引，防止执行全表扫描，可以进行改造:

1
select * from news where publish_time < '2017-01-01'

还有一个建议，不要在列上进行运算，这也将导致索引失效而进行全表扫描

1
select * from news where id / 100 = 1

为了使用索引，防止执行全表扫描，可以进行改造

1
select * from news where id = 1 * 100

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

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

1
select * from news where id = 1 or id = 2

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

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

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

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

复合索引的最左前缀原则

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

1
select * from news where news_month = 1

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

覆盖索引的好处

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

范围查询对多列查询的影响

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

1
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 字段用来存储资讯文章的周信息，使得范围查询变成普通的查询，SQL 可以改写成：

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

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

索引不会包含有NULL值的列

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

隐式转换的影响

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

1
select * from news where date_str = 201701

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

like 语句的索引失效问题

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

MySQL中有哪些索引？有什么特点？

  • 普通索引：仅加速查询
  • 唯一索引：加速查询 + 列值唯一（可以有null）
  • 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
  • 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
  • 全文索引：对文本的内容进行分词，进行搜索
  • 索引合并：使用多个单列索引组合搜索
  • 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
  • 聚簇索引：表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)

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

  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
  • 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立簇索引，那么需要的空间就会更大。
  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

索引如何提高查询速度的

将无序的数据变成相对有序的数据（就像查有目的一样）

使用索引的注意事项

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

增加B+树的路数可以降低树的高度，那么无限增加树的路数是不是可以有最优的查找效率？

不可以，无限增加路数B+树会退化为一个有序数组，并且如果数据量大的话，不一定能一次性加载到内存中。有序数组没法一次性加载进内存。

数据库表锁和行锁

表锁

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

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

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

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

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

行锁

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

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

行锁的实现需要注意

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

行锁的适用场景：

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

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

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

SQL语法中内连接、自连接、外连接（左、右、全）、交叉连接的区别分别是什么？

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

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

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

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

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

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

  • 范式优化： 比如消除冗余（节省空间。。）
  • 反范式优化：比如适当加冗余等（减少join）
  • 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
  • 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
  • 拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

!!! info 反范式优化优缺点 - 优点 - 提高查询性能：反范式设计可以避免复杂的联接操作，从而提高查询性能。当数据量很大的时候，反范式只需在同一张表中查询，显然反范式的效率会更好。 - 简化查询：反范式设计使得某些查询变得更加简单和高效。 - 提升性能：反范式是通过增加冗余、聚合的手段来提升性能。反范式优化也是一种改善慢查询的优化思路。 - 缺点 - 反范式有很多重复的数据，会占用更多的内存，查询时可能会较多地使用GROUP BY或DISTINCT等耗时耗性能的关键字。 - 当要修改更新数据时，范式更灵活，而反范式要修改全部的数据，且易出错。

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

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

  • 水平拆分：将同一个表的数据按一定规则拆到不同的数据库中。当表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放
  • 垂直拆分：将一个表按照字段分成多表，每个表存储其中一部分字段

为什么MySQL索引适用用B+树而不用hash表和B树？

  • 利用Hash需要把数据全部加载到内存中，如果数据量大，是一件很消耗内存的事，而采用B+树，是基于按照节点分段加载，由此减少内存消耗。
  • 和业务场景有段，对于唯一查找（查找一个值），Hash确实更快，但数据库中经常查询多条数据，这时候由于B+数据的有序性，与叶子节点又有链表相连，他的查询效率会比Hash快的多。
  • b+树的非叶子节点不保存数据，只保存子树的临界值（最大或者最小），所以同样大小的节点，b+树相对于b树能够有更多的分支，使得这棵树更加矮胖，查询时做的IO操作次数也更少。

关系型数据库的四大特性在得不到保障的情况下会怎样？

ACID，原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。

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

原子性

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做。即要么转账成功，要么转账失败，是不存在中间的状态！

如果无法保证原子性会怎么样？

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

一致性

一致性是指事务执行前后，数据处于一种合法的状态，这种状态是语义上的而不是语法上的。 那什么是合法的数据状态呢？这个状态是满足预定的约束就叫做合法的状态，再通俗一点，这状态是由你自己来定义的。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！

如果无法保证一致性会怎么样？

  • 例一:A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现了此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须大于0。
  • 例二:A账户200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道此时数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的余额必须不变。

隔离性

隔离性是指多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

如果无法保证隔离性会怎么样？

假设A账户有200元，B账户0元。A账户往B账户转账两次，金额为50元，分别在两个事务中执行。如果无法保证隔离性，A可能就会出现扣款两次的情形，而B只加款一次，凭空消失了50元，依然出现了数据不一致的情形！

持久性

根据定义，持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

如果无法保证持久性会怎么样？

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

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

数据库如何保证一致性？

分为两个层面来说。

  • 从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。
  • 从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

数据库如何保证持久性？

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

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

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

面试问题：MVCC是怎么实现的？

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 4个隐式字段，undo日志 ，Read View 来实现的。

具体实现见MVCC的实现原理

B树和B+树以及区别

B树

首先介绍一下一棵 m 阶的 B 树的特性。m 表示这个树的每一个节点最多可以拥有的子节点个数。一棵 m 阶的 B 树满足的性质如下：

  1. 每个节点最多有 m 个子节点。
  2. 每一个非叶子节点（除根节点）最少有 \(\lceil m/2 \rceil\) 个子节点。
  3. 如果根节点不是叶子节点，那么它至少有两个子节点。
  4. 有 k 个子节点的非叶子节点拥有 k - 1个键，且升序排列，满足 k[i] < k[i+1]。
  5. 所有的叶子节点都在同一层

B+树

m阶B+树

  1. 每个分支结点最多有m棵子树。
  2. 非叶根结点至少有两棵子树,其他每个分支结点至少有⌈m/2⌉棵子树。
  3. 结点的子树个数与关键字个数相等。
  4. 所有叶结点包含全部关键字及指向相应记录的指针，而且叶结点中将关键字按大小顺序排列,并且相邻叶结点按大小顺序相互链接起来。
  5. 所有分支结点（可视为索引的索引）中仅包含它的各个子结点（即下一级的索引块）中关键字的最大值及指向其子结点的指针。

区别

  1. 在B树中，具有n个关键字的结点含有（n+1）棵子树；而在B+树中，具有n个关键字的结点只含有n棵子树,即每个关键字对应一棵子树。
  2. 在B树中，根结点的关键字个数n的范围是1 <= n <= m-1,非根结点的范围是⌈m/2⌉-1<=n <= m-1；而在B+树中，根结点的关键字个数n的范围是1 <= n <= m,非根结点的范围是⌈m/2⌉<=n <= m。
  3. 在B+树中，所有非叶结点仅起到索引作用，即结点中的每个索引项只含有对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址。
  4. 在B树中，叶结点包含的关键字和其他结点包含的关键字是不重复的；而在B+树中，叶结点包含了全部关键字，即其他非叶结点中的关键字包含在叶结点中。

覆盖索引

每一个索引在 InnoDB 里面对应一棵B+树，先讲一下主键索引和非主键索引的区别：

对于表:

1
2
3
4
5
6
7
8
9
10
mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 小赵 |  10 |
|  2 | 小王 |  11 |
|  3 | 小李 |  12 |
|  4 | 小陈 |  13 |
+----+------+-----+
4 rows in set (0.00 sec)
img

叶子节点中，主键索引存储了整行数据，而非主键索引中存储的值为主键id。

当我们执行以下语句:

1
select age from student where name = '小李';

执行顺序:

  1. 在name索引树上找到名称为小李的节点 id为03
  2. 从id索引树上找到id为03的节点 获取所有数据
  3. 从数据中获取字段命为age的值12,返回.

这样从非主键索引树搜索再回到主键索引树搜索的过程称为：回表

回表一定程度上消耗性能,那么如何降低这种性能损耗呢?于是提出了一种方法:覆盖索引.

覆盖索引

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免回表的产生减少了树的搜索次数，显著提升性能。

覆盖索引的使用

如果一个业务中,很多类似于根据姓名查找年龄的业务,那么可以将这些热点业务重新根据(name , age)建立联合索引,先删除之前以name构建的索引:

1
2
ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);

联合索引:

[img

再次执行如下sql:

1
select age from student where name = '小李';

执行流程:

  1. 在name,age联合索引树上找到名称为小李的节点。
  2. 此时节点索引里包含信息age直接返回 12,从而避免回表。

select ... for update where xxx... 加锁情况

更详细见【原创】惊！史上最全的select加锁分析(Mysql)

主键

当where条件用的数据库主键时。

例如开启一个事务1，在事务中更新id=1的用户的年龄：

1
2
3
begin;
select * from user where id=1 for update;
update user set age=22 where id=1;

where条件中的id是数据库的主键，并且使用for update关键字，加了一个行锁，这个事务没有commit。

此时，开启了另外一个事务2，也更新id=1的用户的年龄：

1
2
3
begin;
update user set age=23 where id=1;
commit;

在执行事务2的sql语句的过程中，会一直等待事务1释放锁。

唯一索引

当where条件用的数据库唯一索引时。

开启一个事务1，在事务中更新code=101的用户的年龄：

1
2
3
begin;
select * from user where code='101' for update;
update user set age=22 where code='101';

where条件中的code是数据库的唯一索引，并且使用for update关键字，加了一个行锁，这个事务没有commit。

此时，开启了另外一个事务2，也更新code=101的用户的年龄：

1
2
3
begin;
update user set age=23 where code='101';
commit;

普通索引

当where条件用的数据库普通索引时。

开启一个事务1，在事务中更新name=周星驰的用户的年龄：

1
2
3
begin;
select * from user where name='周星驰' for update;
update user set age=22 where name='周星驰';

where条件中的name是数据库的普通索引，并且使用for update关键字，加了一个行锁，这个事务没有commit。

此时，开启了另外一个事务2，也更新name=周星驰的用户的年龄：

1
2
3
begin;
update user set age=23 where name='周星驰';
commit;

执行结果跟主键的情况也是一样的。 img

主键范围

当where条件用的数据库主键范围时。

开启一个事务1，在事务中更新id in (1,2)的用户的年龄：

1
2
3
begin;
select * from user where id in (1,2) for update;
update user set age=22 where id in (1,2);

where条件中的id是数据库的主键范围，并且使用for update关键字，加了多个行锁，这个事务没有commit。

此时，开启了另外一个事务2，也更新id=1的用户的年龄：

1
2
3
begin;
update user set age=23 where id=1;
commit;

执行结果跟主键的情况也是一样的。 img

此时，开启了另外一个事务2，也更新id=2的用户的年龄：

1
2
3
begin;
update user set age=23 where id=2;
commit;

执行结果跟主键的情况也是一样的。 img

普通字段

当where条件用的数据库普通字段时。该字段既不是主键，也不是索引。

开启一个事务1，在事务中更新age=22的用户的年龄：

1
2
3
begin;
select * from user where age=22 for update;
update user set age=22 where age=22 ;

where条件中的age是数据库的普通字段，并且使用for update关键字，加的是表锁，这个事务没有commit。

此时，开启了另外一个事务2，也更新age=22的用户的年龄：

1
2
3
begin;
update user set age=23 where age=22 ;
commit;

此时，执行事务2时，会一直阻塞等待事务1释放锁。

调整一下sql条件，查询条件改成age=23：

1
2
3
begin;
update user set age=23 where age=23 ;
commit;

此时，行事务3时，也会一直阻塞等待事务1释放锁。

也就是说，在for update语句中，使用普通字段作为查询条件时，加的是表锁，而并非行锁。

空数据

当where条件查询的数据不存在时，会发生什么呢？

开启一个事务1，在事务中更新id=66的用户的年龄：

1
2
3
begin;
select * from user where id=66 for update;
update user set age=22 where id=66 ;

这条数据是不存在的。

此时，开启了另外一个事务2，也更新id=66的用户的年龄：

1
2
3
begin;
update user set age=23 where id=66 ;
commit;

执行结果： img 执行成功了，说明这种情况没有加锁。

总结

最后给大家总结一下select...for update加锁的情况：

  1. 主键字段：加行锁。
  2. 唯一索引字段：加行锁。
  3. 普通索引字段：加行锁。
  4. 主键范围：加多个行锁。
  5. 普通字段：加表锁。
  6. 查询空数据：不加锁。

如果事务1加了行锁，一直没有释放锁，事务2操作相同行的数据时，会一直等待直到超时。

如果事务1加了表锁，一直没有释放锁，事务2不管操作的是哪一行数据，都会一直等待直到超时。

索引下推

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

索引下推优化的原理

我们先简单了解一下MySQL大概的架构：

MySQL大概架构

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

  • 存储引擎读取索引记录；
  • 根据索引中的主键值，定位并读取完整的行记录（回表一次）；
  • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件（回表第二次）。

使用ICP的情况下，查询过程：

  • 存储引擎读取索引记录（不是完整的行记录）；
  • 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
  • 条件满足，使用索引中的主键去定位并读取完整的行记录（回表一次）；
  • 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。、

什么时候会触发索引下推

  1. 查询类型：只有使用了非聚簇索引的查询才可能触发ICP。这是因为在聚簇索引中，索引与数据行是紧密耦合的，所以没有额外的检索步骤。

  2. WHERE 子句的结构：当查询的WHERE子句中的一部分可以使用索引进行过滤，而另一部分则不能时，ICP最有可能被触发。在这种情况下，可以使用索引的部分首先进行过滤，然后应用余下的条件。

例如，考虑一个包含first_name和last_name的复合索引。以下查询可能会触发ICP：

1
SELECT * FROM users WHERE first_name = 'John' AND last_name LIKE 'Smi%';

在这个例子中，first_name的等值比较可以直接用索引进行过滤，而last_name的LIKE操作可能会触发ICP，这样只有满足first_name条件的索引条目才会进一步检查last_name。

  1. 存储引擎：不是所有的存储引擎都支持ICP。例如，InnoDB和MEMORY存储引擎支持ICP，而MyISAM则不支持。

  2. EXPLAIN输出：当怀疑ICP可能对查询有益时，可以使用EXPLAIN命令来查看查询的执行计划。如果ICP被使用，你会在Extra列看到“Using index condition”。

面试
#面试 #MySQL
MySQL
https://gstarmin.github.io/2023/12/23/MySQL/
作者
Starmin
发布于
2023年12月23日
更新于
2023年12月25日
许可协议