上个月帮人排查一个订单查询慢的问题。应用层做了缓存、换了连接池参数、SQL也重构过一版结果效果都不明显。最后进数据库把那条SQL单独拎出来EXPLAIN一看发现建表时字段类型选错了索引根本没走到预期路径上。问题不在应用代码而在最基础的数据库操作环节——表结构设计时没有按查询习惯去规划。这让我翻出了自己最早学MySQL时的笔记发现当时记的都是“命令语法”缺少一层“为什么这么做”的逻辑后来吃了不少亏才慢慢补上。这篇是整理后的第一篇把数据库操作最常用的命令按“操作逻辑”来讲而不是单纯列语法。适合刚入门的数据分析师、后端开发新手以及在日常工作中需要应急处理MySQL的运维同事参考。1. 连接数据库第一条命令的完整正确姿势1.1 最常用的连接参数与命令行写法连接是MySQL所有操作的前提。我见过不少同事在本地写脚本连库时把密码直接贴在命令里虽然能跑通但隐患很大。最稳妥的交互式写法是mysql -h 192.168.1.10 -P 3306 -u app_user -p回车后MySQL会提示输入密码。-h指定主机-P指定端口注意是大写P-u指定用户名。如果不写-P默认走3306端口不写-h默认连本机。这些默认值在日常开发中很省事但在生产环境排查时一定要显式写出来避免连错实例。如果觉得每次交互式输入密码太麻烦可以用mysql_config_editor工具预先保存登录信息mysql_config_editor set --login-pathlocal --host127.0.0.1 --userapp_user --password mysql --login-pathlocal -e SELECT NOW();这样密码不会明文出现在脚本历史里登录时也不需要重新输入适合在跳板机上快速操作。1.2 连接参数选择背后的逻辑连接时有一个容易忽略的点MySQL的账号权限是绑定“用户主机”两个维度的。比如app_userlocalhost只允许本机连接远程连接则需要在授权时指定对应IP段或%。所以“能ping通、端口通、但连不上MySQL”的排查方向往往不是网络而是账号主机匹配问题。新建一个远程访问账号的典型语句CREATE USER app_rw10.20.% IDENTIFIED BY 安全密码; GRANT SELECT, INSERT, UPDATE, DELETE ON biz_db.* TO app_rw10.20.%; FLUSH PRIVILEGES;这里10.20.%表示只允许10.20网段的机器连接比直接开%要安全得多。数据库账号权限的最小化原则和操作系统用户一样给多了权限出问题时排查范围也会跟着扩大。1.3 连接阶段最容易踩的三个坑第一个坑是时区不一致。MySQL服务器默认SYSTEM时区应用服务器如果用的UTC写入的DATETIME和读取的时间会出现偏差。建议在连接参数中显式指定时区Java的JDBC URL可以加serverTimezoneAsia/Shanghai命令行连接可以先执行SET time_zone 08:00;确认当前会话时区。第二个坑是密码明文留在shell历史记录里。很多人在命令行直接写mysql -u root -p123456执行完history一翻就全暴露了。如果实在需要在脚本里使用可以借助MYSQL_PWD环境变量但要注意设置好脚本文件的权限避免其他用户读到。第三个坑是连接数打满。看到“Too many connections”时不是代码层面的问题而是数据库端max_connections已满。可以用SHOW STATUS LIKE Threads_connected;查看当前连接数再用SHOW VARIABLES LIKE max_connections;确认上限。很多时候连接数异常上涨是因为应用连接池没有配置最大空闲时间导致连接不释放这需要在应用侧调参数而不是盲目调高数据库上限。2. 库操作CREATE / ALTER / DROP 之间的设计取舍2.1 创建数据库不是一句CREATE DATABASE就完事很多新手建库就是一条CREATE DATABASE biz_db;后面遇到中文乱码才回头补字符集配置。正确的做法是从一开始就把字符集和排序规则定好CREATE DATABASE IF NOT EXISTS biz_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;这里utf8mb4是完整的UTF-8实现支持emoji和生僻字utf8mb4_0900_ai_ci是MySQL 8.0的默认排序规则不区分大小写。如果是MySQL 5.7及更早版本没有0900这个collation可以用utf8mb4_general_ci或utf8mb4_unicode_ci。选库的字符集时想清楚一个问题排序规则决定了模糊查询时的匹配行为比如大小写是否敏感、中文拼音排序是否生效这些在后续开发中很难在应用层弥补。2.2 字符集与排序规则的选择字符集和排序规则的关系可以类比“字符编码字典”和“字典里的排序方式”。utf8mb4表示字符的存储方式而_ci表示比较大小写时不区分。如果业务要求用户名或邮箱严格区分大小写需要单独在建表时使用utf8mb4_bin或在查询时显式加BINARY。修改已有库的默认字符集经常遇到ALTER DATABASE biz_db DEFAULT CHARACTER SET utf8mb4;但要注意这个操作不会自动修改库里已有表的字符集。表的字符集是独立的需要逐表ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4;。我曾在一个老项目里只改了库的字符集结果新表正常、旧表继续乱码排查了半天才发现是表级别没改。所以接手老库时先执行下面这条语句看清全貌SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA biz_db;2.3 修改库和删库的边界意识ALTER DATABASE能改的其实只有字符集、排序规则和加密选项想改库名在MySQL里没有直接的RENAME DATABASE。常见的迁移办法是创建新库、用mysqldump导出再导入、切换应用配置、删除旧库。这一步务必做好备份和灰度方案我曾经见过直接把生产库改名失败后部分表的权限跟着丢失恢复比预想麻烦得多。DROP DATABASE更不用多说执行后所有表、存储过程、视图全部消失。除非确认有备份或者库本来就是临时测试用否则不要在生产环境敲。稳妥的做法是先RENAME TABLE把重要数据表移到备份库观察几天确认无问题后再清理。3. 表设计字段类型与约束决定未来十年的查询复杂度3.1 字段类型选择INT、VARCHAR、DECIMAL的实际取舍字段类型选错是慢查询的隐形根源。以用户ID为例用INT还是BIGINT取决于表的数据量和增长预期。INT最大约21亿如果业务表每天增长百万级几年就逼近上限BIGINT占用的空间更大但省去了后期改造主键的麻烦。从我的经验看核心业务表的自增主键直接上BIGINT比较稳妥DBA后期改主键类型代价极高。VARCHAR的长度定义经常被误解成字节数其实它是字符数。VARCHAR(255)能存255个字符按utf8mb4编码最多占1020字节超过了InnoDB单行索引的最大长度限制。所以设计索引列时VARCHAR长度不要随手写255要预估业务实际长度比如订单号一般32位就够手机号用VARCHAR(20)都算富余。金额字段建议用DECIMAL(10,2)不要用FLOAT或DOUBLE。浮点数在计算机里是近似值0.10.2的经典问题在数据库里同样存在。比如金额精确到分DECIMAL(10,2)表示最多8位整数加2位小数大多数业务足够用如果要支持更大的金额或更高精度可调整为DECIMAL(14,4)。时间字段优先用DATETIME时区敏感场景用TIMESTAMP。很多团队习惯加两个审计字段created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间ON UPDATE CURRENT_TIMESTAMP可以在记录更新时自动维护时间省去应用层手动赋值。3.2 约束与默认值从业务语义出发约束不是为了建表好看而是把数据合法性的校验放在数据库层。主键约束保证唯一性非空约束NOT NULL减少业务代码里的空指针判断唯一约束UNIQUE KEY保护业务标识不重复。一张实际订单表的示例CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 业务订单号, user_id BIGINT NOT NULL COMMENT 下单用户ID, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 0-待支付 1-已支付 2-已取消, remark VARCHAR(255) DEFAULT NULL COMMENT 备注, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_created (user_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单表;这里用TINYINT表示状态而不是VARCHAR节省空间且语义清晰order_no加唯一索引从数据库层拦截重复单号联合索引(user_id, created_at)覆盖“查某个用户近期的订单”这个高频查询场景。外键约束FOREIGN KEY在互联网业务中我一直建议慎用。它虽然能保证引用完整性但在高并发写入时容易产生额外的锁开销且分库分表后外键基本失效。现在的做法通常是应用层保证关联逻辑数据库里只建普通索引。这个取舍和团队规范强相关不能一概而论。3.3 存储引擎与表结构演进当前MySQL的主流存储引擎是InnoDB支撑事务、行级锁、崩溃恢复默认即为它。MyISAM在MySQL 8.0以后基本没有使用场景它不支持事务表锁在高并发写入时非常痛苦。检查一张表的引擎和字符集SHOW TABLE STATUS FROM biz_db LIKE orders;这里可以重点看Engine、Row_format、Collation三列。Row_format为Dynamic时说明表里存在变长字段InnoDB会动态存储如果是Compact说明早期定义的表结构比较紧凑但遇到大字段时可能会有存储利用率的问题。表结构变更是另一个大坑。ALTER TABLE ADD COLUMN在数据量大的表上会拷贝数据耗时很长。MySQL 8.0虽然支持ALGORITHMINPLACE的一些操作但实际执行前还是建议先查看执行计划成本。线上大表加字段我会优先选择在低峰期操作并预估一个可接受的时间窗口如果表超过千万行可以考虑pt-online-schema-change这类工具但工具本身的配置和依赖也需要演练不能直接在生产上试。4. 索引慢查询的根源往往不在SQL而在索引设计4.1 索引怎么帮助查询的B树直觉理解索引和数据表的关系可以类比书的目录和正文。没有目录时找某一页要一页页翻有了目录先定位章节再翻到具体页快很多。MySQL InnoDB的索引底层是B树它的特点是叶子节点存储实际数据非叶子节点只存索引键数据按顺序排列查询和范围扫描都很快。主键索引的叶子节点存的是整行数据所以通过主键查询是最快的路径。二级索引普通索引的叶子节点存的是主键值查询时先找到主键再回表拿整行数据这个过程叫“回表”。如果二级索引的叶子节点已经覆盖了你需要的所有字段就不需要回表这叫“覆盖索引”这也是SELECT里不要随手写*的一个深层原因——只查必要字段MySQL更有机会命中覆盖索引。4.2 索引类型对比与使用场景常用索引类型有三类索引类型特点典型场景主键索引唯一且非空叶子节点存整行通过id查找、唯一业务标识唯一索引保证列值唯一允许NULL订单号、手机号、邮箱普通索引加速查询不强制唯一user_id、状态列、时间列联合索引要重点理解“最左前缀”匹配规则。索引(user_id, created_at)意味着查询条件里包含user_id时能用到这个索引只查created_at则用不到。所以在设计联合索引时把区分度高的字段放前面把等值查询的字段放前面范围查询字段放后面。一条比较典型的查询SELECT order_no, amount FROM orders WHERE user_id 123 AND created_at 2024-01-01 ORDER BY created_at DESC LIMIT 20;这里(user_id, created_at)的联合索引就非常合适user_id做等值过滤created_at同时服务于范围过滤和排序排序不再需要额外的filesort过程。4.3 EXPLAIN 看执行计划而不是靠猜判断一条SQL是否走索引最直接的手段就是EXPLAINEXPLAIN SELECT order_no, amount FROM orders WHERE user_id 123 ORDER BY created_at DESC LIMIT 20;重点看这几列typeconst最好ref和range也还可以ALL意味着全表扫描是性能瓶颈。key实际用到的索引名如果为NULL说明没用索引。rows估算扫描的行数越小越好。ExtraUsing index表示覆盖索引Using filesort意味着排序没有用到索引Using temporary意味着查询产生了临时表。有一次帮人看慢查询SQL条件里有WHERE status 1status列只有0和1两种值区分度极低优化器认为走索引和全表扫描成本差不多最后选择了全表扫描。EXPLAIN里能看到key为NULL这时候真正要做的不是强制FORCE INDEX而是调整查询策略——比如把status的过滤条件拆走或者改成按主键做范围扫描。索引不是越多越好。每个索引在写入时都要更新索引树越多写入速度越慢磁盘占用越高。核心表的索引数量控制在个位数比较合理建立索引前先想清楚这条查询是不是高频这个字段的区分度够不够联合索引能不能覆盖多个查询模式5. DML操作INSERT / UPDATE / DELETE 中的事务与边界5.1 INSERT的批量插入与性能平衡逐条插入在数据量小的时候没问题但批量场景下性能差距就很明显了。一次插入多行比多次单行插入要快得多因为减少了客户端与MySQL服务端的往返次数INSERT INTO orders (order_no, user_id, amount, status) VALUES (NO20240101001, 1001, 99.00, 0), (NO20240101002, 1002, 199.00, 0), (NO20240101003, 1003, 59.00, 0);如果业务存在“插入后有冲突就更新”的需求MySQL提供了ON DUPLICATE KEY UPDATE语法INSERT INTO order_snapshot (order_no, status, updated_at) VALUES (NO20240101001, 1, NOW()) ON DUPLICATE KEY UPDATE status VALUES(status), updated_at VALUES(updated_at);这里依赖order_no上的唯一索引或主键。要注意REPLACE INTO和ON DUPLICATE KEY UPDATE的区别REPLACE INTO遇到冲突时会先删旧行再插入新行可能导致自增ID跳号、外键关联失效ON DUPLICATE KEY UPDATE只做更新行为更可控绝大多数业务场景建议用后者。5.2 UPDATE的WHERE条件与影响行数确认UPDATE写错WHERE条件是数据库事故的最高发原因之一。先看一条经典的危险语句UPDATE orders SET status 1;这条语句会把整张表的状态全部改成已支付。如果这是线上操作基本就是重大事故。我在实际踩坑中养成的习惯是执行UPDATE前先用相同的WHERE条件跑一遍SELECTSELECT order_no FROM orders WHERE order_no NO20240101001; -- 确认无误后再执行 UPDATE orders SET status 1 WHERE order_no NO20240101001;还有一个细节执行完UPDATE后客户端返回的“Rows matched / Changed”代表什么Rows matched是WHERE条件命中的行数Changed是实际值被修改的行数。如果存在值本身与新值相同的情况Changed会小于matched这是正常的不需要怀疑语句没执行成功。默认情况下MySQL是自动提交事务的也就是说每条UPDATE执行完就直接持久化。想给自己留一条回退保险可以在执行重要操作前先START TRANSACTION;确认结果没问题再COMMIT;有问题就ROLLBACK;。5.3 DELETE与事务回滚的配合DELETE的误操作风险比UPDATE更高因为数据删了很难恢复。大表删除还要注意锁和性能问题一次删几十万行InnoDB会占用大量的undo空间同时行锁持有时间拉长很可能拖垮业务写入。避免大事务删除的常见策略是分批删除DELETE FROM order_log WHERE created_at NOW() - INTERVAL 6 MONTH LIMIT 1000;配合循环语句每次只删除1000行间隔一段时间再继续下一批。这样每批事务都比较小锁粒度可控undo日志不会爆炸。注意LIMIT在MySQL的DELETE语法里是支持的只要在满足条件后加上即可。TRUNCATE TABLE和DELETE有本质区别TRUNCATE是把整张表的存储空间直接重置速度极快但不可按条件过滤也不会触发普通的行级DELETE触发器DELETE可以加WHERE条件精确清理。清空表前要确认这是不是测试表线上表宁可花时间分批删也不要图快一把TRUNCATE。6. 查询语句打磨从 SELECT * 到执行效率的进阶路径6.1 SELECT * 的问题不只是带宽浪费SELECT *最大的问题不是多返回了几个字段而是破坏了覆盖索引的命中条件。这张订单表有联合索引(user_id, created_at)如果只查order_no, amount这个联合索引不一定能完全覆盖如果查询所有列就必然要回到主键索引取整行增加了回表成本。一个实用的建议是查询里只写需要的字段。写代码时确实麻烦一点但换来的是SQL更容易命中覆盖索引、网络传输字节数更小、结果集更清爽。特别是对接前端接口时多返回无用字段还可能导致序列化性能下降边际影响很大。6.2 WHERE与JOIN条件过滤顺序的理解很多人在大表JOIN时习惯写SELECT u.user_name, o.order_no FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 1 AND o.created_at 2024-01-01;MySQL的优化器通常会自动调整表的连接顺序先过滤数据量小的一侧。但前提是过滤条件里涉及的字段有合适的索引。如果orders表的user_id或created_at没有索引JOIN就会变成逐行扫描。开发者容易踩的误区是“先JOIN再过滤”和“先过滤再JOIN”的结果差异。逻辑上只要JOIN类型是INNER JOIN结果是一样的但性能上能在一个表上用索引提前过滤就不要让它带着全量数据去做连接。查看执行计划如果发现type为ALL基本可以定位到瓶颈表。GROUP BY和HAVING也经常被误解。WHERE是在分组前过滤行HAVING是在分组后过滤分组。比如查“订单金额大于1000的用户数”SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total_amount FROM orders WHERE status 1 GROUP BY user_id HAVING total_amount 1000;这里WHERE status 1先排除无效订单HAVING total_amount 1000再筛聚合结果。如果把金额过滤放在WHERE里逻辑就错了。MySQL 8.0默认开启ONLY_FULL_GROUP_BY模式SELECT的字段必须出现在GROUP BY中或使用聚合函数包裹否则直接报错。这个模式对规范SQL有很好的约束作用。6.3 ORDER BY和LIMIT的分页优化深分页是另一个典型的性能杀手。LIMIT 100000, 20在逻辑上要先扫描前面的100000行然后把它们丢弃只返回最后的20行。数据量一大这个“丢弃”的过程非常耗时。常见优化方式是“延迟关联”或用子查询先定位主键SELECT o.id, o.order_no, o.amount FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id 123 ORDER BY id DESC LIMIT 100000, 20 ) tmp ON o.id tmp.id ORDER BY o.id DESC;子查询只查主键能充分利用二级索引和主键索引外层再通过主键拿到完整行这个方案在千万级数据量下也有明显改善。另一个思路是记住上一页的最后一条记录的主键下一页查询直接以它为条件SELECT id, order_no, amount FROM orders WHERE user_id 123 AND id 上一页最后一条id ORDER BY id DESC LIMIT 20;这种“游标分页”在高并发场景下性能最优因为它不依赖偏移量越往后翻差距越大。6.4 一条真实慢SQL的优化过程复盘某业务报表需要查某个用户在某个时间段内的订单明细和汇总金额最初的SQL长这样SELECT * FROM orders WHERE user_id 456 AND status IN (0, 1, 2) AND created_at BETWEEN 2024-01-01 AND 2024-03-31 ORDER BY created_at DESC;表里有两万多条该用户的订单但查询一直在1秒以上。EXPLAIN显示typeALL也就是说走了全表扫描。索引方面user_id上有单列索引但查询里同时包含created_at和排序单列user_id无法完全支撑优化器选择不走到最优路径。后来把索引调整为(user_id, status, created_at)同时把SELECT *改成只查需要的字段SELECT order_no, amount, status, created_at FROM orders WHERE user_id 456 AND status IN (0, 1, 2) AND created_at BETWEEN 2024-01-01 AND 2024-03-31 ORDER BY created_at DESC;执行时间从1秒多降到了几十毫秒。这个案例的启发是慢查询排查的关键不只是“有没有索引”而是“索引的列顺序是不是匹配查询模式”。如果user_id单列索引先过滤用户再在结果集里过滤状态和时间效率显然不如联合索引直接把三个条件一次性收敛。我在实际工作中还养成了一个习惯定期从慢查询日志里捞Top SQL逐个用EXPLAIN过一遍。很多时候数据库变慢不是突然出现的而是业务量增长后以前勉强能用的SQL终于撑不住了。基础操作手册最终还是要在真实数据量下检验纸上谈兵没有意义。
