|
2009,12,09, Wednesday
自己メモ備忘録です。
select文ならdistinct keyで重複を省いて結果を返してくれますが、レコードその物を消したいときどうするか考えてみました。 1.まずは現在のテーブルのレコード数を算出 mysql> select count(*) from table_now; +----------+ | count(*) | +----------+ | 348748 | +----------+ 1 row in set (0.00 sec) 2.distinct tel で電話番号のユニーク数を確認 mysql> select count(distinct tel) from table_now; +---------------------+ | count(distinct tel) | +---------------------+ | 238354 | +---------------------+ 1 row in set (3.15 sec) 3.telとname(念の為)でグループをかけたものをtemp_tableに格納 mysql> CREATE TABLE temp_table as SELECT * FROM table_now GROUP BY tel,name; Query OK, 238354 rows affected (18.05 sec) Records: 238354 Duplicates: 0 Warnings: 0 4.temp_tableのレコード数を確認 mysql> select count(*) from temp_table; +----------+ | count(*) | +----------+ | 238354 | +----------+ 1 row in set (0.00 sec) 5.ユニーク数と合致したので、table_nowを削除 mysql> DROP TABLE table_now; Query OK, 0 rows affected (0.18 sec) 6.temp_tableをtable_nowにalter table mysql> ALTER TABLE temp_table RENAME TO table_now; Query OK, 0 rows affected (0.00 sec) 7.一応確認 mysql> select count(*) from table_now; +----------+ | count(*) | +----------+ | 238354 | +----------+ 1 row in set (0.00 sec) これで完了です。 PHPで関数作っておけば引数渡すだけで一瞬で終わっちゃいます☆ |
|
2009,12,02, Wednesday
datetime型の集計で月別とか時間別とかMySQLだけで集計できないかな？？って思ってテスト
うまくできたんで自己メモ  select substring(datetime,6,2) as month,substring(datetime,12,2) as hour,concat(format(count(*),0),"件") as cnt from t_record where datetime >= '2009-06-01 00:00:00' and datetime <= '2009-11-30 23:59:59' group by month,hour; |
|
2006,05,01, Monday
userテーブル(顧客データ・84万レコード)
recordテーブル(対応履歴・3.5万レコード) 対応した履歴を入力した際に、userテーブルに担当者コードを入力(3名まで登録可)。 担当者が担当ユーザーを検索する際、 select * from `user` where (`tantou1` = `tantou_code` or `tantou2` = `tantou_code` or `tantou3` = `tantou_code`) ORDER BY `zip` LIMIT 10,0; これで10件ずつ表示させたり、地区別に検索したりしていました。 ここで、履歴入力した際に担当者コードを入れる訳だし、履歴があるものが担当者が入力されているってことだし結合しても結果は同じ。 結合してから検索するとどれくらい遅くなるんだろう？？ って訳で select * from `user` inner join `record` on `user`.`id` = `record`.`user_id` where (`tantou1` = `tantou_code` or `tantou2` = `tantou_code` or `tantou3` = `tantou_code`) ORDER BY `zip` LIMIT 10,0; 速い!? ？？ microtime() 使って実行時間を計ってみると… 結合無 6.5秒平均 結合有 0.85秒平均 全件なめてから結合するし、その分メモリも食うだろうなぁって思ってたが、inner joinだから少ないrecordを全件なめればあとに存在するものだけを結合するから速いのかな？？ 詳しいことまでわからないけど、大きなDBだとちょっとしたクエリの差が実行時間の差になってくるようです。 クエリの見なししてみればいい結果が出てくるかも!? inner join した方が単独テーブル検索より速いとは… |
|
2006,04,08, Saturday
社員も増えてきて、Serverも徐々に時代遅れになってきたってことでreplicationして更新中心Serverと参照専用Serverと分けて、更新時にテーブルロックがかかって参照が遅れることのないようにしてみました。
あると便利！！ MySQL 逆引き辞典  拠点A Server A(現在利用中(master)) fedoracore4 kernel-2.6.16 mysql-4.1.16 apache-2.0.54 php-5.0.4 光回線(非固定IP) これに拠点BのServer Bをslaveにすることにした。 Server B(新設(slave)) fedoracore4 kernel-2.6.16 mysql-4.1.16 apache-2.0.54 php-5.0.4 光回線(固定IP) まずはServer A(master)側へmysqlへログイン [fedora@serverA ~]# mysql -u root -p Enter password: mysql> grant replication slave on *.* to replication_user@'%' identified by 'replication_user_password'; slaveの拠点Bは固定IPであるが、他拠点から接続する可能性が極めて高いので @'%' とどのIPアドレスでも問題ないようにした。 mysql> FLUSH TABLES WITH READ LOCK; マスタの書き込みブロック mysql > quit [fedora@serverA ~]# su - [root@serverA ~]# vi /etc/my.cfg [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 #2行追記 log-bin server-id=1 [mysql.server] :wqで保存 [root@serverA ~]# /etc/init.d/mysqld restart MySQL を停止中: [ OK ] MySQL を起動中: [ OK ] [root@serverA ~]# ls /var/lib/mysql server名が頭につく以下のファイルが出てくる。 serverA-bin.000001 serverA-bin.index MｙSQL レプリケーション徹底解説 次はslaveの設定 [fedora@serverB ~]# su - [root@serverB ~]# vi /etc/my.cfg [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 #4行追記 server-id=2 master-host=serverAのIPアドレス(ダイナミックDNSで対応) master-user=replication_user master-password=replication_user_password :wq で保存 今度はserverAからレプリケーションしたいデータをserverBへコピー [root@serverA ~]# tar czvf /var/www/html/sample_db.tar.gz /var/lib/mysql/sample_db [root@serverB ~]# cd / [root@serverB ~]# wget https://serverA/sample_db.tar.gz [root@serverB ~]# tar zxvf sample_db.tar.gz [root@serverB ~]# ls /var/lib/mysql sample_db があるのを確認。 [root@serverB ~]# /etc/init.d/mysqld restart MySQL を停止中: [ OK ] MySQL を起動中: [ OK ] [root@serverB ~]# テスト serverAへログインして、データを変更。 ※元データを "1234567890" とし "1234567890a" へ変更 mysql > UPDATE `table` SET `field` = '1234567890a' `table_id` = 1 LIMIT 1; serverAで確認。 変わっていればOK(queryがあっていれば当然) 続いてserverBで確認。 変わっていればOK。 ※変わっていなければstatusや3306ポートの開放や作成したユーザーでログインできるか確認。 状態の確認は SHOW MASTER STATUS; SHOW SLAVE STATUS; ※今度はserverBで "1234567890a" とし "1234567890ab" へ変更 mysql > UPDATE `table` SET `field` = '1234567890ab' `table_id` = 1 LIMIT 1; serverBで確認。 変わっていればOK(queryがあっていれば当然) それをserverAで確認 "1234567890a"のまま変わっていなければOK また状態により一概に言えませんが、レプリケーションの位置が狂った場合は一旦slaveを止め、再度masterからデータを持ってきて上書きし、slaveを再起動するのがいいのではないでしょうか… また今回はたいそうなデータではなく、単なる商品マスタのみで外部に漏れてもどうってことないデータなので、ネット経由で実運用できるか否かのテストを兼ねて行いましたが、重要データは間違いなくVPN張ったあとで行った方が無難です。 あると便利 DBエンジン問わず使えます！！
追記 IPアドレスが変化するタイミングでエラーが起こっていたようです。 その結果、master側での更新がslaveに反映されなくなっていました。 対応としてはmaster slave ともに /var/lib/mysql/ にある ib_logfile* ibdata* server-name-bin* を削除して再起動すればOKです。 |
|
2006,03,30, Thursday
[root@fedoracore4 ~]# wget http://ovh.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.8.0.2.tar.gz
--16:24:01-- http://ovh.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.8.0.2.tar.gz => `phpMyAdmin-2.8.0.2.tar.gz.1' ovh.dl.sourceforge.net をDNSに問いあわせています... 213.186.33.91 ovh.dl.sourceforge.net|213.186.33.91|:80 に接続しています... 接続しました。 HTTP による接続要求を送信しました、応答を待っています... 200 OK 長さ: 3,453,510 (3.3M) [application/x-gzip] 100%[=========================================================================>] 3,453,510 129.45K/s ETA 00:00 16:24:21 (177.54 KB/s) - `phpMyAdmin-2.8.0.2.tar.gz' を保存しました [3453510/3453510] [root@fedoracore4 ~]# cd /var/www/html [root@fedoracore4 html]# mv phpMyAdmin-2.8.0.2.tar.gz /var/www/html/phpMyAdmin-2.8.0.2.tar.gz [root@fedoracore4 html]# ls |grep phpMyAdmin phpMyAdmin phpMyAdmin-2.8.0.2.tar.gz 古いVerのphpMyAdminがある。 リネーム [root@fedoracore4 html]# mv phpMyAdmin phpMyAdmin-old 解凍 [root@fedoracore4 html]# tar zxvf phpMyAdmin-2.8.0.2.tar.gz 中身を見てみるとまた変わっているようです(^^;; まずはhttp://userdomain/phpMyAdmin/ へアクセスしてみると…  phpMyAdmin 2.7.0 pl2 インストール設定 |