php - MySQL数据库行数优化

Question

我有一个包含大量数据的 MySQL (5.6.26) 数据库，我在表连接时遇到 COUNT 选择问题。

执行此查询大约需要 23 秒:

SELECT COUNT(0) FROM user
LEFT JOIN blog_user ON blog_user.id_user = user.id
WHERE email IS NOT NULL
AND blog_user.id_blog = 1

表 user 是 MyISAM，包含用户数据，如 id、电子邮件、姓名等...

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT '',
  `hash` varchar(100) DEFAULT NULL,
  `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`) USING BTREE,
  UNIQUE KEY `email` (`email`) USING BTREE,
  UNIQUE KEY `hash` (`hash`) USING BTREE,
  FULLTEXT KEY `email_full_text` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=5728203 DEFAULT CHARSET=utf8

表blog_user 是InnoDB 并且只包含id、id_user 和id_blog(用户可以访问多个博客)。 id 是 PRIMARY KEY 并且在 id_blog、id_user 和 id_blog-id_user 上有索引。

CREATE TABLE `blog_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_blog` int(11) NOT NULL DEFAULT '0',
  `id_user` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_blog_user` (`id_blog`,`id_user`) USING BTREE,
  KEY `id_user` (`id_user`) USING BTREE,
  KEY `id_blog` (`id_blog`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5250695 DEFAULT CHARSET=utf8

我删除了所有其他表，没有其他连接到MySQL服务器(测试环境)。

到目前为止我发现了什么:

1. 当我从用户表中删除一些列时，查询持续时间更短(例如每个删除的列 2 秒)
2. 当我从用户表中删除所有列(id 和 email 除外)时，查询持续时间为 0.6 秒。
3. 当我将 blog_user 表也更改为 MyISAM 时，查询持续时间为 46 秒。
4. 当我将用户表更改为 InnoDB 时，查询持续时间为 0.1 秒。

问题是为什么 MyISAM 执行命令这么慢？

Answer 1

首先，对您的查询进行一些评论(在稍作修改之后):

SELECT COUNT(*)
FROM user u LEFT JOIN
     blog_user bu
     ON bu.id_user = u.id
WHERE u.email IS NOT NULL AND bu.id_blog = 1;

表别名有助于更轻松地编写和读取查询。更重要的是，您有一个 LEFT JOIN，但是您的 WHERE 子句正在将它变成一个 INNER JOIN。所以，这样写:

SELECT COUNT(*)
FROM user u INNER JOIN
     blog_user bu
    ON bu.id_user = u.id
WHERE u.email IS NOT NULL AND bu.id_blog = 1;

差异很重要，因为它会影响优化器可以做出的选择。

接下来，索引将帮助这个查询。我猜 blog_user(id_blog, id_user) 和 user(id, email) 是最好的索引。

列数影响您的原始查询的原因是因为它执行大量 I/O。列越少，存储记录所需的页面就越少——查询运行得越快。适当的索引应该更好、更一致地工作。