聊到 MySQL 基础很多人觉得无非是装一下、建库建表、写几个 SELECT但真正踩过线上坑的都清楚基础理解不透后面寸步难行。我这些年帮团队排查过的数据库问题一半以上都跟基础概念模糊有关事务隔离级别没搞清导致数据错乱索引建得随意导致慢查询拖垮业务锁等待处理不当直接卡死整个库。这篇就把 MySQL 基础里最容易被忽略又最要命的部分按照我实际干活的经验捋一遍覆盖安装、连接、事务、索引、锁和常见报错排查不整虚的全是能直接拿到工位上用的东西。1. 为什么还要谈 MySQL 基础1.1 基础不牢地动山摇前几天一个同事把线上的订单表加了个字段用了ALTER TABLE ... ALGORITHMINPLACE结果凌晨两点业务报警。他后来跟我说当年学 MySQL 只知道 DDL 能改表不知道 InnoDB 在改大表时对锁和 IO 的要求更不知道ALGORITHM和LOCK该怎么写。这不是个别现象很多干了三四年的开发SQL 写得飞起但问到事务隔离级别和锁的关系照样含糊其辞。MySQL 的难点不在语法而在“什么时候会发生什么”。比如一个简单的UPDATE在Read Committed和Repeatable Read下的加锁范围就不一样一条SELECT走不走索引直接决定是秒回还是卡 30 秒。这些全是基础但基础如果不扎实线上出问题时的排查方向就会完全跑偏。1.2 这篇文章适合谁不装高雅这篇主要写给三类人第一类是刚入行或者还在校的需要一套能落地的 MySQL 入门路线第二类是平时写业务代码、但不太关心数据库内部机制的后端开发看完能少走很多弯路第三类是运维和 DBA 新手想快速建立排查问题的基本框架。内容深度我控制在“基础偏实战”的档位。路由优化里那些复杂的优化器逻辑我不会展开但该解释的原理一定解释透该给的操作步骤一步步写到命令级别。读完不敢说让你成为专家但至少在装库、写 SQL、看慢查询、处理锁等待这些最常见场景里你能知道自己在做什么以及为什么这么做。2. 部署一台能用的 MySQL版本选择和安装2.1 选 8.0 还是 5.7别只看版本号新项目如果还在用 MySQL 5.7我建议直接考虑 8.0。5.7.44 是 5.7 系列的最后一个版本官方不再提供后续更新和安全补丁也就是说你守着 5.7.43 升级到 5.7.44 之后就再也没有官方维护了。社区里有人会问为什么 5.7.44 之后没有 5.7.45原因很简单生命周期结束官方把资源都转到 8.0 和 8.4 LTS 上了。MySQL 8.0 和 5.7 最大的区别不只是性能而是默认参数和认证方式。8.0 默认的认证插件是caching_sha2_password很多旧客户端和 5.7 时代的工具不兼容会出现连接报错。如果你没有特别的生态限制比如老运维脚本、老 ODBC 驱动那优先 8.0.4x 以上版本如果有历史包袱至少也要用 5.7.44 并做好迁移规划。不建议在生产环境用测试版选 LTS 或成熟 GA 版本是基本常识。2.2 Linux 下 rpm 安装全过程我在生产环境部署 MySQL 时基本不用源码编译也不用 Docker 直接裸启动最常用的是官方 Yum 仓库加 rpm 包安装。源码编译太耗时间而且参数调优需要自己一遍遍试Docker 适合开发环境但在生产上如果没人专职维护容器里的数据卷、权限和网络配置会变成新的坑。具体步骤我以 CentOS 7 系列为例命令可以直接复制。# 1. 下载官方 Yum 仓库 rpm 包 wget https://dev.mysql.com/get/mysql80-community-release-el7-9.noarch.rpm # 2. 安装仓库配置 rpm -ivh mysql80-community-release-el7-9.noarch.rpm # 3. 安装 MySQL 服务端和客户端 yum install -y mysql-community-server mysql-community-client # 4. 初始化并启动 systemctl start mysqld systemctl enable mysqld安装完后MySQL 会默认生成一个临时密码存放在错误日志里。很多人第一次启动后会卡在这一步以为没设置密码就不能登录。正确姿势是查日志grep temporary password /var/log/mysqld.log然后用临时密码登录紧接着改密码。注意 MySQL 8.0 默认有密码复杂度策略简单密码会被拒ALTER USER rootlocalhost IDENTIFIED BY 你的强密码;我建议密码至少 12 位包含大小写字母、数字和特殊字符。别嫌麻烦默认的 validate_password 组件就是干这个的你拿弱密码去改会直接报错。2.3 初始化与服务启动的那些坑服务启动失败是安装环节最常遇到的问题。常见原因有/etc/my.cnf配置里指定了不存在的目录datadir下的目录权限不对或者 SELinux 拦截了 MySQL 对数据文件的访问。判断问题第一步永远是看错误日志而不是盲目重启tail -n 200 /var/log/mysqld.log如果日志里提示[ERROR] [MY-014060]或者类似找不到数据目录的问题优先检查/var/lib/mysql是否存在且属主是mysql:mysql。一个很低级但真实的坑我见过有人用 root 初始化了数据目录导致 MySQL 进程以 mysql 用户身份跑的时候没有权限读写最后所有表都报 1017 错误。这里补充一个实用技巧在 CentOS 环境如果遇到权限问题暂时关闭 SELinux 来验证如果关了就好那说明是策略问题再按需加规则而不是一关了之setenforce 0当然生产环境不能长期关 SELinux但用于排查是没问题的。3. 上手必会的命令和 SQL3.1 连接 MySQL 的方式和权限模型装好 MySQL 之后第一件事就是连接。命令行连接是基本功mysql -h 127.0.0.1 -P 3306 -u root -p这里-h指定主机名-P指定端口-u用户名-p后输入密码。如果是本机还可以通过 socket 方式连接例如mysql -u root -p这种不带-h的方式默认走 Unix socket速度比 TCP 快但只适用于本机。在生产环境我通常建议显式指定-h和-P避免因为平台不同导致连接方式不一致。MySQL 的权限模型一定要理解。用户不是简单的“用户名密码”而是“用户 主机”的组合。比如app192.168.1.%和applocalhost是两个完全不同的账号。很多连接不上问题的根因就是搞混了这两个。创建专用账号时一个标准操作是CREATE USER app% IDENTIFIED BY 密码; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app%; FLUSH PRIVILEGES;这里%表示任意主机但在生产环境要收紧只给业务所在网段开放。另外FLUSH PRIVILEGES在正常 GRANT 操作后不是必须的但如果直接修改了 mysql.user 表就必须执行它来刷新权限缓存。3.2 常用 DDL 和 DMLDDL 建表是高频操作基础的格式必须闭着眼能写出来CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARSET utf8mb4; CREATE TABLE user ( id bigint unsigned NOT NULL AUTO_INCREMENT, name varchar(64) NOT NULL DEFAULT , age int NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有两个细节。第一字符集必须用utf8mb4不是utf8。因为 MySQL 的utf8最大只能存 3 字节像 emoji 和一些生僻字会报错。第二ENGINE要显式写成InnoDB这是唯一支持事务和外键的引擎。虽然 MyISAM 在某些场景下读得快但它不支持行锁和崩溃恢复现在基本只剩历史包袱。修改表结构是另一个高频动作ALTER TABLE user ADD COLUMN phone varchar(20) NOT NULL DEFAULT AFTER name; ALTER TABLE user MODIFY COLUMN age smallint NOT NULL DEFAULT 0; ALTER TABLE user ADD KEY idx_age (age);一定要记住线上大表的 DDL 要非常谨慎。虽然 InnoDB 支持ALGORITHMINPLACE但加索引和修改字段时依然会带来 IO 压力和潜在的锁等待。我处理线上变更时通常会选择业务低峰期并且在变更前查一下表大小SELECT table_name, ROUND(((data_length index_length) / 1024 / 1024), 2) AS size_mb FROM information_schema.tables WHERE table_schema mydb AND table_name user;如果表超过 100MB我更愿意用 gh-ost 或者 pt-online-schema-change 这类工具做在线变更。这不是炫技而是避免直接把数据库锁住。3.3 排序、去重和默认值的细节SQL 基础里的ORDER BY和DISTINCT看起来简单但实际应用中埋着不少雷。排序用ORDER BY但要注意排序字段是否走索引。如果对没有索引的字段排序MySQL 需要对结果集做 filesort数据量大时性能会很难看。举个例子SELECT * FROM user ORDER BY created_at DESC LIMIT 10;如果created_at没有索引这条语句在百万级数据量下就是全表扫描加临时排序。最常见的解法是加一个联合索引让排序也能走索引。至于索引的细节下面有一节专门聊。去重使用DISTINCT但很多人不知道它是对整个结果集去重不是对某一列去重。比如SELECT DISTINCT name, age FROM user会把(张三, 25)和(张三, 26)当成两条不同记录。热搜里有人问“MySQL 的 OR 能去重吗”这其实是个认知混淆。去重只有DISTINCT或者GROUP BYOR是逻辑运算符它负责“或”关系跟去重半毛钱关系都没有。想按某一列去重然后取其他字段应该用窗口函数或者子查询比如SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM order_log ) t WHERE t.rn 1;默认值这个问题看似简单却经常翻车。热搜词里的“MySQL 设置默认值为 0”很多新人以为只要写DEFAULT 0就万事大吉但实际上如果表里已有历史数据新增字段时不给默认值MySQL 会报错或者用默认值填充旧行导致意外结果。正确的姿势是ALTER TABLE user ADD COLUMN status tinyint NOT NULL DEFAULT 0;另外只有NULL值才需要用IFNULL处理DEFAULT 0意味着在写入时如果没指定这一列就会填 0但已有数据不会被自动更新。3.4 事务基础中的核心事务是 MySQL 最值得花时间理解的基础概念。ACID 四字不能只当口号背要知道每一个字母对应什么场景。原子性Atomicity要求一个事务里的操作要么全成功要么全回滚一致性Consistency保证事务前后数据都满足约束隔离性Isolation控制并发事务的可见性持久性Durability确保提交后不丢失。事务的开启和提交START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;注意如果不写COMMIT事务会一直持有锁。我见过线上莫名锁表的事十有八九是某个连接忘记提交事务。隔离级别是事务基础里的重中之重。MySQL InnoDB 默认是Repeatable Read和标准 SQL 的默认Read Committed不太一样。隔离级别与问题之间的对照关系如下隔离级别脏读不可重复读幻读加锁方式Read Uncommitted可能可能可能不加读锁Read Committed不会可能可能读语句快照Repeatable Read默认不会不会可能InnoDB通过间隙锁避免大多数一致性快照Serializable不会不会不会全部串行锁理解脏读和不可重复读很容易脏读是读到了别人没提交的数据不可重复读是同一个事务里两次读同一行结果不一样。幻读是指同一事务两次查询某个范围后一次多出了新行。InnoDB 默认隔离级别下通过 next-key lock 基本能避免幻读但如果你把隔离级别调成Read Committed就会出现“同一事务查询两次数据突然变多”的问题。3.5 存储过程什么时候用什么时候别用存储过程是 MySQL 基础里容易被忽略的一块但理解和掌握它对理解服务端逻辑和批量数据处理有好处。先说怎么建。一个最基本的存储过程DELIMITER $$ CREATE PROCEDURE get_user(IN userId INT) BEGIN SELECT * FROM user WHERE id userId; END$$ DELIMITER ;DELIMITER用于临时把语句分隔符从分号改成$$否则 MySQL 客户端看到分号就认为命令结束了。参数类型有三种IN是入参OUT是返回值INOUT既可以传入也可以传出。存储过程的优势是减少网络往返、复用逻辑适合少量、固定逻辑的批处理。但我不推荐在业务系统里大面积使用存储过程原因是调试困难、版本管理差、对 DBA 的依赖高。很多团队最后都会把复杂的存储过程逐步拆回到应用层用代码逻辑去控制反而更好维护。基础阶段了解语法和场景就够了别舍本逐末。4. 索引与锁进阶基础4.1 索引怎么建才合理索引是 MySQL 性能的第一道防线。InnoDB 使用的是 B 树叶子节点存储数据本身非叶子节点只存储索引键和指针。B 树的好处是数据有序且层级少一般 3 到 4 层就能放下千万级数据所以查询走索引时磁盘 IO 次数很低。建索引的语法很简单CREATE INDEX idx_name_age ON user (name, age);但真正难的是决定“哪些字段进入索引以及顺序”。这里最核心的原则是最左前缀法则。联合索引(name, age)可以服务name条件也可以服务name age条件但单独用age作为条件时这个索引大概率失效。我踩过的一个典型坑有张订单表查询条件经常是user_id和status于是一口气建了两个单列索引。结果 MySQL 优化器只能选其中一个另一个等于白废。后来改成联合索引(user_id, status)查询速度快了一个数量级。判断一个查询有没有走索引不靠猜靠EXPLAINEXPLAIN SELECT * FROM user WHERE name 张三;重点关注type和key两列。如果type是ALL说明全表扫描如果type是ref或者range并且key显示用到了索引就算正常。如果type是index虽然也用到了索引但往往是扫描了整个索引树性能不一定就好。这里要提醒一点索引不是越多越好。每多一个索引插入和更新时的维护成本就多一层。我通常建议单表索引控制在 5 个左右重要的查询场景优先覆盖而不是每个字段都建。4.2 锁的分类和锁表问题锁的知识很抽象但它是处理并发问题的核心。MySQL 里锁大致分三类全局锁、表级锁和行级锁。全局锁用FLUSH TABLES WITH READ LOCK可以给整个库加只读锁一般只在逻辑备份时用线上谨慎。表级锁包括表锁和元数据锁MDL。表锁可以用LOCK TABLES显式加但 MyISAM 才需要InnoDB 支持行锁所以在事务里别乱用表锁。行级锁是 InnoDB 的看家本领。它有两种基本类型共享锁S 锁和排他锁X 锁。普通的SELECT不加锁SELECT ... FOR UPDATE是排他锁SELECT ... LOCK IN SHARE MODE是共享锁。更新、插入、删除默认都会对涉及的行加排他锁。除了普通行锁还有一种很容易被忽略的间隙锁Gap Lock。间隙锁锁的是索引记录之间的“空隙”目的是防止幻读。在REPEATABLE READ隔离级别下如果查询条件是一个范围InnoDB 不仅会锁住匹配的行还会锁住范围内的间隙。这种机制在保证数据一致性的同时也容易引发死锁和锁等待。排查锁等待是我工作中最高频的操作之一。当业务突然卡住时第一件事是看当前进程SHOW FULL PROCESSLIST;如果看到多个Waiting for table metadata lock或者Lock wait timeout exceeded再查当前有没有锁等待事务。下面这条 SQL 可以列出正在等待锁的事务SELECT * FROM performance_schema.data_lock_waits\G我之前帮一个团队排查锁表最后发现是一个开发在测试环境用 Navicat 打开了一个事务一直没有提交然后全组的查询全部卡住。这种问题光靠重启 MySQL 是不行的得找到源头事务并杀掉它。所以记住每个长事务都要盯住短事务也要控制并发锁等待的根因往往不在数据库在应用层的连接管理。5. 常见问题排查速查表5.1 MySQL 服务无法启动服务无法启动是最常见的入门问题。一片报错里最典型的是[ERROR] [MY-014060] [Server] Invalid MySQL server upgrade这个错误多见于从旧版本直接替换二进制文件或数据目录不匹配。解决办法是确认数据目录版本和当前二进制版本一致。Windows 上使用net start mysql报“服务无法启动”时多半是my.ini配置有问题或者端口被占用。检查顺序是# 检查端口占用 netstat -ano | findstr :3306 # 查看 Windows 错误日志 C:\ProgramData\MySQL\MySQL Server 8.0\Data\*.errLinux 上则要看/var/log/mysqld.log关键词搜[ERROR]就好。一个容易被忽略的坑如果改了my.cnf中的port3306和datadir但 socket 路径没改客户端通过 socket 连接时还是会连到默认路径导致“Cant connect to local MySQL server through socket”。遇到这种问题连接时用-h 127.0.0.1走 TCP 绕过。5.2 SSL 连接错误MySQL 8.0 默认启用 SSL 连接客户端工具版本太旧时非常容易报SSL connection error或者Authentication plugin caching_sha2_password cannot be loaded。针对旧客户端不信任证书的情况连接时可以显式指定mysql -h 127.0.0.1 -u root -p --ssl-modePREFERRED如果想彻底关闭 SSL可以在[mysqld]下配置[mysqld] ssl0不过我更推荐升级客户端驱动。比如 Java 的 MySQL Connector/J 8.0 以上版本就能正常支持caching_sha2_password不用动服务端配置。老项目如果临时连接不上可以先创建一个使用旧认证插件的账号CREATE USER old_app% IDENTIFIED WITH mysql_native_password BY 密码; GRANT ALL ON mydb.* TO old_app%;注意这个方式只能临时过渡长期看还是要迁到 8.0 的默认认证方式。5.3 Docker 安装 MySQL 失败Docker 拉镜像报错是热搜里高频问题。比如docker pull mysql报failed to decode referrers index: invalid这种大多是 Docker 引擎或者 registry 缓存问题。优先升级 Docker Desktop 到新版本或者执行docker system prune清掉无效缓存。再不行就换镜像源或者指定具体平台docker pull --platform linux/amd64 mysql:8.0.44如果是在 ARM 架构的 NAS 或者 ARM 服务器上直接用默认mysql:latest可能拉不下来推荐用mysql:8.0.44对应 ARM64 变体或者用mariadb:10.11代替兼容性更好。容器起不来更常见的原因是环境变量没配docker run -d \ --name mysql8 \ -e MYSQL_ROOT_PASSWORD123456 \ -p 3306:3306 \ mysql:8.0.44注意不设置MYSQL_ROOT_PASSWORD会启动失败。另外容器里的数据要想持久化必须挂载/var/lib/mysql数据卷否则容器一删数据全没。这一点即便不是 Docker 基础也算 MySQL 部署基础里的核心。5.4 其他高频问题连接超时问题也很常见。比如mysql -u -p 执行 SQL 超时这通常是net_read_timeout或net_write_timeout设置得太小或者网络本身不稳定。排查时先看跳过权限的本地连接是否正常mysql -u root --socket/var/lib/mysql/mysql.sock如果本地连接正常网络连接超时就要检查防火墙和bind-address配置。MySQL 8.0 默认可能只监听127.0.0.1想允许远程访问需要设置bind-address 0.0.0.0然后重启服务。别忽略skip-name-resolve配置关闭 DNS 反向解析可以避免一部分外部连接延迟。sqoop 连接不上 MySQL 这类生态工具问题九成是 JDBC 驱动版本或认证插件兼容问题。给 sqoop 用的账号尽量使用mysql_native_password或者升级 Connector/J 到 8.0 版本。Linux 下用 xtrabackup 备份主库并部署从库时GTID 同步方式要特别注意。基础环节不要求马上会操作但你要知道它的核心流程主库开启gtid_modeON和enforce_gtid_consistencyON备份时用 xtrabackup 做物理备份在从库上CHANGE MASTER TO MASTER_AUTO_POSITION1。GTID 同步比我早年间用的文件位点同步省心很多前提是主从版本保持一致千万别混用 5.7 和 8.0。最后分享一个我个人的体会。MySQL 基础这种东西看书看文档只是一方面真正的长进来自反复解决真实报错。把SHOW PROCESSLIST、EXPLAIN、SHOW ENGINE INNODB STATUS这些排错命令练到肌肉记忆再遇到问题就不会慌。我觉得最有价值的基础能力不是记住多少函数而是知道“现在这个 SQL 为什么慢、这个锁为什么不释放、这个错误日志到底想告诉你什么”。这一套思路比任何脚本和工具都靠谱。