MySQLでデータを挿入するときには、INSERT文を使っていると思います。
１レコードや１０レコードぐらいのデータをINSERTするときには、そんなに気にならないのですが、数万～数百万単位のデータをINSERTする場合、非常に時間がかかります。
レコード数が多くなれば時間がかかるのはしょうがないことなのですが、できれば処理時間は短くしたいものです。
そこで、バルクINSERT文というものを使えば処理時間は劇的に短縮できます。
私の場合、通常のINSERT文は[INSERT INTO {table_name} SET {カラム名} = {値}]と記述しています。
これを[INSERT INTO {table_name} (カラム名、カラム名) VALUES(値１,値２),(値３,値４)]のようにある程度のレコードをまとめてSQL文を生成して、一括でクエリを実行すると処理は劇的に短縮されます。
とあるシステムが運用していたのですが、データ数の肥大化に伴い設計を見直すことになりました。
そこで、現在１つのDBで運用しているものを複数のDBに分割して構築することになりました。
以前から気になっていた、MySQLデータフォルダ内に格納してあるibdataのファイルサイズの肥大化も
解消されるのかと思いきやどうも複数のDBであっても１つのibdataファイルを共有するようなのです。
唯一の方法として、InnoDBエンジンのテーブル毎ibdataの分割という手段があったのでとりあえずこの方法で
設計をすることにしました。
テーブル毎のibdata分割の方法については、my.cnf内の[mysqld]の項目内に「innodb_file_per_table」と記載するだけです。
ちなみに、この方法はMySQL4.1系以降から利用できるみたいです。
私の場合は、MySQL4.0.26なのでMySQLのバージョンから見直しですorz
この設定に変更した場合であってもテーブルスペースは必要です。
テーブルスペースにはテーブル毎のデータやインデックスの値だけでなくInnoDBに関する情報やREDOログなどが含まれるからです。
MySQLには複数のストレージエンジンが選択できます。
その中で代表的なものが「MyISAM」「InnoDB」「HEAP」の三つだと思います。
それぞれに特徴があります。
まず、「MyISAM」について。
MySQL独自の形式であり、MySQLの特徴である軽快で高速な動作をするという所以のエンジンです。
ただ、速度を重視するためトランザクションをサポートしていません。
一般的には、参照テーブルなどに利用されていると思います。
次に、「InnoDB」。
このエンジンはMyISAMとは異なり、トランザクション、参照整合性制約、行レベルロック、インスタンス障害時の自動リカバリなどを
サポートしています。Oracleの表と似ています。
ただし、速度的には低速になります。
また、ディスクの容量についても、MyISAMより多くの容量を必要とします。
最後に、「HEAP]ですが、私もあまり使わないエンジンなので語弊があるかもしれません。
最大の特徴は、すべてのデータがメモリ上でのみ扱われます。その為、非常に高速です。
そのかわり、TEXT型やBLOB型などの列長の長いデータは使用できませんし、トランザクションもサポートしていません。
また、メモリ上での扱いと言う点からインスタンスの停止によってデータが消失します。
それぞれのエンジンに特徴があるので、データベース設計時には必要なエンジンを選択する必要があります。
※ここで言うエンジンの高速・低速という表現は一般的な表現ですが、システムの構築・データによっては逆になる場合があります。
MySQLでとあるシステムを構築、運用していたときのことです。
要望があり、項目追加の為テーブルにカラムを追加することがありました。
「ALTER TABLE・・・」でカラムを追加しようとしたのですが、MySQLのサービスが停止してしまいました。
既存テーブルにはレコードが９００万件近くあり、なぜ？？と頭をひねっていました。
とりあえずOS,Apache、MySQL全てのログを採取して洗い出したところ原因が判明しました。
原因はMySQL側にあり、ログには「Innodb_buffer_pool_sizeが８Mしかないので、設定を変更してください。」と。
とりあえず、指定された設定値を増やして問題は解決しました。
レコードが増加することを当初の設計段階で把握できてなかった私の落ち度でした。
MySQLって軽快な動作で、検索が早いと評判ですがどうしてもレコード数の増加、並び替えの必要な場合など、処理によっては異常に処理時間が長くなることはないですか？
処理時間の長いクエリは、システムとしては欠陥クエリなんですけど、そこは置いておいて。
ORDER BY句で並び替えをした場合のSQL文をExplainで確認してみると、ExtraにUsing filesortという表示が出た場合の話です。
この表示が出た場合、SQLクエリの処理に時間がかかります。レコード数に比例して。
とあるシステムで、検索に失敗することがありました。
開発環境ではレコード数が少ないので異常はなかったのですが。
「Got error 28 from table handler」が表示されます。
MySQLを使っていて抽出したデータを並び替えるというのはごく一般的に使われると思います。
例えば、INT型のカラム名Aというものを昇順にならび替える場合はSQL文に
「 ORDER BY A 」と追記してあげれば正常な結果が出てきます。
ただ、私の場合はVARCHAR型のカラム名Bのデータを昇順に並び替える必要が出てきたことがあります。
単純にORDER句を使えば正常な結果が出てくるのですが、そのBに入っているデータは数字であったり文字であったりしています。
例えば、「第１号」とか「第２３４号」とか。
この並び替えをORDER句を使って実行すると先頭からの文字でソートされてしまいます。
１「第１号」
２「第１１号」
３「第１１１号」
４「第２号」
という感じです。
これを、番号順に並び替えるにはどうすればいいか。
答えは、桁数順→数字順とすれば正常に抽出されます。
SQL文では「SELECT ＊ FROM table ORDER BY LENGTH（B）、B」
という感じです。
LENGTHでカラムBの文字数順に並び替えてくれます。
ただ、一点未解決の部分がありまして、「第１０−１号」の場合はどうするか？
これは、PHP側でデータを取り込む際もしくは、抽出したデータを再度並び替えすることで対応しました。
