カテゴリーアーカイブ SQL

著者:杉浦

ORDER BY句で指定するカラムはFROM句の頭のテーブルの方が高速

ORDER BY 句と別の GROUP BY 句がある場合、または、ORDER BY または GROUP BY に結合キュー内の最初のテーブルと異なるテーブルのカラムが含まれている場合は、一時テーブルが作成されます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.4.4 MySQL が内部一時テーブルを使用する仕組み

　この一時テーブルを作る動作が案外重く、FROM句の最初のテーブルで指定できるならば、そちらで指定しましょうという話です。具体的には下図です。結合に用いている同じ値を持つmembers.primary_keyとpoints.member_keyそれぞれによるソートを用いたクエリの実行時間です。10数回程度しか試していませんが、ここまで差があるならば違いがあることは明らかです。

　一時テーブルが作られているかどうかを確かめるためにはEXPLAIN句、その結果が次の図等です（どちらもtype=Allなのは気にしないでください）。それぞれ上がmembers.primary_keyによるソート、下がpoints.member_keyによるソートです。上側のEXtraにのみあるUsing temporaryは一時テーブルを作るという意味です。これがある場合FROM句の最初のテーブル中のカラムでソートを指定するとかで、どうにか消すと高速化が望めます。

著者:asaba

【mysql】接続エラーの回数制限を超えてアクセスできなくなったときのメモ

アプリからブログを投稿するアプリで機能のプロトタイプを作っていた時に遭遇しました。

こちら↓

<span style="color: #ff0000; font-size: 14pt;">Host hogehoge.jp' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'</span>

複数回にわたる接続を確認したためブロックされました・・・初見ではなにが原因かさっぱり。

リファレンスを見る限り、予め許可する回数が決まっておりその上限を超えると、mysqldは送信元に問題があるとみなしホストがアクセ

スできないように遮断してしまうみたいです。

外部からの不正な攻撃を防ぐための対策なので、原因を一本に絞ることができなくて狼狽えましたがとりあえず

クッキーを全削除して再びアプリを使って投稿したところ正常に動きました。

正規ではないと思いますが、基本的にはまずクッキーを全部消してみることをお勧めします。

それがだめならブラウザを閉じてchromeを再起動をしてみてください。自分のアカウントを使っている場合はいったんログアウト

してから再度chromeを開いてみてください。

著者:村上

【MySQL】データが重複した行を除いて抽出する方法

今回は、MySQLのデータ抽出についてです。
今まで使ったことがなかったので知らなかったのですが、抽出したデータの重複している行を除いてデータを抽出することができるそうなので、こちらの方法について、主に自分の備忘録のためにまとめ。

参考にさせていただいた記事はこちら。

重複行を除外(All, DISTINCT) – データの取得 – MySQLの使い方
https://www.dbonline.jp/mysql/select/index13.html

早速、その方法ですが、重複しているかどうかを指定したいカラムの前に「DISTINCT」を付けるだけです。
具体的には下記のように指定します。

SELECT DISTINCT [カラム名] FROM [テーブル名];

なお、カラムは複数指定することもできます。
その場合は、下記のように指定します。

SELECT DISTINCT [カラム名1], [カラム名2], [カラム名3] FROM [テーブル名];

ちなみに、DISTINCT ではなく ALL と指定すると、重複した行もすべて抽出するできるとのことでしたが、これは指定しなかった場合と同じ動作なので、使うことはないと思います。

以上、MySQLで重複した行を除いてデータを抽出する方法でした。
今までなぜ使う機会がなかったのか疑問なくらい便利なオプションでした…。
今後は是非積極的に使っていきたいと思います！

著者:杉浦

SQLを試すwebサービスSQL Fiddle

　SQL Fiddleは様々なSQLを簡単に試せるオンラインサービスです。下図の様な画面で、任意のSQL環境を選び、テーブルを定義し、SQLを実行する、といったものです。
　
　左の赤丸は実行環境、右の赤丸はテキストからData Definition Languageへの変換機能です。下図の様に、ヘッダ、データを用意するとテーブル作成とそのテーブルにデータを入れる構文が出力されます。
　
　この図では半角スペースで区切っていますが、様々な区切り方に対応しています。詳しくはDDLBuilder/fixture.html at master · jakefeasel/DDLBuilder · GitHubを参照です。入力したデータはBrowserボタンから開くと見やすいです。
　データを入力したら、任意のSQLを右側に入れて実行、下側に出力される結果を見るだけです。

著者:杉浦

高速化のために一度の小さな問い合わせで済むSQL文を作る

　プログラムを扱っているとDB（データベース）に接続する必要のある場面がいくつもあります。DBへのアクセスにかかる時間、実行にかかる時間は無視出来ず、実行速度のボトルネックになることがあります。DBの内部をチューニングする事でも速度の向上はできますが、問い合わせの回数、DBから引っ張ってくるデータの大きさといった部分を問い合わせ文の工夫によって少なく、小さくすることによっても高速化はできます。
　よくある場面は、必要なデータが複数のテーブルに分散している、あるテーブル中に格納されたデータを基にした条件に合致するデータのみを取得したい、といったものです。こういった時、それぞれのテーブルをそのまま扱うようなことをすると次の様なコードが生まれます。

$arr = query('SELECT * FROM master_table');
foreach($arr as $v){
	$hoge_addr = query("SELECT * FROM hoge_table WHERE addr = '${arr['addr']}'");
	...
	...
}

　こういった何度も問い合わせをDBに送る様なコードは実行にかかる時間が長くなりがちです。DBへの問い合わせは少ない回数で少ないデータをとれるようにした方がプログラムは高速になります。必要最小限のデータの取得は*を使わずに細かく指定することでできます。問い合わせを少ない回数で行うためにはAS、JOIN、入れ子内部のSELECTが便利です。

SELECT * FROM(
	SELECT
		master_table.id,
		master_table.name,
		hoge_addr.name AS name1,
		hoge_addr2.name AS name2,
		fuga_table.name
	FROM
		master_table
		INNER JOIN
			hoge_table AS hoge_addr
		ON  master_table.addr = hoge_addr.addr
		INNER JOIN
			hoge_table AS hoge_addr2
		ON  master_table.addr2 = hoge_addr.addr
		INNER JOIN
			fuga_table
		ON  master_table.branch = fuga_table.id
) AS tmptable
WHERE
	name1 = 'hogehoge'

　SELECTの結果はそのままテーブルとして扱うことが出来ます。ここではそうまとめの様に扱っていますがWHERE句中に使うこともできます。

WHERE id IN (SELECT hoge_id FROM hoge_table WHERE hogehoge = 'fugafuga')

とすればINにより()内の結果であるhoge_idのいずれかと一致するIDという条件を作れます。
　JOINは表の結合です。オプションは様々でINNER以外も多く使われます。ASはそのままA as BでAをBとして扱うという句です。JOINとASを組み合わせることで同じ表から違う条件で汲み上げた結果を一つの表にまとめられます。

master_table
INNER JOIN
	hoge_table AS hoge_addr
ON  master_table.addr = hoge_addr.addr
INNER JOIN
	hoge_table AS hoge_addr2
ON  master_table.addr2 = hoge_addr.addr

著者:村上

【MySQL】テーブルのデータのみをダンプする方法

少し前に、データベースのテーブルの構成情報のみを mysqldumpコマンドでエクスポートする方法についてまとめましたが、今回はデータのみを insert文で抽出する方法について。
めったに使わないので、なかなかオプションが覚えられず…。
なので、あとあと自分がコピー＆ペーストして使えるようにまとめます。

なお、前回の記事はこちらから。

【MySQL】テーブルの情報をエクスポート・インポートする方法
https://cpoint-lab.co.jp/article/201808/【mysql】テーブルの情報をエクスポート・インポー/

さて、データのみを抽出するコマンドは下記のとおりです。

mysqldump -u [ユーザー名] -p -t [データベース名] [テーブル名] > [エクスポートするファイル名]

-t のオプションがポイントで、こちらがあるとテーブル構成情報を除いて、データのみをエクスポートできます。
なお、-t ではなく –no-create-info でも同じことができます。
が、個人的には、短い -t オプションの方が使いやすいかなと思います。

以上、データベースのテーブルからデータのみを抽出する方法でした。
本当は、MySQLの INTO 構文を使ってデータをCSVで抽出したかったのですが、どうやらそれを実行するための権限がなかったのでこのような方法をとることになりました。
ほんの少しデータを整形する必要はありましたが、こちらの方法でも簡単にデータ抽出を行えましたので、場合によってはこちらの方法を使ってもいいのかもしれません。

著者:takahashi

PostgreSQLで大文字のカラム名を指定する方法

一番最初に触ったDBがMySQLだったのですが、それに慣れてしまったがゆえに、他のDBエンジンを触った時にその作法の違いに苦労することがあります。

今回、PostgreSQLを使用したシステムの補修を行ったのですが、例えばSelect文を書く時、MySQLを触っていた時の癖で

SELECT * FROM table WHERE column="value";

のように書いていたのですが、PostgreSQLだとエラーになってしまいます。
というのも、PostgreSQLでは “” は文字列を意味するのではなく、カラム名などを大文字を表すときに使うためです。

では文字列を囲うようにするにはどうすればいいかというと

SELECT * FROM table WHERE column='value';

とシングルコートを用いればいいようです。

基本的にSQLを使ったDBでは、SQL文はほぼ同様に使えるのですが、細かい”作法”が結構異なっているものも多いので注意が必要です。