我发现了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
显然可以看出,字符串表示法仍然更快!
我的团队中的其他开发人员也注意到了同样的行为,所以我可以排除自己暂时的愚蠢……