数据库性能优化实战索引与查询调优一、引言数据库性能是系统性能的关键瓶颈之一。随着业务数据量的增长合理的索引设计和查询优化变得至关重要。本文将深入探讨MySQL数据库的索引原理、查询优化技巧以及性能调优的最佳实践。二、索引原理与设计2.1 BTree索引结构graph TD A[根节点] -- B[中间节点1] A -- C[中间节点2] B -- D[叶子节点1] B -- E[叶子节点2] C -- F[叶子节点3] C -- G[叶子节点4] D -- H[(数据记录1)] D -- I[(数据记录2)] E -- J[(数据记录3)] E -- K[(数据记录4)]2.2 索引类型对比索引类型特点适用场景BTree有序支持范围查询主键、普通索引Hash无序等值查询快唯一键、等值查询Full-text全文搜索文本搜索R-Tree空间索引地理位置查询2.3 索引设计原则单表索引设计示例-- 用户表 CREATE TABLE users ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, phone VARCHAR(20), status TINYINT DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 唯一索引 UNIQUE INDEX uk_email (email), -- 普通索引 INDEX idx_username (username), INDEX idx_status_created_at (status, created_at) );复合索引设计原则最左前缀原则复合索引(a, b, c)可以加速a、a,b、a,b,c的查询选择性原则索引列的区分度越高越好排序原则ORDER BY 列应放在索引末尾三、查询优化实战3.1 使用EXPLAIN分析查询EXPLAIN ANALYZE SELECT u.id, u.username, o.order_no, o.amount FROM users u JOIN orders o ON u.id o.user_id WHERE u.status 1 AND o.created_at 2024-01-01 ORDER BY o.created_at DESC LIMIT 100;EXPLAIN输出解读字段含义优化关注点id查询序列号子查询执行顺序select_type查询类型ALL/SUBQUERY/DERIVEDtable表名避免全表扫描type访问类型ALL/index/range/const/systemkey使用的索引NULL表示未使用索引rows扫描行数越少越好Extra额外信息Using index/Using where/Using filesort3.2 索引失效场景错误示例1索引列参与计算-- 索引失效 SELECT * FROM orders WHERE YEAR(created_at) 2024; -- 正确写法 SELECT * FROM orders WHERE created_at BETWEEN 2024-01-01 AND 2024-12-31;错误示例2LIKE通配符前置-- 索引失效 SELECT * FROM users WHERE username LIKE %zhang; -- 正确写法使用全文索引 SELECT * FROM users WHERE MATCH(username) AGAINST(zhang);错误示例3OR条件未全部使用索引-- 索引可能失效 SELECT * FROM orders WHERE user_id 1 OR amount 100; -- 正确写法拆分查询 SELECT * FROM orders WHERE user_id 1 UNION SELECT * FROM orders WHERE amount 100;3.3 优化JOIN查询-- 优化前小表驱动大表 SELECT * FROM small_table s JOIN large_table l ON s.id l.small_id; -- 使用STRAIGHT_JOIN强制连接顺序 SELECT STRAIGHT_JOIN * FROM small_table s JOIN large_table l ON s.id l.small_id;3.4 分页查询优化-- 低效写法OFFSET过大导致扫描大量数据 SELECT * FROM orders ORDER BY id LIMIT 100000, 10; -- 优化写法利用覆盖索引 SELECT o.* FROM orders o JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 10 ) tmp ON o.id tmp.id;四、数据库配置优化4.1 MySQL关键参数调优# my.cnf 关键配置 # InnoDB缓冲池大小建议设为物理内存的50-70% innodb_buffer_pool_size 8G # 日志文件大小建议256M-1G innodb_log_file_size 1G innodb_log_buffer_size 64M # 查询缓存MySQL 8.0已移除 query_cache_type OFF # 连接数 max_connections 1000 wait_timeout 60 interactive_timeout 60 # 排序与连接缓冲 sort_buffer_size 2M join_buffer_size 2M # 临时表 tmp_table_size 64M max_heap_table_size 64M4.2 InnoDB优化配置# InnoDB配置 innodb_flush_log_at_trx_commit 1 # ACID级别 innodb_flush_method O_DIRECT # 绕过操作系统缓存 innodb_autoinc_lock_mode 2 # 并发插入优化 innodb_file_per_table 1 # 独立表空间 innodb_stats_on_metadata 0 # 关闭元数据统计五、高级优化技巧5.1 分区表优化-- 创建分区表 CREATE TABLE orders ( id BIGINT PRIMARY KEY, order_no VARCHAR(64), user_id BIGINT, amount DECIMAL(10,2), created_at DATETIME ) PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202312 VALUES LESS THAN (TO_DAYS(2024-01-01)), PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)) ); -- 查询时自动使用分区裁剪 SELECT * FROM orders WHERE created_at 2024-01-01;5.2 读写分离配置Configuration public class DataSourceConfig { Bean Primary public DataSource masterDataSource() { return createDataSource(master); } Bean public DataSource slaveDataSource() { return createDataSource(slave); } Bean public DataSource routingDataSource( Qualifier(masterDataSource) DataSource master, Qualifier(slaveDataSource) DataSource slave) { MapObject, Object targetDataSources new HashMap(); targetDataSources.put(DataSourceType.MASTER, master); targetDataSources.put(DataSourceType.SLAVE, slave); RoutingDataSource routing new RoutingDataSource(); routing.setTargetDataSources(targetDataSources); routing.setDefaultTargetDataSource(master); return routing; } }5.3 缓存策略优化Service public class OrderService { Autowired private RedisTemplateString, Order redisTemplate; Autowired private OrderRepository orderRepository; private static final String CACHE_PREFIX order:; private static final int CACHE_TTL_MINUTES 30; Cacheable(value orders, key #orderId) public Order getOrder(Long orderId) { return orderRepository.findById(orderId).orElse(null); } CacheEvict(value orders, key #order.id) public Order updateOrder(Order order) { return orderRepository.save(order); } public ListOrder getOrdersByUserId(Long userId) { String cacheKey CACHE_PREFIX user: userId; ListOrder cached (ListOrder) redisTemplate.opsForValue().get(cacheKey); if (cached ! null) { return cached; } ListOrder orders orderRepository.findByUserId(userId); redisTemplate.opsForValue().set(cacheKey, orders, CACHE_TTL_MINUTES, TimeUnit.MINUTES); return orders; } }六、性能监控与诊断6.1 慢查询日志配置slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 16.2 常用诊断SQL-- 查看当前正在执行的查询 SHOW PROCESSLIST; -- 查看慢查询日志摘要 SELECT COUNT(*) as total, SUM(IF(query_time 1, 1, 0)) as slow_count, AVG(query_time) as avg_time FROM sys.session; -- 查看索引使用情况 SELECT OBJECT_NAME, INDEX_NAME, COUNT(*) as usage_count FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL ORDER BY usage_count DESC; -- 查看未使用的索引 SELECT t.TABLE_NAME, i.INDEX_NAME FROM INFORMATION_SCHEMA.TABLES t JOIN INFORMATION_SCHEMA.STATISTICS i ON t.TABLE_NAME i.TABLE_NAME LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage u ON i.TABLE_NAME u.TABLE_NAME AND i.INDEX_NAME u.INDEX_NAME WHERE u.INDEX_NAME IS NULL AND t.TABLE_TYPE BASE TABLE;6.3 性能优化检查表索引优化 - [ ] 检查是否存在全表扫描typeALL - [ ] 检查是否存在Using filesort - [ ] 检查是否存在Using temporary - [ ] 验证复合索引最左前缀原则 - [ ] 删除未使用的索引 配置优化 - [ ] innodb_buffer_pool_size 配置合理 - [ ] 日志文件大小配置合理 - [ ] 连接数配置满足需求 - [ ] 临时表大小限制合理 查询优化 - [ ] SQL避免SELECT * - [ ] JOIN条件使用索引列 - [ ] WHERE条件避免函数包装索引列 - [ ] 分页查询优化 - [ ] 复杂查询拆分为多个简单查询七、总结数据库性能优化是一个系统性工程需要从索引设计、查询优化、配置调优多个维度入手。通过合理的索引设计、高效的SQL编写和恰当的参数配置可以显著提升数据库的性能表现。同时建立完善的性能监控体系能够及时发现并解决性能问题。参考资料MySQL官方文档https://dev.mysql.com/doc/High Performance MySQL: https://www.oreilly.com/library/view/high-performance-mysql/9781449332471/Percona Toolkit: https://www.percona.com/software/database-tools/percona-toolkit