オート_を変更しない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をインストールする必要があります.インストールしていない場合は、ActiveRecordのmysqlをrequireしてください.rbファイル.なお、InnoDBテーブルの多くは、ALTER TABLE操作とOPTIMIZE TABLE操作が等価である.
我々のデータベースはOPTIMIZE TABLEを実行した後,クエリ速度を10%程度向上させた.