数据库优化|性能提升：mysql索引方法与MySQL索引优化技巧详解

MySQL索引方法

1. 索引类型

   • B-Tree索引：默认类型，支持全值匹配、范围查询、前缀匹配。
   • 哈希索引：仅精确匹配，适用于内存表。
   • 全文索引（FULLTEXT）：文本内容搜索，支持关键词匹配。
   • 空间索引（SPATIAL）：地理数据查询。
   • 复合索引：多列组合索引，遵循最左前缀原则。

2. 索引创建语法

   

   CREATE INDEX idx_name ON table_name(column1, column2);
   ALTER TABLE table_name ADD INDEX idx_name(column);

3. 索引选择原则

   • 高频查询字段优先。
   • 区分度高（基数大）的列（如ID、手机号）。
   • 避免对频繁更新的列建索引。

MySQL索引优化技巧

1. 优化策略

   

   • 覆盖索引：查询列全部包含在索引中，避免回表。
   • 索引下推（ICP）：MySQL 5.6+特性，在存储引擎层过滤数据。
   • 前缀索引：对长文本/字符串截取前N字符建索引，节省空间。
   • 索引合并：优化器自动合并多个单列索引（需权衡效率）。

2. 避免索引失效场景

   • 列使用函数或运算（如WHERE YEAR(create_time) = 2025）。
   • 隐式类型转换（如字符串列用数字查询）。
   • 模糊查询以通配符开头（LIKE '%abc'）。
   • 违反最左前缀原则的复合索引查询。

3. 性能监控与调优

   

   • 使用EXPLAIN分析执行计划，关注type（访问类型）、key（使用索引）、rows（扫描行数）。
   • 慢查询日志定位低效SQL。
   • 定期维护：ANALYZE TABLE更新统计信息，OPTIMIZE TABLE减少碎片。

高级技巧

• 自适应哈希索引（InnoDB）：自动为频繁访问的索引页构建哈希索引。
• 索引跳跃扫描（MySQL 8.0+）：即使复合索引非最左列，也可能利用索引。
• 不可见索引（MySQL 8.0+）：标记索引为“不可见”测试删除影响。

综合了MySQL官方文档及行业实践,适用于【2025-07】前的版本（如MySQL 5.7/8.0），实际优化需结合业务场景和数据特征。