第八十八天

最左前缀法则?

最左前缀法则是在数据库索引设计领域中特别针对MySQL和其他支持类似B+树索引结构的数据库管理系统的一个重要原则。它主要应用于联合索引（也称复合索引或多列索引）的设计和使用。

最左前缀法则的含义：

• 当一个联合索引包含了多个列（例如索引为 (column1, column2, column3) 时），索引可以被用来加速查询条件中涉及从最左边起始的任意列组合的查询。
• 查询时，只要查询条件中包含索引的第一列（最左列），就能利用该索引进行高效搜索。
• 如果查询条件中包含了第一列和紧接着的第二列，那么索引依然可以被充分利用。
• 以此类推，直至索引的最后一列。但如果查询跳过了索引中的某个列，则从跳过的列及其右侧的列都无法利用索引进行优化。

举例说明：

• 对于一个索引 (profession, age, status) ，以下查询可以利用索引：
  • WHERE profession = 'Engineer'
  • WHERE profession = 'Engineer' AND age = 30
  • WHERE profession = 'Engineer' AND age > 25 AND status = 'Active'
• 但以下查询则无法充分利用上述索引：
  • WHERE age = 30（忽略了最左列）
  • WHERE age > 25 AND status = 'Active'（同样忽略了最左列）

另外，需要注意的是：

• 如果查询的第一个列是范围查询（如 age > 25），那么对该列后面的列的查询将只能利用到部分索引（即最左列后的列直到范围查询的那一列为止）。
• 创建联合索引时，应根据业务需求和查询模式，尽可能将最常用于查询条件且能最好地过滤数据的列放在索引的最左边，以最大化索引的效用。

订单列表, 越往后翻越慢, 原因是什么? 如何进行优化?

订单列表在数据库查询时，越往后翻页越慢，主要原因可能有以下几个方面：

1. 索引不当：

   • 如果数据库表没有合理地建立索引，尤其是针对分页查询时经常使用的排序字段（如订单时间、订单ID等），查询优化器可能无法有效利用索引进行快速定位，导致随着页码增加，数据库需要扫描更多的行才能获取所需的结果。

2. 全表扫描：

   • 若查询语句没有利用合适的索引，随着翻页深入，数据库不得不进行全表扫描或者扫描更大的数据块，性能随页码递增而下降。

3. OFFSET分页：

   • 使用LIMIT offset, limit形式的SQL分页查询时，MySQL等数据库在处理较大的offset值时效率较低，因为它必须先跳过前面大量的记录才能找到目标页的数据。

4. 数据量过大：

   • 随着数据量的增长，即使是带有适当索引的表，在进行分页查询时，索引树的深度和宽度都在增长，特别是当查询位于数据集末尾时，可能需要遍历更多索引页或数据页。

5. 磁盘I/O瓶颈：

   • 当数据库不得不从磁盘读取大量数据时，特别是在硬盘I/O性能较低的情况下，后续页的查询速度会明显下降。

优化方案：

• 合理建立索引：

  • 根据查询条件和排序字段，建立覆盖索引（覆盖索引可以直接从索引中获取查询所需的所有列，无需回表），尤其是针对分页查询经常使用的列。

• 优化分页查询：

  • 使用ROW_NUMBER()函数（适用于支持窗口函数的数据库如PostgreSQL，SQL Server等）或者MySQL 8.0及以上版本的OFFSET ... FETCH结合WITH子句（CTE）进行分页，或者利用临时变量存储上次查询的最大值，下次查询时直接从该最大值开始，减少OFFSET带来的性能损失。

• 分页大小控制：

  • 控制分页的大小，不要一次性加载过多数据，避免给数据库带来过大压力。

• 缓存策略：

  • 对于常用或固定范围的分页数据，可以采用缓存策略，将一部分数据存放在内存中，如Redis等高速缓存中。

• 数据库优化：

  • 数据库自身的参数调整，包括内存分配、索引碎片整理、合理的表分区策略等，以提升数据库整体性能。

• 设计合理的数据归档策略：

  • 对历史订单数据进行归档处理，把已经不需要实时查询的旧数据迁移到其他存储介质或独立的表，减轻主表的压力。

针对MySQL的具体优化，可以尝试使用LIMIT配合PRIMARY KEY或者唯一索引进行优化，尤其在数据量大的情况下，尽量避免无序的OFFSET分页查询。一种改进的方法是使用JOIN + WHERE条件配合PRIMARY KEY进行分页，或者在具备二级索引的情况下，利用索引进行倒序排序，然后正向取数，以此改善分页查询效率。