MySQL 2000 万条记录非常慢

Question

做一个简单的查询，如

    select state, count(state) as cnt from big_data where status=0 group by state

大约需要 20 秒。这是我的表定义:

    CREATE TABLE `NewTable` (`id`  bigint(22) NOT NULL AUTO_INCREMENT ,
                             `city`  varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
                             `state`  varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
                             `miles_away`  int(5) NOT NULL ,
                             `member_id`  int(11) NOT NULL ,
                             `gender`  varchar(17) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
                             `profile`  varchar(128) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
                             `status`  varchar(1) CHARACTER SET latin1                           

    COLLATE latin1_swedish_ci NOT NULL ,
    PRIMARY KEY             ( `id`, 
                              `city`,
                              `state`, 
                              `miles_away`, 
                              `member_id`, 
                              `gender`, 
                              `profile`, 
                              `status`),
    UNIQUE INDEX `id` (`id`) USING BTREE ,
    UNIQUE INDEX `profile` (`profile`) USING BTREE ,
    INDEX `city` (`city`) USING BTREE ,
    INDEX `state` (`state`) USING BTREE ,
    INDEX `miles_away` (`miles_away`) USING BTREE ,
    INDEX `member_id` (`member_id`) USING BTREE ,
    INDEX `gender` (`gender`) USING BTREE ,
    INDEX `status` (`status`) USING BTREE)
    ENGINE=InnoDB
    DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
    AUTO_INCREMENT=12889691
    ROW_FORMAT=COMPACT;

即使是基于主键的简单 ID 查找也需要那么长时间。我的服务器有 72 个核心(第 4 代 Xeon ES2690，具有 64GB RAM)

这里是解释的截图:

multi threads http://gaysugardaddyfinder.com/shot.PNG

救命!

Answer 1

使用EXPLAIN显示执行计划。

引用:https://dev.mysql.com/doc/refman/5.5/en/using-explain.html

为了获得最佳性能，请提供合适的索引，以便优化器可以避免“使用文件排序”操作来满足GROUP BY。

我建议:

CREATE INDEX `NewTableIX1` ON `NewTable` (`status`,`state`)

(我建议这样做，因为您的查询在 status 列上包含一个等式谓词，并且它正在对 state 列执行分组操作。使用这个新索引，我希望 EXPLAIN 输出显示它正在使用新索引，并且还显示查询完全由索引满足 - “Using index”显示在 Extra 列中解释输出。)

定义这个新索引后，仅 status 列上的单独索引是多余的，可以删除。

<小时/>

跟进

由于 id 是唯一且非空的，因此它可以用作表的主键。主键不需要包含表中的每一列。 (二级索引将与表本身一样大，因为主键将存储在每个索引中，“指针”返回到集群索引。)

如果这是我的 table ，我会替换它:

PRIMARY KEY             ( `id`, 
                          `city`,
                          `state`, 
                          `miles_away`, 
                          `member_id`, 
                          `gender`, 
                          `profile`, 
                          `status`),
UNIQUE INDEX `id` (`id`) USING BTREE ,

仅此:

PRIMARY KEY (`id`)

(我将创建新表，并复制旧表中的所有数据。)