数据库优化：MySQL索引与查询优化

数据库优化：MySQL索引与查询优化

大家好，我是欧阳瑞（Rich Own）。今天想和大家聊聊数据库优化这个重要话题。作为一个全栈开发者，数据库性能是应用性能的关键。今天就来分享一下MySQL索引和查询优化的实战经验。

为什么需要数据库优化？

索引基础

什么是索引？

索引是一种数据结构，用于加速数据库查询。它就像一本书的目录，可以快速定位到需要的数据。

索引类型

创建索引

— 创建单列索引
CREATE INDEX idx_name ON users(name);

— 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

— 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

— 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

索引失效场景

— 1. 使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2023; — 索引失效

— 2. 使用OR
SELECT * FROM users WHERE name = 'Alice' OR age = 30; — 可能失效

— 3. 类型转换
SELECT * FROM users WHERE id = '123'; — 索引失效

— 4. LIKE以%开头
SELECT * FROM users WHERE name LIKE '%Alice'; — 索引失效

— 5. 范围查询后使用其他列
SELECT * FROM users WHERE age > 30 AND name = 'Alice'; — name索引失效

查询优化

慢查询日志

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

— 查看慢查询日志
SHOW PROCESSLIST;

EXPLAIN分析

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

— 输出解读
— type: ALL < index < range < ref < eq_ref < const
— key: 使用的索引
— rows: 预计扫描行数
— Extra: Using index, Using where, Using filesort

查询优化技巧

— 1. 避免SELECT *
SELECT name, age FROM users WHERE id = 1; — 好

— 2. 使用覆盖索引
SELECT id, name FROM users WHERE name = 'Alice'; — 使用idx_name索引

— 3. 优化JOIN
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

— 4. 子查询优化
— 不好
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

— 好
SELECT u.* FROM users u
JOIN orders o ON u.id = o.user_id;

表结构优化

数据类型选择

范式与反范式

— 范式化
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(id)
);

— 反范式化（性能优化）
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
user_name VARCHAR(100), — 冗余字段
amount DECIMAL(10,2)
);

分区表

— 按时间分区
CREATE TABLE logs (
id INT,
log_time DATETIME,
content TEXT
)
PARTITION BY RANGE (TO_DAYS(log_time)) (
PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01'))
);

实战案例

优化前

— 慢查询
SELECT * FROM orders
WHERE status = 'completed'
AND create_time > '2023-01-01'
ORDER BY amount DESC
LIMIT 100;

— EXPLAIN结果
— type: ALL
— rows: 1000000
— Extra: Using where; Using filesort

优化后

— 创建复合索引
CREATE INDEX idx_status_create_time_amount ON orders(status, create_time, amount);

— 查询
SELECT id, user_id, amount FROM orders
WHERE status = 'completed'
AND create_time > '2023-01-01'
ORDER BY amount DESC
LIMIT 100;

— EXPLAIN结果
— type: range
— key: idx_status_create_time_amount
— rows: 100
— Extra: Using where; Backward index scan

最佳实践

1. 定期分析表

ANALYZE TABLE users;
ANALYZE TABLE orders;

2. 重建索引

ALTER TABLE users ENGINE=InnoDB; — 重建所有索引

3. 使用缓存

import redis

r = redis.Redis(host='localhost', port=6379)

def get_user(user_id):
cache_key = f'user:{user_id}'
cached = r.get(cache_key)

if cached:
return json.loads(cached)

user = db.query("SELECT * FROM users WHERE id = %s", user_id)
r.setex(cache_key, 3600, json.dumps(user))

return user

总结

数据库优化是一个持续的过程。通过合理的索引设计、查询优化和表结构设计，可以显著提升数据库性能。

我的鬃狮蜥Hash对数据库优化也有自己的理解——它总是记住蟋蟀经常出现的位置，这也许就是自然界的"索引"吧！

如果你对数据库优化感兴趣，欢迎留言交流！我是欧阳瑞，极客之路，永无止境！

技术栈：MySQL · 索引优化 · 查询优化