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がない場合InnoDBはUNIQUE NOT NULLのkeyをPKにするが、これが関数indexを含んでいる場合はpromoteされない 関数indexにUNIQUEを指定できる SPATIAL,

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> create table t4 ( -> id int not null auto

MySQL8.0の機能を調べてまとめている。 invisible indexesについて読んだまとめ。 dev.mysql.com MySQL 8.0からはinvisible indexesがサポートされる。 invisible indexはoptimizerに使われないindexのこと。 optimizerから見て、visible(利用可能)かinvisible(利用不可)かを選択できるようになった。 invisible indexを利用することで、indexを削除しても影響がないかを確認することが容易になる。 これは結構ありがたい機能で、行数が多かったり、更新頻度が高いテーブルではindexの削除・作成は非常にコストがかかるし、下手にindexを外すとクエリをつまらせてサービスダウンになることもある。 デフォルトではindexはvsibleな状態で、これを変更するにはCREATE TA

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 ...

NULLを許容するカラムをつくるとそれぞれのカラムで必要なストレージサイズが1byte増えていた。 以下はexplainでkey長を見てみた様子。 INTは4byteを確保する訳だけど、NULL (DEFAULT NULL)指定したら各カラムで1byte増えているのがわかる。 NULLABLEかの判定に1bit使っていて、丸められて1byte使っているとしたらNULLABLEなカラムがN個あるとして(N/8+1) byte余計に使うかと思ったらそれぞれのカラムで1byte増えてた。。。(idx8, idx9はその境界値が出たりしないかと思って作った) mysql> CREATE TABLE `t7` ( -> `id` int NOT NULL AUTO_INCREMENT, -> `c1` int DEFAULT NULL, -> `c2` int DEFAULT NULL, -> `c

MySQL8.0の機能を調べてまとめている。 タイトルどおりGROUP BY ... WITH ROLLUPについてドキュメントを読んだまとめ。 GROUP BYで指定したカラム自体の全体の集約(超集約: super-aggregate)結果を返すWITH ROLLUP句で大きく2点の改良と機能追加があった. ORDER BY及びDISTINCTをWITH ROLLUPと同時に指定することができるようになった GROUPING()の導入。超集約によって発生したNULLなのかもともとNULLが入っていたものかを判別可能になった WITH ROLLUPによるsuper-aggregate そもそもWITH ROLLUPによるsuper-aggregateがどういったものか示す 8.0ドキュメントのサンプルと同じテーブル定義を利用し,データは独自で作成。 準備 CREATE TABLE sale

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 total_profit, SUM(p

MySQL 8.0の新機能について調べてまとめました。 この記事は公式ドキュメントの以下のページの読んだまとめです。 dev.mysql.com サンプルのクエリもほぼドキュメントのものですが、手元の8.0.15で実行した結果をつけている場合もあります。場合によって説明の順番を組み替えたり、補足したりしています。 CTE CTE(Common Table Expression)とは1つのステートメントのスコープ内にできる名前付きの一時結果のこと。 この一時結果は複数回またはCTE内で再帰的に参照できるので、再帰クエリと表現されていたりするが必ずしも再帰クエリを書かなくてはいけないわけではない。 Non-recursive CTEs with句に続けて、subqueryによる副次句に名前をつけることができ、これがCTE。 CTEはcomma区切りで複数定義することができ、with句以降のto