第九天

最左前缀法则的原理是什么?

最左前缀法则（Leftmost Prefix Rule）是应用于数据库索引（尤其是B-Tree索引）中的一个重要原则。这个原则描述了如何在复合索引（也称为联合索引）中使用索引列。

复合索引是基于多个列创建的索引，例如，我们有一个索引 (column1, column2, column3)，它是一个基于三个列的复合索引。

最左前缀法则原理：

• 当查询条件中包含复合索引最左边的列时，索引可以被有效地利用。
• 如果查询条件进一步包含了复合索引中紧随其后的列，索引依然可以继续发挥作用。
• 但是一旦查询条件跳过了复合索引中的某一列，那么索引在此之后的列上将无法提供帮助。

例如，对于上述索引 (column1, column2, column3)：

• 查询 WHERE column1 = value AND column2 = value2 可以利用索引。
• 查询 WHERE column1 = value 也可以利用索引。
• 但是查询 WHERE column2 = value2 或 WHERE column2 = value2 AND column3 = value3 无法利用此索引。

简单来说，就是查询条件必须从复合索引最左边的列开始，并且尽可能包含更多的索引列，才能充分利用索引进行快速查找。这一原则源于B-Tree索引的结构特点，索引的分支节点是按照索引列的顺序排列的，查询只能从根节点开始沿着索引路径逐步向下匹配。

id, name两个字段, where name like %abc%, 有1000万数据, 怎么进行优化?

对于类似 WHERE name LIKE '%abc%' 的查询，由于%abc%是一个前缀模糊匹配，MySQL的B-Tree索引在这种情况下通常无法有效利用，因为前缀匹配中的%在LIKE谓词左边导致索引失效。对于这种情况，有以下几种优化策略：

1. 全文索引（Full-Text Index）：

   • 如果你的MySQL版本支持全文索引（FULLTEXT），并且name字段内容适合全文索引，可以为name字段创建全文索引，然后使用MATCH AGAINST语句代替LIKE，例如：

     SELECT id, name FROM your_table WHERE MATCH(name) AGAINST('+abc*' IN BOOLEAN MODE);

     注意：全文索引更适合对自然语言文本的搜索，对于短关键词可能不是最佳方案。

2. 索引前缀：

   • 如果名字的结构允许，例如总是以某种固定模式开头，而模糊查询通常在末尾，可以考虑为name字段建立前缀索引，例如：

     CREATE INDEX idx_name_prefix ON your_table (name(3)); -- 假设名字的前3个字符是固定的

     然后优化查询语句，使其能在索引中查找，但这需要具体情况具体分析，可能并不能适用所有情况。

3. 数据预处理：

   • 对于固定格式的模糊查询，可以尝试在数据导入或者更新的时候，额外创建一个用于搜索的字段，并填充便于搜索的格式，例如倒排索引或关键字提取，然后对此字段建立索引。

4. 分库分表：

   • 如果数据量非常大，可以考虑分库分表，将数据分散在多个小表中，每个表的数据量减少后，即使是全表扫描也相对较快。

5. 缓存优化：

   • 对于经常查询的关键词，可以考虑使用缓存技术，如Redis等，存储部分结果集，减轻数据库压力。

6. 查询优化：

   • 如果查询结果不需要完全精确，可以尝试放宽查询条件，例如使用BINARY函数使匹配更严格，或者改为查询以abc开头的记录（name LIKE 'abc%'），这样可以利用索引。

7. 硬件升级或分布式数据库：

   • 在硬件资源允许的情况下，增加内存和优化磁盘I/O，或者迁移到支持分布式查询和大规模数据处理的数据库系统。

8. 定期批量处理：

   • 如果此类查询不需实时结果，可以将此类查询任务转化为定时任务，定期批量处理，减轻高峰期的压力。

针对这种模糊查询，最重要的是评估业务需求和数据特性的实际情况，然后采取相应的优化手段。在大多数情况下，最好避免在大规模数据上执行全表扫描的模糊查询，如果不可避免，应当寻求替代方案或者改进数据结构与查询方式。