SQL - 个人技术知识分享

MySQL分页查询优化

引言

后台列表、文章分页是最常见的SQL场景。数据量小的时候LIMIT偏移量查询很快，数据到几十万、上百万行，深分页会明显变慢，甚至拖垮数据库。

为什么深分页慢

LIMIT 100000,20 会先扫描前100000行再丢弃，只返回20行，扫描行数越多IO开销越大。

优化方案一：子查询定位主键

先查出起始主键，再用主键关联取数据，避免回表扫描：

SELECT a.* FROM article a
INNER JOIN (
  SELECT id FROM article ORDER BY id LIMIT 100000,20
) b ON a.id=b.id;

子查询走覆盖索引，速度明显提升。

优化方案二：游标/记住上一页最大ID

SELECT * FROM article
WHERE id > 100000
ORDER BY id LIMIT 20;

每次只查比上一页最后一条ID大的记录，不走大偏移，适合无限滚动。

其他优化点

分页排序字段必须建索引，避免filesort；
只查询需要的字段，不用SELECT *；
禁止前端跳转到任意深页，后台限制最大页码；
超大表考虑分表、搜索引擎承接列表。

总结

深分页慢的根源是大偏移扫描。优先用主键游标翻页，配合覆盖索引；传统offset分页用子查询优化。列表数据量极大时，交给ES等搜索方案更合理。

标签: MySQL 索引 SQL 分页优化深分页

emer 发布于 2026-10-18 07:20

浏览(36)

MySQL索引设计原则

引言

MySQL索引相当于书籍目录，快速定位数据，避免全表扫描。不合理的索引不仅无法提升查询速度，还会降低写入性能。索引设计是数据库优化最基础、最高效的手段，建站、接口开发都需要掌握。

索引核心原理

InnoDB引擎索引基于B+树实现，主键索引叶子节点存储完整行数据；二级索引叶子节点存储主键值，查询会触发回表。索引字段越大，占用磁盘越多，内存缓存能存放的索引越少，查询越慢。

索引设计核心原则

选择性优先：优先给区分度高的字段建索引，如手机号、ID；性别、状态这类只有少量枚举值字段不适合单独建索引。
联合索引遵循最左匹配原则，字段顺序：等值查询在前，范围查询放后面。
避免在索引字段做函数运算，where YEAR(create_time)=2026会直接失效索引。
不要过度建索引，每次新增、更新、删除数据，所有索引都需要同步更新，拖累写入。

示例SQL

-- 联合索引示例，适合 where uid=100 and create_time>xxx
CREATE INDEX idx_uid_ct ON user(uid,create_time);

-- 查看sql是否命中索引
EXPLAIN SELECT * FROM user WHERE uid=100;

常见踩坑

字符串字段不加引号，隐式类型转换，索引失效；
模糊查询%关键词，前置百分号不走索引；
联合索引跳过左侧字段，无法触发索引。

总结

索引不是越多越好，按需创建。建索引前先用explain分析执行计划，优先优化高频查询SQL，控制索引数量，平衡查询与写入性能。

标签: MySQL Explain 索引 SQL 数据库优化

emer 发布于 2026-10-18 07:10

浏览(37)

PHP实现简单分页，SQL limit用法

前言

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

一、分页原理

分页就两件事：

查总条数：看看一共有多少条数据
查当前页数据：用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";

总结

记住：

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

核心公式：

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

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

遇到问题加QQ23979811 协助处理

标签: PHP MySQL SQL 分页 limit

emer 发布于 2026-10-9 07:37

浏览(46)

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 协助处理

标签: MySQL 优化 SQL 分页大表

emer 发布于 2026-10-9 07:27

浏览(40)

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. 视图和表的区别

对比项	表	视图
存数据	是	否（虚拟表）
占空间	是	否
查询速度	快	取决于SQL
安全性	低	高

总结

MySQL 视图使用要点：

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

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

标签: MySQL 数据库开发 SQL 视图

emer 发布于 2026-10-9 07:15

浏览(46)

MySQL索引失效常见场景，避免踩坑

明明建了索引，查询还是很慢？十有八九是索引失效了。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 → 全表扫描，索引失效了

常见坑总结

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

遇到问题加QQ23979811 协助处理

标签: MySQL 数据库索引优化 SQL

emer 发布于 2026-10-8 07:14

浏览(42)

MySQL 性能优化实战

数据库慢是网站性能的大问题，这篇讲实战优化方法，从几个方面下手。

1. 慢查询排查

先找到慢SQL，再优化。

开启慢查询日志：

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

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

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

用 explain 分析：

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

重点看：

type：有没有用到索引（ref/const 好，ALL 全表扫描坏）
key：实际用的索引
rows：扫描行数

2. 索引优化

建索引原则：

where 条件经常用的字段建索引
联合索引最左前缀原则
避免建太多索引，影响写入

查看索引：

SHOW INDEX FROM users;

建索引：

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

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

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

3. SQL 优化

**不要 SELECT ***

-- 坏
SELECT * FROM users WHERE id = 1;

-- 好
SELECT id, name, email FROM users WHERE id = 1;

避免 LIKE 前导模糊：

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

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

大表分页优化：

-- 坏（深度分页慢）
SELECT * FROM users LIMIT 100000, 10;

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

4. 表结构优化

用合适的数据类型（能用 int 就别用 bigint）
字段尽量 NOT NULL
大表分表分库

5. 配置优化

my.cnf 常用配置：

# 缓冲池大小（内存的 70%）
innodb_buffer_pool_size = 1G

# 连接数
max_connections = 500

# 慢查询日志
slow_query_log = 1
long_query_time = 1

6. 总结

MySQL 优化三步走：

找慢SQL： 慢查询日志 + explain
加索引： where 条件字段
优化SQL： 不写 SELECT *，避免全表扫描

数据库优化不是一蹴而就，要持续监控和调整。

标签: MySQL 性能优化索引慢查询 SQL

emer 发布于 2026-10-7 07:14

浏览(30)

MySQL分页查询优化

MySQL分页查询优化

引言

为什么深分页慢

优化方案一：子查询定位主键

优化方案二：游标/记住上一页最大ID

其他优化点

总结

MySQL索引设计原则

MySQL索引设计原则

引言

索引核心原理

索引设计核心原则

示例SQL

常见踩坑

总结

PHP实现简单分页，SQL limit用法

前言

一、分页原理

二、SQL limit用法

语法

例子

三、完整分页代码

第一步：接收当前页码

第二步：查总条数

第三步：查当前页数据

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

四、封装成分页函数

五、常见坑

坑1：页码没做限制

坑2：offset算错了

坑3：总条数查错了

坑4：大表分页越查越慢

六、带搜索条件的分页

总结

MySQL大表分页越查越慢，优化方案

1. 先看为什么会慢

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

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

4. 方案三：延迟关联

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

6. 方案五：用ES做搜索

常见坑

MySQL 视图的使用

MySQL 视图的使用

1. 什么是视图

2. 为什么用视图

3. 创建视图

4. 修改和删除视图

5. 查看视图

6. 视图的限制

7. 实际使用建议

8. 视图和表的区别

总结

MySQL索引失效常见场景，避免踩坑

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

2. 隐式类型转换

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

4. 使用了 != 或 <>

5. LIKE 以 % 开头

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

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

常见坑总结

MySQL 性能优化实战

MySQL 性能优化实战

1. 慢查询排查

2. 索引优化

3. SQL 优化

4. 表结构优化

5. 配置优化

6. 总结

个人资料

搜索

链接