我日常需要管理的一个大数据库中,索引较多,大约有5000~7000组索引(上线一个大版本时会增加许多索引),索引多时,总需要定期进行一些维护工作,比如清理一些从没用过的索引,又或者一张很小的表,却增加很多组字段结构相差不多的重复索引,这些都是要对索引进行维护的日常工作。
假设表中存在A、B两组索引:
索引A的字段为name,age
索引B的字段为name,age,city
那么即认为索引A为重复索引,本文只讨论如何定位重复索引,并不是说重复索引就一定是"重复"多余的,而我在日常管理索引时,我会结合该被标识"重复"索引的很多信息进行深入分析,比如tcb基准(可参考我另外的博文得到索引的tcb信息)、reorgchk的F4-F8等等,是否该删除或者重组等等工作。
可以参考以下SQL进行查询出来:
WITH t
AS (
SELECT CASE
WHEN a.colnames <> b.colnames AND trim(a.colnames) || '+' LIKE trim (b.colnames) || '+%'
THEN 'YES'
ELSE 'NO'
END duplicate
,a.tabschema
,a.tabname
,a.indname
,a.colnames AS colnames1
,b.colnames AS colnames2
FROM syscat.indexes a
LEFT JOIN syscat.indexes b ON a.tabname = b.tabname
)SELECT
duplicate
,tabschema
,tabname
,indname
,colnames1
,(CASE
WHEN duplicate = 'YES' THEN '存在重复索引[' CONCAT colnames2 CONCAT ']'
ELSE '不存在重复索引'
END ) remark
FROM t
WHERE duplicate = 'YES' and colnames2 <> ''
ORDER BY tabname
WITH ur
PS:以上SQL为我日常调优笔记中摘取,可能有格式问题未能成功执行。
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞2
添加新评论7 条评论
2015-08-05 16:31
SELECT CASE WHEN a.colnames<>b.colnames
AND locate(trim(a.colnames)||'+', trim(b.colnames))=1
THEN 'yes
2015-07-28 10:09
SELECT CASE WHEN a.colnames<>b.colnames
AND locate(trim(a.colnames)||'+', trim(b.colnames))=1
THEN 'yes'
ELSE 'no'
END duplicate,
a.tabschema, a.tabname, a.indname,
a.colnames as colnames1, b.colnames as colnames2
FROM syscat.indexes a
LEFT JOIN syscat.indexes b ON a.tabname=b.tabname
)
SELECT duplicate, tabschema, tabname, indname, colnames1,
(CASE WHEN duplicate='yes' THEN '存在重复索引['||colnames2||']' ELSE '不存在重复索引' END) remark
FROM t
WHERE duplicate='yes'
AND colnames2<>''
ORDER BY tabname
WITH UR
2015-07-28 09:53
and locate(trim(a.colnames), trim(b.colnames)||'+')=1
2015-07-26 21:09
2015-07-22 00:40
2015-07-21 10:37
2015-07-20 09:32