mysql重建索引

点击查看代码

# -*- coding: utf-8 -*-

"""

测试数据库
create database test charset=utf8;
use test;

测试数据表
drop table tbl_name;
create table tbl_name(
  id int auto_increment comment "主键",
  username varchar(20) not null comment "账号",
  sex tinyint default 0 comment "性别",
  age tinyint default 1 comment "年龄",
  nickname varchar(20) not null default "游客" comment "昵称",
  PRIMARY KEY `id_000` (`id`),
  UNIQUE KEY `username_100` (`username`),
  index `nickname_2000` (`nickname`),
  UNIQUE KEY index_name_sex (`username`, `sex` ),
  index `index_sex_age` (`sex`,`age`)
);

# 添加测试数据
insert into tbl_name (username,sex,age,nickname) 
values ("xiaoming",1,16,"小明"),("xiaobai",0,16,"小白"),("xiaohui",1,16,"小辉");

# 查看索引
SHOW INDEX FROM tbl_name;

# 基本使用:
# 1. 在脚本头部配置,mysql的基本参数[登录账号, 登录密码, 数据库名]
# 2. 在终端执行当前脚本,以表名作为脚本参数进行启动
  python 脚本.py 表名
  
# 注意事项
  脚本实现了普通索引,唯一索引和多列索引的重建工作,针对全文索引和主键没有实现
"""

import subprocess, argparse,re
class MySQL(object):
    def __init__(self,tbname=None):
        self.tbname = tbname
        self.username = "root"  # 登录账号
        self.password = "123"  # 登录密码
        self.database = "test"  # 数据库名
        self.handle  = None
        self.result = None
        self.connection()
        self.query_index(self.get_index)

    def connection(self):
        cmd = ['mysql', '-u%s' % self.username, '-p%s' % self.password]
        self.handle = subprocess.Popen(cmd, stdin=subprocess.PIPE, stdout=subprocess.PIPE, universal_newlines=True)

    def query_index(self,callback):
        """查询当前表中的索引信息"""
        if len(self.tbname) < 1:
            return
        sql = "SHOW INDEX FROM %s.%s;" % (self.database,self.tbname)
        self.handle.stdin.write(sql)
        self.result = self.handle.communicate()[0]
        index_list = callback()
        print(index_list)
        self.drop_index(index_list)
        self.create_index(index_list)

    def drop_index(self,index_list):
        """删除索引"""
        for index_name in index_list:
            drop_index_sql = "ALTER TABLE %s.%s DROP INDEX %s" % (self.database,self.tbname,index_name)
            self.connection()
            self.query(drop_index_sql)

    def create_index(self,index_list):
        """创建索引"""
        for index_name,index_info in index_list.items():
            """判断是唯一索引还是普通索引"""
            index_type = "UNIQUE KEY" if index_info[0]["non_unique"] == '0' else "INDEX"
            sql = "ALTER TABLE %s.%s ADD %s `%s` (" % (self.database,self.tbname, index_type,index_name)

            if len(index_info) == 1:
                """单列索引"""
                sql += "`%s` " % index_info[0]["column_name"]
            else:
                """多列索引"""
                sql += ",".join( ["`%s`" % item["column_name"] for item in index_info] )
            sql += ");"

            self.connection()
            self.query(sql)

    def query(self,sql):
        self.handle.stdin.write(sql)
        self.result = self.handle.communicate()[0]

    def get_index(self):
        """获取索引信息"""
        data = self.result.strip("\n")
        data = data.split("\n")
        index_list = {}
        for key,row in enumerate(data[1:]):
            ret = re.match("\S*\t(?P<non_unique>\S*)\t(?P<index_name>\S*)\t(?P<seq_in_index>\S*)\t(?P<column_name>\S*)\t\S*",row,re.S)
            if ret.groups()[1] == "PRIMARY":
                continue
            info = ret.groupdict()
            index_name = info["index_name"]
            del info["index_name"]
            if index_name in index_list:
                index_list[index_name].append(info)
            else:
                index_list[index_name] = [info]

        return index_list
if __name__ == '__main__':
    
    parser = argparse.ArgumentParser()
    parser.add_argument('table', default='',type=str)
    args = parser.parse_args()
    MySQL(args.table)