一、查看表结构
CREATE TABLE `happy_for_ni_labels` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name_chn` varchar(255) NOT NULL DEFAULT '0' COMMENT '标签的名字', `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '标签状态', `xx_tag_id` int(11) NOT NULL DEFAULT '0' COMMENT '关联XxTag#ID', `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, `xxxxx_tag_id` int(11) NOT NULL DEFAULT '0' COMMENT 'xxxxx_tags.id(新分类体系)', PRIMARY KEY (`id`), KEY `idx_name_chn_with_id` (`name_chn`,`id`), KEY `idx_xx_tag_id_with_id` (`xx_tag_id`,`id`), KEY `idx_ptag_id` (`xxxxx_tag_id`,`id`) ) ENGINE=InnoDB AUTO_INCREMENT=719 DEFAULT CHARSET=utf8 COMMENT='报名活动标签' CREATE TABLE `happy_for_ni_label_links` ( `id` int(11) NOT NULL AUTO_INCREMENT, `happy_for_ni_id` int(11) NOT NULL DEFAULT '0' COMMENT '关联HappyForNi#ID', `checked_happy_for_ni_id` int(11) NOT NULL DEFAULT '0' COMMENT '关联CheckedHappyForNi#ID', `label_id` int(11) NOT NULL DEFAULT '0' COMMENT '关联HappyForNiLabel#ID', `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '关联状态(可用、删除)', `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `idx_label_id_with_id` (`label_id`,`id`), KEY `idx_status_happy_for_ni_id_with_id` (`happy_for_ni_id`,`status`,`id`), KEY `idx_status_checked_happy_for_ni_id_with_id` (`checked_happy_for_ni_id`,`status`,`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2048836 DEFAULT CHARSET=utf8 COMMENT='报名活动标签关联表' |
执行查询计划可知
explain SELECT `happy_for_ni_labels`.`id` FROM `happy_for_ni_labels` INNER JOIN `happy_for_ni_label_links` ON `happy_for_ni_labels`.`id` = `happy_for_ni_label_links`.`label_id` WHERE `happy_for_ni_label_links`.`happy_for_ni_id` = 3369231 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: happy_for_ni_labels type: index possible_keys: PRIMARY key: idx_xx_tag_id_with_id key_len: 8 ref: NULL rows: 461 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: happy_for_ni_label_links type: ref possible_keys: idx_label_id_with_id key: idx_label_id_with_id key_len: 4 ref: my_local_test.happy_for_ni_labels.id rows: 1872 Extra: Using WHERE 2 rows in set (0.00 sec) ERROR: No query specified |
本来想用到 idx_status_happy_for_ni_id_with_id 但是实际上只用到了 idx_label_id_with_id 这个索引，所以根据现有的资料。
优化有两种方案
去掉现有的索引，重新生成索引。
重用现在的索引，修改查询语句。
二、去掉现有的索引，重新生成索引。
mysql> SELECT count(id), status
-> FROM happy_for_ni_label_links
-> GROUP BY status;
No connection. Trying to reconnect...
Connection id: 112463
Current database: my_local_test
+-----------+--------+
| count(id) | status |
+-----------+--------+
| 980377 | 0 |
+-----------+--------+
1 row in set (2.27 sec)
status 只有为 0 的值。这里其实是个败笔。创建这个表的作者（也就是我），当时考虑到由于业务需要，会查询各种不同状态下的数据量，故设计了这个status。但实际情况，该状态，只有一个为0的值，不需要看索引记录也知道，该列上的选择性太差。建议，不要将该列放在索引第一位。
