代码之家 › 专栏 › 技术社区 › user164863

mysql查询-按订单下订单,查询速度快100倍

query-optimization sorting mysql

user164863 · 技术社区 · 7 年前

我在系统中发现了很长的查询。mysql slow日志显示如下内容:

# 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;

“解释”查询显示“否” 全表扫描 以及使用索引的查询:

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

以下是我的“为使用的表创建表”查询:

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

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

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

不过,我注意到一件事——如果我在查询之外进行排序, 但离开 LIMIT ,然后查询几乎立即运行,时间不超过0.2秒。因此,我开始考虑不使用order by运行查询,并使用php进行排序,这意味着类似的方法,但最终,这似乎变得复杂起来,因为使用了无order by的限制,我得到了错误的排序范围。

我还能做些什么来加速或优化这个查询吗?

作为替代方案 我可以用我的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
}

但当我这样做的时候,我得到

致命错误:允许的内存大小134217728字节已用完

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

4 回复 | 直到 7 年前

deroby 7 年前

警告,我对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

DRapp 7 年前

如果你的数据恢复得那么快,那么把它包装起来怎么样…但实际返回的行数没有限制。也许你会在…之后得到更好的表现。

select PQ.*
   from ( YourQueryWithoutOrderByAndLimt ) PQ
   order by PQ.date desc 
   LIMIT 0, 50;

Øystein Grøvlen 7 年前

我怀疑在这种情况下,MySQL连接优化器高估了块嵌套循环(BNL)连接的好处。您可以尝试通过执行以下操作关闭BNL:

set optimizer_switch='block_nested_loop=off';

希望这将提供更好的加入顺序。您也可以尝试:

set optimizer_prune_level = 0;

强制联接优化器探索所有可能的联接顺序。

另一种选择是使用直接连接来强制特定的连接顺序。在这种情况下,查询文本中指定的顺序似乎很好。因此,为了强制执行这个特殊的连接顺序,您可以编写

SELECT STRAIGHT_JOIN ...

请注意,无论您做什么,您都不能期望查询速度像没有order by一样快。只要您需要查找来自特定发件人的最新电子邮件,并且电子邮件表中没有关于发件人的信息,就不可能使用索引来避免在不浏览来自所有发件人的所有电子邮件的情况下进行排序。如果你在电子邮件路由表中有关于日期的信息,事情会有所不同。然后,可以使用该表上的索引来避免排序。

Jacob 7 年前

在查询中,mysql不能使用index作为order by,因为

查询联接了许多表,而ORDER BY中的列不是全部来自用于检索行的第一个非常量表。 (这是解释输出中没有常量连接类型。)

MySQL Order By Optimization