PHP数据库索引优化与查询调优-源码库

PHP数据库索引优化与查询调优：从慢查询到高性能的实战之路

大家好，我是33blog的技术作者。今天想和大家分享我在PHP项目中进行数据库优化的实战经验。记得去年接手一个电商项目时，商品列表页加载需要5-6秒，经过一系列索引优化和查询调优后，最终降到了200毫秒以内。这个过程让我深刻体会到，数据库优化不是高深的理论，而是需要结合具体业务场景的实践艺术。

理解数据库索引的基本原理

在开始优化之前，我们需要明白索引就像书的目录。没有索引时，数据库需要全表扫描来找到我们需要的数据，这就像在没有目录的书中逐页查找内容。而合适的索引能让数据库快速定位到目标数据。

让我先展示一个常见的用户表结构：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TINYINT DEFAULT 1,
    INDEX idx_username (username),
    INDEX idx_email (email)
);

识别慢查询：找到优化目标

优化第一步是找到需要优化的查询。我通常使用MySQL的慢查询日志：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1; -- 超过1秒的查询被记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

在实际项目中，我经常遇到这样的问题查询：

// 优化前的代码 - 没有使用索引
$sql = "SELECT * FROM orders WHERE customer_id = ? AND status = ? 
        AND created_at BETWEEN ? AND ? ORDER BY created_at DESC";
$stmt = $pdo->prepare($sql);
$stmt->execute([$customerId, $status, $startDate, $endDate]);

创建合适的复合索引

针对上面的查询，我创建了复合索引：

-- 为orders表创建复合索引
ALTER TABLE orders ADD INDEX idx_customer_status_date 
(customer_id, status, created_at);

踩坑提示：复合索引的顺序很重要！应该把等值查询的字段放在前面，范围查询的字段放在后面。这是我通过多次试验得出的经验。

避免索引失效的常见陷阱

即使创建了索引，如果使用不当也会导致索引失效。以下是我总结的几个常见问题：

// 错误示例1：在索引列上使用函数
$sql = "SELECT * FROM users WHERE DATE(created_at) = ?";

// 错误示例2：使用LIKE模糊查询时前导通配符
$sql = "SELECT * FROM products WHERE name LIKE '%手机%'";

// 错误示例3：对索引列进行运算
$sql = "SELECT * FROM orders WHERE price * 1.1 > 100";

正确的做法应该是：

// 正确示例1：避免在索引列上使用函数
$sql = "SELECT * FROM users WHERE created_at >= ? AND created_at < ?";

// 正确示例2：LIKE查询尽量使用后导通配符
$sql = "SELECT * FROM products WHERE name LIKE '手机%'";

使用EXPLAIN分析查询执行计划

EXPLAIN是我最常用的优化工具，它能显示MySQL如何执行查询：

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 123 AND status = 1 
AND created_at BETWEEN '2023-01-01' AND '2023-12-31';

重点关注type列：

const：通过主键或唯一索引查询
ref：使用非唯一索引查询
range：使用索引范围查询
ALL：全表扫描（需要优化）

分页查询的优化技巧

分页查询是另一个性能瓶颈。传统的方式在大数据量时很慢：

// 传统分页 - 数据量大时很慢
$sql = "SELECT * FROM products LIMIT 10000, 20";

我推荐使用游标分页：

// 优化后的游标分页
$sql = "SELECT * FROM products WHERE id > ? ORDER BY id LIMIT 20";
$stmt = $pdo->prepare($sql);
$stmt->execute([$lastId]);

实战案例：商品搜索优化

让我分享一个真实的优化案例。最初的商品搜索查询：

$sql = "SELECT * FROM products 
WHERE name LIKE ? OR description LIKE ? 
ORDER BY created_at DESC LIMIT 50";

优化后：

// 创建全文索引
ALTER TABLE products ADD FULLTEXT idx_search (name, description);

// 使用全文搜索
$sql = "SELECT * FROM products 
WHERE MATCH(name, description) AGAINST(? IN BOOLEAN MODE)
ORDER BY created_at DESC LIMIT 50";

这个改动让搜索性能提升了10倍以上！