论坛首页 编程语言技术论坛

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

浏览 3216 次
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
作者 正文
   发表时间:2009-01-16   最后修改:2009-01-16
数据库中的表不断在增大,删除/更新/添加的多了,表的性能就会降低。定期的执行 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% 左右。
   发表时间:2009-04-24  
很有深度,没怎么看明白。
0 请登录后投票
   发表时间:2009-04-25  
就是把当前的 auto_increment 值保存出来,优化完表之后再写回去。
0 请登录后投票
论坛首页 编程语言技术版

跳转论坛:
Global site tag (gtag.js) - Google Analytics