MySQL逐字节比较，哪个更快?二进制 vs bin_collat​​e

MySQL byte-for-byte comparison, which is faster? binary vs bin_collate(MySQL逐字节比较，哪个更快?二进制 vs bin_collat​​e)

问题描述

假设我们有一个看起来像这样的表格:

Assume we have a table that looks like this:

create table t1(c1 varchar(x)collate utf8mb4_general_ci, index(c1))

要进行字节敏感的比较，我们基本上有两种方法(假设所有相关字符串都不有尾随空格，即它们都是padspace 兼容):

To do byte-sensitive comparisons, we basically have two ways (assume that all relevant strings do not have trailing spaces, i.e. they are all padspace-compliant):

select*from t1 where c1 ='test'collate utf8mb4_bin

select*from t1 where c1 = binary'test'

在考虑性能时应该首选哪个?

Which should be preferred when performance is of concern?

当使用非二进制字符排序的索引时，是否更快比较与二进制字符串a> 还是二进制整理?

When using an index of non-binary character collation, is it faster to compare with binary string or binary collation?

(向表中添加一个新列只是为了存储 c1 的二进制等效项对存储来说是一个很大的打击，而且不可能.)

(Adding a new column to the table just to store the binary equivalent of c1 is a big hit on storage and not possible.)

(P.S. 希望得到比较哈希和 btree 比较的答案，尽管我主要对 btree 比较感兴趣.)

(P.S. Would appreciate an answer that compares both hash and btree comparisons, although I'm primarily interested in btree comparison.)

推荐答案

由于表中有索引，所以二进制匹配使用二进制作为常量，而不是列.这将比您的两种选择都快.

As you have index in the table,for binary match use binary for constant,not to the column. This will be faster than both of your options.

select * from t1 where c1 = binary 'test'

回答你的问题是选项 1 在你做的地方会更快

Answer to you question is option 1 will be faster where you are doing

WHERE c1 collate utf8mb4_bin='test'

这篇关于MySQL逐字节比较，哪个更快?二进制 vs bin_collat​​e的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！