MySQL查询冗余索引和未使用过的索引操作
作者:遗失的曾经! 发布时间:2024-01-22 22:04:23
MySQL5.7及以上版本提供直接查询冗余索引、重复索引和未使用过索引的视图,直接查询即可。
查询冗余索引、重复索引
select * sys.from schema_redundant_indexes;
查询未使用过的索引
select * from sys.schema_unused_indexes;
如果想在5.6和5.5版本使用,将视图转换成SQL语句查询即可
查询冗余索引、重复索引
select a.`table_schema`,a.`table_name`,a.`index_name`,a.`index_columns`,b.`index_name`,b.`index_columns`,concat('ALTER TABLE `',a.`table_schema`,'`.`',a.`table_name`,'` DROP INDEX `',a.`index_name`,'`') from ((select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`) a join (select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`) b on(((a.`table_schema` = b.`table_schema`) and (a.`table_name` = b.`table_name`)))) where ((a.`index_name` <> b.`index_name`) and (((a.`index_columns` = b.`index_columns`) and ((a.`non_unique` > b.`non_unique`) or ((a.`non_unique` = b.`non_unique`) and (if((a.`index_name` = 'PRIMARY'),'',a.`index_name`) > if((b.`index_name` = 'PRIMARY'),'',b.`index_name`))))) or ((locate(concat(a.`index_columns`,','),b.`index_columns`) = 1) and (a.`non_unique` = 1)) or ((locate(concat(b.`index_columns`,','),a.`index_columns`) = 1) and (b.`non_unique` = 0))));
查询未使用过的索引
select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`
补充:mysql ID 取余索引_mysql重复索引、冗余索引、未使用索引的定义和查找
1.冗余和重复索引
mysql允许在相同列上创建多个索引,无论是有意还是无意,mysql需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能。重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引,应该避免这样创建重复所以,发现以后也应该立即删除。但,在相同的列上创建不同类型的索引来满足不同的查询需求是可以的。
冗余索引和重复索引有一些不同,如果创建了索引(a,b),再创建索引(a)就是冗余索引,因为这只是前面一个索引的前缀索引,因此(a,b)也可以当作(a)来使用,但是(b,a)就不是冗余索引,索引(b)也不是,因为b不是索引(a,b)的最左前缀列,另外,其他不同类型的索引在相同列上创建(如哈希索引和全文索引)不会是btree索引的冗余索引。
另外:对于二级索引(a,id),id是主键,对于innodb来说,主键列已经包含在二级索引中了,所以这个也是冗余索引。大多数情况下都不需要冗余索引,应该尽量扩展已有的索引而不是创建新索引,但也有时候处于性能方面的考虑需要冗余索引,因为扩展已有的索引会导致其变得太大,从而影响其他使用该索引的查询性能。如:如果在整数列上有一个索引,现在需要额外增加一个很长的varchar列来扩展该索引,那么性可能会急剧下降,特别是有查询把这个索引当作覆盖索引,或者这是myisam表并且有很多范围查询的时候(由于myisam的前缀压缩)。
如:表userinfo,myisam引擎,有100W行记录,每个state_id值大概2W行,在state_id列有一个索引对下面的查询有用:如:select count(*) from userinfo where state_id=5;测试每秒115次QPS
对于下面的查询这个state_id列的索引就不太顶用了,每秒QPS是10次
select state_id,city,address from userinfo where state_id=5;
如果把state_id索引扩展为(state_id,city,address),那么第二个查询的性能更快了,但是第一个查询却变慢了,如果要两个查询都快,那么就必须要把state_id列索引进行冗余了。但如果是innodb表,不冗余state_id列索引对第一个查询的影响并不明显,因为innodb没有使用索引压缩,myisam和innmodb表使用不同的索引策略的select查询的qps测试结果(以下测试数据仅供参考):
只有state_id列索引 只有state_id_2索引 同时有两个索引
myisam,第一个查询 114.96 25.40 112.19
myisam,第二个查询 9.97 16.34 16.37
innodb,第一个查询 108.55 100.33 107.97
innodb,第二个查询 12.12 28.04 28.06
从上图中可以看出,两个索引都有的时候,缺点是成本更高,下面是在不同的索引策略时插入innodb和myisam表100W行数据的速度(以下测试数据仅供参考):
只有state_id列索引 同时有两个索引
innodb,对有两个索引都有足够的内容的时候 80秒 136秒
myisam,只有一个索引有足够的内容的时候 72秒 470秒
可以看到,不论什么引擎,索引越多,插入速度越慢,特别是新增索引后导致达到了内存瓶颈的时候。解决冗余索引和重复索引的方法很简单,删除这些索引就可以了,但首先要做的是找出这样的索引,可以通过一些复杂的访问information_schema表的查询来找,不过还有两个更简单的方法,使用:shlomi noach的common_schema中的一些视图来定位,也可以使用percona toolkit中的pt-dupulicate-key-checker工具,该工具通过分析表结构来找出冗余和重复的索引,对于大型服务器来说,使用外部的工具更合适,如果服务器上有大量的数据或者大量的表,查询information_schema表可能会导致性能问题。建议使用pt-dupulicate-key-checker工具。
在删除索引的时候要非常小心:
如果在innodb引擎表上有where a=5 order by id这样的查询,那么索引(a)就会很有用,索引(a,b)实际上是(a,b,id)索引,这个索引对于where a=5 order by id这样的查询就无法使用索引做排序,而只能使用文件排序了。所以,建议使用percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更。
2. 未使用的索引
除了冗余索引和重复索引,可能还会有一些服务器永远不使用的索引,这样的索引完全是累赘,建议考虑删除,有两个工具可以帮助定位未使用的索引:
A:在percona server或者mariadb中先打开userstat=ON服务器变量,默认是关闭的,然后让服务器运行一段时间,再通过查询information_schema.index_statistics就能查到每个索引的使用频率。
B:使用percona toolkit中的pt-index-usage工具,该工具可以读取查询日志,并对日志中的每个查询进行explain操作,然后打印出关羽索引和查询的报告,这个工具不仅可以找出哪些索引是未使用的,还可以了解查询的执行计划,如:在某些情况下有些类似的查询的执行方式不一样,这可以帮助定位到那些偶尔服务器质量差的查询,该工具也可以将结果写入到mysql的表中,方便查询结果。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。如有错误或未考虑完全的地方,望不吝赐教。
来源:https://blog.csdn.net/u014609263/article/details/113540365
猜你喜欢
- 统计表中常常以本年累计、上年同期(累计)、当期(例如当月)完成、上月完成为统计数据,并进行同比、环比分析。如下月报统计表所示样例,本文将使用
- 前言:Python函数之所以很好用,还有一点就的能传递参数实现不同场景的灵活使用,对于函数参数的类型小编总结了6种不同的形式。下面来一一学习
- 在 EeePC 上装了个 Mac OS X,相应的开发工具也选择了 Coda。在
- 阅读《YUI学习笔记(1)》YAHOO.lang.dump 与 YAHOO.lang.substitute。1.&nbs
- 本文实例讲述了Python读写及备份oracle数据库操作。分享给大家供大家参考,具体如下:最近项目中需要用到Python调用oracle实
- 一、Pandas Series对象Pandas 是基于 NumPy 设计实现的 Python 数据分析库,Pandas 提供了大量的能让我们
- 引子Matlab中有一个函数叫做find,可以很方便地寻找数组内特定元素的下标,即:Find indices and values of n
- Python的3.0版本,常被称为Python 3000,或简称Py3k。相对于Python的早期版本,这是一个较大的升级。为了不带入过多的
- 字典简介Python 中字典和集合也是非常相近的概念,而且从外观看上去,也有那么一丝丝的相似,Python 语言中字典类型是非常灵活的,在实
- 前言一年一度的虐狗节终于过去了,朋友圈各种晒,晒自拍,晒娃,晒美食,秀恩爱的。程序员在晒什么,程序员在加班。但是礼物还是少不了的,送什么好?
- 前言Scrapy是一个非常好的抓取框架,它不仅提供了一些开箱可用的基础组建,还能够根据自己的需求,进行强大的自定义。本文主要给大家介绍了关于
- 详解Python MD5加密Python 3下MD5加密# 由于MD5模块在python3中被移除# 在python3中使用hashlib模
- 记录训练过程中的每一步的loss变化 if verbose and step % verbose == 0: sys.stdout.writ
- Portable Document Format(可移植文档格式),或者PDF是一种文件格式,可以用于跨操作系统的呈现和文档交换。尽管PDF
- 背景:读取TXT文件,加载到kafka中,然后通过logstash消费kafka中的数据加载到es中第一步:导入相应的依赖包pip inst
- 环境:Zend Studio 8.0Zend Studio是PHP开发者的首选开发工具,其地位相当于微软开发工具中的Visual Studi
- 前言在学习任何一个后端技术,如果不让数据库参与进来,那只能说在学习过程中都不算完整的。以前用的是5.7版本的MySQL,在学习实践Sprin
- Python元字典字典(dictionary)是除列表以外python之中最灵活的内置数据结构类型。列表是有序的对象结合,字典是无序的对象集
- 1、创建存储过程 create or replace procedure test(var_name_1 in type,var_name_
- python字符串查找函数的使用打开Python开发工具IDLE,新建‘findstr.py'文件,并写代码如下:s ='/