转载

MySQL 管理工具集 percona-toolkit

apt-get install -y percona-toolkit

# 检查重复索引pt-duplicate-key-checker –host=’sohu.dev.chenpeng.info’ –port=’43307′ –databases=’grape’

# 运行状态监控pt-mysql-summary –host=’sohu.dev.chenpeng.info’ –port=’43307′ –databases=’grape’

# 在线修改表结构

pt-online-schema-change –host=’sohu.dev.chenpeng.info’ –port=’3306′ –alter “ADD COLUMN c1 INT” D=chenpeng_litchi,t=lc_operate_log –execute –print

pt-online-schema-change –host=’sohu.dev.chenpeng.info’ –port=’3306′ –alter “ENGINE=InnoDB” D=chenpeng_litchi,t=lc_operate_log –execute –print

# 格式化explain结果mysql -hsohu.dev.chenpeng.info -P43307 grape -e”explain SELECT a.line_id,b.dnf_source FROM gp_report_advertiser_day a INNER JOIN gp_line b ON a.line_id=b.id WHERE a.data_date=’2016-04-10′ LIMIT 100″ | pt-visual-explain

重复索引结果示例：

# ########################################################################

# grape.gp_mars_report_advertiser_hour

# ########################################################################

# line_id is a left-prefix of line_id_data_time

# Key definitions:

# KEY `line_id` (`line_id`) USING BTREE,

# KEY `line_id_data_time` (`line_id`,`data_time`) USING BTREE

# Column types:

# `line_id` bigint(20) unsigned not null comment ‘??id’

# `data_time` datetime not null comment ‘??’

# To remove this duplicate index, execute:

ALTER TABLE `grape`.`gp_mars_report_advertiser_hour` DROP INDEX `line_id`;

# ########################################################################

# grape.gp_operate_log

# ########################################################################

# related_id is a left-prefix of search3

# Key definitions:

# KEY `related_id` (`related_id`),

# KEY `search3` (`related_id`,`opt_datetime`),

# Column types:

# `related_id` int(10) unsigned not null comment ‘?????id’

# `opt_datetime` datetime not null comment ‘??????’

# To remove this duplicate index, execute:

ALTER TABLE `grape`.`gp_operate_log` DROP INDEX `related_id`;

# ########################################################################

# grape.gp_report_advertiser_hour

# ########################################################################

# idx_line_id is a left-prefix of line_id_data_time

# Key definitions:

# KEY `idx_line_id` (`line_id`),

# KEY `line_id_data_time` (`line_id`,`data_time`),

# Column types:

# `line_id` bigint(20) not null

# `data_time` datetime not null

# To remove this duplicate index, execute:

ALTER TABLE `grape`.`gp_report_advertiser_hour` DROP INDEX `idx_line_id`;

# ########################################################################

# Summary of indexes

# ########################################################################

# Size Duplicate Indexes 48

# Total Duplicate Indexes 3

# Total Indexes 268

工具检测到gp_operate_log有重复的索引，提供以下SQL语句删除重复的索引：

ALTER TABLE `grape`.`gp_operate_log` DROP INDEX `related_id`;

explain简化显示示例：

root@ubuntu:/tmp/d3OnxmMr# mysql -hsohu.dev.chenpeng.info -P43307 -ugrapeuser -pfaf74223tr grape -e”explain SELECT a.line_id,b.dnf_source FROM gp_report_advertiser_day a INNER JOIN gp_line b ON a.line_id=b.id WHERE a.data_date=’2016-04-10′ LIMIT 100″ | pt-visual-explain

JOIN

+- Filter with WHERE

| +- Bookmark lookup

| +- Table

| | table b

| | possible_keys PRIMARY

| +- Unique index lookup

| key b->PRIMARY

| possible_keys PRIMARY

| key_len 4

| ref grape.a.line_id

| rows 1

+- Index lookup

key a->data_date

possible_keys line_id,data_date

key_len 3

ref const

rows 10722

原文 http://chenpeng.info/html/3594

正文到此结束

所属分类：编程技术

本文标签： cat 删除 http db schema tab explain root key Ubuntu Select mysql HTML sql
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二