インデックスの種類

インデックスはサーバーレイヤではなくストレージエンジンレイヤで実装されているため、標準化されていない。
インデックスとは本の目次のようなもので、ストレージエンジンがデータを検索するときにデータ全体をスキャンする必要がなくなるので高速で検索することが可能になる。

Bツリーインデックス

MySQLのストレージエンジンでで一般的に使用されているインデックス。
なんの断りもなしに「インデックス」と言われているときはBツリーインデックスを指していることが多い。

ここにわかりやすくまとまっている
B-treeインデックス入門

5は4より大きいので、右側に進みます
5は6より小さいので、左側に進みます
5が見つかりました

引用：B-treeインデックス入門

検索の具体的な手順はB-treeインデックス入門で開設されている通りの手順。

ストレージエンジンがこのようなツリー構造をあらかじめ作っておき、上の手順を踏むことでデータの高速な検索が可能になっている。

ツリーのノードはソートされているので、高速化された検索と同じようなソートをすることができる。
例えば名前にインデックスがついていたら、名前の検索も高速だが、ソートも高速。

Bツリーインデックスの制限

引用: B-Treeインデックスの話

Bツリーインデックスで例えば上のような複合インデックスを貼った場合は、インデックスを貼る順序が大事になっている。
上の図では姓→名の順にインデックスが貼られているとする。

• 検索がインデックス付きの列の左から始まらないと意味がない。今回の場合は姓が最初のインデックスなので、姓で検索すると高速だが、名で検索しても高速ではない。同様に特定の文字で終わる姓の人の検索にも役たたない。
• インデックスで列をスキップすることはできない。つまり名がMakikoで特定の誕生日をもつ人物を検索することはできない。必ず一番最初に貼ったインデックスの値を指定しなくてはいけない。

ハッシュインデックス

ハッシュインデックスはハッシュテーブルに基づくインデックス。インデックスのすべての列を使用する正確な検索にのみ役に立つ。
MySQLでこの仕組みをサポートするのはMemoryストレージエンジンだけ。

こんなテーブルがあったとする。
架空のハッシュ関数f()があったとして

f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

というハッシュが生成されるとする。

このようにハッシュ値とポインタのテーブルを用意して、ここから検索することができる。
インデックス自体は短いハッシュ値を格納するだけなので、ハッシュインデックスは非常にコンパクト。

はじめに

業務でクエリ文を書いてjoinの使い方のイメージが集合関係で考えるとわかりやすいと思ったので、それについてメモ

結論

inner がつくjoinの仕方は集合論でいう「かつ」になる
outer がつくjoinの仕方は基準にするテーブルが全体集合としてjoinしたいテーブルのカラムがくっ付いた形になる
参考画像の２段目のような集合になる

参考画像は以下の通り

画像引用元
https://www.dofactory.com/sql/join

inner join

これは結論で書いたように結合したい条件を共に満たすテーブルを用意できる。

select * from A inner join B on <条件>

このコードでAというテーブルとBというテーブルをくっつける際に、条件を満たすデータしか用意しない。
満たさないデータは、テーブルから除ける

right outer join

これは、参考画像の２段目右側の画像のような集合になる。

select * from A right outer join B <条件>

このコードでAというテーブルとBというテーブルを右側のBテーブルを基準に条件を参照してくっつける。
基準にするときはBテーブルの全ての行を参照して条件を満たさないデータはNULLを入れてくっつける。

left outer join

これは、参考画像の２段目の左側のような集合になる。

select * from A left outer join B <条件>

これはright outer joinとほとんど同様で、
左側のAテーブルを基準に条件を参照してAとBテーブルをつなぐ。
基準にするときはAテーブルの全ての行を参照して条件を満たさないデータはNULLを入れてくっつける。

バージョン：MySQL 8.0.16

ALTER TABLEで変更したのに…

ALTER TABLE テーブル名 AUTO_INCREMENT = 値でテーブルのオートインクリメントの値を変更できます。
なのに、SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE table_name = テーブル名で変更前のオートインクリメントの値が取れてしまう…

対策

information_schema.INNODB_TABLESTATSには変更後の値が入っていました。
SELECT AUTOINC FROM information_schema.INNODB_TABLESTATS WHERE NAME = DB名/テーブル名で取得できました。カラム名がAUTOINCな点に注意です。

この記事

初投稿です。お手柔らかにお願いします。

AlibabaCloud DataVとはこんな感じのダッシュボード機能。

このグラフひとつひとつにSQLが記載されているのですが、
最短1秒間隔でクエリを投げることが可能。

当然、コネクションをそれなりに使ったり、接続待ちでタイムアウトが起きたりする。

なので、状況に応じて以下の値を変更する必要あり。

parameter	value
max connection	1000以上
wait_timeout	288000
connect_timeout	288000

上記値はサンプルだが、たくさんのSQLが走るとSQLクライアントまで切断されてしまうので、
connect_timeoutも変更した方がベター。。

ここら辺をつかって調査

SHOW GLOBAL STATUS
SHOW PROCESSLIST;
show global variables like '%timeout%';