MySQL一次慢查询的分析

基础信息

表结构如下：
CREATE TABLE `test`.`user_income` (
  `id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `amount` BIGINT(20) NOT NULL DEFAULT 0,
  `pay_time` BIGINT(20) NULL,
  `ext` TEXT NULL,
  `state` TINYINT(1) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_uid` USING BTREE (`user_id`) INVISIBLE,
  INDEX `idx_paytime` USING BTREE (`pay_time`) VISIBLE);

索引：idx_uid, idx_paytime
数据量：100W+行，数据量4G+ 
慢SQL:
    SELECT sum(amount) as amount
      FROM user_income 
     WHERE `user_id`= 139854281
       AND `state`= 3
       AND(`pay_time`>= 1551369600
       AND `pay_time`<= 1553961600)
执行时间大概是4秒多。

分析步骤

1.通过exlpain 分析  命中索引为idx_uid，过滤行数为3000多行，对于3000行的数据，4秒-5秒的执行时间是不能接收的，因此需要分析,为何这条SQL会需要4-5秒的时间

2.怀疑是否sum这个函数导致的。当将查询规则改为SELECT count(*), 活着 SELECT * 时，其执行时间没有变化。

3.怀疑是否pay_time更具有过滤性，在执行强制使用 idx_paytime之后,发现过滤的行数为200000多行，因此也排除了这个可能。其实这个想法使很愚蠢的，毕竟一个月的数据，过滤性当然比较低。

4.排除空间碎片的影响：show table status like '%user_income%'，发现data free 也才7M，说明这个也不是导致慢SQL的原因。

5.考虑到B+ TREE的特性，怀疑是否因为idx_uid选择出的主键id离散性太强。
通过 SELECT id FROM where user_id =  139854281, 发现最大id和最小id 相差10W多，但数据行数才3000多。这说明，虽然id是递增的，但ID的连续性不强。那么根据B+ TREE 的特性即数据库局部性原理。不能通过一次I/O读取到更多符合条件是数据，因为读取的主键分散，会导致更多的随机I/O请求,无法利用顺序读的优势。

当然这个想法发现最后想想是很愚蠢的，毕竟查询一个uid,想让用户的数据保存一个比较有序的主键ID，基本不可能。有间隔才是正常。

6.对于I/O次数太多这事，我的一个同事提出是否是因为 ext(type:TEXT)导致的。根据MySQL InnoDB 页表说明。一个页是16K。对于VARCHAR,TEXT,BLOB等类型数据，MySQL为了保证一页能保存更多的行数。对于数据大小超出一定大小之后，数据将不再保存到页表中(只保存未超出大小的数据，之后保存的是偏移量，偏移量指向行溢出页)，而是把数据保存在 uncompressed BLOB PAGE 中，原页表中，值保存对应的指针，指向该数据。
为了验证这个可能性。我同事通过复制这个表，然后将ext这个表置空。重新执行了原SQL，发现速度确实减少到2秒左右。 同时通过 show profile all for querry xx 发现 block_ops_in 减少了一大半。

总结

所以导致这条SQL慢的原因可能如下:
 1.idx_uid 查找到主键id过于分散，MySQL只能执行更多的随机I/O，无法有序利用顺序I/O。(但这个顺序ID的有序，是无法解决的，随着用户增加，必然的结果)。
 2.ext 字段导致了更多的溢出页，页同时导致了更多的I/O (已验证)。

解决办法

1.ext 垂直拆分， 将 ext 单独放到一个表（同事）
2.扩展idx_uid, 修改为 idx_uid_paytime (作者)
3.设计汇总表，把每日的数据汇总到一张表中，总收入=直接通过该表+ user_income 当日的数据。但是获取当日的数据，仍需要时间索引，才能高效。