最近為公司寫(xiě)后臺(tái)程序，就是數(shù)據(jù)庫(kù)檢索，其實(shí)就是拼sql，在測(cè)試環(huán)境運(yùn)行沒(méi)問(wèn)題，線上卻沒(méi)有數(shù)據(jù)顯示，最后發(fā)現(xiàn)是sql運(yùn)行超時(shí)，整整6s，最后給優(yōu)化到0.5s

表結(jié)構(gòu)：

| newreport | CREATE TABLE `newreport` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(32) NOT NULL DEFAULT '' COMMENT '...',
  `report_id` varchar(32) NOT NULL DEFAULT '' COMMENT '...',
  `report_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '...',
  `reason_ids` varchar(256) NOT NULL DEFAULT '' COMMENT '...',
  `description` varchar(256) NOT NULL DEFAULT '' COMMENT '...',
  `pictures` varchar(256) NOT NULL DEFAULT '' COMMENT '...',
  `createTs` int(10) unsigned NOT NULL DEFAULT '0',
  `comment_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '...',
  `status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '...',
  `extra` text,
  `chat_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '...',
  `version` varchar(32) NOT NULL DEFAULT '' COMMENT '...',
  `region` varchar(32) NOT NULL DEFAULT '' COMMENT '...',
  `operator` varchar(32) NOT NULL DEFAULT '' COMMENT '...'
  `update_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '...',
  PRIMARY KEY (`id`),
  KEY `report_id` (`report_id`),
  KEY `idx_user_id` (`user_id`),
) ENGINE=InnoDB AUTO_INCREMENT=1701203 DEFAULT CHARSET=utf8 |

原sql

select sql_no_cache report_id, count(*) as report_total, max(createTs) as recent_time from newreport where report_type=4  and createTs between 1566849758 and 1567454558  and 0='\'\''  and 2=2  and status=0  group by report_id  order by report_total desc, createTs  limit 0,20\G

就是這樣的sql運(yùn)行時(shí)間6s

為createTs加了索引：

  KEY `idx_createTs` (`createTs`)

時(shí)間減少到0.5s ...
當(dāng)然沒(méi)有完，要不然這個(gè)就太水了，加個(gè)索引就解決了還有啥說(shuō)的。

createTs小范圍createTs between 1566849758 and 1567454558對(duì)應(yīng)的數(shù)據(jù)量占總數(shù)的0.87%。
大范圍 createTs between 1556668800 and 1567454558對(duì)應(yīng)的行數(shù)占總數(shù)97% 時(shí)間跨度20190501-20190902。
每天新增數(shù)據(jù)量接近2k，數(shù)據(jù)庫(kù)總數(shù) 1701186。
當(dāng)范圍查詢超過(guò)總數(shù)一定比例，mysql會(huì)全局掃描。

問(wèn)題來(lái)了，上面的查詢時(shí)間范圍為大概一周，當(dāng)查詢時(shí)間增加到4個(gè)月時(shí)，查詢時(shí)間降到3s，繼續(xù)優(yōu)化：

select sql_no_cache report_id, count(*) as report_total, max(createTs) as recent_time from newreport s right join (select id from newreport where createTs > 1556668800 and createTs < 1567454558) t using(id) where report_type=4 and 0='\'\''  and 2=2  and status=0  group by report_id  order by report_total desc, recent_time limit 0,20;

explain結(jié)果：

explain select sql_no_cache report_id, count(*) as report_total, max(createTs) as recent_time from newreport where id in (select id from newreport where createTs > 1556668800 and createTs < 1567454558) and report_type=4 and 0='\'\''  and 2=2  and status=0  group by report_id  order by report_total desc, createTs limit 0,20\G


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: newreport
   partitions: NULL
         type: ALL
possible_keys: PRIMARY,report_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1655044
     filtered: 1.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: newreport
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,idx_createTs
          key: PRIMARY
      key_len: 8
          ref: starmaker.newreport.id
         rows: 1
     filtered: 36.68
        Extra: Using where

時(shí)間區(qū)間4個(gè)月，查詢時(shí)間0.8s，勉強(qiáng)夠用，可是看到right join的sql并沒(méi)有使用createTs索引，繼續(xù)優(yōu)化，強(qiáng)制讓mysql使用這個(gè)索引

explain select sql_no_cache report_id, count(*) as report_total, max(createTs) as recent_time from newreport s right join (select id from newreport use index(idx_createTs) where createTs > 1556668800 and createTs < 1567454558) t using(id) where report_type=4 and 0='\'\''  and 2=2  and status=0  group by report_id  order by report_total desc, createTs limit 0,20\G

explain結(jié)果：

           id: 1
  select_type: SIMPLE
        table: newreport
   partitions: NULL
         type: range
possible_keys: idx_createTs
          key: idx_createTs
      key_len: 4
          ref: NULL
         rows: 607124
     filtered: 100.00
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,report_id
          key: PRIMARY
      key_len: 8
          ref: starmaker.newreport.id
         rows: 1
     filtered: 5.00
        Extra: Using where
2 rows in set, 2 warnings (0.00 sec)

掃描行數(shù)由1655044減少到607124
時(shí)間區(qū)間4個(gè)月，查詢時(shí)間0.5s

當(dāng)然如果可以，還能繼續(xù)優(yōu)化，那就是修改索引createTs為聯(lián)合索引，where子句中離散型字段有點(diǎn)多，也就是distinct field后，沒(méi)有幾個(gè)值的field，可以把這幾個(gè)field放在createTs前面構(gòu)建聯(lián)合索引，比如(field, createTs)，放在createTs的前面是因?yàn)樽钭笄熬Y，當(dāng)只需要聯(lián)合索引的后面字段時(shí)，可以把聯(lián)合索引靠前的字段通過(guò) field in (...) and createTs = ...利用起來(lái)。

但是索引不是越多越好，因?yàn)榫S護(hù)索引也是需要成本的嘛，索引多了，插入更新就會(huì)困難，要做的是將已有的資源發(fā)揮到極限。

至于為什么要right join一下，因?yàn)閎tree原理，createTs是非主鍵索引，底層維護(hù)存儲(chǔ)的是createTs和對(duì)應(yīng)記錄行的主鍵，通過(guò)這個(gè)非聚簇索引找到滿足條件的id，這個(gè)id就是主鍵索引，即聚簇索引，聚簇索引底層除了保存了主鍵，還有主鍵對(duì)應(yīng)的記錄行，也就是聚簇索引保存了數(shù)據(jù)表，直接利用聚簇索引的話，不就省了回表操作嘛。