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

我可以让这个mySQL查询更快吗?

mysql-slow-query-log query-optimization mysql
  •  1
  • user164863  · 技术社区  · 8 年前

    我在mysql中有以下条目slow。日志: 

    # 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。这也是问题的一部分吗?

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

    2 回复  |  直到 8 年前
        1
  •  1
  •   Bill Karwin    8 年前

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

    就你而言,你有 二 正在执行表扫描的表。检查中的数字 row 解释栏,18631和899567。将它们相乘=16759832777。这就是查询可能要检查的行组合数!

    部分问题是您的查询正在执行 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\u路由表。

    解释这个查询看起来更好了,因为没有一个表 类型=全部 ,其中一个显示“使用指数”,这是覆盖指数的指标。 

    +----+-------------+-------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
| 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、 美国:MyISAM和InnoDB对这种查询优化的影响很小。该索引对两个存储引擎都有很大帮助。但我始终建议转换为InnoDB(参见我对 MyISAM versus InnoDB ).

        2
  •  1
  •   Rick James diyism    8 年前

    这个看起来 错误的 : 

        FROM  people_emails p
    JOIN  email_routing r  ON r.receiver_email_id = 3223
      AND  r.status = 2

    p 不用于任何 ON 条款。也许你错过了这么多 P 和 r 在一起没有它,你就有了一个“交叉连接”。如果每个连接中有1K行,那么最终连接中会有1M行。

    另外,请使用 在…上 用于显示表格之间的关系;使用 WHERE 用于过滤(3222和2)。

    推荐文章
    JAGADEESH  ·  如何在PostgreSQL中使用特定键的动态SQL查询提取嵌套的JSONB值?[副本]
    2 年前
    Babak  ·  MongoDB-基于数组中同一文档中的另一个字段更新字段
    2 年前
    joaocarlosib  ·  用于动态查询情况的存储过程常识模式
    2 年前
    Brian Moeskau  ·  在PostgreSQL中将视图连接到快速子查询/CTE的糟糕性能
    2 年前
    kriti  ·  在Mysql 8.x数据库和InnoDB存储中,将varchar列大小从255个字符扩展到1024个字符似乎没有使用复制算法
    2 年前
    HALIM  ·  Laravel 1查询多个不同的结果
    3 年前
    Scobbo  ·  从一个pandas数据帧.loc请求设置多个变量
    3 年前
    Kalane  ·  数据集增加时SQL查询缓慢
    3 年前
    Community wiki  ·  优化从同一表中提取的多列的查询
    3 年前
    user164863  ·  我可以让这个mySQL查询更快吗?
    8 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号