ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 第63期 如何监控MySQL数据库索引的使用情况

第63期 如何监控MySQL数据库索引的使用情况

原创 MySQL 作者：pxbibm 时间：2026-05-22 15:15:54 0 删除 编辑

我们数据库管理员或者开发同事，在编写sql语句时，面对数据量大时，经常做的就是给表添加索引，但是有时候经常不知道添加的索引是不是合理，或者很多时间添加索引没有节制，甚至给表所有的列都添加上索引。我们知道索引添加多了，会影响dml语句执行的效率。

最近我们开发同事给我反馈，说用户在页面操作时，添加一个新信息时，要等很久才能保存上，因此我和开发同事要了表的信息。和系统的操作日志。从日志上看，只是简单的insert 一条记录。大约要等2分钟。

我通过show 命令查看索引情况，一看这个表很多索引，下图只是列出部分。
show index from tblcalltask

CREATE TABLE `tblcalltask` (
  `TaskId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `TaskNo` varchar(30) NOT NULL COMMENT '工单ID',
  `ContNo` varchar(20) DEFAULT NULL COMMENT '',
  `GroupNo` varchar(30) DEFAULT NULL COMMENT '',
  `TaskType` varchar(5) DEFAULT NULL COMMENT '',
  `TaskType2` varchar(5) DEFAULT '' COMMENT '',
  `ContType` varchar(20) DEFAULT NULL COMMENT '',
  `PayCount` int(11) DEFAULT NULL COMMENT '',
  `PayYears` int(11) DEFAULT NULL COMMENT '',
  `ContDate` date DEFAULT NULL COMMENT '',
  `ContYear` varchar(20) DEFAULT NULL COMMENT '',
  `PayDate` date DEFAULT NULL COMMENT '',
  `PayYear` varchar(20) DEFAULT NULL COMMENT '',
  `PayEndDate` date DEFAULT NULL COMMENT '',
  `PayAllocDate` date DEFAULT NULL COMMENT '',
  `AppntNo` varchar(24) DEFAULT '' COMMENT '',
  `AppntName` varchar(120) DEFAULT NULL COMMENT '',
  `AppntSex` char(1) DEFAULT NULL COMMENT '',
  `Mobile` varchar(50) DEFAULT NULL COMMENT '',
  `Mobile2` varchar(50) DEFAULT NULL COMMENT '',
  `AppntAge` int(11) DEFAULT NULL COMMENT '',
  `IDNo` varchar(20) DEFAULT NULL COMMENT '',
  `InsuredName` varchar(250) DEFAULT NULL COMMENT '',
  `AppntIdEnd` varchar(20) DEFAULT NULL COMMENT '',
  `InsuredIdEnd` varchar(20) DEFAULT NULL COMMENT '',
  `InsuredIdNo` varchar(20) DEFAULT NULL COMMENT '',
  `InsuredIdType` varchar(20) DEFAULT NULL COMMENT '',
  `InsuredRelation` varchar(20) DEFAULT NULL COMMENT '',
  `RiskLength` varchar(10) DEFAULT NULL COMMENT '',
  `RenewalFlag` varchar(10) DEFAULT NULL COMMENT '',
  `RiskCode` varchar(20) DEFAULT NULL COMMENT 'D',
  `RiskName` varchar(250) DEFAULT NULL COMMENT '',
  `RiskCode2` varchar(20) DEFAULT NULL COMMENT '',
  `RiskName2` varchar(2000) DEFAULT NULL COMMENT '',
  `Salechnl` varchar(20) DEFAULT NULL COMMENT 'D',
  `Salechn1Name` varchar(250) DEFAULT NULL COMMENT '',
  `Salechn2` varchar(80) DEFAULT NULL COMMENT '',
  `Salechn2Name` varchar(250) DEFAULT NULL COMMENT '',
  `BankChannel` varchar(20) DEFAULT NULL COMMENT '',
  `ManageCom` varchar(10) DEFAULT NULL COMMENT 'ID',
  `ManageComName` varchar(60) DEFAULT NULL COMMENT '',
  `ManageAgentCode` varchar(50) DEFAULT NULL COMMENT '',
  `T2Code` varchar(10) DEFAULT NULL COMMENT '',
  `T2Name` varchar(50) DEFAULT NULL COMMENT '',
  `T3Code` varchar(10) DEFAULT NULL COMMENT '',
  `T3Name` varchar(50) DEFAULT NULL COMMENT '',
  `BankCode` varchar(10) DEFAULT NULL COMMENT '',
  `BankName` varchar(200) DEFAULT NULL COMMENT '',
  `SumDuePayPrem` float(16,2) DEFAULT NULL COMMENT '',
  `LastConvertTime` timestamp NULL DEFAULT NULL COMMENT '',
  `ConvertFlag` varchar(200) DEFAULT NULL COMMENT '',
  `PayStates` varchar(1) DEFAULT NULL COMMENT '',
  `TaskStates` varchar(2) DEFAULT NULL COMMENT '',
  `CallOutOrder` int(11) DEFAULT NULL COMMENT '',
  `LimitationDay` int(11) DEFAULT NULL COMMENT '',
  `AgentID` varchar(20) DEFAULT NULL COMMENT '',
  `DeleteFlag` varchar(1) DEFAULT NULL COMMENT '',
  `CreateUser` varchar(20) DEFAULT NULL COMMENT '',
  `ModifyUser` varchar(20) DEFAULT NULL COMMENT '',
  `CreateDate` timestamp NULL DEFAULT NULL COMMENT '',
  `ModifyDate` timestamp NULL DEFAULT NULL COMMENT '',
  `InsuranceContNo` varchar(20) DEFAULT NULL COMMENT '',
  `GuaranteePeriod` varchar(120) DEFAULT NULL COMMENT '',
  `BankAccount` varchar(40) DEFAULT NULL COMMENT '',
  `SumPayPrem` float(16,2) DEFAULT NULL COMMENT '',
  `ReSumPayPrem` float(16,2) DEFAULT NULL COMMENT '',
  `UniversalPayPrem` float(16,2) DEFAULT NULL COMMENT '',
  `amnt` float(16,2) DEFAULT NULL COMMENT '',
  `Mobile2Relation` varchar(1) DEFAULT NULL COMMENT '',
  `PayIntv` varchar(2) DEFAULT NULL COMMENT '',
  `HmFlag` varchar(1) DEFAULT NULL COMMENT '',
  `ContState` varchar(1) DEFAULT NULL COMMENT '态',
  `MaintenanceState` varchar(20) DEFAULT NULL COMMENT '',
  `PayVerificationDate` date DEFAULT NULL COMMENT '',
  `PayVerificationTime` timestamp NULL DEFAULT NULL COMMENT '',
  `PayVerificationType` varchar(10) DEFAULT NULL COMMENT '',
  `FirstSuccFlag` varchar(1) DEFAULT NULL COMMENT ')',
  `ThirdSuccFlag` varchar(1) DEFAULT NULL COMMENT '',
  `CallOutDate` timestamp NULL DEFAULT NULL COMMENT '',
  `CallOutUser` varchar(50) DEFAULT NULL COMMENT '',
  `BuyDate` date DEFAULT NULL COMMENT '',
  `ContYears` int(11) DEFAULT NULL COMMENT '',
  `PushDate` date DEFAULT NULL COMMENT '间',
  `AgentCode` varchar(20) DEFAULT NULL COMMENT '',
  `AgentName` varchar(240) DEFAULT NULL COMMENT '',
  `AgentPhone` varchar(20) DEFAULT NULL COMMENT '',
  `AgentWorkFlag` varchar(20) DEFAULT NULL COMMENT '',
  `FirstAgentCode` varchar(20) DEFAULT NULL COMMENT '',
  `FirstAgentName` varchar(240) DEFAULT NULL COMMENT '',
  `FirstAgentSalechnl` varchar(20) DEFAULT NULL COMMENT '',
  `ZdFlag` varchar(5) DEFAULT NULL COMMENT '',
  `ContStateDate` date DEFAULT NULL COMMENT '',
  `ContStateCode` varchar(100) DEFAULT NULL COMMENT '',
  `ContStateDateHistory` varchar(100) DEFAULT NULL COMMENT '',
  `ContStateCodeHistory` varchar(100) DEFAULT NULL COMMENT '',
  `paytype` varchar(200) DEFAULT NULL COMMENT '',
  `AppntAddress` varchar(200) DEFAULT NULL COMMENT '',
  `handTypeFlag` varchar(1) DEFAULT '0' COMMENT '',
  `RelevanceCount` varchar(20) DEFAULT '0' COMMENT '',
  `AiCount` varchar(20) DEFAULT '0' COMMENT '',
  `AiFlag` varchar(1) DEFAULT '0' COMMENT '',
  `AiResult` varchar(200) DEFAULT NULL COMMENT '',
  `AllocFlag` varchar(1) DEFAULT '0' COMMENT '',
  `PreAgentId` varchar(200) DEFAULT NULL COMMENT '',
  `PreActionTime` timestamp NULL DEFAULT NULL COMMENT '',
  `PreUserName` varchar(200) DEFAULT NULL COMMENT '',
  `RealAgentId` varchar(200) DEFAULT NULL COMMENT '',
  `RealActionTime` timestamp NULL DEFAULT NULL COMMENT '',
  `RealUserName` varchar(200) DEFAULT NULL COMMENT '',
  `WorkPlanDate` date DEFAULT NULL COMMENT '',
  `WorkPlanTime` timestamp NULL DEFAULT NULL COMMENT '',
  `WorkPlanName` varchar(200) DEFAULT NULL COMMENT '',
  `AiDisplay` varchar(1) DEFAULT '1' COMMENT '',
  `AiSendFlag` varchar(1) DEFAULT '0' COMMENT '',
  `ReAppType` varchar(50) DEFAULT NULL COMMENT '',
  `ReOperator` varchar(50) DEFAULT NULL COMMENT '',
  `ReSuccFlag` varchar(1) DEFAULT '0' COMMENT '',
  `NewFlag` varchar(1) DEFAULT '0' COMMENT '',
  `LastFlag` varchar(1) DEFAULT '0' COMMENT '',
  `ConfirmRightFlag` varchar(1) DEFAULT '0' COMMENT '',
  `ExpireFlag` varchar(20) DEFAULT '0' COMMENT '',
  `SearchFlag` varchar(1) DEFAULT '0' COMMENT '',
  `WorkFlag` varchar(1) DEFAULT '0' COMMENT '',
  `WorkDate` date DEFAULT NULL COMMENT '',
  `WorkYear` varchar(20) DEFAULT NULL COMMENT '',
  `WorkCount` varchar(20) DEFAULT NULL COMMENT '',
  `CallCount` varchar(20) DEFAULT NULL COMMENT '',
  `SpeakCount` varchar(20) DEFAULT NULL COMMENT '',
  `BindAgentFlag` varchar(1) DEFAULT '0' COMMENT '',
  `BindAgentResult` varchar(500) DEFAULT NULL COMMENT '',
  `BindAgentTime` timestamp NULL DEFAULT NULL COMMENT '',
  `NowAgentId` varchar(200) DEFAULT NULL COMMENT '',
  `NowActionTime` timestamp NULL DEFAULT NULL COMMENT '',
  `NowUserName` varchar(200) DEFAULT NULL COMMENT '',
  `NowActionType` varchar(20) DEFAULT NULL COMMENT '',
  `NowHandFlag` varchar(1) DEFAULT NULL COMMENT '',
  `OldAgentId` varchar(200) DEFAULT NULL COMMENT '',
  `OldActionTime` timestamp NULL DEFAULT NULL COMMENT '',
  `OldUserName` varchar(200) DEFAULT NULL COMMENT '',
  `OldActionType` varchar(20) DEFAULT NULL COMMENT '',
  `OldHandFlag` varchar(1) DEFAULT NULL COMMENT '',
  `HandAgentFlag` varchar(1) DEFAULT '0' COMMENT '',
  `ActionComment` varchar(200) DEFAULT NULL COMMENT '',
  `FailureAudit` varchar(1) DEFAULT NULL COMMENT '',
  `UniversalAccountFlag` varchar(20) DEFAULT NULL COMMENT '',
  `UniversalSurrenderFlag` varchar(20) DEFAULT NULL COMMENT '',
  `FirstSaleBank` varchar(100) DEFAULT NULL COMMENT '',
  `FirstSaleBankBranch` varchar(100) DEFAULT NULL COMMENT '',
  `MaintenanceNo` varchar(50) DEFAULT NULL COMMENT '',
  `PayLocation` varchar(20) DEFAULT NULL COMMENT '',
  `WorkContFlag` varchar(1) DEFAULT '0' COMMENT '',
  `GoodCustomerFlag` varchar(1) DEFAULT '0' COMMENT '',
  `FortyFivePayedFlag` varchar(1) DEFAULT '0' COMMENT '',
  `BackPlatformFlag` varchar(1) DEFAULT '0' COMMENT '',
  `ScopeFlag` varchar(20) DEFAULT NULL COMMENT '',
  `ReCallFlag` varchar(20) DEFAULT NULL COMMENT '',
  `EstimateStates` varchar(100) DEFAULT NULL COMMENT '',
  `NearInvalidEstimateStates` varchar(100) DEFAULT NULL COMMENT '',
  `CallbackDate` varchar(50) DEFAULT NULL COMMENT '',
  `CallContext` varchar(1000) DEFAULT NULL COMMENT '',
  `CallDate` varchar(50) DEFAULT NULL COMMENT '',
  `CalendarLevel` varchar(2) DEFAULT '99' COMMENT '',
  `CalendarFlag` varchar(1) DEFAULT '1' COMMENT '',
  `ContTypeFlag` varchar(1) DEFAULT '0' COMMENT '',
  `TodayAllocFlag` varchar(1) DEFAULT '0' COMMENT '',
  `ResponseFlag` varchar(1) DEFAULT '0' COMMENT '',
  PRIMARY KEY (`TaskId`) USING BTREE,
  KEY `idx_tblcalltask_ContNo` (`ContNo`) USING BTREE COMMENT '',
  KEY `idx_tblcalltask_AgentID` (`AgentID`) USING BTREE,
  KEY `idx_tblcalltask_ConverFlag` (`ConvertFlag`) USING BTREE,
  KEY `for_full_list` (`PayVerificationDate`,`Salechnl`,`ContState`,`PayDate`,`ContYears`,`RiskLength`) USING BTREE,
  KEY `tblcont_appnt` (`AppntNo`,`ContNo`,`NowAgentId`) USING BTREE,
  KEY `cont_task_no` (`TaskNo`) USING BTREE,
  KEY `cont_appnt_no` (`AppntNo`) USING BTREE,
  KEY `cont_appnt_name` (`AppntName`) USING BTREE,
  KEY `cont_pay_date` (`PayDate`) USING BTREE,
  KEY `cont_agent_id` (`AgentID`) USING BTREE,
  KEY `cont_mobile2` (`Mobile2`) USING BTREE,
  KEY `cont_now_agent_id` (`NowAgentId`) USING BTREE,
  KEY `cont_salechnl` (`Salechnl`) USING BTREE,
  KEY `cont_taskStates` (`TaskStates`) USING BTREE,
  KEY `cont_lastFlag` (`LastFlag`) USING BTREE,
  KEY `cont_workFlag` (`WorkFlag`) USING BTREE,
  KEY `cont_workDate` (`WorkDate`) USING BTREE,
  KEY `for_scope_list` (`ScopeFlag`) USING BTREE,
  KEY `for_calendar_list` (`CalendarFlag`) USING BTREE,
  KEY `for_callout_date` (`CallOutDate`) USING BTREE,
  KEY `for_conttype_flag` (`ContTypeFlag`) USING BTREE,
  KEY `for_todayalloc_flag` (`TodayAllocFlag`) USING BTREE,
  KEY `for_response_flag` (`ResponseFlag`) USING BTREE,
  KEY `for_callout_date1` (`CallOutDate`) USING BTREE,
  KEY `for_Callback_date` (`CallbackDate`) USING BTREE,
  KEY `idx_work_year` (`WorkYear`) USING BTREE,
  KEY `cont_ai_flag` (`AiFlag`) USING BTREE,
  KEY `cont_no` (`ContNo`) USING BTREE,
  KEY `mobile` (`Mobile`) USING BTREE,
  KEY `sum_pay_prem` (`SumPayPrem`) USING BTREE,
  KEY `cont_no_pay_count` (`ContNo`,`PayCount`) USING BTREE,
  KEY `for_manage_com` (`ManageCom`) USING BTREE,
  KEY `for_salechn_two` (`Salechn2`) USING BTREE,
  KEY `for_t2_code` (`T2Code`) USING BTREE,
  KEY `for_t3_code` (`T3Code`) USING BTREE,
  KEY `for_first_agent_code` (`FirstAgentCode`) USING BTREE,
  KEY `for_pay_count` (`PayCount`) USING BTREE,
  KEY `for_expire_flag` (`ExpireFlag`) USING BTREE,
  KEY `for_confirm_right_flag` (`ConfirmRightFlag`) USING BTREE,
  KEY `idx_appnt_payintv_paydate_nowagent` (`AppntNo`,`PayCount`,`NowAgentId`,`TaskNo`,`PayIntv`,`PayDate`) USING BTREE,
  KEY `for_cont_year` (`ContYear`) USING BTREE,
  KEY `for_pay_year` (`PayYear`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8714265 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='test'

很明显，问题找到了，主要就是表上的索引太多了，表数据量比较大。

那么下面我就要解决这个问题，我们第一个想到就是删除索引，我们都知道，索引有无，是不影响业务的，但是它会影响执行的效率，删除了索引，有可能select查询就会变得非常慢。

那么我们就要知道哪些索引使用了，哪些索引没使用，我们只能删除哪些没有使用的索引，因此我们就要监控索引的使用情况。

监控索引的使用情况，首先要开启监控信息。

1.添加mysql配置文件，添加performance_schema参数，设置为on，开启性能监控，修改参数，需要重启数据库。
编辑 my.cnf 或 my.ini
[mysqld]
performance_schema = ON
重启 MySQL 生效。

MySQL 5.7 默认 没开启索引监控，执行下面开启：

-- 开启所有等待、阶段、语句监控
UPDATE performance_schema.setup_instruments SET ENABLED='YES',TIMED='YES' WHERE NAME LIKE 'wait/%';
UPDATE performance_schema.setup_instruments SET ENABLED='YES',TIMED='YES' WHERE NAME LIKE 'stage/%';
UPDATE performance_schema.setup_instruments SET ENABLED='YES',TIMED='YES' WHERE NAME LIKE 'statement/%';
-- 开启消费者写入
UPDATE performance_schema.setup_consumers SET ENABLED='YES';

我们可以通过查看所有索引使用情况，来判断索引是否被使用了。

判断规则
  • use_count = 0：索引 从未被使用
  • use_count > 0：索引已使用
SELECT 
  t.table_schema AS db_name,
  t.table_name,
  t.index_name,
  IFNULL(s.count_star, 0) AS use_count,--使用的次数
  t.column_name
FROM information_schema.statistics t
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage s
  ON t.table_schema = s.object_schema
  AND t.table_name = s.object_name
  AND t.index_name = s.index_name
WHERE t.table_schema NOT IN ('sys','mysql','information_schema','performance_schema') 
ORDER BY use_count ASC;
==============================================
SELECT
	object_schema AS 库名,
	object_name AS 表名,
	index_name AS 索引名,
	count_star AS 索引使用次数 
FROM
	PERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage 
WHERE
	object_schema NOT IN ( 'mysql', 'sys', 'information_schema', 'performance_schema' ) 
ORDER BY
	count_star ASC;

查看从未使用过的索引（最有价值）

SELECT
	object_schema AS 库名,
	object_name AS 表名,
	index_name AS 索引名 
FROM
	PERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage 
WHERE
	index_name != 'PRIMARY' -- 排除主键
	AND count_star = 0 -- 0 = 从未使用
	AND object_schema NOT IN ( 'mysql', 'sys', 'information_schema' );

这样就可以把没有使用过的索引删除掉，但是，一定要多监控一段时间。


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12798004/viewspace-3171917/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 第62期 Oracle通过DBA_HIST_SQLSTAT 追踪SQL历史性能问题
下一篇： 第64期 ORA-00600: internal error code, arguments: [qosdExpStatRead
请登录后发表评论 登录
全部评论
pxbibm
专家姓名：彭小波
Oracle ACE。ACOUG核心成员，Oracle用户组年轻专家。熟悉Oracle的体系结构，擅长Oracle数据库架构规划、SQL优化、OWI方面性能的优化。曾服务于航天、航空、机车、大型制造行业数据库的维护以及系统开发工作,开发过基于Oracle数据库的大型ERP系统。
  • 博文量
    345
  • 访问量
    2477890

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593