数据库入门实战：从建表、SQL到索引与事务一次讲透-北京尧图网络

写这个“数据库——1”系列的第一篇之前我犹豫了很久不知道应该从哪切入。数据库这个领域往外延伸是分布式、云原生、大数据往里深挖是存储引擎、缓冲区、锁和事务任何一个方向都能单独写出一本书。作为系列第一篇我决定务实一点把最常用的地基部分讲透数据库到底解决什么问题、表结构怎么设计、SQL怎么上手、索引是怎么回事、事务为什么不能省。我默认你至少写过一点SQL但不需要你懂底层原理。这篇读完之后你自己去建一张表、写几条查询、分析一次慢SQL心里应该就有底了。1. 数据库到底解决什么问题1.1 为什么不能一直用文件存数据我见过不少本来应该用数据库的项目最后死在文件读写上。印象最深的是某内部工具系统最开始用本地文件存用户配置单机、个人使用一切正常。后来功能多了多台终端同时读写同一个文件开始出现配置丢失和写一半的问题。再后来数据量涨到几千条程序启动时要一次性加载所有内容操作一次要等好几秒。项目最后不得不把存储层整个重写迁移到数据库。很多人对数据库的第一印象就是“一个存数据的地方”。这个说法没问题但不够准确。文件也能存数据表格工具也能存数据数据库之所以成为几乎所有系统的标配是因为它集中解决了三件事数据组织方式、查询能力、并发与一致性保证。文件方案听起来简单一旦并发、体量、权限这些问题叠加在一起自研存储的维护成本会远远超过你的想象。数据库的核心价值不是“存得下”而是“取得快、改得准、并发时不出错”。理解这一点你才能理解后面所有的设计选择。1.2 关系型和非关系型怎么选工作里最常见的两类数据库一类是以MySQL、PostgreSQL为代表的关系型数据库一类是以Redis、MongoDB为代表的非关系型数据库。很多人选型的时候靠跟风看到别人用MongoDB自己也用看到别人上Redis缓存自己也上最后发现核心业务在非关系型数据库里硬着头皮搞事务开发效率反而更低。维度关系型MySQL / PostgreSQL非关系型Redis / MongoDB数据模型二维表强约束结构键值、文档、列族、图典型场景订单、账户、库存、ERP缓存、日志、画像、消息事务能力ACID支持完善偏弱部分弱事务一致性强一致多为最终一致扩展方式垂直扩展为主分布式复杂天然支持水平扩展我的选型经验只有一句话先问业务需不需要事务和强一致。订单、账户、支付这类场景哪怕逻辑再简单也老老实实用关系型用户行为日志、爬取数据、消息流水这类弱约束、高增长的场景再考虑文档型或键值型。记住数据库选型没有绝对的对错但用错场景的代价往往在半年之后才会显现那时候再迁移就很痛了。2. 建表之前必须想清楚的三件事写代码最忌讳键盘一敲就把表建了。建表是一个反悔成本极高的操作生产环境里改字段类型、加非空约束都可能导致锁表和长时间不可用。所以我每次建表之前都会强制自己过三个问题主键用什么、字段类型怎么定、索引要不要先规划。2.1 主键选型自增ID还是UUID主键可能是整个表里最基础也最容易忽略的决策。很多系统直接用一个自增整数做主键简单、查询快、索引紧凑。但这里有一个隐藏问题分布式环境下多个写入节点各自生成自增ID冲突几乎不可避免。这时候需要全局唯一的ID方案最常见的两个替代品是UUID和雪花ID。方案优点缺点自增BIGINT插入性能好、索引占用小多节点写入会冲突UUID字符串生成不依赖数据库、全局唯一36字符、占用大、无序写入导致页分裂雪花ID8字节整数、趋势递增、全局唯一需要引入ID生成服务我的建议很简单单机、内部系统直接自增BIGINT够用且省心需要分布式唯一ID时不要图省事用UUID字符串做主键优先考虑雪花算法。字符串主键会让二级索引空间膨胀写放大明显业务量大了之后你会非常难受。2.2 字段类型长度、精度和默认值字段类型通常是新手的重灾区。手机号用VARCHAR(20)金额用FLOAT时间用字符串这些都是我实际看到过的写法。手机号的问题是号码可能带前缀、短号VARCHAR(11)不一定够。金额的问题更严重FLOAT是浮点数二进制无法精确表示十进制小数存钱会出现0.1加0.2不等于0.3的情况计费系统一旦用浮点数迟早对不上账。字段设计有几个基本原则。金额用DECIMAL并且明确精度比如DECIMAL(10,2)时间用DATETIME或TIMESTAMP别用字符串布尔值在MySQL里用TINYINT(1)PostgreSQL原生BOOLEAN字符串长度不是越大越好VARCHAR(255)和VARCHAR(65535)的存储开销、索引支持完全不一样。每次写DDL的时候多花三十秒想想类型选型后期能省下几百倍的调试时间。2.3 索引的初步设计冗余和权衡很多开发者建表时不建任何索引等线上慢查询爆发了再补结果发现数据已经几百万行加索引要锁大半天。建表时就该想清楚哪些列会成为查询条件。一个基本参考出现在WHERE、JOIN、ORDER BY里的字段都是加索引的候选对象。但这里有一个反方向误区索引不是越多越好。每个索引都要占磁盘空间每次写入都要同步维护所有索引索引过多会严重拖慢写操作。我见过一张表建了二十几个索引的表写一次要维护几十个B树写入性能一塌糊涂。正确做法是宁可少建两个也要把高频查询路径上的索引建对。不常用的索引留着就是负担。3. SQL实操从建库到联表查询一次讲透SQL语法本身不复杂但真正写得好的人不多。问题往往出在只会写单表查询一遇到统计、联表就逻辑混乱。这一节把日常最高频的SQL操作完整过一遍以MySQL语法为主原理同样适用于其它关系型数据库。3.1 建库建表DDL的基本姿势先看一个建表示例CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4; CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(64) NOT NULL, email VARCHAR(128) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有几个容易被忽略的细节。字符集用utf8mb4而不是utf8因为MySQL的utf8最多存3字节遇到emoji会直接报错。ID用BIGINT UNSIGNED而不是INT因为INT最大只能到21亿业务量稍大就满了。NOT NULL和DEFAULT要显式声明避免隐式默认值带来的脏数据。每个表都建议带一个创建时间字段排查线上数据问题时几乎是必备信息。3.2 插入更新删除DML的注意事项DML操作最大的坑永远是那句忘写WHERE的UPDATE或DELETE。我工作以来听过太多次“我把整个表的数据都更新了”的现场事故。写UPDATE和DELETE的第一个习惯就是先写WHERE条件确认条件能选中预期的行再考虑执行。开发环境测试时先跑一遍SELECT验证条件范围这是最便宜的安全措施。插入数据时批量插入效率远高于逐条插入。一条INSERT语句带多组VALUES比循环调用几千次单条INSERT快一个数量级。但批量插入也不要一次插太多我一般控制在500到1000行一批。更新多条记录时尽量包在一个事务里要么全部成功要么全部回滚避免只更新了一半导致的脏数据。3.3 查询和聚合WHERE、GROUP BY、HAVING查询语句里有一个执行顺序经常被搞混FROM先执行然后WHERE然后GROUP BY然后HAVING最后才是SELECT、ORDER BY和LIMIT。理解这个顺序你就能明白为什么不能在WHERE里使用SELECT里定义的别名也能明白HAVING和WHERE的本质区别。WHERE是在分组之前过滤原始行HAVING是在分组之后过滤聚合结果。举个订单统计的例子想统计每个用户的订单总额但只保留下单次数大于10次的用户正确写法是先WHERE过滤掉无效订单再GROUP BY user_id最后HAVING COUNT(*) 10。老手写这类SQL几乎不用想新手最容易犯的错就是用错了WHERE和HAVING的位置导致统计结果对不上账。关于COUNT提一个高频疑问COUNT()和COUNT(1)性能基本相同它们在多数引擎里都是统计行数但COUNT(column)只统计该列非NULL的行。所以如果你想知道一张表有多少条记录写COUNT()就好不要去写COUNT(主键)然后期待性能更好现代优化器早就把这个问题处理掉了。3.4 联表查询INNER JOIN和LEFT JOIN的区别联表查询的核心是搞清楚两种JOIN的语义。INNER JOIN只返回两边匹配上的行左表存在但右表没有对应记录这些行不会出现。LEFT JOIN则保留左表的全部行右表没有匹配时用NULL填充。这个区别写一次性报表时可能无感但在业务SQL里一旦用错统计数字差了十倍你都发现不了。LEFTOUTER JOIN里有一个更隐蔽的坑把右表的过滤条件写在WHERE中会悄悄改变连接语义。比如左连接出所有用户和他们的订单然后WHERE o.status paid这时没订单的用户或订单没支付的用户就被过滤掉了LEFT JOIN实际变成了INNER JOIN。右表的过滤条件应该写在ON子句里除非你明确就是要过滤。这个细节我在代码评审时反复讲过每次都能救到人。4. 索引才是查询性能的命根子查询慢九成是索引问题。这一节不讲太高深的原理只把最核心的B树概念和最左前缀原则讲清楚。4.1 为什么索引能大幅提速用字典打比方没有索引的查询等于从第一页开始逐页翻字典找什么都是全表扫描索引相当于拼音目录先定位到字所在的大区再精确到具体页。数据库最常用的索引结构是B树叶子节点保存索引值并且叶子节点之间形成有序链表这样既能等值定位也能高效做范围查询。这里要区分两类索引。主键索引在InnoDB里是聚簇索引叶子节点直接保存整行数据查询走主键能一次到位二级索引的叶子节点保存的是主键值查询时先找到主键再回表查整行数据这个过程叫回表。理解了回表你就明白为什么“不要用SELECT *”在索引场景下是有道理的你需要什么列就用覆盖索引把列带出来避免多余的回表。4.2 复合索引与最左前缀原则复合索引是最实用也最容易出错的索引类型。假设表上有索引(a, b, c)这个索引真正能高效支撑的查询组合是a单独、a和b一起、a和b和c一起。如果查询条件里没有a只有b或c那么这个复合索引基本派不上用场。这就是最左前缀原则。举个例子。订单表上有索引(user_id, status)查询WHERE user_id 123很好用WHERE user_id 123 AND status paid也很好用但单独的WHERE status paid走不了这个索引。所以设计复合索引时要把区分度高、最常出现在WHERE里的字段放最前面这是索引设计里优先级最高的决策之一。4.3 如何用EXPLAIN定位索引失效任何SQL写完之后都应该养成跑一次EXPLAIN的习惯。我见过太多的线上慢查询EXPLAIN一跑问题立刻现形。重点看几个字段EXPLAIN SELECT user_id, total_amount FROM orders WHERE user_id 1024;type字段非常直观从好到差大致是system、const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描基本可以断定这条SQL要出事了。key字段显示实际用的索引rows字段是优化器预估扫描行数Extra里出现Using filesort和Using temporary通常意味着排序和临时表这些都是性能隐患。实际排查过的一个案例某报表查询从两百毫秒恶化到八秒EXPLAIN之后发现typeALLrows预估扫了两百多万行Extra还带了Using filesort。问题的根源是查询里对索引列做了函数操作导致索引失效。改写SQL让函数操作移到常量的那一侧再配合一个合适的复合索引查询恢复到了三百毫秒以内。5. 事务与并发数据正确性的最后防线很多业务逻辑出问题不是SQL写错而是事务用的不对。事务是数据库和文件方案最本质的分水岭理解它你才算真正用上了数据库的能力。5.1 一次扣款聊聊ACID用最经典的转账场景演示。账户A给账户B转100元数据库要做两步A扣100B加100。如果这两步之间发生异常A扣了钱B没收到业务方马上就炸了。事务要保证的就是这两个操作要么都成功要么都不发生。这就是原子性。事务的四个特性是原子性、一致性、隔离性、持久性。一致性说的是事务执行前后数据都处于合法状态隔离性是多个事务并发时不互相干扰持久性是一旦提交数据不会因为宕机而丢失。初学者不需要把术语背得滚瓜烂熟但需要记住只要涉及多步写入操作并且数据之间有依赖就必须放进同一个事务里处理。5.2 隔离级别怎么理解并发事务会带来三个经典问题脏读、不可重复读、幻读。脏读是读到别的事务未提交的数据不可重复读是同一个事务里两次读同一行结果不一样幻读是同一个事务里两次查询结果集凭空多出几行。为了应对这些问题SQL标准定义了四级隔离级别。隔离级别脏读不可重复读幻读读未提交可能可能可能读已提交不可能可能可能可重复读不可能不可能可能InnoDB实际可防串行化不可能不可能不可能MySQL默认的隔离级别是可重复读PostgreSQL默认是读已提交。大部分互联网业务用默认级别就好。隔离级别越高并发性能越差串行化因为加锁太狠几乎只在极端一致性场景中使用。选级别的时候考虑清楚不要让所有事务都跑到串行化否则并发能力会断崖式下降。5.3 死锁与锁等待的处理经验死锁两个经典场景。场景一连接一先锁A再锁B连接二先锁B再锁A两边互相等待谁都不让。场景二批量更新时两个事务更新同一批数据但顺序不一致。排查死锁最直接的工具是看引擎状态MySQL里执行SHOW ENGINE INNODB STATUS能把最近一次死锁涉及的事务和锁全列出来。我踩过的一个真实坑是批量更新订单状态时代码里用了ORDER BY order_id本身没有问题但另一个任务更新时排序字段不一致两个任务并发跑偶发死锁。当时的解决方法是统一所有批量更新的排序顺序同时把每个事务处理的记录数控制在较小范围让事务快速提交锁等待概率就大幅下降了。经验就是锁这东西等得越久越容易出事事务越短、加锁顺序越统一死锁概率越低。6. 常见问题与排查技巧实录这一节是我平时排查问题的记录遇到了好的排查思路我会顺手记下来。这里放几个出现频率最高的场景。6.1 线上慢查询排查清单慢查询排查的顺序基本固定先确认慢在哪里再通过执行计划找原因最后对症下药。第一步开启慢查询日志并设置阈值例如超过两秒的SQL全部记录第二步把慢SQL拿出来跑EXPLAIN看type、key、rows和Extra第三部根据结论加索引或改写SQL。排查时有一个容易忽略的点SQL本身不慢但并发量上来之后整体变慢这往往不是索引问题而是数据库连接和CPU被打满慢查询只是现象不是原因。这类问题需要顺着连接数、线程数、锁等待时间一层层往上查千万别一头扎进SQL优化里出不来。6.2 连接池配置的坑应用连接数据库几乎都通过连接池管理而不是每次请求都新建连接。连接池配置最大的坑是把最大连接数调得巨大表面上是为了冗余实际效果是数据库连接数被打爆数据库进程直接拒绝服务。连接是宝贵的资源每个连接背后都有线程、内存和可能的锁状态。经验值连接池上限参考机器CPU核心数通常核心数乘以2左右起步再根据实际压测结果微调。另外事务内的数据库操作要尽快释放尤其避免在事务里做远程调用或长时间等待一个长事务占住连接和锁后面的请求全部排队事故就是这么来的。连接池代码要用完了记得归还别让资源泄漏积累成故障。6.3 误删数据的应急处理没有备份的误删严格来说是事故不是故障。真遇到了DELETE忘了WHERE或者UPDATE全表更新正确的处理顺序是先停止应用的写入避免二次破坏然后根据备份和二进制日志恢复数据。在MySQL里开启binlog后可以用mysqlbinlog解析日志定位到误操作语句之前的位置再把后续数据重放回去。这个流程平时看着平淡真出事时非常考验手速和心态。所以我的建议一直是生产环境必须开启binlog必须做定时备份而且备份要定期做恢复演练确认备份文件真的能恢复。没做过恢复演练的备份等于没有备份。趁着还没出事把这件事当成线上基本盘里的头等大事来抓。从建表到索引从SQL到事务这一篇覆盖的是数据库日常使用中最高频的几块内容。我个人在实际操作中的体会是数据库的知识光靠看永远记不牢必须自己建一张表、填几万行数据、刻意写几条慢SQL再用EXPLAIN亲手把问题揪出来这个过程走一遍比看十篇教程都有用。这个系列之后我还会继续写下一篇大概率会专门深入锁与隔离级别在并发场景下的实际操作也可以聊索引底层结构和B树的更多细节。你可以按自己的需要挑着看。最后分享一个小习惯每次建表之前先画一遍逻辑模型写完SQL先跑一遍EXPLAIN把这两个动作变成肌肉记忆至少能帮你避开一半的线上问题。

数据库入门实战：从建表、SQL到索引与事务一次讲透

相关推荐

TensorFlow Lite移动端集成：从模型转换到实时推理的工程实践

Hot-Swappable合约架构：实现智能合约规则热插拔与动态升级

开源AI编程教程被AI助手推荐：内容架构与索引优化实战

更多资讯

Redis分布式锁高并发实战：锁过期、误删与看门狗

Excel ROW与COLUMN函数实战指南：从序列生成到矩阵运算

Log变换：偏态数据归一化的关键技巧与Python实战

IoTDB数据订阅机制详解：实时流式与TsFile文件订阅的工程实践

CNN图像分类实战：从数据准备到迁移学习的完整PyTorch流程

上下文工程实战：从72%到91%准确率的智能体优化复盘

今日更新

电商智能运营体系从0到1：架构设计、落地步骤与避坑指南

压缩空气储能与热网热惯性联合优化调度：热电联产系统建模与求解

若依框架AI代码生成不稳定？用Agent Skill三层结构+五步法搞定

本周精选

金融AI落地指南：从调研报告到技术选型与工程实践

大模型上下文成本与注意力优化技术深度解析——为何O(n²)平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测

基于GMM的语音识别系统原理与MATLAB实现详解

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？