★ FEATURED ARTICLE 做后端开发这些年MySQL几乎是我每天都要打交道的数据库。不管项目大小业务代码里最频繁出现的还是那几条SQL建表、插数据、查数据、更新、删除也就是大家常说的“增删改查”。很多人觉得MySQL基础操作就是背几条命令但真到了生产环境坑往往就藏在最基础的地方——字段类型选得不合适、索引没建对、一条查询把整张表扫了一遍轻则接口变慢重则直接把数据库拖垮。这篇文章我打算把MySQL基础操作里值得掌握的细节认真捋一遍包括建库建表、增删改查、索引和慢查询也会把我实际踩过的坑一并交代清楚。适合刚开始学数据库的朋友也适合那些写了好几年业务、想回来补基础的老手。1. 先把基础打牢MySQL到底是什么能解决什么问题1.1 为什么几乎所有项目都在用MySQL先聊一个很多人没认真想过的问题为什么MySQL能在那么多数据库里脱颖而出我个人理解答案是四个字稳定、省心。关系型数据库需要解决的核心问题是把数据结构化地存下来并且支持灵活的查询、更新和事务。MySQL作为关系型数据库的代表把ACID事务、SQL查询、索引这些能力做得足够成熟同时开源免费、部署成本低文档和社区案例多到看不完。这意味着团队招人能快速上手出了问题能快速查到解决方案对大多数业务来说性价比极高。你可能听说过有些大厂的核心场景在用别的数据库但对中小型团队来说MySQL就是最稳妥的默认选择。它不像NoSQL那样在特定场景下有极致性能但它覆盖的场景最广用户系统、订单系统、内容管理、库存管理几乎都能用它。更重要的是从单机到主从复制再到分库分表MySQL有一套渐进式的扩容路径。业务从小到大数据库方案可以跟着平滑演进这一步是很多替代品做不到的。当一个技术选型能让你从创业初期一直用到业务规模化你自然就明白它为什么长盛不衰了。1.2 基础操作的完整地图你真正要掌握的是这些很多人一上来就抱着SQL语法手册背背完就忘遇到实际问题还是不知道从哪下手。我的建议是先把地图画出来。MySQL基础操作看起来内容很多实际可以分成六大块库和表的创建与修改对应CREATE DATABASE、CREATE TABLE、ALTER TABLE这类DDL语句数据的增删改查对应INSERT、SELECT、UPDATE、DELETE查询的高级能力比如JOIN关联、GROUP BY分组、ORDER BY排序、LIMIT分页和子查询索引设计与优化包括唯一索引、普通索引、联合索引以及EXPLAIN执行计划分析事务与锁重点理解BEGIN、COMMIT、ROLLBACK以及锁等待现象最后是备份与恢复主要掌握mysqldump和binlog的基本用法。这张地图列出来之后你会发现基础操作本身并不难难点永远在“选择”和“排查”上。同一个需求用自增主键还是业务主键用VARCHAR还是TEXT用一条JOIN还是拆成多条查询这些选择直接决定了系统后续的性能和可维护性。所以后面几节我不会只罗列命令而是把每个环节的关键判断逻辑展开讲。你把这张地图吃透再去看任何复杂SQL都会发现它们只是基础操作的组合。2. 环境准备与第一张表动手前的关键选择2.1 安装、连接与基础配置的注意点安装MySQL本身不难官网下载对应版本或者用系统包管理器一行命令装好。但有几个点如果没注意后面会频繁踩坑。第一个就是字符集。MySQL 8.0之前的默认字符集是Latin1如果你建库时不显式指定字符集存中文很容易变成乱码。我现在的习惯是建库建表一律显式指定utf8mb4。utf8mb4兼容utf8还能存emoji和一些生僻字是当前最稳妥的选择。建库时可以这样写CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;第二个是连接工具。命令行永远是最可靠的后备方案日常开发和调试我习惯用可视化客户端但排查问题时一定要会用命令行登录mysql -h127.0.0.1 -P3306 -uroot -p这里容易踩的坑是本机如果装了多个MySQL实例或者端口被改过连接时要用-P参数显式指定端口。连接成功后先执行SELECT VERSION();确认版本避免你以为自己在8.0环境实际连到了一个5.7实例有些语法行为差异会把你搞懵。另外新版MySQL安装后默认有密码策略要求初始化时生成的临时密码一般会写在日志文件里第一次登录后要尽快修改这个细节经常卡住新手。第三个是配置文件。Linux下通常改/etc/my.cnf重点检查几个参数character-set-server、default-storage-engine、max_connections、innodb_buffer_pool_size。日常开发机不用调得太激进但字符集和服务端口这些问题尽量在一开始就确定好。改完配置一定要重启MySQL服务很多人改了半天发现没生效就是忘了这一步。我自己就经历过改了端口没重启软件连的还是旧端口白白折腾了半小时。2.2 建库建表字段类型选错了后面全是坑建表是MySQL基础操作里最值得花时间的一步因为表结构一旦上线后面要改就牵扯数据迁移代价很高。先看一个完整的建表语句CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(64) NOT NULL COMMENT 用户名, email VARCHAR(128) DEFAULT NULL COMMENT 邮箱, age TINYINT UNSIGNED DEFAULT NULL COMMENT 年龄, balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 账户余额, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;这个建表语句里藏着几个看着简单、实际很重要的选择。主键用BIGINT自增而不是INT是因为INT最大约21亿对很多增长快的业务来说不够用而BIGINT基本不会踩上限。id加上UNSIGNED配合自增彻底避免出现负数主键。金额字段一定不要用FLOAT或DOUBLE浮点数存在精度损失算着算着账户余额就会少几分钱对财务系统来说完全不可接受必须用DECIMAL。age这种字段用TINYINT就够1字节能存0到255完全没必要用INT字段长度越小索引和数据文件占用的空间就越小。创建时间和更新时间字段建议每张表都加而且用DEFAULT CURRENT_TIMESTAMP自动填充。很多团队排查线上数据问题时都是靠created_at定位记录产生的时间靠updated_at判断最近有没有被改过没有这俩字段会非常被动。每张表都要设置主键尽量用自增主键不要用UUID当主键。UUID是随机字符串插入时会造成大量随机IO索引节点容易分裂表大了之后性能下降非常明显。如果确实需要业务主键也尽量选有序、定长的字段。3. 增删改查核心操作的细节与坑3.1 INSERT、UPDATE、DELETE 的正确姿势增删改查里INSERT是相对安全的操作但也有一些讲究。单条插入很好写批量插入时建议用一条语句多组值而不是循环执行单条INSERT。写法是这样的INSERT INTO user (username, email) VALUES (user1, aexample.com), (user2, bexample.com), (user3, cexample.com);相比循环执行单条INSERT批量插入能大大减少网络往返和SQL解析开销。10条数据看不出区别但上万条时差异就非常明显。还有一个高频需求是“数据存在就更新不存在就插入”此时用INSERT ... ON DUPLICATE KEY UPDATE比先SELECT再判断要靠谱得多。因为先查再判断的流程在并发下容易踩竞态条件两条请求同时查到不存在就会产生重复数据。示例INSERT INTO user (id, username, balance) VALUES (1, user1, 100.00) ON DUPLICATE KEY UPDATE balance balance 100.00;再说UPDATE最危险的就是忘了写WHERE一条UPDATE会把整张表的数据全部改掉。我见过不止一次开发环境被人一条SQL清空表的情况所以我的习惯是执行UPDATE和DELETE之前先写一条等价的SELECT确认范围。这个习惯能救你很多次-- 先确认 SELECT * FROM user WHERE status 0; -- 再执行 UPDATE user SET status 1 WHERE status 0;DELETE也类似但还有一个容易忽略的点DELETE是逐行删除并写日志的如果只是想清空表数据用TRUNCATE TABLE会快得多因为它直接丢弃整个表的数据页。不过TRUNCATE不能加WHERE条件而且会重置自增计数所以要分清场景。需要保留表结构但又想快速清掉全部数据TRUNCATE很合适只删一部分业务数据只能用DELETE。批量修改多条数据时务必用事务包起来这样中途出错可以整体回滚START TRANSACTION; UPDATE user SET balance balance - 10 WHERE id 1; UPDATE warehouse SET stock stock - 1 WHERE product_id 100; COMMIT;事务能保证多条SQL要么全部成功要么全部失败。如果你在事务中间发现数据不对劲直接ROLLBACK就行不用手动去还原每一条。这里要强调一个新手很容易犯的错开了事务却忘了COMMIT结果锁一直不释放其他会话全部卡住这个场景在后面常见问题部分会专门展开。3.2 SELECT 查询的进阶技巧SELECT是基础操作里花样最多的部分。最基本的WHERE、ORDER BY、LIMIT我就不铺开讲了重点说几个容易被忽略的细节。第一个是避免SELECT *。线上表的字段可能很多SELECT *会把不需要的字段也查出来白白增加网络传输和数据库IO。特别是用ORM框架的查询时很多人图省事写SELECT *数据量一上来性能就崩。正确做法是只查自己需要的字段比如SELECT id, username, balance。第二个是JOIN的理解。INNER JOIN只返回两边都匹配的数据LEFT JOIN会保留左表的所有记录右表没有匹配就补NULL。很多人死记硬背还是容易搞混我分享一个简单经验先看哪边是主表要保留谁谁就是左表。例如SELECT u.username, o.order_no, o.amount FROM user u LEFT JOIN order o ON o.uid u.id WHERE u.status 1;这条语句返回所有正常用户以及他们名下的订单没有订单的用户订单字段会是NULL。这里要特别提醒LEFT JOIN关联时右表的关联字段一定要有索引否则每一条左表记录都要去右表做全表扫描查询会慢得离谱。凡是遇到“关联查询慢”先看关联字段的索引八成问题出在这里。第三个是分组与过滤。GROUP BY搭配COUNT、SUM、AVG这类聚合函数是统计类需求的标配。HAVING和WHERE的区别一定要搞清楚WHERE在分组之前过滤原始行HAVING在分组之后过滤聚合结果。比如查订单量超过3单的用户SELECT uid, COUNT(*) AS order_cnt FROM order WHERE status 1 GROUP BY uid HAVING order_cnt 3;这条SQL的实际执行顺序是先按status过滤掉无效订单再按uid分组最后只保留满足HAVING条件的分组。如果你把status条件放到HAVING里结果可能一样但效率会差很多因为很多本应该提前过滤掉的行都参与了分组计算。3.3 一个完整的业务操作示例把前面这些串起来我模拟一个简单的订单系统操作流程。假设用户下单后要同时完成三件事插入订单、扣减用户余额、扣减库存。这三个动作必须在一个事务里完成否则就会出大问题比如钱扣了订单没生成。代码是这样的START TRANSACTION; INSERT INTO order (uid, order_no, amount, status) VALUES (1, NO202501010001, 199.00, 1); UPDATE user SET balance balance - 199.00 WHERE id 1; UPDATE warehouse SET stock stock - 1 WHERE product_id 100; COMMIT;说到“查账”前端列表页通常需要展示某个用户的最近订单记录。这时候不能一把梭把全表数据捞出来而是要加过滤条件、排序和分页既能保证查询走索引也能控制返回数据量。下面这条SQL用来查询某个用户最近10条订单实际项目中写法很常见SELECT o.order_no, o.amount, o.created_at FROM order o WHERE o.uid 1 ORDER BY o.created_at DESC LIMIT 10;如果用户后续发起退货我们需要更新订单状态并回退余额。这个场景同样涉及两步更新依然建议放在事务里执行UPDATE order SET status 0 WHERE order_no NO202501010001; UPDATE user SET balance balance 199.00 WHERE id 1;注意这两条更新余额的语句都用了“余额金额”的写法而不是先SELECT出来、在程序里算好新值再写回去。这样做的好处是避免并发下读到旧值导致更新丢失。两个人同时退款如果用“读-算-写”的老思路第二次写入会覆盖第一次的结果而直接累加两次更新都基于数据库当前值执行最终结果天然正确。这个原子更新思路属于MySQL基础操作里最容易被忽视、偏偏又最重要的意识之一。4. 索引与性能基础操作背后的核心逻辑4.1 为什么索引这么重要很多新手写SQL用得挺溜一遇到慢查询就懵其实问题大多出在索引上。先把原理讲透MySQL的InnoDB引擎用B树组织索引索引能帮你把查询范围从“整张表扫一遍”缩小到“顺着树走几步”。没有索引时MySQL只能全表扫描。数据量小的时候无所谓一旦上了百万行一条全表扫描的查询可能要好几秒而走索引可能只需要几毫秒。理解了这一点你就能理解为什么索引是MySQL性能的第一关键。索引的类型主要分几种主键索引、唯一索引、普通索引、联合索引。主键索引就是主键字段唯一索引保证字段值唯一普通索引只加速查询不限制重复联合索引是多个字段拼在一起。建索引的语法很简单CREATE INDEX idx_uid_created ON order (uid, created_at);但索引不是越多越好这个坑几乎每个人都踩过。每建一个索引写入数据时就要多维护一棵B树插入和更新的性能都会下降还占磁盘空间。所以索引要以“实际查询需求”为准哪个查询频繁过滤哪个字段才值得建索引。一个简单的判断标准看你的WHERE、JOIN、ORDER BY里反复出现的字段那是索引的候选者一个只在SELECT列表里出现、从不参与过滤的字段不需要建索引。联合索引有一个核心法则叫最左前缀索引按照字段顺序存储查询条件里如果从第一个字段开始连续匹配就能用到这个索引如果跳过第一个字段直接查后面的字段索引大概率用不上。比如上面这个索引(uid, created_at)支持WHERE uid 1也支持WHERE uid 1 AND created_at 2025-01-01但不支持单独用WHERE created_at 2025-01-01过滤。理解了这一点联合索引的字段顺序就知道怎么排了最常用的等值条件放最前面范围条件放后面。如果你拿不准就请教一下有经验的同事或者用EXPLAIN验证。4.2 慢查询与执行计划基础基础操作里一定要会用EXPLAIN。任何你觉得慢的SQL都可以在前面加EXPLAIN看看MySQL到底打算怎么执行EXPLAIN SELECT * FROM order WHERE uid 1 ORDER BY created_at DESC LIMIT 10;执行结果里重点看几个字段type、key、rows。type是访问类型从好到差大概有const、ref、range、index、all这几个常见级别。all就是全表扫描range是范围扫描ref是普通索引等值匹配const是主键或唯一索引匹配到唯一一条。我们写SQL的底线是避免all尽量做到ref或range。key字段表示实际用到的索引如果显示NULL说明这条SQL没走索引。rows是预估扫描行数这个数字越小越好。我见过一个典型场景订单查询明明有WHERE也有ORDER BY但就是慢EXPLAIN一看type是allrows有几十万。原因是查询条件里对字段做了函数处理比如WHERE DATE(created_at) 2025-01-01索引直接失效。改成范围写法WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00查询立刻从几百毫秒降到几毫秒。这是基础操作里特别值得养成的一个习惯不要在索引字段上套函数。同样的道理也适用于模糊查询LIKE %keyword%因为开头不是固定值索引基本帮不上忙如果业务允许改成LIKE keyword%还能走索引效果差异巨大。除了EXPLAIN慢查询日志也是排查利器。MySQL可以配置一个阈值超过阈值的SQL都会被记录下来。开发环境可以临时开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;生产环境一般建议由专职的数据库管理角色统一负责但作为开发人员至少要会看慢查询日志。拿到一条慢SQL之后用EXPLAIN分析执行计划定位是全表扫描、没走索引还是索引选择不对然后针对性优化这基本就是一个完整的排查闭环。5. 常见问题与排查技巧实录5.1 连接不上、乱码、锁等待等典型问题基础操作做得多了总会遇到几个高频问题。我整理了一个速查表都是实际处理过的场景现象常见原因排查思路连接不上数据库服务没启动、端口被占用、账号权限不对先在本机用命令行登录排除网络因素再查服务状态和端口中文乱码客户端、连接、表字符集不一致统一为utf8mb4连接配置里显式指定字符集执行SQL卡住有事务没提交锁没释放执行SHOW PROCESSLIST找Sleep状态的会话必要时KILL掉删除或更新报外键约束错误有关系表数据不一致先查关联表数据按业务规则处理插入报重复键错误唯一键冲突确认业务是否允许覆盖用ON DUPLICATE KEY UPDATE处理先说连接不上的排查顺序。MySQL服务启动后默认监听3306端口连接失败时第一步永远是在MySQL所在机器上本地登录一次确认服务本身是好的。如果本地能连、远程不行再查账号授权和防火墙。权限这块很多新手喜欢用GRANT ALL ON *.*把所有库都授权给应用账号这个习惯太危险了。正确的做法是只给应用账号它需要的那一个库的权限权限最小化能少很多麻烦即使账号泄露影响范围也可控。锁等待是另一个高频问题而且往往直接导致线上事故。我遇到过一个很典型的场景某位同事在开发环境开了一个事务执行了一堆SQL然后去做别的事了事务一直悬着没提交结果整个项目组其他人对同一张表的更新全部卡住。排查方法很简单执行SHOW PROCESSLIST;看到大量Sleep状态的连接基本就是有事务没结束。处理办法是先确认那个事务是否可以被回滚然后用KILL命令结束对应会话。需要牢记的是所有涉及事务的操作务必在代码里用try/finally保证COMMIT或ROLLBACK一定会执行这是线上事故的高发点怎么强调都不为过。5.2 一次恢复误删数据的经历基础知识里最容易被忽略的其实是恢复能力。我讲一段真实经历。某内部项目在开发阶段有人想清理一张测试表的过期数据执行了一条DELETE语句但WHERE条件写错了结果把整张表的数据都删了。开发环境没有冷备份当时大家一度以为数据彻底没了。后来怎么解决的用binlog。MySQL的binlog会记录所有变更操作只要binlog是开启状态。我们用SHOW BINLOG EVENTS找到了误删操作对应的日志位置再把这段日志反转把被删的数据重新插回去。具体命令各家版本差异很大这里不展开全部细节但我想强调两个点第一只要binlog开着误删数据大概率能恢复这件事本身就能给你兜底第二恢复操作的复杂度远比备份要高有些人折腾半天还不一定能恢复完整所以日常做好备份才是根本。我的备份习惯是开发环境每天用mysqldump备份一次生产环境按团队规范维护定期备份和binlog归档。最少你也要会手动备份这一条命令mysqldump -uroot -p --single-transaction --set-gtid-purgedOFF shop backup.sql--single-transaction参数能在不锁表的情况下获得一致性快照对InnoDB表很友好这个参数要记住。恢复时用mysql命令导入备份文件即可。基础操作阶段就把备份练成肌肉记忆比等出了问题再上网查要靠谱得多。最后再分享一个我个人的小习惯。写任何一条稍微复杂的SQL之前先跑一遍EXPLAIN确认它没有全表扫描执行UPDATE、DELETE之前先把WHERE条件对应的SELECT跑一遍确认影响范围准确无误。这两个习惯让我少踩了无数坑也希望你能在刚入门的时候就建立起来。MySQL基础操作看起来是语法问题本质上是在建立对数据存储的直觉——理解数据怎么落盘、怎么被索引、怎么被锁定很多所谓的高级问题都会变得很好解。
阅读完成 · 觉得有帮助？
