mysql 多列group by 索引优化 网上查了查 这种GROUP BY ORDER BY的比较难以优化语句 索引 以及查询计划 都在图片中麻烦大神给指点下 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 DROP TABLE IF EXISTS `rt_out_orders_m`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `rt_out_orders_m` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号', `RECEIPT_NO` varchar(200) NOT NULL COMMENT '单据号', `RECEIPT_STATUS` varchar(200) NOT NULL COMMENT '单据状态', `RECEIPT_TYPE` varchar(200) NOT NULL COMMENT '单据类型', `ORG_NO` varchar(200) DEFAULT NULL COMMENT '机构编号', `DISTRIBUTE_NO` varchar(200) DEFAULT NULL COMMENT '配送中心编号', `WAREHOUSE_NO` varchar(200) DEFAULT NULL COMMENT '仓库编号', `NODE` varchar(200) DEFAULT NULL COMMENT '查询详细请求系统', `CREATE_TIME` datetime NOT NULL DEFAULT '1900-01-01 00:00:00' COMMENT '创建时间', `OPERATOR` varchar(200) DEFAULT NULL COMMENT '操作者', `IS_BACKTRACK` int(11) DEFAULT NULL COMMENT '是否回传', `BACK_CATCH_STATUS` int(11) DEFAULT NULL COMMENT '回传抓取状态', `BACK_BIZKEY` varchar(100) DEFAULT NULL COMMENT '回传业务key', `LOCK_STATUS` int(11) DEFAULT NULL COMMENT '退单锁库存处理状态', `LOCK_UPDATE_TIME` datetime DEFAULT NULL COMMENT '锁库存发生时间', `BACK_UPDATE_TIME` datetime DEFAULT NULL COMMENT '状态回传发生时间', `LIBERATE_CODE` varchar(100) DEFAULT NULL COMMENT '释放码', `UUID` varchar(100) DEFAULT NULL COMMENT '防重码', `OWNER_NO` varchar(100) DEFAULT '0' COMMENT '货主编码', `YN` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标识(0未删除 1已删除)', `TS` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01', `WAVE_NO` varchar(30) DEFAULT NULL COMMENT '波次号', `collection_no` varchar(50) DEFAULT NULL COMMENT '集合单号', `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`ID`), KEY `idx_update_time` (`UPDATE_TIME`), KEY `index_receipt_no` (`RECEIPT_NO`), KEY `index_create_time` (`CREATE_TIME`), KEY `idx_back_catch_status` (`BACK_CATCH_STATUS`)) ENGINE=InnoDB AUTO_INCREMENT=11915348 DEFAULT CHARSET=utf8 COMMENT='出库单信息'; SHOW INDEX FROM rt_out_orders_m;+-----------------+------------+-----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-----------------+------------+-----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| rt_out_orders_m | 0 | PRIMARY | 1 | ID | A | 12239832 | NULL | NULL | | BTREE | | || rt_out_orders_m | 1 | idx_update_time | 1 | UPDATE_TIME | A | 4079944 | NULL | NULL | | BTREE | | || rt_out_orders_m | 1 | index_receipt_no | 1 | RECEIPT_NO | A | 4079944 | NULL | NULL | | BTREE | | || rt_out_orders_m | 1 | index_create_time | 1 | CREATE_TIME | A | 12239832 | NULL | NULL | | BTREE | | || rt_out_orders_m | 1 | idx_back_catch_status | 1 | BACK_CATCH_STATUS | A | 18 | NULL | NULL | YES | BTREE | | |+-----------------+------------+-----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+explain select count(distinct(receipt_no)) receipt_count, receipt_status, receipt_type, org_no, distribute_no, warehouse_no from rt_out_orders_m where yn=0 and create_time>='2015-05-22 00:00:00' and create_time<= '2015-05-22 15:40:35' group by receipt_status, receipt_type, org_no, distribute_no, warehouse_no;+----+-------------+-----------------+-------+-------------------+-------------------+---------+------+--------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------------+-------+-------------------+-------------------+---------+------+--------+-----------------------------+| 1 | SIMPLE | rt_out_orders_m | range | index_create_time | index_create_time | 8 | NULL | 252042 | Using where; Using filesort |+----+-------------+-----------------+-------+-------------------+-------------------+---------+------+--------+-----------------------------+ 从索引上没什么可优化的了。MYSQL已经走了 index_create_time,最多是根据实际情况决定是否再创建一个 (yn,index_create_time)的复合索引。 版主你好, 您可能没太明白我的意思YN 是状态 只有两个值 0,1 也可以去掉SQL慢是因为慢在了GROUP BY 我要问的是建立怎样的索引才能优化group by并不是 WHERE 条件 查询分析里面 extra 里面有这个:Using filesort这语句如果去掉GROUP BY 会非常快 的盼回复 GROUP 中的没办法优化了。特别是 CREATE_TIME 在表中根本没有重复的。所以只能先WHERE,然后GROUP的时候无任何索引可用。 那你觉得我的语句 低性能在什么位置? 是group by 还是distinct 还是count 也可以给个顺序另外 斑竹大人 有优化SQL的 好的链接 和博客么 sql 语句如何在mysql里实现 MySQL时间格式 求助一条插入语句! 关于mysqlhotcopy的疑问 'Table 'aaa.user_tmp也属于 复制表? my.cnf中这几个参数的值是不是合适? 求一个mysql触发器 有mysql的备份文件,如何还原数据库? 请问MYSQL一个为二进位的列,为什么用ASP的RS出来是乱码? 求驱动程序!! mysql 子查询中结果全部为某个值 求助一个查询语句!
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `rt_out_orders_m` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
`RECEIPT_NO` varchar(200) NOT NULL COMMENT '单据号',
`RECEIPT_STATUS` varchar(200) NOT NULL COMMENT '单据状态',
`RECEIPT_TYPE` varchar(200) NOT NULL COMMENT '单据类型',
`ORG_NO` varchar(200) DEFAULT NULL COMMENT '机构编号',
`DISTRIBUTE_NO` varchar(200) DEFAULT NULL COMMENT '配送中心编号',
`WAREHOUSE_NO` varchar(200) DEFAULT NULL COMMENT '仓库编号',
`NODE` varchar(200) DEFAULT NULL COMMENT '查询详细请求系统',
`CREATE_TIME` datetime NOT NULL DEFAULT '1900-01-01 00:00:00' COMMENT '创建时间',
`OPERATOR` varchar(200) DEFAULT NULL COMMENT '操作者',
`IS_BACKTRACK` int(11) DEFAULT NULL COMMENT '是否回传',
`BACK_CATCH_STATUS` int(11) DEFAULT NULL COMMENT '回传抓取状态',
`BACK_BIZKEY` varchar(100) DEFAULT NULL COMMENT '回传业务key',
`LOCK_STATUS` int(11) DEFAULT NULL COMMENT '退单锁库存处理状态',
`LOCK_UPDATE_TIME` datetime DEFAULT NULL COMMENT '锁库存发生时间',
`BACK_UPDATE_TIME` datetime DEFAULT NULL COMMENT '状态回传发生时间',
`LIBERATE_CODE` varchar(100) DEFAULT NULL COMMENT '释放码',
`UUID` varchar(100) DEFAULT NULL COMMENT '防重码',
`OWNER_NO` varchar(100) DEFAULT '0' COMMENT '货主编码',
`YN` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标识(0未删除 1已删除)',
`TS` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',
`WAVE_NO` varchar(30) DEFAULT NULL COMMENT '波次号',
`collection_no` varchar(50) DEFAULT NULL COMMENT '集合单号',
`UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
KEY `idx_update_time` (`UPDATE_TIME`),
KEY `index_receipt_no` (`RECEIPT_NO`),
KEY `index_create_time` (`CREATE_TIME`),
KEY `idx_back_catch_status` (`BACK_CATCH_STATUS`)
) ENGINE=InnoDB AUTO_INCREMENT=11915348 DEFAULT CHARSET=utf8 COMMENT='出库单信息'; SHOW INDEX FROM rt_out_orders_m;
+-----------------+------------+-----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rt_out_orders_m | 0 | PRIMARY | 1 | ID | A | 12239832 | NULL | NULL | | BTREE | | |
| rt_out_orders_m | 1 | idx_update_time | 1 | UPDATE_TIME | A | 4079944 | NULL | NULL | | BTREE | | |
| rt_out_orders_m | 1 | index_receipt_no | 1 | RECEIPT_NO | A | 4079944 | NULL | NULL | | BTREE | | |
| rt_out_orders_m | 1 | index_create_time | 1 | CREATE_TIME | A | 12239832 | NULL | NULL | | BTREE | | |
| rt_out_orders_m | 1 | idx_back_catch_status | 1 | BACK_CATCH_STATUS | A | 18 | NULL | NULL | YES | BTREE | | |
+-----------------+------------+-----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+explain select count(distinct(receipt_no)) receipt_count, receipt_status, receipt_type, org_no, distribute_no, warehouse_no from rt_out_orders_m where yn=0 and create_time>='2015-05-22 00:00:00' and create_time<= '2015-05-22 15:40:35' group by receipt_status, receipt_type, org_no, distribute_no, warehouse_no;
+----+-------------+-----------------+-------+-------------------+-------------------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+-------------------+-------------------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | rt_out_orders_m | range | index_create_time | index_create_time | 8 | NULL | 252042 | Using where; Using filesort |
+----+-------------+-----------------+-------+-------------------+-------------------+---------+------+--------+-----------------------------+
YN 是状态 只有两个值 0,1 也可以去掉
SQL慢是因为慢在了GROUP BY 我要问的是建立怎样的索引才能优化group by
并不是 WHERE 条件 查询分析里面 extra 里面有这个:Using filesort
这语句如果去掉GROUP BY 会非常快 的盼回复
那你觉得我的语句 低性能在什么位置? 是group by 还是distinct 还是count 也可以给个顺序
另外 斑竹大人 有优化SQL的 好的链接 和博客么