数据库知识点梳理（一）：从基础操作到底层原理

一、数据库模块总览数据库的知识体系看似繁杂其实可以归纳为四大板块SQL操作数据库最基础、最常用的核心能力也是日常工作中使用频率最高的部分数据建模ER图数据库设计的第一步帮我们理清业务逻辑避免后期表结构混乱性能优化索引提升查询速度的关键技术解决大数据量下的查询卡顿问题底层原理引擎/事务/锁/日志数据库进阶的核心理解它才能真正读懂数据库的运行逻辑二、SQL操作日常数据交互SQL是与数据库交互的“语言”核心分为「基础增删改查CRUD」和「高级查询」两部分无需死记硬背理解逻辑就能灵活运用。1 基础增删改查CRUD这是SQL最基础的操作对应数据的新增、查询、修改、删除其中查询SELECT日常使用频率最高。INSERT新增数据向数据表中插入一条或多条记录SELECT查询数据可根据条件筛选、排序是所有SQL操作中最灵活、最常用的指令UPDATE修改数据根据条件更新表中已有的记录注意避免误改全表DELETE删除数据根据条件删除表中的记录同样需谨慎操作防止误删2 多表关联查询实际业务中数据往往分散在多张表中比如“用户表”和“订单表”这就需要通过关联查询获取完整数据常用的关联方式有4种内连接INNER JOIN只返回两张表中匹配的数据不匹配的记录会被过滤左连接LEFT JOIN返回左表全部数据右表匹配的数据会关联显示不匹配的部分补NULL右连接RIGHT JOIN与左连接相反返回右表全部数据左表匹配的数据关联显示不匹配部分补NULL全连接FULL JOIN返回两张表的所有数据双方不匹配的部分都补NULL3 高级查询技巧面对复杂的业务需求基础查询无法满足这时候就需要用到高级查询技巧提升查询效率和灵活性分页通过LIMITMySQL或ROW_NUMBER()SQL Server实现数据分页展示避免一次性加载大量数据分组使用GROUP BY配合聚合函数对数据进行分组统计比如统计每个部门的人数、每个商品的销售额嵌套查询通过子查询实现复杂逻辑把一个查询的结果作为另一个查询的条件常用函数聚合函数MAX最大值、MIN最小值、AVG平均值、SUM求和、排序函数ORDER BY ASC升序/DESC降序简化查询逻辑三、ER图数据库设计的“蓝图”很多人做数据库设计时直接上手建表最后导致表结构混乱、关联关系不清后期修改成本极高。而ER图就是解决这个问题的关键。ER图全称「实体-关系图Entity-Relationship Diagram」用于可视化数据库表结构和表间关系是数据库设计的第一步也是梳理业务逻辑的核心工具。1 ER图元素实体对应数据库中的一张表比如“用户”“订单”“商品”每个实体都有自己的属性属性对应表中的字段比如“用户表”的“用户名”“手机号”“密码”“订单表”的“订单ID”“下单时间”关系表与表之间的关联比如“用户-订单”是一对多关系一个用户可以有多个订单“商品-订单”是多对多关系一个订单可以包含多个商品一个商品可以出现在多个订单中四、索引提升查询速度的“加速器”当表中数据量达到几十万、几百万条时普通查询会变得非常缓慢甚至出现卡顿而索引就是解决这个问题的核心技术——它能帮数据库快速定位数据减少硬盘扫描次数从而大幅提升查询速度。1 索引的类型联合索引由多个字段组合而成的索引遵循“最左前缀原则”比如索引name, age查询时先匹配name再匹配age顺序不能颠倒聚簇索引聚集索引数据行和索引存储在一起一张表只能有一个聚簇索引。通常主键默认就是聚簇索引查询速度最快因为索引和数据在同一个位置非聚簇索引非聚集索引索引和数据分开存储一张表可以有多个非聚簇索引。查询时先通过索引找到数据的存储地址再回表查询完整数据速度比聚簇索引稍慢索引不是越多越好过多的索引会增加数据插入、修改、删除的成本因为每次操作都要更新索引合理创建索引才是关键。五、“程序与数据”的交互逻辑很多人学完SQL操作后还是不懂“为什么查询会慢”“为什么宕机可能丢数据”“为什么需要缓存”其实答案都在数据库的底层原理里运行的程序在内存中持久化的数据在硬盘上。1 程序与数据的存储位置运行的程序一定在内存中数据库服务比如MySQL的mysqld进程本质是硬盘上的可执行文件启动后操作系统会把它加载到内存RAMCPU才能执行指令。内存是高速存储区域但断电后数据会丢失数据一定在硬盘上数据库中的业务数据用户表、订单表等需要持久化保存关机断电后不丢失所以必须存在硬盘HDD/SSD中。硬盘的最小存储单位是文件因此数据库数据最终都以文件形式存在——数据库本质上就是一个“高效的文件管理系统”2 数据库完整运行流程阶段1程序加载数据库程序文件如mysqld位于硬盘的某个目录启动时操作系统将其加载到内存变成一个运行中的进程服务并监听指定端口比如MySQL默认3306端口等待客户端连接。阶段2数据交互数据库会在硬盘上创建专属数据目录如MySQL的data目录一张表通常对应一个或多个文件如InnoDB的.ibd文件存储表数据和索引。当执行SQL查询时内存中的数据库进程接收SQL请求进程去硬盘读取对应表的数据文件将数据加载到内存在内存中完成计算、修改等操作将修改后的数据写回硬盘文件或先写日志再异步刷盘兼顾性能和安全阶段3数据库的使用方式日常使用数据库就是围绕“启动服务-连接-执行SQL-关闭服务”的流程启动服务运行数据库程序让其加载到内存并监听端口Windows服务/ Linux systemctl start mysql连接数据库用客户端工具mysql命令行、Navicat、JDBC连接到数据库服务执行SQL发送CREATE TABLE/INSERT/SELECT等语句数据库服务在内存中处理与硬盘数据文件交互返回结果关闭服务正常关闭时数据库会将内存中未持久化的数据刷回硬盘保证数据不丢失阶段4SQL请求的完整流转除了整体运行流程我们再细化一下“一条SQL请求从发出到返回结果”的完整路径更能理解底层逻辑通过网卡和端口如3306接收客户端发来的SQL字符串如SELECT * FROM users WHERE id1可通过主动轮询或事件驱动接收数据内存中的数据库进程解析SQL字符串识别指令类型、定位操作的表和条件将人类可读的SQL翻译成程序能理解的操作指令找到目标表对应的硬盘文件将文件加载到内存并解析成CPU可操作的数组结构本质是数字编码对内存中的数组执行CRUD查询就是遍历数组、插入就是新增数组元素、修改就是修改元素、删除就是移除元素将修改后的数组重新编码成二进制文件写回硬盘对应表文件完成数据持久化优化机制先写日志WAL再异步刷盘避免宕机丢数据3 底层组件数据库引擎决定数据存储和事务处理方式比如MySQL的InnoDB支持事务、行锁和MyISAM不支持事务、表锁数据库事务遵循ACID原则原子性、一致性、隔离性、持久性保证数据操作的安全性比如转账时“扣钱”和“加钱”必须同时成功或同时失败数据库锁用于并发控制防止多线程操作导致数据不一致分为行锁、表锁、共享锁、排他锁等数据库日志核心用于数据安全和恢复包括重做日志Redo Log保证事务持久性、回滚日志Undo Log实现事务回滚、二进制日志Bin Log用于数据备份和主从复制六、数据库卡顿的原因日常工作中我们最常遇到的数据库问题就是“卡顿”比如选课系统高峰期卡顿、大数据量查询缓慢其实卡顿的本质很简单「总线带宽有限磁盘IO速度慢」再加上并发和锁的影响具体可以分为两点1 总线与磁盘IO瓶颈内存与硬盘之间通过总线传输数据总线有固定的带宽上限。大表对应大文件加载时需要传输大量数据会占满总线带宽导致加载缓慢而小表文件体积小传输速度快加载到内存的速度也更快。2 并发与锁竞争加剧卡顿多个用户同时操作同一张表比如选课系统的“选课表”会同时向总线发起数据加载请求挤占总线资源形成排队效应同时为了防止数据错乱数据库会加锁并发高时大量请求排队等待锁释放进一步拉长响应时间导致卡顿。总结以上就是数据库的核心知识点梳理从基础的SQL操作到ER图设计、索引优化再到底层运行原理和卡顿原因形成了完整的知识闭环。其实数据库并不难关键是抓住“程序在内存、数据在硬盘”的核心逻辑再结合实际使用场景理解每个知识点的作用。