MySQL基础操作实战：从增删改查到索引慢查询优化

做后端开发这些年，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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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', 'a@example.com'), ('user2', 'b@example.com'), ('user3', 'c@example.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是all，rows有几十万。原因是查询条件里对字段做了函数处理，比如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 连接不上、乱码、锁等待等典型问题

基础操作做得多了，总会遇到几个高频问题。我整理了一个速查表，都是实际处理过的场景：

先说连接不上的排查顺序。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-purged=OFF shop > backup.sql

--single-transaction参数能在不锁表的情况下获得一致性快照，对InnoDB表很友好，这个参数要记住。恢复时用mysql命令导入备份文件即可。基础操作阶段就把备份练成肌肉记忆，比等出了问题再上网查要靠谱得多。

最后再分享一个我个人的小习惯。写任何一条稍微复杂的SQL之前，先跑一遍EXPLAIN确认它没有全表扫描；执行UPDATE、DELETE之前，先把WHERE条件对应的SELECT跑一遍，确认影响范围准确无误。这两个习惯让我少踩了无数坑，也希望你能在刚入门的时候就建立起来。MySQL基础操作看起来是语法问题，本质上是在建立对数据存储的直觉——理解数据怎么落盘、怎么被索引、怎么被锁定，很多所谓的高级问题都会变得很好解。