分享 MySQL 慢查询优化实战一例

ibugs · 2015年07月27日 · 最后由 jasontang168 回复于 2015年07月28日 · 3271 次阅读

慢查询优化

一、查看表结构

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 说明优化确实起到了作用。

建议指数：五颗星

我的简书

4 个赞

jasontang168 #1 2015年07月28日

赞一个楼主

需要登录后方可回复, 如果你还没有账号请注册新账号

4 个赞

共收到 1 条回复

收到新回复，点击立即加载