logo标签列表

MySQL性能-字符串 vs 整数

mysqlsqldatabaseperformancemysql-workbench
6

我发现了MySQL的一种非常奇怪的行为，我无法解释。

这是一个不太复杂的查询：

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

列Enricher3State、Enricher4State、Enricher5State和Enricher9State具有索引，并且是int(11)数据类型。

现在我尝试将这些Enricher[x]State更改为字符串：

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

每个常识都会认为字符串变量应该表现得相同，或者更慢，因为列的数据类型是整数！

但显然情况并非如此！

整数表示法的查询（第一个）：7.23048825秒

字符串表示法的查询（最后一个）：5.22188450秒

正如您所看到的，尽管查询成本在两种情况下都相同，但性能差异巨大。

我绝对不知道这种差异是如何发生的 - 如果这意味着我应该改变所有在我的项目中使用字符串表示法的查询......

我正在使用MySQL版本5.7.10

根据您的评论，我已停用所有写入或读取数据库的服务，并重复了实验。

A）整数表示法：

SET profiling=0;
SET profiling=1;

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

  SHOW PROFILES;

每个查询的执行时间：

  • 6.42429325
  • 5.95059900
  • 6.34392825
  • 6.53041775
  • 6.69593450

B) 字符串表示法：

SET profiling=0;
SET profiling=1;

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

  SHOW PROFILES;

执行时间：

  • 5.07188875
  • 4.90356250
  • 4.86164300
  • 4.48403375
  • 5.06533725

显然可以看出，字符串表示法仍然更快！

我的团队中的其他开发人员也注意到了同样的行为，所以我可以排除自己暂时的愚蠢……

1
很可能是InnoDB缓存在起作用。再试一次，这次连续运行每个查询两次。丢弃第一个结果，报告每个查询的第二个结果。也就是说，运行整数查询、整数查询（报告此查询），字符串查询、字符串查询（报告此查询）。此外，如果结果仍然有多达4个不同的值，则仍然丢弃第一个，并对每个查询的剩余3个结果取平均值。 - Willem Renzema
1
此外，数据库中不应有其他活动，以确保真正地进行苹果与苹果的比较。 - Willem Renzema
我已根据您的输入修改了测试 - 但结果仍然相同。 - Andreas
在查询语句前面输入 EXPLAIN 时，会显示什么信息？ - verhie
4个回答
4

由于这些字段被索引，你使用了 OR 条件，并且查询中有整数常量作为条件，MySQL 可能会花费时间进行交叉索引连接计算，然后进行表扫描。使用字符串常量时，MySQL 不会考虑索引，而是直接进行表扫描。

当许多字段上具有索引并且这些字段用于 OR 条件时，这并不是 MySQL 的优势，而是额外的工作量。

OR 条件不能保证所涉及字段的索引，经常在“1,2,3,4”字段上建立索引对表格是不利的。应该将这些字段分离到一个单独的表格中。

附加：运行 EXPLAIN 命令，如果你看到被考虑键列表中的索引包括"1,2,3,4" 字段，那么 MySQL 正在花费时间进行这个操作。

4
考虑到Sergiy Tytarenko的回答，我已经删除了Enricher[x]State列上的索引。
整数表示法的执行时间为:
4.93739900 5.01461550 5.05932075 5.02891175 5.02525075
字符串表示法的执行时间为:
5.04365650 5.07545950 5.12358825 5.14665200 5.15426525
现在执行时间几乎相同。因此，在具有与OR相关联的多个列上具有多个索引时，应该小心处理。
看起来我意外地发现了一个不错的解决方法（除了删除索引），通过将整数转换为字符串...
是的，这是一个不错的解决方法，但您必须依赖开发人员遵循它，并且您的框架允许在准备查询时使用引号。在其他情况下（例如单个条件或AND条件），为整数常量使用引号将会带来很大的伤害。考虑到表格上“1,2,3,4”字段的常见原因，这些字段不应该被索引（而且通常您不能仅仅重构表格以避免使用它们）。 - Sergiy Tytarenko
个人而言，我会尝试以不同的方式编写查询，避免使用“OR”连接谓词，因为MySQL优化器倾向于为具有“OR”连接谓词的查询生成不太优化的执行计划。 - spencer7593
0

假设您已经执行了多次每个查询，并且丢弃了第一次执行的结果，我们确实看到了平均执行时间上的显著差异。

性能差异很可能是由于执行计划的不同造成的。

我建议仔细查看两个查询的 EXPLAIN EXTENDED 输出。很可能执行计划在某些方面有所不同（使用哪些索引、操作顺序等）。

我的观察是... MySQL 查询优化器和带有 OR 条件的查询... 查询计划并不是最优的。为了获得更好的性能，我通常会将查询拆分并使用 UNION ALL 集合操作。

对于获取“计数”，我倾向于像这样编写查询：

  SELECT SUM(2 IN (enr.enricher3state,
                   enr.enricher4state,
                   enr.enricher5state,
                   enr.enricher9state))
    FROM incidents.incidents inc
    JOIN incidents.enrichment enr 
      ON enr.parenttableid = inc.id
   WHERE inc.id <= 606734 
     AND inc.id >= 1

我会确保有一个覆盖索引可用，例如：

ON enrichment (parenttableid, enricher3state, enricher4state,
                              enricher5state, enricher9state)

（或者任何以parenttableid作为主列的索引，同时包括其他四个列）

然后我会检查EXPLAIN EXTENDED输出和性能。

0

与数字的比较

char = 123   -- slow because it converts the char to numeric; can't use index
char = '123' -- fine
int = 123    -- fine
int = '123'  -- fine - because '123' is converted to numeric up front

底线：引用常量总是安全的。

OR

OR 本质上是不可优化的。然而，以下方法可能会产生相同的效果，但速度更快...

在模式设计中的一个通用规则是：“不要将一组事物分散到列中。” 相反，创建另一个表并在它们之间建立1：多关系可能是性能最佳的解决方案。

请使用JOIN ... ON ...语法，而不是“commajoin”。

分析

5.6.7说：“SHOW PROFILE和SHOW PROFILES语句。使用Performance Schema代替；请参阅MySQL Performance Schema。”

索引

对于低基数列（例如我预计的Enricher3State），很少有索引是有用的。

IN vs OR

2 IN (...)与..=2 OR ..=2 OR... - 这些可能没有太大区别。不能使用任何索引; 两者都涉及一些复杂性。

更多信息

需要查看两个表的SHOW CREATE TABLE。
需要查看EXPLAIN SELECT ...。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接