数据库设计的三大范式

  1. 每一列字段都是原子的，比如产品编码列中，每个单元只能有一个编码；
  2. 在满足第一范式的基础上，确保非主键属性完全依赖于主键；
  3. 在满足第一二范式，确保非主键属性之间没有传递依赖。

drop delete truncate区别

dorp是用于表的删除，这个删除是无法进行恢复的；

delete是用于表中数据的一行，可以使用ROLLBACK进行回滚的；

truncate是清空表，会删除表中所有数据并且保留薄的结构，也是无法使用ROLLBACK进行撤销操作。

count(1) count() count(列名)区别

count(1)和count()都是统计所有行数，而count(1)会忽略具体列中的具体数据查询效率较高，count(列名)是查询该列中非NULL的所有列。

SQL查询语句的执行顺序

  1. FROM 子句 ：从指定的表中获取数据。
  2. WHERE 子句 ：根据指定的条件筛选数据。
  3. GROUP BY 子句 ：根据指定的列对数据进行分组。
  4. HAVING 子句 ：对分组后的数据进行筛选，类似于 WHERE，但作用于分组数据。
  5. SELECT 子句 ：选择要查询的列，进行计算或数据处理。
  6. ORDER BY 子句 ：对结果进行排序。
  7. LIMIT/OFFSET 子句 ：根据指定的数量和偏移量返回结果的子集。

InnoDB和MyISAM区别

  1. InnoDB 支持事务而MyISAM不支持事物
  2. InnoDB是行级锁而MyISAM是表级锁
  3. InnoDB支持崩溃恢复而MyISAM没有崩溃恢复机制
  4. InnoDB从 MySQL 5.6.4 开始支持全文索引，而MyISAM很早就支持不过它性能和功能方面相对较弱
  5. InnoDB会将数据和索引存储在内存缓存中，适合于对内存要求较高的应用，而MyISAM 也支持内存缓存，但与 InnoDB 相比，性能相对较低

索引分类

  1. 主键索引（Primary Key Index） ：

    • 主键索引是一种唯一性索引，用于标识表中的唯一记录。
    • 在主键索引下，数据行的值是唯一的，因此可以快速定位到特定的数据行。

  2. 唯一索引（Unique Index） ：

    • 唯一索引保证索引列的值在整个表中都是唯一的，但可以有 NULL 值。
    • 通过唯一索引可以确保数据的完整性，同时提高检索效率。

  3. 普通索引（Normal Index 或 Non-Unique Index） ：

    • 普通索引用于加速对表中数据的搜索和检索，但允许索引列有重复值。
    • 适用于频繁用于查询、排序和过滤的列。

  4. 全文索引（Full-Text Index） ：

    • 全文索引用于对文本数据进行全文搜索，允许对文本内容进行关键词匹配。
    • 适用于需要进行文本搜索的场景，如文章内容、评论等。

  5. 空间索引（Spatial Index） ：

    • 空间索引用于处理地理空间数据，如地理坐标点、地理区域等。
    • 可以用于查询地理位置相关的信息，如距离计算、区域判断等。

  6. 组合索引（Composite Index 或 Compound Index） ：

    • 组合索引是由多个列组合而成的索引，用于加速多个列的联合查询。
    • 可以优化查询条件中涉及多个列的情况。

索引在什么情况下会失效

在使用模糊查询、使用or链接的条件、数据量过少、连接查询的时候选择了不正确的链接索引，这些都会导致索引失效。

索引不适合哪些场景

不适用于数据量小、增删改频繁的、大字段的列、维度表、频繁的批量操作

索引存储的数据结构

一般使用B+数的结构 也有B树结构

为什么使用B+而不是普通二又树

因为他更适合硬盘存储，减少了磁盘 I/O 次数，有序性强，平衡性好，能够减少高层索引的维护成本，查询效率稳定。

聚簇索引和非聚簇索引区别

区别主要在于数据存储方式、唯一性、查询性能和更新性能。聚簇索引下数据行的物理存储顺序与索引顺序相同，用于范围查询，要求键值唯一。非聚簇索引维护了键和数据行的映射，适用于插入和更新频繁的情况。选择索引类型要根据具体需求和数据库表的特性。

回表和覆盖索引

回表是指查询过程中需要通过索引再次查找表中的数据行，而覆盖索引是指索引已经覆盖了查询所需的字段，无需回表操作，从而提高了查询性能。优化查询性能时，选择合适的索引和避免回表操作是重要的考虑因素。

什么是最左匹配原则

最左匹配原则是数据库索引优化中的一个概念，在复合索引中，从索引的左边开始匹配查询条件，以便实现最佳的索引利用。如果一个复合索引包含多个列，那么在查询时，数据库会从复合索引的最左边开始，依次匹配查询条件中的列。只有当查询条件中的列依次从左到右与索引中的列匹配，才能有效地利用这个索引。一旦遇到一个不匹配的列，索引就不能继续使用。

MySQL中的锁有哪几种

  1. 共享锁（Shared Lock，也称读锁）：
    共享锁允许多个事务同时获取同一资源的锁，用于保证读操作的并发性，多个事务可以共享同一个资源的共享锁，不会互相阻塞。
  2. 排他锁（Exclusive Lock，也称写锁）：
    排他锁只允许一个事务获取资源的锁，其他事务无法获取该资源的共享锁或排他锁。用于保证写操作的原子性。
  3. 意向共享锁（Intention Shared Lock）：
    意向共享锁是一种在行级锁和表级锁之间的辅助锁，用于指示一个事务要在某个资源上获取共享锁。
  4. 意向排他锁（Intention Exclusive Lock）：
    意向排他锁是一种在表级锁上的辅助锁，用于指示一个事务要在某个资源上获取排他锁。
  5. 自增锁（Auto-Increment Lock）：
    用于保证自增操作的并发安全，MySQL在执行自增操作时会为相关的行加上自增锁，防止多个事务同时对同一行进行自增。
  6. 间隙锁（Gap Lock）：
    用于防止幻读现象，间隙锁会锁定一个范围，阻止其他事务在这个范围内插入新的数据。
  7. 临键锁（Next-Key Lock）：
    临键锁是 MySQL 中用于解决幻读问题的锁，它是间隙锁和记录锁的组合，用于保证范围查询的一致性。

MySQL中事务的四大特性

原子性、隔离性、一致性、持久性

  1. 原子性（Atomicity）：
    原子性指事务是一个不可分割的最小工作单元，要么全部执行成功，要么全部不执行。如果事务中的任何一部分失败，整个事务会回滚到起始状态，保持数据的一致性。
  2. 一致性（Consistency）：
    一致性指事务在执行前后，数据库的状态必须保持一致。这意味着事务应该将数据库从一个合法状态转移到另一个合法状态，不会破坏数据完整性和业务逻辑。
  3. 隔离性（Isolation）：
    隔离性指多个事务并发执行时，每个事务都应该与其他事务隔离开来，互不干扰。每个事务的操作都应该在完全隔离的环境下进行，以防止并发访问引发的问题。
  4. 持久性（Durability）：
    持久性指事务一旦提交成功，对数据库的改变就是永久性的，即使发生系统故障也不会丢失。数据库应该将事务的更改写入持久存储介质，以确保数据的持久性。

MVCC怎么实现的

他实际上是多版本并发控制实现事务隔离性的机制，每个事务在开始时会被分配一个唯一的时间戳，用于标识事务的先后顺序，数据行不仅保存当前的值，还会保存多个历史版本的值。每个版本都有一个与之相关的时间戳，当一个事务执行写操作时，不会直接修改原始数据行。而是将修改后的数据创建为一个新版本，并与事务的时间戳关联，当事务执行读操作时，只能看到在它开始之前已经提交的版本。这确保了事务只能看到自己开始之前的数据库状态，从而实现了隔离性。当事务提交后，其时间戳会被记录为提交时间。其他事务在此之后执行的读操作会看到该事务所做的修改。

MySQL主从复制原理水平分表有几种路由方式

一种常见方式是使用数据库中间件，如Sharding-JDBC、Mycat等，它们拦截SQL语句并根据配置将查询路由到对应的数据节点。另一种方式是代码封装，通过代码控制写操作走主库，读操作走从库。还可以结合分库分表策略，将不同的数据分散到不同的数据库或表中，以实现数据水平分片

大数据量下的表如何删除和添加字段

创建一个新表，将需要保留的字段迁移到新表中，然后重命名表，达到删除/添加字段的效果。

MySQL数据库CPU飙升如何处理

  1. 排查高耗CPU SQL ：通过系统命令（如top）查看是否由mysqld进程引起的，排查高耗CPU SQL语句并进行优化。
  2. 索引优化 ：优化查询语句，添加合适的索引以减少全表扫描和数据访问开销。
  3. 慢查询日志 ：开启慢查询日志，记录执行时间超过阈值的SQL语句，以便分析和优化。
  4. 资源分配 ：合理配置数据库服务器的CPU和内存资源，防止资源争抢导致性能下降。
  5. 分析工具 ：使用数据库性能分析工具（如Explain）分析查询执行计划，找出潜在的性能瓶颈。
最后修改：2023 年 08 月 25 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏