MySQL 総合 Part18

普通に不等号で比較するようにした方がいいね
検索対象を変換してちゃ速度はまったく稼げんよね

変換関数使うとｺｰﾃﾞｨﾝｸﾞは便利だけどインデックス効きません

ハイスコア表示したいのですが
scoreというintの中から
点数が高い方から10件取得とかってどうやればいいのでしょうか

>>939
まずはSQLの基本でも勉強してから・・・

え？SQLのスレじゃないんですか？

select score from table_name order by score desc limit 10;

こんな小学生でもわかりそうな回答書かせるなよ恥ずかしい

すいません、質問してもいいでしょうか？
UNIXにMySQLいれたら
「information_schema」と、「mysql」と「test」ってDBが
元からあるのですが、これはどれも削除してはならないのでしょうか？
よろしくお願いします。ｍ（＿＿＿）ｍ

>>943
Test以外は必須かと。

結局with-opensslオプション入れてTritonn入れなおしたんですが、
mysql > show variables like '%ssl%';
すると最初は
| have_openssl | YES
| have_ssl | YES

になるのに、sslキーと証明書のパス（ssl_ca、ssl_cert、ssl_key）を
入れてMySQLを再起動すると両方 DISABLED になってしまう。

パスが間違ってるはずはないのですが、
何か考えられるミスはあるでしょうか？

あるレコードに関連する都道府県情報を格納するとき
・pref というカラムを作って、カンマ区切りの「,1,2,3,」（1=北海道,2=青森等）で格納
・pref_1 pref_2 pref_3... と都道府県毎にカラムを分けてbooleanで格納
・pref_lists というテーブルを作って、 id,pref_id と正規化
どれが一番現実的でしょうか？

>>946

３回読み直したが、質問の意図が読み取れなかった。スマソ

１番はなんか生理的にイヤ
２番はカラムが少ないならいいけど４７カラムは多すぎる
３番が良さげ

普通、3番なのでは？

タプル数が10万単位だと、一番速いのは2番だよね？

普通は３ですよ。　２でboolean使うなら、64ビットカラム１個作ってフラグ管理するとかもあるけど。

普通は３番でも、>>946がやる３番は、何か違うものになりそうな気がする・・・

>>944
実は必須ではないんだぜ。

あー…解決しました。一応書いておきます。
パスじゃなくてファイルの権限ミスでした。そんなことだと思った…orz
ちゃんとmysqlのユーザーにしましょうってことで…。
chown mysql:mysql file.key でOkでした。

レプリケーションについてご教示ください。

マスターで、テーブルAに対するLOAD DATAによる登録が完了した直後、
テーブルBに対して更新があったとします。
このとき、スレーブにテーブルBの更新が反映されるのは、
スレーブでのテーブルAの登録完了後になるのでしょうか。

スレーブで、テーブルA登録と並行してテーブルBが更新されることはない？

>>955
詳しくはないがbinlog順なら並列はないんじゃないかな

>>955
前の処理が完了してからだよー。
LOAD DATA とレプリケーションの組み合わせには癖があるから以下も参照推奨
http://dev.mysql.com/doc/refman/5.1/en/replication-features-load-data.html

MySQLをインストールしたら、phpMyAdminの「特権」タブを見ましたら、
ユーザ　　ホスト　　パスワード
すべて　　　%　　　　--
すべて　sql.localhost　いいえ
すべて　localhost　　　いいえ
root　　127.0.0.1　　　いいえ
root　　sql.localhost　いいえ
root　　localhost　　　はい
---------------
と6つのユーザがいるのですが、これはroot/localhost/パスワードあり
以外全部削除しても大丈夫なのでしょうか？
よろしくお願いします。ｍ（＿＿）ｍ

Aテーブル
id
---
1
2
3

Bテーブル
id | a_id
----------
1 | 1
2 | 1
3 | 2

というテーブルで以下のような結果を受け取りたいのですが、可能でしょうか？
a.id | b.id
------------
1 | 1
1 | 2
2 | 3
3 | 0

普通に結合すると、Bテーブルに存在しない
a.id=3を出すことが出来ず悩んでいます。

こういう時に left join 使えばいいんですね…。

>>956
>>957
ありがとうございます・
1テーブルに大量件数LOAD DATAすると、
全テーブルのレプリケーションが遅延してしまうんですね。。。

バッチで30個のテーブルに1万件ずつほどのデータを登録しようと思っています。
バッチで登録処理を行なっている間も他のプログラムからテーブルへの
参照が行われる事が考えられるのでトランザクションを貼ろうと考えていますが、
beginをバッチの開始直後,commitをバッチの終了時に行うと
凄くパフォーマンスが悪いように思えます。
テーブル単位でトランザクションを貼ると、
他のプログラムから参照を行ったときに
テーブル間のデータの不整合が発生することが考えられるのですが、
みなさんはこういう場合はどの様に設計されますか？

>>962

> バッチで登録処理を行なっている間も他のプログラムからテーブルへの
> 参照が行われる事が考えられるのでトランザクションを貼ろうと考えていますが、

参照が行われるからトランザクションを使う？
その発想にいたる背景がわからないので答え難い。

安易にバッチ処理での登録と考える前に、
登録処理中にテーブル参照されることで発生する不都合が何かを考慮してから、
データ登録の方法を考えるべきだと思います。

>>963

言葉足らずで申し訳ありません。

テーブルを参照するプログラムは
データの集計を行うプログラムになります。
Aというテーブルではバッチからの更新が終了しているけれど
Bというテーブルではまだバッチで更新中となっている場合
HOGEというプログラムが両方のテーブルの情報を集計するとなると
テーブル間の不整合が発生するのでトランザクションを使用しています。

そして今回はバッチで登録・更新をするテーブルの数が多いので
>>962にあるような質問をさせていただきました。

HOGE というプログラムが、テーブルAとテーブルBを参照するんですね?
そのHOGEというプログラムが、テーブルAとテーブルBに登録処理をしている最中でも稼動するんですね?

HOGE というプログラムがシステムの中で重要度が高いのであれば…

テーブルAとテーブルBにデータを登録する際には、HOGEというプログラムを考慮して、
テーブルAとBへのデータ登録は両テーブルの整合性が取れている状態として1件ごとに処理をする。

そんな方法を検討する可能性も有ります。

述べられている「参照するプログラム」が出力する結果が、そのシステムでどれだけ重要であるかを考慮する必要があります。

逆の意味で述べれば、テーブル更新が終了する前に、システムとして重要な出力結果を出すHOGEを実行しない、これが適切だとも言えます。

初歩的な質問ですが探してもわからなかったので質問します
MySQL5.1をInnoDB Pluginで使っていますが、
1つのテーブルのカラム数は大体このくらいまでにしておいた方が良い、ってのはありますか？
扱うデータが多くて、素直に組むとカラム数が200ぐらいになるのですが
これが1億レコードぐらいになると、パフォーマンスに結構影響しますか？
経験がなくてわからないので、皆さんの意見を聞きたいです

>>966
InnoDBは8KBのレコード長制限がある。
VARCHAR、TEXTなど長いカラムはページ外に保存されるのだけど、
200カラムは作れない可能性が高いので事前に確認しないとだめ
作れるなら性能はそんなに気にしなくてもよい

>>964
別にバッチ処理中か確認するようにすれば？
別テーブルにフラグみたいなもん立てるとか。

素直にトランザクションで解決してなにが悪いのかワカラン。
コミットに時間がかかってもバッチ処理なら問題ないだろうし。

ユーザー作成時に

CREATE USER ＜ユーザ名＞ IDENTIFIED BY '＜パスワード＞';

この時に同時に権限を設定するという事は出来ないのですか？これはあくまでもユーザーを作成するだけで
権限付与は従来からのgrant文でということ？

役立たずの古い技術者です。

バッチプログラムからデータベースのデータ処理をするときに、
カーソルを使って、１件ずつ読みだして処理する。。
という、昔ながらの手法があります。

MySQLで同じような処理を行おうと思いましたが、
カーソルに対応していないことを知りました。

カーソルのフェッチに相当する処理をCライブラリで
実現しようと考えていますが、結果セットを取得するAPIでは
処理実現が厳しいかな？と考えています。

Mysql_store_result()は結果セットをローカルに保存するので、
結果が大量になったとき、メモリ不足が発生しそうで、
枕を高くして眠れません。

Mysql_use_result()は１件読み出しが実現できますが、
対象のテーブルがロックされるとマニュアルに記載があります。
検索範囲や分離レベルによらずに、です。
これでは、バッチ処理が終わるまで他の処理が継続できなくなります。

あとは、mysql_stmt系のサーバ再度カーソルがあるようですが、
サーバ側のメモリを大量消費＆閾値こえるとMyISAMにデータコピーする
仕様になっており、ちょっと手を出すのをためらわれます。

MySQLで大量データを保管するテーブルで、バッチで範囲指定（これも大量にヒットする）
してデータ抽出し処理したい場合、どういう処理をしているのでしょうか？

範囲指定をsqlのselectで指定して
後はupdateするだけじゃね？
行指定ならlimit使うだけ
何がわからんの？

>>971
サーバサイドのmysql_stmt_fetch()使うしかない気がする。
大きな結果セットでMyISAM使うとしても
それってOracleでORDER BY、GROUP BYなどによって一時テーブル使うのと
そんなに変わらない、といいね

>>972
LIMIT 0, 100
LIMIT 100, 100
…
というのは案としてあると思う。
けどACIDとるために最初にテーブルロックせざるをえない

>>971

> カーソルを使って、１件ずつ読みだして処理する。。
> MySQLで同じような処理を行おうと思いましたが、
> カーソルに対応していないことを知りました。

あなたの言われている「カーソルに対応していない」とは、何を指しているのか理解できません。

>>974
カーソル知らないの？
1行ずつフェッチして、更新したりできるんだよ、大量データを逐次処理できるの

> カーソルに対応していない

カーソルは知っているけれども、対応していないが漠然としているのでは？

>>971
>カーソルに対応していないことを知りました。


あなたの使ってるMySQLはカーソルに対応していない
だろ。正確に書いてよ

>>971
カーソルに対応しているmysqlがあるとでも？

>>978
http://dev.mysql.com/doc/refman/5.1/ja/cursors.html

>>979
それ、ストアドだって。
もともと、Cライブラリでカーソルとかいうことじゃね？

問題は、MySQLの通信プロトコル上、非prepare系のAPIでは
SQLを実行した瞬間に結果の全件がクライアントに送られるところ。
MySQLのカーソル操作はクライアント上でエミュレーションしているに過ぎない。
だからSELECT結果が5GBとかに達する場合、クライアントが32bitだと処理できない。

他のRDBMSでは「次のレコードをよこせ」というリクエストを送って
数十レコードごと逐次結果が返されるようになっている。

mysql_stmt_prepare()系は逐次レコードを返すはずだけど
そもそもMySQLでそういうバッチ処理を書く人があまりいないので
ノウハウが少ない。

ウェブ系以外でMySQLよりPostgreSQLが使われるケースが多い理由のひとつ。

>>965
レスありがとうございます。
>HOGE というプログラムが、テーブルAとテーブルBを参照するんですね?
そうです。
>そのHOGEというプログラムが、テーブルAとテーブルBに登録処理をしている最中でも稼動するんですね?
そうです。

>テーブル更新が終了する前に、システムとして重要な出力結果を出すHOGEを実行しない、これが適切だとも言えます。
こちらの方法を検討してみようかと思っています。
ただ、バッチは30分近くかかりそうなので、
その間HOGEが実行されないのが業務用件的にNGになりそうな気配もあります。


>>968
ありがとうございます。
その方向で少し検討してみます。

>>969
大量のデータを登録・更新するときって
トランザクションをずーっと貼りつづけていると
すこぶるパフォーマンスが落ちることってありませんか？
あ、バックグラウンドのバッチだからそんなの気にしなくてもいいじゃないかって
ことでしょうか？

>>983
> >>969
> 大量のデータを登録・更新するときって
> トランザクションをずーっと貼りつづけていると
> すこぶるパフォーマンスが落ちることってありませんか？

InnoDBのMVCCはそこまでアホな実装じゃない。
バッチだから仮に遅くても気にしなくていいってのはそう思うし。

レプリケーションで、スレーブで起動されるSQLスレッドの数は
1スレッドのみなんでしょうか？

1スレッドのみです