MySQLのストレージエンジンはplugableになっていて、APIを実装すれば自作のストレージエンジンを組み込むことができる。 ということで、試しにRedisをストレージエンジンとして使うRedis Storage Engineを作りました。

github.com

途中で飽きてしまった ちまちま実装するよりC++の勉強とInnoDB読んだほうが良さそうと思ったので、お蔵入りするつもりでしたが、Yahoo! …

sakaikさんのブログで今月の日付一覧を得るクエリを読んでいて、ここで紹介されているクエリが手元で実行できなかったので、メモ。

sakaik.hateblo.jp

原因は手元の環境が8.0.17でVALUES()関数がなかったことが原因。

よく見るとvalues()関数でテーブルを作って、それを自己結合しつつ出力を作ってソートしている。
再帰CTEでシーケンスが作れるので、それを使っても良いかもなと思い、復習がてら書いてみました。

with recursive rec(V, D) as (
  select 1, cast(date_format(@dt, '%Y-%m-01') as date)
  union all
  select V+1, date_add(D, interval +1 day)
  from rec
  where D < last_day(@dt)
)
select D as `DATE` from rec; …

MySQLで(他のDBMSは知らない)ある値のリストxを使ってテーブルからIN(x), NOT IN(x)を使ってデータを取っても2つのクエリで全件を取ってこれないという話は有名だが、MySQL Server Blogにantijoin optimizationも含めた解説記事が出たので、これを機に(is null, is true, in()などとNULLに対する比較演算を整理してみる。

reference: https://mysqlserverteam.com/a-must-know-about-not-in-in-sql-more-antijoin-optimization/

結論

• …

8.0.18がリリースされたのでHash Joinを試してみました。

dockerには8.0.18 imageはなかったのでcentos7にinstallして実験

先にまとめ

• HASH JOINは等価条件のJOINでかつjoinするカラムにindexがない場合に採用される(ドキュメント1行目)
• HASH JOINしたかどうかはEXPLAIN ANALYZEもしくはEXPLAIN FORMAT=TREE:で確認する

• JOINアルゴリズムの選択をコスト計算で行っているかは不明

  • optimizer_traceみても単純に判断できそうな出力はない

install

yum localinstall -y …

MySQL8.0の機能を調べてまとめている。 Functional Key Partsについて読んだまとめ。

dev.mysql.com

MySQL 8.0.13から関数index(functional key parts)がサポートされる。
5.7以前ではカラムの値そのものかカラムのprefixでしかindexを作成することができなかったが、8.0.13からはテーブルに直接入っている値以外でもindexを作成することができるようになる。

• マルチカラムインデックスでは関数インデックスと通常indexを混在して指定できる
• ASC, DESC指定は関数インデックスでも可能
• PKに指定できない
• PKがない場合 …

MySQL8.0の機能を調べてまとめている。
Descending indexについて読んだまとめ。

dev.mysql.com

MySQL 8.0では、Descending index(DESC, 降順のindex)がサポートされるようになった。 これまではASC(昇順)のindexを逆順にスキャンすることはできたが、パフォーマンス上のペナルティがあった。 Descending indexを使うことで、降順のアクセスでも正順でスキャンすることができる。 optimizerはDESCを含むマルチカラムindexであってもコストを考慮してこれを利用してくれる。

サンプル

c1, c2カラムにasc, descの組み合わせでカラムを作った。
index名はasc, descの頭１文字をとってaかd

mysql> …

MySQL8.0の機能を調べてまとめている。
invisible indexesについて読んだまとめ。

dev.mysql.com

MySQL 8.0からはinvisible indexesがサポートされる。

invisible indexはoptimizerに使われないindexのこと。 optimizerから見て、visible(利用可能)かinvisible(利用不可)かを選択できるようになった。 invisible indexを利用することで、indexを削除しても影響がないかを確認することが容易になる。
これは結構ありがたい機能で、行数が多かったり、更新頻度が高いテーブルではindexの削除・作成は非常にコストがかかるし、下手にindexを外すとクエリをつまらせてサービスダウンになることもある。

…

MySQL8.0の機能を調べてまとめている。 Derived Table, Lateral Derived Tableについて読んだまとめ。

DERIVED TABLE

LATERAL DERIVED TABLESや8.0の新機能を説明する前に、DERIVED TABLEについて説明する

dev.mysql.com

DERIVED TABLEとはFROM句のscope内で作るテーブルのことで、例えばFROM句に書いたサブクエリのSELECT等がある。 構文としては以下で、

SELECT ... FROM (subquery) [AS] tbl_name [(col_list)] ...

JSON_TABLE()関数によってJSONカラムからderived tableを作ることもできる

SELECT * FROM JSON_TABLE(arg_list) [AS] tbl_name ...

このとき、

• …

MySQL8.0の機能を調べてまとめている。
タイトルどおりGROUP BY ... WITH ROLLUPについてドキュメントを読んだまとめ。

GROUP BYで指定したカラム自体の全体の集約(超集約: super-aggregate)結果を返すWITH ROLLUP句で大きく2点の改良と機能追加があった.

1. ORDER BY及びDISTINCTをWITH ROLLUPと同時に指定することができるようになった
2. GROUPING()の導入。超集約によって発生したNULLなのかもともとNULLが入っていたものかを判別可能になった

WITH ROLLUPによるsuper-aggregate

そもそもWITH ROLLUPによるsuper-aggregateがどういったものか示す

…

MySQL 8.0の新機能について調べてまとめています。 今回はWindow関数に関する章(https://dev.mysql.com/doc/refman/8.0/en/window-functions.html )を読んだまとめです。

window関数はクエリの結果から行ごとにその行に関連する計算を行う機能。
GROUP BYでは集約操作の集約するカラムの値ごとに1行の結果が返ってくるのに対し、window関数では行ごとにwindow関数による計算され結果が返る。

例えば(https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html より)

mysql> SELECT
         year, country, product, profit,
         SUM(profit) OVER() AS …