主页 / dba / 问题 / 25029
Accepted
Ran
Asked: 2012-09-28 00:20:51 +0800 CST

我怎样才能改进这个查询

  • 772

我有下表：

CREATE TABLE `twitter_relationships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  UNIQUE KEY `source_and_target` (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index` (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (user_id)
PARTITIONS 1000 */

这个表很大，大约有 1.5 亿条记录。

我有以下查询：

SELECT target_twitter_id 
 FROM `twitter_relationships` 
WHERE (`twitter_relationships`.`relationship_status` = ? 
   AND `twitter_relationships`.`user_id` = ? 
   AND `twitter_relationships`.`source_twitter_id` = ?) 
LIMIT ?, ?

这是此查询的解释：

           id: 1
  select_type: SIMPLE
        table: twitter_relationships
         type: ref
possible_keys: source_and_target,target_status_and_change_date_index,user_id_index
          key: source_and_target
      key_len: 12
          ref: const,const
         rows: 8560582
        Extra: Using where

有什么想法可以在查询中甚至在表结构中加快查询速度吗？

mysql query

2 个回答

  1. Best Answer

    首先，由于您已经有一个包含 的 UNIQUE 索引user_id，您应该能够摆脱该id字段，并使用 UNIQUE 索引作为 PRIMARY KEY：

    CREATE TABLE `twitter_relationships` (
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index`
    (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
PARTITION BY HASH (user_id) PARTITIONS 1000;

    不幸的是，虽然这会删除索引，但由于 InnoDB 索引数据的方式，它可能会增加存储需求。请参阅http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html中的“二级索引如何与聚集索引相关”

    其次，虽然source_and_target索引在您的 WHERE 子句中有三个字段中的两个，但 MySQL 将不得不进行额外的读取以找到relationship_status.

    因此，要提高性能，请创建一个包含 WHERE 子句中所有三个字段的索引：

    CREATE INDEX user_source_status ON twitter_relationships
    (`user_id`,`source_twitter_id`,`relationship_status`);

    然后，如果 MySQL 没有自动使用这个索引，你可以强制使用它，方法是：

    SELECT target_twitter_id 
 FROM `twitter_relationships` FORCE INDEX (user_source_status)
WHERE (`twitter_relationships`.`user_id` = ? 
   AND `twitter_relationships`.`source_twitter_id` = ?
   AND `twitter_relationships`.`relationship_status` = ?) 
LIMIT ?, ?

    最后，您缺少、、和字段UNSIGNED上的属性。我猜这些字段永远不会存储负值，所以将它们设为.iduser_idsource_twitter_idtarget_twitter_idUNSIGNED

    • 2

  2. 我认为您查询的答案就在这里 - https://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index

    据我所知，顺序在索引中很重要，您的查询有where status, user_id, source_twitter_id. 您可以更改查询顺序以使用索引source_and_target。

    希望这可以帮助！

    • 0

相关问题