4、数据库高阶 -- sql优化等面试总结

4、数据库高阶 – sql优化等面试总结 文章目录

• 4、数据库高阶 -- sql优化等面试总结
• • @[toc]
  • 一 、 explain
  • • 1.1 explain是什么？干什么用的，提到SQL优化为什么要知道explain？
    • 1.2 explain怎么用？
    • 1.3 explain结果字段的含义介绍
    • 1.4 explain总结
  • 二、具体的应该怎么优化？
  • • 2.1 创建索引
    • • 2.1.1 索引是什么
      • 2.1.2 索引的优劣
      • 2.1.3 索引的分类和基本语法
      • 2.1.4 哪些情况需要创建索引
      • 2.1.5 哪些情况不要创建索引
      • 2.1.6 索引使用注意事项
      • 2.1.7 索引最大可以创建多少个？
      • 2.1.8 索引下推（索引条件下推优化）
    • 2.2 索引的常见模型（Hash索引，有序数组，B-Tree索引）
    • • 2.2.1 B树和B+树的区别
      • 2.2.2 二叉树和红黑树为什么不能用在索引的结构上?
    • 2.3 引擎对索引的支持对比图
    • 2.4 InnoDB 的索引模型（聚簇索引，非聚簇索引）
    • • 2.4.1 聚簇索引和非聚簇索引的查询有什么区别
      • 2.4.2 索引覆盖
    • 2.5 MySQL中提供的存储引擎
    • • 2.5.1 MySQL表级锁、行级锁和页面锁的区别
  • 三、事务
  • • • 3.1 事务的四大特性（ACID）
      • 3.2 在不考虑隔离性的前提下，事务的并发会产生的问题
      • 3.3 为了解决这些问题，就有了"隔离级别"的概念。
      • 3.4 深入解析隔离级别
      • 3.5 事务的传播性
      • 3.6 事务的启动方式
  • 四、乐观锁和悲观锁的区别
  • • • 4.1 事务并发问题：更新丢失
      • 4.2 悲观锁（synchronized/lock）
      • 4.3 MySQL修改数据时，如何实现悲观锁
      • 4.4 乐观锁 （CAS ：compare and swap）
      • 4.5 建表
      • 4.6 实体
      • 4.7 安装插件
      • 4.8 测试
  • 五、SQL优化的具体方案
  • 六、面试高频问题
  • • • 6.1、mysql插入时主键自增问题
      • 6.2、mysql的存储引擎及区别
      • 6.3、mysql优化实际操作
      • 6.4、mysql索引
      • 6.5、事务四大特性
      • 6.6、不考虑隔离情况，并发情况下事物会产生的问题
      • 6.7、数据隔离级别（主要就是解决并发下上面事物产生的问题）
      • 6.8、事物的传播特性
      • 6.9、mysql的多表查询<内链接和外连接区别>
      • 6.10、数据库三大范式

一 、 explain 1.1 explain是什么？干什么用的，提到SQL优化为什么要知道explain？

• 使用 explain 查询和分析SQL的执行记录，可以进行sql的性能优化！

1.2 explain怎么用？

• explain + sql 语句

1.3 explain结果字段的含义介绍

• id

  • SELECt识别符。这是SELECT的查询序列号，表示查询中执行select子句或操作表的顺序！(决定表的读取顺序)

  • id相同，表执行顺序由上到下，与sql中顺序无关

  • id不同，id值越大优先级越高，越先被执行

  • id 如果相同，可以认为是一组，从上往下顺序执行（衍生 = derived）；在所有组中，id值越大，优先级越高，越先执行

• select_type

  • SIMPLE：简单查询，查询中不使用子查询或者union等任何复杂查询

    • sql explain select * from film where id = 2;
    • UNIOn和子查询的补充

  • PRIMARY：查询中若包含任何复杂的子查询，则最外层被标记为PRIMARY，俗称是鸡蛋壳

  • SUBQUERY：在SELECt或WHERe列表中包含了子查询，该子查询被标记为：SUBQUERY

  • UNIOn：UNIOn中的第二个或后面的SELECT语句

  • DEPENDENT UNIOn：UNIOn中的第二个或后面的SELECT语句,取决于外面的查询

  • UNIOn RESULT：从UNIOn表获取结果的SELECT

  • DEPENDENT SUBQUERY：子查询中的第一个SELECT,取决于外面的查询

  • DERIVED：在FROM列表中包含的子查询被标记为：DERIVED（衍生）

    • 用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
    • 若第二个SELECT出现在UNIOn之后，则被标记为UNIOn；若UNIOn包含在FROM子句的子查询中，外层SELECt将被标记为：DERIVED

• 案例

• • 第一行：id列为1，表示第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select。
  • 第二行：id为3，表示该查询的执行次序为2（ 4 >= 3），是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。
  • 第三行：select列表中的子查询，select_type为subquery，为整个查询中的第二个select。
  • 第四行：select_type为union，说明第四个select是union里的第二个select，最先执行。
  • 第五行：代表从union的临时表中读取行的阶段，table列的表示用第一个和第四个select的结果进行union操作。

• table

  • 输出的行所引用的表！

• type：显示查询使用了何种类型，从好到差：system>const>eq_ref>range>index>all

  • 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
    • system：表只有一行记录(=系统表)。这是const联接类型的一个特例。平时不会出现，也可以忽略不计
    • const：表示通过索引一次就找到了，const用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置 where 列表中，mysql就能将该查询转换为一个常量。
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引作为关联条件进行扫描。
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
    • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在where 语句中出现了 between 或者<, > , in 等的查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。
    • index：Full Index Scan，index与ALL区别为index类型只遍历索引树，这通常比 all 快，因为索引文件通常比数据文件小。（也就是说 index 和 all 虽然都是读全表，但是 index 是从索引中读取的，而 all 是从硬盘中读取的）
    • ALL：Full Table Scan，将遍历全表以找到匹配的行
    • 一般来说，得保证查询至少达到 range 级别, 最好达到 ref

• possible_keys

  • 显示可能应用在这张表中得索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

• key

  • 实际使用的索引。如果显示为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在 key 列表中。

• key_len

  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。（key_len显示的值为索引字段的最大可能得长度，并非实际使用长度，即key_len是根据建表时定义计算而得，不是通过表内检索出的）

• ref

  • 显示索引得哪一列被使用了，如果可能的话，是一个常量。表示上述表的连接匹配条件，即那些列或常量被用于查找索引列上的值

• rows

  • 根据表统计信息及索引选用情况，大致估算出找到所需记录要读取得行数。

• Extra

  • 该列包含不适合在其他列中显示但非常重要的
    • Using filesort：说明MySQL会对数据使用一个外部得索引排序,而不是按照表内得索引顺序进行得读取。
    • Using temporary：使用了临时表保存中间结果，mysql 再对查询结果排序时使用临时表。常见用 order by 和 group by
    • Using index：表示相应的 select 操作中使用了覆盖索引，避免了访问表的数据行，效率很高。如果同时出现 using where ， 表明索引被用来执行索引键值的查找；如果没有同时出现 using where ， 表明索引用来读取数据而非执行查找动作
      备注：覆盖索引查询列要被所建的索引覆盖
      • 覆盖索引（Covering Index）
      • MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）
      • 注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降
    • Using where: 表明使用了where 过滤
    • Using join buffer ： 使用了连接缓存
    • impossible where ：where 子句的值总是 false，不能用来获取任何元素
    • select tables optimized away ：在没有 group by 子句的情况下，基于索引优化 min/max 操作或对于 myisam 存储引擎优化 count（*） 操作，不必等到执行阶段再进行计算，查询执行计划生成的极端即完成优化
    • Distinct：优化distinct操作，在找到第一匹配的元素后即停止找同样值的动作

1.4 explain总结

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

---

二、具体的应该怎么优化？ 2.1 创建索引 2.1.1 索引是什么

• 索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
  • 可以理解为“排好序的快速查找数据结构”
• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据,这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
• MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
• 创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERe 子句的条件)。

2.1.2 索引的优劣

• 优势

  • 类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本。
  • 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

• 劣势

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。
  • 虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。都会调整因为更新所带来的键值变化后的索引信息。
  • 建立索引会占用磁盘空间的索引文件。

2.1.3 索引的分类和基本语法

• 主键索引

  • PRIMARY KEY（主键索引）：

    • ALTER TABLE 'table_name' ADD PRIMARY KEY('col')
      

    • 是一种特殊的唯一索引，不允许有空值。

• 唯一索引

  • UNIQUE（唯一索引）：

    • ALTER TABLE 'table_name' ADD UNIQUE('col')
      

    • 与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。

• 普通索引

  • INDEX（普通索引）：

    • ALTER TABLE 'table_name' ADD INDEX index_name('col')
      

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

• 全文索引

  • FULLTEXT（全文索引）：

    • ALTER TABLE 'table_name' ADD FULLTEXT('col')
      

    • 仅可用于MyISAM和InoDB，针对较大的数据，生成全文索引很耗时耗空间。

• 组合索引

  • 组合索引：

    • ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')
      

    • 为了更多的提高mysql效率可建立组合索引，遵循“最左前缀”原则。

      • 创建复合索引应该将最常用（频率）做限制条件的列放在最左边，依次递减。

      • 组合索引最左字段用in是可以用到索引的。相当于建立col1,col1col2,col1col2col3三个索引

      • 修改索引名称（mysql）

        • 对于MySQL 5.7及以上版本,可以执行以下命令：

        • ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name
          

        • 对于MySQL 5.7以前的版本，可以执行下面两个命令：

        • ALTER TABLE tbl_name DROP INDEX old_index_name
          ALTER TABLE tbl_name ADD INDEX new_index_name(column_name)
          

2.1.4 哪些情况需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
• WHERe条件里用不到的字段不创建索引
• 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
• 查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

2.1.5 哪些情况不要创建索引

• 表记录太少
• 经常增删改的表
  • 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE
  • 更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
  • 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引
• 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

2.1.6 索引使用注意事项

• 不要滥用索引

  • 索引提高查询速度，却会降低更新表的速度，因为更新表时，mysql不仅要更新数据，保存数据，还要更新索引，保存索引
  • 索引会占用磁盘空间

• 索引不会包含含有NULL值的列

  • 复合索引只要有一列含有NULL值,那么这一列对于此符合索引就是无效的，因此我们在设计数据库设计时不要让字段的默认值为NULL。

• MySQL查询只是用一个索引

  • 如果where字句中使用了索引的话，那么order by中的列是不会使用索引的

• like

  • like '%aaa%'不会使用索引而like "aaa%"可以使用索引

2.1.7 索引最大可以创建多少个？

一个表最多16个索引,最大索引长度256字节。索引一般不明显影响插入性能（大量小数据例外），因为建立索引的时间开销是O(1)或者O(logN)。

2.1.8 索引下推（索引条件下推优化）

• 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。
• 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
• 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
• 这个优化技术关键的操作就是将与索引相关的条件由MySQL服务器向下传递至存储引擎，由此减少IO次数。MySQL服务器到存储引擎是向下，传递的是与索引列相关的查询条件，所以还是索引条件下推优化更容易理解一些。

2.2 索引的常见模型（Hash索引，有序数组，B-Tree索引）

• 哈希索引

  • 哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。

  • 当多个key值经过哈希函数的换算，出现同一个值的情况时。处理这种情况的一种方法是拉出一个链表。

  • Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash 索引的查询效率要远高于 B-Tree索引

  • Hash 索引的弊端

    • Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
    • Hash 索引无法被用来避免数据的排序操作。
    • Hash 索引不能利用部分索引键查询。
    • Hash 索引在任何时候都不能避免表扫描。
    • Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

• 有序数组

  • 有序数组在等值查询和范围查询场景中的性能就都非常优秀
  • 如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
  • 所以，有序数组索引只适用于静态存储引擎 (比如你要保存的是2017年某个城市的所有人口信息，这类不会再修改的数据)

• B-Tree索引

  • 索引存储的值按索引列中的顺序排列。可以用B-Tree索引进行全关键字、关键字范围和关键字前缀查询。如果使用索引，必须保证按索引最左边前缀进行查询。由于B树中节点是顺序存储的，可以对查询结果进行order by。

  • B-Tree索引的弊端

    • 查询必须从索引的最左边的列开始
    • 不能跳过某一索引列。
    • 存储引擎不能使用索引中范围条件右边的列。

2.2.1 B树和B+树的区别

• B+树的所有数据都存放在叶子节点上
• B+树的叶子节点之间通过链表的形式相连
• B+树的非叶子节点只存下一个节点的指针，而B树的非叶子节点不光存指针还要存数据
• 正式因为B+树的这三个特性，使得在查找时效率比B树要高，所以Innodb的索引的底层数据结构才会使用B+树来实现。

2.2.2 二叉树和红黑树为什么不能用在索引的结构上?

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：又叫平衡二叉树，树的高度随着数据量增加而增加，IO代价高。

2.3 引擎对索引的支持对比图

2.4 InnoDB 的索引模型（聚簇索引，非聚簇索引）

• InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。

• 假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。这个表的建表语句是：

  mysql> create table T(
  id int primary key, 
  k int not null, 
  name varchar(16),
  index (k))engine=InnoDB;
  

• 表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下。

• 从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

  • 主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。
  • 非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）或非聚簇索引。

2.4.1 聚簇索引和非聚簇索引的查询有什么区别

• 看上图
• 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
• 如果语句是select * from T where k=5，即非聚簇索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。

也就是说，基于非聚簇索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

2.4.2 索引覆盖

• 如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
• 只扫描索引而无需回表的优点：
  • 索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
  • 因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
  • 一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
  • innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。

• 覆盖索引的坑：mysql查询优化器会在执行查询前判断是否有一个索引能进行覆盖，假设索引覆盖了where条件中的字段，但不是整个查询涉及的字段，mysql5.5和之前的版本也会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

2.5 MySQL中提供的存储引擎

• MyISAM存储引擎

  • 不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有要求或者以select，insert为主的应用基本上可以用这个引擎来创建表
  • 表级锁，存储读多写少的数据
  • 将数据和索引分开放，查询效率高

• InnoDB存储引擎

  • 该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。
  • InnoDB存储引擎的特点：支持自动增长列，支持外键约束。
  • 支持事务，默认是表级锁，支持行级锁 (索引失效，全盘扫描时，会升级为表级锁)
  • 数据和索引放在一个目录，数据很多的时候，会慢一点

• MEMORY存储引擎

  • Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
  • MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围
  • Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

• MERGE存储引擎

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

2.5.1 MySQL表级锁、行级锁和页面锁的区别

• myisam存储引擎默认是表级锁
  • 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
• innodb存储引擎默认是行级锁
  • 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• DBD存储引擎默认是页面锁
  • 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

---

三、事务 3.1 事务的四大特性（ACID）

• 原子性 Atomicity：

  • 事务是最小的执行单位，一次事务的操作，要么都成功，要么都失败

• 一致性 Consistency：

  • 一次事务操作后，最终的结果和预期的结果是一致的

• 隔离性 Isolation：

  • 多个事务之间是相互隔离的，互不干扰

• 持久性 Durability：

  • 提交事务后，数据会永久的保存在磁盘/硬盘中

3.2 在不考虑隔离性的前提下，事务的并发会产生的问题

• 脏读 dirty read：

  • 读到了未提交事务的数据

• 不可重复读 non-repeatable read：

  • 一次事务中多次查询同一数据，结果不一致。有人修改数据
  • 在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复

• 幻读 phantom read：

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

• 那么从形成的原因来看，不可重复读大多由update,delete造成，而幻读一般由insert造成。

3.3 为了解决这些问题，就有了"隔离级别"的概念。

• 你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。
• SQL标准的事务隔离级别包括：
  • 读未提交（read uncommitted）：
    • 一个事务还没提交时，它做的变更就能被别的事务看到。（什么都不能解决）
  • 读提交（read committed）：
    • 一个事务提交之后，它做的变更才会被其他事务看到。
    • 只能解决脏读，Oracle默认事务隔离级别
  • 可重复读（repeatable read）：
    • 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
    • 可以解决脏读和不可重复读，MySQL默认事务隔离级别
  • 串行化（serializable ）：
    • 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
    • 什么都能解决

3.4 深入解析隔离级别

• 若隔离级别是“读未提交”， 则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。因此，V2、V3也都是2。
• 若隔离级别是“读提交”，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以， V3的值也是2。
• 若隔离级别是“可重复读”，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看， V1、V2值是1，V3的值是2。

• 在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
• 在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
• 在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。
• 这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；
• 而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

3.5 事务的传播性

• PROPAGATION_REQUIRED:
  • 如果存在一个事务，则支持当前事务。如果没有事务则开启
• PROPAGATION_SUPPORTS:
  • 如果存在一个事务，支持当前事务。如果没有事务，则非事务的执行
  • 随缘
• PROPAGATION_REQUIRES_NEW:
  • 总是开启一个新的事务。如果一个事务已经存在，则将这个存在的事务挂起
  • 老子要最新的
• PROPAGATION_NOT_SUPPORTED:
  • 总是非事务地执行，并挂起任何存在的事务
  • 老子啥都不要
• PROPAGATION_MANDATORY:
  • 如果已经存在一个事务，支持当前事务。如果没有一个活动的事务，则抛出异常
• PROPAGATION_NEVER:
  • 总是非事务地执行，如果存在一个活动事务，则抛出异常
• PROPAGATION_NESTED：
  • 如果一个活动的事务存在，则运行在一个嵌套的事务中. 如果没有活动事务, 则按TransactionDefinition.PROPAGATION_REQUIRED 属性执行

3.6 事务的启动方式

• 显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。
• set autocommit=0，
  • 这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。

---

四、乐观锁和悲观锁的区别 4.1 事务并发问题：更新丢失

解决方法：给查询加锁

4.2 悲观锁（synchronized/lock）

问题：如果操作量比较大的话，效率太低了

4.3 MySQL修改数据时，如何实现悲观锁

• 使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

4.4 乐观锁 （CAS ：compare and swap）

问题一：多次循环尝试修改数据失败，会对CPU造成额外的消耗（针对自旋指定次数
问题二：ABA问题，在我修改之前，有人多次修改了（加版本号）

4.5 建表

表中添加一列 versio，作为版本列

4.6 实体

添加注解：@Version

@Data
@TableName("tb_test")
public class TbTest {
    @TableId(type = IdType.UUID)
    private String id;
    private String name;
	// 声明此属性为版本列
    @Version
    private Integer version;
}

4.7 安装插件

4.8 测试

@Test
    public void test1(){
        // 先查询，再更新
        User user = userMapper.selectById(1269634597069430785l);
        user.setUsername("zz");
        // 更新时，会自动维护version列
        //UPDATE t_user SET username=?, age=?, create_time=?, version=? WHERe id=? AND version=? 
        //Parameters: zz(String), 15(Integer), 2020-06-09(Date), 4(Integer), 
        //            1269634597069430785(Long), 3(Integer)
        userMapper.updateById(user);
    }

---

五、SQL优化的具体方案

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
  • 如：select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0
• 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
• 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
  • 如：select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20
• in 和 not in 也要慎用，否则会导致全表扫描
  • 如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
• 下面的查询也将导致全表扫描：select id from t where name like ‘%abc%’
• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
  • 如： select id from t where num/2=100 应改为: select id from t where num=100*2
• 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
  • 如：select id from t where substring(name,1,3)=‘abc’ --name以abc开头的id 应改为: select id from t where name like ‘abc%’
• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
• 不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)
• .很多时候用 exists 代替 in 是一个好的选择 select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)
• 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
  一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
• 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
• 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
• 避免频繁创建和删除临时表，以减少系统表资源的消耗。
• 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
• 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
• 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
• 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
• 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
• 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
• 尽量避免大事务操作，提高系统并发能力。
• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。ava

---

六、面试高频问题 6.1、mysql插入时主键自增问题

一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 Mysql 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15 ？

• 如果表的类型是 MyISAM，那么是 18

  • 因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里，重启 MySQL 自增主键的最大

    ID 也不会丢失

• 如果表的类型是 InnoDB，那么是 15

  • InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或者是对表进行

    OPTIMIZE 操作，都会导致最大 ID 丢失

mysql5.*之后 表的类型都为innoDB

6.2、mysql的存储引擎及区别

innoDB : 支持事物，默认是表级锁，但支持行级锁（只有索引失效，进行全盘扫描时才会升级表级锁）InnoDB就是使用的B+Tree

MYISam: 不支持事物，只有表级锁， 更适用于读多写少的数据

6.3、mysql优化实际操作

• 查询语句的书写上作优化，比如查询是要返回必要数据，减少通讯数据，不要使用select * 进行查询，
• 查询加上limit限制等（当确定查询只有一个时加上limit 1），查到后就不会继续向下扫描了
• 查询语句是加上explain关键字,查寻、分析sql的执行计划，看是否引起全表扫描，是否使用索引
• 比如建表时数据类型，就是越小的越简单的越好
• 还有外键约束也是会影响删除等操作效率的，在保证表结构的前提下，去除外键索引
• 避免在where语句后使用函数
• 避免使用not in
• 最好每个字段都是非空的 notnull
• 选择适当的存储引擎
• 为适当的字段添加索引。索引的“最左前缀原则 ， 覆盖索引
  • 查多写少的表适合建索引
  • 查询频繁的字段适合建索引
  • 重复率少的是个建索引

6.4、mysql索引

• 索引是一种数据结构，用于对数据库中数据进行快速检索
• 常见索引结构hash、b+tree innoDB默认是b+tree
• hash、b+tree区别
• hash不能进行范围查询（会进行全表扫描），只能等值查询
• hash不能进行排序
• 大量重复键hash会效率很低，因为存在hash碰撞问题
• 而b+tree是多路平衡数，节点是天然的有序的，可以做排序
• 因为有序做范围查寻也不需要全表扫描
• 聚簇索引、非聚簇索引
• b+tree的叶子节点存的是整行数据的就为主键索引，也称聚簇索引
• 而只存储主键id（主键值）的就为非主键索引，也称非聚簇索引
• 聚簇索引查询效率更快，因为非聚簇索引在查询到主键id后还需要进行回表
• 非聚簇索引都要回表多次吗
• 不是，覆盖索引不需要回表多次
• 覆盖索引（也称索引覆盖）就是在索引中就可一拿到查询所需要的值，不需要再去回表查询
• 联合索引、最左前缀匹配
• 是将查询次数多的做索引
• 联合索引：在创建多列索引时，将使用最多的索引放在最左，mysql会根据最左前缀匹配原则，优先检索最左边索引
• 如建立一个联合索引（key1 key2 key3） 就相当于建了 key1 、 key1key2、key1key2key3 三个索引
• Mysql5.6对索引做的优化
• 索引下推 ：可以在模糊查询是减少回表次数
• 怎么确定有没有走索引查询
• 可以通过添加 explain 关键字来查看sql语句的执行计划，来分析索引
• 那什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？
• 他内部有个查询优化器，会选则一个成本最低的，

普通索引：普通索引没有特殊限制

唯一索引：数据只有一个

主键索引：特殊的唯一索引，不能为空，一张表只有一个主键索引，是一个约束

组合索引：多个字段组合索引，可以加快检索数度，遵循最左前缀原则

6.5、事务四大特性

A ：原子性 一次事物为一个不可在分割原子操作，要么全部成功，要么全部失败

C： 一致性 一次操作后，最终结果与预期结果一致

I： 隔离性 多个事物操作之间相互隔离，互不影响

D： 持久性 一次事物commit后就会进行持久化存储到磁盘中

6.6、不考虑隔离情况，并发情况下事物会产生的问题

脏读 ： 读到未提交数据

不可重复读： 一次事物中多次查询相同数据，结果不同，有人修改数据

幻读： 一次事物中多次查询相同数据，结果不同，有人增删数据

6.7、数据隔离级别（主要就是解决并发下上面事物产生的问题）

read uncommit ： 读未提交 —》什么也不能解决

read commit ： 读已提交 —〉 解决脏读问题 oracle 默认隔离级别

repeatable read ： 可重复读 — 》 解决不可重复读问题 mysql默认隔离级别

Serializable ： 串行化 —〉 什么都能解决

6.8、事物的传播特性

REQUIRED : 如果之前有事物就加入，没有事物就创建一个事物

SUPPORT: 如果之前有事务就加入，没事务也不会自己创建

REQUIRED-NEW ： 只是用自己的事务，不管有没有都新建

NOT-SUPPORT： 不管有没有事物都不用

6.9、mysql的多表查询<内链接和外连接区别>

inner join（join on）：内链接 ，查询出有关联数据

left/right join ：查询出基础表的所有数据

6.10、数据库三大范式

• 第一范式： 表的每一列都是不可再分割的原子数据项
• 第二范式： 第一范式的基础上，非码必须完全依赖主码（消除非码对主码的部分函数依赖）
  • 函数依赖： 通过A属性组可以确定一个B属性的值，就说明 B 函数依赖 A。（学号 —〉姓名）
  • 完全函数依赖： 必须通过 A属性组所有属性才能确定B，就是B完全依赖于A。
    • （学号，课程名称） --> 分数完全依赖
  • 部分函数依赖： 只需部分 A属性组属性就可以确定B，
    • （学号，课程名称） – > 姓名只依赖学号
  • 传递函数依赖： A属性组属性 可以确定唯一属性组属性B ，而通过属性组属性B 又可以确定唯一属性组属性 C 这就是 C 属性 传递依赖 于 A属性
    • 学号–>系名，系名–>系主任
  • 主码： 一个表中，一个属性或属性组被其他属性完全依赖，就为主属性，也称主码
  • 非码： 非主码都为非码
    repeatable read ： 可重复读 — 》 解决不可重复读问题 mysql默认隔离级别

Serializable ： 串行化 —〉 什么都能解决