程序员狮子🦁

Appearance

MySQL优化实战 ​

索引优化 ​

sql
-- 创建复合索引
CREATE INDEX idx_user_status_time ON users(status, created_time);

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE status = 1 ORDER BY created_time;
-- 创建复合索引
CREATE INDEX idx_user_status_time ON users(status, created_time);

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE status = 1 ORDER BY created_time;

查询优化 ​

sql
-- 避免SELECT *
SELECT id, name, email FROM users WHERE status = 1;

-- 使用LIMIT分页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
-- 避免SELECT *
SELECT id, name, email FROM users WHERE status = 1;

-- 使用LIMIT分页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

配置优化 ​

ini
# my.cnf
[mysqld]
innodb_buffer_pool_size = 1G
query_cache_size = 64M
max_connections = 200
# my.cnf
[mysqld]
innodb_buffer_pool_size = 1G
query_cache_size = 64M
max_connections = 200