MySQL自增ID重置

引言

测试表删完数据后，下一条插入的自增ID还接着上次的数往上跳，演示或本地开发时想让ID从1重新开始，就要重置AUTO_INCREMENT。DELETE清空不会归零，这是新手最容易踩的坑。

DELETE不会重置自增

DELETE FROM users;
INSERT INTO users(name) VALUES('张三');
-- 新行ID仍然接着旧最大值递增，不从1开始

DELETE只删行，自增计数器保留在内存和表结构里，不受影响。

TRUNCATE直接归零

TRUNCATE TABLE users;

TRUNCATE相当于重建空表，自增计数清零、表结构保留，速度远快于全表DELETE，但会清空全部数据且不可回滚，谨慎使用。

只改起始值不清数据

表里还有数据时，把自增起点设为当前最大值+1：

ALTER TABLE users AUTO_INCREMENT = 1;

若表内已有大于1的数据，MySQL会自动取max(id)+1作为真实起点，不会产生主键冲突。

查看当前自增值

SHOW TABLE STATUS LIKE 'users';
-- 或
SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_NAME='users';

注意事项

• 生产环境不要随意TRUNCATE，误删数据无法恢复；
• 自增ID一般不连续，删行出现空洞属正常，不必追求从1排列；
• 用DELETE后想重置，单独ALTER设置AUTO_INCREMENT；
• InnoDB重启后自增值从max(id)重算，与MyISAM行为不同；
• 若ID被其他表外键引用，重置前先确认依赖。

总结

DELETE不清零自增，TRUNCATE才归零重建表；保留数据用ALTER TABLE ... AUTO_INCREMENT=1设起点，查当前值用SHOW TABLE STATUS。开发演示可重置，生产环境优先只调起点、避免TRUNCATE误删。

MySQL慢查询定位

引言

网站访问缓慢很多根源是执行时间过长的SQL。开启MySQL慢查询日志，可以捕获执行超时SQL，找到需要优化的语句，是数据库性能排查的核心手段。

慢查询基础配置

慢查询日志会记录执行时长超过指定阈值的SQL语句。

-- 查看慢查询是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 设置阈值，单位秒，超过1秒就记录
SET GLOBAL long_query_time =1;

常用分析工具

1. mysqldumpslow：mysql自带工具，统计慢日志

   mysqldumpslow -s t /var/lib/mysql/slow.log

2. pt-query-digest：percona工具，分析效果更强，适合大量日志。

优化排查流程

1. 开启慢查询，收集慢SQL；
2. 使用explain查看执行计划，判断是否缺少索引；
3. 优化where条件，添加合适索引，改写SQL逻辑；
4. 优化完成后，持续观察慢日志确认效果。

示例

EXPLAIN SELECT * FROM article WHERE title like '%PHP%';

看type字段，ALL代表全表扫描，是需要优化的信号。

总结

慢查询日志用来抓取低效SQL，但是开启会少量增加服务器IO压力，生产环境可以按需开启。定位到慢SQL后优先检查索引，再调整SQL写法，解决数据库卡顿问题。

前言

明明加了索引，但查询还是慢？explain一看type是ALL，索引根本没生效！本文总结10种索引失效的场景，帮你避坑。

一、对索引字段用函数

失效写法

SELECT * FROM users WHERE LEFT(name, 3) = '张三';
SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';

正确写法

SELECT * FROM users WHERE name LIKE '张三%';
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

原因： 对索引字段用函数，MySQL不知道索引值会变成什么，索引用不上。

二、索引字段用运算

失效写法

SELECT * FROM users WHERE age + 1 = 20;
SELECT * FROM orders WHERE price * 2 > 100;

正确写法

SELECT * FROM users WHERE age = 19;
SELECT * FROM orders WHERE price > 50;

原因： 运算后索引值变了，MySQL没法用索引。

三、最左前缀原则不满足

联合索引

INDEX idx_name_age_city(name, age, city)

失效写法

SELECT * FROM users WHERE age = 20; -- 跳过了name
SELECT * FROM users WHERE city = '北京'; -- 跳过了name和age

正确写法

SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 20;

原则： 联合索引要从最左边的字段开始用，不能跳过。

四、like以%开头

失效写法

SELECT * FROM users WHERE name LIKE '%张三%';
SELECT * FROM users WHERE name LIKE '%张三';

正确写法

SELECT * FROM users WHERE name LIKE '张三%';

原因： 以%开头，MySQL不知道从哪开始找，索引用不上。

五、类型不匹配

失效写法

-- phone是varchar类型
SELECT * FROM users WHERE phone = 13800138000; -- 传了数字

正确写法

SELECT * FROM users WHERE phone = '13800138000'; -- 传字符串

原因： 类型不匹配，MySQL会做隐式转换，索引用不上。

六、OR两边有没索引的字段

失效写法

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

原因： OR只要有一边没索引，MySQL就干脆全表扫描。

解决

-- age也加索引
ALTER TABLE users ADD INDEX idx_age(age);

七、使用NOT、!=、<>

失效写法

SELECT * FROM users WHERE name != '张三';
SELECT * FROM users WHERE age NOT IN (18, 20);

原因： 不等于操作，MySQL觉得要查大部分数据，不如全表扫描。

解决

-- 改成范围查询
SELECT * FROM users WHERE name > '张三';

八、is null / is not null

这个要看情况，有的版本会失效，有的不会。

建议： 尽量让字段不要为null，设默认值。

九、查询条件用了函数或表达式

失效写法

SELECT * FROM users WHERE ABS(age) = 20;
SELECT * FROM users WHERE age / 2 = 10;

正确写法

SELECT * FROM users WHERE age = 20;

十、MySQL自己判断不走索引

有时候SQL写法没问题，但MySQL优化器觉得走索引还不如全表扫描。

比如： 表数据很少，或者要查的数据占表的大部分。

解决： 用FORCE INDEX强制走索引

SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

十一、怎么检查索引有没有生效

用explain

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

看key字段：

• key有值：用了索引
• key是NULL：没用到索引

看type字段：

• type=ALL：全表扫描
• type=ref/range：用了索引

十二、常见坑

坑1：加了索引但SQL写法不对

解决： 按上面10种场景检查。

坑2：索引加太多

索引不是越多越好，写的时候要维护索引，会变慢。

建议： 单表索引不超过5个。

坑3：区分度低的字段不加索引

比如gender（男/女），加索引没用。

原则： 区分度高的字段加索引。

坑4：隐式类型转换

例子： varchar字段传了数字。

解决： 传参时类型要匹配。

十三、索引使用原则

1. 对WHERE条件的字段加索引
2. 对ORDER BY的字段加索引
3. 联合索引遵守最左前缀
4. 不要对索引字段用函数
5. 不要对索引字段做运算
6. like不要以%开头
7. 类型要匹配
8. 区分度高的字段加索引

总结

10种索引失效场景：

1. 对索引字段用函数
2. 索引字段用运算
3. 最左前缀原则不满足
4. like以%开头
5. 类型不匹配
6. OR两边有没索引的字段
7. 使用NOT、!=、<>
8. is null / is not null
9. 查询条件用了表达式
10. MySQL自己判断不走索引

记住：

• 用explain检查索引有没有生效
• key=NULL就是没用到索引
• type=ALL就是全表扫描
• 避开上面10种场景

遇到问题加QQ23979811 协助处理

Redis 缓存穿透解决

缓存穿透是常见问题，恶意请求能把数据库打挂。

1. 什么是缓存穿透

现象： 请求的数据在缓存和数据库中都不存在，每次请求都直接打到数据库。

危害：

• 恶意攻击时数据库压力大
• 正常请求被拖慢
• 数据库CPU飙升

2. 为什么会穿透

• 查询一个不存在的ID
• 缓存没有命中
• 数据库也查不到
• 请求直接穿透到数据库

3. 解决方案一：缓存空值

思路： 即使数据库查不到，也在Redis存一个空值，设置短过期时间。

function getUser($id) {
    $key = "user:$id";
    $user = $redis->get($key);

    if ($user !== false) {
        // 空值标记
        if ($user === 'null') {
            return null;
        }
        return json_decode($user, true);
    }

    // 查数据库
    $user = $db->query("SELECT * FROM users WHERE id = $id");

    if ($user) {
        // 存在就缓存1小时
        $redis->setex($key, 3600, json_encode($user));
    } else {
        // 不存在也缓存，5分钟过期
        $redis->setex($key, 300, 'null');
    }

    return $user;
}

优点： 简单有效
缺点： 占内存，可能被恶意key刷爆

4. 解决方案二：布隆过滤器

思路： 在缓存前面加一层布隆过滤器，不存在的key直接拦截。

原理：

• 所有存在的数据先存到布隆过滤器
• 请求先过布隆过滤器
• 不存在直接返回，不查缓存和数据库

PHP 示例（用 Redis 的布隆过滤器模块）：

# 加载 RedisBloom 模块
redis-cli BF.ADD user:1
redis-cli BF.EXISTS user:1

function getUser($id) {
    $key = "user:$id";

    // 先过布隆过滤器
    if (!$redis->rawCommand('BF.EXISTS', 'user_filter', $id)) {
        return null; // 一定不存在
    }

    // 布隆说可能存在，再查缓存
    $user = $redis->get($key);
    if ($user) {
        return json_decode($user, true);
    }

    // 查数据库...
}

优点： 内存占用小，效率高
缺点： 有误判率，实现复杂

5. 解决方案三：参数校验

思路： 从源头过滤非法请求。

function getUser($id) {
    // 参数校验
    if ($id <= 0 || !is_numeric($id)) {
        return null;
    }

    // 正常查询逻辑...
}

适用： ID类查询，不合法直接拦截

6. 三种方案对比

7. 生产建议

1）组合使用

• 先做参数校验
• 再缓存空值
• 大流量用布隆过滤器

2）空值过期时间要短

• 5分钟到10分钟
• 不要太长

3）监控异常请求

• 发现大量404要警惕
• 及时加防护

4）接口限流

• 用 Nginx 或网关限流
• 防止恶意刷

8. 缓存雪崩和击穿

缓存穿透： 查不存在的数据
缓存击穿： 热点key过期，大量请求打数据库
缓存雪崩： 大量key同时过期

区分清楚： 三个问题现象类似但原因不同，解决方案也不同。

总结

Redis 缓存穿透解决要点：

• 查不存在的数据穿透到数据库
• 缓存空值是最简单方案
• 布隆过滤器适合大数据量
• 参数校验从源头拦截
• 空值过期时间要短
• 生产建议组合使用
• 注意和雪崩、击穿区分开
• 监控异常请求
• 接口加限流

缓存穿透是面试高频题，也是生产常见问题，一定要掌握。

MySQL 索引优化技巧

索引是提升查询性能的关键，但用不好反而拖慢速度。

1. 为什么要建索引

• 没有索引要全表扫描
• 数据量大时查询很慢
• 索引相当于书的目录
• 合适的索引能提升百倍速度

2. 索引类型

主键索引：

• 主键自动创建
• 唯一且非空

普通索引：

• 最基本的索引
• 加速查询

唯一索引：

• 值不能重复
• 比如手机号

联合索引：

• 多个字段组成
• 遵循最左前缀原则

3. 建索引原则

1）最频繁查询的字段建索引

-- 经常按name查
CREATE INDEX idx_name ON users(name);

2）区分度高的字段建索引

• 性别这种区分度低的不要建
• 手机号、身份证这种适合

3）联合索引注意顺序

-- 联合索引 (a, b, c)
-- 最左前缀：
-- a 能用
-- a,b 能用
-- a,b,c 能用
-- 单独b,c 用不了

4. 索引失效情况

1）where 里对索引字段用函数

-- 失效
SELECT * FROM users WHERE DATE(create_time) = '2026-01-01';

-- 有效
SELECT * FROM users WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02';

2）where 里对索引字段做运算

-- 失效
SELECT * FROM users WHERE age + 1 = 20;

-- 有效
SELECT * FROM users WHERE age = 19;

3）like 以 % 开头

-- 失效
SELECT * FROM users WHERE name LIKE '%张';

-- 有效
SELECT * FROM users WHERE name LIKE '张%';

4）隐式类型转换

-- phone是字符串
-- 失效（数字转字符串）
SELECT * FROM users WHERE phone = 13800138000;

-- 有效
SELECT * FROM users WHERE phone = '13800138000';

5. EXPLAIN 查看执行计划

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

重点看：

• type： 访问类型，至少要 range
• key： 实际用到的索引
• rows： 扫描行数
• Extra： 额外信息

6. 索引不要建太多

• 每个索引都占空间
• 插入更新时要维护索引
• 不是越多越好
• 一张表索引建议不超过5个

7. 覆盖索引

避免回表：

-- 索引包含查询的所有字段
SELECT name, age FROM users WHERE name = '张三';

-- 索引 (name, age) 就是覆盖索引
-- 不用回表查主键

Extra 显示 Using index 就是覆盖索引。

8. 慢查询定位

-- 开启慢查询
slow_query_log = 1
long_query_time = 1  -- 超过1秒记录

-- 查看慢查询日志
-- 找到慢SQL，加索引优化

9. 常见优化场景

1）分页查询优化

-- 慢（深分页）
SELECT * FROM orders LIMIT 100000, 10;

-- 快（子查询）
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders LIMIT 100000, 1)
LIMIT 10;

2）JOIN 字段建索引

• JOIN 的两边都要建索引
• 类型要一致

3）ORDER BY 用索引

• 排序字段建索引
• 避免 filesort

10. 索引维护

查看索引：

SHOW INDEX FROM users;

删除多余索引：

DROP INDEX idx_name ON users;

分析表：

ANALYZE TABLE users;

总结

MySQL 索引优化要点：

• 区分度高的字段建索引
• 联合索引遵循最左前缀
• 避免索引失效（函数、运算、like%）
• 用 EXPLAIN 分析执行计划
• 索引不要建太多
• 覆盖索引避免回表
• 慢查询日志找问题SQL
• 深分页要优化
• JOIN 两边都建索引
• 定期维护索引

索引优化是MySQL性能优化的核心，一定要多实践。

导入sql文件的时候各种报错？下面整理了最常见的导入报错和解决办法，照着排查就行。

1. 先看最常见的导入命令

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

或者进入MySQL后用source：

mysql -u root -p
USE 数据库名;
source /path/to/你的文件.sql;

2. 报错1：Unknown database

原因： 数据库不存在，你直接导入了。

解决： 先创建数据库再导入：

CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8mb4;

然后再执行导入命令。

3. 报错2：Access denied

原因： 权限不够，或者密码错了。

解决： 确认用户名密码对不对，给用户授权：

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'localhost' IDENTIFIED BY '密码';
FLUSH PRIVILEGES;

4. 报错3：File not found

原因： 文件路径不对，或者文件没权限读。

解决：

• 用绝对路径，不要用相对路径
• 确认文件存在：ls -l /path/to/file.sql
• 给文件加读权限：chmod 644 file.sql

5. 报错4：Duplicate entry

原因： 导入的表已经有数据了，主键冲突。

解决：

• 先清空表再导入：TRUNCATE TABLE 表名;
• 或者导入SQL文件开头加上 DROP TABLE IF EXISTS 表名;

6. 报错5：Unknown collation

原因： 字符集排序规则不一致，比如导出用的是 utf8mb4_general_ci，导入的数据库是 utf8_general_ci。

解决： 导入前设置字符集：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- 导入你的SQL
SET FOREIGN_KEY_CHECKS = 1;

7. 报错6：MySQL server has gone away

原因： SQL文件太大，导入超时了。

解决： 修改MySQL配置：

# /etc/my.cnf
max_allowed_packet = 64M
wait_timeout = 300
interactive_timeout = 300

然后重启MySQL：

systemctl restart mysqld

8. 报错7：Syntax error

原因： SQL文件格式不对，或者MySQL版本不兼容。

解决：

• 导出的时候选择兼容版本
• 去掉注释行（-- 开头的）
• 用文本编辑器打开检查一下有没有乱码

9. 大文件导入更快的方法

如果SQL文件几百MB，用mysql命令行比phpMyAdmin快10倍：

mysql -u root -p 数据库名 < big_file.sql

还可以开事务加速：

SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- 导入完成后
SET autocommit = 1;
SET unique_checks = 1;
SET foreign_key_checks = 1;
COMMIT;

遇到问题加QQ23979811 协助处理

MySQL 主键和自增

主键是表的重要概念，搞懂怎么用很有必要。

1. 什么是主键

主键（Primary Key）是表中唯一标识一行记录的字段。

特点：

• 唯一性： 每行数据主键不能重复
• 非空性： 主键不能为 NULL
• 一张表只能有一个主键

2. 创建主键

建表时指定

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

自增主键（最常用）

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

3. 自增主键特点

• 自动增长： 插入数据不用指定ID，自动+1
• 从1开始： 默认从1开始
• 不重复： 保证唯一

-- 插入数据不用写id
INSERT INTO users (name) VALUES ('张三');

-- id自动变成1
-- 再插入自动变成2

4. 查看自增值

-- 查看表当前自增值
SHOW TABLE STATUS LIKE 'users';

-- 查看建表语句
SHOW CREATE TABLE users;

5. 修改自增值

-- 设置下一次自增值为100
ALTER TABLE users AUTO_INCREMENT = 100;

6. 联合主键

多个字段组成主键：

CREATE TABLE user_role (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id)
);

7. 主键设计建议

1）用自增整数做主键

• INT 或 BIGINT
• 查询快，占用空间小
• 不要用 UUID 做主键

2）为什么不用字符串做主键

• 占空间大
• 查询慢
• 自增效率高

3）什么时候用联合主键

• 中间表（如用户角色关联表）
• 没有合适的单字段做主键

4）BIGINT 还是 INT

• 一般 INT 够用（约21亿）
• 数据量大用 BIGINT

8. 常见问题

1）删除数据后ID不连续

• 正常现象，自增只增不减
• 不要依赖ID连续

2）自增值跳号

• 删除了中间数据
• 插入失败也会占用ID
• 正常现象

3）为什么不用手机号做主键

• 可能会变
• 字符串查询慢
• 用自增ID更稳妥

总结

MySQL 主键和自增要点：

• 主键唯一且非空
• 一张表只能一个主键
• 自增主键最常用
• 用 INT 或 BIGINT
• 不要用 UUID 做主键
• 中间表可以用联合主键
• ID不连续是正常现象

主键设计对表性能影响很大，一定要重视。

MySQL 常用数据类型

建表选对数据类型很重要，这些常用类型要记住。

1. 数值类型

整数类型

• TINYINT： 1字节，范围 -128~127
• SMALLINT： 2字节，范围 -32768~32767
• INT： 4字节，常用，范围约 ±21亿
• BIGINT： 8字节，大数值

小数类型

• FLOAT： 单精度浮点数
• DOUBLE： 双精度浮点数
• DECIMAL： 精确小数，存金额用这个

-- 金额用 DECIMAL(总位数, 小数位数)
price DECIMAL(10,2)  -- 最多10位，2位小数

2. 字符串类型

CHAR： 定长字符串，比如 CHAR(10) 存不满补空格

• 适合固定长度，比如手机号、性别

VARCHAR： 变长字符串，最常用

• VARCHAR(255)，存多少占多少
• 适合变长内容，比如用户名、标题

TEXT： 长文本

• TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
• 存文章内容、备注等

常用选择：

• 短文本：VARCHAR(50)
• 长文本：TEXT
• 固定长度：CHAR

3. 日期时间类型

DATE： 日期，格式 YYYY-MM-DD
TIME： 时间，格式 HH:MM:SS
DATETIME： 日期+时间，范围 1000-9999年
TIMESTAMP： 时间戳，范围 1970-2038年，自动更新

create_time DATETIME DEFAULT CURRENT_TIMESTAMP
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

4. 常用字段设计示例

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    age TINYINT UNSIGNED COMMENT '年龄',
    phone CHAR(11) COMMENT '手机号',
    money DECIMAL(10,2) DEFAULT 0 COMMENT '余额',
    status TINYINT DEFAULT 1 COMMENT '状态：1正常 0禁用',
    content TEXT COMMENT '简介',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5. 选择建议

1）主键ID

• 用 INT 或 BIGINT
• 不要用 UUID 做主键

2）金额

• 用 DECIMAL，不要用 FLOAT/DOUBLE
• 避免精度丢失

3）状态字段

• 用 TINYINT，不要用 VARCHAR 存 "正常/禁用"

4）时间

• 创建时间用 DATETIME
• 更新时间用 TIMESTAMP 自动更新

5）字符串

• 短文本用 VARCHAR
• 长文本用 TEXT
• 固定长度用 CHAR

6. 注意事项

• 不要过度设计： 够用就行，不要字段太大
• 能用数字不用字符串： 排序、计算都快
• 避免 NULL： 尽量设默认值，NULL 不好查询
• 加注释： 每个字段都加 COMMENT

总结

MySQL 常用数据类型要点：

• INT/BIGINT：整数
• DECIMAL：金额
• VARCHAR：字符串
• TEXT：长文本
• DATETIME/TIMESTAMP：时间
• 主键用 INT 自增
• 金额用 DECIMAL
• 状态用 TINYINT
• 字段加注释

建表选对类型，性能和存储都好。

PHP PDO 连接数据库

PDO 是 PHP 推荐的数据库连接方式，支持多种数据库，防注入。

1. 什么是 PDO

PDO（PHP Data Objects）是 PHP 数据库抽象层，统一接口操作多种数据库。

2. 为什么用 PDO

• 防SQL注入： 预处理语句
• 支持多数据库： MySQL、PostgreSQL、SQLite 等
• 面向对象： 写法更规范
• 错误处理： 统一的错误模式

3. 连接数据库

// 数据库配置
$host = '127.0.0.1';
$dbname = 'test';
$user = 'root';
$pass = 'root';

try {
    // 连接数据库
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $user,
        $pass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    );
    echo '连接成功';
} catch(PDOException $e) {
    die('连接失败：' . $e->getMessage());
}

4. 查询数据

查询多条

$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll();

查询单条

$stmt = $pdo->query("SELECT * FROM users WHERE id = 1");
$user = $stmt->fetch();

5. 预处理语句（防注入）

查询

// 问号占位符
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();

// 命名占位符
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([':id' => 1]);
$user = $stmt->fetch();

插入

$stmt = $pdo->prepare("INSERT INTO users (name, age) VALUES (:name, :age)");
$stmt->execute([
    ':name' => '张三',
    ':age' => 20
]);

更新

$stmt = $pdo->prepare("UPDATE users SET name = :name WHERE id = :id");
$stmt->execute([
    ':name' => '李四',
    ':id' => 1
]);

删除

$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute([':id' => 1]);

6. 获取自增ID

$pdo->exec("INSERT INTO users (name) VALUES ('王五')");
$id = $pdo->lastInsertId();

7. 事务处理

try {
    $pdo->beginTransaction();

    $pdo->exec("INSERT INTO orders ...");
    $pdo->exec("UPDATE stock ...");

    $pdo->commit();
    echo '事务成功';
} catch(Exception $e) {
    $pdo->rollBack();
    echo '事务失败：' . $e->getMessage();
}

8. 常用配置

// 设置返回关联数组
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC

// 错误模式
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

// 关闭预处理模拟（真正服务端预处理）
PDO::ATTR_EMULATE_PREPARES => false

总结

PHP PDO 连接数据库要点：

• PDO 是统一数据库接口
• 预处理语句防 SQL 注入
• 支持事务操作
• 配置错误模式方便调试
• 推荐用命名占位符
• 生产环境关闭预处理模拟
• 记得设置 utf8mb4 字符集

做数据库开发，PDO 比 mysqli 更好用。

MySQL 视图的使用

视图是虚拟表，用好了能简化查询、保护数据。

1. 什么是视图

视图（View）是基于 SQL 查询结果的虚拟表，本身不存数据，数据来自真实表。

2. 为什么用视图

1）简化查询

• 复杂查询封装成视图
• 直接查视图就行

2）权限控制

• 只暴露部分字段给用户
• 敏感字段不暴露

3）数据独立

• 表结构变了，视图不变
• 上层应用不用改

3. 创建视图

-- 创建视图
CREATE VIEW v_user_order AS
SELECT u.id, u.name, o.order_no, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 使用视图
SELECT * FROM v_user_order WHERE id = 1;

4. 修改和删除视图

-- 修改视图
CREATE OR REPLACE VIEW v_user_order AS
SELECT u.id, u.name, o.order_no, o.amount, o.create_time
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 删除视图
DROP VIEW IF EXISTS v_user_order;

5. 查看视图

-- 查看所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 查看视图结构
DESC v_user_order;

-- 查看创建语句
SHOW CREATE VIEW v_user_order;

6. 视图的限制

1）不能用 ORDER BY 在视图里

• 外层查询可以排序
• 视图里排序意义不大

2）某些视图不能更新

• 包含 GROUP BY、DISTINCT、JOIN 等的视图
• 不能通过视图 INSERT/UPDATE/DELETE

3）视图不存数据

• 每次查询都执行 SQL
• 复杂视图查询可能慢

7. 实际使用建议

1）视图不是越多越好

• 复杂业务逻辑用视图
• 简单查询直接写 SQL

2）视图不要嵌套太多层

• 视图套视图性能差
• 最多一层就够了

3）敏感数据保护

• 密码、手机号等敏感字段
• 用视图隐藏或脱敏

8. 视图和表的区别

总结

MySQL 视图使用要点：

• 视图是虚拟表，不存数据
• 简化复杂查询
• 保护敏感数据
• 用 CREATE VIEW 创建
• 简单视图可以更新
• 复杂视图查询可能慢
• 不要过度使用视图

视图是好东西，但别滥用。

MySQL 字符集设置

中文乱码是数据库开发常见问题，字符集设置一定要搞对。

1. 常用字符集

• utf8： 最多3字节，不支持 emoji
• utf8mb4： 最多4字节，支持 emoji，推荐用这个
• gbk： 中文编码，不推荐

2. 查看当前字符集

-- 查看数据库字符集
SHOW VARIABLES LIKE 'character_set%';

-- 查看排序规则
SHOW VARIABLES LIKE 'collation%';

3. 创建数据库时指定

CREATE DATABASE mydb 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

4. 创建表时指定

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

5. 修改已有表字符集

-- 修改表字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;

-- 修改字段字符集
ALTER TABLE users MODIFY name VARCHAR(50) CHARACTER SET utf8mb4;

6. 连接时设置字符集

PHP 连接数据库后一定要设置：

$conn = mysqli_connect('localhost', 'user', 'pass', 'db');
mysqli_set_charset($conn, 'utf8mb4');

或者 SQL 语句：

SET NAMES utf8mb4;

7. 中文乱码排查

出现中文乱码，按顺序检查：

1. 数据库字符集是不是 utf8mb4
2. 表字符集是不是 utf8mb4
3. 字段字符集是不是 utf8mb4
4. 连接字符集是不是 utf8mb4
5. 前端页面编码是不是 utf-8

8. 常用排序规则

• utf8mb4_general_ci： 不区分大小写，常用
• utf8mb4_unicode_ci： 支持 Unicode，更准确
• utf8mb4_bin： 区分大小写，按二进制比较

总结

MySQL 字符集设置要点：

• 统一用 utf8mb4，不要用 utf8
• 数据库、表、字段都要设置
• 连接后要设置字符集
• 中文乱码逐层排查
• 推荐 utf8mb4_general_ci 排序

字符集统一，才能避免乱码问题。

前言

PHP连MySQL用mysqli扩展，比mysql扩展好用，支持预处理、事务等。本文写个完整的连接和增删改查示例。

一、连接数据库

面向过程写法

<?php
$host = '127.0.0.1';
$user = 'root';
$pass = '123456';
$dbname = 'test';
$port = 3306;

// 连接
$conn = mysqli_connect($host, $user, $pass, $dbname, $port);

// 检查连接
if (!$conn) {
    die('连接失败: ' . mysqli_connect_error());
}

echo '连接成功';

面向对象写法

<?php
$conn = new mysqli('127.0.0.1', 'root', '123456', 'test', 3306);

if ($conn->connect_error) {
    die('连接失败: ' . $conn->connect_error);
}

echo '连接成功';

二、设置字符集

mysqli_set_charset($conn, 'utf8mb4');
// 或者
$conn->set_charset('utf8mb4');

注意： 一定要设utf8mb4，不然中文表情存不进去。

三、查询数据（SELECT）

面向过程

$sql = "SELECT id, name, age FROM users WHERE status = 1";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row['id'] . " 姓名: " . $row['name'] . "<br>";
    }
} else {
    echo "没有数据";
}

面向对象

$sql = "SELECT id, name, age FROM users WHERE status = 1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row['id'] . " 姓名: " . $row['name'] . "<br>";
    }
}

取所有数据为数组

$list = [];
while ($row = mysqli_fetch_assoc($result)) {
    $list[] = $row;
}

print_r($list);

四、插入数据（INSERT）

直接写SQL

$name = '张三';
$age = 20;

$sql = "INSERT INTO users (name, age) VALUES ('$name', $age)";

if (mysqli_query($conn, $sql)) {
    echo "插入成功，ID: " . mysqli_insert_id($conn);
} else {
    echo "插入失败: " . mysqli_error($conn);
}

预处理（防SQL注入，推荐）

$stmt = mysqli_prepare($conn, "INSERT INTO users (name, age) VALUES (?, ?)");

mysqli_stmt_bind_param($stmt, 'si', $name, $age);
// 'si' 表示：string, int

$name = '李四';
$age = 25;

if (mysqli_stmt_execute($stmt)) {
    echo "插入成功，ID: " . mysqli_insert_id($conn);
} else {
    echo "失败: " . mysqli_error($conn);
}

五、更新数据（UPDATE）

$id = 1;
$name = '王五';

$sql = "UPDATE users SET name = '$name' WHERE id = $id";

if (mysqli_query($conn, $sql)) {
    echo "更新成功，影响行数: " . mysqli_affected_rows($conn);
}

六、删除数据（DELETE）

$id = 1;

$sql = "DELETE FROM users WHERE id = $id";

if (mysqli_query($conn, $sql)) {
    echo "删除成功";
}

七、关闭连接

mysqli_close($conn);
// 或者
$conn->close();

八、完整示例封装

<?php
class DB
{
    private $conn;

    public function __construct()
    {
        $this->conn = new mysqli('127.0.0.1', 'root', '123456', 'test', 3306);
        if ($this->conn->connect_error) {
            die('连接失败');
        }
        $this->conn->set_charset('utf8mb4');
    }

    // 查询多行
    public function queryAll($sql)
    {
        $result = $this->conn->query($sql);
        $list = [];
        while ($row = $result->fetch_assoc()) {
            $list[] = $row;
        }
        return $list;
    }

    // 查询单行
    public function queryOne($sql)
    {
        $result = $this->conn->query($sql);
        return $result->fetch_assoc();
    }

    // 关闭
    public function __destruct()
    {
        $this->conn->close();
    }
}

// 使用
$db = new DB();
$users = $db->queryAll("SELECT * FROM users");
print_r($users);

九、常见坑

坑1：SQL注入

直接拼接变量到SQL里，有注入风险。

解决： 用预处理语句（?占位符）。

坑2：中文乱码

忘了设字符集。

解决： 连接后立刻 set_charset('utf8mb4')。

坑3：只取一行

用 mysqli_fetch_assoc 循环多次才取完。

注意： 这个函数每次只取一行，要循环。

坑4：连接失败没报错

生产环境关了错误显示。

解决： 手动检查 mysqli_connect_error()。

坑5：忘了关连接

频繁连接不关，占资源。

解决： 用完就关，或者用单例。

总结

记住：

1. mysqli_connect 连接数据库
2. set_charset('utf8mb4') 设字符集
3. mysqli_query 执行SQL
4. mysqli_fetch_assoc 取数据
5. 防注入用预处理（?占位符）
6. 用完 mysqli_close 关连接

最常用：

$conn = mysqli_connect('127.0.0.1', 'root', '123456', 'test', 3306);
mysqli_set_charset($conn, 'utf8mb4');

遇到问题加QQ23979811 协助处理

前言

删了几条数据，再插入新数据，id跳了一大截，不连续了。怎么让自增主键从1开始？

一、为什么自增主键会不连续

原因1：删除了数据

删了中间的几条，id就跳了。这是正常的，InnoDB的自增计数器不会回退。

原因2：事务回滚

插入一半回滚了，自增id已经用了，不会退回来。

原因3：批量插入部分失败

插100条，第50条失败了，前49条成功，后面的id就跳了。

原因4：TRUNCATE和DELETE的区别

TRUNCATE会重置自增计数器，DELETE不会。

二、查看当前自增值

SHOW TABLE STATUS LIKE 'users';

看 Auto_increment 字段，就是下一条要插入的id。

三、重置自增主键

方法1：修改自增值

ALTER TABLE users AUTO_INCREMENT = 1;

注意： 如果表里已有数据，会设为当前最大id+1。

方法2：清空表后重置

-- 清空表（重置自增）
TRUNCATE TABLE users;

TRUNCATE会把自增计数器重置为1。

方法3：DELETE后手动重置

-- 删除所有数据
DELETE FROM users;

-- 手动重置自增
ALTER TABLE users AUTO_INCREMENT = 1;

四、怎么让自增主键连续

问题：真的需要连续吗？

其实不需要。

自增主键只要唯一就行，不连续不影响业务。用户看到的id跳了也没关系。

如果非要连续

那只能：

1. 导出数据
2. 清空表
3. 重新导入

# 导出数据
mysqldump -u root -p mydb users > users.sql

# 清空表
mysql -u root -p mydb -e "TRUNCATE TABLE users"

# 重新导入
mysql -u root -p mydb < users.sql

注意： 重新导入后id会重新从1开始，但如果表之间有外键关联，会出问题。

五、InnoDB自增的坑

坑1：重启后自增值会变

MySQL 5.7之前，InnoDB把自增值存在内存里，重启后会从当前max(id)+1重新算。

如果删了最大id的那条，重启后自增值就变小了，可能重复。

MySQL 8.0修复了这个问题，把自增值持久化到redo log了。

坑2：批量插入会跳很多id

一次性插入很多数据，中间失败了，id跳了一大截。

解决： 不要批量插，一条条插。

坑3：自增步长

默认步长是1，可以改：

SHOW VARIABLES LIKE 'auto_increment_increment';

一般主从复制时会设不同步长，防止id冲突。

六、常见问题

问题1：自增主键突然跳到很大

突然从100跳到10000，怎么回事？

可能原因：

1. 批量插入失败了
2. 有人手动改了AUTO_INCREMENT
3. 主从同步导致的

问题2：自增主键重复了

ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

原因： 重启后自增值变小了，和已有数据冲突。

解决： 手动设大一点：

ALTER TABLE users AUTO_INCREMENT = 10000;

问题3：删除数据后id不连续

这是正常现象，不用管。

常见坑

坑1：TRUNCATE和DELETE搞混

DELETE FROM users 不会重置自增。
TRUNCATE TABLE users 会重置自增。

记住： 想重置自增用TRUNCATE。

坑2：外键约束导致TRUNCATE失败

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

解决： 先关外键检查：

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE users;
SET FOREIGN_KEY_CHECKS = 1;

坑3：重置自增后插入报错

设AUTO_INCREMENT=1，但表里已经有id=1的数据了。

解决： 设成max(id)+1：

SELECT MAX(id) FROM users;
-- 假设max(id)=100
ALTER TABLE users AUTO_INCREMENT = 101;

总结

记住几点：

1. 自增主键不连续是正常的，不用纠结
2. TRUNCATE会重置自增，DELETE不会
3. 重置自增用 ALTER TABLE t AUTO_INCREMENT = n
4. MySQL 8.0之前重启会丢自增值，要注意

别追求id连续，没意义，唯一就行。

遇到问题加QQ23979811 协助处理

前言

忘记MySQL root密码是常见问题，不用慌，本文教你一步步重置。

重置步骤

第一步：停止MySQL服务

systemctl stop mysqld

第二步：以跳过权限模式启动

mysqld_safe --skip-grant-tables &

或者用这个方式：

systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
systemctl start mysqld

第三步：无密码登录MySQL

mysql -u root

现在不需要密码就能直接进了。

第四步：修改密码

-- 刷新权限
FLUSH PRIVILEGES;

-- 修改密码（MySQL 5.7）
ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码';

-- 如果上面不行，用这个
UPDATE mysql.user SET authentication_string=PASSWORD('你的新密码') WHERE User='root' AND Host='localhost';

-- 刷新权限
FLUSH PRIVILEGES;

第五步：退出，正常重启MySQL

exit
systemctl stop mysqld
systemctl start mysqld

第六步：用新密码登录测试

mysql -u root -p

输入新密码，能进去就成功了。

常见坑

坑1：MySQL 8.0和5.7语法不一样

MySQL 8.0 不能用 PASSWORD() 函数了。

解决： 用 ALTER USER 语句。

坑2：skip-grant-tables没生效

启动后还是要密码。

原因： 可能MySQL已经在跑了，新启动的没生效。

解决： 先彻底停掉MySQL：

systemctl stop mysqld
pkill -9 mysqld

再重新启动。

坑3：改完密码忘记FLUSH PRIVILEGES

改完密码直接退出，结果还是不能用新密码登录。

解决： 改完密码一定要执行 FLUSH PRIVILEGES;

坑4：远程登录还是不行

本地改完密码，远程连接还是连不上。

原因： root用户只允许本地登录。

解决： 需要单独授权远程登录：

CREATE USER 'root'@'%' IDENTIFIED BY '密码';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

总结

六步重置root密码：

1. 停MySQL
2. 跳过权限启动
3. 无密码登录
4. 改密码
5. 正常重启
6. 测试登录

记住： 改完密码一定要 FLUSH PRIVILEGES;

遇到问题加QQ23979811 协助处理

MySQL 事务隔离级别

事务隔离是数据库的重要概念，搞懂4种级别很有必要。

1. 事务的ACID

• 原子性（Atomicity）： 要么全成功，要么全失败
• 一致性（Consistency）： 事务前后数据一致
• 隔离性（Isolation）： 多个事务互不影响
• 持久性（Durability）： 提交后永久保存

2. 4种隔离级别

1）读未提交（Read Uncommitted）

• 能读到未提交的数据
• 有脏读、不可重复读、幻读问题
• 性能最好

2）读提交（Read Committed）

• 只能读到已提交的数据
• 解决脏读
• 有不可重复读、幻读问题
• Oracle默认级别

3）可重复读（Repeatable Read）

• 同一个事务里读多次结果一样
• 解决脏读、不可重复读
• 有幻读问题
• MySQL默认级别

4）串行化（Serializable）

• 事务排队执行
• 解决所有问题
• 性能最差

3. 三种问题

脏读：

• 读到另一个事务未提交的数据
• 对方回滚了，你读的就是脏数据

不可重复读：

• 同一个事务里，两次读同一行结果不一样
• 中间被其他事务修改了

幻读：

• 同一个事务里，两次查询行数不一样
• 中间被其他事务插入了

4. 查看和设置

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

5. 实际使用

• 一般用默认的可重复读就够了
• 对一致性要求高的用串行化
• 不要随便改隔离级别

总结

MySQL事务隔离级别要点：

• 读未提交：性能最好，问题最多
• 读提交：解决脏读
• 可重复读：MySQL默认，解决不可重复读
• 串行化：最安全，性能最差
• 理解脏读、不可重复读、幻读

做数据库开发，事务隔离一定要懂。

MySQL 索引优化技巧

索引是数据库性能的关键，用对了查询快很多。

1. 索引类型

• 主键索引： 主键自带，唯一且非空
• 普通索引： 最基本的索引
• 唯一索引： 字段值不能重复
• 联合索引： 多个字段组成的索引
• 全文索引： 全文搜索用

2. 创建索引

-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_phone ON users(phone);

-- 联合索引
CREATE INDEX idx_name_age ON users(name, age);

3. 索引失效情况

1）不要在索引字段上做运算：

-- 失效
SELECT * FROM users WHERE age + 1 = 20;

-- 生效
SELECT * FROM users WHERE age = 19;

2）like 左模糊：

-- 失效
SELECT * FROM users WHERE name LIKE '%张';

-- 生效
SELECT * FROM users WHERE name LIKE '张%';

3）联合索引不满足最左前缀：

-- 索引：(name, age)
-- 生效（从name开始）
SELECT * FROM users WHERE name = '张三' AND age = 20;

-- 失效（跳过name直接查age）
SELECT * FROM users WHERE age = 20;

4）用 or 连接：

• or 两边有一个没索引就全表扫描

4. 优化原则

• 频繁查询的字段加索引
• 区分度高的字段适合做索引
• 不要加太多索引（影响写入性能）
• 联合索引注意最左前缀
• 尽量扩展索引，不要新建索引

5. 查看索引

-- 查看表的索引
SHOW INDEX FROM users;

6. 常见问题

• 索引加了还是慢？检查是不是失效了
• 查询还是慢？用 EXPLAIN 分析
• 写入慢？索引太多了

总结

MySQL 索引优化要点：

• 常用查询字段加索引
• 注意索引失效情况
• 联合索引最左前缀
• 不要加太多索引
• EXPLAIN 分析执行计划

数据库性能优化，索引是第一步。

MySQL 慢查询优化实战

数据库慢查询是性能瓶颈的常见原因，学会优化很重要。

1. 开启慢查询日志

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';

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

-- 设置慢查询时间（秒）
SET GLOBAL long_query_time = 1;

2. 分析慢查询

用 EXPLAIN 分析：

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

重点看：

• type：访问类型，ALL 是全表扫描，要优化
• key：用了哪个索引
• rows：扫描行数

3. 常见优化方法

1）加索引：

-- 加索引
ALTER TABLE users ADD INDEX idx_name(name);

*2）避免 SELECT ：**

-- 不好
SELECT * FROM users;

-- 好
SELECT id, name FROM users;

3）避免在索引字段上做运算：

-- 不好
SELECT * FROM users WHERE YEAR(create_time) = 2026;

-- 好
SELECT * FROM users WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';

4）避免 LIKE 左模糊：

-- 不好（用不到索引）
SELECT * FROM users WHERE name LIKE '%张%';

-- 好
SELECT * FROM users WHERE name LIKE '张%';

4. 分页优化

-- 不好（大偏移量慢）
SELECT * FROM users LIMIT 100000, 10;

-- 好（用子查询）
SELECT * FROM users WHERE id >= (
    SELECT id FROM users LIMIT 100000, 1
) LIMIT 10;

5. 常见慢查询原因

• 没加索引
• 索引失效
• 关联查询太多
• 数据量太大
• 大表分页

总结

MySQL 慢查询优化要点：

• 开启慢查询日志
• 用 EXPLAIN 分析
• 合理加索引
• 避免全表扫描
• 优化分页查询

数据库优化是个细活，多分析多测试。

MySQL root密码忘了进不去数据库？别慌，按下面步骤操作，5分钟就能重置。

1. 先停止MySQL服务

systemctl stop mysqld

CentOS 7 默认是这个命令，如果是 mariadb 就改成 systemctl stop mariadb。

2. 跳过权限验证启动MySQL

mysqld_safe --skip-grant-tables &

这一步会跳过密码验证，直接进入MySQL，不需要输密码。

3. 无密码登录MySQL

mysql -u root

现在直接就能进去了，不需要密码。

4. 重置root密码

USE mysql;

-- MySQL 5.7 及以上版本
UPDATE user SET authentication_string = PASSWORD('你的新密码') WHERE User = 'root';

-- 如果是 MySQL 5.6 及以下版本
UPDATE user SET password = PASSWORD('你的新密码') WHERE User = 'root';

FLUSH PRIVILEGES;

EXIT;

把 你的新密码 换成你想设置的新密码，注意密码强度。

5. 重启MySQL服务

systemctl start mysqld

6. 用新密码登录测试

mysql -u root -p

输入刚才设置的新密码，能登录就成功了。

常见坑

坑1：mysqld_safe 找不到
有些系统用的是 mysqld，试试：

mysqld --skip-grant-tables &

坑2：改完密码登录还是不行
一定要执行 FLUSH PRIVILEGES;，不然不生效。

坑3：密码太简单报错
MySQL 有密码强度要求，新密码最好是字母+数字+符号，8位以上。

坑4：MySQL 8.0 版本
MySQL 8.0 改密码的语法不一样：

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

遇到问题加QQ23979811 协助处理

MySQL 备份恢复实战

数据库备份是运维的基本功，数据丢了就找不回来了。

1. 备份方式

1）物理备份：

• 直接拷贝数据文件
• 速度快，适合大数据库

2）逻辑备份：

• 导出 SQL 语句
• 体积小，跨平台

2. mysqldump 备份

备份整个数据库：

mysqldump -u root -p 数据库名 > backup.sql

备份单个表：

mysqldump -u root -p 数据库名 表名 > table.sql

备份所有数据库：

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

3. 恢复数据

mysql -u root -p 数据库名 < backup.sql

注意： 恢复前要先创建数据库。

4. 压缩备份

# 备份并压缩
mysqldump -u root -p 数据库名 | gzip > backup.sql.gz

# 恢复压缩文件
gunzip < backup.sql.gz | mysql -u root -p 数据库名

5. 定时备份脚本

#!/bin/bash
# 数据库备份脚本

DB_USER="root"
DB_PASS="123456"
DB_NAME="test"
BACKUP_DIR="/data/backup"
DATE=$(date +%Y%m%d)

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

# 备份
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/$DB_NAME_$DATE.sql.gz

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

加 crontab 定时执行：

# 每天凌晨3点备份
0 3 * * * /bin/bash /root/backup.sh

6. 常见问题

1）备份太大：

• 用 --quick 参数
• 压缩备份

2）备份报错：

• 检查用户名密码
• 检查权限

3）恢复失败：

• 检查 SQL 文件是否完整
• 检查数据库字符集

7. 最佳实践

• 每天定时备份
• 备份文件存到异地
• 定期测试恢复
• 重要数据做双备份

总结

MySQL 备份恢复要点：

• mysqldump 导出 SQL
• gzip 压缩减小体积
• crontab 定时执行
• 定期测试恢复

做运维，数据备份永远是第一位的。

明明建了索引，查询还是很慢？十有八九是索引失效了。MySQL 索引失效是开发中最常见的坑，下面整理了最容易导致索引失效的场景，写SQL的时候一定要避开。

1. 对索引字段做函数操作

-- 索引失效
SELECT * FROM user WHERE DATE(create_time) = '2026-10-01';

-- 正确写法，走索引
SELECT * FROM user WHERE create_time >= '2026-10-01' AND create_time < '2026-10-02';

对索引字段用了 DATE()、YEAR()、SUBSTRING() 等函数，索引就失效了。

2. 隐式类型转换

-- 假设 phone 是 varchar 类型
-- 索引失效（数字和字符串比较，MySQL自动转换）
SELECT * FROM user WHERE phone = 13800138000;

-- 正确写法，走索引
SELECT * FROM user WHERE phone = '13800138000';

字符串类型的字段，查询条件一定要加引号。

3. 联合索引不满足最左匹配原则

-- 联合索引：(a, b, c)
-- 走索引
SELECT * FROM t WHERE a = 1 AND b = 2;

-- 不走索引（跳过了a）
SELECT * FROM t WHERE b = 2 AND c = 3;

-- 走部分索引（a走，b和c不走）
SELECT * FROM t WHERE a = 1 AND c = 3;

联合索引必须从最左边的列开始用，不能跳过。

4. 使用了 != 或 <>

-- 索引大概率失效
SELECT * FROM user WHERE status != 1;

-- 可以改成范围查询
SELECT * FROM user WHERE status < 1 OR status > 1;

不等于操作通常会导致全表扫描，因为要返回的结果太多了。

5. LIKE 以 % 开头

-- 索引失效
SELECT * FROM user WHERE name LIKE '%张';

-- 走索引
SELECT * FROM user WHERE name LIKE '张%';

模糊查询左边带 %，索引就用不上了。

6. OR 连接的条件有一个没索引

-- name 有索引，age 没索引 → 整个SQL不走索引
SELECT * FROM user WHERE name = '张三' OR age = 20;

OR 两边只要有一个字段没索引，整个查询就不走索引。

7. 用 explain 验证索引是否生效

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

看 key 列：

• 显示索引名 → 走索引了
• 显示 NULL → 没走索引

看 type 列：

• ref、eq_ref → 正常
• ALL → 全表扫描，索引失效了

常见坑总结

1. 不要在索引字段上做运算：WHERE age + 1 = 20 → 改成 WHERE age = 19
2. 不要用 NOT IN / NOT EXISTS：大数据量下很慢
3. **不要用 SELECT ***：只查需要的字段，覆盖索引更快
4. 数据量小的表不要建索引：全表扫描可能比索引还快

遇到问题加QQ23979811 协助处理