数据库

最新推荐文章于 2024-08-13 19:10:19 发布

桃九醉

最新推荐文章于 2024-08-13 19:10:19 发布

阅读量246

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_38415505/article/details/83413080

本文深入解析数据库连接池、SQL查询优化、事务隔离级别、索引原理及优化、SQL注入防御等核心概念，涵盖数据库设计、性能调优及安全防护关键知识点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库连接池的原理

如何实现不同数据库的数据查询分页

SQL注入的原理，如何预防

SQL性能优化

数据库索引的优缺点以及什么时候数据库索引失效

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是	oracle\sql server\postgresql 默认，加行锁实现
可重复读（repeatable-read）	否	否	是	mysql默认，底层MVCC实现
串行化（serializable）	否	否	否	两段锁协议

数据库如何实现串行化

- https://blog.youkuaiyun.com/a724888/article/details/61414113
- 平常都用什么数据库？关系型数据库平时用哪些？
- 为什么你的缓存更新策略是先更新数据库后删除缓存，讲讲其他的情况有什么问题
- 你的项目用什么框架去连接数据库，连接池用的什么（druid），使用druid的过程中有没有遇到什么问题？
- 分库分表
- 数据库范式
- 主从同步（当时回答使用二进制日志，和中继日志保证，但是好像说不对）
- 负载均衡常见的负载均衡算法有哪些
- 数据库的垂直拆分和水平拆分
- 事务
- 什么是事务
  - 构成单一逻辑工作单元的操作集合称为事务——即使发生故障，也必须保证事务的正确执行：要么执行整个事务，要么属于这个事务的操作一个都不执行。
  - 满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。
- 事物的基本要素ACID
  - 原子性（Atomicity）
    - 事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生，不可能停滞在中间环节。
    - 事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。
  - 一致性（Consistency）
    - 事务开始前和结束后，数据库的完整性约束没有被破坏。
    - 比如A向B转账，不可能A扣了钱，B却没收到。
  - 隔离性（Isolation）【多种隔离级别】
    - 多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
    - 即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
  - 持久性（Durability）
    - 事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
- 事务并发导致的问题
  - 丢失修改
    - T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T1的修改
  - 脏读
    - 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
  - 不可重复读
    - 事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
  - 幻读
    - 例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
  - 不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
- 隔离级别、底层实现
- 不可重复读和幻读怎么避免，底层实现（行锁表锁+封锁协议
- 悲观锁 & 乐观锁
  - 悲观锁（数据库自带）
    - 悲观地觉得别人一定会修改我的数据。悲观锁有两种，读锁和写锁
    - 排它锁、写锁(Exclusive)，X 锁。
      - 一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
    - 共享锁、读锁(Shared)，S 锁。
      - 一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。
    - 锁的粒度：行级锁、表级锁（综合考虑并发度和锁开销）
    - 意向锁
      - IX / IS，皆表锁，表示一个事务想要在表中的某个数据行上加X锁或S锁。
      - 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁
      - 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁
    - 间隙锁（幻读）
      - 用范围条件（而不是相等条件）检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(NEXT-KEY)锁。
      - 优缺点
        因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
        当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定值范围内的任何数据，在某些场景下这可能会针对性造成很大的危害。
        避免幻读
- 乐观锁（数据库未实现，有框架封装，如hibernate）
  - 解决写-写冲突：丢失修改
  - 乐观锁是指操作数据库时(更新操作)，乐观地认为这次的操作不会导致冲突，在操作数据时，不加锁，先看一下数据的版本/时间戳，在进行更新后，真正提交的时候再看一下版本/时间戳，判断是否有冲突（版本是否一样），如果有冲突，就要回滚。
  - 通常实现
    - 在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。操作时先获取version字段，如果要对那条记录进行操作(更新)，则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
  - 优缺点
    - 在资源冲突不激烈的场合，用乐观锁性能较好。如果资源冲突严重，乐观锁的实现会导致事务提交的时候经常看到别人在他之前已经修改了数据，然后要进行回滚或者重试，还不如一上来就加锁。
- MVCC Multi-Version Concurrency Control
  - 实现
    - InnoDB在每行记录后面保存两个隐藏的列，分别保存行的创建时间&删除时间——系统版本号(事务ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID
    - InnoDB只会操作版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.
    - 行的删除版本要么未定义,要么大于当前事务版本号(这可以确保事务读取到的行，在事务开始之前未被删除)，
    - 只有条件同时满足的记录，才能返回作为查询结果.
- 优缺点
  - MVCC在大多数情况下代替了行锁，实现了对读的非阻塞，读不加锁，读写不冲突。缺点是每行记录都需要额外的存储空间，需要做更多的行维护和检查工作。
- 三级封锁协议
  - 一级封锁协议
    - 事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。
    - 解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。
  - 二级封锁协议
    - 事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。
    - 解决脏读问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
  - 三级封锁协议
    - 事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。
    - 解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。
- 两段锁协议
  - 可保证可串行化调度。
  - 整个事务分为两个阶段，前一个阶段为加锁，后一个阶段为解锁。在加锁阶段，事务只能加锁，也可以操作数据，但不能解锁，直到事务释放第一个锁，就进入解锁阶段，此过程中事务只能解锁，也可以操作数据，不能再加锁。
  - 不足是没有解决死锁的问题，因为它在加锁阶段没有顺序要求。如两个事务分别申请了A, B锁，接着又申请对方的锁，此时进入死锁状态。
- 分布式事务
- 分布式锁、如何添加，放在什么位置
  - 刚性事务，柔性事务
- 缓存的使用策略和从底层数据库开始往上各个层级中缓存的使用，把你知道的都说出来，这个问题刚开始有点懵逼，后来就从数据库底层原理开始说起，然后讲了缓存的各种使用策略，和这些策略避免的什么问题，这里讲了好久，边说边被追问，里面涉及到各种缓存击穿，缓存穿透，缓存雪崩，互斥锁等内容
- 缓存一致性

- 索引，要自己编程一下
- 对索引的理解
  - 数据库索引本质上是一种数据结构(存储结构+算法)，目的是为了加快目标数据检索的速度。
- 索引的应用场景
- 索引特点
- 索引选择原则
- 索引失效
- 对于建立索引的列，数据均匀分布好还是不均匀好？
- 索引的分类
  - 顺序 & 散列
- 聚集（聚簇）索引 & 非聚集索引**************
  - 聚簇索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序（索引码指定的顺序）相同。一个表中只能拥有一个聚集索引。
  - 优缺点
  - 聚簇索引的优点
    - 当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。
    - 当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址，即多一次I/O。
  - 聚簇索引的缺点
    - 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
    - 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
    - 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。
    - 采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。
- 稠密索引 & 稀疏索引
  - 稠密索引：文件中的每个搜索码值都对应一个索引值。也就是说，稠密索引为数据记录文件的每一条记录都设一个键-指针对。索引项包括索引值以及指向该搜索码的第一条数据记录的指针，即我们所说的键-指针对。
  - 稀疏索引：只为搜索码的某些值建立索引项。也就是说，稀疏索引为数据记录文件的每个存储块设一个键-指针对，存储块意味着块内存储单元连续。
  - 优缺点
    - 稠密索引更快定位一条记录。
    - 稀疏索引所占空间小，插入和删除时所需维护的开销也小。
- 单列索引
  - 一个索引只包含单个列，但一个表中可以有多个单列索引。
  - 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
  - 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
  - 主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）
- 联合索引（代码预警）
  - 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
  - 联合索引的设计：
  - 比如（A,B,C）索引，在where a=1,b>1,c=1中哪些列可以用到索引？where b=1,c=1可不可以用到索引，为什么（联合索引的结构）？where a=1,c=1时可不可以用到索引？where a=1,b=1 order by c可不可以用到？
  - 我给一个表三个ABC列建了一个组合索引，我查询B会用到索引嘛？
- 辅助索引
- 全文索引
  - 在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行
- 索引设计：根据一些常用的查询条件设计索引；
- 索引的优缺点
  - 优点
    - 加快数据的检索速度
    - 创建唯一性索引，保证数据库表中每一行数据的唯一性
    - 加速表和表之间的连接
    - 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
  - 缺点
    - 索引需要占用数据表以外的物理存储空间
    - 创建索引和维护索引要花费一定的时间
    - 当对表进行更新操作时，索引需要被重建，降低了数据的维护速度
- 索引优化
- 索引的底层实现原理——B+树
- hash索引和b树索引的区别、适用场景
- 使用b树有什么优势，b树和b+树的区别，b树和红黑树有什么区别。
- 数据库层面怎么优化秒杀？

- SQL（语言） & mysql（关系型数据库管理系统）
- 基本的写sql能力：灵活使用join,groupby,order by以及常见的聚合函数；
- 经典SQL语句大全(绝对的经典)http://www.cnblogs.com/dajiang02/archive/2011/08/13/2136929.html
- 一题用到group by 和 having count 的sql题目
- 如何判断SQL查询操作是不是慢sql，如何优化
- sql优化有哪些着手点？组合索引的最左前缀原则的含义？
- mysql存储引擎，种类、区别，用的什么数据结构，优缺点，怎么使用
  - MyISAM【mysql 默认的引擎，强调性能】
    - 不支持事务，不支持行级锁（使用表锁）和外键，写操作（更新时）效率低。
    - 适用于查询和插入为主的（读操作远多于写操作）
    - 读操作不占用大量内存和资源，性能高（大多数数据库都是查询比修改更多的）。但不支持事务等高级功能，崩溃之后不能恢复。
    - B+树中的存储内容是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
  - InnoDB 【recommended，MVCC】
    - 支持事务，提供行级锁和外键的约束，崩溃修复能力。锁粒度低，在并发度较高的场景下使用会提升效率。
    - 适用于更新密集型，或者并发度高的，对可靠性要求高的。设计目标是处理大数据容量的数据库系统。
    - B+树，存储实际数据，这种索引有被称为聚集索引。
  - MEMORY【内存、快、hash】
    - 使用存在于内存中的内容创建表，每一个memory只实际对应一个磁盘文件。因为是存在内存中的，所以memory访问速度非常快，而且该引擎使用hash索引，可以一次定位，不需要像B树一样从根节点查找到支节点，所以精确查询时访问速度特别快，但是非精确查找时，比如like，这种范围查找，hash就起不到作用了。另外一旦服务关闭，表中的数据就会丢失，因为没有存到磁盘中。
    - 适用场景：内容变化不频繁的表，或者作为中间的查找表。对表的更新要谨慎因为数据没有被写入到磁盘中，服务关闭前要考虑好数据的存储。
  - MERGE
    - 一组 myisam 表的组合
- 平时用mysql用什么引擎
- 知不知道怎么检测select语句在哪里可以优化，查询的时候有没有走索引
- 讲讲nosql
- MySQL连接池对比
- MySQL命令
- JOIN的执行计划？
- 设计
- Sql设计一个部门表
- 用SQL获得一个表级锁应该怎么写？
- 高并发系统，海量数据分库分表的策略