mysql - MySQL 查询优化

Question

我是这个tune db的新人，想请教一下当数据达到3500万左右时如何优化查询。我在几个论坛上读到要执行以下任务，但它并没有按预期改进我的查询。

1. 为我需要指定的所有字段创建索引。
2. 我从最大基数开始对 where 子句进行排序。
3. 对指定数据使用 where 子句。
4. 完成了优化表命令，分析表命令

我希望有人能给我一些线索:)

我的查询

SELECT  a.msisdn, a.service, a.adn, a.operator, a.channel_subscribe,
        a.subscribed_from, b.blacklist_status, b.date_created,
        b.status, b.price
    FROM  `xmp_new`.`subscription` a
    INNER JOIN  msisdntools.`subscription_import` b ON a.msisdn = b.msisdn
    WHERE  b.msisdn IN (
        SELECT  msisdn
            FROM  `xmp_new`.`subscription`
            WHERE  msisdn NOT IN (
                SELECT  msisdn
                    FROM  `msisdntools`.`tbl_blacklist`
                    WHERE  blacklist_status='1'
                      AND  active='1'
                          )
              AND  msisdn NOT IN (
                SELECT  msisdn
                    FROM  subscription_import
                    WHERE  DATE(date_created)=DATE(NOW())
                          )
              AND  operator = 'xxxx'
              AND  subscribed_from BETWEEN DATE('2015-05-16') AND DATE('2015-05-17')
                       )
      AND  DATE(b.date_created) = '2012-05-16'
      AND  b.STATUS = 0
      AND  b.price IS NULL;

subscription_import的索引

    Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
    subscription_import     0   PRIMARY     1   id  A   33136464    (NULL)  (NULL)      BTREE        
    subscription_import     1   msisdn  1   msisdn  A   6627292     (NULL)  (NULL)      BTREE        
    subscription_import     1   service     1   service     A   18  (NULL)  (NULL)      BTREE        
    subscription_import     1   short_code  1   adn     A   18  (NULL)  (NULL)      BTREE        
    subscription_import     1   operator    1   operator    A   18  (NULL)  (NULL)      BTREE        
    subscription_import     1   channel     1   channel     A   18  (NULL)  (NULL)      BTREE 

订阅表上的索引

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
subscription    0   PRIMARY     1   id  A   2179811     (NULL)  (NULL)      BTREE        
subscription    1   msisdn  1   msisdn  A   2179811     (NULL)  (NULL)      BTREE        
subscription    1   service     1   service     A   19  (NULL)  (NULL)      BTREE        
subscription    1   short_code  1   adn     A   19  (NULL)  (NULL)      BTREE        
subscription    1   operator    1   operator    A   19  (NULL)  (NULL)      BTREE        
subscription    1   channel     1   channel_subscribe   A   19  (NULL)  (NULL)      BTREE        
subscription    1   subscribed_from     1   subscribed_from     A   2179811     (NULL)  (NULL)      BTREE        
subscription    1   subscribed_until    1   subscribed_until    A   2179811     (NULL)  (NULL)      BTREE        
subscription    1   active  1   active  A   19  (NULL)  (NULL)  YES     BTREE        
subscription    1   time_created    1   time_created    A   2179811     (NULL)  (NULL)      BTREE        
subscription    1   time_updated    1   time_updated    A   2179811     (NULL)  (NULL)      BTREE        

为 subscription_import 创建表命令

CREATE TABLE `subscription_import` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `msisdn` varchar(32) NOT NULL,
  `service` varchar(64) NOT NULL,
  `adn` varchar(8) NOT NULL,
  `operator` varchar(32) NOT NULL,
  `channel` varchar(16) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `description` varchar(20) DEFAULT NULL,
  `blacklist_status` tinyint(4) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `price` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `msisdn` (`msisdn`) USING BTREE,
  KEY `service` (`service`) USING BTREE,
  KEY `short_code` (`adn`) USING BTREE,
  KEY `operator` (`operator`) USING BTREE,
  KEY `channel` (`channel`) USING BTREE,
  KEY `date_created` (`date_created`),
  KEY `STATUS` (`status`),
  KEY `price` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=33274291 DEFAULT CHARSET=utf8

为表订阅创建表命令

CREATE TABLE `subscription` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_id_subscribe` varchar(64) NOT NULL,
  `transaction_id_unsubscribe` varchar(64) NOT NULL,
  `msisdn` varchar(32) NOT NULL,
  `service` varchar(64) NOT NULL,
  `adn` varchar(8) NOT NULL,
  `operator` varchar(32) NOT NULL,
  `channel_subscribe` varchar(16) NOT NULL,
  `channel_unsubscribe` varchar(16) NOT NULL,
  `subscribed_from` datetime NOT NULL,
  `subscribed_until` datetime NOT NULL,
  `partner` varchar(20) NOT NULL,
  `active` tinyint(1) DEFAULT NULL,
  `time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `time_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `msisdn` (`msisdn`) USING BTREE,
  KEY `service` (`service`) USING BTREE,
  KEY `short_code` (`adn`) USING BTREE,
  KEY `operator` (`operator`) USING BTREE,
  KEY `channel` (`channel_subscribe`) USING BTREE,
  KEY `subscribed_from` (`subscribed_from`) USING BTREE,
  KEY `subscribed_until` (`subscribed_until`) USING BTREE,
  KEY `active` (`active`) USING BTREE,
  KEY `time_created` (`time_created`) USING BTREE,
  KEY `time_updated` (`time_updated`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7868759 DEFAULT CHARSET=utf8

Answer 1

不要使用 IN ( SELECT ...);这是非常低效的。而是使用 JOIN。对于 NOT IN ( SELECT ... )，使用 LEFT JOIN ... WHERE id IS NULL。

不要在函数中“隐藏”列(只要可能):AND DATE(b.date_created) = '2012-05-16' 可以变成 AND b。 date_created = '2012-05-16' 如果 date_created 是 DATE。如果是 DATETIME，则执行:

AND  b.date_created >= '2012-05-16'
AND  b.date_created  < '2012-05-16' + INTERVAL 1 DAY

这样，就可以使用 INDEX(date_created) 了。更好的可能是复合 INDEX(price, status, date_created)。

subscription 将其转换为 LEFT JOIN 后需要 INDEX(msisdn, blacklist_status, active)。

DATE(NOW()) 与 CURDATE() 相同。但是 WHERE DATE(date_created) = DATE(NOW()) 可能是 WHERE date_created >= CURDATE()。

subscription_import 将需要 INDEX(msisdn, date_created)。

msisdn 是否唯一？也就是说，它可以用作“自然”PRIMARY KEY 而不是 id 吗？

进行这些更改，性能会更好。然后回来寻求更多建议/虐待。