MySQL索引优化和查询优化

MySQL索引是数据库性能优化的关键，理解索引原理和优化方法对于提高数据库性能至关重要。

核心问题

索引的类型

1. B+树索引：最常用的索引类型
2. 哈希索引：Memory引擎支持
3. 全文索引：用于全文搜索
4. 空间索引：用于地理数据

B+树索引原理

• B+树是多路平衡查找树
• 所有数据存储在叶子节点
• 非叶子节点只存储索引键
• 叶子节点之间通过指针连接

索引优化原则

1. 最左前缀原则

   • 联合索引按照最左列开始匹配
   • (a, b, c)索引可以匹配a、a,b、a,b,c

2. 避免在索引列上使用函数

   • WHERE YEAR(date) = 2024 ❌
   • WHERE date >= '2024-01-01' ✅

3. **避免使用SELECT ***

   • 只查询需要的列
   • 减少数据传输

查询优化

1. EXPLAIN分析

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

2. 慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

3. 索引优化建议

• 为WHERE子句中的列创建索引
• 为JOIN的列创建索引
• 避免创建过多索引
• 定期分析表，更新统计信息

常见面试题

1. 索引为什么能提高查询速度？

   • 减少全表扫描
   • B+树结构提高查找效率
   • 索引覆盖避免回表

2. 什么情况下索引会失效？

   • 使用函数或表达式
   • 类型不匹配
   • 使用LIKE '%xxx'
   • 使用NOT、!=、<>

3. 如何优化分页查询？

   • 使用索引覆盖
   • 使用延迟关联
   • 使用游标分页