mysql - 索引不影响 ms sql 2014 VS mysql (mariaDB 10) 中的时间执行

Question

我正在将统计分析系统从 MySQL (MariaDB 10) 移植到 MS SQL 2014，我发现了一个奇怪的事情。通常我大部分操作都是使用单字段和多字段索引:统计数据库在4核PC上保存了大约6000万个事件，分析包括漏斗、事件分割、队列分析、KPI等，因此可能会很慢有时。

但是当我在 MS SQL 上执行了多个查询序列，然后删除了所有索引(除了主聚集 id)时，我感到非常惊讶:我发现执行时间甚至减少了!我已经重新启动了服务器(缓存被清除)，但每次重新启动后结果都是相似的 - 我的查询在没有索引的情况下工作得更快(实际上速度是相同的，但没有时间花在手动创建索引上)。

我认为 MS SQL 为我创建了隐式索引，但在这种情况下，我似乎应该从查询中删除所有索引创建？在MySQL中你可以清楚地看到添加索引确实有效。这种 MS SQL 行为是否意味着我不再需要关心索引？我对查询进行了多次测试，似乎索引几乎不影响执行时间。我上次处理 MS SQL 是很久以前的事了，当时是 MS SQL 2000，所以也许 MSFT 在过去 15 年里开发了 f**n' AI？ :)

<小时/>

以防万一，这个测试sql代码(由后端为前端生成)如下。简而言之，它会生成过去 3 个月内特定类型事件的图表数据，然后按一个参数进行分段。它根据用户设置的约束(时间段、参数)从主事件表创建临时表，创建更多临时表和索引，执行多次连接并返回最终选择结果:

select  min(tmstamp), max(tmstamp)
    from  evt_db.dbo.events
    where  ( ( source = 3 )
              and  ( event_id=24 )
              and  tmstamp > 1451606400
              AND  tmstamp < 1458000000 
           ); 
select  min(param1), max(param1), count(DISTINCT(param1))
    from  evt_db.dbo.events
    WHERE  ( ( source = 3 )
              AND  ( event_id=24 )
              AND  tmstamp > 1451606400
              AND  tmstamp < 1458000000
           ); 

create  table #_tmp_times_calc_analyzer_0_0 (
    tm_start int, 
    tm_end int, 
    tm_origin int, 
    tm_num int
);

insert into  #_tmp_times_calc_analyzer_0_0 values
( 1451606400, 1452211200, 1451606400, 0 ), 
( 1452211200, 1452816000, 1452211200, 1 ), 
( 1452816000, 1453420800, 1452816000, 2 ), 
( 1453420800, 1454025600, 1453420800, 3 ), 
( 1454025600, 1454630400, 1454025600, 4 ), 
( 1454630400, 1455235200, 1454630400, 5 ), 
( 1455235200, 1455840000, 1455235200, 6 ), 
( 1455840000, 1456444800, 1455840000, 7 ), 
( 1456444800, 1457049600, 1456444800, 8 ), 
( 1457049600, 1457654400, 1457049600, 9 ), 
( 1457654400, 1458259200, 1457654400, 10 );

还有...

CREATE INDEX tm_num ON _tmp_times_calc_analyzer_0_0 (tm_num); 

SELECT  id, t1.uid, tmstamp, floor((tmstamp - 1451606400) / 604800) period_num,
        param1 into #_tmp_events_view_analyzer_0_0
    FROM  evt_db.dbo.events t1
    WHERE  ( ( source = 3 )
              AND  ( event_id=24 )
              AND  tmstamp > 1451606400
              AND  tmstamp < 1458000000
           ); 

CREATE INDEX uid ON _tmp_events_view_analyzer_0_0 (uid);

CREATE INDEX period_num ON _tmp_events_view_analyzer_0_0 (period_num);

CREATE INDEX tmstamp ON _tmp_events_view_analyzer_0_0 (tmstamp);

CREATE INDEX _index_param1 ON _tmp_events_view_analyzer_0_0 (param1);

create table #_tmp_median_analyzer_0_0 (ts int );

insert into #_tmp_median_analyzer_0_0
    select  distinct(param1) v
        from  #_tmp_events_view_analyzer_0_0
        where  param1 is not null
        order by  v ; 

select  tm_origin, count(distinct uid), count(distinct id)
    from  #_tmp_times_calc_analyzer_0_0
    left join  #_tmp_events_view_analyzer_0_0 ON period_num = tm_num
    GROUP BY  tm_origin; 
select  top 600 (param1) seg1, count(distinct uid), count(distinct id)
    from  #_tmp_events_view_analyzer_0_0
    GROUP BY  param1
    order by  1 asc;

还有...

select  seg1, tm_origin, count(distinct uid), count(distinct id)
    from  
      ( SELECT  (param1) seg1, tm_origin, uid, id
            from  #_tmp_times_calc_analyzer_0_0
            left join  #_tmp_events_view_analyzer_0_0 ON period_num = tm_num
            group by  param1, tm_origin, uid, id 
      ) t
    GROUP BY  seg1, tm_origin; 
select  min(param1), max(param1), round(avg(param1),0)
    from  #_tmp_events_view_analyzer_0_0; 

DECLARE @c BIGINT = (SELECT COUNT(*) FROM #_tmp_median_analyzer_0_0);

SELECT  round(AVG(1.0 * ts),0)
    FROM  
      ( SELECT  ts
            FROM  #_tmp_median_analyzer_0_0
            ORDER BY  ts OFFSET (@c - 1) / 2 ROWS
                FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY 
      ) AS median_val; 

Answer 1

evt_db.dbo.events 需要 INDEX(source, event, tmstamp)，其中 tmstamp 第三。对于 MySQL，前 2 个 SELECT 将完全在索引中运行(因为它是“覆盖”索引)。 source 和 event 可以采用任意顺序。

稍后，您将获得类似的 SELECT，但它也具有 id, t1.uid。您可以为其创建覆盖索引:INDEX(source, event, tmstamp, uid, id)。同样，tmstamp 必须位于列表中的第三位。

选择前 600 个 (param1) seg1、count(distinct uid)、count(distinct id) ... 可能会受益于 INDEX(param1, uid, id) ，其中 param1 必须位于第一个。

您列出的其他索引可能根本没有用。您尝试了哪些索引？

MySQL 和其他数据库之间的一个区别——MySQL 在查询中几乎从不使用多个索引。而且，根据我的经验，MySQL 的选择是“明智的”。也许 MSSql 太努力地尝试使用两个索引，而简单地扫描表会减少工作量。