MySQL查询冗余索引和未使用过的索引操作

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的表中,方便查询结果。

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。如有错误或未考虑完全的地方,望不吝赐教。

(0)

相关推荐

  • mysql重复索引与冗余索引实例分析

    本文实例讲述了mysql重复索引与冗余索引.分享给大家供大家参考,具体如下: 重复索引:表示一个列或者顺序相同的几个列上建立的多个索引. 冗余索引:两个索引所覆盖的列重叠 冗余索引在一些特殊的场景下使用到了索引覆盖,所以比较快. 场景 比如文章与标签表 +--+---+--+ | id | artid | tag | +--+---+--+ | 1 | 1 | PHP | | 2 | 1 | Linux | | 3 | 2 | MySQl | | 4 | 2 | Oracle | +--+---

  • 详解mysql中的冗余和重复索引

    mysql允许在相同列上创建多个索引,无论是有意还是无意,mysql需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能. 重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引,应该避免这样创建重复索引,发现以后也应该立即删除.但,在相同的列上创建不同类型的索引来满足不同的查询需求是可以的. CREATE TABLE test( ID INT NOT NULL PRIMARY KEY, A INT NOT NULL, B INT NOT NULL, UNI

  • MySQL中冗余和重复索引的区别说明

    MySQL允许在单个列上创建多个索引,无论是有意还是无意,MySQL需要单独维护这些重复索引,优化器在优化查询时也需要逐个考虑这会影响MySQL的性能 概念阐述 重复索引: 在相同的列上按照相同的顺序创建的相同类型的索引.应该避免创建这样的重复索引,发现之后也应该立即移除. 冗余索引: 两个索引按照相同的顺序覆盖了相同的列. 创建的原因 一般来说,我们有时候会在不经意间创建了重复索引,例如下面的例子: CREATE TABLE test( ID INT NOT NULL PRIMARY KEY,

  • MySQL查询冗余索引和未使用过的索引操作

    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

  • MySQL查询条件中in会用到索引吗

    当用人问你MySQL 查询条件中 in 会不会用到索引,你该怎么回答? 答案:可能会用到索引 动手来测试下 1.创建一张表,给字段port建立索引 CREATE TABLE `pre_request_logs_20180524` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ip` char(16) NOT NULL COMMENT '代理IP', `port` int(8) NOT NULL COMMENT '端口号', `status` enum('成功'

  • MySQL系列之九 mysql查询缓存及索引

    目录 一.MySQL的架构 二.查询缓存(Query Cache) 哪些查询可能不会被缓存: 查询缓存相关的服务器变量: 查询缓存相关的状态变量: 三.索引 1.索引类型: 2.高性能索引策略: 3.索引的优化建议 4.索引的创建与删除 四.EXPLAIN命令 五.SQL语句性能优化 一.MySQL的架构 连接器 连接池,安全认证.线程池.连接限制.检查内存.缓存 SQL接口 DML.DDL SQL解析器,对SQL语句的权限检查.解析为二进制程序 优化器,优化访问路径 缓存cache,buffe

  • MySQL查询性能优化七种方式索引潜水

    目录 前言: 有读者可能会一脸懵? 啥是索引潜水? 你给起的名字的吗?有没有索引蛙泳? 这个名字还真不是我起的,今天要讲的知识点就叫索引潜水(Index dive) . 先要从一件怪事说起: 我先造点数据复现一下问题,创建一张用户表: CREATE TABLE `user` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `name` varchar(100) NOT NULL DEFAULT '' COM

  • MySQL查询性能优化索引下推

    目录 前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围 前言 前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪 今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用 主要作用有两个: 减少回表查询的次数 减少存

  • MySQL性能优化之如何高效正确的使用索引

    实践是检验真理的唯一途径,本篇只是站在索引使用的全局来定位的,你只需要通读全篇并结合具体的例子,或回忆以往使用过的地方,对整体有个全面认识,并理解索引是如何工作的,就可以了.在后续使用索引,或者优化索引时,可以从这些方面出发,进一步来加深对索引正确高效的使用. 一.索引失效 索引失效,是一个老生常谈的话题了.只要提到数据库优化.使用索引,都能一口气说出一大堆索引失效的场景,什么不能用.什么不该用这类的话,在此,我就不再一一罗列啰嗦了. 索引失效,是指表中有字段创建了索引,由于sql语句书写不当导

  • 如何设计高效合理的MySQL查询语句

    MySQL查询语句大家都在用,但是应该如何设计高效合理的MySQL查询语句呢?下面就教您MySQL查询语句的合理设计方法,分享给大家学习学习. 1.合理使用索引 索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率.现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构.索引的使用要恰到好处,其使用原则如下: ●在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引. ●在频繁进行排序或分组(即进行group by或order by操作)的列上

  • MySQL查询语句大全集锦

    1:使用SHOW语句找出在服务器上当前存在什么数据库: mysql> SHOW DATABASES; 2:2.创建一个数据库MYSQLDATA mysql> CREATE DATABASE MYSQLDATA; 3:选择你所创建的数据库 mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!) 4:查看现在的数据库中存在什么表 mysql> SHOW TABLES; 5:创建一个数据库表 mysql> CREATE TAB

  • MySQL查询语句过程和EXPLAIN语句基本概念及其优化

    网站或服务的性能关键点很大程度在于数据库的设计(假设你选择了合适的语言开发框架)以及如何查询数据上. 我们知道MySQL的性能优化方法,一般有建立索引.规避复杂联合查询.设置冗余字段.建立中间表.查询缓存等,也知道用EXPLAIN来查看执行计划. 但对MySQL复杂查询语句执行过程和内部机制,MySQL Optimizer本身所做优化以及查询语句调整对性能所产生的影响及其原因知之甚少. 本文试图对其中的一些关键概念如执行过程.索引使用等做比较深入的探讨,知其然,知其所以然, 这样可以避免在原本通

随机推荐