慢查询优化
一、查看表结构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;
ERROR 2006 (HY000): MySQL server has gone away
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 的值，不需要看索引记录也知道，该列上的选择性太差。建议，不要将该列放在索引第一位。
删除索引
ALTER TABLE `happy_for_ni_label_links` DROP INDEX `idx_status_happy_for_ni_id_with_id`;
ALTER TABLE `happy_for_ni_label_links` DROP INDEX `idx_status_checked_happy_for_ni_id_with_id`;
添加索引
ALTER TABLE `happy_for_ni_label_links` ADD INDEX `idx_status_happy_for_ni_id_with_id` (happy_for_ni_id, status, id);
Query OK, 0 rows affected (3.52 sec)
ALTER TABLE `happy_for_ni_label_links` ADD INDEX `idx_status_checked_happy_for_ni_id_with_id` ( checked_happy_for_ni_id, status, id);
Query OK, 0 rows affected (3.57 sec)
最终结果如下(不需要修改查询语句，重建索引即可)
mysql> 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\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: happy_for_ni_label_links
type: ref
possible_keys: idx_label_id_with_id,idx_status_happy_for_ni_id_with_id
key: idx_status_happy_for_ni_id_with_id
key_len: 4
ref: const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: happy_for_ni_labels
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: my_local_test.happy_for_ni_label_links.label_id
rows: 1
Extra: Using index
2 rows in set (0.00 sec)
对应的 key, ref, rows 都有明显的优化。所以优化已经生效。
但是注意
完成这些数据数据定义索引修改的(DDL)，总共花费了 3.52 + 3.57 = 7.09 秒。在此期间，由于 ALTER 语句是阻塞操作，因此所有为表添加和修改数据的额外请求都被阻塞了。此时 SELECT 语句也会被阻塞而无法完成。并且修改大表的索引，会产生碎片和一些临时空间。
建议指数：三颗星
三、重用现在的索引，修改查询语句
首先分析下该表上索引基数(Cardinality)，重点查看下 idx_status_happy_for_ni_id_with_id
*************************** 2. row ***************************
Table: happy_for_ni_label_links
Non_unique: 1
Key_name: idx_status_happy_for_ni_id_with_id
Seq_in_index: 1
Column_name: status
Collation: A
Cardinality: 18
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: happy_for_ni_label_links
Non_unique: 1
Key_name: idx_status_happy_for_ni_id_with_id
Seq_in_index: 2
Column_name: happy_for_ni_id
Collation: A
Cardinality: 996079
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: happy_for_ni_label_links
Non_unique: 1
Key_name: idx_status_happy_for_ni_id_with_id
Seq_in_index: 3
Column_name: id
Collation: A
Cardinality: 996079
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
根据上述分析得出，status 的索引基数为 18， happy_for_ni_id 的索引基数为 996079， id 的索引基数为 996079
一般来说，将索引基数大的放置在索引的最前面。
那为什么要把索引基数大的放置在索引最前面呢？因为所以基数大，代表在数据库中唯一性值最高，唯一性值更高，代表的查询效率更快。如果数据库中，该列索引基数不高，查询要么关联其他字段，要么重复回表操作，CPU，内存和网络消耗更高一些。
但是这里为什么要把status 索引基数低的值放置在索引的最前面呢？
考虑到业务需要，会查询各种状态下的数据量，所以将 status 放在索引的最前面。该字段也是为了将来业务系统做扩展使用。
根据
KEY `idx_status_happy_for_ni_id_with_id` (`status`,`happy_for_ni_id`,`id`)
只有下面三种情况会使用到索引
1、WHERE happy_for_ni_label_links.status = xxx
2、WHERE happy_for_ni_label_links.status = xxx AND happy_for_ni_label_links.happy_for_ni_id = xxx
3、WHERE happy_for_ni_label_links.status = xxx AND happy_for_ni_label_links.happy_for_ni_id = xxx AND happy_for_ni_label_links.id = xxx
那么，我们的 SQL 就可以改写成
mysql> 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`.status = 0 AND `happy_for_ni_label_links`.`happy_for_ni_id` = 3369231\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: happy_for_ni_label_links
type: ref
possible_keys: idx_status_happy_for_ni_id_with_id,idx_status_checked_happy_for_ni_id_with_id,idx_label_id_with_id
key: idx_status_happy_for_ni_id_with_id
key_len: 5
ref: const,const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: happy_for_ni_labels
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: my_local_test.happy_for_ni_label_links.label_id
rows: 1
Extra: Using index
2 rows in set (0.00 sec)
ERROR:
No query specified
key 由 idx_xx_tag_id_with_id 变为 idx_status_happy_for_ni_id_with_id。
ref都由NULL类型，变为常量索引类型const, 看来效率提升的确实不少。
扫描的记录数，也有 461,1872 变为了现在的 1,1 说明优化确实起到了作用。
建议指数：五颗星
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
