MySQL 5.7查询sys.schema_redundant_indexes居然慢如蜗牛...

老叶茶馆 2021-02-05

616

大家好，我是知数堂SQL 优化班老师网名：骑龟的兔子

作为DBA 经常使用SYS视图，但是如下情况。

查询mysql自己创建好的 sys 系统视图，超过70秒还没结果，超时了。


SELECT TABLE_SCHEMA, TABLE_NAME,                
REDUNDANT_INDEX_NAME, REDUNDANT_INDEX_COLUMNS 
FROM  sys.schema_redundant_indexes 
WHERE  TABLE_SCHEMA NOT IN ('information_schema','performance_schema','mysql','sys', 'test') 
GROUP BY TABLE_SCHEMA, TABLE_NAME,  REDUNDANT_INDEX_NAME, REDUNDANT_INDEX_COLUMNS;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: <derived3>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: <derived4>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 4. row ***************************
           id: 4
  select_type: DERIVED
        table: statistics
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned all databases; Using filesort
*************************** 5. row ***************************
           id: 3
  select_type: DERIVED
        table: statistics
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned all databases; Using filesort
5 rows in set, 1 warning (0.00 sec)

一般情况下，不会出现问题，但是，如本案例，表很多的时候就会出现问题

select count(*) from information_schema.STATISTICS;
+----------+
| count(*) |
+----------+
|   141719 |
+----------+

那这时候该怎么办呢？我们再分析一下执行计划，可以看出都是TYPE 为 ALL 那为什么都是ALL 呢？再看执行计划发现 ROWS 都很小，导致MySQL 误认为数据量很小，就不走索引走 Using join buffer (Block Nested Loop)

而实际情况呢，如上所示，数据量很多

找出问题之后，就好办了，既然数据量判断出问题，本应该搜集统计信息，但是由于是系统表，所以不能，所以我们就把 Using join buffer (Block Nested Loop)

这个功能session 级别关掉就可以了

set session optimizer_switch='block_nested_loop=off' ;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: <derived3>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: <derived4>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 388
          ref: redundant_keys.table_schema,redundant_keys.table_name
         rows: 2
     filtered: 50.00
        Extra: Using where
*************************** 4. row ***************************
           id: 4
  select_type: DERIVED
        table: statistics
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned all databases; Using filesort
*************************** 5. row ***************************
           id: 3
  select_type: DERIVED
        table: statistics
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned all databases; Using filesort
5 rows in set, 1 warning (0.00 sec)

从上可以看到，使用了 <auto_key0>

最终运行结果为

460 rows in set, 5 warnings (14.99 sec)

我的新一轮的SQL 优化课即将在春节后开课

我是知数堂SQL 优化班老师~ ^^

如有关于SQL优化方面疑问和一起交流的请加并且 @兔子@知数堂SQL优化

高性能MySQL,SQL优化群有叶金荣，吴炳锡两位大神坐镇：579036588

欢迎加入知数堂大家庭。

我的微信公众号：SQL开发与优化(sqlturning)

扫码直达宝藏课程

mysql

文章转载自老叶茶馆，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL 5.7查询sys.schema_redundant_indexes居然慢如蜗牛...

评论