某系统mysql实例，IOPS较高，有明显陡增波峰

首先看的是慢sql。

但是还是那句话，慢sql不一定会抓取到关键sql，执行时间不超过1s，但是执行次数较高，执行计划不够优秀的sql，同样需要优化

 

因为平台有抓取所有sql，查看了在波峰期间次数较高的sql有2个，一个delete一个insert。

 delete from tablzl where lzl_uid = :1 and uuid = :2 and snapshot_idx != :3

 insert into tablzl ( gmt_create , lzl_uid  ) values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 ) ,..., :350 )       

insert语句是insert values，这样的sql优化空间不大，只有从业务逻辑和磁盘性能上优化。

但是这个delete语句是有优化空间的，优化空间就在where条件的过滤


    

 

 tablzl | CREATE TABLE `tablzl` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '??',
  `gmt_create` datetime NOT NULL COMMENT '????',
  `lzl_uid` bigint(20) unsigned NOT NULL COMMENT 'lzl_uid',
  `uuid` varchar(64) NOT NULL COMMENT 'uuid',
  `namexxx` varchar(1024) NOT NULL COMMENT '????',
  `lzlzlzlcol` varchar(1024) DEFAULT NULL COMMENT '??????',
  `col123` varchar(128) NOT NULL COMMENT '????',
  `col456` varchar(128) DEFAULT NULL COMMENT '????',
  `snapshot_idx` varchar(128) NOT NULL COMMENT '??id',
  PRIMARY KEY (`id`),
  KEY `idx_namexxx` (`namexxx`(128)),
  KEY `idx_lzl_uid` (`lzl_uid`),
  KEY `idx_uuid` (`uuid`),
  KEY `idx_lzlzlzlcol` (`lzlzlzlcol`(128)),
  KEY `idx_lzl_uid_uuid` (`lzl_uid`,`uuid`),
  KEY `idx_gmt_create` (`gmt_create`),
  KEY `idx_lzl_uid_software` (`lzl_uid`,`namexxx`(255),`lzlzlzlcol`(255),`col123`),
  KEY `idx_lzl_uid_uuid_software` (`lzl_uid`,`uuid`,`namexxx`(255),`lzlzlzlcol`(255),`col123`),
  KEY `idx_snapshot_idx_lzl_uid` (`snapshot_idx`,`lzl_uid`)
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8 COMMENT='????' |

 mysql> select count(1) from tablzl;
+----------+
| count(1) |
+----------+
|   977793 |
+----------+
1 row in set (1.72 sec)

 

mysql> explain delete from tablzl where lzl_uid=1 and uuid=1 and snapshot_idx !=1;
+----+-------------+-------------------------+-------+-----------------------------------------------------------------------------+-------------+---------+-------+------+-------------+
| id | select_type | table                   | type  | possible_keys                                                               | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------------------------+-------+-----------------------------------------------------------------------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tablzl | range | idx_lzl_uid,idx_lzl_uid_uuid,idx_lzl_uid_software,idx_lzl_uid_uuid_software | idx_lzl_uid | 8       | const |    1 | Using where |
+----+-------------+-------------------------+-------+-----------------------------------------------------------------------------+-------------+---------+-------+------+-------------+

    

表的key有很多个，执行计划的possible keys也有很多，实际执行的key为idx_lzl_uid ，但是这个索引只能过滤lzl_uuid，不能过滤uuid（！=是不能走索引的，所以不用考虑snapshot_idx的过滤）

但是索引idx_lzl_uid_uuid是lzl_uuid和uuid列的联合索引，走这个索引的效率明显更高。mysql优化器没有选择最优的索引（这种情况我已经遇到很多次了，mysql优化器果然不太行）

怎么让它走正确的索引呢？

1.hint

2.删除不正确的索引（某些情况下可用，谨慎！），8.0以上可以索引invisible

我这个sql没法改，删除索引看看行不行

因为，idx_lzl_uid_uuid索引的前导列是lzl_uid，索引只要需要走这个列索引的sql，都可以走idx_lzl_uid_uuid，那么idx_lzl_uid（lzl_uid)这个索引就重复了。

而且删除重复索引还可以降低dml维护索引的代价

 

drop重复索引：    

mysql> drop index idx_lzl_uid on tablzl;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

--删除索引后走到正确的索引上

mysql> explain delete from tablzl where lzl_uid=1 and uuid=1 and snapshot_idx !=1;
+----+-------------+-------------------------+-------+-----------------------------------------------------------------+------------------+---------+-------+------+-------------+
| id | select_type | table                   | type  | possible_keys                                                   | key              | key_len | ref   | rows | Extra       |
+----+-------------+-------------------------+-------+-----------------------------------------------------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tablzl | range | idx_lzl_uid_uuid,idx_lzl_uid_software,idx_lzl_uid_uuid_software | idx_lzl_uid_uuid | 8       | const |    1 | Using where |
+----+-------------+-------------------------+-------+-----------------------------------------------------------------+------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

观察发现实例的iops降低了80%

 

总结：

1.慢sql不一定会抓到隐患sql。比如这个案例，sql走了索引，执行时间在1s以内，但是执行次数高，也可能大量占用IO等资源

2.mysql优化器不一定总是选择最优执行计划