前言

做网站开发或者运维的同学肯定都遇到过这种情况：网站越来越慢，数据库CPU占用越来越高，用户体验越来越差。这时候怎么办？很多同学一上来就说：加机器！加内存！其实这是最笨的方法。

MySQL性能优化是一个系统工程，要从多个方面下手：SQL、索引、表结构、配置参数、架构。很多时候，不用加机器，只要优化一下SQL和索引，性能就能提升好几倍。

这篇文章就把MySQL性能优化的完整思路整理出来，从定位问题到SQL优化、表结构优化、参数优化、架构优化，一步一步讲清楚。看完之后你就知道遇到性能问题应该从哪里下手了。

一、优化之前先定位问题

很多同学一上来就瞎优化，优化了半天也不知道有没有效果。正确的做法是：先定位问题，再针对性优化。

怎么定位慢SQL

1. 开启慢查询日志

MySQL的慢查询日志可以记录所有执行慢的SQL。先把慢查询日志开了，看看哪些SQL慢。

# my.cnf里配置
slow_query_log = 1
long_query_time = 1   # 超过1秒的SQL记录下来
slow_query_log_file = /var/log/mysql/slow.log

2. 用explain分析慢SQL

找到慢SQL之后，用explain看看它的执行计划，看看有没有走索引，是不是全表扫描。

3. 看数据库的状态

用下面的命令看看数据库当前的状态：

SHOW GLOBAL STATUS;

重点关注这几个指标：

• Slow_queries：慢查询的数量
• Threads_connected：当前连接数
• Innodb_row_reads：读了多少行数据
• Innodb_data_reads：读了多少数据

二、SQL和索引优化

这是最常用、也是性价比最高的优化方式。很多时候，加个索引，SQL性能就能提升几十倍。

1. 加合适的索引

哪些字段要加索引

• WHERE条件里经常用到的字段
• 表连接的字段
• ORDER BY、GROUP BY的字段
• 区分度高的字段

索引的注意点

• 不要加太多索引，索引也占空间，而且插入更新的时候要维护索引
• 联合索引要注意最左前缀原则
• 尽量不要在索引字段上做函数操作，不然索引会失效

2. 避免索引失效的情况

常见的索引失效场景：

• WHERE条件里对索引字段做函数操作
• WHERE条件里用了!=、<>、NOT IN
• LIKE以%开头
• 联合索引不满足最左前缀
• 字段类型不匹配（比如字符串字段用数字去查）

3. 优化SQL写法

不要用SELECT *

只查需要的字段，不要查所有字段。这样不仅减少数据传输，还能用到覆盖索引。

避免大分页

比如LIMIT 100000, 10，这种越往后越慢。优化方法：用上次的最大id来查。

避免在WHERE里做计算

比如WHERE age + 1 = 18，这样索引会失效。应该写成WHERE age = 17。

小表驱动大表

联表查询的时候，小表在前，大表在后，性能更好。

三、表结构优化

1. 选择合适的数据类型

• 能用数字类型就不要用字符串
• 能用短的就不要用长的，比如用TINYINT不要用INT
• 时间类型用DATETIME或者TIMESTAMP，不要用字符串存时间

2. 避免太多字段

一张表不要有太多字段，字段太多的话，数据页能放的行就少，IO次数就多。不常用的字段可以拆到另一张表里。

3. 适当加冗余字段

有些字段虽然可以联表查出来，但是如果经常用到，可以考虑加冗余字段，减少联表查询。

4. 大表做归档

如果表里有很多历史数据，但是查询的时候很少用到，可以把历史数据归档到另一张表里，主表只保留最近的数据。这样主表数据量小了，查询就快了。

四、配置参数优化

MySQL的默认配置很多都不是最优的，需要根据自己的服务器配置调一下。

1. innodb_buffer_pool_size

这个是InnoDB的缓冲池大小，是最重要的参数。一般设置成服务器内存的50%~70%。如果你的服务器内存是16G，那这个参数就设成8G~10G。

这个参数设大了，很多数据和索引都能在内存里，就不用读磁盘了，性能会好很多。

2. innodb_log_file_size

这个是redo log的大小，一般设成256M~1G。这个参数影响写性能，太小的话会频繁刷盘。

3. max_connections

最大连接数，根据你的业务量调整，一般设成500~1000就够了。不要设太大，不然每个连接都占内存，反而会拖慢性能。

4. query_cache

MySQL 8.0已经把查询缓存去掉了，因为查询缓存的命中率不高，而且维护成本很高。如果是老版本的MySQL，建议把查询缓存关了。

五、架构层面优化

如果SQL和索引都优化过了，还是不行，那就要从架构层面下手了。

1. 读写分离

主库写，从库读，把读的压力分散到多个从库上。大部分网站都是读多写少，读写分离效果很明显。

2. 加缓存

把热点数据放到Redis里，直接从Redis读，不用查数据库。这是提升性能最明显的方式。

3. 分库分表

单表数据量太大了，到了千万级甚至亿级，那就分库分表，把数据分散到多个表或者多个数据库里。

4. 用更好的硬件

如果前面的优化都做了，还是不够，那只能加硬件了。比如用SSD代替机械硬盘，数据库的性能瓶颈很多时候都是磁盘IO。SSD的IOPS比机械硬盘高几个数量级，提升非常明显。

常见坑

坑1：上来就加机器，不优化SQL

很多同学一遇到性能问题就说：加机器！加内存！其实很多时候都是SQL写的烂，没加索引，优化一下SQL性能就能提升好几倍。加机器是最后手段，不是第一选择。

坑2：索引加了很多，但是都没用到

很多同学觉得索引加的越多越好，结果加了一堆索引，真正查询的时候一个都没用到。加索引之前要先explain一下，看看是不是真的能用到。

坑3：以为加了索引就一定快

很多同学以为只要加了索引，查询就一定快。其实不是，如果索引字段的区分度很低，比如性别只有男和女，那加了索引也没用，优化器可能还是会选择全表扫描。

坑4：调参数瞎调

很多同学看了网上的优化文章，把一堆参数往自己服务器上套，结果把数据库搞出问题了。调参数一定要一点点调，调完观察效果，不要一下子改一堆参数。

坑5：不做监控

很多同学数据库出问题了才知道性能差，平时根本没监控。一定要做监控，比如慢查询数量、CPU使用率、连接数这些指标，提前发现问题。

总结

MySQL性能优化的思路和步骤：

1. 先定位问题：开慢查询日志，找到慢SQL
2. SQL和索引优化：加合适的索引，优化SQL写法，这是性价比最高的
3. 表结构优化：选合适的数据类型，大表归档，适当加冗余字段
4. 配置参数优化：重点调innodb_buffer_pool_size
5. 架构层面优化：读写分离、加缓存、分库分表
6. 最后才是加硬件：比如用SSD、加内存

记住：性能优化是一个系统工程，要从多个方面下手。不要指望一招就能解决所有问题，先从最简单、最容易见效的地方下手，比如加索引、优化SQL，然后再一步步深入。

遇到问题加QQ23979811 协助处理

前言

做网站的同学肯定都遇到过这种情况：网站用户越来越多，数据库里的表数据量越来越大，查询越来越慢，插入也越来越慢。这时候怎么办？很多同学第一反应就是：分库分表！

但是分库分表不是万能药，也不是说只要数据量大了就一定要分。很多同学上来就分库分表，结果把自己搞的更累了，问题反而更多。

这篇文章就把分库分表的知识点从头到尾讲清楚：什么时候需要分库分表？怎么分？分完之后有什么问题？看完之后你就知道什么时候该分，什么时候不该分了。

一、什么时候需要分库分表

很多同学一上来就问：分库分表好不好？其实这是个伪命题。分库分表是有代价的，不是什么场景都适合。

一般来说，出现下面这些情况的时候，才考虑分库分表：

1. 单表数据量太大

一般来说，MySQL单表数据量到了千万级之后，性能就开始下降了。如果到了亿级，那基本上就必须分了。

但是也不是说千万级就一定要分，要看你的表结构和查询。如果表结构简单，查询也都走索引，几千万条数据性能还是能接受的。

2. 单库压力太大

一个数据库实例能承受的连接数、IOPS、CPU都是有限的。如果单库的QPS太高，扛不住了，这时候就需要分库，把压力分散到多个数据库实例上。

3. 业务发展到一定阶段

很多互联网公司的业务发展到一定阶段，单库单表确实扛不住了，这时候才需要分。创业初期的小项目，根本没必要分库分表，纯属给自己找麻烦。

总结一下： 分库分表是最后的手段，不是第一选择。在分之前，先看看能不能通过优化SQL、加索引、读写分离、加缓存这些手段解决问题。如果这些手段都用了还是不行，再考虑分库分表。

二、分库分表的两种方式

分库分表有两种方式：垂直拆分和水平拆分。

1. 垂直拆分

垂直拆分又分两种：

垂直分库

按照业务把不同的表分到不同的数据库里。比如把用户相关的表放到user库，订单相关的表放到order库，商品相关的表放到product库。

优点：

• 业务解耦，不同业务之间互不影响
• 可以针对不同的业务做不同的优化

缺点：

• 跨库联表查询比较麻烦
• 分布式事务问题

垂直分表

把一张大表的字段拆到两张表里。比如把不常用的字段拆到另一张表里，常用的字段留在原表里。

比如一张用户表，有基本信息和详细信息。把基本信息（id、昵称、头像）放到user表，详细信息（简介、地址、备注）放到user_detail表。

优点：

• 减少单表的数据量，查询更快
• 把热点字段和冷门字段分开

缺点：

• 查询的时候可能要联表
• 代码复杂度变高

2. 水平拆分

水平拆分就是把同一张表的数据，按照某个规则分到多张表里。每张表的结构是一样的，只是数据不一样。

比如一张user表有1000万条数据，我们把它分成4张表：user_0、user_1、user_2、user_3。按照用户id取模，0的放user_0，1的放user_1，以此类推。

优点：

• 单表数据量小了，性能上去了
• 可以分散到多个数据库实例上

缺点：

• 跨片查询很麻烦
• 分布式事务问题
• 扩容麻烦

三、怎么选择分片键

水平拆分的时候，最关键的就是选分片键。分片键选不好，后面会很痛苦。

好的分片键有什么特点

1. 数据分布均匀：不要有的分片数据特别多，有的特别少
2. 查询都能带上分片键：大部分查询都能根据分片键定位到具体的分片，不用扫全部分片
3. 尽量不要跨片：尽量避免需要跨片查询的场景

常见的分片键选择

1. 用user_id做分片键

如果你的业务大部分都是跟用户相关的，比如用户的订单、用户的评论，那用user_id做分片键就很合适。同一个用户的数据都在同一个分片上，查询的时候不用跨片。

2. 用order_id做分片键

如果是订单系统，大部分查询都是按订单id查的，那就用order_id做分片键。

3. 按时间做分片

如果是日志类的数据，按时间查询比较多，那就按时间做分片。比如一个月一张表，或者一个季度一张表。

分片算法选什么

常见的分片算法有：

1. 取模

比如：user_id % 4，这样数据会比较均匀。

优点： 数据分布均匀
缺点： 扩容的时候很麻烦，要重新对所有数据做取模，大部分数据都要迁移

2. 范围分片

比如：id在1-1000万的放第一个分片，1000万-2000万的放第二个分片。

优点： 扩容简单，直接加新的分片就行
缺点： 数据分布不均匀，新的数据都写到新的分片上，容易出现热点

3. 一致性哈希

一致性哈希可以解决扩容的时候数据迁移太多的问题。

四、分库分表带来的问题

分库分表不是银弹，分完之后会带来很多新的问题。

1. 分布式事务

原来一个事务里操作同一张表，现在跨了多个分片，原来的本地事务就不管用了，需要用分布式事务。分布式事务的复杂度和性能损耗都是很大的。

2. 跨片查询

原来一条SQL就能查出来的数据，现在要从多个分片查出来再合并。比如要统计所有用户的总数，就得查所有分片然后加起来。如果要分页就更麻烦了。

3. 跨片联表

原来两张表联查很简单，现在两张表在不同的分片上，联查就很麻烦。

4. 全局唯一ID

原来用自增主键就行了，现在分了多个表，自增主键就重复了。需要用全局唯一ID，比如雪花算法。

5. 扩容麻烦

一开始分了4个表，后来数据量太大了，要扩成8个表。这时候数据要重新分布，迁移量很大。

五、常用的分库分表中间件

自己写分库分表的逻辑太麻烦了，一般都是用现成的中间件：

1. ShardingSphere

Apache的开源项目，国内用的比较多。支持分库分表、读写分离、分布式事务这些功能。

2. MyCat

也是一个开源的分库分表中间件，用的也比较多。

3. 官方方案

MySQL本身也有一些分区的功能，比如Range分区、List分区。但是这个是在单库内的分区，不是真正的分库分表。

常见坑

坑1：一上来就分库分表

很多同学刚做项目，就想着分库分表，搞的很复杂。其实小项目根本没必要，单库单表就能扛住。分库分表是等到数据量真的大了再做的事情，不要提前过度设计。

坑2：分片键选的不好

很多同学分片键随便选一个，结果大部分查询都带不上分片键，每次都要扫全部分片，性能反而更差了。分片键一定要选那些查询最常用的字段。

坑3：用取模算法，扩容的时候崩溃了

很多同学一开始用取模算法分了4个表，后来要扩到8个表，结果发现数据全都要重新分布，迁移量巨大，欲哭无泪。所以一开始就要考虑好扩容的问题。

坑4：分完之后跨片查询太多

很多同学分完库分完表之后，发现原来的很多查询都要跨片，性能反而比原来更差了。这就是分片键没选好，或者业务设计的问题。

坑5：以为分库分表是万能的

很多同学觉得分库分表能解决所有性能问题，其实不是。分库分表只是把单表的数据量变小了，但是复杂SQL、慢SQL这些问题还是存在的。优化SQL、加索引这些基础工作还是要做。

总结

MySQL分库分表的核心知识点：

1. 分库分表是最后的手段，不是第一选择，先试试优化SQL、加索引、读写分离、加缓存
2. 什么时候需要分：单表千万级以上、单库压力太大、业务真的到那个规模了
3. 垂直拆分：按业务拆库、按字段拆表
4. 水平拆分：同一张表按某个规则拆成多张表
5. 分片键很重要：要数据分布均匀、查询都能带上、尽量不要跨片
6. 分片算法：取模（均匀但扩容麻烦）、范围（扩容简单但容易有热点）、一致性哈希
7. 分完之后的问题：分布式事务、跨片查询、跨片联表、全局唯一ID、扩容麻烦
8. 常用中间件：ShardingSphere、MyCat

记住：分库分表是有代价的，能不分就不分。真的要分的时候，一定要想清楚分片键和分片算法，不然后面会很痛苦。

遇到问题加QQ23979811 协助处理

前言

做后端开发的同学肯定都遇到过这种情况：写了一条SQL，查询特别慢，但是不知道为什么慢。是没走索引？还是全表扫描？还是数据量太大？

这时候就需要用MySQL的explain命令了。explain可以告诉你MySQL是怎么执行这条SQL的，走了哪个索引，扫了多少行，是不是全表扫描。搞懂了explain，你就能快速定位SQL慢的原因，然后针对性优化。

很多新手同学不知道explain怎么用，也不知道每一列是什么意思。这篇文章就把explain的知识点从头到尾讲清楚，看完之后你就能看懂执行计划了。

一、explain是什么

explain就是MySQL提供的一个分析工具。你在SELECT语句前面加上EXPLAIN，MySQL就不会真的执行这条SQL，而是告诉你它准备怎么执行这条SQL。

explain能告诉你什么

• 这条SQL有没有用到索引
• 扫了多少行数据
• 有没有全表扫描
• 用的是哪个索引
• 表连接的顺序是什么

二、怎么用explain

用法很简单，就在你的SELECT语句前面加上EXPLAIN就行：

EXPLAIN SELECT * FROM users WHERE name = '张三';

执行完之后，MySQL会返回一行结果，每一列都代表不同的信息。

三、explain的各列详解

explain返回的结果有很多列，我们重点讲几个常用的。

1. id

这一列表示SELECT的序号。如果有多个SELECT（比如子查询），id越大的越先执行。

2. select_type

这一列表示查询的类型，常见的有：

• SIMPLE：简单查询，没有子查询或者UNION
• PRIMARY：最外层的查询
• SUBQUERY：子查询里的第一个SELECT
• DERIVED：派生表（FROM子句里的子查询）
• UNION：UNION里的第二个及以后的SELECT

3. table

这一列表示当前这一行是在查哪张表。

4. type

这一列非常重要，表示MySQL是怎么找到数据的，也就是访问类型。从好到差依次是：

• system：表里只有一行数据，最好的情况
• const：通过索引一次就找到了，比如WHERE id=1，id是主键
• eq_ref：联表查询的时候，用主键或者唯一索引关联，最多匹配一行
• ref：用普通索引关联，可能匹配多行
• range：索引范围扫描，比如WHERE id BETWEEN 1 AND 10
• index：扫描整个索引树，比全表扫描好一点
• ALL：全表扫描，最差的情况，一定要优化

重点关注： 如果type是ALL或者index，说明这条SQL性能很差，要赶紧优化。

5. possible_keys

这一列表示理论上可能用到的索引。如果是NULL，说明没有用到索引。

6. key

这一列表示实际用到的索引。如果是NULL，说明真的没用到索引。

重点关注： 如果possible_keys有值，但是key是NULL，说明MySQL没有选择用索引，可能是数据量太小，或者索引失效了。

7. key_len

这一列表示用了索引的长度。可以用来判断用了联合索引的前几个字段。

8. ref

这一列表示用了哪个常量或者列来和索引做比较。

9. rows

这一列表示MySQL估计要扫多少行才能找到需要的数据。这个值越大，说明要扫的数据越多，性能越差。

10. Extra

这一列非常重要，包含了很多额外的信息，常见的有：

• Using where：服务器在存储引擎返回数据之后，又做了一层WHERE过滤
• Using index：用了覆盖索引，不需要回表，性能很好
• Using temporary：用了临时表，一般是GROUP BY或者ORDER BY的时候没有用到索引
• Using filesort：用了文件排序，ORDER BY没有用到索引，性能差
• Using join buffer：联表的时候用了缓冲，说明联表的字段没有索引
• Impossible WHERE：WHERE条件永远是false，根本查不到数据

重点关注： 如果Extra里出现了Using temporary或者Using filesort，说明SQL性能有问题，要优化。

四、重点看哪几列

很多同学看explain结果的时候，这么多列不知道重点看什么。其实重点看这几个：

1. type：有没有全表扫描（ALL）
2. key：有没有用到索引
3. rows：扫了多少行
4. Extra：有没有Using filesort或者Using temporary

只要这几个都没问题，这条SQL的性能一般就不会太差。

五、举个例子

比如我们执行这条SQL：

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;

执行完之后看到：

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders | ref  | user_id       | user_id | 4       | const |  100 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

我们来分析一下：

• type是ref：说明用了普通索引，还不错
• possible_keys是user_id：理论上可以用user_id索引
• key是user_id：实际用了user_id索引
• rows是100：估计要扫100行
• Extra是Using where：说明返回之后又做了一层过滤

从这个结果来看，这条SQL用了索引，性能还可以。但是status字段没有用到索引，如果status过滤性好的话，可以考虑建一个(user_id, status)的联合索引。

六、explain的扩展用法

除了基本的explain，还有几个扩展的用法：

EXPLAIN PARTITIONS

看这条SQL会不会命中分区表：

EXPLAIN PARTITIONS SELECT * FROM orders WHERE ...;

EXPLAIN FORMAT=JSON

输出JSON格式的执行计划，信息更详细：

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE ...;

常见坑

坑1：看到type是const就以为没问题

很多同学看到type是const就以为这条SQL性能很好。但是如果rows很大，或者Extra里有Using filesort，性能还是可能有问题。不能只看type，要综合看。

坑2：以为possible_keys有值就一定会用索引

很多同学看到possible_keys有值，就以为这条SQL一定会用到索引。其实不是，possible_keys只是说理论上可能用到，实际用不用要看key列。如果key是NULL，说明MySQL最后没有用这个索引。

坑3：rows是精确值

很多同学以为rows列是精确的行数，其实不是。rows是MySQL估计出来的行数，不是精确值。但是大致能反映出扫描的数据量大小。

坑4：以为Using filesort就是用了文件排序

很多同学看到Extra里有Using filesort就以为是真的用磁盘文件排序。其实不是，Using filesort只是说明MySQL做了额外的排序操作，不一定是用磁盘，数据量小的时候也可能在内存里排。

坑5：只看单表的explain，不看联表的

很多同学优化SQL的时候，只看单表的执行计划，不看联表的。其实联表的时候，表的连接顺序对性能影响很大。explain会按表的连接顺序一行一行列出来，要按顺序看。

总结

MySQL explain执行计划的核心知识点：

1. explain的作用：看MySQL是怎么执行SQL的，不用真的执行
2. type列最重要：从好到差是system > const > eq_ref > ref > range > index > ALL，出现ALL就是全表扫描
3. key列：实际用到的索引，NULL就是没用到索引
4. rows列：估计要扫多少行，越大越慢
5. Extra列：重点关注Using filesort和Using temporary，出现这两个就要优化
6. 重点看type、key、rows、Extra这四列，其他的了解就行
7. rows是估计值，不是精确的，但是能反映大致的数据量

explain是分析SQL性能的必备工具，不管是面试还是实际开发，都是必须掌握的。以后遇到SQL慢，先explain一下，看看是哪里出了问题。

遇到问题加QQ23979811 协助处理

前言

做运维或者DBA的同学肯定都遇到过这种情况：不小心执行了一个DROP TABLE语句，或者DELETE忘加WHERE条件，把数据删了。这时候怎么办？别急，MySQL的binlog日志可以帮你把数据找回来。

很多新手同学不知道binlog是什么，也不知道怎么用它来恢复数据。这篇文章就把binlog的知识点从头到尾讲清楚，包括binlog的三种格式、怎么开启、怎么查看、怎么用它恢复数据，看完之后你就彻底搞懂了。

一、binlog是什么

binlog的全称是二进制日志（Binary Log）。它记录了MySQL数据库里所有的写操作（INSERT、UPDATE、DELETE、CREATE TABLE这些），但是不记录SELECT查询。

binlog有什么用

1. 数据恢复：不小心删了数据，可以用binlog恢复
2. 主从复制：主库把binlog传给从库，从库重放binlog，实现主从同步
3. 数据审计：可以通过binlog追溯谁在什么时候做了什么操作

二、binlog的三种格式

binlog有三种格式，不同的格式记录的内容不一样。

1. STATEMENT（语句模式）

这种格式记录的是SQL语句本身。比如你执行了一条INSERT语句，binlog里就记录这条INSERT语句。

优点：

• 日志量小，占用空间少
• 同步的时候从库重放SQL就行

缺点：

• 有些函数（比如NOW()、UUID()）在主库和从库执行结果可能不一样，导致主从不一致
• 某些复杂的SQL可能在从库上执行结果不一样

2. ROW（行模式）

这种格式记录的是每一行数据的修改。比如你更新了100行数据，binlog里就记录这100行每一行改前是什么样，改后是什么样。

优点：

• 记录的是真实的数据修改，不会出现主从不一致的问题
• 能精确知道哪一行被改了

缺点：

• 日志量大，因为每一行修改都要记录
• 批量更新的时候，日志会特别大

3. MIXED（混合模式）

这种模式是前两种的混合。MySQL会自动判断：一般的SQL用STATEMENT模式，遇到那些可能出问题的SQL（比如用了NOW()、UUID()这些函数的），就自动切换成ROW模式。

总结： 现在一般推荐用ROW模式，虽然日志量大一点，但是数据一致性有保证，不会出主从不一致的问题。

三、怎么开启binlog

默认情况下，MySQL的binlog可能是没开的。我们先看看开没开：

SHOW VARIABLES LIKE 'log_bin';

如果Value是ON，说明开了；如果是OFF，说明没开。

开启binlog

编辑MySQL的配置文件 /etc/my.cnf，在[mysqld]下面添加：

[mysqld]
# 开启binlog
log_bin = mysql-bin

# binlog的格式，推荐用ROW
binlog_format = ROW

# 保存多少天的binlog，过期自动删除
expire_logs_days = 7

# 单个binlog文件最大多大
max_binlog_size = 100M

修改完之后重启MySQL服务：

systemctl restart mysqld

重启完之后再查一下，应该就开了。

四、怎么查看binlog

查看有哪些binlog文件

SHOW BINARY LOGS;

执行完之后会看到类似这样的结果：

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     15264 |
| mysql-bin.000002 |       126 |
| mysql-bin.000003 |       126 |
+------------------+-----------+

binlog文件是按编号来的，mysql-bin.000001满了就生成mysql-bin.000002，以此类推。

查看binlog里的内容

binlog是二进制文件，不能直接用cat看，要用mysqlbinlog工具。

比如要看mysql-bin.000001这个文件：

mysqlbinlog /var/lib/mysql/mysql-bin.000001

如果是ROW格式的binlog，直接看是看不懂的，要加-vv参数：

mysqlbinlog -vv /var/lib/mysql/mysql-bin.000001

这样就能看到具体改了哪一行，改前是什么，改后是什么。

五、怎么用binlog恢复数据

这是大家最关心的：不小心删了数据，怎么用binlog恢复？

恢复的原理

binlog记录了所有的写操作。如果你不小心删了某段时间的数据，只要把这段时间之前的binlog重新执行一遍，数据就回来了。

举个例子

假设你在2026-10-05 10:00的时候，不小心执行了一条DELETE语句，把testdb库的users表全删了。现在要恢复数据。

步骤1：找到要恢复的binlog文件

先看看有哪些binlog文件，找到那个时间点对应的文件：

SHOW BINARY LOGS;

步骤2：找到删除操作的位置

用mysqlbinlog工具查看binlog，找到DELETE语句的位置：

mysqlbinlog --start-datetime="2026-10-05 09:00:00" --stop-datetime="2026-10-05 11:00:00" /var/lib/mysql/mysql-bin.000003

然后找到那条DELETE语句在binlog里的位置（position）。

步骤3：恢复数据

把DELETE语句之前的binlog重新执行一遍，数据就回来了：

mysqlbinlog --stop-position=刚才找到的位置 /var/lib/mysql/mysql-bin.000003 | mysql -u root -p

这样就把DELETE之前的数据恢复了。

注意： 恢复完之后，DELETE之后的操作也没了，所以还要把DELETE之后的操作也重新执行一遍，直到最新的位置。

六、binlog的其他常用操作

手动生成新的binlog文件

FLUSH LOGS;

这个命令会关闭当前的binlog文件，生成一个新的。一般备份完数据库之后执行这个，这样备份之后的操作都记录到新的binlog里，恢复的时候方便。

删除旧的binlog文件

-- 删除指定文件之前的所有binlog
PURGE BINARY LOGS TO 'mysql-bin.000003';

-- 删除指定日期之前的所有binlog
PURGE BINARY LOGS BEFORE '2026-10-01 00:00:00';

查看当前正在写的binlog

SHOW MASTER STATUS;

这个命令会显示当前正在写的binlog文件名和位置，主从复制的时候经常用到。

常见坑

坑1：binlog没开，删了数据找不回来

很多同学的MySQL默认没开binlog，结果不小心删了数据，才发现根本没有日志可以恢复，欲哭无泪。生产环境一定要开binlog！

坑2：binlog格式用了STATEMENT，主从不一致

很多同学用默认的STATEMENT格式，结果主从数据不一致。推荐用ROW格式，虽然日志大一点，但是数据一致性有保证。

坑3：binlog占满磁盘

很多同学开了binlog但是没设置过期时间，结果binlog文件越来越多，把磁盘占满了，MySQL直接挂了。一定要设置expire_logs_days，让旧的binlog自动删除。

坑4：恢复数据的时候把DELETE也执行了

很多同学恢复的时候，把DELETE语句也一起执行了，结果恢复完数据又被删了。一定要找到DELETE之前的位置，只恢复到那个位置之前。

坑5：恢复完没验证

很多同学恢复完数据就以为完事了，结果一查发现数据不对。恢复完一定要仔细核对数据，确保没问题了再上线。

总结

MySQL binlog的核心知识点：

1. binlog记录所有写操作，不记录查询
2. binlog的三个作用：数据恢复、主从复制、数据审计
3. 三种格式：
   • STATEMENT：记录SQL语句，日志小，但是可能主从不一致
   • ROW：记录每行数据修改，日志大，但是数据一致，推荐用
   • MIXED：混合模式，自动选择
4. 开启binlog：在my.cnf里配置log_bin和binlog_format
5. 查看binlog：用mysqlbinlog工具，ROW格式要加-vv
6. 恢复数据：找到误操作之前的位置，重新执行之前的binlog
7. 注意设置过期时间：不然binlog会把磁盘占满

binlog是MySQL最重要的日志之一，生产环境必须开。学会了用binlog恢复数据，以后不小心删数据的时候就不用慌了。

遇到问题加QQ23979811 协助处理

前言

做后端开发的同学肯定都听说过事务隔离级别，但是很多同学搞不清楚读未提交、读提交、可重复读、串行化这四个级别到底有什么区别，也不知道自己项目应该用哪个级别。

很多面试的时候也经常被问到：MySQL的默认隔离级别是什么？能解决什么问题？很多同学答不上来。

这篇文章就把MySQL的事务隔离级别从头到尾讲清楚，看完之后你就彻底搞懂了。

一、先复习一下事务的ACID特性

讲隔离级别之前，先复习一下事务的四个特性，也就是ACID：

1. 原子性（Atomicity）：事务里的操作要么全部成功，要么全部失败回滚
2. 一致性（Consistency）：事务执行前后，数据库的完整性约束没有被破坏
3. 隔离性（Isolation）：多个事务之间互相隔离，不能互相干扰
4. 持久性（Durability）：事务提交之后，修改是永久的

我们今天讲的隔离级别，就是跟第三个特性"隔离性"有关的。隔离性的程度不同，就有了不同的隔离级别。

二、并发事务会带来什么问题

为什么需要隔离级别？因为多个事务同时操作数据库的时候，会出现各种问题。

常见的并发问题有三个：

1. 脏读

脏读就是：一个事务读到了另一个事务还没提交的数据。

比如：

• 事务A把某条数据从1改成了2，但是还没提交
• 事务B读了这条数据，读到的是2
• 然后事务A回滚了，数据又变回了1
• 事务B读的那个2就是脏数据，这就是脏读

2. 不可重复读

不可重复读就是：一个事务里，两次读同一条数据，结果不一样。

比如：

• 事务A第一次读某条数据，值是1
• 事务B把这条数据改成了2，并且提交了
• 事务A第二次再读这条数据，值变成了2
• 同一个事务里，两次读同一条数据结果不一样，这就是不可重复读

3. 幻读

幻读就是：一个事务里，两次查询的结果集不一样。

比如：

• 事务A查询id > 5的记录，查到了5、6两条
• 事务B插入了一条id=7的记录，并且提交了
• 事务A再查一次，发现多了一条id=7的
• 这就是幻读

注意：不可重复读和幻读的区别：

• 不可重复读：同一条数据，两次读结果不一样（修改导致的）
• 幻读：查询的结果集变多或者变少了（插入/删除导致的）

三、四个事务隔离级别

为了解决上面说的这三个问题，SQL标准定义了四个隔离级别，级别越高，越能解决问题，但是并发性能越差。

1. 读未提交（Read Uncommitted）

最低的隔离级别。一个事务可以读到另一个事务还没提交的数据。

能解决什么问题： 啥也解决不了
会出现什么问题： 脏读、不可重复读、幻读都可能出现

这个级别基本上没人用，所以就不多说了。

2. 读提交（Read Committed，RC）

一个事务只能读到另一个事务已经提交的数据。

能解决什么问题： 解决了脏读
还会出现什么问题： 不可重复读、幻读

Oracle默认就是这个隔离级别。

3. 可重复读（Repeatable Read，RR）

一个事务里，多次读同一条数据，结果是一样的。

能解决什么问题： 解决了脏读、不可重复读
还会出现什么问题： 幻读

注意：MySQL的InnoDB引擎在RR级别下，通过间隙锁解决了幻读问题，所以MySQL的RR级别实际上可以完全解决幻读。

MySQL默认就是这个隔离级别。

4. 串行化（Serializable）

最高的隔离级别。所有事务一个一个排队执行，完全串行。

能解决什么问题： 脏读、不可重复读、幻读全都解决了
缺点： 性能太差了，并发度最低，基本上没人用

四、四个隔离级别对比表

给大家整理了一个表格，一目了然：

五、MySQL默认的隔离级别

很多同学面试的时候被问到"MySQL默认的隔离级别是什么"，答不上来。

记住：MySQL InnoDB默认的隔离级别是可重复读（RR）。

注意和Oracle区分一下：Oracle默认的是读提交（RC）。

六、怎么查看当前的隔离级别

登录MySQL之后，执行下面的命令：

-- 查看全局的隔离级别
SELECT @@global.tx_isolation;

-- 查看当前会话的隔离级别
SELECT @@session.tx_isolation;

执行完之后会看到类似这样的结果：

+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这就说明当前是可重复读级别。

七、怎么修改隔离级别

临时修改（当前会话）

-- 改成读提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 改成可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

这种修改只对当前连接有效，断开连接之后就恢复了。

全局临时修改

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

这种修改对新连接有效，已经存在的连接不受影响，MySQL重启之后就恢复了。

永久修改

要永久修改的话，需要改配置文件。编辑my.cnf，在[mysqld]下面添加：

[mysqld]
transaction-isolation = READ-COMMITTED

修改完之后重启MySQL服务，就永久生效了。

八、实际项目中应该用哪个级别

很多同学问：实际项目中应该用哪个隔离级别？

一般来说：

• 如果对并发性能要求高：用读提交（RC）就够了，很多互联网公司都是用RC
• 如果对数据一致性要求高：用可重复读（RR），MySQL默认就是这个
• 串行化基本上没人用，性能太差了

其实大部分情况下，用MySQL默认的RR就够了，不用特意改。如果并发特别高，可以考虑改成RC，这样间隙锁就没了，并发性能会好很多。

常见坑

坑1：不知道MySQL默认的隔离级别是RR

很多同学以为MySQL默认是读提交，跟Oracle一样，结果面试答错了。记住：MySQL默认是可重复读（RR）。

坑2：把不可重复读和幻读搞混了

很多同学分不清不可重复读和幻读。记住：

• 不可重复读：同一条数据被修改了，两次读结果不一样
• 幻读：结果集的行数变多或者变少了，是插入/删除导致的

坑3：以为RR级别解决不了幻读

很多同学以为RR级别解决不了幻读，其实MySQL的InnoDB引擎在RR级别下，通过间隙锁是可以解决幻读的。这一点跟标准SQL不一样，MySQL做了增强。

坑4：修改了隔离级别但是没生效

很多同学用SET GLOBAL改了隔离级别，然后用当前连接查，发现还是原来的级别。这是因为SET GLOBAL只对新连接有效，当前连接还是原来的。要当前连接生效的话，用SET SESSION。

总结

MySQL事务隔离级别的核心知识点：

1. 并发事务的三个问题：脏读、不可重复读、幻读
2. 四个隔离级别：读未提交、读提交、可重复读、串行化，级别越高越安全，性能越差
3. 脏读：读到了别的事务还没提交的数据
4. 不可重复读：同一个事务里，两次读同一条数据结果不一样（修改导致的）
5. 幻读：同一个事务里，两次查询的结果集不一样（插入/删除导致的）
6. MySQL InnoDB默认是可重复读（RR），并且通过间隙锁解决了幻读
7. 查看隔离级别：SELECT @@tx_isolation
8. 修改隔离级别：SET SESSION/GLOBAL TRANSACTION ISOLATION LEVEL ...
9. 实际项目用RR或者RC就够了，串行化性能太差没人用

事务隔离级别是数据库的基础知识点，不管是面试还是实际开发，都是必须掌握的。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定都遇到过这种情况：两个人同时修改同一条数据，结果后提交的把先提交的覆盖了，数据就不对了。或者两个事务互相等对方释放锁，结果都卡住了，这就是死锁。

这些问题其实都是跟MySQL的锁机制有关的。很多新手同学对锁的概念很模糊，不知道什么是表锁、行锁、间隙锁，也不知道什么时候会加什么锁。这篇文章就把MySQL的锁机制从头到尾讲清楚，看完之后你就对锁有一个完整的认识了。

一、为什么需要锁

为什么数据库需要锁？很简单，因为要保证数据的一致性。

如果没有锁，两个事务同时修改同一条数据，就会出现问题。比如：

• A事务读了一条数据，值是100
• B事务也读了同一条数据，值也是100
• A事务给它加1，变成101，写回去
• B事务也给它加1，变成101，写回去

结果两个操作加起来，应该变成102，结果实际变成了101。这就出问题了。

锁的作用就是：当一个事务在操作数据的时候，其他事务不能操作，这样就保证了数据的一致性。

二、表锁

表锁就是锁整张表。当一个事务给表加了表锁，其他事务就不能操作这张表了。

表锁的特点

• 开销小，加锁快：因为不用锁每一行，直接锁整张表就行
• 不会出现死锁：因为一次就锁整张表，不存在互相等的情况
• 锁粒度大，并发度低：一张表同一时间只能有一个事务写，并发高了性能就差

什么时候用表锁

• 基本上我们用的InnoDB引擎很少用表锁，MyISAM引擎默认就是表锁
• InnoDB引擎在某些特殊情况下也会用表锁，比如：
  • 没有用到索引，导致行锁失效，升级成表锁
  • 要修改很多行数据，优化器觉得用表锁更划算

怎么手动加表锁

-- 加读锁（共享锁）
LOCK TABLES test READ;

-- 加写锁（排他锁）
LOCK TABLES test WRITE;

-- 释放锁
UNLOCK TABLES;

三、行锁

行锁就是锁某一行数据。当一个事务给某一行加了行锁，其他事务就不能操作这一行了，但是可以操作表里的其他行。

行锁的特点

• 开销大，加锁慢：要找到具体的行，然后加锁
• 可能会出现死锁：两个事务分别锁了不同的行，又互相等对方释放
• 锁粒度小，并发度高：不同的行可以同时被不同的事务操作，并发性能好

InnoDB的行锁是怎么实现的

很多同学以为InnoDB的行锁是真的锁数据行，其实不是。InnoDB的行锁是锁索引实现的。

也就是说，你WHERE条件用到了哪个索引，InnoDB就会给那个索引上加锁。如果你的WHERE条件没有用到索引，那InnoDB就没办法给行加锁了，这时候就会扫描全表，把所有行都锁一遍，相当于变成了表锁。

这就是为什么索引失效会导致性能差：不仅查询慢，还会把所有行都锁了，并发性能直接崩了。

行锁的两种模式

1. 共享锁（S锁）：读锁，加了S锁之后，其他事务也能加S锁，但是不能加X锁

   • 比如：SELECT ... LOCK IN SHARE MODE

2. 排他锁（X锁）：写锁，加了X锁之后，其他事务既不能加S锁，也不能加X锁

   • 比如：SELECT ... FOR UPDATE
   • UPDATE、DELETE语句默认都会加X锁

四、间隙锁

间隙锁是InnoDB里比较特殊的一种锁，很多同学都搞不懂。

简单来说，间隙锁就是锁两个值之间的间隙。比如你的表有1、3、5三个值，那间隙锁可以锁 (1,3)、(3,5) 这些区间，让你不能往这个区间里插数据。

间隙锁是干嘛的

间隙锁是为了防止幻读。

什么是幻读？比如：

• 事务A查询id > 5的记录，查到了5、6两条
• 事务B插入了一条id=7的记录
• 事务A再查一次，发现多了一条id=7的，这就是幻读

间隙锁就是为了解决这个问题：当你查询某个范围的数据时，InnoDB会给这个范围的间隙也加上锁，这样其他事务就不能往这个范围里插数据了，就不会出现幻读了。

间隙锁的注意点

1. 间隙锁只会在可重复读（RR）隔离级别下才有
2. 间隙锁和间隙锁之间不冲突，但是间隙锁和插入操作是冲突的
3. 间隙锁会导致加锁的范围变大，本来你只想锁一条记录，结果把周围的间隙也锁了，并发性能就差了

五、临键锁（Next-Key Lock）

临键锁就是行锁 + 间隙锁的组合。InnoDB在可重复读隔离级别下，默认用的就是临键锁。

比如你有一个索引，值是1、3、5、7。如果你查询id=3，那InnoDB加的临键锁就是 (1, 3]，也就是锁了1到3这个区间，包括3本身。

临键锁的作用就是：既锁了这条记录，又锁了它前面的间隙，这样就防止了幻读。

六、死锁

死锁就是两个或者多个事务互相等对方释放锁，结果谁也动不了，就卡住了。

比如：

• 事务A锁了id=1的行，想要锁id=2的行
• 事务B锁了id=2的行，想要锁id=1的行
• 这时候两个事务互相等，就死锁了

怎么解决死锁

MySQL有自动检测死锁的机制，发现死锁之后，会自动回滚其中一个事务，让另一个事务继续执行。

怎么避免死锁

1. 按相同的顺序访问表和行：比如所有事务都先锁id=1，再锁id=2，就不会死锁了
2. 大事务拆成小事务：事务越小，持锁时间越短，死锁概率越低
3. 尽量用索引访问数据：不用索引的话会锁更多的行，更容易死锁
4. 降低隔离级别：比如从可重复读降到读提交，间隙锁就没了，死锁概率也会低一点

七、怎么查看当前有哪些锁

MySQL8.0可以用下面的命令查看当前的锁：

SELECT * FROM performance_schema.data_locks;

或者看一下当前的事务：

SELECT * FROM information_schema.innodb_trx;

常见坑

坑1：没有用索引，行锁变成表锁

很多同学以为自己加的是行锁，结果发现整张表都被锁了。这是因为你的WHERE条件没有用到索引，InnoDB没办法定位到具体的行，只能扫描全表，把所有行都锁了，相当于表锁。

解决方法： 确保你的查询语句用到了索引，这样才会加行锁而不是表锁。

坑2：间隙锁导致并发性能差

很多同学发现自己的并发不高，但是锁冲突很严重。这就是因为间隙锁把范围锁大了。本来你只想锁一条记录，结果把周围的间隙也锁了，其他事务插数据就会被挡住。

解决方法： 如果不需要解决幻读，可以把隔离级别降到读提交（RC），这样就没有间隙锁了，并发性能会好很多。

坑3：事务太长，持锁时间久

很多同学写代码的时候，事务里包含了RPC调用、查Redis、做复杂计算这些耗时操作。结果这个事务持锁的时间特别长，其他事务都在等锁，性能就差了。

解决方法： 事务里只放数据库操作，其他耗时操作都放到事务外面。

坑4：死锁了不知道怎么排查

很多同学遇到死锁就慌了，不知道怎么查。其实MySQL默认会把死锁的信息记录到错误日志里。可以看看MySQL的错误日志，里面有详细的死锁信息。

也可以用这个命令查看最近的死锁：

SHOW ENGINE INNODB STATUS;

里面有个LATEST DETECTED DEADLOCK部分，就是最近一次死锁的信息。

总结

MySQL锁机制的核心知识点：

1. 表锁：锁整张表，开销小，并发低，MyISAM默认用
2. 行锁：锁某一行，开销大，并发高，InnoDB默认用
3. 行锁是锁索引实现的，没有用到索引的话会变成表锁
4. 共享锁（S锁）：读锁，多个事务可以同时加
5. 排他锁（X锁）：写锁，只能有一个事务加
6. 间隙锁：锁两个值之间的间隙，用来防止幻读，只有可重复读隔离级别才有
7. 临键锁：行锁 + 间隙锁，InnoDB默认的加锁方式
8. 死锁：两个事务互相等对方释放锁，MySQL会自动回滚其中一个
9. 避免死锁的方法：按相同顺序访问、小事务、用索引

锁是数据库里比较复杂的一个知识点，但是也是必须掌握的。搞懂了锁，你才能写出高并发的数据库代码。

遇到问题加QQ23979811 协助处理

前言

做网站的同学肯定都遇到过这种情况：网站用户越来越多，数据库压力越来越大，单台数据库扛不住了。这时候怎么办？很常见的方案就是做主从复制，主库写数据，从库读数据，把读写压力分开。

很多新手同学觉得主从复制很难，不敢尝试。其实只要按照步骤来，一步步配置，很快就能搭起来。这篇文章就把一主一从的主从复制搭建完整教程整理出来，从原理到实操，一步一步讲清楚。

一、主从复制是干嘛的

简单来说，主从复制就是：主数据库（Master）负责写数据，从数据库（Slave）负责读数据。主库的数据会自动同步到从库上。

主从复制的作用

1. 读写分离：写操作走主库，读操作走从库，减轻主库压力
2. 数据备份：从库相当于实时备份，主库挂了从库还能顶上
3. 做数据分析：可以在从库上跑复杂的统计查询，不影响主库的业务

主从复制的原理

主从复制的原理其实很简单，三步：

1. 主库把数据变更记录到二进制日志（binlog）里
2. 从库把主库的binlog拉过来，写到自己的中继日志（relay log）里
3. 从库重放中继日志里的SQL，这样数据就和主库一致了

二、准备工作

搭建主从复制之前，先准备好环境：

服务器准备

需要两台服务器：

• 主库服务器：IP假设是 192.168.1.10
• 从库服务器：IP假设是 192.168.1.11

软件要求

• 两台服务器都要安装MySQL，版本最好一样
• 主库和从库的server-id不能一样
• 两台服务器的MySQL端口要能互相通（一般是3306）

三、配置主库

首先配置主库，编辑主库的my.cnf配置文件：

vi /etc/my.cnf

在 [mysqld] 下面添加这些配置：

[mysqld]
# 唯一的服务器ID，主库和从库不能一样
server-id = 1

# 开启二进制日志
log-bin = mysql-bin

# 需要同步的数据库，多个用逗号隔开
binlog-do-db = testdb

# 不同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

修改完之后重启主库的MySQL：

systemctl restart mysqld

给从库创建一个复制账号

主库上要创建一个专门用来复制的用户，从库要用这个账号来连主库拉数据。

登录主库MySQL：

mysql -u root -p

然后执行：

CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY 'repl123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11';
FLUSH PRIVILEGES;

这个命令创建了一个叫repl的用户，密码是repl123456，只能从从库的IP（192.168.1.11）连接，并且有复制的权限。

查看主库的状态

接下来要记录主库的binlog文件名和位置，从库配置的时候要用：

SHOW MASTER STATUS;

执行完之后会看到类似这样的结果：

+------------------+----------+--------------+----------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------+-------------------+
| mysql-bin.000001 |      156 | testdb       | mysql,information_schema,...     |                   |
+------------------+----------+--------------+----------------------------------+-------------------+

记住File和Position的值，比如这里是 mysql-bin.000001 和 156。

注意：执行完这个命令之后，主库就不要再写数据了，不然Position会变，到时候从库同步就会出错。

四、配置从库

接下来配置从库。编辑从库的my.cnf配置文件：

vi /etc/my.cnf

在 [mysqld] 下面添加：

[mysqld]
# 唯一的服务器ID，和主库不一样就行
server-id = 2

# 开启中继日志
relay-log = relay-bin

# 只读模式，从库默认只读，防止误写
read_only = 1

修改完之后重启从库的MySQL：

systemctl restart mysqld

配置从库连接主库

登录从库的MySQL：

mysql -u root -p

然后执行下面的命令，告诉从库主库在哪里，用什么账号连接：

CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl123456',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=156;

这里的MASTER_LOG_FILE和MASTER_LOG_POS就是刚才在主库上用SHOW MASTER STATUS查出来的值。

启动从库复制

配置完之后，启动从库的复制线程：

START SLAVE;

查看从库状态

看看从库是不是正常运行：

SHOW SLAVE STATUS\G

执行完之后会看到很多信息，重点看这两个：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果这两个都是Yes，那就说明主从复制已经搭好了！

如果是No，那就说明哪里出错了，下面的Last_Error字段会显示错误信息，根据错误信息去排查就行。

五、测试主从复制

现在来测试一下主从复制是不是正常工作。

在主库上创建一个表，插入一些数据：

USE testdb;
CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

INSERT INTO test (name) VALUES ('张三'), ('李四'), ('王五');

然后去从库上查一下：

USE testdb;
SELECT * FROM test;

如果能看到刚才插入的那三条数据，那就说明主从复制是正常工作的！

六、主从复制的常见问题

主从延迟

主从复制不是实时的，有时候主库写了数据，从库要等一会儿才能读到。如果主库压力很大，或者从库性能差，延迟可能会很大。

可以用这个命令查看从库延迟了多少秒：

SHOW SLAVE STATUS\G

看Seconds_Behind_Master这个值，就是延迟的秒数。如果是0，说明没有延迟。

从库误写数据

很多同学不知道从库是只读的，不小心在从库上写了数据，导致主从数据不一致，复制出错。

解决方法：一定要开启read_only=1，并且不要用root账号在从库上操作。

常见坑

坑1：主库和从库的server-id一样

很多同学配置的时候，忘了改从库的server-id，结果主从复制启动不起来。记住：主库和从库的server-id必须不一样。

坑2：主库防火墙没开

主库的防火墙没开3306端口，导致从库连不上主库。一定要检查防火墙是不是放行了3306端口。

坑3：配置完CHANGE MASTER之后忘了START SLAVE

很多同学配置完主库和从库之后，就以为完事了，结果发现数据根本没同步。这是因为忘了执行START SLAVE启动复制线程。

坑4：SHOW MASTER STATUS之后主库还在写数据

很多同学在主库执行了SHOW MASTER STATUS之后，还在往主库写数据，导致Position变了，从库配置的时候用的是旧的Position，结果同步出错。记住：执行SHOW MASTER STATUS之后，主库就不要再写数据了。

坑5：主库和从库数据一开始就不一致

很多同学的主库已经有很多数据了，直接配置主从复制，结果从库同步的时候出错。正确的做法应该是：先把主库的数据导出，导入到从库，然后再配置主从复制。

总结

MySQL主从复制搭建的步骤：

1. 准备两台服务器，都安装好MySQL
2. 配置主库：设置server-id，开启binlog
3. 主库创建复制账号
4. 主库执行SHOW MASTER STATUS，记录File和Position
5. 配置从库：设置server-id，开启relay log
6. 从库执行CHANGE MASTER TO，指向主库
7. 从库执行START SLAVE启动复制
8. 查看从库状态，确认IO和SQL线程都是Yes
9. 在主库写数据，从库查数据，测试同步

主从复制是数据库架构的基础，学会了之后才能搞读写分离、分库分表这些更高级的东西。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定遇到过这个错误：用户访问网站的时候，突然报错了，说"MySQL连接失败：Too many connections"。这时候网站就打不开了，用户都在骂娘，你急得满头大汗不知道怎么办。

这个错误其实很常见，就是MySQL的最大连接数不够用了。很多新手同学遇到这个错误就慌了，不知道怎么处理。这篇文章就把这个问题的原因和解决方法讲清楚，以后再遇到就不用慌了。

一、为什么会出现Too many connections错误

MySQL默认的最大连接数是151，这个数在小项目里够用，但是如果项目用户多了，或者连接池设置得太大，就很容易把连接数占满。

当所有连接都被占满的时候，新的请求就进不来了，这时候MySQL就会报"Too many connections"的错误。

常见原因

1. 应用程序连接池设置太大：比如你的应用配置了500个连接，但是MySQL最大连接数才151，那肯定不够用
2. 慢SQL太多：很多SQL执行得很慢，连接迟迟不释放，占着茅坑不拉屎
3. 连接没有及时释放：应用程序写完代码忘了关闭连接，导致连接泄漏
4. 突发流量：突然来一波大流量，把连接数占满了

二、查看当前连接数和最大连接数

先看看你的MySQL现在最大连接数是多少：

SHOW VARIABLES LIKE 'max_connections';

执行完之后会看到类似这样的结果：

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

再看看当前有多少个连接：

SHOW STATUS LIKE 'Threads_connected';

结果类似：

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 120   |
+-------------------+-------+

你看，当前已经用了120个连接，最大才151，再涨一点就满了。

三、临时调整最大连接数

如果现在已经出现Too many connections错误了，可以先临时把最大连接数调大一点，救个急：

SET GLOBAL max_connections = 500;

这个命令执行完之后立刻生效，不用重启MySQL。但是注意：这个修改是临时的，MySQL重启之后就会恢复成原来的值。

四、永久修改最大连接数

要永久修改的话，需要改配置文件。找到MySQL的配置文件 /etc/my.cnf，在 [mysqld] 下面添加：

[mysqld]
max_connections = 1000

修改完之后重启MySQL服务：

systemctl restart mysqld

这样以后MySQL重启之后最大连接数还是1000，不会变回去。

注意：不要设得太大

很多同学觉得既然不够用，那就设大点，直接设个10000。这是不对的。

每个连接MySQL都要分配内存，如果连接数设得太大，每个连接占的内存加起来就很多了，可能会把服务器内存吃满。

一般来说，最大连接数设到500~1000就够了，要看你的服务器内存有多大。内存大就设大点，内存小就设小点。

五、查看连接是从哪来的

如果连接数突然涨得很猛，你得看看这些连接都是从哪来的，是不是有哪个程序疯了一直在创建连接。

SELECT 
    host, 
    COUNT(*) AS count
FROM information_schema.processlist
GROUP BY host
ORDER BY count DESC;

这个命令会把每个IP的连接数统计出来，你看看是不是某个IP连接特别多，如果是的话，就去查那个程序是不是有问题。

六、查看正在执行的SQL

如果很多连接都在执行SQL，那可能是慢SQL太多导致的。看看现在都在执行什么SQL：

SHOW PROCESSLIST;

执行完之后会列出所有正在运行的连接和SQL语句。你看看有没有执行了很久还没跑完的SQL，如果有的话，那就是罪魁祸首。

七、杀掉空闲连接

如果有很多连接都是空闲的（Sleep状态），占着连接不干活，那可以把它们杀掉，释放连接。

先看看有多少个Sleep状态的连接：

SELECT COUNT(*) FROM information_schema.processlist WHERE command = 'Sleep';

如果Sleep状态的连接很多，可以把它们杀掉。不过手动一个个杀太麻烦了，可以设置一个自动断开空闲连接的参数：

SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;

这两个参数的意思是：连接如果空闲超过60秒，MySQL就自动把它断开，释放连接。

同样，这个修改是临时的，要永久生效的话需要写到配置文件里：

[mysqld]
wait_timeout = 60
interactive_timeout = 60

八、从根本上解决问题

光调大最大连接数只是治标不治本，要从根本上解决问题，还得从应用层面下手：

1. 检查应用程序是不是有连接泄漏：用完连接记得释放，不要一直占着
2. 优化慢SQL：把执行慢的SQL优化一下，减少连接占用时间
3. 合理设置连接池大小：连接池不要设得太大，比最大连接数小一点就行
4. 使用连接池中间件：比如MyCat、ProxySQL这些数据库中间件，可以统一管理连接

常见坑

坑1：改完max_connections之后重启又变回去了

很多同学用SET GLOBAL改完最大连接数之后，以为就完事了，结果MySQL一重启又变回原来的值。这是因为SET GLOBAL是临时修改，要永久生效必须改配置文件。

坑2：max_connections设得太大

很多同学一遇到连接数不够，就直接把max_connections改成10000，结果MySQL直接起不来了，或者服务器内存被吃满了。每个连接都要占内存，连接数越大，内存消耗越多，要根据服务器内存来设，不要盲目设大。

坑3：只改了max_connections，没改表连接数

其实MySQL还有一个参数叫max_user_connections，是限制单个用户的最大连接数的。如果你给某个用户限制了最大连接数，就算全局max_connections很大，那个用户的连接数到上限了还是会报错。

可以用这个命令查看：

SHOW VARIABLES LIKE 'max_user_connections';

坑4：杀掉了正在执行重要SQL的连接

很多同学一看到连接数满了，就不管三七二十一把所有连接都杀掉。结果把正在执行重要业务SQL的连接也杀了，导致业务出问题。杀连接之前一定要看清楚，不要乱杀。

总结

MySQL Too many connections错误的解决方法：

1. 先查看当前最大连接数和已用连接数
2. 临时救急：用SET GLOBAL调大max_connections
3. 永久解决：改my.cnf配置文件，设置max_connections
4. 看看连接都是从哪来的，有没有异常
5. 优化慢SQL，减少连接占用时间
6. 设置wait_timeout，自动断开空闲连接
7. 从应用层面优化，减少连接泄漏

记住：调大最大连接数只是治标，优化SQL和代码才是治本。不要一遇到连接数不够就盲目调大max_connections，先找找根本原因。

遇到问题加QQ23979811 协助处理

前言

做网站开发或者运维的同学，肯定会遇到这种情况：需要给开发或者运营同学一个数据库账号，但是又不能给root权限，怕他们误操作删库。这时候就需要用到MySQL的用户和权限管理功能了。

很多新手同学只会用root账号登录数据库，不知道怎么创建新用户、怎么分配权限。这篇文章就把MySQL用户创建和权限管理的完整教程整理出来，从创建用户、授权、改密码到删除用户，一步一步讲清楚。

一、创建用户

首先我们来创建一个新用户。MySQL创建用户有两种方式：CREATE USER 和 GRANT。

方式1：用CREATE USER创建用户

CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123456';

这个命令创建了一个叫testuser的用户，密码是123456，只能从本机（localhost）连接MySQL。

如果你想让用户可以从任何地方连接，可以把localhost改成%：

CREATE USER 'testuser'@'%' IDENTIFIED BY '123456';

注意：生产环境不建议用%，最好指定具体的IP地址，更安全。

方式2：用GRANT创建用户并直接授权

其实更常用的是创建用户的同时直接授权，一步到位：

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost' IDENTIFIED BY '123456';

这个命令会自动创建testuser用户，并且把testdb数据库的所有权限都给它。

二、给用户授权

创建完用户之后，我们来给用户分配权限。MySQL的权限粒度很细，可以精确到某张表、某个字段。

常用权限说明

• ALL PRIVILEGES：所有权限
• SELECT：查询权限
• INSERT：插入权限
• UPDATE：更新权限
• DELETE：删除权限
• CREATE：创建表/数据库权限
• DROP：删除表/数据库权限
• ALTER：修改表结构权限
• INDEX：创建索引权限
• CREATE VIEW：创建视图权限
• PROCESS：查看进程权限
• RELOAD：执行flush权限
• SHOW DATABASES：查看所有数据库权限

给用户所有权限

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

这个命令的意思是：把testdb数据库里所有表的所有权限都给testuser用户。

只给用户查询和修改权限

GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'localhost';

这样用户就只能查询、插入、修改、删除数据，不能修改表结构，也不能删表。

只给用户某张表的权限

GRANT SELECT ON testdb.users TO 'testuser'@'localhost';

这样用户只能查询testdb库里的users表，其他表都访问不了。

给用户所有数据库的权限

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';

.的意思是所有数据库的所有表。这个权限基本相当于root了，要谨慎使用。

授权之后一定要刷新权限

每次给用户授权之后，一定要执行下面这个命令刷新权限，不然授权可能不生效：

FLUSH PRIVILEGES;

三、查看用户权限

想知道某个用户有什么权限，可以用下面这个命令：

SHOW GRANTS FOR 'testuser'@'localhost';

执行完之后会显示这个用户的所有权限。

查看所有用户

如果想看看MySQL里有哪些用户，可以查询mysql.user表：

SELECT user, host FROM mysql.user;

四、修改用户密码

如果要修改某个用户的密码，可以用下面的命令：

ALTER USER 'testuser'@'localhost' IDENTIFIED BY '新密码';

修改完之后也要刷新权限：

FLUSH PRIVILEGES;

五、回收用户权限

如果用户权限给多了，想收回某些权限，可以用REVOKE命令：

收回所有权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser'@'localhost';

收回某个具体权限

REVOKE DELETE ON testdb.* FROM 'testuser'@'localhost';

这样用户就没有删除数据的权限了。

回收完权限之后也要刷新：

FLUSH PRIVILEGES;

六、删除用户

如果某个用户不需要了，可以删除：

DROP USER 'testuser'@'localhost';

注意：删除用户的时候，用户已有的权限也会一起被删掉。

七、创建用户的安全建议

1. 不要随便给用户ALL PRIVILEGES：按需授权，只给需要的权限就行
2. 不要用%允许所有IP连接：生产环境最好指定具体的IP
3. 不要用root账号给应用用：单独创建一个应用账号，只给需要的权限
4. 密码要复杂一点：不要用123456这种简单密码
5. 定期清理不用的用户：不用的用户及时删掉，减少安全隐患

常见坑

坑1：授权之后忘了FLUSH PRIVILEGES

很多同学授权完之后直接就用了，结果发现权限没生效。这就是因为忘了执行FLUSH PRIVILEGES。每次授权或者改密码之后，一定要执行一下这个命令刷新权限。

坑2：用户创建了但是连不上

很多同学创建完用户之后，用这个用户去连接数据库，结果连接失败。这一般是因为：

1. 你创建用户的时候指定的是localhost，但是你从其他IP连接
2. 防火墙没放行MySQL端口
3. 用户密码输错了

坑3：%和localhost搞混了

很多同学不知道'localhost'@'%'和'%'@'localhost'的区别。记住：用户名是前面的，后面的是允许连接的IP。

坑4：给了权限但是还是操作不了

有时候给了用户权限，但是用户还是操作不了。这一般是因为：

1. 忘了FLUSH PRIVILEGES
2. 表或者字段的权限没给
3. 用户根本就没有这个数据库的权限

总结

MySQL用户创建和权限管理的核心内容：

1. 创建用户：CREATE USER 或者 GRANT 直接创建并授权
2. 给用户授权：GRANT 权限 ON 数据库.表 TO 用户@IP
3. 查看权限：SHOW GRANTS FOR 用户
4. 改密码：ALTER USER 用户 IDENTIFIED BY 新密码
5. 回收权限：REVOKE 权限 ON 数据库.表 FROM 用户
6. 删除用户：DROP USER 用户
7. 所有操作完之后都要 FLUSH PRIVILEGES 刷新权限

记住这些命令，以后给应用或者同事分配数据库账号的时候就不用慌了。安全第一，不要随便给用户root权限。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定都遇到过中文乱码的问题。本来好好的中文，存到数据库里就变成问号或者乱码了。这其实很大概率就是字符集设置不对导致的。

很多新手同学不知道utf8和utf8mb4有什么区别，建库的时候直接用默认的utf8，结果存个emoji表情就报错了，或者存进去之后变成乱码。

这篇文章就详细讲讲MySQL字符集的设置方法，以及utf8和utf8mb4到底有什么区别，应该怎么选。

一、utf8和utf8mb4的区别

首先大家要搞清楚一个坑：MySQL里的utf8并不是真正的UTF-8。

MySQL里的utf8字符集，每个字符最多只能存3个字节。而真正的UTF-8编码，有些字符是占4个字节的，比如emoji表情（😀😂👍这些），还有一些生僻字。

所以如果你用utf8字符集，存emoji表情的时候就会报错，或者存进去之后变成乱码。

而utf8mb4才是真正的UTF-8，每个字符最多可以存4个字节，emoji表情、生僻字都能正常存。

总结一下：

• utf8：每个字符最多3字节，存不了emoji表情
• utf8mb4：每个字符最多4字节，完全兼容UTF-8，推荐使用

现在做新项目，直接用utf8mb4就对了，不要再用utf8了。

二、查看当前MySQL字符集设置

先看看你的MySQL现在用的是什么字符集：

SHOW VARIABLES LIKE '%character%';

执行完之后会看到类似下面的输出：

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

重点看这几个：

• character_set_server：服务器默认字符集
• character_set_database：当前数据库的字符集
• character_set_client：客户端字符集
• character_set_connection：连接字符集
• character_set_results：查询结果字符集

最好这几个都统一成utf8mb4，这样就不会有乱码问题了。

三、新建数据库的时候指定utf8mb4

如果你要新建一个数据库，直接在建库的时候指定字符集：

CREATE DATABASE testdb 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

这样建出来的数据库默认就是utf8mb4字符集了。

新建表的时候也可以指定：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

四、修改MySQL默认字符集

如果你想把MySQL的默认字符集改成utf8mb4，可以修改my.cnf配置文件。

找到MySQL的配置文件 /etc/my.cnf，在 [mysqld] 下面添加：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

修改完之后重启MySQL服务：

systemctl restart mysqld

这样以后新建的数据库和表就默认都是utf8mb4字符集了。

五、修改已有数据库的字符集

如果你的数据库已经建好了，想改成utf8mb4，可以执行：

ALTER DATABASE testdb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

注意：这个只会修改数据库的默认字符集，已经建好的表的字符集不会变。

六、修改已有表的字符集

如果要修改某一张表的字符集，可以执行：

ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

这样整张表的所有字段都会改成utf8mb4字符集。

如果要批量修改整个数据库里所有表的字符集，可以生成一个SQL脚本：

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.tables
WHERE table_schema = 'testdb';

执行完之后会生成一堆ALTER TABLE语句，把这些语句复制出来执行就行。

七、连接的时候指定字符集

除了数据库和表的字符集，连接的时候也要指定字符集，不然还是可能会乱码。

用命令行连接的时候：

mysql -u root -p --default-character-set=utf8mb4

或者登录之后执行：

SET NAMES utf8mb4;

常见坑

坑1：用了utf8字符集，存emoji表情报错

很多同学建库的时候用的是utf8，结果存个emoji表情就报错了，或者存进去之后变成问号。这就是因为utf8最多只能存3个字节，emoji表情是4个字节的。

解决方法就是把字符集改成utf8mb4。

坑2：数据库字符集改了，但是表的字符集没改

很多同学改完数据库的字符集之后，以为就完事了，结果还是乱码。这是因为数据库的默认字符集改了，但是已经建好的表的字符集还是旧的。

一定要把表的字符集也一起改了，最好是整张表转成utf8mb4。

坑3：连接字符集不对，导致中文乱码

有时候数据库和表都是utf8mb4，但是查出来还是乱码。这一般是连接字符集不对。可以执行 SET NAMES utf8mb4 试一下，或者连接的时候加上 --default-character-set=utf8mb4 参数。

坑4：utf8mb4_general_ci和utf8mb4_unicode_ci选哪个

很多同学不知道这两个排序规则有什么区别。简单来说：

• utf8mb4_general_ci：速度快，但是对一些特殊字符的排序可能不太准确
• utf8mb4_unicode_ci：排序更准确，但是速度稍微慢一点

一般网站用utf8mb4_general_ci就够了，不用太纠结。

总结

MySQL字符集和utf8mb4的核心知识点：

1. MySQL的utf8不是真正的UTF-8，最多只能存3字节
2. utf8mb4才是真正的UTF-8，能存emoji表情，推荐使用
3. 新建数据库和表的时候直接用utf8mb4
4. 老的数据库如果要改，要同时改数据库和表的字符集
5. 连接的时候也要指定utf8mb4，不然还是会乱码

现在做新项目，直接全程用utf8mb4就对了，省得以后再改。

遇到问题加QQ23979811 协助处理

前言

做运维的同学经常会遇到这种情况：服务器磁盘突然满了，不知道是哪个数据库或者哪张表占了这么多空间。或者想知道自己的数据库现在有多大了，好提前规划扩容。

这时候就需要用SQL命令来查看数据库和表的大小了。很多新手同学不知道怎么查，只能傻乎乎的用du命令看整个data目录的大小，看不到具体是哪个数据库或哪张表占了空间。

这篇文章就把MySQL查看数据库大小和表大小的常用SQL命令都整理出来，以后遇到磁盘满了的问题就能快速定位了。

一、查看所有数据库的大小

如果你想知道MySQL里每个数据库分别占了多少空间，可以执行下面这个SQL：

SELECT 
    table_schema AS '数据库名',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

执行完之后会列出所有数据库的大小，单位是MB，从大到小排序。这样你一眼就能看出来哪个数据库最占空间了。

二、查看指定数据库的大小

如果你只想看某个数据库的大小，比如testdb数据库，可以执行：

SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '数据库大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'testdb';

把testdb换成你要查询的数据库名就行。

三、查看数据库里所有表的大小

知道了哪个数据库大，接下来就要看这个数据库里哪张表最大了。执行下面这个SQL：

SELECT 
    table_name AS '表名',
    table_rows AS '行数',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'testdb'
ORDER BY (data_length + index_length) DESC;

这样就能列出testdb数据库里所有表的大小，从大到小排序。你一眼就能看出来哪张表最占空间了。

四、查看单张表的大小

如果你只想看某一张表的大小，比如users表，可以执行：

SELECT 
    table_name AS '表名',
    table_rows AS '行数',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'testdb' 
AND table_name = 'users';

五、查看表的详细大小（数据+索引分开）

有时候你想知道一张表里数据占了多少，索引占了多少，方便优化。可以执行：

SELECT 
    table_name AS '表名',
    ROUND(data_length / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(index_length / 1024 / 1024, 2) AS '索引大小(MB)',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '总大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'testdb'
ORDER BY (data_length + index_length) DESC;

六、查看数据库里所有表的行数

有时候你只想知道每张表有多少行数据，不需要看大小。可以执行：

SELECT 
    table_name AS '表名',
    table_rows AS '行数'
FROM information_schema.tables
WHERE table_schema = 'testdb'
ORDER BY table_rows DESC;

七、查看MySQL整体占了多少磁盘空间

如果你想知道整个MySQL一共占了多少磁盘空间，可以执行：

SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'MySQL总大小(GB)'
FROM information_schema.tables;

常见坑

坑1：table_rows显示的行数不准确

很多同学发现，用 information_schema.tables 查出来的 table_rows 和实际 count(*) 出来的行数对不上。

这是因为 information_schema 里的 table_rows 是估算值，不是精确值。InnoDB引擎的话，这个值大概有10%左右的误差。如果需要精确的行数，还是得用 SELECT COUNT(*) FROM 表名。

坑2：查出来的大小和实际磁盘占用对不上

有时候你用SQL查出来的数据库大小，和用 du 命令查出来的 data 目录大小对不上，差了不少。

这是因为MySQL的data目录里除了表数据和索引，还有：

1. redo log、undo log这些日志文件
2. ibdata1这些系统表空间文件
3. 二进制日志文件

这些都不算在表数据和索引大小里。所以用SQL查出来的大小一般会比实际磁盘占用小一点。

坑3：查完之后发现有个库特别大，不知道是什么

有时候你查完发现有个库特别大，但是你根本不知道这个库是干嘛的。一般可能是：

1. mysql系统库
2. information_schema、performance_schema这些系统库
3. 之前测试的时候建的库，忘了删了

坑4：磁盘满了但是查出来数据库没多大

如果磁盘满了，但是查出来数据库没多大，那大概率不是数据库的问题，可能是：

1. 日志文件太大（比如nginx日志、MySQL的binlog日志）
2. 网站上传的文件太大
3. 系统临时文件太多

这时候需要去查一下具体是哪个目录占了空间。

总结

MySQL查看数据库和表大小的常用SQL命令总结一下：

1. 查看所有数据库大小：查 information_schema.tables，按 table_schema 分组
2. 查看指定数据库大小：加 WHERE table_schema = '数据库名'
3. 查看数据库里所有表的大小：按 table_name 分组
4. 查看单张表的大小：加 AND table_name = '表名'
5. 数据和索引分开看：data_length 和 index_length
6. 查看行数：table_rows 字段

记住这些SQL命令，以后遇到磁盘满了的问题，就能快速定位是哪个数据库、哪张表占了空间，不用再瞎找了。

遇到问题加QQ23979811 协助处理

前言

做运维或者开发的同学都知道，数据库是整个网站最核心的资产。一旦数据库出了问题，数据丢了，那损失可就大了。所以做好数据库备份是非常重要的一件事。

很多新手同学可能从来没做过数据库备份，等真的出问题了才后悔莫及。这篇文章就详细讲讲MySQL数据库怎么备份和恢复，包括常用的备份命令、恢复方法，还有自动定时备份的脚本。

一、备份方法

MySQL备份最常用的工具就是mysqldump，它是MySQL自带的备份工具，不需要额外安装。

1. 备份整个数据库

备份单个数据库是最常用的场景。比如你要把本地的testdb数据库备份成一个sql文件：

mysqldump -u root -p testdb > /root/testdb.sql

执行完之后输入MySQL的密码，就开始备份了。备份完之后会在/root/目录下生成一个testdb.sql文件。

2. 备份单张表

有时候你只需要备份数据库里的某一张表，比如只备份users表：

mysqldump -u root -p testdb users > /root/users.sql

3. 备份所有数据库

如果你想把MySQL里所有的数据库都备份下来，可以用 --all-databases 参数：

mysqldump -u root -p --all-databases > /root/all.sql

4. 只备份表结构，不备份数据

有时候你只需要备份表结构，不需要备份数据，这时候可以用 --no-data 参数：

mysqldump -u root -p --no-data testdb > /root/testdb_schema.sql

5. 备份的时候加锁保证数据一致性

备份大表的时候，为了保证备份过程中数据不被修改，可以加 --lock-tables 参数：

mysqldump -u root -p --lock-tables testdb > /root/testdb.sql

6. 备份的时候压缩文件

如果数据库很大，备份出来的sql文件也会很大，这时候可以用gzip压缩一下：

mysqldump -u root -p testdb | gzip > /root/testdb.sql.gz

二、恢复方法

备份完了，怎么恢复呢？

1. 恢复整个数据库

恢复整个数据库的命令：

mysql -u root -p testdb < /root/testdb.sql

注意：恢复之前一定要确保testdb这个数据库已经存在了。如果不存在，需要先创建数据库：

CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2. 恢复单张表

恢复单张表的话，先登录MySQL，然后选择对应的数据库，再用source命令恢复：

mysql -u root -p

USE testdb;
source /root/users.sql;

3. 恢复压缩的备份文件

如果你的备份文件是用gzip压缩过的，恢复的时候需要先解压：

gunzip < /root/testdb.sql.gz | mysql -u root -p testdb

三、自动定时备份脚本

手动备份太麻烦了，我们可以写个脚本，每天自动备份数据库。

备份脚本内容

创建一个备份脚本 /root/mysql_backup.sh：

#!/bin/bash

# 数据库配置
DB_USER="root"
DB_PASSWORD="你的MySQL密码"
DB_NAME="testdb"
BACKUP_DIR="/root/backup"

# 备份文件名，用日期命名
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/testdb_$DATE.sql"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u$DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE

# 压缩备份文件
gzip $BACKUP_FILE

# 删除7天前的备份文件
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "备份完成：$BACKUP_FILE.gz"

给脚本加执行权限

chmod +x /root/mysql_backup.sh

添加到定时任务

用crontab添加定时任务，每天凌晨3点自动备份：

crontab -e

添加下面这行：

0 3 * * * /root/mysql_backup.sh

这样每天凌晨3点就会自动备份数据库了，而且只保留最近7天的备份，旧的会自动删除。

常见坑

坑1：备份的时候忘了加密码，执行的时候卡住了

很多同学写备份命令的时候，直接写 mysqldump -u root -p testdb > xxx.sql，然后执行的时候就会停下来让你输入密码。如果是在脚本里用的话，就会卡住。

解决方法是把密码直接写在命令里：mysqldump -u root -p你的密码 testdb > xxx.sql。注意-p和密码之间没有空格。

坑2：恢复的时候数据库不存在

很多同学恢复的时候直接执行 mysql -u root -p testdb < xxx.sql，结果报错说数据库不存在。记住恢复之前一定要先创建好对应的数据库。

坑3：备份文件太大，恢复的时候超时了

大文件恢复的时候很容易超时，或者因为各种原因中断。这时候可以用source命令在MySQL内部恢复，会稳定很多。

坑4：备份文件存在本地，服务器挂了备份也没了

很多同学备份文件直接放在服务器本地，觉得这样就安全了。其实不对，如果服务器磁盘坏了，或者被黑客删了，备份文件也没了。

正确的做法是：

1. 每天备份完之后，把备份文件下载到本地电脑
2. 或者上传到云存储（比如阿里云OSS、腾讯云COS）
3. 重要的备份最好异地多存几份

总结

MySQL数据库备份和恢复的核心内容：

1. 备份命令：mysqldump，常用参数有 --all-databases、--no-data、--lock-tables
2. 恢复命令：mysql < 备份文件，或者用source命令
3. 自动备份：写个shell脚本，配合crontab定时执行
4. 备份文件一定要异地存储，不能只放在服务器本地

数据库备份是个大事，千万不要等出问题了才想起来备份。养成每天自动备份的好习惯，出问题的时候才能从容应对。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定遇到过这种情况：数据库里有一张几百万行甚至上千万行的大表，做分页查询的时候，第一页第二页查得很快，但是翻到第100页、第1000页的时候，查询就变得特别慢，甚至直接卡死。

这是为什么呢？怎么优化才能让大表分页查询不卡呢？这篇文章就详细讲讲MySQL大表分页越查越慢的原因，以及对应的优化方案。

为什么分页会越查越慢

先看一下最常见的分页查询写法：

SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

这个SQL的意思是：从 orders 表里，跳过100000条数据，取后面10条。

很多同学以为这个查询很快就能出来，其实不是的。MySQL执行这个SQL的时候，会先扫描前100010条数据，然后把前面的100000条扔掉，只返回最后10条。

也就是说，你翻到第1000页的时候，MySQL要先扫描100000多条数据，然后再扔掉。翻的页数越深，MySQL要扫描和扔掉的数据就越多，所以查询就会越来越慢。

优化方案

方案1：子查询优化（延迟关联）

这是最常用的优化方案。思路就是：先用索引把需要的id查出来，然后再根据id去查完整的数据。

原来的慢查询：

SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

优化后的写法：

SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 10
) t ON o.id = t.id;

这样优化的好处是：子查询里的 SELECT id FROM orders ORDER BY id LIMIT 100000, 10 只需要扫描索引就能完成，不需要回表查完整数据，速度会快很多。然后再根据查出来的10个id，去关联完整的表数据，只需要10次回表操作就行。

这种优化方式也叫"延迟关联"，是大表分页最常用的优化手段。

方案2：书签分页（记住上一页最后一条id）

如果你的业务场景允许（比如只允许"上一页"和"下一页"，不允许直接跳页），那用书签分页是最快的。

思路就是：记住上一页最后一条数据的id，下一页直接从这个id后面开始查。

第一页：

SELECT * FROM orders ORDER BY id LIMIT 10;

假设第一页最后一条数据的id是100。

第二页：

SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;

这样不管你翻到第几页，查询速度都是一样快的，因为每次都是从某个id开始往后查10条，MySQL不需要扫描前面的几十万条数据。

这种分页方式的缺点是不支持直接跳页，只能一页一页往后翻。很多信息流产品（比如朋友圈、抖音）都是用这种分页方式。

方案3：覆盖索引

如果你查询的字段刚好都在索引里，那MySQL就不需要回表查完整数据了，速度会快很多。

比如你只需要查 id, title, create_time 这几个字段，那你可以建一个联合索引：

CREATE INDEX idx_title_time ON orders(title, create_time);

然后查询的时候只查这几个字段：

SELECT id, title, create_time FROM orders ORDER BY create_time LIMIT 100000, 10;

这样MySQL直接从索引里就能拿到所有需要的数据，不需要回表，查询速度会快很多。

方案4：禁止跳页查询

如果你的业务场景允许，可以考虑禁止用户直接跳页。比如只允许用户看前100页，或者只允许"上一页"和"下一页"。

因为翻到第1000页、第10000页的用户其实很少，大部分用户都是看前几页。禁止跳页可以避免大量深分页查询拖慢数据库。

常见坑

坑1：用LIMIT offset的时候，offset越大越慢

很多同学不知道LIMIT offset的原理，以为MySQL直接就跳过了前面的offset条数据。其实不是的，MySQL是先把前面的offset条数据都查出来，然后再扔掉。所以offset越大，查询越慢。

坑2：优化了子查询，但是子查询里没有用到索引

如果你的ORDER BY字段没有建索引，那就算用了子查询优化，还是会很慢。一定要确保ORDER BY的字段有索引。

坑3：书签分页的时候，id不是连续的

书签分页的前提是你的id是连续的、递增的。如果你的业务场景里id可能会被删除，导致id不连续，那书签分页可能会漏数据。这时候需要根据实际情况调整分页策略。

坑4：用count(*)查总页数很慢

大表查总页数也很慢，比如 SELECT COUNT(*) FROM orders，几百万行的表可能要查好几秒。

这个优化方案是：

1. 如果业务不需要精确的总页数，可以用"大约XX万条"来代替
2. 或者单独建一张表存总条数，定期更新
3. 或者用explain估算一下大概的行数

总结

MySQL大表分页越查越慢的问题，核心原因就是LIMIT offset会先扫描offset条数据然后扔掉，offset越大越慢。

常用的优化方案：

1. 子查询优化（延迟关联）：先查id，再关联完整数据
2. 书签分页：记住上一页最后一条id，下一页从这个id开始查
3. 覆盖索引：只查索引里有的字段，避免回表
4. 禁止跳页：不允许直接翻到第1000页

根据你的业务场景选择合适的优化方案，大表分页查询慢的问题就能很好地解决了。

遇到问题加QQ23979811 协助处理

前言

做网站开发或者运维的同学肯定经常遇到这种情况：把本地写好的项目传到服务器上，需要把本地的数据库sql文件导入到服务器的MySQL里。但是导入的时候经常报错，各种问题层出不穷，折腾半天都导不进去。

这篇文章就把MySQL导入sql文件时最常见的报错和解决方法都整理出来，帮你快速搞定导入问题。

常用导入命令

首先先讲一下最常用的导入sql文件的命令：

mysql -u root -p 数据库名 < /路径/你的sql文件.sql

比如你要把 test.sql 导入到 testdb 数据库里，命令就是：

mysql -u root -p testdb < /root/test.sql

执行完之后输入密码，就开始导入了。

常见报错及解决方法

1. 报错：Unknown database 'xxx'

这个报错的意思是你要导入的数据库不存在。很多同学直接就执行导入命令了，结果数据库还没建，肯定报错。

解决方法：

先登录MySQL，创建对应的数据库：

CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

创建完数据库之后再执行导入命令。

2. 报错：File 'xxx.sql' not found

这个报错的意思是找不到sql文件。一般是因为你写的文件路径不对。

解决方法：

先确认一下sql文件的实际路径：

pwd
ls -l

确认文件确实在那个路径下，然后用绝对路径来导入，不要用相对路径。

3. 报错：Access denied for user 'root'@'localhost'

这个报错的意思是权限不足，没有权限往这个数据库里导入数据。

解决方法：

确认你登录MySQL用的用户有没有这个数据库的权限。如果是root用户，一般不会有这个问题。如果是其他用户，需要给用户授权：

GRANT ALL PRIVILEGES ON testdb.* TO '用户名'@'localhost';
FLUSH PRIVILEGES;

4. 报错：Packet larger than max_allowed_packet bytes

这个报错一般是因为你的sql文件太大了，超过了MySQL允许的最大数据包大小。

解决方法：

先查看当前的 max_allowed_packet 大小：

SHOW VARIABLES LIKE 'max_allowed_packet';

然后临时调大一点，比如调到100M：

SET GLOBAL max_allowed_packet = 104857600;

或者修改my.cnf配置文件，在 [mysqld] 下面加上：

[mysqld]
max_allowed_packet = 100M

然后重启MySQL服务再导入。

5. 报错：You have an error in your SQL syntax

这个报错的意思是SQL语法错误。一般是因为你的sql文件里有某些SQL语句在当前MySQL版本不兼容。

解决方法：

看看报错信息里说的是哪一行的语法错误，然后打开sql文件找到那一行，把不兼容的语法改一下。

比如从MySQL 8.0导出的sql文件导入到MySQL 5.7里，经常会有一些语法不兼容的问题，需要手动改一下。

6. 导入到一半就卡住了，或者超时了

如果你的sql文件特别大（几百M甚至几个G），导入的时候可能会卡住，或者因为超时而中断。

解决方法：

1. 先把 sql_mode 调整一下，去掉一些严格模式：

SET GLOBAL sql_mode = '';

2. 关闭自动提交，提升导入速度：

SET autocommit = 0;

3. 如果还是太慢，可以考虑用 source 命令导入：

USE testdb;
source /root/test.sql;

7. 中文乱码问题

导入完数据之后发现中文都变成乱码了。这一般是因为字符集不匹配。

解决方法：

导入之前先设置一下字符集：

SET NAMES utf8mb4;

然后再导入数据。或者导入的时候加上 --default-character-set 参数：

mysql -u root -p --default-character-set=utf8mb4 testdb < /root/test.sql

常见坑

坑1：忘记先创建数据库就直接导入

很多同学导入的时候直接就执行命令了，结果数据库还没建，肯定报错。一定要先确认数据库存在，再导入。

坑2：用相对路径导入找不到文件

很多同学写的是 mysql -u root -p testdb < test.sql，结果提示找不到文件。这是因为你当前所在的目录和sql文件所在的目录不一样，相对路径找不到。最好用绝对路径来导入。

坑3：导入完数据之后中文乱码

导入完数据发现中文都变成问号或者乱码了，这一般是字符集的问题。导入的时候一定要指定 utf8mb4 字符集，不然中文很容易乱码。

坑4：大文件导入到一半就中断了

大文件导入的时候很容易因为超时而中断，特别是几百M以上的sql文件。这时候可以调整一下超时时间，或者用 source 命令在MySQL内部导入，会稳定很多。

总结

MySQL导入sql文件的常见报错和解决方法总结一下：

1. 数据库不存在 → 先创建数据库
2. 文件找不到 → 用绝对路径
3. 权限不足 → 给用户授权
4. 数据包太大 → 调大 max_allowed_packet
5. 语法错误 → 检查SQL语句兼容性
6. 导入慢/卡住 → 关闭自动提交，用source命令
7. 中文乱码 → 指定utf8mb4字符集

记住这些常见问题和解决方法，以后导入sql文件的时候就不会再手忙脚乱了。

遇到问题加QQ23979811 协助处理

前言

做运维或者开发的同学肯定遇到过这种情况：服务器上的MySQL数据库用了很久，突然有一天忘记了root密码，登录不进去了。这时候项目可能正在运行，不能随便重装数据库，那怎么办呢？

别慌，MySQL提供了一种跳过权限验证的启动方式，我们可以用这种方式登录进去，然后重置root密码。这篇文章就详细讲讲在Linux环境下怎么重置MySQL的root密码。

操作步骤

1. 先停止MySQL服务

首先我们需要把正在运行的MySQL服务停掉：

systemctl stop mysqld

如果你用的是MariaDB，命令是：

systemctl stop mariadb

执行完之后可以检查一下MySQL是不是真的停了：

ps aux | grep mysql

如果没有看到mysql进程，说明已经停掉了。

2. 跳过权限验证启动MySQL

接下来我们要用 --skip-grant-tables 参数启动MySQL，这样启动之后就可以不用密码直接登录了。

mysqld_safe --skip-grant-tables &

注意最后面的 & 符号，这个是让MySQL在后台运行。

执行完这个命令之后，等个几秒钟，让MySQL完全启动起来。

3. 无密码登录MySQL

现在我们就可以不用密码直接登录MySQL了：

mysql -u root

执行完之后直接就进入MySQL命令行界面了，不需要输入密码。

4. 修改root密码

登录进去之后，我们就可以修改root密码了。

首先选择mysql数据库：

USE mysql;

然后修改root密码。注意不同版本的MySQL，修改密码的语句不一样：

MySQL 5.7版本：

UPDATE user SET authentication_string=PASSWORD('你的新密码') WHERE User='root';

MySQL 8.0版本：

ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码';

修改完密码之后，一定要执行一下刷新权限的命令：

FLUSH PRIVILEGES;

然后退出MySQL：

EXIT;

5. 关闭跳过权限的MySQL进程，正常重启MySQL

现在我们需要把刚才用跳过权限方式启动的MySQL进程关掉，然后用正常方式重启。

先找到MySQL的进程ID：

ps aux | grep mysqld_safe

然后杀掉这个进程：

kill -9 进程ID

等个几秒钟，让MySQL完全关闭。然后用正常方式启动MySQL：

systemctl start mysqld

6. 验证新密码能不能登录

最后我们来验证一下新密码能不能正常登录：

mysql -u root -p

输入你刚才设置的新密码，如果能正常登录进去，说明密码重置成功了。

常见坑

坑1：修改密码之后忘了执行FLUSH PRIVILEGES

很多同学修改完密码之后直接就退出了，结果发现重启MySQL之后密码还是不对。这是因为修改完密码之后一定要执行 FLUSH PRIVILEGES 刷新权限，不然修改不会生效。

坑2：MySQL版本不一样，修改密码的语句不一样

很多同学抄网上的教程，结果修改密码的语句不对，报错了。这是因为MySQL 5.7和MySQL 8.0修改密码的方式不一样。5.7用的是 authentication_string 字段，8.0用的是 ALTER USER 语句。

你可以先执行一下这个命令看看你的MySQL版本：

SELECT VERSION();

然后用对应的语句修改密码。

坑3：跳过权限启动的时候没加 & 符号

如果执行 mysqld_safe --skip-grant-tables 的时候没加 & 符号，那这个命令就会一直占着你的终端窗口，你没法继续操作了。所以一定要记得在最后面加个 & 符号，让它在后台运行。

坑4：杀进程的时候杀错了

杀进程的时候一定要看清楚，不要把其他无关的进程杀掉了。最好先 ps aux | grep mysqld 看一下，确认哪个是我们刚才启动的跳过权限的MySQL进程。

总结

Linux环境下重置MySQL root密码的步骤总结一下：

1. 停止MySQL服务
2. 用 --skip-grant-tables 参数跳过权限启动
3. 无密码登录MySQL
4. 修改root密码
5. 刷新权限
6. 关闭跳过权限的进程，正常重启MySQL
7. 用新密码验证登录

记住这几个步骤，以后忘记root密码的时候就不用慌了，按照这个流程操作就能搞定。

遇到问题加QQ23979811 协助处理

前言

做数据库开发的同学都知道，索引是提升查询性能最有效的手段。很多时候一张几百万行的大表，加个合适的索引查询速度就能从几秒变成几毫秒。

但是很多同学不知道的是，就算你加了索引，如果SQL语句写得不对，索引也可能会失效，结果还是全表扫描，查询慢得要命。这篇文章就把MySQL索引失效的常见场景都整理出来，帮你避免踩坑。

常见索引失效场景

1. 对索引列使用函数或运算

这是最常见的索引失效场景。如果你在索引列上用了函数或者做了运算，MySQL就没法用到这个索引了。

失效的写法：

-- 对索引列使用函数
SELECT * FROM users WHERE DATE(create_time) = '2026-10-05';

-- 对索引列做运算
SELECT * FROM orders WHERE amount + 100 > 500;

正确的写法：

-- 改成范围查询
SELECT * FROM users 
WHERE create_time >= '2026-10-05 00:00:00' 
AND create_time < '2026-10-06 00:00:00';

-- 把运算移到右边
SELECT * FROM orders WHERE amount > 400;

2. 不满足最左前缀原则

如果你建的是联合索引（比如 index(a, b, c)），查询的时候必须从最左边的列开始用，否则索引就会失效。

联合索引：index(name, age, city)

失效的写法：

-- 没有用到最左列name
SELECT * FROM users WHERE age = 25;

-- 跳过了中间的列
SELECT * FROM users WHERE name = '张三' AND city = '北京';

正确的写法：

-- 从最左列开始
SELECT * FROM users WHERE name = '张三';

-- 按顺序用
SELECT * FROM users WHERE name = '张三' AND age = 25;

3. 使用like以%开头

如果你的查询条件是 like '%xxx' 或者 like '%xxx%'，那索引就会失效，因为MySQL不知道从哪里开始找。

失效的写法：

-- %在前面
SELECT * FROM users WHERE name LIKE '%张%';

-- %在前后都有
SELECT * FROM users WHERE name LIKE '%张';

正确的写法：

-- %在后面，这样可以用到索引
SELECT * FROM users WHERE name LIKE '张%';

如果一定要用前后模糊匹配的话，可以考虑全文索引或者ES搜索引擎。

4. 索引列使用 != 或 <>

如果查询条件用了 != 或者 <>，MySQL优化器会觉得大部分数据都要查，还不如直接全表扫描，所以索引就失效了。

失效的写法：

SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE status <> 1;

正确的写法：

-- 改成in或者or
SELECT * FROM users WHERE status IN (0, 2, 3);

5. 使用or连接条件

如果or两边的列不是都有索引，那整个查询就会全表扫描。

失效的写法：

-- name有索引，但是age没有索引
SELECT * FROM users WHERE name = '张三' OR age = 25;

正确的写法：

-- 用union all代替or
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 25;

或者给age也加上索引，这样两边都能用索引。

6. 隐式类型转换

这是最坑的一个场景。如果你的索引列是字符串类型，但是查询的时候传了数字，MySQL会自动做类型转换，结果索引就失效了。

失效的写法：

-- 索引列phone是varchar类型，但是传了数字
SELECT * FROM users WHERE phone = 13800138000;

正确的写法：

-- 加引号，传字符串
SELECT * FROM users WHERE phone = '13800138000';

7. 使用not in、not exists

not in 和 not exists 也很容易导致索引失效。因为MySQL优化器对这类反查询的优化做得不太好。

失效的写法：

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

正确的写法：

-- 改成left join
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

8. 优化器认为全表扫描更快

还有一种情况是，虽然你写的SQL完全符合索引使用规则，但是MySQL优化器自己判断说"这张表数据太少了，全表扫描比走索引还快"，那它就会选择全表扫描。

这种情况是正常的，不用纠结。一般小表全表扫描确实更快。

如何检查索引是否生效

那怎么知道自己写的SQL有没有用到索引呢？很简单，用EXPLAIN关键字看执行计划就行。

EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看这几个字段：

• type：如果是 ALL 就是全表扫描，说明索引失效了；如果是 ref、eq_ref、const 就说明用到索引了
• key：这里显示实际用到的索引名，如果是 NULL 说明没用到索引
• rows：扫描的行数，越大说明效率越低

常见坑

坑1：加了索引但是没生效，以为索引没用

很多同学加了索引之后发现查询还是慢，就觉得索引没用。其实大概率是你的SQL写法有问题，导致索引失效了。一定要先用EXPLAIN看一下执行计划，确认索引有没有被用到。

坑2：联合索引建了不用最左列

很多同学建了联合索引，但是查询的时候没用到最左边的列，结果索引白建了。一定要记住最左前缀原则。

坑3：隐式类型转换导致索引失效

这个最坑，因为写SQL的时候完全看不出来。比如手机号、身份证号这些字符串类型的字段，查询的时候一定要加引号，不然索引就失效了。

总结

MySQL索引失效的常见场景总结一下：

1. 对索引列使用函数或运算
2. 不满足最左前缀原则
3. like以%开头
4. 使用 != 或 <>
5. 使用or连接条件
6. 隐式类型转换
7. 使用not in、not exists
8. 表太小，优化器选择全表扫描

写SQL的时候一定要注意避开这些坑，写完之后用EXPLAIN检查一下索引有没有生效。这样你的数据库查询性能才能真正提上去。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定遇到过这种情况：网站打开越来越慢，数据库CPU占用很高，但就是不知道哪条SQL语句拖慢了整个系统。这时候就需要用到MySQL的慢查询日志功能，把执行慢的SQL语句记录下来，然后逐个优化。

这篇文章就详细讲讲如何开启MySQL慢查询日志，以及怎么用它来定位和优化慢SQL语句。

操作步骤

1. 先查看当前慢查询日志的状态

登录MySQL之后，先看看慢查询日志有没有开启：

SHOW VARIABLES LIKE '%slow_query_log%';

执行完之后会看到类似下面的输出：

+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /var/lib/mysql/izuf6w7x1x2x3x4x5x6x-slow.log  |
+---------------------+-----------------------------------------------+

从这里可以看到，slow_query_log 是 OFF 状态，说明慢查询日志没有开启。

2. 查看当前慢查询时间阈值

慢查询的默认时间阈值是10秒，也就是说执行时间超过10秒的SQL才会被记录。这个阈值对生产环境来说太大了，一般我们会改成1秒或者2秒。

查看当前阈值：

SHOW VARIABLES LIKE 'long_query_time';

3. 临时开启慢查询日志（重启后失效）

如果只是想临时排查问题，可以直接在MySQL命令行里开启，不需要重启服务：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值为1秒
SET GLOBAL long_query_time = 1;

这样设置之后，执行时间超过1秒的SQL语句就会被记录到慢查询日志文件里了。

4. 永久开启慢查询日志（修改配置文件）

临时开启的方式在MySQL重启之后就会失效，如果想长期开启，需要修改MySQL的配置文件。

找到MySQL的配置文件，一般是 /etc/my.cnf 或者 /etc/mysql/my.cnf，在 [mysqld] 下面添加以下配置：

[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志文件位置
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询阈值，单位秒
long_query_time = 1
# 没有用到索引的SQL也记录下来
log_queries_not_using_indexes = 1

修改完配置文件之后，重启MySQL服务：

systemctl restart mysqld

5. 查看慢查询日志内容

慢查询日志开启之后，执行慢的SQL语句就会被记录到日志文件里。我们可以直接查看日志文件：

cat /var/log/mysql/slow.log

不过慢查询日志的格式比较复杂，直接看日志文件不太直观。我们可以用 mysqldumpslow 工具来分析：

# 查看最慢的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 查看访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

6. 使用EXPLAIN分析慢SQL

找到慢SQL之后，下一步就是分析这条SQL为什么慢。我们可以用 EXPLAIN 关键字来查看SQL的执行计划：

EXPLAIN SELECT * FROM users WHERE name = '张三';

执行完之后会输出很多字段，我们重点关注这几个：

• type：访问类型，最好是 const、eq_ref，最差是 ALL（全表扫描）
• key：实际用到的索引，如果是 NULL 说明没用到索引
• rows：扫描的行数，这个值越大越慢
• Extra：额外信息，如果看到 Using filesort 或者 Using temporary 就说明需要优化了

常见坑

坑1：开启了慢查询但日志文件里什么都没有

很多同学开启了慢查询日志，但是去看日志文件却发现什么都没有。这通常是因为以下几个原因：

1. 慢查询时间阈值设置得太大了，你的SQL执行时间还没超过阈值
2. 日志文件的路径不对，你看的不是正确的日志文件
3. MySQL没有权限写入日志文件，需要检查文件权限

坑2：long_query_time设置了但不生效

有时候你设置了 long_query_time = 1，但是发现执行时间0.5秒的SQL也被记录下来了。这是因为当前已经建立的MySQL连接还是用的旧的阈值，需要重新连接MySQL才能生效。

坑3：慢查询日志文件太大占满磁盘

如果你的网站访问量很大，慢查询日志可能会增长得非常快，几天就把磁盘占满了。所以一定要配置日志切割，或者定期清理慢查询日志文件。

总结

MySQL慢查询日志是定位数据库性能问题的必备工具，核心就三步：

1. 开启慢查询日志，设置合理的时间阈值（一般1秒）
2. 用mysqldumpslow工具分析慢查询日志，找到最慢的SQL
3. 用EXPLAIN分析慢SQL的执行计划，针对性优化（加索引、改写SQL等）

掌握了慢查询日志的使用方法，网站数据库性能问题就能快速定位和解决了。

遇到问题加QQ23979811 协助处理

MySQL 索引优化技巧

索引是数据库性能的关键，加对了查询快十倍，加错了反而更慢。

1. 索引类型

主键索引：主键自带索引，一张表只能一个。

普通索引：最基本的索引，加速查询。

CREATE INDEX idx_name ON user(name);

唯一索引：字段值不能重复。

CREATE UNIQUE INDEX idx_email ON user(email);

联合索引：多个字段组合的索引。

CREATE INDEX idx_name_age ON user(name, age);

2. 索引失效的情况

1）like 以 % 开头

-- 索引失效
SELECT * FROM user WHERE name LIKE '%张';

-- 索引生效
SELECT * FROM user WHERE name LIKE '张%';

2）对字段做函数运算

-- 索引失效
SELECT * FROM user WHERE LEFT(name,1) = '张';

-- 索引生效
SELECT * FROM user WHERE name LIKE '张%';

3）联合索引不满足最左前缀

联合索引 (name, age)：

-- 生效
SELECT * FROM user WHERE name = '张三';

-- 生效
SELECT * FROM user WHERE name = '张三' AND age = 25;

-- 失效
SELECT * FROM user WHERE age = 25;

4）隐式类型转换

-- phone 是 varchar
-- 索引失效
SELECT * FROM user WHERE phone = 13800001111;

-- 索引生效
SELECT * FROM user WHERE phone = '13800001111';

3. 查看索引使用情况

-- 查看执行计划
EXPLAIN SELECT * FROM user WHERE name = '张三';

-- 重点看 type 字段：
-- system > const > eq_ref > ref > range > index > ALL
-- 最差是 ALL（全表扫描）

4. 索引优化原则

• 为高频查询加索引：不要乱加
• 区分度高的字段优先：性别这种区分度低的不加
• 联合索引把最常用的放左边
• 索引不是越多越好：增删改会变慢
• 避免索引冗余：(a,b) 包含了 (a)

5. 慢查询排查

-- 开启慢查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log_file';

总结

MySQL 索引优化核心：

• 联合索引满足最左前缀
• like 不要以 % 开头
• 不要对字段做函数运算
• 用 EXPLAIN 看执行计划
• 慢查询找出来再优化

搞懂这些，数据库性能提升一大截。

MySQL 事务隔离级别详解

事务是数据库并发控制的基础，搞懂四个隔离级别，才能解决脏读、幻读问题。

1. 事务 ACID 特性

• 原子性（Atomicity）：要么全成功，要么全回滚
• 一致性（Consistency）：事务前后数据一致
• 隔离性（Isolation）：事务之间互不干扰
• 持久性（Durability）：提交后永久保存

2. 四种隔离级别

MySQL 有四种隔离级别，从上到下越来越严格：

读未提交（READ UNCOMMITTED）

• 可以读到别人还没提交的数据
• 会出现脏读
• 几乎没人用

读提交（READ COMMITTED）

• 只能读到别人已经提交的数据
• 解决了脏读，但会出现不可重复读
• Oracle、SQL Server 默认级别

可重复读（REPEATABLE READ）

• 同一个事务里，读多次结果都一样
• 解决了脏读、不可重复读
• MySQL InnoDB 默认级别
• InnoDB 用间隙锁解决了幻读

串行化（SERIALIZABLE）

• 事务一个一个排队执行
• 最安全，但性能最差
• 几乎没人用

3. 三种读问题

脏读：读到了别人还没提交的数据，别人回滚了，你读到的就是脏数据。

不可重复读：同一个事务里，两次读同一行数据，结果不一样（别人中间改了并提交了）。

幻读：同一个事务里，两次查同一个范围，行数不一样（别人中间插了新行）。

4. 查看和设置隔离级别

-- 查看当前隔离级别
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;

-- 设置全局隔离级别
SET GLOBAL tx_isolation = 'READ-COMMITTED';

-- 设置当前会话隔离级别
SET SESSION tx_isolation = 'READ-COMMITTED';

5. 实际应用建议

• 默认就好：MySQL 默认的可重复读已经够用
• 高并发场景：可以降到读提交，提升并发性能
• 金融级场景：用串行化，绝对安全
• 大多数业务：默认级别就行，不用折腾

6. 代码示例

// PDO 事务示例
try {
    $pdo->beginTransaction();

    $pdo->exec("UPDATE account SET money = money - 100 WHERE id = 1");
    $pdo->exec("UPDATE account SET money = money + 100 WHERE id = 2");

    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "转账失败：" . $e->getMessage();
}

总结

MySQL 事务隔离级别核心：

• 读未提交：脏读
• 读提交：不可重复读
• 可重复读：默认级别，解决幻读
• 串行化：最安全最慢

大多数业务用默认的可重复读就够了。

MySQL 锁机制详解

MySQL 锁是并发控制的核心，搞懂锁，才能避免死锁和性能问题。

1. 表锁 vs 行锁

表锁：锁整个表，开销小，并发低。

• MyISAM 用的就是表锁
• 适合读多写少的场景

行锁：锁单行数据，开销大，并发高。

• InnoDB 用的就是行锁
• 适合写多的场景

2. 共享锁 vs 排他锁

共享锁（S锁）：读锁，多个读不互斥。

SELECT ... LOCK IN SHARE MODE

排他锁（X锁）：写锁，一个写独占。

SELECT ... FOR UPDATE

3. 间隙锁

InnoDB 用间隙锁防止幻读。锁的是一个范围，不是具体行。

比如你查 id > 10 AND id < 20，间隙锁会把这个区间锁住，别人插不进来。

4. 死锁

两个事务互相等对方的锁，都不放，就死锁了。

避免死锁的方法：

• 按相同顺序访问表
• 事务尽量小，快速提交
• 降低隔离级别
• 加索引，减少行锁范围

5. 查看锁状态

-- 查看当前锁
SHOW OPEN TABLES WHERE In_use > 0;

-- 查看 InnoDB 锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- 查看锁等待
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

6. 行锁什么时候会变成表锁

行锁失效就会升级成表锁：

• 没用索引
• 索引失效
• 全表扫描

所以加索引不仅是为了查得快，也是为了锁得准。

总结

MySQL 锁的核心：

• InnoDB 默认行锁，MyISAM 是表锁
• 共享锁读、排他锁写
• 间隙锁防幻读
• 死锁要避免：顺序访问、小事务、加索引

搞懂这些，数据库并发问题基本能解决一半。