MySQL中怎样快速找出超长索引-低调大师

MySQL中怎样快速找出超长索引

2021-02-03 489

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

需求：

想要查找哪些索引太长了，这个SQL在5.7下跑的特别慢，8.0则挺快的，帮看下有啥优化方案没

具体SQL 和执行计划如下：

SELECT c.TABLE_SCHEMA AS DB,  c .TABLE_NAME AS TBL, c.COLUMN_NAME AS COL, c.CHARACTER_OCTET_LENGTH AS COL_LEN_BYTES,  s.INDEX_NAME,  s.SUB_PART * CHARACTER_OCTET_LENGTH/CHARACTER_MAXIMUM_LENGTH AS SUB_PART_LENFROM information_schema.COLUMNS c INNER JOIN information_schema.STATISTICS s USING(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)INNER JOIN information_schema.TABLES t USING(TABLE_SCHEMA, TABLE_NAME)  WHERE c.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test')AND c.DATA_TYPE IN ("varchar", "char", "text", "blob") AND ((CHARACTER_OCTET_LENGTH > 50 and SUB_PART is null) orSUB_PART * CHARACTER_OCTET_LENGTH/CHARACTER_MAXIMUM_LENGTH > 50)AND t.TABLE_ROWS > 10000ORDER BY COL_LEN_BYTES DESC;
执行计划
*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: c   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: Using where; Open_frm_only; Scanned all databases; Using temporary; Using filesort*************************** 2. row ***************************           id: 1  select_type: SIMPLE        table: s   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: Using where; Open_frm_only; Scanned all databases; Using join buffer (Block Nested Loop)*************************** 3. row ***************************           id: 1  select_type: SIMPLE        table: t   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 join buffer (Block Nested Loop)3 rows in set, 1 warning (0.01 sec)

select count(*) from information_schema.tables;+----------+| count(*) |+----------+|    33600 |+----------+
select count(*) from information_schema.COLUMNS;+----------+| count(*) |+----------+|   342967 |+----------+select count(*) from information_schema.STATISTICS;+----------+| count(*) |+----------+|   135167 |+----------+

上面的SQL 运行450+ s 也运行不出来，最后kill掉了。

我们初步分析一下，从执行计划中可以看出三个表都是ALL 所以很慢

那添加索引不就行了吗，因为是系统表，所以不能随便添加！

那该怎么办？想到了AUTOKEY 就是临时索引，那思路就是改写SQL

达到生成临时索引，最终达到优化效果

改写的SQL 如下

SELECT c.TABLE_SCHEMA AS DB, c.TABLE_NAME AS TBL, c.COLUMN_NAME AS COL, c.CHARACTER_OCTET_LENGTH AS COL_LEN_BYTES, s.INDEX_NAME,s.SUB_PART * c.CHARACTER_OCTET_LENGTH/c.CHARACTER_MAXIMUM_LENGTH AS SUB_PART_LENFROM ( select c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME ,c.CHARACTER_OCTET_LENGTH ,c.CHARACTER_MAXIMUM_LENGTH , c.DATA_TYPEfrom information_schema.COLUMNS c WHERE c.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test') AND c.DATA_TYPE IN ("varchar", "char", "text", "blob") limit 10000000000) c INNER JOIN (select s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME ,s.SUB_PART,s.INDEX_NAMEfrom information_schema.STATISTICS s WHERE s.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test')limit 10000000000 
)s USING(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)INNER JOIN information_schema.TABLES t USING(TABLE_SCHEMA, TABLE_NAME) WHERE c.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test') AND c.DATA_TYPE IN ("varchar", "char", "text", "blob") AND ((c.CHARACTER_OCTET_LENGTH > 50 and s.SUB_PART is null) ors.SUB_PART * c.CHARACTER_OCTET_LENGTH/c.CHARACTER_MAXIMUM_LENGTH > 50)AND t.TABLE_ROWS > 10000ORDER BY COL_LEN_BYTES DESC;

*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t   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 temporary; Using filesort*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: <derived2>   partitions: NULL         type: refpossible_keys: <auto_key0>          key: <auto_key0>      key_len: 388          ref: information_schema.t.TABLE_SCHEMA,information_schema.t.TABLE_NAME         rows: 2     filtered: 50.00        Extra: Using where*************************** 3. row ***************************           id: 1  select_type: PRIMARY        table: <derived3>   partitions: NULL         type: refpossible_keys: <auto_key0>          key: <auto_key0>      key_len: 582          ref: information_schema.t.TABLE_SCHEMA,information_schema.t.TABLE_NAME,c.COLUMN_NAME         rows: 2     filtered: 100.00        Extra: Using where*************************** 4. row ***************************           id: 3  select_type: DERIVED        table: s   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: Using where; Open_frm_only; Scanned all databases*************************** 5. row ***************************           id: 2  select_type: DERIVED        table: c   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: Using where; Open_frm_only; Scanned all databases5 rows in set, 1 warning (0.01 sec)

结果来了 2463 rows in set, 417 warnings (23.39 sec)

但是经过几次运行之后有时候是40多秒有时候甚至达到了166s 非常不稳定！

那分析下上面这个SQL的问题在哪里？

问题就是生成的AUTO KEY的量相对来说非常大！因为没有进行任何过滤

那现在的思路就是对生成的AUTOKEY的量进行减少

我们通过相对小的表TABLES 表生成autokey 之后 STATISTICS ,COLUMNS

表分别跟 TABLES 表进行JOIN 然后减少数据量达到减少生成AUOKEY 的量减少达到优化目的，具体的方法如下


select count(1) from (select s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME ,s.SUB_PART,s.INDEX_NAMEfrom information_schema.STATISTICS s WHERE s.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test') )s straight_join (select t.TABLE_SCHEMA, t.TABLE_NAMEfrom  information_schema.TABLES t  WHERE t.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test')  AND t.TABLE_ROWS > 10000limit 10000000000) t on s.TABLE_SCHEMA=t.TABLE_SCHEMA and  s.TABLE_NAME =t.TABLE_NAME
*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: s   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: Using where; Open_frm_only; Scanned all databases*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: <derived3>   partitions: NULL         type: refpossible_keys: <auto_key0>          key: <auto_key0>      key_len: 388          ref: information_schema.s.TABLE_SCHEMA,information_schema.s.TABLE_NAME         rows: 2     filtered: 100.00        Extra: Using index*************************** 3. row ***************************           id: 3  select_type: DERIVED        table: t   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 databases3 rows in set, 1 warning (0.00 sec)

+----------+| count(1) |+----------+|     7478 |+----------+1 row in set, 40 warnings (7.52 sec)

select count(1) from ( select c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME ,c.CHARACTER_OCTET_LENGTH ,c.CHARACTER_MAXIMUM_LENGTH , c.DATA_TYPEfrom information_schema.COLUMNS c WHERE c.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test') AND c.DATA_TYPE IN ("varchar", "char", "text", "blob")  ) c straight_join (select t.TABLE_SCHEMA, t.TABLE_NAMEfrom  information_schema.TABLES t  WHERE t.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test')  AND t.TABLE_ROWS > 10000limit 10000000000) t on c.TABLE_SCHEMA=t.TABLE_SCHEMA and  c.TABLE_NAME =t.TABLE_NAME
*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: c   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: Using where; Open_frm_only; Scanned all databases*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: <derived3>   partitions: NULL         type: refpossible_keys: <auto_key0>          key: <auto_key0>      key_len: 388          ref: information_schema.c.TABLE_SCHEMA,information_schema.c.TABLE_NAME         rows: 2     filtered: 100.00        Extra: Using index*************************** 3. row ***************************           id: 3  select_type: DERIVED        table: t   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 databases3 rows in set, 1 warning (0.00 sec)
+----------+| count(1) |+----------+|     8106 |+----------+1 row in set, 417 warnings (8.62 sec)

最终SQL 如下


SELECT c.TABLE_SCHEMA AS DB, c.TABLE_NAME AS TBL, c.COLUMN_NAME AS COL, c.CHARACTER_OCTET_LENGTH AS COL_LEN_BYTES, s.INDEX_NAME,s.SUB_PART * c.CHARACTER_OCTET_LENGTH/c.CHARACTER_MAXIMUM_LENGTH AS SUB_PART_LENfrom ( select c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME ,c.CHARACTER_OCTET_LENGTH ,c.CHARACTER_MAXIMUM_LENGTH , c.DATA_TYPEfrom information_schema.COLUMNS c WHERE c.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test') AND c.DATA_TYPE IN ("varchar", "char", "text", "blob")  ) c straight_join (select t.TABLE_SCHEMA, t.TABLE_NAMEfrom  information_schema.TABLES t  WHERE t.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test')  AND t.TABLE_ROWS > 10000limit 10000000000) t on c.TABLE_SCHEMA=t.TABLE_SCHEMA and  c.TABLE_NAME =t.TABLE_NAME
straight_join(select s.* from (select s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME ,s.SUB_PART,s.INDEX_NAMEfrom information_schema.STATISTICS s WHERE s.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test') )s straight_join (select t.TABLE_SCHEMA, t.TABLE_NAMEfrom  information_schema.TABLES t  WHERE t.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys', 'test')  AND t.TABLE_ROWS > 10000limit 10000000000) t on s.TABLE_SCHEMA=t.TABLE_SCHEMA and  s.TABLE_NAME =t.TABLE_NAMElimit 10000000000) s on c.TABLE_SCHEMA=s.TABLE_SCHEMA and  c.TABLE_NAME=s.TABLE_NAME and c.COLUMN_NAME =s.COLUMN_NAMEwhere   ((c.CHARACTER_OCTET_LENGTH > 50 and s.SUB_PART is null) ors.SUB_PART * c.CHARACTER_OCTET_LENGTH/c.CHARACTER_MAXIMUM_LENGTH > 50)

*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: c   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: Using where; Open_frm_only; Scanned all databases*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: <derived3>   partitions: NULL         type: refpossible_keys: <auto_key0>          key: <auto_key0>      key_len: 388          ref: information_schema.c.TABLE_SCHEMA,information_schema.c.TABLE_NAME         rows: 2     filtered: 100.00        Extra: Using index*************************** 3. row ***************************           id: 1  select_type: PRIMARY        table: <derived4>   partitions: NULL         type: refpossible_keys: <auto_key0>          key: <auto_key0>      key_len: 582          ref: information_schema.c.TABLE_SCHEMA,information_schema.c.TABLE_NAME,information_schema.c.COLUMN_NAME         rows: 2     filtered: 100.00        Extra: Using where*************************** 4. row ***************************           id: 4  select_type: DERIVED        table: s   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: Using where; Open_frm_only; Scanned all databases*************************** 5. row ***************************           id: 4  select_type: DERIVED        table: <derived6>   partitions: NULL         type: refpossible_keys: <auto_key0>          key: <auto_key0>      key_len: 388          ref: information_schema.s.TABLE_SCHEMA,information_schema.s.TABLE_NAME         rows: 2     filtered: 100.00        Extra: Using index*************************** 6. row ***************************           id: 6  select_type: DERIVED        table: t   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*************************** 7. row ***************************           id: 3  select_type: DERIVED        table: t   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 databases7 rows in set, 1 warning (0.00 sec)

看起来稳定了，跑了几次，都没超过15秒

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

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

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

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

欢迎加入知数堂大家庭。

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

扫码直达宝藏课程

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

微信关注我们

原文链接：https://my.oschina.net/yejr/blog/4944868

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

腾讯基于 Flink SQL 的功能扩展与深度优化实践

整理：戴季国（Flink社区志愿者）校对：苗文婷（Flink 社区志愿者）摘要：本文由腾讯高级工程师杜立分享，主要介绍腾讯实时计算平台针对 Flink SQL 所做的优化，内容包括： Flink SQL 现状窗口功能的扩展回撤流的优化未来的规划 Tips：点击文末「阅读原文」即可回顾作者原版分享视频～一、背景及现状 1.三种模式的分析 Flink 作业目前有三种创建方式：JAR 模式、画布模式和 SQL 模式。不同的提交作业的方式针对的人群也是不一样的。 ■ Jar 模式 Jar 模式基于 DataStream/DataSet API 开发，主要针对的是底层的开发人员。优点：功能灵活多变，因为它底层的 DataStream/DataSet API 是 Flink 的原生 API，你可以用它们开发任何你想要的算子功能或者 DAG 图；性能优化方便，可以非常有针对性的去优化每一个算子的性能。缺点：依赖更新繁琐，无论扩展作业逻辑或是 Flink 版本的升级，都要去更新作业的代码以及依赖版本；学习门槛较高。 ■ 画布模式所谓的画布模式，一般来讲会提供一个可...

2021-02-02

504

去年 5 月初，王者荣耀上线了一个挑战 AI 「绝悟」的限时活动，当时打开游戏界面，就能看到「挑战·绝悟」的入口，活动出来，我就去体验了一下。这个挑战一共五关，一关比一关难，像我这样的农药大神前几关随便过，最后一关就只能推塔了，正面硬刚肯定刚不过。这种不敢正面怼的感觉「似曾相识」，我在英雄联盟的末日人机中也体会过，拉克丝的天女散花我至今还有阴影。腾讯的「绝悟」在世界冠军杯半决赛中打败了职业选手，随后又在面向顶级业余玩家开放的体验测试中拿下 99.8% 的胜率。虽然输了一场，但这个出道没多久的 AI 玩家在电竞圈也算打出了名气，当然这个战绩和王校长比还差点，校长在 100% 胜率时宣布退役，是 LOL 电竞圈唯一一位主动放弃 S 赛名额的选手。话说回来，训练出像「绝悟」这样吊的 AI，需要腾讯掌握的海量数据和强大的财力，平民也只能体会挨打的快乐，普通玩家要想训练出一个这样的人工智能不太现实。不过，今天，我给大家带来一个「平民版」的王者农药 AI。这个项目已经在 GitHub 开源，作者是 FengQuanLi，我把预训练文件下载方式和开源地址放在了后台，关注微信公众号「逛...

2021-02-03

519

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。