MySQL实战学习路径：从源码编译到索引压测与慢日志分析

简介这是一套面向数据库初学者与Web开发者的MySQL与SQL系统学习资料聚焦关系型数据库核心能力培养覆盖从环境搭建、基础语法到高阶特性和性能优化的完整知识链。资源共41个文件含20份PDF教程如《MySQL数据类型精讲》《触发器》《存储过程与函数》、19个SQL实战脚本对应各章练习如多表查询、子查询、视图、事务等及2份Markdown说明文档结构清晰、学练结合。压缩包大小21.88MB内容紧扣MySQL 8特性与SQL标准包含大量可直接运行的示例代码、分步解析笔记和典型应用场景如Web开发集成、权限管理、备份恢复。已有220人下载学习适合零基础入门者按章节循序渐进掌握建库建表、CRUD操作、JOIN联查、事务控制及索引优化等关键技能并通过配套脚本即时验证学习效果。1. MySQL 学习资料不是找“一堆PDF”而是搭一条能跑通、能调优、能排错的实战路径你搜“MySQL学习资料”页面弹出几百个链接带“零基础”“21天速成”“面试必背”的PDF、视频合集、网盘资源……但真正动手建库、连Java应用、查慢查询、修主从同步时90%的人卡在第一步——连不上本地服务报错ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock或者改完配置重启失败systemd 显示Failed to start mysqld.service: Unit not found又或者用 Navicat 连上却查不出数据发现sql_mode里默认开了STRICT_TRANS_TABLESINSERT 一个 NULL 到 NOT NULL 字段直接被拒连错误提示都看不懂。这不是资料太少是资料太散、太旧、太脱离真实运维和开发场景。这篇笔记不给你列100个下载链接只讲清楚一套可验证、可调试、可延伸的 MySQL 学习路径怎么搭——从 Linux 下源码编译安装开始到用 Python 脚本压测索引效果中间每一步都带参数逻辑、失败信号和修复动作。适合正在写 Java/Python 后端、需要独立部署数据库的开发者也适合刚考完 OCP 想补实操短板的 DBA。它不承诺“三天学会”但保证你做完第3章后能自己诊断show processlist里那个Killed状态的线程为什么杀不死第5章结束时能用pt-query-digest解析慢日志并定位到具体 SQL 的执行计划缺陷。2. 从源码编译安装开始为什么跳过 yum/apt 安装反而更稳、更可控很多教程一上来就sudo apt install mysql-server或yum install mysql-community-server看似省事但实际踩坑率极高Ubuntu 22.04 默认装的是 MySQL 8.0.33但你的 Spring Boot 2.7 项目依赖的 JDBC 驱动只兼容到 8.0.28CentOS 7 的 yum 源里 MySQL 版本是 5.7.38而你要测试 JSON 函数必须用 8.0更麻烦的是这些包管理器安装的二进制文件路径、配置文件位置、systemd unit 文件写法全都不统一mysqld --help --verbose | grep datadir查出来的路径和/etc/my.cnf里写的对不上debug 成本翻倍。所以我坚持用官方源码编译——不是为了炫技而是为了彻底掌控CMAKE_INSTALL_PREFIX、SYSCONFDIR、LOCALSTATEDIR这三个核心路径让所有配置、日志、数据目录都在你眼皮底下。下面步骤基于 MySQL 8.0.332023年LTS版本适配 x86_64 LinuxCentOS 7/Ubuntu 20.04。2.1 下载、解压与依赖检查别急着 cmake先确认这三件事# 1. 创建专用工作目录避免污染 /root 或 /home mkdir -p /opt/mysql-build cd /opt/mysql-build # 2. 下载源码包注意必须是 *-source.tar.gz不是 *-linux-glibc2.12-x86_64.tar.gz wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.33.tar.gz tar -xzf mysql-8.0.33.tar.gz # 3. 检查编译依赖缺一不可否则 cmake 直接报错 # Ubuntu/Debian sudo apt update sudo apt install -y \ build-essential cmake libncurses5-dev libssl-dev liblz4-dev libzstd-dev # CentOS/RHEL sudo yum groupinstall -y Development Tools sudo yum install -y cmake3 openssl-devel ncurses-devel lz4-devel zstd-devel提示liblz4-dev和libzstd-dev是 MySQL 8.0 新增的压缩算法依赖老教程常漏掉会导致make时undefined reference to LZ4_compress_default报错。cmake3在 CentOS 7 必须用因为系统自带 cmake 2.8 不支持 MySQL 的 CMakeLists.txt 语法。2.2 CMake 配置这 7 个参数决定你后续 80% 的运维体验cd mysql-8.0.33 cmake . -DCMAKE_INSTALL_PREFIX/usr/local/mysql \ -DSYSCONFDIR/etc/mysql \ -DLOCALSTATEDIR/var/lib/mysql \ -DDEFAULT_CHARSETutf8mb4 \ -DDEFAULT_COLLATIONutf8mb4_0900_ai_ci \ -DWITH_SSLsystem \ -DWITH_ZLIBsystem \ -DWITH_LZ4system \ -DWITH_ZSTDsystem \ -DENABLED_LOCAL_INFILEON \ -DWITH_BOOSTboost参数逻辑说明-DCMAKE_INSTALL_PREFIX/usr/local/mysql这是 MySQL 二进制文件、脚本、头文件的根目录必须和后续 service 文件里的 ExecStart 路径一致-DSYSCONFDIR/etc/mysql配置文件my.cnf的存放位置不是/etc/my.cnf很多人误以为 MySQL 只读/etc/my.cnf其实它按顺序读/etc/my.cnf→/etc/mysql/my.cnf→/usr/local/mysql/etc/my.cnf我们强制指定为/etc/mysql避免和其他软件冲突-DLOCALSTATEDIR/var/lib/mysql数据目录必须提前创建并赋权sudo mkdir -p /var/lib/mysql sudo chown -R mysql:mysql /var/lib/mysql否则初始化会失败-DDEFAULT_CHARSETutf8mb4不是 utf8MySQL 的utf8实际是 utf8mb3不支持 emoji 和部分生僻汉字utf8mb4才是真正的 UTF-8-DWITH_SSLsystem复用系统 OpenSSL 库避免自己编译 OpenSSL 带来的版本混乱如SSL_CTX_set_options符号未定义-DENABLED_LOCAL_INFILEON开启LOAD DATA LOCAL INFILE否则 Python pandas 的to_sql(if_existsreplace)会报错Local file capability is disabled-DWITH_BOOSTboostMySQL 8.0 强制依赖 Boost 库源码包里已自带boost子目录无需额外下载。2.3 编译与初始化make -j$(nproc) 后最关键的一步是 mysql_install_db# 编译-j$(nproc) 用满 CPU 核心通常 4~8 分钟 make -j$(nproc) # 安装到指定前缀目录 sudo make install # 创建 mysql 用户不要用 root 运行 mysqld sudo useradd -r -s /bin/false mysql # 初始化数据目录关键必须用 --initialize-insecure否则 root 密码随机且无法登录 sudo /usr/local/mysql/bin/mysqld \ --initialize-insecure \ --usermysql \ --basedir/usr/local/mysql \ --datadir/var/lib/mysql # 赋权必须否则启动时报错 Cant open the mysql.plugin table sudo chown -R mysql:mysql /var/lib/mysql sudo chown -R mysql:mysql /usr/local/mysql注意--initialize-insecure是学习阶段的正确选择。它生成空密码的 root 用户让你能立刻mysql -u root -p直接回车登录。生产环境必须用--initialize生成随机密码日志里会打印但初学时反复重置密码、查 error log 耗时太多。等你熟悉ALTER USER rootlocalhost IDENTIFIED WITH caching_sha2_password BY xxx;再切过去。3. systemd 服务配置让 mysqld 真正“开机自启”而不是靠nohup ./mysqld 装完二进制很多人直接sudo /usr/local/mysql/bin/mysqld --usermysql 结果一关终端进程就死ps aux | grep mysql找不到更别说systemctl status mysqld。这不是 MySQL 的问题是你没给它一个符合 Linux 标准的 service 单元文件。下面这个/etc/systemd/system/mysqld.service经过 3 个大版本验证覆盖 CentOS 7/8、Ubuntu 20.04/22.04[Unit] DescriptionMySQL Server Documentationman:mysqld(8) Documentationhttps://dev.mysql.com/doc/refman/en/using-systemd.html Afternetwork.target Aftersyslog.target [Service] Typesimple Usermysql Groupmysql ExecStart/usr/local/mysql/bin/mysqld --defaults-file/etc/mysql/my.cnf --daemonize Restarton-failure RestartSec10 TimeoutSec300 StartLimitInterval300 StartLimitBurst3 MemoryLimit2G CPUQuota75% # 关键指定 PID 文件和 Socket 路径否则 systemctl 无法管理进程 PIDFile/var/run/mysqld/mysqld.pid Socket/var/run/mysqld/mysqld.sock # 环境变量避免找不到 libssl.so.1.1 等 EnvironmentLD_LIBRARY_PATH/usr/local/mysql/lib:/usr/lib64 # 安全加固学习阶段可注释但要知道存在 # NoNewPrivilegestrue # PrivateTmptrue # ProtectHometrue # ProtectSystemfull [Install] WantedBymulti-user.target配置要点解析ExecStart必须带--defaults-file否则 mysqld 会去读/etc/my.cnf而你实际配置在/etc/mysql/my.cnfPIDFile和Socket路径必须和my.cnf里[mysqld]段的pid-file、socket参数严格一致否则systemctl stop mysqld会超时失败EnvironmentLD_LIBRARY_PATH...是血泪经验MySQL 8.0 动态链接libssl.so.1.1、libcrypto.so.1.1但系统 OpenSSL 库可能在/usr/lib64/而 mysqld 默认只搜/usr/local/mysql/lib不加这个环境变量启动直接报error while loading shared libraries: libssl.so.1.1: cannot open shared object fileMemoryLimit2G和CPUQuota75%是学习机的合理限制防止mysqld吃光内存导致 SSH 断连。3.1 my.cnf 配置一份精简但覆盖 95% 场景的模板# /etc/mysql/my.cnf [client] port 3306 socket /var/run/mysqld/mysqld.sock default-character-set utf8mb4 [mysqld] # 基础路径 basedir /usr/local/mysql datadir /var/lib/mysql pid-file /var/run/mysqld/mysqld.pid socket /var/run/mysqld/mysqld.sock log-error /var/log/mysql/error.log # 字符集必须和 cmake 时一致 character-set-server utf8mb4 collation-server utf8mb4_0900_ai_ci # 网络与安全 bind-address 127.0.0.1 port 3306 skip-networking OFF max_connections 200 wait_timeout 28800 interactive_timeout 28800 # 日志学习阶段开 general_log 便于 debug general_log ON general_log_file /var/log/mysql/general.log slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 2 # InnoDBMySQL 8.0 默认引擎 innodb_buffer_pool_size 512M innodb_log_file_size 128M innodb_flush_log_at_trx_commit 1 innodb_file_per_table ON # 兼容性解决 JDBC 连接报错Public Key Retrieval is not allowed require_secure_transport OFF关键参数说明bind-address 127.0.0.1学习阶段务必设为 localhost避免暴露 3306 端口到公网general_log ON打开通用日志所有 SQL 都记录tail -f /var/log/mysql/general.log就能看到 Navicat 或 Python 连接时发了什么语句require_secure_transport OFF关闭强制 SSL否则 JDBC URL 加?useSSLtrueserverTimezoneUTC还会报Public Key Retrieval is not allowedinnodb_buffer_pool_size 512M设为物理内存的 25%~50%学习机 2G 内存就设 512M太大反而触发 swap。3.2 启动与验证用三条命令确认服务真正在跑# 1. 重载 systemd 配置每次改 service 文件后必须执行 sudo systemctl daemon-reload # 2. 启动服务并设开机自启 sudo systemctl enable mysqld sudo systemctl start mysqld # 3. 验证三步缺一不可 sudo systemctl status mysqld # 看 Active: active (running) sudo ss -tlnp | grep :3306 # 看 LISTEN 状态和 mysqld 进程 PID mysql -u root -p -e SELECT VERSION(), socket; # 直接连输出版本和 socket 路径如果systemctl status mysqld显示failed立刻看journalctl -u mysqld -n 50 -f不是猜是看日志最后一行。常见失败原因/var/run/mysqld目录不存在sudo mkdir -p /var/run/mysqld sudo chown mysql:mysql /var/run/mysqld、/var/log/mysql无写权限sudo mkdir -p /var/log/mysql sudo chown mysql:mysql /var/log/mysql。4. 避坑5 个新手必踩的“连不上、查不到、改不了”真实故障4.1 现象ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock原因客户端默认找/tmp/mysql.sock但你的 mysqld 实际监听/var/run/mysqld/mysqld.sock由my.cnf的socket参数指定。解决方案1推荐在~/.my.cnf里写死 socket 路径[client] socket /var/run/mysqld/mysqld.sock方案2启动 mysql 时显式指定mysql -u root -p --socket/var/run/mysqld/mysqld.sock方案3建软链不推荐易混淆sudo ln -sf /var/run/mysqld/mysqld.sock /tmp/mysql.sock4.2 现象mysql -u root -p输入密码后报Access denied for user rootlocalhost原因--initialize-insecure初始化后root 用户密码为空但你输错了比如多按了一个空格或my.cnf里skip-grant-tables没关导致权限表未加载。解决先确认是否真为空密码mysql -u root --skip-passwordMySQL 8.0.19 支持如果不行临时加skip-grant-tables到my.cnf的[mysqld]段重启 mysqld然后执行FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED WITH caching_sha2_password BY ;血泪经验改完my.cnf必须sudo systemctl restart mysqld不能只 kill 进程再手动启。4.3 现象Navicat 连接成功但执行SELECT * FROM information_schema.TABLES报错Table information_schema.TABLES doesnt exist原因information_schema是内存视图不是物理表但它依赖performance_schema正常工作。而performance_schema在my.cnf里被禁用了performance_schema OFF。解决在my.cnf的[mysqld]段加一行performance_schema ON重启 mysqld。验证SHOW ENGINES;输出里PERFORMANCE_SCHEMA行的Support列必须是YES。4.4 现象Python 用 PyMySQL 连接报OperationalError: (2059, Authentication plugin caching_sha2_password cannot be loaded)原因MySQL 8.0 默认认证插件是caching_sha2_password但老版 PyMySQL 0.10.0不支持。解决升级 PyMySQLpip install --upgrade pymysql或降级用户认证方式学习阶段可接受ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY your_password; FLUSH PRIVILEGES;注意JDBC 连接同理URL 必须加?serverTimezoneUTCallowPublicKeyRetrievaltrueuseSSLfalse。4.5 现象执行UPDATE users SET name张三 WHERE id1;后SELECT * FROM users WHERE id1;查不到刚改的数据原因事务未提交且 autocommit 关闭MySQL CLI 默认autocommitON但某些 ORM 或脚本里可能设为 OFF。解决查当前 autocommitSELECT autocommit;返回 1 是开启0 是关闭如果是 0要么加COMMIT;要么在连接后执行SET autocommit1;玄学排查用SELECT CONNECTION_ID();获取当前连接 ID再SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID xxx;看PROCESSLIST_INFO是否有未完成的 UPDATE。5. 用 Python 脚本压测索引效果从建表到 explain验证你学的到底有没有用学 MySQL 最大的幻觉就是“看懂了 BTree 就会优化”。真实场景是你加了个INDEX (status, created_at)但线上慢查询日志里SELECT * FROM orders WHERE statuspaid ORDER BY created_at DESC LIMIT 20还是 3s。为什么因为ORDER BY和WHERE的字段顺序不匹配或者SELECT *触发了回表。下面这个 Python 脚本用 50 行代码模拟真实压测让你亲眼看到索引生效与否的差距。5.1 建测试表与造数据用存储过程批量插入 10 万条-- 创建 orders 表故意不加索引制造慢查询 CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, status ENUM(pending, paid, shipped, delivered) NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- 插入 10 万条模拟数据status 随机分布 DELIMITER $$ CREATE PROCEDURE insert_orders() BEGIN DECLARE i INT DEFAULT 0; WHILE i 100000 DO INSERT INTO orders (user_id, status, amount, created_at) VALUES ( FLOOR(1 RAND() * 1000), ELT(FLOOR(1 RAND() * 4), pending, paid, shipped, delivered), ROUND(RAND() * 1000, 2), DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY) ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL insert_orders();5.2 Python 压测脚本对比有无索引的执行时间# test_index.py import time import pymysql conn pymysql.connect( host127.0.0.1, port3306, userroot, password, databasetestdb, charsetutf8mb4 ) def exec_query(sql): start time.time() with conn.cursor() as cur: cur.execute(sql) result cur.fetchall() end time.time() print(fSQL: {sql[:50]}... | Rows: {len(result)} | Time: {end-start:.3f}s) return end - start # 测试无索引时的慢查询 print( Without Index ) t1 exec_query(SELECT * FROM orders WHERE statuspaid ORDER BY created_at DESC LIMIT 20) # 加复合索引 print(\n Adding INDEX (status, created_at) ) with conn.cursor() as cur: cur.execute(CREATE INDEX idx_status_created ON orders(status, created_at);) conn.commit() # 再测 print(\n With Index ) t2 exec_query(SELECT * FROM orders WHERE statuspaid ORDER BY created_at DESC LIMIT 20) print(f\nSpeedup: {t1/t2:.1f}x)运行结果示例 Without Index SQL: SELECT * FROM orders WHERE statuspaid ORDER ... | Rows: 20 | Time: 1.823s Adding INDEX (status, created_at) With Index SQL: SELECT * FROM orders WHERE statuspaid ORDER ... | Rows: 20 | Time: 0.004s Speedup: 455.8x为什么这么快因为INDEX (status, created_at)让 MySQL 能直接在索引 BTree 上按statuspaid定位再按created_at逆序取前20行全程不用回表查SELECT *的其他字段覆盖索引。但如果把 SQL 改成SELECT id, user_id, status FROM ...速度几乎不变改成SELECT *就会慢一点要回表但依然比无索引快百倍。这就是EXPLAIN要告诉你的typeref走了索引、ExtraUsing index覆盖索引、rows200预估扫描行数。5.3 用 EXPLAIN 验证执行计划三列必看两行必盯在 MySQL CLI 里执行EXPLAIN FORMATTRADITIONAL SELECT * FROM orders WHERE statuspaid ORDER BY created_at DESC LIMIT 20;输出关键字段解读idselect_typetabletypekeyrowsExtra1SIMPLEordersrefidx_status_created25000Using where; Using filesorttyperef表示走了非唯一索引status是 ENUM重复值多所以是 ref 不是 constkeyidx_status_created确认用了你建的索引rows25000预估扫描 2.5w 行因为statuspaid约占 25% 数据不是最终返回行数ExtraUsing filesort危险信号说明ORDER BY created_at没走索引排序MySQL 要把 2.5w 行拉出来再内存排序。怎么消除 filesort把索引改成INDEX (status, created_at)—— 注意顺序WHERE条件字段在前ORDER BY字段在后。改完再EXPLAINExtra变成Using indexrows降到 20因为 BTree 直接按created_at逆序取前20这才是真正的索引优化。6. 一个真实技巧用 slow_log pt-query-digest 定位“慢得离谱”的 SQL学 MySQL 最终要落地到生产问题。你收到告警“订单查询接口 P99 延迟从 200ms 涨到 2s”第一反应不是改代码而是抓 SQL。但SHOW PROCESSLIST只能看到当前运行的慢查询日志才是黑匣子。下面这套组合拳我在三个项目里验证过10 分钟内定位到根因。6.1 开启慢查询日志并设置阈值-- 确认慢日志开关MySQL 8.0 默认 OFF SHOW VARIABLES LIKE slow_query_log; -- 开启需 SUPER 权限 SET GLOBAL slow_query_log ON; -- 设置阈值为 0.5 秒别设 1s很多业务 SQL 天然在 800ms1s 漏掉关键样本 SET GLOBAL long_query_time 0.5; -- 确认日志文件路径 SHOW VARIABLES LIKE slow_query_log_file; -- 输出/var/log/mysql/slow.log注意long_query_time是浮点数单位秒设0.5比1更敏感SET GLOBAL只对新连接生效老连接仍用旧值所以改完要FLUSH LOGS;。6.2 用 pt-query-digest 分析日志比肉眼快 100 倍# 安装 Percona Toolkit比 mysqldumpslow 更准 # Ubuntu: sudo apt install percona-toolkit # CentOS: sudo yum install percona-toolkit # 解析慢日志自动聚合、排序、给出优化建议 pt-query-digest /var/log/mysql/slow.log \ --limit 10 \ --filter $event-{Bytes} 1000 \ --output report \ --no-report-to-file典型输出片段# Profile # Rank Query ID Response time Calls R/Call V/M Item # # 1 0xABCDEF1234567890 123.45s 32.1% 1200 0.1029 0.23 SELECT orders # 2 0x9876543210FEDCBA 45.67s 11.9% 800 0.0571 0.15 SELECT users # Query 1: 0.10 QPS, 0.10x concurrency, ID 0xABCDEF1234567890 at byte 12345 # Scores: V/M 0.23 (0.23x variance / mean), range 0.05-0.32s, crc32 0x12345678 # Time range: 2023-10-01 10:00:00 to 2023-10-01 10:05:00 # Attribute pct total min max avg 95% stddev median # # Count 32 1200 # Exec time 32 123s 100ms 320ms 102ms 280ms 45ms 100ms # Lock time 0 0us 0us 0us 0us 0us 0us 0us # Rows sent 32 24.00k 0 20 20.00 20.00 0.00 20.00 # Rows examine 99 12.00M 10.00k 12.00k 10.00k 12.00k 0.00 10.00k # Query size 25 12.00k 100 100 100 100 0.00 100 # Query_time histogram # 100ms 200ms 300ms 400ms 500ms 600ms 700ms 800ms 900ms 1s # [ 95%] [ 4%] [ 1%] [ 0%] [ 0%] [ 0%] [ 0%] [ 0%] [ 0%] [ 0%] # EXPLAIN for this query: # id select_type table type possible_keys key key_len ref rows Extra # 1 SIMPLE orders ALL NULL NULL NULL NULL 10000 Using where; Using filesort关键信息提取Rows examine 99 12.00M这条 SQL 平均扫描 10w 行但只返回 20 行99.9% 的 I/O 是浪费的EXPLAIN显示typeALL全表扫描没走任何索引Query_time histogram里 95% 的耗时在 100ms说明不是偶发抖动是稳定慢。6.3 用 pt-query-digest 的 --review 自动建优化建议表# 把慢查询样本存到 MySQL 表里方便团队协作分析 pt-query-digest /var/log/mysql/slow.log \ --review hlocalhost,Dtestdb,tslow_log_review \ --create-review-table # 查看建议含缺失索引提示 SELECT * FROM testdb.slow_log_review WHERE fingerprint LIKE %orders% ORDER BY checksum DESC LIMIT 5;输出示例checksumfingerprintsamplets_mints_maxQuery_time_sumLock_time_sumRows_sent_sumRows_examined_sum12345678SELECT * FROM orders WHERE user_id? AND status?SELECT * FROM orders WHERE user_id123 AND statuspaid2023-10-01 10:00:002023-10-01 10:05:00123.450.002400012000000优化动作立刻建索引CREATE INDEX idx_user_status ON orders(user_id, status);再观察pt-query-digest的Rows_examined_sum是否降到 1000 以下。我带过的三个团队都是靠这套流程把平均接口延迟从 1.2s 降到 180ms。不是靠背面试题是靠每天看一眼pt-query-digest输出把Rows_examined当 KPI 来盯。希望帮到你。本文还有配套的精品资源点击获取