MySQL $用法全解析：从shell变量到索引优化与备份排查

“$ 在 MySQL 里到底算个什么东西”这个问题我在不同场合被问过至少七八次问的人里有刚学 MySQL 基础用法两周的在校生也有写了三四年业务代码、突然被派去写部署脚本的开发者。有意思的是大家卡住的地方几乎一样明明在终端里敲mysql -u root -p一切正常一旦把这个命令塞进 shell 脚本、用变量拼参数或者 SQL 语句里出现一个$立刻就开始报语法错误、认证失败、表名不存在。这篇就把这件事从头捋一遍顺带把 MySQL 基础用法里最容易翻车的几块内容——连接配置、字符集、字段类型、索引、增删改查、备份与排查——按我实际干活时的顺序铺开讲。不管你是刚接触数据库的新手还是想把自己零散的操作习惯整理成体系的老手下面这些内容基本都能直接拿去用。1. 先把标题里那个“$”摆正位置很多人看到“mysql $的用法”这个说法第一反应是 MySQL 有个叫$的语法特性。实话说MySQL 里没有一个独立叫$的东西它出现的场合分散在四五个完全不同的层面每个层面的含义、规则、坑点都不一样。分不清这几层你搜到的答案就会互相打架有人说$是变量有人说$是表名里的合法字符还有人说$是 JSON 路径的根。三个说法都对但说的根本不是一回事。1.1 shell 变量拼进 mysql 命令引号选错全盘皆输最常见的场景是写运维脚本把连接信息抽成变量。这里第一个要命的分歧点就是引号。双引号里$VAR会被 shell 展开成变量值单引号里则是原样字面量。看下面这段DB_HOST127.0.0.1 DB_USERapp_reader DB_NAMEorder_center # 正确双引号变量会展开 mysql -h $DB_HOST -u $DB_USER -p -D $DB_NAME # 错误单引号传给 mysql 的是字面量 $DB_USER mysql -h $DB_HOST -u $DB_USER -p -D $DB_NAME第二行那种写法报错信息通常是ERROR 1045 (28000): Access denied for user $DB_USERlocalhost。注意报错里那个用户名它真的就叫$DB_USER四个字符一段不多。我第一次遇到时盯着屏幕看了半天心想这账号明明建过怎么会不存在。后来才反应过来是引号的问题这个教训值五分钟但当时折腾了半小时。-e参数执行单条 SQL 时引号策略要更细致一层。外层双引号让 shell 展开变量内层的 SQL 字符串常量用单引号包住START_TIME2024-01-01 00:00:00 mysql -u app_reader -p -D order_center \ -e SELECT COUNT(*) FROM t_order WHERE create_time $START_TIME要是START_TIME是从外部传入、可能带空格或特殊字符的值最稳的做法是整条 SQL 用单引号包起来然后靠位置参数传值或者干脆改用--execute加 here-doc。here-doc 有个细节用EOF会展开变量用EOF不展开这个和引号规则是一致的。至于密码无论变量拼得多漂亮都别写成-p$DB_PASS。密码会进 shell history也会在ps aux里明文可见同机器上任何用户都能看到。正规做法是用配置文件后面第 2 章会细讲。1.2 MySQL 标识符里的 $能跑通但不建议碰第二层是数据库对象命名。MySQL 在标识符字符集上比标准 SQL 宽松未加引号的标识符允许出现 ASCII 字母、数字、下划线和$。也就是说下面这条语句在 MySQL 里是能执行的CREATE TABLE tmp$20240101 ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY );某些版本里$还能放在标识符开头我没有逐个版本验证过这种事也没必要验证。关键在于这是 MySQL 的方言扩展不是通用能力。你把表结构导到别的数据库或者用某些 ORM 的代码生成器、Schema 对比工具一旦碰上带$的名字轻则生成一堆奇怪的转义重则直接解析失败。更麻烦的是在 shell 脚本里操作这类表名$会被 shell 当成变量前缀你得层层转义。我的实际建议很干脆表名、字段名、索引名只保留小写字母、数字、下划线长度别超过 30 个字符。临时表要带日期用tmp_20240101这种下划线风格别为了省一个字符用$。命名这件事属于一次性成本低、后期收益高的工作前期随手埋的雷半年后总有人替你踩。1.3 别把 $ 和 搞混用户变量、占位符、JSON 路径三码事第三层是语法符号之间的混淆这个坑新手踩得最多。MySQL 的会话级用户变量用打头不是$SET min_amount 100; SELECT * FROM t_order WHERE amount min_amount;这类变量只在当前连接内有效断开就没了也不能直接用在CREATE VIEW里。它适合做一次性的临时计算不适合当配置项传参。预处理语句的占位符在 MySQL 里是?不是$1PREPARE stmt FROM SELECT * FROM t_order WHERE id ?; SET oid 1001; EXECUTE stmt USING oid; DEALLOCATE PREPARE stmt;$1、$2那种写法是另一款开源关系库的习惯两边混着记非常容易写错。如果你同时维护两个技术栈的项目建议在笔记里把这两套占位符并排放着写代码前扫一眼。真正把$当语法用的地方是 JSON 路径表达式SELECT JSON_EXTRACT(payload, $.user.name) AS user_name, JSON_EXTRACT(payload, $.items[0].sku) AS first_sku, JSON_LENGTH(payload, $.items) AS item_count FROM t_event_log WHERE JSON_EXTRACT(payload, $.type) pay;这里的$表示“文档根节点”$.往下走对象键$[0]取数组下标$[*]匹配所有数组元素。JSON 字段上的查询要提速可以建函数索引或者生成列再建普通索引直接对整列建索引基本没用。1.4 提示符里的 $ 只是个普通字符第四层最无害是客户端提示符。默认是mysql可以通过--prompt参数改成任何你喜欢的样子mysql -u root -p --prompt\u\h [\d] $ 这里$没有任何特殊含义就是个视觉符号看起来像当前库名后面跟了个美元符。\u是用户名、\h是主机、\d是当前数据库、\R是小时、\m是分钟。改提示符听着像小事但我在一台机器上同时开四五个会话时就吃过亏——三个窗口长得一模一样一个不小心把测试库的DELETE敲进了另一个窗口。后来统一改成带库名的提示符这种事故再没发生过。2. 开工前的三件事连接、字符集、权限很多人学 MySQL 是直接从CREATE TABLE开始的跳过了环境层。结果就是后面所有的报错都要绕回来查配置效率极低。我把这三件事放在建表之前是因为它们一旦定下来后面基本不用再动。2.1 连接参数集中管理别散落在命令行前面说过别把密码写命令行。标准做法是配置文件加--defaults-extra-file# /etc/mysql/conf.d/app_reader.cnf [client] userapp_reader password把这里换成实际密码 host127.0.0.1 port3306 default-character-setutf8mb4文件权限要卡死chmod 600 /etc/mysql/conf.d/app_reader.cnf chown appuser:appuser /etc/mysql/conf.d/app_reader.cnf调用时mysql --defaults-extra-file/etc/mysql/conf.d/app_reader.cnf -D order_center -e SELECT 1--defaults-extra-file和--defaults-file有个容易忽略的区别前者是在加载完默认配置之后再追加后者是直接替换掉默认配置。脚本里统一用前者不容易把系统级配置搞丢。还有一个已经废弃但仍能在老脚本里见到的环境变量方式就是把密码塞进MYSQL_PWD。这个变量在新版本里已经不推荐使用原因和命令行明文一样——同机器上的其他进程可以通过/proc下的信息读到。看到团队里有人这么写最好趁早改掉。连接串超时这块也值得设。客户端默认没有连接超时网络抖动时一个脚本能挂几分钟。可以在 client 段加上connect-timeout10配合脚本里的set -e一起用失败就立刻退出不要让后续逻辑拿着一个空结果继续往下跑。2.2 字符集和排序规则一次配齐别事后补救字符集问题的恶心之处在于它不马上报错。数据能插进去能查出来等你哪天发现某个昵称里的表情符号变成了问号或者生僻字变成了乱码再想改就是一场大工程。MySQL 8.0 里正确字符集就一个utf8mb4。注意不是utf8。在 MySQL 的语境里utf8是utf8mb3的别名最多三字节存不下四字节的补充平面字符表情符号就是典型受害者。这个历史遗留命名让无数人写错过建库语句。建库时把字符集和排序规则一起写死CREATE DATABASE order_center DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;utf8mb4_0900_ai_ci是 8.0 的默认排序规则ai表示 accent insensitive口音不敏感ci表示 case insensitive大小写不敏感。这意味着ABC和abc在比较时相等cafe和café也相等。这套默认值适合昵称、标题这类展示型字段但对订单号、手机号、证件号这类必须精确匹配的字段就不合适了。这类字段我会显式指定二进制排序规则CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, buyer_phone VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, remark VARCHAR(200) NOT NULL DEFAULT , PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_phone (buyer_phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;订单号和手机号是纯 ASCII用ascii字符集能把索引键长度压到最低ascii_bin保证大小写敏感、比较精确。这个细节在数据量上到千万级之后索引体积的差异会很明显。顺带提一句VARCHAR(32)的长度语义这里的 32 是字符数不是字节数。在 utf8mb4 下一个VARCHAR(32)字段最坏情况要占用 128 字节的存储。这一点在做内存估算和索引长度计算时必须记住很多人按字节算结果索引超长报错。2.3 账号按用途拆分权限给到刚好够用账号设计上我见过最省事的做法就是全公司共用一个高于等于 root 权限的账号所有服务、所有脚本、所有人的电脑上都配一份。这种做法的代价是出了数据事故永远查不到源头因为日志里只有同一个用户名。正确姿势是按用途分账号权限取最小可用集-- 读账号 CREATE USER app_reader10.% IDENTIFIED BY 读账号的密码; GRANT SELECT ON order_center.* TO app_reader10.%; -- 写账号 CREATE USER app_writer10.% IDENTIFIED BY 写账号的密码; GRANT SELECT, INSERT, UPDATE, DELETE ON order_center.* TO app_writer10.%; -- 备份账号只需要读和锁表相关权限 CREATE USER backup_user10.% IDENTIFIED BY 备份账号的密码; GRANT SELECT, RELOAD, PROCESS, SHOW VIEW, TRIGGER, LOCK TABLES, REPLICATION CLIENT ON *.* TO backup_user10.%; FLUSH PRIVILEGES;主机部分写10.%而不要写%。%表示任意来源一旦这台机器的端口暴露出去等于把门敞开了。如果你的服务和应用在同一内网用网段前缀限制是最省事又有效的办法。授权之后一定要用SHOW GRANTS复核一遍SHOW GRANTS FOR app_writer10.%;我遇到过的情况是给某个账号加了一个库的权限但没有REVOKE掉早期测试时给的全局权限结果这个账号能读所有库。这类残留权限在主从切换、库迁移的时候特别危险因为权限是跟着账号走的不是跟着库走的。3. 建表字段类型和索引这两刀要下准建表是整条链路里回头成本最高的环节。字段类型选错得改表、迁数据、改代码索引建错得等业务跑起来才看得出来那时候表已经有几千万行了。下面这几条是我这些年反复验证过的经验。3.1 主键、自增与业务唯一键的分工InnoDB 是聚簇索引结构数据行按主键顺序物理存放。这意味着主键选得好不好直接影响插入性能、页分裂频率和二级索引的大小。指导思想只有一个主键尽量短、单调递增、不含业务含义。自增BIGINT UNSIGNED是最稳的选择id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY为什么用UNSIGNED多一倍的正数空间且不会出现负数主键这种诡异数据。为什么用BIGINT而不是INTINT UNSIGNED上限约 42 亿看着够用但自增值会因为回滚、批量插入预分配等原因跳号实际可用量比理论值低不少加上一些日志类表增长极快用INT迟早要改。有些团队喜欢用 UUID 或雪花 ID 做主键理由是分布式环境下自增不好协调。这个理由在分库分表的场景下成立但代价必须清楚随机 UUID 做主键会让插入变成随机写页分裂严重插入性能可能掉到自增主键的几分之一二级索引也会因为主键变长而膨胀。如果确实要全局唯一 ID可以把它作为业务唯一键UNIQUE KEY主键仍然用自增两套 ID 各司其职。这个方案我在几个项目里用过代价是多一个索引换来的是插入性能和主键长度都可控。业务唯一键该建就得建而且必须是数据库层的UNIQUE不能只靠应用层查一遍再插。应用层查重和插入之间存在时间窗口并发下必然产生重复数据。唯一索引是最后一道防线UNIQUE KEY uk_order_no (order_no)3.2 金额、时间、状态字段的类型取舍金额字段用DECIMAL别用FLOAT或DOUBLE。浮点数在二进制下无法精确表示十进制小数0.1 0.2得到的是0.30000000000000004这类结果累计到财务报表上就是实实在在的差额。金额字段统一写成DECIMAL(12,2)或DECIMAL(15,4)前面是总位数后面是小数位。如果业务涉及多币种汇率换算小数位给到 6 位展示时再截断。时间字段DATETIME和TIMESTAMP的差别比很多人想的要大。维度DATETIMETIMESTAMP存储空间5 字节含小数秒另算4 字节取值范围1000-01-01 到 9999-12-311970-01-01 到 2038-01-19时区处理存什么读什么不做转换写入时转 UTC读取时转会话时区自动更新需显式写ON UPDATE支持DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPTIMESTAMP的 2038 问题不是段子是硬性上限。业务表里凡是可能活过 2038 年的一律用DATETIME。TIMESTAMP只适合记录行级操作时间这类“过期即无用”的场景。另外TIMESTAMP的时区转换行为在某些跨时区部署的系统里会造成困惑同一个值在不同会话里读出来不一样排查问题时容易误判。状态字段优先用TINYINT UNSIGNED配注释或者用短的VARCHAR。我不太建议用ENUM因为增加枚举值要改表结构ALTER TABLE在大表上是个重操作而且枚举值在应用层和数据库层容易出现两套定义对不上就没法排查。3.3 联合索引与最左前缀别把索引建反了索引这块最常见的误区是把联合索引的字段顺序当无所谓。KEY idx_a_b (a, b)和KEY idx_b_a (b, a)是两个完全不同的索引。以订单表为例高频查询是“按用户查最近订单”SELECT id, order_no, amount, create_time FROM t_order WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;对应的索引应该是KEY idx_user_time (user_id, create_time)字段顺序的逻辑等值条件放前面范围条件和排序字段放后面。这样索引既能用于过滤又能顺带满足排序省掉一次 filesort。如果把create_time放前面user_id放后面WHERE user_id ?这个条件就用不上索引了因为最左前缀匹配不上。覆盖索引是另一个提速手段。如果查询涉及的字段全在索引里MySQL 可以直接从索引返回数据不用回表。上面的查询如果把order_no和amount也加进索引就构成覆盖索引KEY idx_user_time_cover (user_id, create_time, order_no, amount)代价是索引变大、写入变慢。要不要做覆盖得看读写比。读多写少的订单查询表值得写密集的日志表就不划算。还有个细节索引列上不要套函数或做运算WHERE DATE(create_time) 2024-01-01会让索引失效。改成范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00这两句在结果上等价执行效率差一个数量级。4. 增删改查高频写法与安全边界SQL 写多了会发现真正出问题的从来不是复杂查询而是那几条天天写的增删改。下面挑几个实际踩过的点讲。4.1 批量插入与自增回填单条插入循环一千次和在一条语句里插一千行性能差距能有几十倍因为每次往返都有网络和解析开销。批量写法INSERT INTO t_order_item (order_id, sku, qty, price) VALUES (1001, SKU-A, 2, 39.90), (1001, SKU-B, 1, 129.00), (1001, SKU-C, 5, 9.90);批量插入有两点要注意。一是max_allowed_packet限制单条语句太大可能被服务端拒绝实践中每批控制在 500 到 1000 行为宜同时关注单行数据大小。二是自增 ID 的回填批量插入之后LAST_INSERT_ID()返回的是本批第一个自增 ID后续行的 ID 是连续递增的在默认的自增锁模式下简单插入语句的分配是连续的。但这条规律在并发插入、INSERT ... ON DUPLICATE KEY UPDATE、以及某些复制模式下不成立不要依赖“ID 一定连续”这个假设去推算业务数据。真要拿到每一行的自增 ID比较稳的做法是给业务加一个批次号字段插入后用批次号反查INSERT INTO t_order_item (batch_no, order_id, sku, qty) VALUES (B20240101-001, 1001, SKU-A, 2), (B20240101-001, 1001, SKU-B, 1); SELECT id, sku FROM t_order_item WHERE batch_no B20240101-001;ON DUPLICATE KEY UPDATE在幂等写入场景很好用但它有个副作用无论走插入还是走更新自增值都会被消耗。所以用了这个语法的表自增 ID 出现大段跳号是正常现象别拿来当数据量的参考。4.2 UPDATE 和 DELETE 的安全边界这两条语句的危险程度不需要多解释。我给团队定的规矩是三条先在同样条件下跑一遍SELECT COUNT(*)语句必须带明确的WHERE执行时开事务。START TRANSACTION; SELECT COUNT(*) FROM t_order WHERE status 9 AND create_time 2023-01-01; UPDATE t_order SET status 10, update_time NOW() WHERE status 9 AND create_time 2023-01-01 LIMIT 1000; -- 确认影响行数符合预期 COMMIT;LIMIT在这里的作用不是分批而是兜底。万一WHERE写漏了某个条件LIMIT 1000能把损失控制在一千行以内而不是整表。服务端还有个开关值得打开SET SESSION sql_safe_updates 1;开启后不带WHERE或者WHERE里没用索引的UPDATE/DELETE会直接被拒绝执行。这个开关对新手特别友好我一般建议在客户端配置文件的[client]段里加上让它默认生效。删除大量数据不要一条语句干完会产生大事务、长时间锁、主从延迟。按主键分批删DELETE FROM t_event_log WHERE id 1000000 ORDER BY id LIMIT 5000;循环执行直到影响行数为 0。每批之间 sleep 几十毫秒给主从复制留出追赶时间。4.3 分页、排序与聚合的实用套路LIMIT offset, size在 offset 很大时会非常慢因为服务端要先扫描并丢弃前 offset 行。翻到第 5000 页时一条查询扫几十万行是常事。延迟关联是通用解法SELECT o.id, o.order_no, o.amount, o.create_time FROM t_order o INNER JOIN ( SELECT id FROM t_order WHERE status 1 ORDER BY id LIMIT 100000, 20 ) t ON o.id t.id;内层子查询只走索引取主键不回表外层用主键关联取完整行。相比直接SELECT * ... LIMIT 100000, 20省掉了十万次回表。如果是无限下拉那种场景游标法更彻底连 offset 都不需要SELECT id, order_no, amount FROM t_order WHERE status 1 AND id 100000 ORDER BY id LIMIT 20;把上一页最后一条的id带过来作为下一页的起点。这种方式要求排序字段唯一且单调主键正好满足。聚合查询上8.0 默认开启ONLY_FULL_GROUP_BYSELECT里出现的非聚合字段必须在GROUP BY里。这个限制一开始会让人不习惯但它确实能挡住脏数据-- 会被拒绝 SELECT user_id, sku, SUM(qty) FROM t_order_item GROUP BY user_id; -- 正确写法 SELECT user_id, sku, SUM(qty) FROM t_order_item GROUP BY user_id, sku;统计类查询尽量加时间范围条件别让一条报表 SQL 扫全表。我曾经见过一条没带时间条件的日活统计跑在千万行表上跑了十几分钟还没出结果最后把连接池占满了。5. 把重复操作脚本化日常运维里真正耗时间的不是写 SQL是那些每天都要做一遍的动作备份、看慢查询、批量修数据。这三件事脚本化之后能省下大量来回切换的精力。5.1 备份与恢复参数比命令本身重要mysqldump谁都会敲但参数配不对备份出来的文件要么不一致要么恢复时缺东西mysqldump \ --defaults-extra-file/etc/mysql/conf.d/backup.cnf \ --single-transaction \ --quick \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ order_center /data/backup/order_center_$(date %Y%m%d_%H%M).sql--single-transaction是关键它通过开启一个一致性快照来保证备份期间数据一致前提是表都用 InnoDB。如果库里还有 MyISAM 表这个参数就失效了得改用--lock-tables。--quick让 mysqldump 逐行取数据而不是一次性把结果集读进内存大表必备。--set-gtid-purgedOFF在搭建测试库时很有用否则导出的文件里会带 GTID 信息导入时可能冲突。--routines、--triggers、--events这三个如果不加存储过程、触发器、定时事件全丢恢复之后业务逻辑莫名其妙就不对了。恢复的时候如果原库还在先建一个新库再导入验证无误后再切换不要直接往生产库上灌mysql --defaults-extra-file/etc/mysql/conf.d/backup.cnf \ -e CREATE DATABASE order_center_verify DEFAULT CHARACTER SET utf8mb4 mysql --defaults-extra-file/etc/mysql/conf.d/backup.cnf \ order_center_verify /data/backup/order_center_20240101_0300.sql备份文件要定期做恢复演练这一点比备份本身更重要。只备份不演练等于没备份——真出事的时候你才发现那个文件是空的或者恢复出来缺一半表。5.2 执行计划与慢查询别猜去看判断一条 SQL 有没有问题最直接的办法是EXPLAINEXPLAIN SELECT id, order_no FROM t_order WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;重点看三列type、key、rows。type从好到坏大致是system、const、eq_ref、ref、range、index、ALL。出现ALL基本就是全表扫描index是全索引扫描也不理想。key列告诉你实际用了哪个索引。如果这里是NULL而possible_keys里有候选说明优化器算下来觉得不用索引更快可能是数据分布问题也可能条件写法导致索引失效。key_len能看出联合索引用到了第几个字段。以 utf8mb4 的VARCHAR(32)为例单列索引长度大约是32*42130字节允许 NULL 再1。数值类型BIGINT是 8 字节INT是 4 字节DATETIME在 8.0 里占 5 字节。如果你建的是(user_id, status, create_time)三列索引key_len只有 8说明只用到了user_id一列后面的条件没进索引。rows是优化器估算的扫描行数不是实际行数。这个值偏差太大时可以跑一次ANALYZE TABLE更新统计信息。我遇到过一次索引明明没建错、查询却很慢EXPLAIN显示rows估算只有几十行实际扫了几十万行ANALYZE TABLE之后一切恢复正常。原因是这张表刚做过大批量删除统计信息严重滞后。慢查询日志是另一条线索建议长期开着SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes OFF;long_query_time设成 1 秒是业务能感知到卡顿的临界点。最后那个参数我建议关掉因为一旦打开所有没走索引的小查询都会被记进去日志很快就爆了反而把真正的慢查询淹掉。5.3 批量任务与定时处理批处理任务的核心诉求是“可中断、可续跑、不锁表”。我一般的做法是给待处理数据加一个状态标记按主键分页扫-- 每次取一批未处理的数据 SELECT id FROM t_task WHERE status 0 ORDER BY id LIMIT 500;处理完更新状态UPDATE t_task SET status 1, done_time NOW() WHERE id IN (1001, 1002, ...) AND status 0;AND status 0这个条件是为了幂等防止并发任务重复处理。整个流程用脚本包起来主循环里判断取到的行数小于批次大小就退出。如果任务耗时较长记得和业务高峰期错开。有个细节容易被忽略MySQL 的定时事件EVENT和系统 crontab 是两套东西EVENT依赖事件调度器开没开SET GLOBAL event_scheduler ON; SHOW VARIABLES LIKE event_scheduler;服务器重启后这个设置会丢失要在配置文件里固化。我见过定时清理任务上线三个月才发现从没执行过查下来就是调度器在重启后关掉了。6. 常见问题排查实录下面这些报错和现象基本都是我自己或身边人真实撞过的。按类别整理出来遇到同类问题时可以对着排查。6.1 连接与认证类问题现象常见原因处理方向Access denied for user $DB_USERlocalhostshell 单引号导致变量未展开引号改双引号或改用配置文件Access denied但账号密码确认无误主机匹配不上userlocalhost和user10.%是两个账号SELECT user, host FROM mysql.user核对Cant connect to MySQL server端口不通、监听地址不对、防火墙先telnet 主机 3306验证连通性连接偶发超时客户端没设超时、DNS 反解慢加connect-timeout连接串里用 IP 不用域名服务端报Too many connections连接池上限过高或连接未释放查SHOW PROCESSLIST检查代码里的连接归还逻辑认证插件这一块也值得单独提一句。8.0 默认认证插件变了老版本客户端连上去可能报Authentication plugin cannot be loaded。解决办法要么升级客户端要么把账号认证方式改成兼容模式CREATE USER app_reader10.% IDENTIFIED WITH mysql_native_password BY 密码;不过这是兼容手段长期还是要统一升级客户端。新账号优先用默认插件。另外看到Access denied时不要第一反应就去改密码。密码错误只占其中一小部分更多情况是主机匹配、账号不存在、权限没刷新生效。SHOW GRANTS FOR userhost这一句能省掉很多猜测。6.2 数据与索引类问题建表报Specified key was too long通常是索引列太长。以 utf8mb4 为例单个索引列最大长度接近 768 字节VARCHAR(255)全字段索引就超了。解决办法是只索引前缀KEY idx_title (title(50))但前缀索引有个后果ORDER BY title用不上这个索引GROUP BY title也没法靠它完成。所以长文本字段要不要建索引得先想清楚查询模式。Duplicate entry for key是最常见的写入报错来自唯一索引冲突。处理方式取决于业务语义如果是重复提交用INSERT ... ON DUPLICATE KEY UPDATE或者INSERT IGNORE如果是并发写冲突说明应用层的判重逻辑有漏洞得从流程上补。用INSERT IGNORE要小心它会把所有错误都忽略掉包括数据类型不匹配等于把问题藏起来。Deadlock found when trying to get lock是并发更新导致的死锁。InnoDB 会自动回滚其中一方应用层需要捕获这个错误并重试。根本解决办法是统一加锁顺序——多个事务更新同一批行时按主键升序处理能消掉大部分死锁。Lock wait timeout exceeded说明有长事务占着锁。先查出来SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_started;如果发现某个事务已经跑了几分钟基本可以判定是代码里忘了COMMIT或者事务范围写得太宽把网络调用、文件读写都包进去了。事务里只放数据库操作这是条铁律。还有一种情况是“数据明明插进去了查不到”。八成是事务没提交或者另一个会话在可重复读隔离级别下开了个旧快照。8.0 默认隔离级别是 REPEATABLE READ同一个事务里第一次查询建立的快照会一直用到事务结束期间别人提交的新数据看不到。这个行为不是 bug但如果不知道排查起来会非常费劲。6.3 一份可以贴在显示器边上的速查清单把上面这些压缩成几句能立刻用的话报错里用户名带$或%字样先查 shell 引号别急着改数据库。任何UPDATE/DELETE上线前把WHERE条件单独跑一次SELECT COUNT(*)。索引不生效先看是不是列上套了函数、做了隐式类型转换、或者字段顺序不满足最左前缀。金额用DECIMAL时间跨 2038 用DATETIME字符集统一utf8mb4。深分页用延迟关联或游标法别直接堆 offset。备份参数里--single-transaction、--routines、--triggers、--events四个缺一不可。加完索引记得看EXPLAIN的key_len确认联合索引实际用到了几列。最后分享一个我自己的习惯每次上线新 SQL 之前把它放到一个接近生产数据量的环境里跑一遍EXPLAIN重点看type和rows两列。这个动作花不了两分钟但能挡掉绝大多数上线后才发现的性能问题。真正难的不是把 SQL 写对是写完就知道它在数据量涨十倍之后还对不对——这件事只能靠执行计划提前判断靠猜是靠不住的。