mysql - 我可以使这个 mySQL 查询更快吗？

Question

我在 mysql-slow.log 中有以下条目:

# Time: 180506 21:57:03
# User@Host: mysqlserver[mysqlserver] @ localhost []
# Query_time: 88.963476  Lock_time: 0.000088 Rows_sent: 50  Rows_examined: 114197

SET timestamp=1525633023;

SELECT n1.full_name AS sender_full_name, s1.email AS sender_email, e.subject, e.body, 
e.attach, e.date, e.id, r.status, n2.full_name AS receiver_full_name,
s2.email AS receiver_email, r.basket 
FROM people_emails p 
JOIN email_routing r ON r.receiver_email_id = 3223 AND r.status = 2 
JOIN email e ON e.id = r.message_id 
JOIN people_emails s1 ON s1.id = r.sender_email_id 
JOIN people n1 ON n1.id = s1.people_id 
JOIN people_emails s2 ON s2.id = r.receiver_email_id 
JOIN people n2 ON n2.id = s2.people_id  
WHERE p.internal_user_id = 314 
ORDER BY e.date desc 
LIMIT 0, 50;

该查询的结果类似于:

 ----------------------------------------------------------------------------------------------------
 |sender_full_name|sender_email|subject|body| attach | date |  id  |status|receiver_full_name|basket| 
 ----------------------------------------------------------------------------------------------------
 |John Blow       |jb@corp.lan |Aloha  |Text|        |180506|856050|2     |Mary Johns        |1     |
 ----------------------------------------------------------------------------------------------------

这里是关于查询和使用的表的所有数据:

EXPLAIN SELECT n1.full_name AS sender_full_name, s1.email AS sender_email, 
e.subject, e.body, e.attach, e.date, e.id, r.status, n2.full_name AS receiver_full_name, 
s2.email AS receiver_email, r.basket, 'user777' FROM people_emails p 
JOIN email_routing r ON r.receiver_email_id = 3233 AND r.status = 2 
JOIN email e ON e.id = r.message_id 
JOIN people_emails s1 ON s1.id = r.sender_email_id 
JOIN people n1 ON n1.id = s1.people_id 
JOIN people_emails s2 ON s2.id = r.receiver_email_id 
JOIN people n2 ON n2.id = s2.people_id 
WHERE p.internal_user_id = 314 ORDER BY e.date desc LIMIT 0, 50; 

id  select_type table   type    possible_keys   key key_len     ref                     rows    Extra
1   SIMPLE      s2      const   PRIMARY         PRIMARY 4       const                   1       Using temporary; Using filesort
1   SIMPLE      n2      const   PRIMARY         PRIMARY 4       const                   1   
1   SIMPLE      p       ALL     NULL            NULL    NULL    NULL                    18631   Using where
1   SIMPLE      r       ALL     NULL            NULL    NULL    NULL                    899567  Using where; Using join buffer
1   SIMPLE      e       eq_ref  PRIMARY         PRIMARY 4       server.r.message_id     1   
1   SIMPLE      s1      eq_ref  PRIMARY         PRIMARY 4       server.r.sender_email_id1   
1   SIMPLE      n1      eq_ref  PRIMARY         PRIMARY 4       server.s1.people_id     1   



SHOW CREATE TABLE people_emails; 
CREATE TABLE `people_emails` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `nick` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `key_name` varchar(255) NOT NULL,
 `people_id` int(11) NOT NULL,
 `status` int(11) NOT NULL DEFAULT '0',
 `activity` int(11) NOT NULL,
 `internal_user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 FULLTEXT KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=22114 DEFAULT CHARSET=utf8

SHOW CREATE TABLE email_routing; 
CREATE TABLE `email_routing` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `message_id` int(11) NOT NULL,
 `sender_email_id` int(11) NOT NULL,
 `receiver_email_id` int(11) NOT NULL,
 `basket` int(11) NOT NULL,
 `status` int(11) NOT NULL,
 `popup` int(11) NOT NULL,
 `tm` int(11) NOT NULL,
 KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=987389 DEFAULT CHARSET=utf8

SHOW CREATE TABLE email; 
CREATE TABLE `email` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `subject` text NOT NULL,
 `body` text NOT NULL,
 `date` datetime NOT NULL,
 `attach` text NOT NULL,
 `attach_ondisk` text NOT NULL,
 `attach_dir` varchar(255) CHARACTER SET cp1251 DEFAULT NULL,
 `attach_subject` varchar(255) DEFAULT NULL,
 `attach_content` longtext,
 PRIMARY KEY (`id`),
 KEY `Index_2` (`attach_dir`),
 FULLTEXT KEY `path` (`attach_dir`)
) ENGINE=MyISAM AUTO_INCREMENT=856151 DEFAULT CHARSET=utf8

SHOW CREATE TABLE people; 
CREATE TABLE `people` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `fname` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `lname` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `patronymic` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `gender` tinyint(1) NOT NULL,
 `full_name` varchar(255) NOT NULL DEFAULT ' ',
 `category` int(11) NOT NULL,
 `people_type_id` int(255) DEFAULT NULL,
 `tags` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `job` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `post` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `profession` varchar(255) CHARACTER SET cp1251 DEFAULT NULL,
 `zip` varchar(16) CHARACTER SET cp1251 NOT NULL,
 `country` int(11) DEFAULT NULL,
 `region` varchar(10) NOT NULL,
 `city` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `address` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `address_date` date DEFAULT NULL,
 `inner` tinyint(4) NOT NULL,
 `contact_through` varchar(255) DEFAULT '',
 `next_call` date NOT NULL,
 `additional` text CHARACTER SET cp1251 NOT NULL,
 `user_id` int(11) NOT NULL,
 `changed` datetime NOT NULL,
 `status` int(11) DEFAULT NULL,
 `nick` varchar(255) DEFAULT NULL,
 `birthday` date DEFAULT NULL,
 `last_update_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `area` text NOT NULL,
 `reviewed_` tinyint(4) NOT NULL,
 `phones_old` text NOT NULL,
 `post_sticker` text NOT NULL,
 `permissions` int(120) NOT NULL DEFAULT '0',
 `internal_user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `most_used` (`category`,`status`,`city`,`lname`,`next_call`),
 KEY `registrars` (`category`,`status`,`contact_through`,`next_call`),
 FULLTEXT KEY `lname` (`lname`),
 FULLTEXT KEY `fname` (`fname`),
 FULLTEXT KEY `mname` (`patronymic`),
 FULLTEXT KEY `Full Name` (`full_name`)
) ENGINE=MyISAM AUTO_INCREMENT=415009 DEFAULT CHARSET=utf8

在根据评论的请求获取上述输出时，我还注意到我的表都采用不同的格式 - MyISAM 和 InnoDB。这也可能是问题的一部分吗？

我是不是把表结构弄得太复杂了？我想了解查询的哪一部分使它如此缓慢，以便我可以重新安排我的表。

Answer 1

通常，您希望从 EXPLAIN 报告中删除 type=ALL 的条目。这意味着它正在进行表扫描，如果它发生在大表上，这对性能不利。

在您的例子中，您有两个 表在进行表扫描。检查解释的 行 列中的数字，18631 和 899567。将它们相乘 = 16,759,832,777。这就是查询可能会检查的行组合的数量!

部分问题是您的查询正在执行 Cartesian product .您没有将表 p 与其他表相关联的条件。因此，对于 p 中检查的每一行，它会将其与其他表中检查的行结合起来。这具有非常高的成本。

不清楚为什么您的查询中甚至有 p，因为它与其他表无关，并且您不会在选择列表中从中获取任何列。即使我从查询中取出 p ，我也可以生成您描述的结果集:

SELECT n1.full_name AS sender_full_name, s1.email AS sender_email,
e.subject, e.body, e.attach, e.date, e.id, r.status, n2.full_name AS receiver_full_name,
s2.email AS receiver_email, r.basket, 'user777'
FROM email_routing r
JOIN email e ON e.id = r.message_id
JOIN people_emails s1 ON s1.id = r.sender_email_id
JOIN people n1 ON n1.id = s1.people_id
JOIN people_emails s2 ON s2.id = r.receiver_email_id
JOIN people n2 ON n2.id = s2.people_id
WHERE r.receiver_email_id = 3233 AND r.status = 2
ORDER BY e.date desc LIMIT 0, 50;

我还建议添加这个索引:

ALTER TABLE email_routing ADD KEY bk1 (receiver_email_id, status,
    sender_email_id, message_id, basket);

这有助于搜索 r.receiver_email_id = 3233 AND r.status = 2。

额外的列在索引中以使其成为覆盖索引。这意味着查询根本不必读取 email_routing 表，如果它从索引中获取了所需的所有列。

此查询的 EXPLAIN 看起来更好，因为没有一个表在执行 type=ALL，其中一个显示“使用索引”，这是覆盖索引的指示符。

+----+-------------+-------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                    | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
|  1 | SIMPLE      | s2    | const  | PRIMARY       | PRIMARY | 4       | const                  |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | n2    | const  | PRIMARY       | PRIMARY | 4       | const                  |    1 | NULL                            |
|  1 | SIMPLE      | r     | ref    | bk1           | bk1     | 8       | const,const            |    1 | Using index                     |
|  1 | SIMPLE      | s1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.r.sender_email_id |    1 | NULL                            |
|  1 | SIMPLE      | n1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.s1.people_id      |    1 | NULL                            |
|  1 | SIMPLE      | e     | eq_ref | PRIMARY       | PRIMARY | 4       | test.r.message_id      |    1 | NULL                            |
+----+-------------+-------+--------+---------------+---------+---------+------------------------+------+---------------------------------+

P.S.:MyISAM 与 InnoDB 对于此查询优化几乎没有区别。该索引将对两种存储引擎都有很大帮助。但我总是建议转换为 InnoDB(请参阅我对 MyISAM versus InnoDB 的回答)。