【应届应知应会】SQL常用知识点50道

SueWakeup

个人主页：SueWakeup

系列专栏：借他一双眼，愿这盛世如先生所愿

个性签名：人生乏味啊，我欲令之光怪陆离 

本文封面由 凌七七~❤ 友情提供

 目录

数据库的概念 (什么是数据库) 

RDBMS

NOSQL

 数据库的分类

关系型数据库(RDBMS)与(NOSQL)非关系型数据库的比较 

主流的NOSQL产品 

基础篇 

1. SQL数据库结构

2. SQL语句分类 

3. SQL语句的执行流程 

4. 存储引擎 MyISAM 和 InnoDB的区别 

5. MySQL设计三大范式

6. 什么是 SQL 注入

7. 内连接和外连接的区别 AND 左连接和右连接的区别

8. Union 和 Union all 的区别

9. MySQL 如何取差集

10. Delete 和 Truncate的区别

11. Count(*) 、Count(1)、Count(column) 的区别

12. 模糊查询使用什么关键词？ %和 _有什么区别？

13. 慢查询的概念

14. varchar和char的区别

视图篇

15. 视图的理解

16. 视图的缺点

 索引篇

17. 什么是索引

18. 索引的特性

19. 索引的分类

20. 索引的优点及缺点

优点

缺点

21. 如何添加/ 删除索引(index)

 主键索引 (primary key)

唯一索引 (unique)

普通索引 (index)

全文索引 (fulltext)

联合索引 (复合/多列索引)

22. 主键索引和唯一索引的区别

23. 什么是联合索引？什么情况下会触发？

24. 如何添加/ 删除约束(constraint)

主键约束 (primary key)

唯一约束 (unique)

外键约束 (foreign key)

非空约束 (not null)

检查约束 (check)

25. 索引和约束的关系及区别

26. B+Tree 索引（MySQL 5.5 之后默认）

26.1 B+树执行查找操作

27. B树和B+树的区别？

28. MySql为什么使用B+树，不用红黑树？

29. 哈希索引

30. 什么是回表？

31. 索引覆盖

好处

措施

32. 索引的使用场景

33. 索引的失效场景

34. 索引的优化

35. SQL优化

 事务篇

36. 什么是事务

37. MySQL 事务的四大特性

38. NOSQL 的 BASE 理论

39. MySQL 实现事务的步骤

40. 数据库事务的实现原理

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

脏读

不可重复读

丢失修改

幻读

42. 不可重复度和幻读的区别？

43. 事务的隔离级别

读未提交

读已提交

可重复读

串行化

锁篇

44. MySQL中的锁

45. 悲观锁和乐观锁的区别

悲观锁

乐观锁

46. 什么是MVCC？

存储过程篇 

47. 存储过程

48. 为什么使用存储过程

49. 存储过程和函数的区别

50. 存储过程的缺陷

手机端浏览本文章可能会出现 “目录”无法有效展示的情况，还请谅解！ 

数据库的概念 (什么是数据库) 

        长期存放在计算机内，有组织、可共享的大量数据的集合，是一个数据“仓库”。

  • 数据库分为：关系型数据库和非关系型数据库
  • MySQL的特点：操作便捷、小巧且功能齐全、免费开源的数据库，可运行于 windows 或 linux 系统

RDBMS

  • 全称 “Relational Database Management System”，意为 “关系型数据库管理系统”。

        使用 SQL（结构化查询语言）进行数据的查询和管理，将数据组织为表格，每个表格包含多行和多列记录与字段。

NOSQL

  • 全称 “Not Only SQL”,意为 “不局限于 SQL”。

        通常对数据进行非规范化处理，用于大规模数据应用，特别是分布式数据存储和大数据应用。

 数据库的分类

数据库分类数据模型举例
关系型（RDBMS）表格Oracle、MySQL、SQL Server等
非关系型（NoSQL）键值对（K-V）、文档（Document）、列族（Column Family）、图形MongoDB、Redis、Elasticsearch(引擎)、Cassandra等
对象型

以对象作为基本单位

支持面向对象的数据建模和操作

db4o、Versant等
XMLXML文档eXist、BaseX等
图形

图形结构数据

适用于网络关系、社交网络等场景

Neo4j、FlockDB等
内存存储在内存中SAP HANA、MemSQL等
时间序列存储时间序列InfluxDB、Prometheus等

关系型数据库(RDBMS)与(NOSQL)非关系型数据库的比较 

NoSQL关系型数据库
优点
  1. 简单易部署，低成本
  2. 查询速度快（数据存储于缓存中）
  3. 存储数据多样
  4. 扩展性好
  1. 技术又成熟又早，提供丰富的技术支持和生态系统
  2. 标准化查询语句，对数据的操作和管理更方便灵活
  3. 支持事务管理，确保数据的一致性、完整性和持久性
  4. 存储基础数据，对数据的管理直观简单
缺点
  1. 相较于关系型数据库发展时间短，维护工具和资料有限
  2. 不遵循 SQL 标准，提升了学习和使用成本
  3. 不提供关系型数据库对事务的处理
  1. 相较于 NoSQL 成本高
  2. 存储在磁盘中，速度不及 NoSQL
  3. 仅能存储基础数据
  4. 类似 join 的多表查询机制限制了扩展性

主流的NOSQL产品 

键值对（K-V）存储列族存储文档型存储图形数据库
相关产品Redis、Voldemort、Berkeley DBCassandra，HBase,RiakMongoDB、Elasticsearch、CouchDBNeo4j、InfoGrid、Infinite Graph
典型应用内容缓存，主要用于处理大量数据的高访问负载分布式的文件系统Web应用（与 K-V 类似，V是结构化的）社交网络
数据模型一系列键值对以列族式存储，将同一列数据存在一起一系列键值对图结构
优势快速查询查找速度快，可扩展性强，更容易进行分布式扩展数据结构要求不严格利用图结构相关算法
劣势存储的数据缺少结构化功能相对局限缺乏统一的查询语法需要对整个图做计算才能得出结构，不容易做分布式的集群方案

基础篇 

1. SQL数据库结构

  • 数据库（Database）：以文件的形式存放在磁盘上，即对应于一个或多个物理文件
  • 数据表（Table）：由一组数据记录组成，数据库中的数据以表为单位进行组织
  • 字段(Field)：也称域。表中的每一类称为一个字段。每个字段都有相应的描述信息。
  • 记录(Record)：表中的每一行称为一个记录，由若干字段组成。
  • 索引(Index)：一种特殊类型的表，其中含有关键字段的值和指向表实际记录位置的指针，可以提高访问数据库的效率。
  • SQL语句：结构化查询语句命令，用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库脚本文件的扩展名。

2. SQL语句分类 

名词解释命令

DDL

（数据定义语言)

定义和管理数据对象，

如数据库，数据表等

create、drop、alter

DML

（数据操作语言）

用于操作数据库对象中所包含的数据

insert、update、delete

DQL

（数据查询语言）

用于查询数据库数据select

DCL

（数据控制语言）

用来管理数据库的语言，包括管理权限及数据更改

grant、commit、rollback

3. SQL语句的执行流程 

MySQL请求执行流程示意图
  1. 词法/语法解析（Parser）：在收到用户发送的SQL请求后，将请求中的SQL语句按照语法规则分割成一个个单词（tokens）。例如，SELECT、FROM、WHERE、AND、OR等都是一个单词。对单词进行语法解析，判断SQL语句是否符合语法规则。如果不符合，则会返回 “语法错误” 的结果。
    MySQL语法解析错误返回结果图
  2. 语义分析（Resolver）：对SQL语句进行语义分析，如表名、列名、索引类型等，确保它们都是正确的。如果有错误，会抛出语义错误（错误的表名、列名、数据类型不匹配、引用不存在的对象、违反了数据库的约束等）。
    MySQL语义解析错误返回结果图其一
  3. 逻辑改写（Transformer）：采用不同的改写策略（基于规则 / 基于代价）对用户的请求字符串进行改写并生成多个版本的执行计划，使用成本模型估计每种改写后的执行计划的成本，最终让优化器选择成本最低的执行计划来执行查询。
  4. 优化器（Optimizer）：对SQL语句进行优化，如选择最优的执行计划、使用索引等来提高查询效率。
  5. 代码生成器（Code Generator）：将优化器生成的执行计划转换为可执行代码。
  6. 执行SQL（Executor）：根据优化后的执行计划，执行SQL语句。执行过程中，会进行锁定、缓存、排序、分组等操作。
  7. 执行计划缓存（Plan Cache）：SQL执行引擎将SQL第一次生成的执行计划缓存在内存中，后续对该SQL的重复执行可以复用这个计划，避免重复查询优化的过程。

4. 存储引擎 MyISAM 和 InnoDB的区别 

MyISAMInnoDB
外键不允许创建外键支持外键
事务不支持事务事务型数据库引擎，可以使用 Commit 和 Rollback 语句
并发只支持表级锁(table-level locking)支持行级锁(row-level locking)和表级锁，默认为行级锁；
备份(缓存)只缓存索引，不缓存真实数据支持在线热备份(缓存索引和真实数据)
崩溃恢复MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

5. MySQL设计三大范式

数据库设计范式是数据库在设计过程中，需要遵守的设计准则，避免数据冗余；

特殊情况不一定要三个范式都满足，数据库设计看重需求与性能，需求>性能>表结构

  • 第一范式（1NF）：每个字段都是最小字段，具有原子性，不可再分； 确保每列保持原子性
  • 第二范式（2NF）：每张表必须存在主键，并且其它字段也必须与主键直接依赖，不能仅仅与主键的某一部分依赖（联合主键） 确保表中的每列都和主键相关
  • 第三范式（3NF）：每个字段不能与主键间接依赖，每张表只保存一种数据，表和表之间使用外键关系关联； 确保每列都和主键列直接相关，而不是间接相关

6. 什么是 SQL 注入

  • 攻击者通过用户输入的字符串内容中加入 SQL 语句，与程序中的原 SQL 语句之间产生拼接，形成恶意查询、非法命令执行等攻击方式
  • 避免方式：过滤输入内容和使用参数化传值（？占位符）

7. 内连接和外连接的区别 AND 左连接和右连接的区别

  1. 内连接是保证两个表中所有行都满足连接条件,join不加前缀默认为 inner join
  2. 外连接结果仅包含符合连接条件的行
  3. 外连接不仅包含符合连接条件的行，还包含左表、右表或两个连接表中的所有数据行

外连接分为：①左外连接、②右外连接、③全外连接

  • 左外连接：保存左表全部数据，右表保留符合条件的行数据
left outer join || left join
  • 右外连接：保存右表全部数据，左表保留符合条件的行数据
right outer join || right join
  • 全外连接：保留所有行数据
full outer join || full join

8. Union 和 Union all 的区别

  1. union 是对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
  2. union all 也是对两个结果集进行并集操作，包括重复行，不会对结果进行排序

9. MySQL 如何取差集

  1. 当对比的字段存在索引时，使用 NOT EXISTS效率高
  2. 当对比的字段没有索引时，使用 left join || right join效率高

10. Delete 和 Truncate的区别

  1. Delete语句后可写条件，Truncate不可用
  2. Delete语句是逐条记录删除，且删除的记录都会写入日志；Truncate一次性删除整个数据页，日志只记录页释放
  3. Delete删空表后，会保留一个空的数据页，Truncate 在表中不会留有任何数据页
  4. 当使用行级锁执行 Delete语句时，将锁定表中各行以便删除；Truncate始终锁定页和表
  5. 如果有identity产生的自增id列，Delete后仍会从上次的数字开始增加，种子不变，而Truncate后，种子会恢复初始

11. Count(*) 、Count(1)、Count(column) 的区别

  • 执行方式
    • count(1)计算当前查询结果存在多少个常量值 1;
    • count(*)会把*号替换为所有字段名，用于计算查询结果中每个字段存在多少个值;
    • count(column)会统计指定列中非NULL值的行数
  • 执行效率
    • 如果存在主键，count(主键)效率最高，其次是 count(column) > count(1) > count(*)

12. 模糊查询使用什么关键词？ %和 _有什么区别？

  • like 关键词默认是大小写敏感，如果需要进行不区分大小写的模糊查询，可以使用collate 子句指定不区分大小写的字符集，或者使用 lower 或 upper 函数将列值转换为小写或大写进行比较
  • %：表示零个或多个字符。当使用%通配符时，它可以匹配任意长度的字符串，包括空字符串
例如，WHERE column_name LIKE 'abc%'会匹配以"abc"开头的任意字符串。
  • _：表示一个字符。当使用_通配符时，它可以匹配一个任意字符的位置
例如，WHERE column_name LIKE 'a_c'会匹配"a"后面跟着一个任意字符，然后是"c"的字符串。

13. 慢查询的概念

  • MySQL默认10秒内没有响应SQL结果则为慢查询
  • MySql对慢查询的操作：
-- 显示到mysql数据库的连接数
show status like 'connections';
-- 查询慢查询的状态
Show variables like '%slow_query%';
-- 设置慢查询到表 mysql.slow_log
set global log_output='TABLE';
-- 设置慢查询的时间
set global long_query_time=3;
-- 开启慢查询
set global slow_query_log='ON';
-- 慢查询的次数
show status like 'slow_queries';
-- 慢查询记录
select * from mysql.slow_log;
-- 慢查询sql语句
select convert(sql_text using utf8) sql_text from mysql.slow_log
-- 关闭慢查询
set global slow_query_log='OFF';

14. varchar和char的区别

  1. char类型的长度固定,如果存储的字符长度小于指定长度，将会用空格进行填充，查询时可以更快定位
  2. varchar根据实际字符串来动态分配存储空间，最大长度是创建表时指定的，需要进行额外的长度判断

视图篇

15. 视图的理解

  • 视图是一个虚拟表，数据来自一个表或多个表，表中的数据发生改变，显示在视图中的数据也会发生改变
  • 视图用于隐藏敏感数据
  • 视图可降低复杂的查询，易于理解和使用

16. 视图的缺点

缺点描述
性能下降在查询视图时，数据库系统需要执行视图的定义以生成结果集，这可能会导致性能下降
更新限制某些视图是不可更新的，特别是当视图的定义中包含了聚合函数、group by 子句或 distinct 关键字时。即使视图是可更新的，更新操作也可能会受到限制，因为数据库系统需要确保更新操作不会破坏视图的定义
增加依赖性和耦合性使用视图可能会增加数据库对象之间的依赖性和耦合性。当视图被其他查询或存储过程引用时，修改视图的定义可能会影响到引用该视图的其他对象，导致系统的复杂性增加
查询透明度降低视图隐藏了底层表的细节，使得查询的透明度降低。当开发人员查询视图时，可能不清楚视图是如何定义的，从而增加了理解和调试的难度
增加维护成本在数据库中添加、删除或修改视图可能会增加维护成本。特别是当数据库模式发生变化时，需要确保视图的定义与底层表的结构保持一致，否则可能会导致查询失败或产生不正确的结果

 索引篇

17. 什么是索引

  • 索引是一种用于快速查询和检查数据的数据库存储结构，保存了数据库指定字段的数据位置
  • MySQL 最经常用的存储结构： B+Tree 和 HashTable

作用：提升数据库的查询性能，如果没有索引，数据库的查询会进行全表搜索，消耗时间，造成大量磁盘的IO操作；如果建立索引，则通过索引中所保存的数据位置，快速找到表中的对应记录

18. 索引的特性

  1. 高效性：利用索引可以提高数据库的查询效率
  2. 唯一性：索引可以确保所查的数据的唯一性
  3. 完整性：加速表和表之间的连接，实现表与表之间的参照完整性

19. 索引的分类

分类方式分类描述
存储方式B+Tree索引InnoDB 存储引擎的 B+Tree 索引分为主键索引和辅助索引
哈希索引自适应哈希索引
逻辑主键索引主键列使用索引
辅助索引唯一索引保证该数据列的唯一性，允许数据为Null，但不能出现重复数据，一张表允许创建多个唯一索引
普通索引为了快速查询数据，一张表允许创建多个普通索引，允许数据重复和 Null
前缀索引只适用于字符串类型的数据，对文本的前几个字符创建索引，相比普通索引建立的数据更小
全文索引为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术
使用字段单列索引针对单个列创建的索引，当查询条件只涉及单列时，可以有效提高查询的性能
组合索引针对多个列创建的索引，当查询条件涉及到多个列时，可以提供更好的性能，查询时必须按照索引的顺序提供条件

20. 索引的优点及缺点

优点

  1. 加快数据的检索速度，减少数据库需要扫描的数据行数
  2. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性

缺点

  1. 创建索引和维护索引需要耗费许多时间
  2. 对表中数据进行增删改的时候，如果数据有索引，索引也需要动态的修改，降低SQL的执行效率
  3. 索引需要物理文件存储，耗费一定空间
  4. 如果数据库的数据量比较小，那么使用索引也不能带来很大提升

21. 如何添加/ 删除索引(index)

  • 约束实际上就是表中数据的限制条件,目的是为了保证表中的记录完整和有效

 主键索引 (primary key)

  • 确保表中每一行都有一个唯一的标识符
  • 可在其他表中作为外键引用，建立表与表之间的关系
-- 创建表后添加主键索引
alter table 表名 add primary key(字段，字段1...);
-- 移除主键
alter table 表名 drop primary key;

唯一索引 (unique)

  • unique 约束的字段，具有唯一性，不可重复，但可以为null
-- 创建表后添加唯一索引
alter table 表名 add unique(字段，字段1...);
-- 删除唯一索引
alter table 表名 drop index 唯一索引名;

普通索引 (index)

  • 允许列中有重复的值，不会限制数据的插入
  • 提高查询性能，尤其是在经常需要搜索的列上
-- 创建表后添加普通索引
alter table 表名 add index 索引名 (字段);
-- 删除普通索引
alter table 表名 drop index 索引名;

全文索引 (fulltext)

  • 只能用于CHAR、VARCHAR或TEXT类型的列
  • 用于全文搜索，适用于文本搜索，如文章、博客等
  • 提供了强大的文本匹配和搜索功能，特别是使用MATCH AGAINST操作时
-- 创建表后添加全文索引
alter table 表名 add fulltext(字段,字段1...);
-- 移除全文索引
alter table 表名 drop index 全文索引名;

联合索引 (复合/多列索引)

  • 在数据库表中的两个或更多列上创建的索引
  • 按照索引创建时定义的列的顺序存储索引值，并且通常遵循最左前缀原则提高多条件查询的性能(特别是当查询中包含索引的所有列或前导列时)
-- 创建表后添加联合索引
alter table 表名 add index (字段,字段1,字段2...);
-- 移除联合索引
alter table 表命 drop index 联合索引名;

22. 主键索引和唯一索引的区别

  1. 定义数量：每张表只能有一个主键，而可以有多个唯一索引。主键是用来唯一标识表中的每一行记录的，而唯一索引可以用来保证某一列或多列的取值唯一。
  2. 索引特性：主键索引是一种特殊的唯一索引，它具有自动递增的特性（如果是整数类型），并且不允许为空。主键索引在物理存储上一般会被优化为聚簇索引，即按照主键值的顺序将数据存储在磁盘上。而唯一索引没有这些特性，可以包含空值。
  3. 查询性能：由于主键索引的特殊性，当使用主键进行查询时，MySQL可以直接定位到相应的行，因此查询速度较快。而唯一索引在查询时需要进行额外的查找操作。
  4. 重复值：主键索引要求列的值必须唯一且非空，而唯一索引则允许有重复值，但对于重复值的插入操作会失败。
  5. 外键关联：主键索引通常被用作外键关联的参考，而唯一索引可以作为外键的参考，但不是必须的。

23. 什么是联合索引？什么情况下会触发？

  • 在MySQL中，联合索引也称为复合索引或多列索引，是指将多个列组合起来创建的一个索引。它可以包含多个列，并且查询时可以使用这些列的任意组合进行查找
  • 当查询语句的条件涉及到联合索引的所有列或部分列时，MySQL会尝试使用联合索引来加速查询，减少扫描的数据量。具体触发联合索引的情况包括：
  1. 查询条件中涉及到联合索引的所有列：如果查询语句的条件中使用了联合索引的所有列，并且按照索引列的顺序使用，MySQL可以高效地使用联合索引进行查找
  2. 查询条件中涉及到联合索引的部分列：如果查询语句的条件中使用了联合索引的部分列，并且按照索引列的顺序使用，MySQL也可以部分利用联合索引进行查找。这种情况下，MySQL会先根据索引找到满足条件的记录，然后再进行进一步的过滤
  • 当查询条件中只使用了联合索引的某个列而没有使用其他列时，并不会触发联合索引的使用。此时，MySQL可能会选择其他适用的索引或者进行全表扫描

24. 如何添加/ 删除约束(constraint)

主键约束 (primary key)

  • 与主键索引类似

唯一约束 (unique)

  • 与唯一索引类似

外键约束 (foreign key)

  • 维护表与表之间的关系，确保数据的引用完整性
  • 防止在子表中插入不存在的父表中的值
-- 创建表后添加外键约束
alter table 表名  add foreign key(字段) references 父级表名(父级表字段);
-- 移除外键约束
alter table 表名 drop foreign key 外键名;

非空约束 (not null)

  • 用于保证数据的完整性，防止出现无效的数据
  • 确保列中的数据不能有NULL值，必须提供非空的数据
-- 创建表后添加非空约束，且添加约束字段不能存在 NULL 值，否则失败
alter table 表名 modify 字段 字段类型 not null;
-- 移除非空约束
alter table 表名 modify 字段 字段类型 null;

检查约束 (check)

  • 从 MySQL 8.0.16 开始支持
  • 用于限制列中可以接受的值，比如年龄不能为负数
  • 允许定义列中的值的范围或条件，确保数据的合法性
-- 创建表后添加检查约束
alter table 表名 add check (字段限制范围);
-- 移除检查约束
alter table 表名 drop constraint DROP 检查约束名

25. 索引和约束的关系及区别

索引约束
关系

主键约束（primary key）和唯一约束（unique）会自动创建索引，以确保数据的唯一性和提高查询效率。

外键约束（foreign key）通常不会自动创建索引，但在实际应用中，为了提高查询性能，通常建议在外键列上手动创建索引。
区别目的主要用于提高查询性能。索引可以快速定位到表中的数据，减少全表扫描的次数，从而加快查询速度。主要用于保证数据的完整性和一致性。
类型包括主键索引、普通索引、唯一索引、全文索引、联合索引等。包括主键约束、外键约束、唯一约束、非空约束和检查约束等。
创建使用可以在创建表时定义，也可以在表创建后通过alter table语句或create index语句添加或删除。通常在创建表时定义，也可以在表创建后通过alter table语句添加或删除。
必要性是可选的，但通常为了提高性能而创建。并非所有列都需要索引，过多的索引可能会降低插入、更新和删除操作的性能。是数据库设计的重要组成部分，用于确保数据的准确性和可靠性。

26. B+Tree 索引（MySQL 5.5 之后默认）

  • 因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组
  • InnoDB的B+Tree索引分为主键索引和辅助索引
  • 主键索引的叶子节点 data域记录着完整的数据记录
  • 原则：尽量选择访问频率高的字段值作为主键索引
  • 辅助索引的叶子节点data域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主键索引中进行查找

26.1 B+树执行查找操作

  • 进行查找操作时，首先在根节点进行二分查找，找到对应的叶子节点。然后在叶子节点上进行二分查找，找出 key 所对应的 data
  • 区间查找操作时，由于叶子节点形成了有序列表，可以直接通过指针继续遍历相邻个叶子节点，提高区间查询效率

27. B树和B+树的区别？

区别B树B+树
节点结构B树的节点除了包含关键字和子节点指针外，还包含了数据记录的指针B+树的非叶子节点只包含关键字和子节点指针，所有的数据记录都存储在叶子节点中
叶子节点链表B树的叶子节点并没有形成一个链表，而是通过非叶子节点来进行定位B+树的叶子节点之间形成了一个有序的链表，便于范围查询和遍历整个表
查询方式B树可以直接从非叶子节点开始查找，然后按照关键字顺序遍历叶子节点B+树必须从根节点到叶子节点遍历整棵树才能找到所需的数据记录
磁盘读写B树需要遍历整棵树B+树的叶子节点之间形成了一个有序的链表，因此在进行范围查询时只需要遍历叶子节点链表即可
使用场景数据量较小或者需要支持随机访问需要支持范围查询或者大数据量的索引

28. MySql为什么使用B+树，不用红黑树？

区别红黑树B+树结论
磁盘读写需要进行随机读取，导致磁盘IO次数较多B+ 树的叶子节点形成了一个有序链表，可以通过顺序读取的方式高效地进行范围查询和遍历由于数据库系统通常面对大量的数据和频繁的查询操作，B+ 树的磁盘读写优化能够提升数据库的整体性能
数据有序性红黑树没有对数据进行排序，只是通过平衡性质来保证树的高度平衡将数据按照关键字排序存储在叶子节点中，并通过非叶子节点进行定位这种有序性使得 B+ 树在范围查询、排序等操作上具有更好的性能
持久化存储红黑树由于需要存储更多的指针信息，导致节点的大小相对较小，增加了磁盘IO次数，降低了写入性能B+ 树的节点结构相对简单，每个节点存储的关键字数量较多，使得每次写入磁盘的数据量较大，减少了磁盘IO次数数据库系统需要将索引结构持久化地存储到磁盘上，以防止系统故障时数据丢失

29. 哈希索引

  • 能以O(1)时间复杂度进行查找，但是失去了有序性
  • 无法用于排序和分组
  • 只支持精确查找，无法用于部分查找和范围查找
  • InnoDB存储引擎有一个特殊的功能叫”自适应哈希索引“，当某个索引值被使用的非常频繁时，会在B+Tree索引之上创建一个哈希索引，让B+Tree索引具有哈希索引的一些优点

30. 什么是回表？

  • 在使用索引进行查询时，如果查询需要返回的数据不在索引中，MySQL会根据索引中的数据行的主键值再次到表中取检索数据

31. 索引覆盖

  • 一个查询可以完全使用索引来满足，而无需访问实际的数据行

好处

  1. 减少磁盘 IO：从索引中获取，不需要回表访问实际的数据行
  2. 减少内存开销：当查询只涉及到索引列，MySQL只需要将索引数据加载到内存中
  3. 减少了网络传输开销：当数据库和应用程序分布在不同的服务器上时，索引覆盖可以减少从数据库服务器到应用服务器之间的网络传输开销

措施

  1. 使用合适的查询语句：编写查询语句时，明确指定需要返回的列，并确保这些列都包含在索引中。避免使用 select *，它可能无法实现索引覆盖
  2. 合理涉及索引：确保索引包含需要的所有列，尽量覆盖查询所需的列

32. 索引的使用场景

  1. 匹配全值：对索引中所有列都指定具体值，即对索引中的所有列都有等值匹配的条件。
  2. 匹配值的范围查询：对索引的值能够进行范围查找
  3. 匹配最左前缀：仅仅使用索引中的最左边列进行查询。比如组合索引（col1,col2,col3）
  4. 能够被col1，col1+col2，col1+col2+col3的等值查询利用到的。
  5. 仅对索引进行查询：当查询列都在索引字段中。即select中的列都在索引中。
  6. 匹配列前缀：仅仅使用索引的第一列，并且只包含索引第1列的开头部分进行查找。例
  7. 如：WHERE title LIKE ‘xxx%’
  8. 索引部分等值匹配，部分范围匹配
  9. 若列名是索引，则使用column_name is null就会使用索引

33. 索引的失效场景

  1. 使用模糊查询时，%在字符的左侧
  2. 组合索引包含从左到右的字段使用索引，不包含左边的字段索引失效
  3. 数据类型不匹配
  4. 不等于运算（!= 、 <、>、not in）
  5. 字段内容为 null
  6. 添加索引的字段上使用函数或计算
  7. or前后条件中的字段都包含索引或前后有一个字段不包含索引

34. 索引的优化

  1. 选择合适的字段创建索引
  2. 被频繁更新的字段应该慎重建立索引
  3. 尽可能考虑建立联合索引而不是单列索引
  4. 避免冗余索引
  5. 考虑在字符串类型的字段上使用前缀索引代替普通索引
  6. 避免 where子句中对索引字段使用函数，这会造成索引失效

35. SQL优化

  1. 在 SQL查询中禁止使用 select *，必须明确查询字段名称
  2. 对查询进行优化，尽量避免全表扫描，应考虑在 where 及 order by 涉及到的字段上建立索引
  3. 尽量避免在 where子句上对字段进行 null值判断，否则会导致引擎放弃使用索引进行全表扫描
  4. 避免在where子句中使用 !=或<>操作符
  5. 避免在where子句中使用or连接条件
  6. 慎用 in和not in，否则会导致全表扫描
  7. 严禁左模糊查询或者全模糊查询，因为索引采用 B-Tree存储，具有B-Tree的最左前缀匹配特性
  8. 尽量避免在 where子句中对字段进行算术运算操作
  9. 尽量避免在where子句中对字段进行函数操作
  10. 使用 explain查询语句，观察SQL执行计划

 事务篇

36. 什么是事务

  • 事务是数据库的一种特性，用于确保一个执行过程中所有步骤全部成功或者全部失败，是数据库操作的最小执行单元

37. MySQL 事务的四大特性

ACID vs BASE
  • 原子性：事务是数据库的最小执行单元，事务的原子性确保数据库操作过程中的所有步骤全部成功或者全部失败
  • 一致性：数据修改前和修改后的状态保持一致
  • 隔离性：一个事务的执行不会被其他事务干扰，一个事务内部的操作和使用的数据，在并发中对其他事务是隔离的
  • 持久性：事务一旦被提交，将对数据库中的数据的改变是永久性的

38. NOSQL 的 BASE 理论

  • BASE是对ACID（原子性、一致性、隔离性、持久性）事务特性的一个补充，反映了NoSQL 数据库在设计上对可用性和一致性取舍的不同。
  • 基本可用

        系统在面临任何故障时，仍然能够提供一定程度的服务。即使是在高负载或系统故障的情况下，系统也能够响应客户端的请求，尽管响应可能不是完全正常或快速的

  • 软状态

        数据的状态可以在多个节点之间同步，而不需要立即保持一致。数据可以在多个节点上存在短暂的差异，最终会通过某种机制（如网络同步、消息队列等）达到一致

  • 最终一致性

        系统不保证数据在任意时刻都是正确的，而是保证在一定时间后数据会收敛到正确的状态

  • 核心思想：在分布式系统中，由于网络延迟、节点故障等因素，数据的一致性可能无法实时保证，但是可以保证数据的最终一致性。

39. MySQL 实现事务的步骤

-- 关闭事务自动提交
set autocommit=0;
-- 开启一个事务，标记事务的起始点
start transaction；
-- 向数据库提交事务
commit;
-- 将事务回滚，所有数据库操作被取消
rollback;
-- 开启mysql自动提交
set autocommit=1;

40. 数据库事务的实现原理

  • MySQL InnoDB引擎使用 redo log（重做日志）保证事务的的持久性，使用 undo log（回滚日志）来保证事务的原子性
  • redo log 是 InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值。当MySQL意外宕机，InnoDB存储引擎会使用 redo log恢复，以此来确保数据的持久性
  • undo log保存了事务发生之前的数据的一个版本，用于回滚
  • MySQL InnoDB引擎通过锁机制、MVCC（多版本并发控制）等手段保证事务的隔离性
  • 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障

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

脏读

  • 一个事务正在访问数据并且对数据进行了修改，而修改操作还没提交到数据库中，这时另外一个事务也访问这个数据，然后使用这个数据。由于这个数据是没有提交的数据，那么另外一个事务读到的这个数据就是“脏数据”

不可重复读

  • 一个事务内多次读同一个数据，在这个事务还没结束时，另外一个事务也访问该数据，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务读取到的数据可能不一致，这就发生了在一个事务内两次读到的数据是不一致的情况，所以称为不可重复读

丢失修改

  • 一个事务读取一个数据时，另外一个事务也访问这个数据，在第一个事务修改这个数据后，第二个事务也修改了这个数据导致第一个事务的修改丢失

幻读

  • 与不可重复读类似，在第一个事务读取了几行数据，另一个事务插入了一些数据，在随后的查询中，第一个事务发现多了一些原本不存在的记录

42. 不可重复度和幻读的区别？

  • 不可重复读的重点是修改，多次读取一条记录，发现其中某些列的值被修改
  • 幻读的重点在于新增或删除，比如多次读取某条件下的记录，发现记录增多或减少

43. 事务的隔离级别

读未提交

  • 最低的隔离级别，允许读取尚未提交的数据变更，可能导致 脏读、幻读或不可重复读

读已提交

  • 允许读取并发事务已经提交的数据，可以阻止脏读

可重复读

  • 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读

串行化

  • 最高的隔离级别，完全服从 ACID 的隔离级别，所有的事务依次逐个执行，防止事务之间产生干扰，可阻止脏读、不可重复度以及幻读

锁篇

44. MySQL中的锁

  • 意向锁是InnoDB存储引擎特有的概念，它们是表级锁，用于表示事务对表中的行有意向进行共享或排他锁定。意向锁不会阻塞其他事务对表的访问，除非它们也尝试在同一行上获取排他锁。

45. 悲观锁和乐观锁的区别

悲观锁
  • 总是假设最坏的情况，每次读取数据都默认其他事务会修改数据，会进行加锁操作，操作之后释放锁
乐观锁
  • 一般情况不会造成冲突，在数据进行提交更新时，才会对数据的冲突与否进行检查，一般是通过版本号控制，适用于读多写少的场景

46. 什么是MVCC？

  1. MVCC是行级锁的一个变种，在很多情况下避免了加锁情况，开销更低。
  2. 大多数的MVCC都实现了非阻塞的读操作，写操作也只锁定必要的行；
  3. MVCC是一种用来解决 读-写冲突的并发控制，为事务分配单向增长的时间戳，为每个修改保存一个版本，每个事务都有一个对应版本的快照，快照版本按照单向增长的时间戳来决定先后顺序
  4. 读操作，只需要读该事务开始前的数据库快照，并不去读取正在修改的数据，仅读取事务开始前的最新版本

存储过程篇 

47. 存储过程

  • 在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后，再次调用不需要重复编译，用户通过指定存储过程的名字并给出参数来执行它
delimiter //
create procedure 存储过程名(
    [ [in | out | inout] 参数名 参数类型],
    ...
)
begin
    declare 变量 变量类型;
    -- 存储逻辑 ...
end;
//
delimiter ;
  • 查看存储过程： 
SELECT * FROM information_schema.routines WHERE routine_schema = '库名';
  • 删除存储过程
drop procedure 存储过程名;

48. 为什么使用存储过程

  1. 业务流程复杂：业务复杂时，SQL语句相互依赖，顺序执行
  2. 频繁访问数据库：每条SQL语句都需单独连接和访问数据库
  3. 先编译后执行：SQL语句的执行需要先编译

49. 存储过程和函数的区别

要素存储过程函数
关键字procedurefunction
执行可以独立执行必须依赖表达式的调用
返回值可以定义多个返回结果只有一个返回值
功能函数不易做复杂的业务逻辑，但是存储过程可以

50. 存储过程的缺陷

  1. 移植性：大多数关系数据库的存储过程存在细微差异
  2. 维护性：存储过程的维护成本高，修改调试较为麻烦
  3. 协作性：团队中对于存储过程的使用大多是依赖文档

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/768886.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

分布式锁——基于Redis分布式锁

分布式锁——基于Redis分布式锁

单机锁 服务器只有一个&#xff0c;JVM只有一个。 用synchronized加锁&#xff0c;对lock对象加锁&#xff0c;只有线程1结束&#xff0c;线程2,3才会开始。 再用uid避免一个线程多次进来。 分布式锁 真正上线时&#xff1a; 【注&#xff1a;这些服务器连接的是一个Redis集…
阅读更多...
FreeRTOS和UCOS操作系统使用笔记

FreeRTOS和UCOS操作系统使用笔记

FreeRTOS使用示例 UCOS使用示例 信号量使用 信号量访问共享资源区/ OS_SEMMY_SEM; //定义一个信号量&#xff0c;用于访问共享资源OSSemCreate ((OS_SEM* )&MY_SEM, //创建信号量&#xff0c;指向信号量(CPU_CHAR* )"MY_SEM", //信号量名字(OS_SEM_CTR )1, …
阅读更多...
STM32入门笔记(03)： ADC（SPL库函数版)（2）

STM32入门笔记(03)： ADC（SPL库函数版)（2）

A/D转换的常用技术有逐次逼近式、双积分式、并行式和跟踪比较式等。目前用的较多的是前3种。 A/D转换器的主要技术指标 转换时间 分辨率 例如&#xff0c;8位A/D转换器的数字输出量的变化范围为0&#xff5e;255&#xff0c;当输入电压的满刻度为5V时&#xff0c;数字量每变化…
阅读更多...
Google Play上架防关联，打包环境是关联因素之一还是无足轻重？

Google Play上架防关联，打包环境是关联因素之一还是无足轻重？

在Google Play上架应用&#xff0c;对于矩阵式上架或马甲包的开发者来说&#xff0c;防关联的处理技能是必须要精通的。想象一下&#xff0c;你辛辛苦苦开发的应用&#xff0c;因为一些看似微不足道的细节&#xff0c;比如打包环境的问题&#xff0c;就可能被谷歌无情下架或封号…
阅读更多...
力扣双指针算法题目：移动零

力扣双指针算法题目：移动零

1.题目 . - 力扣&#xff08;LeetCode&#xff09; 2.思路解析 这个题目的思路和“使用递归排序快速排序解决数组的排序问题”相同 class solution { public:void QuickSort(vector<int>& nums, int left, int right){if (left > right) return;int key left…
阅读更多...
信息收集---端口服务信息收集

信息收集---端口服务信息收集

1. 什么是端口 是设备与外界通讯交流的出口。端口可分为虚拟端口和物理端口&#xff0c;其中虚拟端口指计算机内部或交换机路由器内的端口&#xff0c;不可见。例如计算机中的80端口、21端口、23端口等。物理端口又称为接口&#xff0c;是可见端口&#xff0c;计算机背板的RJ45…
阅读更多...
我在高职教STM32——时钟系统与延时控制（2）

我在高职教STM32——时钟系统与延时控制（2）

大家好&#xff0c;我是老耿&#xff0c;高职青椒一枚&#xff0c;一直从事单片机、嵌入式、物联网等课程的教学。对于高职的学生层次&#xff0c;同行应该都懂的&#xff0c;老师在课堂上教学几乎是没什么成就感的。正因如此&#xff0c;才有了借助 CSDN 平台寻求认同感和成就…
阅读更多...
Http 实现请求body体和响应body体的双向压缩方案

Http 实现请求body体和响应body体的双向压缩方案

目录 一、前言 二、方案一（和http header不进行关联） 二、方案二（和http header进行关联） 三、 客户端支持Accept-Encoding压缩方式，服务器就一定会进行压缩吗？ 四、参考 一、前言 有时请求和响应的body体比较大，需要进行压缩，以减少传输的带宽。 二、方案一（和…
阅读更多...
助力构建新型电力系统自主可控实时底座

助力构建新型电力系统自主可控实时底座

近日&#xff0c;2024亚洲新型电力系统及储能展览会&#xff06;亚洲新型电力及储能论坛会在广州广交会展馆圆满落下帷幕&#xff01;科东软件携多款电力产品亮相展会,并在2024亚洲新型电力及储能论坛发表《“鸿道Intewell操作系统助力构建新型电力系统自主可控实时底座”》的主…
阅读更多...
Unity之创建与导出PDF

Unity之创建与导出PDF

内容将会持续更新&#xff0c;有错误的地方欢迎指正&#xff0c;谢谢! Unity之创建与导出PDF TechX 坚持将创新的科技带给世界&#xff01; 拥有更好的学习体验 —— 不断努力&#xff0c;不断进步&#xff0c;不断探索 TechX —— 心探索、心进取&#xff01; 助力快速…
阅读更多...
2024-2025年本田维修电路图线路图接线图资料更新

2024-2025年本田维修电路图线路图接线图资料更新

此次更新了2024-2025年本田车系电路图资料&#xff0c;覆盖市面上99%车型&#xff0c;包括维修手册、电路图、新车特征、车身钣金维修数据、全车拆装、扭力、发动机大修、发动机正时、保养、电路图、针脚定义、模块传感器、保险丝盒图解对照表位置等等&#xff01; 汽修帮手汽…
阅读更多...
日期和时区

日期和时区

日期 时区 修改时区可分为两步 删除系统自带的 localtime 文件 rm -f /etc/localtime 将系统中内置的 Shanghai 文件软连接到 /etc/localtime中 sudo ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
阅读更多...
Unity3D中，AI角色Rigidbody旋转导致的动画问题

Unity3D中，AI角色Rigidbody旋转导致的动画问题

在制作一些AI角色的时候&#xff0c;可能会运用到Rigidbody组件来使AI角色拥有一些相关的物理属性&#xff0c;但是AI角色在受到一些物理碰撞或者惯性等原因&#xff0c;会发生旋转导致动画出现意料外的错误&#xff0c;比如在由动转静的时候&#xff0c;可能会发生向前翻转等一…
阅读更多...
TP8/6 子域名绑定应用

TP8/6 子域名绑定应用

原www.xxx.com/admin改为admincms.xxx.com config/app.php
阅读更多...
【Linux】线程——线程的概念、线程的特点、线程的优点和缺点、线程和进程、线程函数的使用

【Linux】线程——线程的概念、线程的特点、线程的优点和缺点、线程和进程、线程函数的使用

文章目录 Linux线程1. 线程的概念1.1 什么是线程 2. 线程的特点2.1 线程的优点2.2 线程的缺点2.4 线程和进程 3. 线程函数的使用pthread_create() 创建线程pthread_self() 获取线程IDpthread_exit() 线程终止pthread_cancel() 线程取消pthread_join() 线程等待pthread_detach()…
阅读更多...
Element-UI - el-table中自定义图片悬浮弹框 - 位置优化

Element-UI - el-table中自定义图片悬浮弹框 - 位置优化

该篇为前一篇“Element-UI - 解决el-table中图片悬浮被遮挡问题”的优化升级部分&#xff0c;解决当图片位于页面底部时&#xff0c;显示不全问题优化。 Vue.directive钩子函数已在上一篇中详细介绍&#xff0c;不清楚的朋友可以翻看上一篇&#xff0c; “Element-UI - 解决el-…
阅读更多...
3.js - 色调映射（renderer.toneMapping）

3.js - 色调映射（renderer.toneMapping）

// ts-nocheck// 引入three.js import * as THREE from three// 导入轨道控制器 import { OrbitControls } from three/examples/jsm/controls/OrbitControls// 导入lil.gui import { GUI } from three/examples/jsm/libs/lil-gui.module.min.js// 导入tween import * as TWEEN…
阅读更多...
不同操作系统下的换行符

不同操作系统下的换行符

1. 关键字2. 换行符的比较3. ASCII码4. 修改换行符 4.1. VSCode 5. 参考文档 1. 关键字 CR LF CRLF 换行符 2. 换行符的比较 英文全称英文缩写中文含义转义字符ASCII码值操作系统Carriage ReturnCR回车\r13MacIntosh&#xff08;早期的Mac&#xff09;LinefeedLF换行/新行\…
阅读更多...
Windows下载安装配置并使用Redis（保姆级教程）

Windows下载安装配置并使用Redis（保姆级教程）

文章目录 1、Redis的下载与安装 2、Redis的使用 3、Redis的图形界面客户端 4、Redis开机自启动 1、Redis的下载与安装 下载Redis&#xff1a;https://pan.baidu.com/s/1zBonkO2y6AZeqCdRe0W5ow?pwd9999 提取码: 9999 下载后直接解压就可以使用了 2、Redis的使用 我们…
阅读更多...
全网最佳硕士研究生复试简历模板

全网最佳硕士研究生复试简历模板

硕士研究生复试简历模板 ✨ 简介 提供了一个适用于国内硕士研究生复试的个人简历模板。该模板通过统一的“样式”形成规范的Word格式&#xff0c;是目前研究生复试的最佳简历模板之一。模板使用“华文中宋”字体&#xff0c;如您的电脑中未安装此字体&#xff0c;请提前安装。…
阅读更多...
最新文章

Copyright @ 2022~2023