什么是 MySQL？重要知识点/面试题总结

什么是MySQL？

MySQL是一种关系型数据库，由于MySQL开源、免费、易于扩展，在企业级Java开发中应用非常广泛。阿里巴巴的数据库系统也大量使用MySQL，因此其稳定性是有保证的。 MySQL是开源的，因此任何人都可以在GPL（通用公共许可证）下下载它并根据个人需要进行修改。 MySQL 的默认端口号是3306。

与事务相关

什么是事务？

事务是全部执行或不执行的操作的逻辑序列。

最经典的交易，也是经常被引用的例子，就是转账。如果小明想给小红转账1000元，这次转账会涉及两个关键操作：小明的余额减少1000元，小红的余额增加1000元。如果这两个操作之间突然出现问题，比如银行系统崩溃，导致小明的余额减少，而小红的余额不会增加，那就错了。事务必须确保这两个关键操作成功或失败。

介绍一下事物的四大特征（ACID）？

1. 原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作完全完成或无效；
2. 一致性：事务前后数据保持一致，多个事务读取相同数据的结果相同；
3. 隔离性：并发访问数据库时，其他事务不干扰用户的事务，并发事务期间数据库是独立的；
4. 持久化：提交交易后。他对数据库中数据的更改是永久性的，即使数据库发生故障也不应该产生任何影响。

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

在典型的应用程序中，多个事务并发运行，通常对相同的数据进行操作来完成其任务（多个用户对相同的数据进行操作）。尽管并发是必要的，但它可能会导致以下问题。

• 脏读：当一个事务访问数据并修改数据，但更改尚未提交到数据库时，另一个事务也会访问该数据，然后使用检索到的数据。因为这些数据还没有提交，所以其他事务读取到的数据就是“脏数据”，基于“脏数据”的操作可能会不正确。
• 丢失修改：表示当一个事务读取一条数据时，另一个事务也访问该数据，第一个事务修改数据后，第二个事务也修改了该数据。这样，第一个事务中的修改结果就丢失了，所以称为丢失修改。例如：事务1读取表中的数据A=20，事务2也读取A=20，事务1更改A=A-1，事务2也更改A=A-1，最终结果为A=19，交易 Change 1 丢失。
• 不可重复读：是指在一个事务内多次读取相同的数据。在该事务结束之前，另一个事务也会访问该数据。那么，在第一个事务读取数据时，由于第二个事务的改变，第一个事务两次读取的数据可能会不同。当一个事务内两次读取的数据不同时就会发生这种情况，因此称为不可重复读取。
• 幻读： 幻读类似于不可重复阅读。当一个事务 (T1) 读取一些数据行，然后另一个并发事务 (T2) 插入一些数据时，就会发生这种情况。在接下来的查询中，第一个事务（T1）会发现更多一些原本不存在的记录，就像出现幻觉一样，所以称为幻读。

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

不可重复读的重点是修改，而幻读的重点是增加或删除。

示例1（同样的条件，读取的数据，再次读取时发现数值不同）：在事务1中，A先生还没有完成读取工资为1000的操作，并且在事务2中，B先生将A的工资更改为2000，导致A再次读取工资时，工资变为2000；这是不可重复的阅读。

示例2（相同条件，第一次和第二次读取的记录条数不同）：假设工资表中有4个人工资大于3000，事务1读取所有工资的人大于 3000 找到 4 条记录。此时事务2插入了另一条工资大于3000的记录，当事务1再次读取时，查到的记录条数变成了5条，造成了幻读。

事务隔离级别是什么？ MySQL 的默认隔离级别是？

SQL 标准定义了四种隔离级别：

• READ-UNCOMMITTED：允许的最低隔离级别。读取未提交的数据更改 可能会导致脏读、幻读或 的不可重复读。
• READ-COMMITTED（验证读）：允许读取并发事务已提交的数据，可以防止脏读，但幻读或不可重复读仍然可能发生。
• REPEATABLE-READ（可重复读）：同一个字段的多次读取结果是一致的，除非数据被自己的事务本身改变，可以防止脏读和不可重复读，但是幻读仍然可能发生。
• SERIALIZABLE（可串行化）： 最高级别的隔离，完全符合 ACID 隔离级别。所有交易均按顺序依次执行，因此交易之间不存在干扰的可能性。也就是说，这个级别可以防止脏读、不可重复读、幻读。隔离级别 √√可重复读取××√系列产品选项×××

  默认存储引擎MySQL InnoDB 支持的隔离级别为 REPEATABLE-READ 。可以使用命令 SELECT @@tx_isolation; 查看

  mysql> SELECT @@tx_isolation;
  +-----------------+
  | @@tx_isolation  |
  +-----------------+
  | REPEATABLE-READ |
  +-----------------+
  复制代码

  。这里需要注意的是：与 SQL 标准的区别在于 InnoDB 存储引擎在 **REPEATABLE-READ 事务上运行。 Next-Key Lock算法在隔离级别以下使用，这样可以避免幻读，这一点与其他数据库系统（例如SQL Server）不同。因此，InnoDB存储引擎的隔离级别默认支持REPEATABLE-READ，可以充分保证事务的隔离要求，也就是说达到了SQL标准的SERIALIZABLE（可串行化）**隔离级别。

  因为隔离级别越低，事务所需的锁定就越少，所以大多数数据库系统的隔离级别REQUIRED READING（阅读提供的内容）：但是你需要了解的是 InnoDB 的存储引擎使用默认的REPEATABLE-READ，不会造成性能损失。

  InnoDB存储引擎在分布式事务的情况下一般使用**SERIALIZABLE（可串行化）**隔离级别。

  与索引相关

  为什么索引可以提高查询速度

  以下内容组成： 地址：juejin.im/post/5b55b8… 作者：Java3y

  我们先从MySQL内存的基本结构开始

  MySQL 的基本存储结构是页（记录存储在页中）：

  • 每个数据页可以构成双向链表
  • 每个数据页中的记录可以构成单向链表
    • 每个数据page 将为记录创建一个页目录，并将其存储在其中。通过主键查找记录时，可以使用二分法快速找到页目录中对应的槽，然后遍历对应槽组中的记录。快速查找给定记录
    • 使用其他列（非主键）作为搜索条件：只能从单链表中从最小的记录开始搜索每条记录。

  所以如果我们写一条像 select * from user where indexname = 'xxx' 这样的 SQL 语句，不做任何优化，就会默认完成：

  1. 找到记录所在的页面：需要遍历双链表查找位置页面
  2. 从页面中查找匹配的记录：由于不是根据主键查询，所以只能遍历页面

  的单向链表，显然这样的查找会是如果数据量很大的话，速度会很慢。 ！这个的时间复杂度是O(n)。

  索引如何加快我们的查询速度？其实就是将无序数据转换为有序（相对）：

  查找 ID 为 8 的记录的简单步骤：

  很明显：不使用索引，我们需要去双向链表找到对应的页面，现在您可以通过“目录”快速找到合适的页面！ （二分查找，时间复杂度约为O(logn)）

  其实基本结构就是B+树。 B+树作为一种树的实现，可以让我们快速找到匹配的记录。

  以下内容包括： 《Java工程师修炼之道》

  什么是最左前缀原则？

  MySQL 中的索引可以按特定顺序引用多个列。这种类型的索引称为复合索引。例如User表和共享索引的姓名和城市为(姓名,城市)，最左前缀原则是指如果查询时的查询条件与索引左侧的一个或多个连续列完全匹配， ，然后就可以使用这个专栏自带的了。如下：

  select * from user where name=xx and city=xx ; ／／可以命中索引
  select * from user where name=xx ; // 可以命中索引
  select * from user where city=xx ; // 无法命中索引            
  复制代码

  这里需要注意的是，如果查询时同时使用了两个条件，但是顺序不同，例如city=xx和name=xx，当前查询引擎会自动优化And yes 匹配共享索引的顺序，这样可以命中索引。

  由于最左前缀原则，在创建普通索引时，索引字段的顺序必须考虑去重后字段值的数量，将较大的放在前面。 ORDER BY 子句也遵循此规则。

  注意避免冗余索引

  冗余索引是指具有相同功能的索引。如果他们能进球，他们一定会进球。然后这些是冗余索引，例如（名称，城市）和（名称）。这两个索引是多余的。在大多数情况下，尝试扩展现有索引而不是创建新索引。

  MySQLS.7版本之后，可以通过查询sys库的表schema_redundant_indexes来查看冗余索引

  Mysql如何为表字段添加索引？ ？添加多列索引

  ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
  复制代码

  数据存储机制

  一些常用命令

  查看MySQL提供的所有存储引擎

  mysql> show engines;
  复制代码

  在上图中我们可以查看当前默认的存储引擎是否为MySQL InnoDB以及5.7版本 所有存储引擎中，只有InnoDB是事务型存储引擎，也就是说只有InnoDB支持事务。

  查看当前MySQL默认存储机制

  默认存储机制也可以通过以下命令查看。

  mysql> show variables like '%storage_engine%';
  复制代码

  查看表存储引擎

  show table status like "table_name" ;
  复制代码

  

  MyISAM和InnoDB的区别

  MyISAM是默认的MySQL数据库引擎（5.5版本之前）。虽然MyISAM具有出色的性能并提供了大量的功能，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁定，其最大的缺点是无法安全地从数据库中恢复。碰撞。不过在5.5版本之后，MySQL引入了InnoDB（事务型数据库引擎），5.5版本之后默认的存储引擎就是InnoDB。

  大多数时候我们使用InnoDB存储引擎，但在某些情况下使用MyISAM是合适的，比如读密集型的情况。（如果您不介意从 MyISAM 崩溃中恢复的麻烦）。

  两者对比：

  1. 是否支持行级锁定：MyISAM只有表级锁定（table-level lock），而InnoDB支持行级锁定（row-level lock）并且表级，默认为行级锁定。
  2. 是否支持事务和安全崩溃恢复：MyISAM强调性能。每个查询都是原子的，其执行时间比InnoDB类型的查询快，但它不提供对事务的支持。但是InnoDB为高级数据库功能（例如事务、外键等）提供事务支持。事务安全（ACID 兼容）表，具有事务（提交）、回滚（rollback）和崩溃恢复功能。
  3. 是否支持外键：MyISAM不支持，但InnoDB支持。
  4. 是否支持MVCC：仅InnoDB支持。对于处理大量并发事务，MVCC比简单的锁定更加高效； MVCC仅在两种隔离级别下工作：READ COMMITTED和REPEATABLE READ； MVCC可以使用乐观锁，使用悲观锁实现； MVCC在不同数据库中的实现并不统一。
  5. ......

  《MySQL高性能》上面有一句话是：

  不要轻易相信“MyISAM比InnoDB快”之类的经历。这个结论往往不是绝对的。在我们知道的很多场景下，InnoDB在速度上会落后于MyISAM，特别是当使用聚集索引或者要访问的数据可以存储在内存中时。

  一般情况下，我们选择InnoDB是没有问题的，但是在某些情况下，如果你不关心扩展性和并发性，就不需要事务支持，也不关心安全崩溃恢复，选择MyISAM也是一个不错的选择。不错的选择。但一般情况下我们还是要考虑这些问题。

  乐观锁定和悲观锁定之间的区别

  悲观锁定

  始终假设最坏的情况。每次你拿到数据的时候，你都认为别人会改变它，所以你每次拿到数据的时候都会加锁。 ，这样其他想要获取数据的人就会被阻塞，直到被锁定（共享资源一次只能被一个线程使用，其他线程被阻塞，使用后资源转移给其他线程）。传统关系数据库中使用了很多这样的锁机制，比如行锁、表锁、读锁、写锁等，都是在操作前加锁。 Java中的synchronized和ReentrantLock等独占锁都是悲观锁思想的实现。

  乐观锁定

  始终假设最好的情况。每次你去获取数据的时候，你认为别人不会改变它，所以你不会锁定它。不过更新的时候你会判断这段时间是否被别人修改过。您可以使用版本号机制和CAS算法来更新此数据。 乐观锁适合多读类型的应用，可以提高吞吐量。类似于数据库提供的 write_condition 机制，它们实际上提供了乐观锁。在Java中，包java.util.concurrent.atomic下的原子变量类是用乐观锁实现方法CAS来实现的。

  两种锁的使用场景

  从上面对两种锁的介绍，我们知道两种锁都有各自的优点和缺点。不能认为一个比另一个更好。由于 乐观锁适用于写操作相对较少（多次读的场景），即很少发生争用时，可以节省锁定成本，提高系统整体吞吐量。但如果写入较多，往往会发生冲突，导致上层应用不断重试，有效降低性能。因此， 一般来说，在脚本较多的场景下使用悲观锁更为合适。

  乐观锁的两种常见实现方法

  乐观锁通常使用版本号机制或CAS算法来实现。

  1。版本号机制

  一般会在数据表中添加一个数据版本号version字段，用来表示数据被修改了多少次。当数据被修改时，版本值加一。当线程A想要更新某个数据值时，它在读取数据的同时也会读取版本值。提交更新时，仅当刚刚读取的版本值与当前数据库中的版本值相同时才更新，否则重试。更新过程直至更新成功。

  举个简单的例子：假设数据库中的账户数据表中有一个版本字段，当前值为1；支票账户余额字段（余额）为 100 。

  1. 经理 A 现在读取它（版本=1）并从他的帐户余额中扣除 50 （100-50 ）。
  2. 当操作员 A 运行时，操作员 B 也会读取该用户信息（版本=1）并从他的帐户余额中扣除 20 （100-20 ）。
  3. A经理完成修改，添加1的数据版本号（version=2），以及扣除后的账户余额（balance=$50），并发送到数据库更新。由于提交的数据版本大于当前数据库记录版本，因此更新数据，数据库记录版本更新为2。
  4. 经理B完成操作，同时将版本号增加1（version=2），尝试将数据发送到数据库（余额=$80）。但对比数据库记录版本时发现，操作者B提交的数据版本是2号，当前数据库记录版本也是2，不满足“提交的版本必须大于”的乐观锁策略在更新发生之前从当前版本的记录开始。”因此，运营商B的提交被拒绝。

  这样我们就避免了算子B根据version=1的旧数据修改结果覆盖算子A的运算结果的可能性。 ？无锁编程是指在多个线程之间不使用锁来同步变量，即在不阻塞线程的情况下同步变量，因此也称为非阻塞同步。 CAS算法 包括三个操作数

  • 要读写的内存值 V
  • 比较值 A
  • 要写入的新值 B

  当且仅当 V 等于 A 时，CAS原子地将V的值更新为B的新值，否则不执行任何操作（比较和替换是原子操作）。一般来说，是一个 旋转操作，也就是说不断重试。

  乐观锁的缺点

  ABA问题是乐观锁的常见问题

  1 ABA问题

  如果变量V在第一次读取时值为A，在准备时检查赋值后仍然是A的值，那么是否可以表明其他线程没有改变它的值呢？显然这是不可能的，因为在这期间它的值可以改变为其他值然后又变回A，那么CAS操作就会误认为它从未改变过。这个问题被称为 CAS 运算的“ABA”问题。

  JDK 1.5 及更高版本 AtomicStampedReference 类 提供此功能。方法compareAndSet 首先检查当前引用是否等于预期引用以及当前标志是否等于预期标志。如果它们全部相等，则自动将此引用和此标志的值设置为给定的更新值。

  2 周期长、成本高

  CAS 轮换（意味着会循环直到成功）。如果长时间失败，CPU将会产生非常高的执行成本。 如果JVM能够支持处理器提供的暂停指令，性能将会得到一定程度的提升。暂停指令有两个功能。首先，它可以延迟指令执行流水线（de-pipeline），这样CPU就不会消耗太多的资源来执行，延迟时间取决于具体的实现版本，在某些处理器上延迟时间为零。其次，它可以避免退出循环时内存顺序违规导致的CPU管道刷新，从而提高CPU执行效率。

  3只能提供对共享变量的原子操作。

  CAS仅对一个共享变量有效，当操作涉及捕获多个共享变量时CAS无效。但从JDK 1.5开始，有一个类AtomicReference来保证引用对象之间的原子性。您可以将多个变量放入单个对象中以执行 CAS 操作。所以我们可以使用锁或者使用AtomicReference类将多个共享变量合并为一个共享变量进行操作。

  InnoDB 锁定机制和锁定算法

  MyISAM 和 InnoDB 存储引擎使用的锁：

  • MyISAM 使用表级锁定。
  • InnoDB 支持行级锁定（row-level lock）和表级锁定。默认情况下，行级锁为

  表级锁与行级锁对比：

  • 表级锁：Mysql中的锁定最大粒度锁。锁定当前操作的整个表。实现简单，消耗资源少，锁定快，不会造成宕机。它的锁碎片最高，触发锁冲突的概率最高，并发性最低。 MyISAM 和 InnoDB 引擎支持表级锁定。
  • 行级锁定：Mysql锁定最小粒度锁定仅锁定当前操作的行。行级锁可以大大减少数据库操作的冲突。它具有最低的锁碎片和高并发性，但锁开销也最高，加锁速度慢，并且会导致死锁。

  InnoDB 存储引擎共有三种锁定算法：

  记录锁定：单行记录锁定

• 间隙锁定：间隙锁定、范围锁定，不包括记录本身
• 下一键锁定：记录+间隙锁定范围，包括记录本身，锁定记录键的下一个键
• 间隙锁设计的目的是为了防止多个事务向同一范围插入记录，从而导致幻读问题
• 是二显式关闭间隙锁的方式：（除外来限制键和唯一性检查外，其他情况仅使用记录锁） A. 设置事务隔离级别为 RC B. 设置参数 innodb_locks_unsafe_for_binlog 为 1

大的优化

当MySQL每个表的记录数太大时，数据库CRUD的性能会明显降低。一些常见的优化措施如下：

1。限制数据量

请记住禁止所有不限制数据量的条件。检查短语。例如：当用户查询订单历史记录时，我们可以监控1个月内的情况；

2。读写分离

经典的数据库分区方案，主库负责写入，从库负责读取；

3.垂直分区

是根据数据库中数据表的相关性进行分割。 例如，用户表包含用户的登录信息及其基本信息。用户表可以拆分为两个单独的表，甚至可以放在单独的数据库中作为子数据库。

简单来说，垂直分区就是对数据表的列进行拆分，将一个列多的表拆分成多个表。 如下图所示，大家应该更容易理解。

• 垂直分区的优点： 可以减少列中的数据，减少查询时读取的块数，减少I/O次数。另外，垂直划分可以简化表的结构，更容易维护。
• 垂直拆分的缺点： 主键会冗余，冗余列需要管理，会导致join操作，可以通过在应用层面实现join来解决。另外，垂直分割会让交易变得更加困难；

4。水平分区

保持数据表的结构不变，按照一定的策略存储数据碎片。这样，每条数据就分散到不同的表或库中，从而达到分布的目的。水平分区可以支持非常大量的数据。

水平拆分是指对数据表的行进行划分。当表的行数超过200万时，速度就会变慢。目前可以将一张表的数据拆分为多张表进行存储。例如：一张用户信息表可以拆分为多个用户信息表，避免一张表数据过多对性能造成影响。

水平分区可以支持非常大量的数据。需要注意的一点是，表分区只是解决了单表数据过多的问题，但是由于表中的数据还是在同一台机器上，所以实际上对于提高 MySQL 的并发能力没有任何作用，所以横向划分是最好的方式 好库。

水平分片可以支持非常大量的数据存储，并且需要应用程序端较少的重构，但是分片事务难以解决且节点之间的join性能较差穷人和逻辑很复杂。 《Java工程师修炼之道》的作者建议尽量不要对数据进行分片，因为分片会带来逻辑、部署、运维的各种复杂性。如果优化得当，通用数据表可以支持数千条数据。 10000条以下的数据量问题不大。如果确实需要分片，请尝试选择可以通过中间件减少网络 I/O 的客户端分片架构。

常见的数据库分区方案有以下两种：

• 客户端broker： 分区逻辑在应用程序端，封装在jar包中，通过修改或封装JDBC层来实现。 当当网的Sharding-JDBC和阿里巴巴的TDDL是两种常用的实现方式。
• 中间件代理： 在应用程序和数据之间添加代理层。分发逻辑在中间件服务中统一维护。 我们现在所说的Mycat、Atlas360、DDB网易等。都是该架构的实现。

作者：SnailClimb
链接：https://juejin.im/post/5d1758d06fb9a07eed351405
来源：掘金
版权归作者所有。商业转载请联系作者获得许可。非商业转载请注明来源。