不改变Auto_increment，对InnoDB表进行优化

数据库中的表不断在增大，删除/更新/添加的多了，表的性能就会降低。定期的执行 OPTIMIZE TABLE 很有效的提高查询速度。

不过，对于 InnoDB，如果一旦进行 OPTIMIZE TABLE 操作，就会把 Auto_increment 变更为目前的最大的值 +1。Rails 的数据表默认是以 id 作为主键的，而且它就是把 Auto_increment 的值作为自己的值。这样，如果一个数据表最后添加的记录被删除了，在执行 OPTIMIZE TABLE 后，那些被删除的 id 就有可能被重新使用。如果你是定期的表的导入导出，或者以 id 为目录存储文件，就会产生大问题了。

这个script主要是对数据库进行 OPTIMIZE TABLE 操作，而不会改变 Auto_increment 的值。
建议专门建立用于 OPTIMIZE TABLE 的用户（这里设定的用户名和密码都是 optimize），需要给他表的 SELECT、INSERT、ALTER、LOCK TABLES 权限。

#!/usr/bin/env ruby

require "mysql"

USER = "optimize"
PASSWORD = "optimize"
SOCKET = "/var/lib/mysql/mysql.sock"

def optimize_host(host)
  begin
    # connect to the MySQL server
    dbh = Mysql.real_connect(host, USER, PASSWORD, nil, nil, SOCKET)
    puts "========== #{host} =========="

    dbh.list_dbs.each do |database|
      next if database == 'mysql' || database == 'information_schema' || database == 'test'
      puts "#{database} Tables"

      dbh.select_db(database)
      tables = dbh.query("SHOW TABLE STATUS;")
      tables.each_hash do |table|
        name = table["Name"]
        next if name == 'schema_info'
        puts "Optimize Tables #{name}"
        engine = table["Engine"]
        puts "Engine: " + engine.to_s

        if engine == 'MyISAM'
          dbh.query("OPTIMIZE TABLES #{name};")
        elsif engine == 'InnoDB'
          dbh.query("LOCK TABLES #{name} WRITE;")
          autoid = nil
          dbh.query("SHOW TABLE STATUS LIKE '#{name}';").each_hash do |row|
            autoid = row["Auto_increment"]
          end
          puts "Auto_increment: " + autoid.to_s
          if autoid.nil?
            dbh.query("ALTER TABLE #{name} ENGINE=InnoDB;")
          else
            dbh.query("ALTER TABLE #{name} ENGINE=InnoDB AUTO_INCREMENT=#{autoid};")
          end
          dbh.query("UNLOCK TABLES;")
        elsif
          puts "Warning: Unknown table engine.............................................."
        end
      end
      tables.free
    end
  rescue Mysql::Error => e
    puts "Error code: #{e.errno}"
    puts "Error message: #{e.error}"
    puts "Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")
  ensure
    # disconnect from server
    dbh.close if dbh
  end
end

ARGV.each do |host|
  optimize_host(host)
end

需要安装 Ruby/MySQL 才可以，如果没有安装，可以 require 一下 ActiveRecord 中的 mysql.rb 文件。还要说明一下，对已 InnoDB 表，大多数的 ALTER TABLE 操作和 OPTIMIZE TABLE 操作是等价的。

我们的数据库在执行了 OPTIMIZE TABLE 后，查询速度提升了 10% 左右。

评论

2 楼 rainux 2009-04-25  

就是把当前的 auto_increment 值保存出来，优化完表之后再写回去。

1 楼 wcily123 2009-04-24  

很有深度，没怎么看明白。