做MySQL开发这几年，避免全表更新是被身边同事问得最多的问题之一。今天把答案整理出来，供大家参考。

很多初学者在学习MySQL时，都会遇到子查询优化相关的问题。网上的资料零散且质量参差不齐，本文尝试用一篇文章把子查询优化讲清楚。

在日常MySQL开发中，触发器简介是一个出现频率非常高的话题。无论是新手还是有一定经验的开发者，都可能在细节上踩坑。本文将系统地梳理触发器简介的相关知识，并给出可以直接运行的示例代码。

如果你正在学习MySQL，那么HAVING与WHERE的区别几乎是绕不开的一个环节。本文从原理到实践，带你快速上手。

最近在项目里又遇到了索引优化入门的典型问题，索性把解决方法整理成这篇文章，希望能帮到遇到同样问题的同学。

熟练掌握事务隔离级别详解，是写好MySQL代码的基本功。本文循序渐进，从概念到示例一次讲透。

项目赶进度的时候，触发器简介这类基础问题最容易浪费调试时间。提前掌握本文的内容，可以少走很多弯路。

慢查询排查思路看似简单，实际使用中却有不少细节需要注意。本文把常见场景和坑点都列出来，方便随时查阅。

前言

明明加了索引，但查询还是慢？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 协助处理

前言

网站突然变慢，数据库CPU100%？多半是慢查询搞的鬼。本文讲怎么开启慢查询日志、用explain分析SQL执行计划，找到慢的原因。

一、开启慢查询日志

临时开启

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

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

-- 超过1秒就算慢查询
SET GLOBAL long_query_time = 1;

永久开启

编辑my.cnf：

vi /etc/my.cnf

加：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

重启MySQL：

systemctl restart mysqld

看慢查询日志

tail -f /var/log/mysql/slow.log

二、explain是什么

explain用来分析SQL是怎么执行的，看有没有用索引、扫了多少行。

用法

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

三、explain字段解释

id

SELECT的标识符，越大越先执行。

select_type

查询类型：

• SIMPLE：简单查询
• PRIMARY：主查询
• SUBQUERY：子查询

table

查的哪张表。

type（最重要）

访问类型，从好到差：

重点看： 出现ALL就是全表扫描，要加索引。

possible_keys

可能用到的索引。

key

实际用到的索引。

key是NULL就是没用到索引。

rows

预估要扫描多少行。

越大越慢。

Extra

额外信息：

四、常见慢查询原因

原因1：没加索引

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

name字段没索引 → 全表扫描 → 慢。

解决： 加索引

ALTER TABLE users ADD INDEX idx_name(name);

原因2：索引失效

虽然加了索引，但SQL写法不对，索引用不上。

索引失效的情况：

1. 对索引字段用函数：

   SELECT * FROM users WHERE LEFT(name, 3) = '张三'; -- 索引失效

2. 索引字段用运算：

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

3. 最左前缀原则不满足：

   -- 联合索引(a, b, c)
   SELECT * FROM t WHERE b = 1; -- 索引失效

4. like以%开头：

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

5. 类型不匹配：

   -- phone是varchar
   SELECT * FROM users WHERE phone = 13800138000; -- 索引失效

原因3：SELECT *

查了不需要的字段，占内存。

解决： 只查需要的字段

SELECT id, name FROM users WHERE ...

原因4：大表分页越查越慢

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

慢的原因： 要先扫10万行再跳过。

解决：

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

五、实战案例

慢SQL

SELECT * FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time DESC LIMIT 10;

explain分析

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time DESC LIMIT 10;

发现： type是ALL，key是NULL → 全表扫描。

加索引

ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);

再explain： type变成ref，key是idx_user_status_time → 用上索引了。

六、优化原则

1. WHERE条件加索引
2. ORDER BY字段加索引
3. 联合索引遵守最左前缀
4. **不要SELECT ***
5. 避免在索引字段上用函数
6. 大分页用子查询优化

七、常见坑

坑1：加了索引但没用

原因： SQL写法导致索引失效。

排查： explain看key字段。

坑2：索引加太多

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

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

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

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

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

总结

记住：

1. 开启慢查询日志找慢SQL
2. 用explain分析SQL执行计划
3. type=ALL就是全表扫描，要加索引
4. key=NULL就是没用到索引
5. rows越大越慢
6. 索引失效要避免

最常用：

EXPLAIN SELECT * FROM users WHERE ...

遇到问题加QQ23979811 协助处理

前言

数据库备份是每个站长的基本功。MySQL最常用的备份工具就是mysqldump，简单好用。本文讲怎么用mysqldump备份和恢复数据库，包括完整命令和常见坑。

一、mysqldump是什么

mysqldump是MySQL自带的备份工具，把数据库导出成SQL文件，需要的时候再导入回去。

特点：

• 免费，MySQL自带
• 导出的是SQL文本，方便查看和编辑
• 适合小数据库（几十G以内）

二、备份数据库

备份单个数据库

mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql

例子：

mysqldump -uroot -p123456 test > test.sql

注意： -p和密码之间没有空格。

备份多个数据库

mysqldump -uroot -p123456 --databases db1 db2 db3 > dbs.sql

备份所有数据库

mysqldump -uroot -p123456 --all-databases > all.sql

备份单个表

mysqldump -uroot -p123456 test users > users.sql

备份多个表

mysqldump -uroot -p123456 test users orders > tables.sql

三、常用参数

带删除表语句（--add-drop-table）

导出的SQL里会有 DROP TABLE IF EXISTS，恢复的时候先删表再建表。

mysqldump -uroot -p123456 --add-drop-table test > test.sql

默认就是开的。

不带建表语句（--no-create-info）

只导数据，不导表结构。

mysqldump -uroot -p123456 --no-create-info test > data.sql

只导表结构（--no-data）

只导表结构，不导数据。

mysqldump -uroot -p123456 --no-data test > structure.sql

压缩备份

mysqldump -uroot -p123456 test | gzip > test.sql.gz

解压：

gunzip test.sql.gz

指定端口

mysqldump -uroot -p123456 -P 3307 test > test.sql

指定主机

mysqldump -uroot -p123456 -h 1.2.3.4 test > test.sql

四、恢复数据库

方法1：用mysql命令

mysql -u用户名 -p密码 数据库名 < 备份文件.sql

例子：

mysql -uroot -p123456 test < test.sql

方法2：登录MySQL后source

mysql -uroot -p123456

mysql> use test;
mysql> source /root/test.sql;

恢复压缩备份

gunzip < test.sql.gz | mysql -uroot -p123456 test

五、定时备份脚本

写个备份脚本

#!/bin/bash

# 数据库配置
DB_USER="root"
DB_PASS="123456"
DB_NAME="test"

# 备份目录
BACKUP_DIR="/data/backup"

# 文件名（带日期）
DATE=$(date +%Y%m%d_%H%M%S)
FILE_NAME="${DB_NAME}_${DATE}.sql.gz"

# 备份
mysqldump -u${DB_USER} -p${DB_PASS} ${DB_NAME} | gzip > ${BACKUP_DIR}/${FILE_NAME}

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

echo "备份完成：${FILE_NAME}"

加执行权限

chmod +x /root/backup.sh

加到crontab

每天凌晨3点备份：

crontab -e

加一行：

0 3 * * * /root/backup.sh

六、常见坑

坑1：密码写在命令行里不安全

-p123456 会在进程列表里看到密码。

更安全的方法：

mysqldump -uroot -p test > test.sql

然后手动输入密码。

或者用配置文件：

# ~/.my.cnf
[mysqldump]
user=root
password=123456

然后直接：

mysqldump test > test.sql

坑2：大表备份很慢

数据多了以后，mysqldump会锁表，影响业务。

解决： 用 --single-transaction 参数：

mysqldump -uroot -p123456 --single-transaction test > test.sql

这个参数适合InnoDB，不锁表。

坑3：字符集不对

导出的SQL乱码。

加参数：

mysqldump -uroot -p123456 --default-character-set=utf8mb4 test > test.sql

坑4：自增ID不连续

恢复后自增ID变了。

正常现象，不用管。

坑5：备份文件太大

用gzip压缩：

mysqldump -uroot -p123456 test | gzip > test.sql.gz

七、备份验证

备份完了要验证一下能不能恢复。

看文件大小

ls -lh test.sql

不能是0字节。

看文件内容

head -20 test.sql

看开头是不是正常的SQL。

测试恢复

找个测试库恢复一下：

mysql -uroot -p123456 test_restore < test.sql

总结

记住：

1. 备份：mysqldump -uroot -p123456 数据库 > 备份.sql
2. 恢复：mysql -uroot -p123456 数据库 < 备份.sql
3. 大表加 --single-transaction 不锁表
4. 用gzip压缩备份文件
5. 定时备份要自动删旧备份
6. 备份完要验证

最常用：

# 备份
mysqldump -uroot -p123456 --single-transaction test | gzip > test.sql.gz

# 恢复
gunzip < test.sql.gz | mysql -uroot -p123456 test

遇到问题加QQ23979811 协助处理

前言

做网站列表页，数据太多一页显示不完，需要分页。PHP+MySQL做分页很简单，就是用SQL的limit。本文写个完整的分页示例，直接拿去用。

一、分页原理

分页就两件事：

1. 查总条数：看看一共有多少条数据
2. 查当前页数据：用limit取当前页要显示的那几条

公式：

偏移量 = (当前页 - 1) × 每页条数

比如每页10条，第3页：

偏移量 = (3 - 1) × 10 = 20
SQL: SELECT * FROM users LIMIT 20, 10

二、SQL limit用法

语法

SELECT * FROM 表名 LIMIT 偏移量, 每页条数

例子

-- 取前10条
SELECT * FROM users LIMIT 0, 10;

-- 取第11-20条
SELECT * FROM users LIMIT 10, 10;

-- 取第21-30条
SELECT * FROM users LIMIT 20, 10;

三、完整分页代码

第一步：接收当前页码

<?php
// 当前页，默认第1页
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;

// 每页显示多少条
$pageSize = 10;

// 计算偏移量
$offset = ($page - 1) * $pageSize;

第二步：查总条数

// 连接数据库
$conn = mysqli_connect('127.0.0.1', 'root', '123456', 'test', 3306);
mysqli_set_charset($conn, 'utf8mb4');

// 查总条数
$sql = "SELECT COUNT(*) as total FROM users";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$total = $row['total'];

// 算总页数
$totalPage = ceil($total / $pageSize);

第三步：查当前页数据

// 查当前页的数据
$sql = "SELECT id, name, age FROM users ORDER BY id DESC LIMIT $offset, $pageSize";
$result = mysqli_query($conn, $sql);

// 取数据
$list = [];
while ($row = mysqli_fetch_assoc($result)) {
    $list[] = $row;
}

第四步：显示数据和分页导航

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>用户列表</title>
</head>
<body>

<h1>用户列表</h1>

<table border="1">
    <tr>
        <th>ID</th>
        <th>姓名</th>
        <th>年龄</th>
    </tr>
    <?php foreach ($list as $item): ?>
    <tr>
        <td><?php echo $item['id']; ?></td>
        <td><?php echo $item['name']; ?></td>
        <td><?php echo $item['age']; ?></td>
    </tr>
    <?php endforeach; ?>
</table>

<p>共 <?php echo $total; ?> 条，第 <?php echo $page; ?> / <?php echo $totalPage; ?> 页</p>

<p>
    <?php if ($page > 1): ?>
    <a href="?page=<?php echo $page - 1; ?>">上一页</a>
    <?php endif; ?>

    <?php for ($i = 1; $i <= $totalPage; $i++): ?>
        <?php if ($i == $page): ?>
            <strong><?php echo $i; ?></strong>
        <?php else: ?>
            <a href="?page=<?php echo $i; ?>"><?php echo $i; ?></a>
        <?php endif; ?>
    <?php endfor; ?>

    <?php if ($page < $totalPage): ?>
    <a href="?page=<?php echo $page + 1; ?>">下一页</a>
    <?php endif; ?>
</p>

</body>
</html>

四、封装成分页函数

function pagination($page, $totalPage, $url = '')
{
    $html = '';

    if ($page > 1) {
        $html .= '<a href="' . $url . '?page=' . ($page - 1) . '">上一页</a> ';
    }

    for ($i = 1; $i <= $totalPage; $i++) {
        if ($i == $page) {
            $html .= '<strong style="margin:0 5px;">' . $i . '</strong> ';
        } else {
            $html .= '<a href="' . $url . '?page=' . $i . '" style="margin:0 5px;">' . $i . '</a> ';
        }
    }

    if ($page < $totalPage) {
        $html .= '<a href="' . $url . '?page=' . ($page + 1) . '">下一页</a>';
    }

    return $html;
}

// 使用
echo pagination($page, $totalPage);

五、常见坑

坑1：页码没做限制

用户输入 ?page=999，结果查出来是空的。

解决： 限制页码范围：

$page = max(1, min($page, $totalPage));

坑2：offset算错了

$offset = $page * $pageSize; // 错！

正确：

$offset = ($page - 1) * $pageSize; // 对！

坑3：总条数查错了

带条件查询的时候，总条数也要带条件。

比如：

// 带条件查数据
$sql = "SELECT * FROM users WHERE status = 1 LIMIT $offset, $pageSize";

// 总条数也要带条件
$sql = "SELECT COUNT(*) FROM users WHERE status = 1";

坑4：大表分页越查越慢

数据多了以后，LIMIT 100000, 10 会很慢。

原因： MySQL要先扫描前面10万条再跳过。

优化：

-- 先查id，再关联
SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 100000, 10) t
ON u.id = t.id;

六、带搜索条件的分页

// 搜索关键词
$keyword = isset($_GET['keyword']) ? trim($_GET['keyword']) : '';

// 拼WHERE
$where = "WHERE 1=1";
if ($keyword) {
    $where .= " AND name LIKE '%$keyword%'";
}

// 总条数
$sql = "SELECT COUNT(*) as total FROM users $where";

// 当前页数据
$sql = "SELECT * FROM users $where ORDER BY id DESC LIMIT $offset, $pageSize";

总结

记住：

1. 分页就是 LIMIT 偏移量, 每页条数
2. 偏移量 = (当前页 - 1) × 每页条数
3. 先查总条数，再算总页数
4. 页码要做限制，不能超出范围
5. 带条件查询时，总条数也要带条件

核心公式：

$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$pageSize = 10;
$offset = ($page - 1) * $pageSize;

$sql = "SELECT * FROM users LIMIT $offset, $pageSize";

遇到问题加QQ23979811 协助处理

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性能优化的核心，一定要多实践。

数据量小的时候分页很快，一旦表数据上百万条，翻到后面的页就慢得要死？这是MySQL分页最常见的问题，下面说几个实用的优化方案。

1. 先看为什么会慢

SELECT * FROM 表名 ORDER BY id LIMIT 100000, 10;

这个SQL的意思是：扫描前100010条，丢掉前100000条，返回后10条。

数据量越大，扫描的行数越多，就越慢。这就是为什么越往后翻越慢。

2. 方案一：子查询优化（最常用）

SELECT * FROM 表名 
WHERE id >= (SELECT id FROM 表名 ORDER BY id LIMIT 100000, 1)
LIMIT 10;

原理：

• 子查询先通过索引找到第100001条的id
• 然后直接从这个id开始往后查
• 不需要扫描前10万条

速度对比：

• 原SQL：扫描10万+行，耗时几秒
• 优化后：走索引，几毫秒就出来

3. 方案二：游标分页（推荐用在后台）

-- 第一页
SELECT * FROM 表名 ORDER BY id LIMIT 10;

-- 第二页（记住上一页最后一条的id）
SELECT * FROM 表名 WHERE id > 100 ORDER BY id LIMIT 10;

-- 第三页
SELECT * FROM 表名 WHERE id > 110 ORDER BY id LIMIT 10;

优点：

• 永远都是走索引，不管翻到第几页都一样快
• 适合无限滚动加载

缺点：

• 不能直接跳到第100页
• 不适合传统的"上一页/下一页"翻页

4. 方案三：延迟关联

SELECT t.* FROM 表名 t
INNER JOIN (
    SELECT id FROM 表名 ORDER BY id LIMIT 100000, 10
) tmp ON t.id = tmp.id;

原理：

• 先通过索引查出需要的10个id
• 再用这10个id去关联原表查完整数据
• 避免回表太多行

5. 方案四：限制最大翻页深度

如果不是必须让用户翻到很后面，可以直接限制：

-- 最多只允许翻100页（1000条数据）
IF 页码 > 100 THEN
    -- 提示用户"请搜索更精确的条件"
END IF;

一般用户也不会翻到很后面的页，限制一下性能就好很多。

6. 方案五：用ES做搜索

如果是文章、商品之类的内容，直接用Elasticsearch做全文搜索和分页，MySQL只存原始数据。

ES天生适合做大数据量的分页搜索，MySQL不是干这个的。

常见坑

坑1：ORDER BY 字段没加索引
ORDER BY 的字段一定要加索引，不然不管怎么优化都是全表扫描。

坑2：OFFSET 太大了
OFFSET 100万，不管怎么优化都慢，直接用游标分页。

*坑3：SELECT 不要用**
只查需要的字段，减少回表数据量。

遇到问题加QQ23979811 协助处理

导入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 排序

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