MYSQL管理之索引改造简析_MySQL
程序员文章站
2022-05-15 16:49:13
...
bitsCN.com
作为MYSQL DBA需要定期的查看服务器的索引情况，尤其是当你到了一个新的环境，开始接手一些数据库的维护工作，需要对线上服务器的索引使用情况有所了解。如果索引设置不合理，会导致服务器的性能受到非常大的影响，尤其是当SQL语句又比较复杂（比如多表联合查询等），本来就来大致介绍一下线上数据库的索引改造，本文只是对个人的工作总结，如果大家有更好的索引改造方案，也请指点。
这里提到的索引改造主要分成如下几个阶段：
一、去除重复的索引 1. 为什么要去除重复的索引A. 多余的索引占用磁盘空间，会引起不必要的磁盘ioB. 多余的索引会导致数据库在进行索引选择的时候变慢，尤其是索引越多的时候越突出（主要是相关联的索引才会影响索引选择）C. 重复的索引会导致表的更新变慢
2. 如何找出重复的索引这里给大家介绍一个好用的Maatkit工具，Maatkit工具安装完成以后就会有一个mk-duplicate-key-checker命令，这个命令就是检测数据库中存在的重复索引，并会自动生成删除重复索引的语句，非常方便。
下面介绍Maatkit工具的安装和mk-duplicate-key-checker命令的使用。
Maatkit工具的安装：wget http://maatkit.googlecode.com/files/maatkit-7540.tar.gztar zxvf maatkit-7540.tar.gz cd maatkit-7540 perl Makefile.PL make installmk-duplicate-key-checker命令的使用：mk-duplicate-key-checker --databases=databasename --user=root --password=passwd这里只需要制定数据库名，用户名以及密码，如果还想知道其他的一些参数，可以使用命令mk-duplicate-key-checker –help查看。备注：提醒大家一下，在执行完删除重复索引的语句之后，还需要再用这个工具重新检测一下，因为删除重复之后还可能会出现新的重复索引，尤其是PHPCMS V9的数据库。
二、去除不必要的索引何为不必要的索引：我的理解是有一些字段辨识度很低的，比如abolish字段只有0和1，就没有必要建立索引，因为使用索引和全表扫描的速度差不多甚至肯能使用索引扫描会更慢。如果实在要建立，就根据查询情况和其他的字段建立组合索引效果会更好。
1. 为什么要去除不必要的索引这个理由和去除重复的索引差不多，这里就不详述了。
2.如何找出不必要的索引可以通过information_schema的STATISTICS表找出类似的索引，然后再经过人工过滤，应该还有更好的办法，呵呵。比如如下查询，能查询出制定库的辨识度低的列索引：SELECT TABLE_NAME,INDEX_NAME,COLUMN_NAME,CARDINALITY FROM `information_schema`.`STATISTICS` WHERE TABLE_SCHEMA='databasename' AND CARDINALITY 三、添加必须的索引何为必须的索引：我的理解比较简单就是会影响到查询性能的索引就是必须的索引
1. 为什么要添加必须的索引必须的索引会影响到数据库的查询性能，很简单又很充分的理由，呵呵！
2. 如何找出必须的索引 关于这个问题，我认为是比较有技术含量的，我的步骤如下：A. 找出性能差或者没有使用到索引的SQL语句要找出性能差或者没有使用到索引的SQL语句，需要做一些设置，比如将long_query_time设置成0.2秒左右，这个根据自己的环境来定，打开log_queries_not_using_indexes参数，可以通过set global log_queries_not_using_indexes=on;命令打开。可以通过mysqldumpslow命令来对慢查询日志进行统计，比如按照执行时间长短来提取或者按照执行次数来提取或者查询记录数来提取。列举两个非常常用的组合：mysqldumpslow -s t -t 10 slow.log #找出10条消耗时间最长的慢查询SQLmysqldumpslow -s c -t 10 slow.log #找出10条执行次数最多的满查询SQB. 通过explain和profiling分析性能差和没有用到索引的sql，确定需要添加的索引（也可能需要改写对应的SQL，这个不在本文的讨论范围），关于explain和profiling的用法读者可以自己查看相关的文档，这里不赘述。 作者 飞鸿无痕 bitsCN.com
作为MYSQL DBA需要定期的查看服务器的索引情况，尤其是当你到了一个新的环境，开始接手一些数据库的维护工作，需要对线上服务器的索引使用情况有所了解。如果索引设置不合理，会导致服务器的性能受到非常大的影响，尤其是当SQL语句又比较复杂（比如多表联合查询等），本来就来大致介绍一下线上数据库的索引改造，本文只是对个人的工作总结，如果大家有更好的索引改造方案，也请指点。
这里提到的索引改造主要分成如下几个阶段：
一、去除重复的索引 1. 为什么要去除重复的索引A. 多余的索引占用磁盘空间，会引起不必要的磁盘ioB. 多余的索引会导致数据库在进行索引选择的时候变慢，尤其是索引越多的时候越突出（主要是相关联的索引才会影响索引选择）C. 重复的索引会导致表的更新变慢
2. 如何找出重复的索引这里给大家介绍一个好用的Maatkit工具，Maatkit工具安装完成以后就会有一个mk-duplicate-key-checker命令，这个命令就是检测数据库中存在的重复索引，并会自动生成删除重复索引的语句，非常方便。
下面介绍Maatkit工具的安装和mk-duplicate-key-checker命令的使用。
Maatkit工具的安装：wget http://maatkit.googlecode.com/files/maatkit-7540.tar.gztar zxvf maatkit-7540.tar.gz cd maatkit-7540 perl Makefile.PL make installmk-duplicate-key-checker命令的使用：mk-duplicate-key-checker --databases=databasename --user=root --password=passwd这里只需要制定数据库名，用户名以及密码，如果还想知道其他的一些参数，可以使用命令mk-duplicate-key-checker –help查看。备注：提醒大家一下，在执行完删除重复索引的语句之后，还需要再用这个工具重新检测一下，因为删除重复之后还可能会出现新的重复索引，尤其是PHPCMS V9的数据库。
二、去除不必要的索引何为不必要的索引：我的理解是有一些字段辨识度很低的，比如abolish字段只有0和1，就没有必要建立索引，因为使用索引和全表扫描的速度差不多甚至肯能使用索引扫描会更慢。如果实在要建立，就根据查询情况和其他的字段建立组合索引效果会更好。
1. 为什么要去除不必要的索引这个理由和去除重复的索引差不多，这里就不详述了。
2.如何找出不必要的索引可以通过information_schema的STATISTICS表找出类似的索引，然后再经过人工过滤，应该还有更好的办法，呵呵。比如如下查询，能查询出制定库的辨识度低的列索引：SELECT TABLE_NAME,INDEX_NAME,COLUMN_NAME,CARDINALITY FROM `information_schema`.`STATISTICS` WHERE TABLE_SCHEMA='databasename' AND CARDINALITY 三、添加必须的索引何为必须的索引：我的理解比较简单就是会影响到查询性能的索引就是必须的索引
1. 为什么要添加必须的索引必须的索引会影响到数据库的查询性能，很简单又很充分的理由，呵呵！
2. 如何找出必须的索引 关于这个问题，我认为是比较有技术含量的，我的步骤如下：A. 找出性能差或者没有使用到索引的SQL语句要找出性能差或者没有使用到索引的SQL语句，需要做一些设置，比如将long_query_time设置成0.2秒左右，这个根据自己的环境来定，打开log_queries_not_using_indexes参数，可以通过set global log_queries_not_using_indexes=on;命令打开。可以通过mysqldumpslow命令来对慢查询日志进行统计，比如按照执行时间长短来提取或者按照执行次数来提取或者查询记录数来提取。列举两个非常常用的组合：mysqldumpslow -s t -t 10 slow.log #找出10条消耗时间最长的慢查询SQLmysqldumpslow -s c -t 10 slow.log #找出10条执行次数最多的满查询SQB. 通过explain和profiling分析性能差和没有用到索引的sql，确定需要添加的索引（也可能需要改写对应的SQL，这个不在本文的讨论范围），关于explain和profiling的用法读者可以自己查看相关的文档，这里不赘述。 作者 飞鸿无痕 bitsCN.com