当前位置:首页 > mysql > 正文内容

mysql中找出无用的索引或重复索引

phpmianshi4年前 (2017-05-23)mysql319

背景

MySQL允许在相同列上创建重复的索引,但这样做对数据库却是有害而无利的,需要定期检查此类重复索引以改善数据库性能。

可减少磁盘空间占用、减少磁盘IO、减少优化器优化查询时需要比较的索引个数、减少数据库维护冗余索引的各类开销、提高数据库性能(插入、更新、删除)


重复索引检测 

pt-duplicate-key-checker:通过SHOW CREATE TABLE输出的表定义检测MySQL表中重复或者冗余的索引或外键

可以检测到的冗余/重复索引类型:若某个索引和另外某个索引以同样的顺序包含同样的列,或者该索引包含的列是另外某个索引的最左前缀列,则被认为是重复/冗余的索引。默认情况下只在同类型的索引间(如BTREE索引)进行比较,不同类型的索引即使符合上述描述也不会被认为是重复/冗余,但这一行为可以通过参数改变。除此之外,还可检测重复的外键,即引用的表和列均相同的外键。对于聚簇索引的表,在辅助索引后添加主键列的索引也被认为是冗余的,因为这种情况下,辅助索引末尾本身就包含有主键信息。

基本用法以及样例输出如下

 pt-duplicate-key-checker A=utf8, F=/etc/my.cnf, h=localhost, u=root, P=3306 –ask-pass


样例输出:

# ########################################################################
# dcf.privilege                                                          
# ########################################################################

# Uniqueness of UQI_IDX_1 ignored because PRIMARY is a duplicate constraint
# UQI_IDX_1 is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `UQI_IDX_1` (`privilege_id`),
#   PRIMARY KEY (`privilege_id`),
# Column types:
#     `privilege_id` varchar(50) collate utf8_bin not null comment '权限id'
# To remove this duplicate index, execute:
ALTER TABLE `dcf`.`privilege` DROP INDEX `UQI_IDX_1`;

# ########################################################################
# dcf.t_game_config                                                      
# ########################################################################

# Uniqueness of pkey ignored because PRIMARY is a duplicate constraint
# pkey is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `pkey` (`pkey`)
#   PRIMARY KEY (`pkey`),
# Column types:
#     `pkey` bigint(20) not null auto_increment
# To remove this duplicate index, execute:
ALTER TABLE `dcf`.`t_game_config` DROP INDEX `pkey`;

# ########################################################################
# dcf.t_project_institution                                              
# ########################################################################

# index_1 is a left-prefix of index_2
# Key definitions:
#   KEY `index_1` (`project_id`),
#   KEY `index_2` (`project_id`,`institution_id`,`delete_flag`)
# Column types:
#     `project_id` bigint(20) not null comment '项目id'
#     `institution_id` varchar(20) not null comment '机构id'
#     `delete_flag` tinyint(4) not null
# To remove this duplicate index, execute:
ALTER TABLE `dcf`.`t_project_institution` DROP INDEX `index_1`;

# ########################################################################
# dcf_commons.bank_cnaps                                                  
# ########################################################################

# idx is a duplicate of PRIMARY
# Key definitions:
#   KEY `idx` (`cnaps`)
#   PRIMARY KEY (`cnaps`),
# Column types:
#     `cnaps` varchar(255) not null comment '电子联行号'
# To remove this duplicate index, execute:
ALTER TABLE `dcf_commons`.`bank_cnaps` DROP INDEX `idx`;

# ########################################################################
# dcf_contract.customer_bank_account                                      
# ########################################################################

# IDX_CUSTOMER_ID is a left-prefix of UQI_IDX_1
# Key definitions:
#   KEY `IDX_CUSTOMER_ID` (`customer_id`)
#   UNIQUE KEY `UQI_IDX_1` (`customer_id`,`account_no`,`branch_bank`,`account_type`,`account_name`) USING BTREE,
# Column types:
#     `customer_id` varchar(20) collate utf8_bin not null comment '客户id'
#     `account_no` varchar(40) collate utf8_bin default null comment '银行账号'
#     `branch_bank` varchar(100) collate utf8_bin default null comment '开户支行'
#     `account_type` tinyint(4) default null comment '账户类型:比如收款账户,还款账户等\n0-收款账户\n1-还款账户'
#     `account_name` varchar(100) collate utf8_bin default null comment '银行账户户名'
# To remove this duplicate index, execute:
ALTER TABLE `dcf_contract`.`customer_bank_account` DROP INDEX `IDX_CUSTOMER_ID`;

# ########################################################################
# dcf_contract.t_contract_account                                        
# ########################################################################

# IDX_CONTRACT_ID is a left-prefix of t_contract_account_uq1
# Key definitions:
#   KEY `IDX_CONTRACT_ID` (`contract_id`)
#   UNIQUE KEY `t_contract_account_uq1` (`contract_id`,`account_type`),
# Column types:
#     `contract_id` bigint(20) not null comment '合同id'
#     `account_type` tinyint(4) not null comment '账户类 型:globalconstant.bankaccounttypec常数 \n0-收款账户\n1-还款账户 等'
# To remove this duplicate index, execute:
ALTER TABLE `dcf_contract`.`t_contract_account` DROP INDEX `IDX_CONTRACT_ID`;

......
......

# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Size Duplicate Indexes   173317386
# Total Duplicate Indexes  18
# Total Indexes            562

会给出重复/冗余类型、索引/外键定义、索引包含的列类型、移除重复/冗余索引/外键的SQL、最后会给出有关索引的统计信息。


分析没用的索引

SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME,
    s.SEQ_IN_INDEX,
    (
        SELECT
            MAX(SEQ_IN_INDEX)
        FROM
            INFORMATION_SCHEMA.STATISTICS s2
        WHERE
            s.TABLE_SCHEMA = s2.TABLE_SCHEMA
        AND s.TABLE_NAME = s2.TABLE_NAME
        AND s.INDEX_NAME = s2.INDEX_NAME
    ) AS `COLS_IN_INDEX`,
    s.CARDINALITY AS "CARD",
    t.TABLE_ROWS AS "ROMS",
    ROUND(
        (
            (
                s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)
            ) * 100
        ),
        2
    ) AS `SEL %`
FROM
    INFORMATION_SCHEMA.STATISTICS s
INNER JOIN INFORMATION_SCHEMA. TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
AND s.TABLE_NAME = t.TABLE_NAME
WHERE
    t.TABLE_SCHEMA != 'mysql'
AND t.TABLE_ROWS > 10
AND s.CARDINALITY IS NOT NULL
AND (
    s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)
) < 1.00
ORDER BY
    `SEL %`,
    TABLE_SCHEMA,
    TABLE_NAME
LIMIT 10;


SELECT DISTINCT
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME
FROM
    information_schema.statistics `s`
LEFT JOIN information_schema.index_statistics INDXS ON (
    s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA
    AND s.TABLE_NAME = INDXS.TABLE_NAME
    AND s.INDEX_NAME = INDXS.INDEX_NAME
)
WHERE
    INDXS.TABLE_SCHEMA IS NULL;



SELECT
    concat(
        'alter table ',
        d.table_schema,
        '.',
        d.table_name,
        ' drop index ',
        group_concat(
            index_name SEPARATOR ',drop index '
        ),
        ';'
    ) stmt
FROM
    (
        SELECT DISTINCT
            s.TABLE_SCHEMA,
            s.TABLE_NAME,
            s.INDEX_NAME
        FROM
            information_schema.statistics s
        LEFT JOIN information_schema.index_statistics iz ON (
            s.TABLE_SCHEMA = iz.TABLE_SCHEMA
            AND s.TABLE_NAME = iz.TABLE_NAME
            AND s.INDEX_NAME = iz.INDEX_NAME
        )
        WHERE
            iz.TABLE_SCHEMA IS NULL
        AND s.NON_UNIQUE = 1
        AND s.INDEX_NAME != 'PRIMARY'
        AND (
            SELECT
                rows_read + rows_changed
            FROM
                information_schema.table_statistics ts
            WHERE
                ts.table_schema = s.table_schema
            AND ts.table_name = s.table_name
        ) > 0
    ) d
GROUP BY
    table_schema,
    table_name;
    
    
SELECT DISTINCT
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME
FROM
    information_schema.statistics `s`
LEFT JOIN information_schema.index_statistics IST ON CONCAT_WS(
    '.',
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME
) = IST.INDEX_NAME
WHERE
    IST.INDEX_NAME IS NULL;



版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

mysql中AnalyzeTable优化

Analyze TableMySQL的Optimizer(优化元件)在优化SQL语句时,首先需要收集一些相关信息,其中就包括表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列...

mysql中explain分析sql详解

Explain举例mysql> explain select * from event;  +—-+————-+——-+——+————...

mysql中QueryCache优化

原理 MySQL 的 Query Cache 实现原理实际上并不是特别的复杂,简单的来说就是将客户端请求的 Query 语句(当然仅限于 SELECT 类型的 Query)通过一定的 has...

mysql中index_merge索引合并优化

前言深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术,我们才知道应该如何在表上建立索引。MySQL在分析执行计划时发现走单个索引的过滤效果都不是...

mysql中超大表的删除方法

在mysql中遇到一个大表,大概有17G左右,删除这张表。通常的删除操作可以通过delete、drop、truncate操作,但是有可能导致mysql hang住,必须使用些特殊的方法。1、建立硬链接...

mysql中CPU或内存利用率过高问题

CPU 利用率过高原因在 MySQL 使用过程中,出现 CPU 利用率过高甚至超过100%时,与数据库存在低效 SQL 或大量行锁冲突有非常大的关系,一般都是由于大量低效的 SQL 导致,出现行锁冲突...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。