MySQL的很慢有20万条记录

问题描述：

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

大约需要20秒。这里是我的表格def：

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代至强ES2690与64GB RAM）

这里解释的截图：

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

帮助！

这不是从EXPLAIN输出。运行'EXPLAIN选择状态，计数（状态）...'。这将显示优化程序在声明中所做的事情。另外，表中的主键是表中的所有列是非常奇怪的，特别是当你已经有一个非空的列，它有一个唯一的约束（'id'），可以作为主键。 – spencer7593

答

使用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输出显示它使用新索引，并且还显示查询完全从EXPLAIN输出的额外列中显示的索引 - “使用索引”中满足。）

在定义了这个新的索引之后，单独的索引就是status列是多余的，可以丢弃。

随访

由于id是独一无二的，NOT NULL，它可以作为表的主键。 PRIMARY KEY不需要在表格中包含每一列。（二级索引将会和表本身一样大，因为PRIMARY KEY将被存储在每个索引中，“指针”将返回到集群索引。）

如果这是我的表， d放回这样的：

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

通过如此：

PRIMARY KEY (`id`)

（我想创建新表，并且从旧表复制所有数据。）

MySQL的很慢有20万条记录

相关推荐