カテゴリー「mysql」

アプリケーションで長々SQLを書かなきゃいけないんだけど、

条件によってはSQLを評価しないときってあるかと思います。

たとえば、検索するときの対象テーブルとして、

  • 記事
  • 動画

の2つがあったとき、条件によっては、記事のみにしたいって場合です。

このとき、アプリケーションのIFで、動画のSQLは流さないようにするってのが普通だと思うんですが、

CTE(WITH)とか使っていると、後続の式(たとえばUNION)で動画の分を抜くとか調整しなくちゃいけなくてめんどくさかったり。。

そんなとき、ちょっと役に立つ技が「LIMIT 0」です。

これは、公式にも書いてあるように、即座に空の結果セットを返すというものです。

https://dev.mysql.com/doc/refman/8.0/ja/limit-optimization.html

explain時のExtraには、「Zero limit」というのが表示されます。

それ以外にも、SQLとして正しいかどうかだけをチェックするときにも使えたりします。

「LIMIT 0」以外にも、自分の小ネタを紹介しておきます。

たとえば、アプリケーションでWHEREを組み立てなきゃいけないとき、

条件がなにもなかったら、WHERE句自体を消すとかもありますよね。

そんなとき、自分は「1 = 1」で初期化しておくみたいなこともやったりします。

そうすると、WHERE句自体は残したままでよくなるので、文字列編集の煩わしさが減ります。

「LIMIT 0」とか「WHERE 1 = 1」とか、はっきいって邪道ですが、

使うと意外にアプリケーションでのSQL構築が楽になったりします。

以上

投稿日時:2022年05月10日 00:07   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

1対Nのテーブル2つがある場合、N側のテーブルはデータを縦持ちするはず。

しかしながら、1側のテーブルに合わせて、1行で表示したい場合の小ネタ。

せっかくなので、MariaDB10.7の新機能も添えて紹介。


たとえば、こんなテーブルとデータがあります。

CREATE TABLE `question` (
  `question_id` int(11) NOT NULL COMMENT '問題ID',
  `question_text` text NOT NULL COMMENT '本文',
  PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='問題'

> select * from question;
+-------------+--------------------------------------------------------------------------------------------------------------+
| question_id | question_text                                                                                                |
+-------------+--------------------------------------------------------------------------------------------------------------+
|           1 | しゃっくりはある調味料をなめると止まります。ある調味料とはなんでしょう?                                                 |
+-------------+--------------------------------------------------------------------------------------------------------------+
CREATE TABLE `question_select` (
  `question_id` int(11) NOT NULL COMMENT '問題ID',
  `select_id` int(11) NOT NULL COMMENT '選択肢ID',
  `select_text` text NOT NULL COMMENT '選択肢内容',
  PRIMARY KEY (`question_id`,`select_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='選択肢'

> select * from question_select;
+-------------+-----------+-------------+
| question_id | select_id | select_text |
+-------------+-----------+-------------+
|           1 |         1 | お酢        |
|           1 |         2 | 砂糖        |
|           1 |         3 | 醤油        |
|           1 |         4 | 塩          |
+-------------+-----------+-------------+

これを1行で表示したい場合、以下のようにやる。

> with 
 t as (
   select
     t1.question_id,
     t1.question_text,
     sformat('[{}]', group_concat(distinct json_object('select_id', t2.select_id, 'select_text', t2.select_text) order by t2.select_id)) as select_list
   from
     question as t1
     join
     question_select as t2
     on t1.question_id = t2.question_id
   group by
     t1.question_id, t1.question_text
 )
 select
   question_id,
   question_text,
   json_value(select_list, '$[0].select_id') as select_id_1,
   json_value(select_list, '$[0].select_text') as select_text_1,
   json_value(select_list, '$[1].select_id') as select_id_2,
   json_value(select_list, '$[1].select_text') as select_text_2,
   json_value(select_list, '$[2].select_id') as select_id_3,
   json_value(select_list, '$[2].select_text') as select_text_3,
   json_value(select_list, '$[3].select_id') as select_id_4,
   json_value(select_list, '$[3].select_text') as select_text_4
 from
   t
 ;
+-------------+--------------------------------------------------------------------------------------------------------------+-------------+---------------+-------------+---------------+-------------+---------------+-------------+---------------+
| question_id | question_text                                                                                                | select_id_1 | select_text_1 | select_id_2 | select_text_2 | select_id_3 | select_text_3 | select_id_4 | select_text_4 |
+-------------+--------------------------------------------------------------------------------------------------------------+-------------+---------------+-------------+---------------+-------------+---------------+-------------+---------------+
|           1 | しゃっくりはある調味料をなめると止まります。ある調味料とはなんでしょう?                                                 | 1           | お酢          | 2           | 砂糖          | 3           | 醤油          | 4           | 塩            |
+-------------+--------------------------------------------------------------------------------------------------------------+-------------+---------------+-------------+---------------+-------------+---------------+-------------+---------------+

ポイントはgroup_concatで横持ちに変換する際に、JSON配列にしてしまうこと。

集計するときに結構使える技。

今回はMariaDBの10.7新機能であるsformat関数を使ってみましたが、concat関数でも代用可能です。

MySQLの場合は、「->>」とかで、JSONから値を取り出す感じですかね。

以上

投稿日時:2022年02月22日 18:33   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

またMySQLのUDFをつくってみた。

https://github.com/shigenobu/mysql_ws_neologd_normalize

今回は有名なneologdの標準化アルゴリズムに、CR・LF・タブ・水平タブを削除するものを追加したUDFです。

(参考 neologdの標準化アルゴリズム)

https://github.com/neologd/mecab-ipadic-neologd/wiki/Regexp.ja

なぜこれを作ったかというと、すべては検索のためですね。

入力も、対象データも、そして辞書すらも、すべて一定のアルゴリズムで標準化されることで、

正しく検索が機能するためです。

今回のUDFは、対象データの部分に該当します。

入力の部分は、C#とPHPでも同様のアルゴリズムを実装していますが、

そこまで大したものではないので、特に公開はしてないです。

※さらにいえば、入力の部分では、対象データと同じアルゴリズムの形態素解析も必要かなと思います。

だいぶ、cgoでMySQLのUDFを作ってきた(現在5つをgithubに公開)のですが、

今後はrustとかに手を出して、redisモジュールなんかもつくってみたいかと(自分の中で需要がないが。。)。

今回のUDFは、すでに長いこと実践投入しているので、多分大丈夫だと思います。

いや、他のUDFが不具合ありって、いうわけじゃないですけど。

以上

投稿日時:2021年10月05日 23:40   カテゴリー:go, mariadb, mysql   [コメントがあればどうぞ]

Mysql/MariaDBのsubstring関数は、マルチバイトセーフと書いてあります。

しかしながら、日本語の長文を扱っている際、JDBCおよびADO.NETからsubstringするとなぜか、

Incorrect string value:

のエラーが出てしまいました。

文字コードや各接続はutf8mb4で統一しており、どうもおかしいと思い、原因は不明なままでしたが、

例によってcgoで作ってみました。

https://github.com/shigenobu/mysql_ws_substring

この関数を通すことで、上記エラーは発生しなくなりました。

あまりに長文だったのですが、utf8の不正なバイトシーケンスが入っていたのでは?と思っていますが、

さすがに根気がなくなり調査断念しました。(それに急ぎだったので)

まあ、なんでこんな関数つくったのかというと、mroongaを使っていた際、

mecab_sparse_tostr() failed len=xxxxx err=too long sentence

のエラーが出てしまい、自作のwordwrap関数でもこの問題は解決せず、

「あれ、ひょっとして文書が長過ぎる?」と思い、データを見てみたところ、

どうも256KiBを超えたあたりが怪しい感じがしました。

というわけで、10万文字でsubstringしてINSERTできればOKじゃね?と思い、

この関数を作ってみました。

結果として、10万文字に収めたところ、上記エラーはでなくなりました。

mroongaの公式サイトには、

GRN_MECAB_CHUNKED_TOKENIZE_ENABLED=yes

の環境変数を利用することで、解決するかもと書いてあって試してみたものの、

当該カラムの文書量が多いせいか、2時間以上たっても、FULL TEXT インデックスが構築完了しませんでした。

※文書が少ないカラムはこちらの環境変数を設定していても即座にインデックスの構築は完了しました。

一度でも登録したらエラーとなっているので、今回の関数でいかざるを得なかった感じです。

時間があったら、mecabのソースなどを調査してみようと思います。

以上

投稿日時:2021年10月01日 00:28   カテゴリー:go, mariadb, mysql   [コメントがあればどうぞ]

PHPのwordwrap関数のマルチバイト対応版をMySQLのUDFとしてつくりました。

いつものようにcgoで作ってます。

https://github.com/shigenobu/mysql_ws_wordwrap

PHPのwordwrap関数は以下のリンクとなります。

(PHPのwordwrap)

https://www.php.net/manual/ja/function.wordwrap.php

今回作ったUDFのポイントは、なるべくPHPの関数の第4引数(cut_long_words)をtrueの状態にしたものとなります。

で、このPHPのwordwrap関数の特徴として、何度やっても同じになる(微妙な表現ですが)になるってことなのかと思います。

php > $input = 'aa<br>abbbccc<br>dddee<br>e';
php > $dst1 = wordwrap($input, 3, '<br>', true);
php > echo $dst1;
aa<br>abb<br>bcc<br>c<br>ddd<br>ee<br>e
php > $dst2 = wordwrap($dst1, 3, '<br>', true);
php > echo $dst2;
aa<br>abb<br>bcc<br>c<br>ddd<br>ee<br>e
php > echo (int) $dst1 == $dst2;
1

つまり、breakワードが分割位置に含まれていたら、そこで分割せずに、breakワードを残しつつ、次以降の文字列の評価を行うものなのかと。

※なんていうアルゴリズムなのかわからなかったので、正解ではないかもしれません。

作り始めた当初は、breakワードを一旦消してから、指定文字数でbreakワードいれればいける?と思っていたのですが、

もとの文章にbreakワードが入っている場合、それを消してしまうことになるので、それはちょっと違うかなと思い、

breakワードの位置を探しつつ、構築する形にしてみました。

なんでこんな関数を作ったのかというと、mroongaを使っていた際、

mecab_sparse_tostr() failed len=xxxxx err=too long sentence

というエラーがでたので、これってmecabのストップワードがないから?と思ってしまい、

それなら強制的にwordwrapさせればいける?と思い、作りました。

実際には、文書自体が長過ぎるとエラーになるようで、この関数では解決しなかったんですが。。

本来ならストップワードを指定して分割する形も考えたのですが、急ぎで対応する必要があったので、

今後ヒマがあったら考えみようかと思います。

以上

投稿日時:2021年10月01日 00:02   カテゴリー:go, mariadb, mysql   [コメントがあればどうぞ]

またしてもcgoで作りました。

https://github.com/shigenobu/mysql_ws_split

前回作成した、ws_parse_url関数との違いは、

引数が2つあることですが、意外とこれがはまりました。

MySQLって、splitする関数がなくて(まあ、あったとしてもどうするんだという話)、

splitして、JSON配列に展開しちゃえば、そこから加工しやすくなるだろうという算段です。

以上

投稿日時:2020年05月28日 18:26   カテゴリー:go, mariadb, mysql   [コメントがあればどうぞ]

PHPのparse_url関数のような動作をするMySQLのUDFをcgoで作成しました。

https://github.com/shigenobu/mysql_ws_parse_url

(参考)PHPのparse_url関数

https://www.php.net/manual/ja/function.parse-url.php

この関数のポイントは、クエリー形式のパラメータをキー名でとれるところです。

最近はアクセスログをDBに保存して、そのまま分析に使うなどをすることもあり、MySQL側で一気に加工できれば楽だなと思いつくりました。

これ以外にも作成したものがいくつかあるので、そのうち公開しようと思います。

なお、動作確認したのはMariaDB10.4のみなので、もしお使いになる場合は自身の環境での動作確認をお願いします。

以上

投稿日時:2020年05月13日 22:45   カテゴリー:go, mariadb, mysql   [コメントがあればどうぞ]

pt-online-schema-changeをslaveに対して実行した場合に痛い目見たので、その備忘録。

 

そもそもの話、master側で不要なINDEXをslaveのみ張ってました。

OLTP側ではいらないINDEXだけど、集計とかであると嬉しいINDEXってやつですね。

そして、slaveに張っているINDEXをpt-oscでオンラインで張替えをしようとしたら、

リレーログからの取り込みが、旧テーブルに行ってしまい、

pt-oscで作成される新テーブルに入らなかったいうオチ。。。

その結果、レプリケーションの不整合が起きて、最初から構築するという目にあった。。

 

このような場合、素直にstop slaveを掛けてからDDLを実行すれば良かったです。。

とはいえ、リレーログからのデータ取り込みの際、pt-oscで実行しているとダメなことがわかったので、

もうちょっとこのあたりについて調べてみようかと思います。

 

あ、binlog_row_image=FULLだったし、MariaDB10.3だからflashback使えば、

もっと短時間で復旧できたかも、、って書いているときに思いました。。

 

以上

投稿日時:2018年10月22日 12:48   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

unionする前にwhereをするのと、

unionした後にwhereをする場合、

mysqlにおいて、差異はあるのかを確認してみた。

 

動機としては、

「unionする前にwhereをする」のと「unionした後にwhereをする」が同パフォーマンスであれば、

multisourceでviewを作っておいても十分高速に引ける可能性がある、と思ったところです。

いろいろなバージョンで試してみました。

 

予め以下のようなテーブルを作成しておき、100万件登録しておきます。

> desc users;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| email | varchar(128) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

(MySQL5.6)

mysql> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+
| id   | select_type  | table      | type | possible_keys | key         | key_len | ref   | rows   | Extra           |
+------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+
| 1    | PRIMARY      | <derived2> | ref  | <auto_key0>   | <auto_key0> | 4       | const | 10     | NULL            |
| 2    | DERIVED      | users      | ALL  | NULL          | NULL        | NULL    | NULL  | 996473 | NULL            |
| 3    | UNION        | users      | ALL  | NULL          | NULL        | NULL    | NULL  | 996473 | NULL            |
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL        | NULL    | NULL  | NULL   | Using temporary |
+------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+
4 rows in set (0.12 sec)

mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id   | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1    | PRIMARY      | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | NULL            |
| 2    | DERIVED      | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    | NULL            |
| 3    | UNION        | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    | NULL            |
| NULL | UNION RESULT | <union2,3> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
4 rows in set (0.00 sec)

 

(MySQL5.7)

mysql> explain select * from (select * from users union all select * from users) as t where id = 7542;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| 1  | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const | 10     | 100.00   | NULL  |
| 2  | DERIVED     | users      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 996473 | 100.00   | NULL  |
| 3  | UNION       | users      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 996473 | 100.00   | NULL  |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1  | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | 100.00   | NULL  |
| 2  | DERIVED     | users      | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | NULL  |
| 3  | UNION       | users      | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

 

(MySQL8)

mysql> explain select * from (select * from users union all select * from users) as t where id = 7542;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| 1  | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const | 10     | 100.00   | NULL  |
| 2  | DERIVED     | users      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 996761 | 100.00   | NULL  |
| 3  | UNION       | users      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 996761 | 100.00   | NULL  |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
3 rows in set, 1 warning (0.05 sec)

mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1  | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | 100.00   | NULL  |
| 2  | DERIVED     | users      | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | NULL  |
| 3  | UNION       | users      | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

 

(MariaDB10.1)

MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| 1    | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1994176 | Using where |
| 2    | DERIVED     | users      | ALL  | NULL          | NULL | NULL    | NULL | 997088  |             |
| 3    | UNION       | users      | ALL  | NULL          | NULL | NULL    | NULL | 997088  |             |
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
3 rows in set (0.01 sec)

MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    |       |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.00 sec)

 

(MariaDB10.2)

MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | Using where |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.001 sec)

MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    |       |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.001 sec)

 

(MariaDB10.3)

MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | Using where |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.001 sec)

MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    |       |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.000 sec)

 

これ、正直言って、動機を満たす結果を得るのは無理だと思ってたが、、、

MariaDB10.2と10.3は嬉しい結果を出してくれました。

 

以上

投稿日時:2018年09月11日 20:08   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

MariaDB10.3、MySQL8から、ALTER構文にinstantの機能が導入されました。

利用できる範囲は限られていますが、その中であれば超高速でALTERが完了します。

たぶん、メタデータだけ書き換えて、実際のデータは初回アクセス時に補完する形をとっているような感じです。

 

詳細は、公式マニュアルを参考指定だければと思います。

https://mariadb.com/kb/en/library/instant-add-column-for-innodb/

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

 

実際にデータを突っ込んでみて、どのくらいALTERが高速になるか試してみました。

以下のようなテーブルを用意して、100万件のデータを入れておきます。

> desc users;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(11)             | NO   | PRI | NULL    |       |
| email  | varchar(128)        | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+

 

(MySQL5.7)

mysql> alter table users add column money1 bigint unsigned not null;
Query OK, 0 rows affected (2.35 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

(MySQL8)

mysql> alter table users add column money1 bigint unsigned not null;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

一旦drop。

mysql> alter table users add column money1 bigint unsigned not null, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

(MariaDB10.3)

MariaDB [test]> alter table users add column money1 bigint unsigned not null;
Query OK, 0 rows affected (0.006 sec)
Records: 0 Duplicates: 0 Warnings: 0

一旦drop。

MariaDB [test]> alter table users add column money1 bigint unsigned not null, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.007 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

MySQL5.7と比較して、超高速ですね。。

なお、MariaDB10.3もMySQL8も、デフォルトで最後尾へのadd columnはINSTANTとして扱われるようです。

 

ちなみに、MySQL8のほうが、INSTANTが扱える範囲が広いようです。

以下のような条件でINSTANTが使えるようです。(※公式より引用)

  • Adding a column. This feature is referred to as “Instant ADD COLUMN”. Limitations apply. See Section 15.12.1, “Online DDL Operations”.
  • Adding or dropping a virtual column.
  • Adding or dropping a column default value.
  • Modifying the definition of an ENUM or SET column. The same restrictions apply as described above for ALGORITHM=INSTANT.
  • Changing the index type.
  • Renaming a table. The same restrictions apply as described above for ALGORITHM=INSTANT.

 

instantが発動したかどうかは、

MariaDB10.3の場合は以下のクエリーによって確認できます。

SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column';

MySQL8の場合は以下。

SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%{table_name}%';

 

条件はあるものの、MySQL8は仮想列に対するinstantにも対応しているので、結構使い勝手も広がる予感です。

とはいえ、これまでalterに非常に大きな時間・処理コストがかかっていたのが、一気に超高速になり、非常に素晴らしいと思います。

パッチを提供したTencentに感謝ですね。

 

あとは、instantで変更をかけて、その後のアクセスでどのくらいIO負荷を持っていかれるか、試してみたいな。。

 

以上

 

投稿日時:2018年09月11日 18:48   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]