現状フルサイズ350カラム、実際に運用時30カラム程度で、165万件くらいのデータベースなんだが、ほぼすべてのカラムがTextとして構築されているためにMySQLに乗り切らないことは前に書いた。
で、どうしてTextなんだということだが、要するにデータ解析の過程でno dataなところにNAもしくは . が入力されているためなんだが、だったらこれらを空白に置き換えてしまえば、データも圧縮され、Queryの速度も上がるんじゃない？
ということでやってみた。データベースから一旦textファイルに書き出し、sedでNAと. を取り除くことにする。
sed s/$'\t'NA/$'\t'/g exome.bak > exome_edit1.txt sed s/$'\t'$'\.'/$'\t'/g exome_edit1.txt > exome_edit2.txt
TAB区切りテキストなので
で、tableの設定も該当箇所をStringからFloatもしくはIntegerに書き換えて、インポートした。
で、諸々の設定を新しい設定に合わせて書き直し、データベースにアクセスしてみると・・・
全然速度変わらんし。
ファイルサイズは5GBから4.3GBくらいに下がっているので、全く意味が無いわけではないが。あと、350カラムあってもMySQLに構築できたので、環境移行するなら意味はあるかもしれない。
とはいってもそもそもMySQLとSQLiteでは下手するとSQLiteのほうが軽快に動作するので、無理にMySQLに移行しなければならないという理由はない。MySQLだとMySQL Workbench等データベースの汎用フロントエンドが使いやすいかな？というレベルなので、メンテナンスツールをちゃんと作り込んでおけばSQLiteのほうが優位性があるように思う。
で、優位点の一つとしてSQLiteにはインメモリーデータベースというのがある。データベースをHDDに置くのではなく、メモリー上に作成して高速に検索するという機能である。
そのままではSQLiteをシャットダウンするとデータも消えてしまうが、どうやらHDDにおいたデータを最初にメモリ上にまるっとコピーして、リードオンリーで検索をかけて結果を表示させることが可能なようだ。データベースのメンテナンス時はやはり時間が掛かるが、一度構築してしまえば、セッションの最初に読み込みに時間がかかったとしても、その後の検索はサクサク行くはずだ。
http://labo.utsubo.tokyo/2017/02/23/sqliteをpythonからインメモリで使う/
このサイトによると2倍以上高速化するらしいから、是非試してみようと思う。
