MySQL 查询 - 取消 ORDER BY 使查询速度提高 100 倍

Question

我在我的系统中发现了很长的查询。 MySQL 慢日志说明如下:

# Time: 2018-07-08T18:47:02.273314Z
# User@Host: server[server] @ localhost []  Id:  1467
# Query_time: 97.251247  Lock_time: 0.000210 Rows_sent: 50  Rows_examined: 41646378
SET timestamp=1531075622;
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 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.sender_email_id = 21897 ORDER BY e.date desc LIMIT 0, 50;

EXPLAIN 查询显示没有全表扫描并且查询使用了索引:

id select_type table partitions type    possible_keys key       key_len  ref                  rows filtered Extra
1  SIMPLE      s1    NULL       const   PRIMARY       PRIMARY   4        const                1    100.00   Using temporary; Using filesort
1  SIMPLE      n1    NULL       const   PRIMARY,ppl   PRIMARY   4        const                1    100.00   NULL
1  SIMPLE      n2    NULL       index   PRIMARY,ppl   ppl       771      NULL                 1    100.00   Using index
1  SIMPLE      s2    NULL       index   PRIMARY       s2        771      NULL                 3178 10.00    Using where; Using index; Using join buffer (Block Nested Loop)
1  SIMPLE      r     NULL       ref     bk1,bk2,msgid bk1       4        server.s2.id         440  6.60     Using where; Using index
1  SIMPLE      e     NULL       eq_ref  PRIMARY       PRIMARY   4        server.r.message_id  1    100.00   NULL

这是我对使用过的表的 SHOW CREATE TABLE 查询:

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 DEFAULT '0',
 `tm` int(11) NOT NULL DEFAULT '0',
 KEY `id` (`id`),
 KEY `bk1` (`receiver_email_id`,`status`,`sender_email_id`,`message_id`,`basket`),
 KEY `bk2` (`sender_email_id`,`tm`),
 KEY `msgid` (`message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1055796 DEFAULT CHARSET=utf8

-

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_dir` varchar(255) CHARACTER SET cp1251 DEFAULT NULL,
 `attach_subject` varchar(255) DEFAULT NULL,
 `attach_content` longtext,
 `sphinx_synced` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `Index_2` (`attach_dir`),
 KEY `dt` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=898001 DEFAULT CHARSET=utf8

-

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`),
 KEY `s2` (`email`,`people_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22146 DEFAULT CHARSET=utf8

-

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,
 `last_update_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `ppl` (`id`,`full_name`)
) ENGINE=InnoDB AUTO_INCREMENT=415040 DEFAULT CHARSET=utf8

这是这 4 个表的 SHOW TABLE STATUS 输出:

Name          Engine Version Row_format Rows    Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment
email         InnoDB 10      Dynamic    753748  12079          9104785408  0               61112320     4194304   898167
email_routing InnoDB 10      Dynamic    900152  61             55132160    0               69419008     6291456   1056033
people        InnoDB 10      Dynamic    9538    386            3686400     0               2785280      4194304   415040
people_emails InnoDB 10      Dynamic    3178    752            2392064     0               98304        4194304   22146

MySQL 版本 5.7.22 Ubuntu 16.04

但是我注意到一件事 - 如果我从查询中取出 ORDER BY，但保留 LIMIT，那么查询几乎立即运行，所用时间不超过 0.2 秒.所以我开始考虑在没有 ORDER BY 的情况下运行查询并通过 PHP 方式进行排序，但最终这似乎很复杂，因为使用没有 ORDER BY 的 LIMIT 我得到了错误的排序范围。

我还能做些什么来加速或优化该查询吗？

作为替代方案 我可以通过我的 PHP 代码进行排序和分页。我将附加列添加到 SELECT ..., UNIX_TIMESTAMP(e.date) as ts 中，然后执行:

<?php
...
$main_query = $server->query($query);
$emails_list = $main_query->fetch_all(MYSQLI_ASSOC);
function cmp($a, $b) {
    return strcmp($a['ts'], $b['ts']);
}

$emails_sorted = usort($emails_list, "cmp");
for ($i=$start;$i<$lenght;$i++)
{
    $singe_email = $emails_sorted[$i]
    // Format the output
}

但是当我这样做的时候我得到了

Fatal error: Allowed memory size of 134217728 bytes exhausted

在 $emails_sorted = usort($emails_list, "cmp"); 的行

Answer 1

警告，我对 MySQL 不是很熟悉，事实上我主要是在我(主要)阅读的有关 MySQL 的内容之上转换 MSSQL 经验。

1) 可能的解决方法:假设 email.id 和 email.date 始终处于相同顺序是否安全？从功能的角度来看，这似乎是合乎逻辑的，因为随着时间的推移，电子邮件会被添加到表格中，因此自动编号会不断增加……但也许数据的初始加载顺序不同/随机？无论如何，如果是，如果您使用 ORDER BY e.id 而不是 ORDER BY e.date 会发生什么情况？

2) 在 email (id, date) 上添加复合索引(按此顺序!)有帮助吗？

3) 如果所有这些都没有帮助，将查询分成两部分可能有助于优化器。 (您可能需要修复 MySQL 的语法)

-- Locate what we want first
CREATE TEMPORARY TABLE results (
    SELECT e.id
           r.basket
    FROM email_routing r 
    JOIN email e ON e.id = r.message_id 
    WHERE r.sender_email_id = 21897 
    ORDER BY e.date desc LIMIT 0, 50 );

-- Again, having an index on email (id, date) seems like a good idea to me

-- (As a test you may want to add an index on results (id) here, shouldn't take long and
--  in MSSQl it would help build a better query plan, can't tell with MySQL)

-- return actual results
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 results 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 
ORDER BY e.date desc