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

02/05 00:00
阅读数 62

大家好,我是知数堂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: ALLpossible_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: ALLpossible_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: ALLpossible_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: ALLpossible_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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Using where; Open_full_table; Scanned all databases; Using filesort5 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: ALLpossible_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: ALLpossible_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: refpossible_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: ALLpossible_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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Using where; Open_full_table; Scanned all databases; Using filesort5 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)

扫码直达宝藏课程


本文分享自微信公众号 - 老叶茶馆(iMySQL_WX)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部