假设在列上应用正确的索引,最好使用这个SQL代码!!
假设常量是一个文本字段的输入!!
select ...
from .....
where lower(column) like 'Constant%' or lower(column) like '%Constant%'
比?
select ...
from .....
where lower(column) like '%Constant%'
在第一个代码中,我尝试匹配一个"常数"使用喜欢,但使用一个索引尝试幸运地找到一个匹配,后来我尝试做一个完整的匹配!!
我只想让我的表现不下降!我的意思是,如果两个查询在同一时间运行,或者如果查询有时可以得到性能升级对我来说是OK的
我使用lower,因为我们使用DEFAULT CHARSET=utf8 COLLATE=utf8_bin
我创建了一个小表格:
create table dotdotdot (
col varchar(20),
othercol int,
key(col)
);
我在一个类似于你展示的查询上做了一个EXPLAIN:
explain select * from dotdotdot where lower(col) = 'value'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dotdotdot
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
注意type: ALL
,这意味着它不能在col
上使用索引。通过使用lower()
函数,我们破坏了MySQL使用索引的能力,它必须诉诸于表扫描,对每一行求值表达式。当您的表变大时,这将变得越来越昂贵。
这是没有必要的!字符串比较在默认排序中不区分大小写。因此,除非有意使用区分大小写的排序规则或二进制排序规则声明表,否则最好跳过lower()
函数调用,这样就可以使用索引。
的例子:
explain select * from dotdotdot where col = 'value'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dotdotdot
partitions: NULL
type: ref
possible_keys: col
key: col
key_len: 23
ref: const
rows: 1
filtered: 100.00
Extra: NULL
type: ref
表示使用非唯一索引
也比较使用通配符进行模式匹配。这也挫败了索引的使用,并且它必须执行表扫描。
explain select * from dotdotdot where col like '%value%'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dotdotdot
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
使用这样的通配符进行模式匹配是非常低效的!
您需要使用全文索引。
你可能会喜欢我的演讲全文搜索下拉框和视频:https://www.youtube.com/watch?v=-Sa7TvXnQwY
在另一个答案中,你问使用OR
是否有帮助。不。
explain select * from dotdotdot where col like 'value%' or col like '%value%'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dotdotdot
partitions: NULL
type: ALL
possible_keys: col
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
注意,优化器将col索引标识为可能的键,但最终决定不使用它(key: NULL
)。
不,这不会显著提高查询性能。MySQL将匹配WHERE子句"每行",因此在继续到下一行之前检查所有条件。如果存在匹配,首先命中索引可能会略微提高性能,但是在第一个条件不匹配的情况下,这种增益很可能会被双重计算所抵消。
可以帮上忙的是:
1)使用like 'Constant%'
2)使用like '%Constant%'
在这种情况下,如果有匹配,第一个可以加速。但是,您很可能会受到开销的影响,并且两个查询的性能比一个查询更差。
而且,LIKE操作符不区分大小写。因此,lower(column)
是不必要的。
同时,如果您希望您的数据主要在第一个条件上匹配,而很少在第二个条件上匹配,则YES,这将导致增加,因为第二个条件没有计算。
使用LOWER()
可以防止使用索引。因此,切换到..._ci
排序并放弃LOWER
。
考虑 FULLTEXT
索引;它比 LIKE
%…快得多。前者速度快;后者是一个全表扫描。OR
几乎总是性能杀手。