MySQL

黄淇楠
139 阅读16分钟

数据库的三范式是什么

第一范式：列不可再分
第二范式：行可以唯一区分，主键约束
第三范式：表的非主属性不能依赖于其他表的非主属性 外键约束
三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式 建立第一第二范式上。

B+树和B树的区别

B树（Balanced Tree）：

  1. 节点存储数据：每个节点既存储键值，也存储数据。
  2. 子节点数量：每个节点的子节点数可以不同，取决于树的阶数。
  3. 树的高度：通常较高，因为每个节点存储的数据较多。
  4. 叶子节点：叶子节点不需要连接，数据可以在任何节点上查找。

B+树（Balanced Plus Tree）：

  1. 节点存储键值：内部节点只存储键值，不存储数据，数据存储在叶子节点。
  2. 子节点数量：每个节点有固定数量的子节点，取决于树的阶数。
  3. 树的高度：通常较低，因为每个节点只存储键值，结构更紧凑。
  4. 叶子节点：叶子节点通过链表相连，方便区间查询。

共同点：插入元素时都是从根节点开始通过比较键值对插入的方式，如果结点满了通过递归分裂父节点的方式

如何排查慢SQL，如何优化？

首先分析硬件和网络，然后分析代码，算法比如多层嵌套循环
1.EXPLAIN输出执行计划
2.通过druid连接池的内置监控来定位慢SQL
3.通过MySQL的慢查询日志查看慢SQL（默认超过10s的查询会记录在日志）
4.通过show processlist，查看当前数据库SQL执行情况来定位慢SQL

MySQL 的优化

  • 索引优化：合理创建和选择索引类型,覆盖索引（所有列都可索引，无需回表查询），避免全表扫描，提高查询效率。避免索引失效。
  • 查询优化：通过分析查询计划（EXPLAIN），优化SQL语句，减少复杂的JOIN操作。

避免SELECT*： 尽量选择需要的列，而不是使用SELECT *，以减少不必要的数据传输。

使用LIMIT：在查询中使用LIMIT子句限制返回的行数，可以减少数据处理量。

优化JOIN操作：使用适当的JOIN类型（如INNER JOIN、LEFT JOIN）并确保连接条件有索引。

避免子查询：在某些情况下，可以用JOIN替代子查询，以提高性能。

使用适当的WHERE条件：确保WHERE子句中的条件尽量使用索引，并避免使用不必要的复杂表达式。

  • 表结构设计：规范化设计表结构，避免冗余数据和数据异常。
  • 分库和分表：对大表进行分库或分表，提高数据操作效率。
  • 缓存机制：使用Query Cache和第三方缓存（如Redis、Memcached）减少数据库查询压力。
  • 连接池：使用数据库连接池（如C3P0、HikariCP）管理数据库连接，提高并发处理能力。

事务并发问题有哪些

脏读：事务A读到了事务B修改还未提交的数据
幻读，也叫虚读：事务A两次读取相同条件的数据，两次查询到的数据“条数”不一致，是由于事务B再这两次查询中插入或删除了数据造成的
不可重复读：事务A两次读取相同条件的数据，结果读取出不同的结果，是由于事务B再这两次查询中修改了数据造成的
第一类丢失更新：也叫回滚丢失，事务A和事务B更新同一条数据，事务B先完成了修改，此时事务A异常终止，回滚后造成事务B的更新也丢失了
第二类丢失更新：也叫覆盖丢失，事务A和事务B更新同一条数据，事务B先完成了修改，事务A再次修改并提交，把事务B提交的数据给覆盖了

三种日志：

  1. 撤销日志（Undo Log）：开始前记录事务修改前的数据，用于事务回滚和MVCC，确保事务的原子性和一致性。
  2. 重做日志（Redo Log）：提交前记录事务的修改操作，用于崩溃恢复，确保事务持久性。 循环写入，满了就清空，只存储一段数据。
  3. 二进制日志（Binary Log, Binlog） ：记录所有修改数据的SQL语句，用于主从复制和数据恢复，不会清空。

MVCC，多版本并发控制，它是在读取数据的时候通过一种类似快照的方式将数据保存下来，不同的事物看到的快照版本是不一样的，即使其他事务修改了数据，但是对本事务仍然是不可见的，它只会看到第一次查询到的数据。

事务的四大特性

原子性：指的是一个事务应该是一个最小的无法分割的单元，不允许部分成功部分失败，只能同时成功，或者同时失败
持久性：一旦提交事务，那么数据就应该持久化，保证数据不会丢失
隔离性：两个事务修改同一个数据，必须按顺序执行，并且前一个事务如果未完成，那么中间状态对另一个事务不可见，MVCC ，读写锁
一致性：要求任何写到数据库的数据都必须满足预先定义的规则，它基于其他三个特性实现的（转账前后，总金额始终一致）

事务隔离级别：

读未提交(Read Uncommitted, RU)：事务读不阻塞其他事务的读和写，事务写阻塞其他事务的写但不阻塞读，能解决第一类丢失更新的问题（阻塞1个）
读已提交(Read Committed, RC)：事务读不阻塞其他事务读和写，事务写会阻塞其他事务的读和写，能解决第一类丢失更新，脏读的问题 。（阻塞2个）
可重复读(Repeatable Read, RR)：事务读会阻塞其他事务的写但不阻塞读，事务写会阻塞其他事务读和写，能解决第一类丢失更新，脏读，不可重复读，第二类丢失更新问题。（阻塞3个，解决所有问题，除了幻读）
串行化：使用表级锁，让事务一个一个的按顺序执行，能解决以上所有并发安全问题

阻塞只是阻塞其他事务对当前读取数据的操作，其他事务仍然可以操作其他数据，因此插入数据不受影响，“可重复读”隔离级别下还是会出现幻读。

MySQL的并发控制手段

  1. 锁机制：

    • 表锁：锁定整张表，适用于小表和批量操作（意向共享锁和意向排他锁）。
    • 行锁：锁定单行数据，适用于并发高的场景，减少锁竞争（共享锁和排他锁）。
    • 间隙锁（Next-Key Lock） ：锁定索引区间，防止幻读。

  2. 事务隔离级别：通过选择合适的事务隔离级别控制并发访问的一致性和性能。

  3. MVCC（多版本并发控制） ：通过类似快照的方式在事务开始时保存数据的多个版本，允许读写并发操作而对其他事务不可见（隔离性，互不影响），提高并发性能。

5. 网页中输入URL，直到页面显示，这个过程中发生了什么？

  1. URL 解析 ： 浏览器首先会解析URL，分离出协议、主机名、端口等信息 。
  2. DNS 解析：将URL中的域名解析为IP地址（查询浏览器缓存-操作系统缓存-路由器缓存-ISP缓存是否命中，否则再向上级服务器递归查询）。
  3. 建立TCP连接：客户端与服务器通过三次握手建立TCP连接。
  4. 发送HTTP请求：客户端发送HTTP请求到服务器，包含请求行、请求头和请求体。
  5. 服务器处理请求：服务器接收请求，处理并生成响应内容。
  6. 发送HTTP响应：服务器将响应内容发送回客户端，包括状态行、响应头和响应体。
  7. 浏览器解析响应和页面渲染：浏览器接收到HTML、CSS、JavaScript等资源，开始解析和渲染页面。
  8. 断开连接： 数据传输完成后，通过 四次挥手 断开TCP连接

SpringMVC运行一次经历了哪些过程

（同上）HTTP请求到达后，Spring MVC的核心组件DispatcherServlet接收请求，经过处理器映射（Handler Mapping）根据URL来匹配合适的Controller方法，执行处理器（Controller），Controller方法返回一个视图名称，视图解析（View Resolution）（同上）

MySQL执行一条SQL语句的过程

当MySQL执行一条SQL语句时，大致过程如下：

  1. 客户端发送请求：客户端通过MySQL连接器发送SQL请求到MySQL服务器。
  2. 连接器处理：连接器管理客户端连接，并进行身份验证和权限检查。
  3. 查询缓存：查询解析器先检查查询缓存，如果命中缓存，直接返回结果；否则继续执行。
  4. 解析器：将SQL语句解析为语法树，进行语法检查和词法分析。
  5. 优化器：优化器选择最佳执行计划，包括选择索引、优化JOIN顺序等（优化前作预处理，检查表或字段是否存在，将* 扩展为所有列）。
  6. 执行器：执行器根据执行计划逐步执行查询，调用存储引擎接口操作数据。
  7. 存储引擎：存储引擎负责数据的实际存储和检索，例如InnoDB、MyISAM等。
  8. 返回结果：执行器将结果返回给客户端。

MySQL索引存储的位置及失效场景

索引存储的位置：

  • InnoDB存储引擎：索引存储在表空间文件中。InnoDB使用聚簇索引（一级索引），主键索引和数据一起存储，二级索引（非聚簇索引）存储键值和对应的主键。
  • MyISAM存储引擎：索引存储在独立的.MYI文件中，数据存储在.MYD文件中。
特点聚簇索引（Primary Index）非聚簇索引（Secondary Index）
数据存储数据按索引顺序存储索引和数据分开存储
叶子节点存储完整的数据行存储索引键和行指针或主键值
每表数量每个表只能有一个每个表可以有多个
访问速度通过聚簇索引查找数据较快查找数据需要先查索引，再回表查找数据
使用场景适用于需要排序和范围查询的场景适用于在多个字段上频繁查询的场景
维护成本更新聚簇索引键会影响数据存储顺序更新非聚簇索引键不会影响数据存储顺序

索引失效场景：

  • 使用函数、表达式：在索引列上使用函数或表达式，如WHERE UPPER(name) = 'JOHN'。
  • 隐式类型转换：如字符串列上进行数值比较。
  • 前缀通配符：如LIKE '%abc'。
  • 使用不等号：如<>或!=。
  • 使用OR条件：在多个列之间使用OR条件可能导致索引失效。
  • 查询字段与索引字段顺序不一致：在复合索引中，查询字段顺序与索引字段顺序不一致可能导致索引失效。

如何做索引优化

1. 选择合适的列建立索引

  • 频繁查询的列：优先为查询频率高的列建立索引。
  • 经常用于过滤条件（WHERE）或连接条件（JOIN）的列：这些列应该考虑建立索引。
  • 选择性高的列：选择性高的列（即具有较多不同值的列）更适合作为索引列。

2. 索引类型的选择

  • 单列索引：适用于单独查询或单独作为过滤条件的列。
  • 复合索引：适用于多个列经常组合查询的情况，注意复合索引中列的顺序，遵循最左前缀原则。
  • 唯一索引：确保列的唯一性，同时提升查询速度。
  • 全文索引：适用于全文搜索的场景，例如文本字段的模糊匹配。

3. 覆盖索引（Covering Index）

  • 覆盖索引是指索引包含了查询所需的所有列，避免回表（即只从索引里读到一部分，还需要二次读取数据表获取其他列）。通过覆盖索引，可以减少I/O操作，提高查询效率。代价是索引增加，维护成本增加。

4. 避免冗余索引

  • 检查现有索引，避免重复的或功能重叠的索引，减少维护索引的开销。

5. 维护和管理索引

  • 定期重建和优化索引：随着数据变化，索引的性能可能下降，定期重建和优化索引可以保持索引性能。
  • 监控索引的使用情况：使用数据库自带的工具（如MySQL的EXPLAIN命令）分析查询计划，查看索引的使用情况，调整不合理的索引。

6. 避免索引失效的操作

  • 避免对索引列进行函数或计算操作：如前所述，使用函数或计算操作会导致索引失效。
  • 避免使用负向条件：如!=、<>、NOT IN、NOT LIKE等，这些条件会使索引失效。
  • 避免对大范围数据的模糊查询：如LIKE '%value%'，这种查询无法利用前缀索引。
  • 尽量使用索引前缀：对于字符串列，使用索引前缀（如LIKE 'value%'）可以提高查询效率。

7. 合理设计索引顺序

  • 最左前缀原则：复合索引的列顺序应该按照查询条件的使用频率和过滤效果来设计，以最常用和选择性高的列在前。
  • 查询排序：如果查询需要排序（ORDER BY），可以考虑在索引设计中包含排序列，避免排序操作的额外开销。

8. 特殊场景下的索引优化

  • 分区索引：对于非常大的表，可以考虑使用表分区和分区索引，以减少单个查询的扫描范围。
  • 空间索引：适用于地理位置数据的查询优化，如GIS（地理信息系统）应用。

Mysql主从解决什么问题，不能解决什么问题？

MySQL主从同步，主负责写，从负责读，使用一主多从，能减轻读的压力但是这不能解决写的压力和主库的单点故障，如果主库的写并发高，可以做成多个主库

MySql主从复制原理？

主要依靠binlog来实现的，它记录的是所有的DDL，DML，TCL操作当主库的数据发生改变时，会将改变记录保存到binlog中从库新开一个线程将binlog内容发送到从库从库会发起一个I/O线程请求主库的binlog，并保存到中继日志中从库新开一个SQL线程，读取中继日志并解析成具体操作，从而将主库更新的内容写到了从库中

写好一段代码到程序执行的过程

  1. 编写代码：开发者使用编程语言编写代码，保存为源代码文件。
  2. 编译代码：编译器将源代码翻译为目标代码（如字节码或机器码），生成可执行文件。
  3. 加载程序：操作系统将可执行文件加载到内存中，分配所需资源。
  4. 启动程序：操作系统创建进程，分配CPU时间片，程序开始执行。
  5. 执行程序：CPU读取指令，进行计算和数据处理，程序按照指令序列执行。
  6. 程序结束：程序执行完毕，操作系统回收资源，进程结束。

MySQL的数据类型

1.数值

整数类型

  • TINYINT: 非常小的整数（-128 到 127）。
  • SMALLINT: 小整数（-32,768 到 32,767）。
  • MEDIUMINT: 中等大小的整数（-8,388,608 到 8,388,607）。
  • INT: 标准整数（-2,147,483,648 到 2,147,483,647）。
  • BIGINT: 大整数（-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807）。

浮点数类型

  • FLOAT: 单精度浮点数。
  • DOUBLE: 双精度浮点数。

2.日期时间

  • DATE: 日期值（'YYYY-MM-DD'）。

  • TIME: 时间值或持续时间（'HH:MM'）。

  • DATETIME: 混合日期和时间值（'YYYY-MM-DD HH:MM'）。

  • TIMESTAMP: 混合日期和时间值，自动更新（'YYYY-MM-DD HH:MM'）。

  • YEAR: 年份值（四位或两位格式，默认四位）。

3.字符串

  • CHAR: 固定长度字符串。

  • VARCHAR: 可变长度字符串。

  • TINYTEXT: 非常小的文本。

  • TEXT: 小文本。

  • MEDIUMTEXT: 中等大小的文本。

  • LONGTEXT: 大文本。

  • 二进制类型

  • BINARY: 固定长度二进制数据。

  • VARBINARY: 可变长度二进制数据。

  • TINYBLOB: 非常小的二进制对象。

  • BLOB: 小二进制对象。

  • MEDIUMBLOB: 中等大小的二进制对象。

  • LONGBLOB: 大二进制对象

  • 枚举和集合

  • ENUM: 枚举类型，字符串对象，其值来自预定义的枚举列表。

  • SET: 集合类型，字符串对象，其值可以是零个或多个来自预定义集合的成员。

4.JSON

JavaScript Object Notation

5.空间数据类型

什么是SQL注入

SQL注入是一种代码注入攻击，攻击者通过在输入字段中插入恶意的SQL代码，来欺骗应用程序执行未经授权的SQL命令。这可能导致攻击者访问、修改甚至删除数据库中的数据。SQL注入通常发生在应用程序直接将用户输入的数据嵌入到SQL查询中，而没有进行适当的验证或转义。

为什么使用 ${} 不能防止SQL注入

${} 会直接进行字符串拼接，如果用户输入了恶意的 SQL 代码，它会直接插入到 SQL 语句中执行。而#{} 会将变量的值作为参数绑定到 SQL 语句中，使用 SQL 的预编译机制。这样可以有效防止 SQL 注入，因为变量的值不会直接拼接到 SQL 语句中，而是作为参数处理。

drop, delete, truncate的区别

drop 删除整个数据库或表(整个表结构删除)。
delete 删除表中的某些行。 通过 WHERE 子句指定删除条件，从而有选择性地删除数据。
truncate删除表中的所有行。

image.png

image.png

avatar
文章
阅读
粉丝