mysql复合索引会包含哪些索引(MySQL查询冗余索引和未使用过的索引操作)
mysql复合索引会包含哪些索引
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的表中,方便查询结果。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持开心学习网。如有错误或未考虑完全的地方,望不吝赐教。
原文链接:https://blog.csdn.net/u014609263/article/details/113540365
- mysqlworkbench怎么设置连接(详解MySQL Workbench使用教程)
- mysql索引基本知识(MySql索引使用策略分析)
- windows7mysql服务无法启动(Windows系统下MySQL无法启动的万能解决方法)
- 对mysql索引的理解(详解MySQL 8.0 之不可见索引)
- mysql sql优化方法(MySQL SQL优化教程之in和range查询)
- windows下mysql密码重置(Windows10系统下Mysql8.0.13忘记root密码的操作方法)
- 怎么知道sqlyog连接的哪个mysql(SQLyog连接MySQL8.0报2058错误的完美解决方法)
- mysqldump 命令详解(mysqldump你可能不知道的参数)
- mysql判断表中字段是否存在(mysql插入前判断数据是否存在的操作)
- mysql8.0详解(MySQL 8.0 的 5 个新特性,太实用了!)
- mysql缓存是什么(详解mysql查询缓存简单使用)
- mysql数据库间的连接(MySQL中表的几种连接方式)
- mysql存储引擎是什么(详解mysql中的存储引擎)
- mysql时间戳和datetime对比(mysql中 datatime与timestamp的区别说明)
- innodb和myisam(Mysql InnoDB和MyISAM区别原理解析)
- mysql多核cpu利用(mysql CPU高负载问题排查)
- 观花盆栽佛肚竹盆景制作及养护(观花盆栽佛肚竹盆景制作及养护)
- 春天养佛肚竹,做好这几件事,叶绿根壮寓意好 越养越旺家(春天养佛肚竹做好这几件事)
- 律界衣品最好,时尚圈学识数高,41岁的Amal堪称现实版的傲骨贤妻(时尚圈学识数高)
- 刘智泰确认出演《Good Wife》 担纲男一号(刘智泰确认出演Good)
- 《傲骨之战》剧终 律政女魔头Diane,为什么是大女主天花板(傲骨之战剧终律政女魔头Diane)
- 汉族(汉族的祖先是谁)
热门推荐
- ASP.NET中获取匿名对象的属性值
- vuekeep-alive源码(vue中keepAlive组件的作用和使用方法详解)
- 怎么用apache自定义网站根目录(Apache 二级域名实现方法介绍)
- SQL中的with as的使用
- python 配置文件环境变量(python环境路径配置以及命令行运行脚本)
- 云锁服务器怎么设置安全(SugarHosts云服务器如何开启访问端口和使用安全组)
- Sql Server 更新锁(UPDLOCK)
- python爬取微博登录数据(Python实现爬取马云的微博功能示例)
- 虚拟主机已被禁用(虚拟主机提示网站被暂停了是怎么回事?)
- 织梦网站栏目管理和内容发布(使用DEDE织梦计划任务功能定时更新首页)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9