ドゥアイネットのプログラマー、湯川が開発日記を綴ります。

CALENDAR
S M T W T F S
    123
45678910
11121314151617
18192021222324
252627282930 
<< November 2018 >>
ARCHIVES
CATEGORIES
複合インデックス
Mysqlの複合インデックスを使用しました。

「検索条件に複数列を指定する場合などでは、単一カラムのインデックスよりも
複合インデックスの方が高速な検索を行うことが出来る」というものです。

作成するときは以下のように書きます

create index idx_colA_colB_colC  on  tableA(colA,colB,colC);
※create index 後の名称は任意です。
複合インデックスにしたいカラムをまとめて記述します。

消すときは以下のように書きます。

drop index idx_colA_colB_colC  on  tableA;

複合インデックスを使用することで、検索速度を上げることができました。
注意しないといけないのは、指定したカラムが検索条件として使われるようにしないと
複合インデックスが検索に使われないところです。

上記の場合だと以下の条件で検索しても、複合インデックスは検索に使われません。
where colA = '001' AND   (colB = '002' OR colC = '003')

この場合は、複合インデックスが使用されます。
where colA = '001' AND   colB = '002' AND colC = '003'

ちょっと気をつけなければいけませんが、
データ数が多くなってしまったシステムの検索速度向上に使えると思いました。
| Mysql | 16:02 | comments(0) | - |
改行コードを検索
 Mysqlで改行コードを検索する場合は以下のように書きます。

select col1 from testtbl where col1 like '%¥n%';
「¥n」で検索できます。

改行コードをスペースなどに置き換えたい場合は、
UPDATE testtbl SET col1 = REPLACE (col1 ,'¥n',' ')
where col1 like '%¥n%';
※col1に改行コードが入っているものを対象に改行コードをスペースに置き換えます。

改行コードを検索できることも、「REPLACE 」関数があることも今日知りました。
他の人に聞かれたことがきっかけで、調べて知ることができました。
ちょっと得したかなと思います。
| Mysql | 17:15 | comments(1) | - |
カラム位置の入れ替え
Mysqlでカラムの位置を入れかえる方法を調べました。

以下のテーブルで、user_nameを二番目にしたい場合です。
create table testtable (
  id char(8),
  address varchar(100),
  user_name varchar(30),
  primary key (id)
);

このSQLで入れ替えができます。
alter table testtable change user_name user_name varchar(30) after id;

change構文を使って、user_nameの名前や型は変えないで、最後に位置指定を記述します。

これで入れ替え完了です。
create table testtable (
  id char(8),
  user_name varchar(30),
  address varchar(100),
  primary key (id)
);

仕様追加の際に、ちょっとわかりにくい位置にカラムを追加したときに使えます。
| Mysql | 17:43 | comments(0) | - |
MySQLのFORCE INDEX
 開発を進めている中で、MySQLのINDEXが有効にならない場合がありました。
どうも、WHERE句で条件指定する中で"OR"を用いて指定する場合に有効にならないようです。

色々と試していく中で、FROM句のテーブル名のあとに、FORCE INDEX(INDEX名)と書くと有効になるようです。
MySQL4.0.9から使用できるみたいです。それ以前のバージョンでは USE INDEXになるようです。

データを検索する中で、INDEX を有効にした場合とそうでない場合は、データ数が多ければ多いほどその効果に差が出ます。
考案したSELECT文が INDEXを有効になっているかは気にかけたいところです。
| Mysql | 17:28 | comments(0) | - |
MySQLのインデックスのファイルサイズ

 MySQLでレコード数が約60万件あるテーブルがあるとします。
そのテーブルにたいしてSQLを発行すると、朝一は遅いのですが、2回目以降は速いです。

RESET QUERY CACHE; でクエリキャッシュをクリアしても実行時間は速いです。

なんでだろうかと思い、ふとレコードのファイルサイズはどのくらいだろうと思いました。
以下のSQLで調べることができました。

データのファイルサイズ      : 87M
インデックスのファイルサイズ:106M

インデックスのファイルサイズがデータより大きかったです。
MySQLがインデックスを読み込むI/O処理にオーバヘッドがかかっているのかとも思ったのですが、
ちょっと解決策が見えない状態です。

select table_name, engine, table_rows, avg_row_length, data_length, index_length
from information_schema.tables
where table_schema=database()
order by (data_length + index_length) desc;

+------------+----------------+-------------+--------------+
| table_rows | avg_row_length | data_length | index_length |
+------------+----------------+-------------+--------------+
|     615570 |            141 |    87006480 |    106492928 |
|      29471 |           1856 |    54698176 |      1368064 |
|      30237 |            748 |    22626304 |            0 |
| Mysql | 19:13 | comments(0) | - |
MySQLでLock wait timeout exceededエラー

以前からMySQLで「Lock wait timeout exceeded; try restarting transaction」エラーが発生していました。

原因がよくわからなかったのですが、ロックをかける際にテーブルロックとレコードロックを同時に実行するとデッドロックに陥るようです。

以下のようなサンプルをコンソールから同時に実行すると片方が止まります。
テーブルタイプがInnoDBの場合は、レコードロックのみでロックするのがよさそうです。

<?php
//レコードロックでロックする
$db = mysql_connect('192.168.1.xx', 'xxxxx', 'xxxxx');
mysql_select_db('test', $db);
for ($i=0; $i<1000; $i++) {
  mysql_query('begin');
  $rs = mysql_query("select b from test where a = 'AAA' for update");
  $row = mysql_fetch_assoc($rs);
  $num = $row['b'];
  $nextnum = $num + 1;
  mysql_query("update test set b = '$nextnum' where a = 'AAA'");
  mysql_query('commit');
  print("b=[" . $num . "]¥n");
}
?>

<?php
//テーブルロックでロックする
$db = mysql_connect('192.168.1.xx', 'xxxxx', 'xxxxx');
mysql_select_db('test', $db);
for ($i=0; $i<1000; $i++) {
  mysql_query('lock tables test write');
  $rs = mysql_query("select b from test where a = 'AAA'");
  $row = mysql_fetch_assoc($rs);
  $num = $row['b'];
  $nextnum = $num + 1;
  mysql_query("update test set b = '$nextnum' where a = 'AAA'");
  mysql_query('unlock tables');
  print("b=[" . $num . "]¥n");
}
?>

| Mysql | 17:59 | comments(0) | - |
指定した時間が含まれるか
既に登録されているデータと時間が重複していないか?
開発でよく書く条件です。

開始・終了が一致する時間だけでなく、
登録するデータと1時間でも重なっていたら、重複とするときは
以下のように条件を書けば、重複の有無を確認できます。

14:00〜20:00と時間が重複するデータを抽出する場合の例です。

select * from 対象テーブル
where (starttime = '14:00' and endtime ='20:00')
or (starttime < '14:00' and endtime > '14:00')
or (starttime < '20:00' and endtime > '20:00')
or ('14:00' < starttime and '20:00' > starttime)
or ('14:00' < endtime and '20:00' >endtime );

何度か開発で作成したことがある条件なのですが、
必要になるたびに一から考えているような気がするので
ここにメモしておきます。
| Mysql | 18:23 | comments(0) | - |
Mysqlダンプ
Mysqlダンプコマンドは便利です。
使おうとしたときにオプションを思い出せないことがあるので
まとめておきます。

文字コード指定
mysqldump -u userid -ppaswd databasename --default-character-set=ujis>/tmp/dmp.sql

データなし
mysqldump -u userid -ppaswd --no-data databasename >/tmp/dmp.sql

create文なし
mysqldump -u userid -ppaswd --no-create-info databasename >/tmp/dmp.sql

データベース内の指定テーブルのみ出力
mysqldump -u userid -ppaswd databasename table名1 table名2>/tmp/dmp.sql
※データベース名、テーブル名をスペースで区切って指定する。

出力内容の一行のバイト数指定
mysqldump --opt -O net_buffer_length=4096 -u userid -ppaswd databasename --default-character-set=ujis>/tmp/dmp.sql

組み合わせもOK
mysqldump --opt -O net_buffer_length=4096 -u userid -ppasswd --no-create-info databasename table名1 table名2 --default-character-set=ujis>/tmp/dmp.sql
| Mysql | 17:39 | comments(0) | - |
Mysqlでの0詰め
Mysqlで取得したデータを左ゼロ詰めする処理が必要になりました。
取得したあとフォーマットしてもよかったのですが、
ちょっと面倒だなと思い、関数がないか調べてみました。

「lpad」、「rpad」という関数が見つかりました。

select lpad(col1,3,'0') from table1;
とすると、col1が「1」の場合、「001」が取得できます。

select rpad(col1,5,'0') from table1;
とすると、col1が「3」の場合、「30000」が取得できます。

今回はこのようにして、並び順の更新に使用しました。
update table1 set
dispseq = lpad(CAST(dispseq as SIGNED) + 1, 3, '0')

「001」のようなフォーマットで保存されている「dispseq 」を
1プラスして更新しています。

まず、文字型なのでcastで数値にして+1したあとに
lpad関数で3桁にして保存しています。

関数を組み合わせることで少し楽にデータ更新できました。
| Mysql | 18:33 | comments(0) | - |
Mysqlでテーブル構造が変更された時間
Mysqlでテーブル構造が変更された時間を取得できないかと調べてみました。
とりあえず、「show table status」を使うと、テーブルの情報は取得できます。

show table status from DB名 like 'テーブル名';

この結果に「Create_time」と「Update_time」があります。

「Update_time」かなと思ったら、テーブルデータが更新された日時でした。
「Create_time」は当然、テーブルが作成された日付です。

テーブル構造を「Alter文」で変更して、再度「show table status」してみたら
「Create_time」が更新されていました。
これでよさそうですが、「Create_time」は、テーブルが作成された日付としか
ネット上でも書かれていません。

なんとか裏づけがほしいと思って調べました。
「Alter文」のところを見てみたら、このような記述がありました。
「ほとんどの場合、 ALTER TABLE は元テーブルのテンポラリ コピーを作成する事で起動します。そのコピー上で変更が行われ、その後元テーブルが削除されて新しいテーブルがリネームされます。」
なので、Alterが実行されたら、テーブルは新しくなっているようです。

これでテーブル構造が変更された時間がとれそうです。
| Mysql | 18:20 | comments(0) | - |
| 1/2PAGES | >>