單表GROUP BY 出現Using temporary； Using filesort 優化

一.先贴结论

可能是因为索引长度的原因导致联合所以没有生效

1. 看我原先的建表语句和查询语句

CREATE TABLE `easyflow_config_release_zzq_test_youhua` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `project_code` varchar(32) NOT NULL COMMENT '项目编号',
  `env_code` varchar(32) NOT NULL COMMENT '环境编号',
  `app_id` varchar(500) NOT NULL DEFAULT 'default' COMMENT 'app_id',
  `cluster_name` varchar(500) NOT NULL DEFAULT 'default' COMMENT 'cluster_name',
  `namespace_name` varchar(500) NOT NULL DEFAULT 'default' COMMENT 'namespace_name',
  `release_key` varchar(64) NOT NULL DEFAULT '' COMMENT '发布的key',
  `name` varchar(64) NOT NULL DEFAULT 'default' COMMENT '发布名字',
  `comment` varchar(256) DEFAULT NULL COMMENT '发布说明',
  `configurations` longtext NOT NULL COMMENT '发布配置',
  `is_abandoned` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否废弃',
  `deleted` bigint(20) NOT NULL DEFAULT '0' COMMENT 'normal:0, deleted: deleted timestamp',
  `created_by` varchar(64) NOT NULL DEFAULT 'default' COMMENT '创建人邮箱前缀',
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_modified_by` varchar(64) DEFAULT '' COMMENT '最后修改人邮箱前缀',
  `last_update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `api_type` char(2) NOT NULL COMMENT '接口类型 (EnumApiType 01:转发接口 02:流程接口)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_release_key` (`release_key`),
  KEY `ix_last_update_time` (`last_update_time`),
  KEY `ix_release_key` (`release_key`),
  KEY `ix_project_code` (`project_code`) USING BTREE,
  KEY `ix_env_code` (`env_code`) USING BTREE,
  KEY `ix_app_id_cluster_name_group_name` (`project_code`,`env_code`,`app_id`(191),`cluster_name`(191),`namespace_name`(191)) USING BTREE,
  KEY `ix_app_id` (`app_id`) USING BTREE,
  KEY `ix_cluster_name` (`cluster_name`) USING BTREE,
  KEY `ix_namespace_name` (`namespace_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12566 DEFAULT CHARSET=utf8mb4 COMMENT='发布';
 
 
 
 
 
#待优化的sql
 
EXPLAIN
SELECT max(id)           id
#,max(created_time) created_time
,
               project_code,
               env_code,
               app_id,
               cluster_name,
               namespace_name
        FROM easyflow_config_release_zzq_test_youhua
        GROUP BY project_code, env_code, app_id, cluster_name, namespace_name ORDER BY NULL
           

2.可以看到group by 相关字段的独立索引，按顺序的联合索引都是有的 ，但是这个sql的执行计划是没有走索引的

3. 最后修改了字段长度，发现可以使用索引

CREATE TABLE `easyflow_config_release_zzq_test_youhua` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `project_code` varchar(32) NOT NULL COMMENT '项目编号',
  `env_code` varchar(32) NOT NULL COMMENT '环境编号',
  `app_id` varchar(64) NOT NULL DEFAULT 'default' COMMENT 'app_id',
  `cluster_name` varchar(64) NOT NULL DEFAULT 'default' COMMENT 'cluster_name',
  `namespace_name` varchar(64) NOT NULL DEFAULT 'default' COMMENT 'namespace_name',
  `release_key` varchar(64) NOT NULL DEFAULT '' COMMENT '发布的key',
  `release_name` varchar(64) NOT NULL DEFAULT 'default' COMMENT '发布名字',
  `remark` varchar(1024) DEFAULT NULL COMMENT '发布说明',
  `configurations` longtext NOT NULL COMMENT '发布配置',
  `is_abandoned` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否废弃',
  `deleted` bigint(20) NOT NULL DEFAULT '0' COMMENT 'normal:0, deleted: deleted timestamp',
  `created_by` varchar(64) NOT NULL DEFAULT 'default' COMMENT '创建人邮箱前缀',
  `created_time` timestamp NOT NULL COMMENT '创建时间',
  `last_modified_by` varchar(64) DEFAULT '' COMMENT '最后修改人邮箱前缀',
  `last_update_time` timestamp NOT NULL COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ecr_udx_release_key` (`release_key`),
  KEY `ecr_ix_last_update_time` (`last_update_time`),
  KEY `ecr_ix_release_key` (`release_key`),
  KEY `ecr_ix_project_env_app_cluster_group` (`project_code`,`env_code`,`app_id`,`cluster_name`,`namespace_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COMMENT='发布';