极客教程MySQL “like”和通配符的查询优化
在MySQL中，使用LIKE和通配符的查询可以实现模糊搜索。但是，经常出现在大型数据集上使用LIKE通配符搜索时会导致非常慢的查询速度问题。为了优化此类型的查询，我们需要注意许多方面，包括表结构，查询语句，和其他配置选项。
阅读更多：MySQL 教程
表结构优化
- 索引
索引是提高SELECT语句速度的重要因素。在LIKE查询中，可以为匹配字段添加索引。该索引将按照查询条件的第一个字符进行匹配，并选择索引中在该字符范围内的记录。
例如：假设有一个表格t1，包含name列，现在我们正在使用like模糊搜索jack这个关键字。如果我们为name列添加了前缀索引，它将按照’j’，’a’，’c’，’k’，’的字符顺序排列。然后，MySQL只需要扫描索引的前缀，而不需要扫描整个表。
对于以下查询：
SELECT * FROM t1 WHERE name LIKE '%jack%'
如果没有索引，
SELECT * FROM t1 WHERE name = 'jack'
使用前缀索引，会给查询一个很大的性能提升。此时，查找以J开头的记录，直到’j%’的范围就可以停止，避免了对整个表进行扫描。
同时，我们还可以使用全文索引来提高LIKE和通配符查询的性能。这个索引类型与前缀索引不同，它从列中提取关键词并将其存储在索引中，可以匹配关键字和单词的任意部分。
- 数据类型
如果我们搜索的是数字类型或日期类型，使用LIKE会降低查询的速度。在这些情况下，最好使用等于或不等于的操作符号。
- 数据规范化
在多个表中使用相同的值（通常是字符串类型）时，可以考虑使用数据规范化。这意味着将具有相同值的行移动到另一个表中，将值替换为唯一的ID。通过这种方式，我们可以减少循环扫描操作的数量，并极大地提高查询速度。
查询语句优化
- 通配符
使用通配符时，在字符前面使用成功搜索，但在前面使用通配符会使索引无效。 这是因为MySQL将无法使用索引上的匹配，而是将扫描整个表格。
例如，以下查询将导致索引失效，并强制MySQL使用表扫描：
SELECT * FROM t1 WHERE name LIKE '%ack'
- 语法
使用更复杂的语法可能会导致优化器具有困难。保持查询尽可能短，简单，并使用明确语法有助于优化程序。
- 分裂调整
如果我们使用通配符搜索一个很长的字符串（比如整个的段），最好将其分成多个子字符串和多个LIKE子句。
其他配置选项
- 优化查询缓存
在查询缓存命中率非常高的情况下，服务器的负载会大大减少。然而，当使用通配符的查询时，查询缓存将不起作用。在实践中，对于具有大量行或大型文本字段的表，查询缓存将无法用于适当的性能提升。
- 内存设置
可以调整内存设置来优化查询速度。例如，可以提高read_buffer_size和read_rnd_buffer_size的值，以提高查询读取和排序的性能。
- 分区
对于存储非常大型数据集的表格，可以考虑将其分区。通过这种方式，可以将表格拆分为更小的部分，这样MySQL只需要扫描必要的分区，而不需要扫描整个表格。
总结
在MySQL中使用LIKE和通配符的查询可以实现模糊搜索，但它们可能会极大地影响查询的效率。通过优化表结构、查询语句和其他配置选项，我们可以使查询更有效地执行。同时，需要意识到使用通配符可能使索引失效，并考虑使用分区来处理大型数据集。通过这些技巧，我们可以更好地优化MySQL的性能，并提高数据库查询的速度。