mysql查询-通过关闭获取订单使查询速度提高100倍

qoefvg9y 于 2021-06-21 发布在 Mysql

关注(0)|答案(4)|浏览(289)

我在系统中发现了很长的查询。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

下面是我的演示：为使用的表创建表查询：

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");

mysql query-optimization sorting

来源：https://stackoverflow.com/questions/51236357/mysql-query-taking-order-by-off-making-query-100-times-faster

4条答案

按热度按时间

axr492tv1#

mysql无法在查询中使用order by索引，因为
该查询连接了许多表，orderby中的列并不都来自用于检索行的第一个非恒定表(这是explain输出中第一个没有const join类型的表。）
mysql按优化排序

赞(0）回复(0）举报 2021-06-21

t9eec4r02#

我怀疑mysql连接优化器高估了块嵌套循环（bnl）连接的好处。您可以尝试通过执行以下操作来关闭bnl：

set optimizer_switch='block_nested_loop=off';

希望这能提供更好的连接顺序。您也可以尝试：

set optimizer_prune_level = 0;

强制联接优化器探索所有可能的联接顺序。
另一种选择是使用直接连接来强制特定的连接顺序。在这种情况下，在查询文本中指定的顺序似乎是好的。因此，要强制执行这个特定的连接顺序，可以编写

SELECT STRAIGHT_JOIN ...

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

赞(0）回复(0）举报 2021-06-21

smdnsysy3#

如果你的数据这么快就回来了，那就把它 Package 起来。。。但实际上有多少行将被无限制地返回。也许你会得到更好的表现后，如。。。

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

赞(0）回复(0）举报 2021-06-21

jq6vz3qz4#

警告，我对mysql不是很熟悉，事实上，我主要是把mssql的经验放在我（主要）读到的关于mysql的东西之上。
1）潜在的解决方法：假设email.id和email.date总是处于同一顺序是否安全？从功能的Angular 来看，这似乎是合乎逻辑的电子邮件被添加到表中，随着时间的推移，从而有一个不断增加的自动编号。。。但可能数据的初始加载顺序不同/随机？不管怎样，如果是的话，如果你 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

赞(0）回复(0）举报 2021-06-21

我来回答

mysql查询-通过关闭获取订单使查询速度提高100倍

4条答案

相关问题

热门标签

最新问答