mysql索引维护

重复冗余的索引
主键和unique作用于同一个字段
多个索引的前缀列相同，或是在联合索引中包含了主键的索引

create table test(
id int not null primary key,
name varchar(10) not null,
title varchar(50) not null,
key(name,id)
)engine=innodb;
key(name,id) 就是一个冗余索引

查找重复及冗余索引

select a.table_schema AS '数据名',
 a.table_name as '表名',
 a.index_name as "索引1",
 b.index_name as '索引2',
 a.column_name as '重复列名'
from STATISTICS a JOIN STATISTICS b on
a.table_schema=b.table_schema 
and 
a.table_name=b.table_name
and 
a.column_name=b.column_name 
where a.seq_in_index=1
and a.index_name <> b.index_name

pt-duplicate-key-checker 工具检测
pt-duplicate-key-checker -uroot -p'' -h127.0.0.1

缺少工具到
https://www.percona.com/downloads/percona-toolkit/LATEST/
下载解压后
perl Makefile.PL；make；make install

得出类似
# Uniqueness of shop_id ignored because PRIMARY is a duplicate constraint
# shop_id is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `shop_id` (`shop_id`) USING BTREE,
#   PRIMARY KEY (`shop_id`),
# Column types:
#      `shop_id` int(11) not null default '0' comment '??id'
# To remove this duplicate index, execute:
ALTER TABLE `lepu`.`shop_turn` DROP INDEX `shop_id`;

# ########################################################################
# lepu.user_ext                                                           
# ########################################################################

# u_idx is a duplicate of user_id
# Key definitions:
#   KEY `u_idx` (`user_id`)
#   UNIQUE KEY `user_id` (`user_id`) USING BTREE,
# Column types:
#      `user_id` int(11) default null comment 'weixin_user????id'
# To remove this duplicate index, execute:
ALTER TABLE `lepu`.`user_ext` DROP INDEX `u_idx`

删除不用的索引
目前mysql中还没有记录索引的使用情况,在PerconMySQL和MariaDB中可通过index_statistics表来查看哪些索引未使用,但在mysql中只能通过慢查询日志配合
注意主从的日志
pt-index-usage工具进行索引使用情况的分析
pt-index-usage -uroot -p'' mysql-slow.log

日期时间用Int
ip地址 bigint
inet_aton(),inet_ntoa进行转换