做了这么多年数据相关工作接触过的数据库不少但兜兜转转MySQL始终是绕不开的那个。后台服务、管理系统、中台业务、数据分析平台的底层存储大半都能看到它的身影。网上讲MySQL的教程铺天盖地但多数要么停留在安装和登录要么一上来就甩几十条优化参数读者看完还是不知道日常开发到底该怎么设计表、怎么写SQL、怎么排查慢查询。这篇我把“MySQL必备基础”串成一条实操主线按建表、索引、SQL写法、事务锁、备份运维、问题排查六个环节来展开讲清楚每一步为什么这么做以及最常见的坑在哪。适合刚接触MySQL两三个月的新手也适合用过一阵但没系统性捋过的开发同学直接照着操作就能少走不少弯路。1. 建库建表与字段设计决定你后面三年省不省心很多新手拿到数据库第一件事就是建表字段现想现写等业务跑起来才发现类型不够用、字符集乱码、索引没地方加最后只能在大表上做痛苦的DDL。我在实际项目里的体会是建表花十分钟认真设计比后面花十小时修修补补要划算得多。1.1 字段类型选错到底有多伤字段类型不是随便挑一个能存就行。先看最常用的一类整数类型。TINYINT、SMALLINT、INT、BIGINT很多人只知道“越大越能存”却没想过存储空间和索引效率的代价。TINYINT只占1字节范围是-128到127SMALLINT占2字节范围到32767INT占4字节约21亿BIGINT占8字节几十亿以上才需要。这里有个经典误解INT(11)里的11不代表能存11位数它只是显示宽度配合zerofill才有视觉意义存数的上下限完全由INT本身决定。所以别为了“保险”把所有的ID、状态、排序字段全部上BIGINT一个表几十个字段都翻几倍索引树自然又宽又慢。字符串类型同样有讲究。CHAR是定长VARCHAR是变长两者在存储和比较上的行为差异很大。CHAR适合长度基本固定的场景比如手机号、身份证号、MD5值引擎不需要额外记录长度信息VARCHAR适合描述性文本比如昵称、备注、URL。注意VARCHAR(255)和VARCHAR(30)在超长字符串场景下对内存临时表排序、GROUP BY操作的影响截然不同。还有一个容易忽略的点utf8mb4字符集下一个字符最多占4字节如果索引前缀长度计算不准建索引时会直接报“Specified key was too long; max key length is 3072 bytes”之类的错误这在老版本上尤其常见。金额和经济类数据必须用DECIMAL千万别用FLOAT或DOUBLE。浮点数是近似存储做累加、比较时会出现0.10.2不等于0.3的经典问题。DECIMAL是定点数按位精确存储配合合适的精度和标度比如DECIMAL(10,2)能保证账目严谨。日期时间类型里DATETIME和TIMESTAMP各有侧重DATETIME不依赖时区范围从1000年到9999年TIMESTAMP依赖数据库时区范围到2038年。如果业务是全球化部署建议统一用DATETIME避免时区转换时数据错乱。类型一旦上线再修改成本极高。一个大表的ALTER TABLE可能耗时数分钟甚至更久影响线上读写。所以建表时宁可在合理范围内多花几分钟确认也别抱着“先跑起来再说”的心态。1.2 字符集和排序规则乱码和ORDER BY不对劲都从这里查字符集问题是我见过最多、也最容易被忽略的基础问题。以前老项目大量使用utf8mb3也就是平时说的utf8它只能存基本多语言平面字符遇到emoji、生僻汉字就直接报错或变乱码。现在统一用utf8mb4才是稳妥方案。在建库时就该固定好CREATE DATABASE demo_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;排序规则里那个0900是Unicode版本ai表示不区分重音ci表示不区分大小写。MySQL 8默认用utf8mb4_0900_ai_ci而MySQL 5.7及以前常见的是utf8mb4_general_ci或者utf8mb4_unicode_ci。这三者在排序比较时性能差异不大但碰到特殊字符、德语元音变音、法语重音字符时比较结果可能不同。如果从旧版本迁移到MySQL 8要留意线上查询是否依赖原来的排序行为尤其是有唯一索引或ORDER BY的字段。连接层字符集同样要盯住。客户端连接时最好显式设置SET NAMES utf8mb4;或者用Java、Python这些语言的连接参数指定characterEncodingutf8mb4。实践里经常出现“表结构是utf8mb4程序写入乱码”的情况最后定位发现是连接层没设置。更隐蔽的问题是字符集不一致会让索引失效。比如一个字段的列字符集是utf8mb4却用utf8mb3的字符串去WHERE关联MySQL无法直接使用索引只能做转换再比较性能立刻下降。1.3 建表时把索引想清楚比后面给大表加索引强十倍索引不是DBA的事是建表那一刻就要想的事。建索引前先问三个问题哪些字段会出现在WHERE条件里哪些字段需要排序或分组哪些字段要做唯一性约束一张常用业务表示例CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 业务订单号, user_id BIGINT UNSIGNED NOT NULL, status TINYINT NOT NULL DEFAULT 0, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, created_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_status_time (user_id, status, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;订单号需要频繁按单查询加唯一索引既有约束又能快速定位。用户查订单时通常同时带用户ID、状态、创建时间所以做成联合索引idx_user_status_time。联合索引要遵守最左前缀原则查询条件里必须有user_id才能用到这个索引只查status或者created_at是用不上的。如果只想看某个用户最近订单那么user_id created_at这个组合其实更高效具体取舍要看业务里高频查询是“用户状态”还是“用户时间”。反过来那些区分度极低、基本不筛选的字段比如性别、是否删除这种只有两个取值的列单独加索引基本是浪费B树空间。索引的价值在于快速缩小扫描范围区分度太低时优化器算一下成本发现还不如全表扫描索引就形同虚设。2. 索引到底怎么用才算把MySQL玩明白了设计完索引只是第一步更关键的是要读懂一条SQL到底有没有用上索引。很多开发同学索引建了一堆慢查询依旧一堆原因就是没验证过执行计划只是凭感觉“我觉得它用了索引”。2.1 从一条慢SQL开始学会看EXPLAINEXPLAIN是排查SQL问题最直接的工具。它不会真正执行查询只展示优化器预计的执行计划。拿一条典型查询举例EXPLAIN SELECT order_no, total_amount FROM order_info WHERE user_id 10086 AND status 1 ORDER BY created_at DESC LIMIT 20;输出结果里重点看几个字段。type这一列从好到差大致是system、const、eq_ref、ref、range、index、ALL。ref表示通过非唯一索引匹配到某几行range表示范围扫描ALL就是全表扫描必须警惕。key列表示实际使用的索引名如果为NULL说明没有命中索引。rows是优化器估算要读取的行数这个数字越大越危险。Extra列也有讲究出现Using filesort说明排序没有用到索引出现Using temporary说明用到了临时表这两类都值得优化。探究一下就能发现上面这条SQL在user_id有索引的前提下查询走ref但ORDER BY created_at是否能利用联合索引完全取决于联合索引里created_at排在哪个位置。如果是idx_user_status_time那么user_id和status走索引后created_at的排序无法继续复用索引可能额外做filesort。这种细节不实际执行EXPLAIN根本看不出来。2.2 索引生效与失效的边界一张表列明白日常开发里“写法和索引不匹配”是最常见的问题。我整理了一个速查表场景为什么会失效正确写法WHERE DATE(create_time) 2024-01-01列上套函数索引无法用于范围匹配create_time 2024-01-01 AND create_time 2024-01-02WHERE mobile 13812345678列是varchar隐式类型转换索引失效应用层保证传字符串WHERE name LIKE %关键词%前导模糊匹配无法按前缀定位业务上改搜索引擎或使用全文索引WHERE a 1 OR b 2a有索引b没有OR两边都要扫优化器可能直接全表拆成两个查询UNION或让b也建索引联合索引(a,b)WHERE b 1违反最左前缀原则调整索引顺序或加单列索引范围条件后的列a 100 AND b 1b无法继续走索引把等值条件放前面函数和隐式转换是最坑的两个。当你看到明明建了索引EXPLAIN里key还是NULL时第一反应就查这两点。曾经接手过一个线上系统订单表的手机号字段是varchar程序里却传入Integer类型MySQL做了隐式转换索引失效每次查询全表扫跑了很久没人发现。最后把应用层字段类型统一成字符串查询时间从秒级降到毫秒级。2.3 哪些场景真的不该加索引不是所有字段都适合索引。表很小比如几千行配置表全表扫描本身就走一遍几毫秒的事建索引反而占据额外空间并增加写入开销。区分度极低的字段效果也差性别列基本只有两个值索引二叉树很快退化成线性扫描。高频写入、低频查询的表每个索引都要在插入、更新时同步维护索引太多会让写入性能直线下降。最容易被忽略的是冗余索引已经有一个(a, b)联合索引又建了一个单独的a索引这就是重复。可以用下面命令检查SHOW INDEX FROM order_info;我见过一个表上同时存在idx_user_id和idx_user_status_time的情况后者已经覆盖了前者的前缀idx_user_id纯属多余删掉后写入性能立刻改善。加索引之前问一问这个索引能帮我少扫多少行如果回答不了就先别加。3. SQL基本功别在CRUD上栽跟头索引解决的是查询快慢问题而SQL本身写得好不好决定的是逻辑正确性和会不会出事故。很多开发几年下来CRUD写得飞快但细节一扣就漏。3.1 UPDATE和DELETE前不确认条件事故就是这么来的写UPDATE和DELETE时漏掉WHERE或者WHERE写错范围是数据库操作中最容易出大事的情况。之前处理过一次线上事故某个服务要更新一批用户的会员状态代码里update语句的条件变量在某些边界场景下没拼进SQL结果变成对整张表执行了全量更新。解决办法不是靠运气而是养成几个习惯UPDATE和DELETE之前先用相同WHERE跑一遍SELECT确认影响行数和数据范围是否符合预期必要的情况下用LIMIT做分批限制例如UPDATE t SET status 1 WHERE user_id 10000 LIMIT 200避免一次锁住过多数据多行DML务必放进显式事务一旦影响范围不对执行ROLLBACK还能救回来。MySQL的UPDATE语法支持LIMIT子句这个用法在日常批量修复数据时极为实用。比如发现一张表的某字段写入了一批错误数据想分批修正UPDATE coupon SET status 2 WHERE batch_no 20240101 AND status 1 LIMIT 500;每批最多改500行观察执行时间和锁状态循环执行直到影响行数为0。这个模式在生产环境调整数据时非常稳不用一把梭全量更新也方便随时中断检查。3.2 排序与分页坑比你想的多ORDER BY看着简单但要想高效尽量让排序字段走索引。如果SELECT的字段、WHERE条件能和索引完全匹配上Extra里就不会出现Using filesort排序直接在索引扫描时完成。分页深翻页是个经典问题。LIMIT 1000000, 20看起来只是取20条但MySQL需要先扫描或者跳过前100万条记录行数越大成本越高。一个常用优化是延迟关联先用覆盖索引找出目标主键再回原表取详细字段SELECT o.* FROM order_info o INNER JOIN ( SELECT id FROM order_info WHERE user_id 10086 ORDER BY id DESC LIMIT 1000000, 20 ) t ON o.id t.id;这样内层查询只扫描索引不碰数据页面效率要高出几个量级。另一种更彻底的办法是改成游标分页利用WHERE id 上一页最大id ORDER BY id LIMIT 20这需要客户端记住上一页的位置但对超大数据集最友好。翻页深度特别大、同时展示大量历史数据的场景直接考虑这类方案。3.3 GROUP BY、COUNT和HAVING的常见误用WHERE和HAVING很多人傻傻分不清。WHERE是分组前过滤HAVING是分组后过滤。查“每个用户的订单总额超过1000的用户”这样写SELECT user_id, SUM(total_amount) AS total FROM order_info WHERE status 1 GROUP BY user_id HAVING total 1000;用WHERE先过滤掉非成功订单减少分组数据量再用HAVING过滤分组结果顺序反了性能会差很多甚至得到错误结果。COUNT的写法也有讲究。COUNT()是对行的统计COUNT(字段名)只统计该字段非NULL的数量。统计全表行数时用COUNT()它没有任何“慢”的毛病InnoDB做了专门优化。COUNT(1)和COUNT(*)在MySQL里没有性能差异选一个看着顺眼的就行。还有一个常见错误在GROUP BY模式下查询列中出现了既不是分组字段、又不是聚合函数的列MySQL 8默认开启了ONLY_FULL_GROUP_BY会直接报错。比如查每个用户的最近订单时间不能简单SELECT user_id, order_no然后GROUP BY user_id必须明确order_no怎么聚合用MAX、MIN或者其他方案。4. 事务与锁数据一致性是被这样保住的MySQL的InnoDB是事务型存储引擎理解事务机制才算从“会写SQL”走到“懂数据库”。事务和锁的话题看起来很空但并发场景下不出问题就没人提一出问题就是脏数据或死锁。4.1 事务的ACID与自动提交很多人其实没吃透ACID是原子性、一致性、隔离性、持久性。原子性意味着事务里要么全部执行要么全部回滚隔离性是多个事务并发时的可见性规则。日常开发默认autocommit1每条单独的SQL就是一个事务。但多条DML必须绑定成业务事务时就应该显式开启START TRANSACTION; UPDATE account SET balance balance - 100 WHERE account_id 1; UPDATE account SET balance balance 100 WHERE account_id 2; COMMIT;只要有一条UPDATE报错程序里必须走ROLLBACK否则前面成功的更改就生效了两边账就对不上。这里有个隐式提交的坑在事务中执行DDL语句比如CREATE TABLE、ALTER TABLEMySQL会隐式提交当前事务之前未提交的数据变更会被固化。还有SET AUTOCOMMIT这种会话级设置同样会触发隐式提交。事务里别混着写DML和DDL。4.2 隔离级别为什么MySQL默认是可重复读标准SQL定义了四种隔离级别区别就在于并发条件下一个事务能不能看到其他事务未提交或已提交的更改隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED不可能可能可能REPEATABLE READ不可能不可能可能SERIALIZABLE不可能不可能不可能MySQL InnoDB默认是REPEATABLE READ配合MVCC和间隙锁机制基本消除了幻读问题这也是它敢把默认级别设在这的原因。很多其他数据库默认READ COMMITTED于是有些从别的数据库转过来的同学会疑惑“为什么默认不能读到其他事务提交的新数据”。实际业务里如果你对隔离级别没有强需求直接保持默认最稳。只有遇到高并发插入场景中明显的间隙锁竞争时才考虑单独把会话级别调成READ COMMITTEDSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;隔离级别影响的是并发正确性和锁竞争程度调整前必须理解业务到底要防什么。为了性能牺牲正确性是得不偿失的。4.3 行锁、间隙锁与死锁到底怎么排查InnoDB的锁是行级锁但不是所有条件都能锁行。如果UPDATE的WHERE条件没走索引InnoDB会扫描全表并对扫描到的记录逐条加锁表现上等同于锁全表。所以更新、删除操作的WHERE条件务必走索引这既是性能问题也是并发问题。间隙锁是REPEATABLE READ下为了防止幻读引入的机制它会锁住索引记录之间的间隙。这就导致一个现象明明只更新了1行但并发插入时会被锁阻塞尤其是在范围条件的UPDATE或DELETE下。死锁的经典场景是两个事务按相反顺序更新两条记录。会话A锁表1等表2会话B锁表2等表1两边都不让InnoDB自动检测到死锁后会回滚其中一个事务。排查死锁最快的方法是看InnoDB状态SHOW ENGINE INNODB STATUS;重点看LATEST DETECTED DEADLOCK部分里面会显示两个事务各自持有和等待的锁。解决死锁没有银弹但有几条经验非常有效多个事务始终按同一个顺序访问资源比如都先更新account_id1再更新2事务尽量短小减少锁持有时间控制单个事务涉及的行数避免大范围批量更新。5. 备份恢复和基础运维平时不吃力出事才救命不少开发同学觉得备份和运维是DBA的事情自己负责写好代码就行。但小团队没有专职DBA很多生产MySQL就是开发兼着管。基础备份和日志排查是“MySQL必备基础”里最被低估的部分。5.1 逻辑备份与物理备份怎么选mysqldump是最常用的逻辑备份工具导出的是SQL文本可读性好可以改库名、导单表、做迁移。缺点是速度慢适合中小数据量的场景。关键参数要记牢mysqldump -h 127.0.0.1 -u backup_user -p \ --single-transaction --quick --routines --triggers \ --default-character-setutf8mb4 \ demo_db demo_db_$(date %F).sql--single-transaction在InnoDB下基于一致性快照备份备份期间不锁业务表--routines和--triggers用于导出存储过程和触发器容易漏。需要注意的是逻辑备份在恢复时是逐条执行SQL备份和恢复都要在业务低峰期进行。数据量动辄几十GB、上百GB时mysqldump的恢复速度会让人崩溃此时得考虑物理备份方案比如直接复制数据目录或使用专门的物理备份工具。备份策略不能只做全量日常应该“全量增量”结合。每天凌晨全量白天开启binlog随时可以基于binlog把数据恢复到任意时间点。恢复操作不是嘴上说说建议每季度做一次恢复演练找个测试实例把备份导回去确认备份文件真的能用来恢复。很多团队备份文件堆了一堆真到恢复时才发文件损坏或备份不全。5.2 binlog与增量恢复把数据找回的关键binlog即二进制日志记录了对数据有变更的SQL或行事件是增量恢复和数据复制的基础。生产库务必开启配置文件中设[mysqld] log_bin mysql-bin server_id 1 expire_logs_days 14 max_binlog_size 256M查看当前binlog文件SHOW BINARY LOGS; SHOW MASTER STATUS;假设今天凌晨全量备份后上午10点误删了一张业务表想恢复到10点之前的状态流程是先恢复全量备份再重放binlog到指定时间mysql -u root -p demo_db demo_db_20240101.sql mysqlbinlog --stop-datetime2024-01-01 10:00:00 mysql-bin.000101 | mysql -u root -p demo_db这里有个细节binlog里也包含了误删除前所有正常业务写入只要你知道误操作发生的准确时间点就能精准截断。所以生产环境里记录维护操作的时间点是一个非常有价值的习惯。5.3 日志和状态检查快速判断数据库是否健康日常巡检不需要装一堆监控工具命令行就能看个大概。错误日志通常记录启动、关闭、异常崩溃等信息查看位置SHOW VARIABLES LIKE log_error;慢查询日志是优化SQL的宝库。开启方式SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time设为1秒写入超过1秒的SQL都会被记录日志会准确定位到哪些SQL需要优化。很多时候拿到一条慢SQL用EXPLAIN一看问题基本都能发现优化完再刷新慢查询日志确认效果。连接数异常也是常见问题。执行SHOW PROCESSLIST;观察有没有大量State为Sending data或Waiting for table metadata lock的连接。如果应用连接数暴增可能是代码里的连接没释放也可能是某条慢SQL把连接都占住了。找到问题会话后根据实际需要kill掉KILL 123456;6. 常见问题排查与避坑实录最后这个环节我整理几个实践里反复遇到的坑。这些问题不在教科书第一章但每一个都值得记到自己的避坑清单里。6.1 字段类型不匹配导致的隐式转换前面提过隐式转换会使索引失效。排查慢SQL时如果发现一个明明该走索引却走了ALL的查询立刻看两件事WHERE后面字段的数据类型和传入参数的数据类型是不是一致。曾经有个用户表的id字段在业务里有两种用法一种是数值一种是带字母的字符串程序没有统一类型。后面把所有入口都强制为字符串问题才彻底解决。隐式转换是最让人头疼的“看不见的性能杀手”因为它不会报错只会默默拖慢查询。6.2 MySQL 8连接报加密插件不兼容MySQL 8默认的认证插件是caching_sha2_password老版本客户端和旧驱动可能不认识连接时报Authentication plugin无法加载的错误。解决办法有两个方向升级所有客户端和驱动到支持caching_sha2_password的版本或者为老客户端单独建账号并指定老的认证插件CREATE USER legacy_app% IDENTIFIED WITH mysql_native_password BY StrongPass123; GRANT SELECT, UPDATE ON demo_db.* TO legacy_app%;新项目一律建议升级驱动认证插件关系到密码传输方式尽量选更安全的默认方案。6.3 大表ALTER TABLE卡住或长时间无响应给大表加索引或修改字段时即使底层支持在线DDL也会遇到阶段性的元数据锁MDL竞争。如果当时有长时间未提交的事务占了MDL锁DDL会一直阻塞然后后续所有的查询和更新全部堵住形成雪崩。执行在线DDL前先排查是否存在长事务SELECT * FROM information_schema.innodb_trx;把那些跑了几小时的事务处理掉再执行DDL。生产环境做大表结构调整强烈建议选在业务低峰并且先在小表上测试语句耗时或者使用在线迁移工具进行分批处理。跳过这一步的后果往往是数据库连接数瞬间被打满。6.4 别急着分库分表先看看基础优化做了没很多人遇到“表数据多、查询慢”时第一反应是分库分表或者换中间件。但我觉得先别急拖慢查询的原因八成以上出在基础优化没做透。要么是缺了关键索引要么是SQL写法不友好要么是查了大量不需要的列和数据行。先把慢查询日志打开把TOP SQL用EXPLAIN跑一遍该加索引加索引该重写重写观察一两周再说。只有确认了单表数据量和索引优化都没法满足业务增长需求才去考虑分表、分库或者引入更重的方案。我个人在实际操作中养成了一个习惯每次提交代码前把涉及新表或新SQL的语句先在测试库跑一遍EXPLAIN看type是不是ref或者rangerows是不是在可控范围内有没有出现Using filesort和Using temporary。这个习惯帮我挡下了很多上了生产才暴露的慢查询。还有一个小技巧建任何表都显式指定ENGINEInnoDB和CHARSETutf8mb4不依赖数据库默认配置这样换环境、迁移数据时都不会因为隐式默认值不同而出幺蛾子。数据库的问题大多是前期设计时敷衍、运行期才爆发基础打牢一点后面真的能省下无数个熬夜排查的夜晚。