与大表进行简单连接时的 MySQL 性能问题

Question

我正在将一个 PHP 应用程序从 MSSQL Server 迁移到 MySQL，但我遇到了一个简单的查询，MSSQL Server 持续 0.5 秒，MySQL 持续 50 秒。有任何想法吗？索引？服务器配置？ MySQL 服务器硬件等于或优于 MSSQL 服务器。

表结构

两者都是InnoDB:

CREATE TABLE `tb1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nec` INT(11),
    `start_date` DATETIME(6) NOT NULL,
    `end_date` DATETIME(6) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `IX_nec` (`nec`)
);

CREATE TABLE `tb2` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nec` INT(11) NOT NULL,
    `start_date` DATETIME(6) NOT NULL,
    `end_date` DATETIME(6) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `IX_nec` (`nec`)
);

table 很大。 Tb1 大约有 300.000 行，而 tb2 大约有 400.000 行。

选择

SELECT count(1)
FROM tb2
LEFT JOIN tb1 ON tb1.nec = tb2.nec

结果大约是 180.000.000。

这只是一个示例，最终目标是使用其他日期过滤器/交叉点等进行更大的查询。

解释计划

SQL Server 的执行计划:

配置、硬件、...

@@innodb_buffer_pool_size: 2147483648
选择版本(): 5.7.17-0ubuntu0.16.04.1
Profiling of the query (CSV 文件)
Procedure Analyse() (XLS文件)
my.cnf
系统:
VMware 虚拟平台
Intel(R) Xeon(R) CPU E5530 @ 2.40GHz
4GiB DIMM DRAM EDO
Ubuntu 16.04.2 LTS (Linux gt 4.4.0-66-generic x86_64)

Answer 1

这不是答案

我创建了 2 个包含随机数据的示例表(我知道它不一样)，每个表有 500000 行并测试您的查询。这将需要 1.1 秒。所以我几乎可以肯定这是配置或硬件问题。所以我会使用更多的信息。你能把查询的输出贴出来吗

1) 选择版本();

2) 这个的输出

SET PROFILING=ON;

SELECT count(1)
FROM tb2
LEFT JOIN tb1 USING(nec);

SHOW PROFILE ALL;
SET PROFILING=OFF;

3) 你的my.cnf

4) 关于硬件和操作系统的一些信息

你也可以检查这个查询。在我的服务器上只需要 500 毫秒

SELECT sum(IF(s IS NULL,1,s)) AS cnt
FROM tb2
LEFT JOIN 
  (SELECT DISTINCT nec, SUM(1) AS s FROM tb1 GROUP BY nec) tmp USING (nec) ;