mysql慢查询优化实战_MySQL 慢查询优化实战一例

慢查询优化

一、查看表结构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 说明优化确实起到了作用。

建议指数：五颗星