理解索引的本质
索引是数据库性能优化中最重要的工具。在没有索引的情况下，数据库需要扫描整个表来查找符合条件的记录，这在数据量大时会非常缓慢。索引类似于书籍的目录，通过索引可以快速定位到目标数据的位置，避免全表扫描。
MySQL 中最常用的索引结构是 B+ 树。B+ 树是一种平衡多路查找树，所有数据都存储在叶子节点，非叶子节点只存储索引键值。叶子节点之间通过指针连接，支持高效的范围查询。这种结构保证了无论数据量多大，查找的时间复杂度始终保持在 O(log n) 级别，而且由于数据集中存储在叶子节点，顺序访问的性能也很好。
索引设计原则
设计索引需要综合考虑查询模式和数据特点。首先要分析系统中的高频查询，为 WHERE 子句、JOIN 条件、ORDER BY 子句中涉及的列创建索引。但索引不是越多越好，每个索引都会占用存储空间，并且在数据插入、更新、删除时需要维护，过多的索引会影响写入性能。
复合索引的列顺序很重要，遵循最左前缀原则。将选择性高的列放在前面，选择性指的是不重复值的比例。例如，用户性别只有两个值，选择性很低；而用户手机号几乎每个用户都不同，选择性很高。在设计复合索引时，高选择性的列应该放在左边，这样索引能更快地过滤掉不符合条件的记录。
执行计划分析
EXPLAIN 命令是分析查询性能的利器。通过执行计划可以了解 MySQL 是如何执行查询的，包括使用了哪个索引、扫描了多少行、是否进行了排序等信息。type 列显示了表的访问类型，从好到差依次是 system、const、eq_ref、ref、range、index、ALL，应该尽量避免 ALL 类型即全表扫描。
key 列显示实际使用的索引，如果显示为 NULL 说明没有使用索引。rows 列是 MySQL 估算需要扫描的行数，这个值越小越好。Extra 列包含了很多有用的信息，如 Using index 表示使用了覆盖索引，Using filesort 表示需要额外的排序操作，Using temporary 表示使用了临时表。这些额外操作通常意味着性能可以进一步优化。
查询优化技巧
避免在索引列上使用函数或进行计算，这会导致索引失效。例如，WHERE YEAR(create_time) = 2024 无法使用 create_time 上的索引，应该改写为 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'。同样，前导通配符的 LIKE 查询如 LIKE '%keyword' 也无法使用索引，应该尽量避免或考虑全文索引。
JOIN 操作的优化同样重要。确保关联字段上有索引，小表驱动大表可以减少扫描次数。对于复杂的多表关联查询，可以考虑分解为多个简单查询，在应用层进行数据组合。虽然这增加了查询次数，但每个查询都更简单、更容易缓存，在某些场景下反而能提升整体性能。
慢查询分析
开启慢查询日志是性能优化的第一步。通过配置 slow_query_log 参数，MySQL 会记录执行时间超过阈值的查询。使用 mysqldumpslow 或 pt-query-digest 等工具可以分析慢查询日志，找出需要优化的查询。关注那些执行频率高且单次耗时较长的查询，优化它们能带来最大的收益。
对于频繁执行的热点查询，可以考虑使用查询缓存或应用层缓存。但需要注意 MySQL 8.0 已经移除了查询缓存功能，因为在高并发写入场景下，查询缓存的失效和维护开销往往超过其带来的收益。推荐使用 Redis 等外部缓存系统来缓存热点数据。
表结构与分区
合理的表结构设计是性能优化的基础。选择合适的数据类型，使用最小的能够满足需求的类型。例如，存储状态值使用 TINYINT 而不是 INT，IP 地址使用 INT UNSIGNED 而不是 VARCHAR。字段尽量设置为 NOT NULL，NULL 值需要额外的存储空间和处理逻辑。
对于数据量很大的表，分区可以有效提升查询性能。按时间范围分区是最常见的策略，比如按月分区存储日志数据，查询特定月份的数据时只需要扫描对应的分区。分区还能简化数据归档，直接删除旧分区比 DELETE 语句更快更高效。对于超大规模的数据，还需要考虑分库分表等更复杂的架构方案。