MariaDB

はじめまして。サイバーエージェントでインフラエンジニアをしていますSです。
よろしくお願いします。

現在、弊社ではMySQLの使用が多いのですが、
別のオープンソース・データベースとしてMariaDBを触ってみました。

MariaDBはMySQLからフォークして立ち上げられたプロジェクトで、
インストール手順などはMySQLとほぼ一緒です。
MySQLを使用している方は何の違和感もなく導入できると思います。

その中で、今回は『MariaDB』の拡張機能や新機能を見てみました。

Storege Engine

MySQLに含まれる（MyISAM、Blackhole、CVS、Memory、and Archive storage engine）のもの以外に以下をサポートしている。

・Aria ･････････ MyISAMベースのMariaDB用ストレージエンジン
・PBXT ･･･････ PrimeBase社が開発
・XtraDB ･････ Percona社が開発、InnoDBがベースで高スペックサーバ向けにカスタマイズされている
・FederatedX ･ Federatedの代替
・OQGRAPH ･･ MariaDB5.2よりサポート開始
・SphinxSE ･･･ MariaDB5.2よりサポート開始
・IBMDB2I. ･･･ Mysql5.1.55移行では削除されたもの

Pool of Thread

以下のオプションで起動することにより、接続スレッドの同時実行を調整可能となり、短いクエリ(実行時間が短い)でいくつかのテーブル/行ロックが存在する場合パフォーマンスを上げることができる

mysqld --thread-handling=pool-of-threads --thread-pool-size=20

Table Elimination

Viewからのデータ取得時に実行計画の最適化を行う。
下記のように、table1～table4を使用し作成したtest_vにアクセスした際
必要なテーブルにしかアクセスしなくなる。

>create view test_v as
>select table_1.a,b,c,d
>from table_1
> left join table_2 on table_1.a=table_2.a
> left join table_3 on table_1.a=table_3.a
> left join table_4 on (
>   table_1.a=table_4.a and
>   table_4.e = (select max(sub.e) from table_4 sub where sub.a = table_4.a) 
> );

>explain select count(*) from test_v where b='xxxx'; 
+----+------------+---------+--------+---+-------------------+-------------+
| id | select_type| table   | type   | ･ | ref               | Extra       |
+----+------------+---------+--------+---+-------------------+-------------+
| 1  | SIMPLE     | table_1 | index  | ･ | NULL              | Using index |
| 1  | SIMPLE     | table_2 | eq_ref | ･ | test_db.table_1.a | Using where |
+----+------------+---------+--------+---+-------------------+-------------+

Query optimizer

①サブクエリの最適化

以下のオプションを使用することによりサブクエリを使用しているSQL文の実行計画が変化する

set optimizer_switch='semijoin=on,firstmatch=on,materialization=on,loosescan=on';

例）
以下の例では、TABLE_4の「DEPENDENT SUBQUERY」でのアクセスがなくなり、
table_4も「PRIMARY」でのアクセスとなる。

> explain extended select * from table_1 where a in (select a from table_4 where e='XXXXX');
+----+--------------------+---------+---+--------------------------+
| id | select_type        | table   | ･ | Extra                    |
+----+--------------------+---------+---+--------------------------+
|  1 | PRIMARY            | table_1 | ･ | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | table_4 | ･ | Using index; Using where |
+----+--------------------+---------+---+--------------------------+
2 rows in set, 1 warning (0.00 sec)

> show warnings;
+-------+------+--------------------------------------------------------------------+
| Level | Code | Message                                                            |
+-------+------+--------------------------------------------------------------------+
| Note  | 1003 | select `test_db`.`table_1`.`a` AS `a` from `test_db`.`table_1`     |
|       |      |   where <in_optimizer>(`test_db`.`table_1`.`a`,<exists>            |
|       |      |  (<primary_index_lookup>(<cache>(`test_db`.`table_1`.`a`) in       |
|       |      |  table_4 on PRIMARY where ((`test_db`.`table_4`.`e` = 'XXXXX') and |
|       |      |  (<cache>(`test_db`.`table_1`.`a`) = `test_db`.`table_4`.`a`)))))  |
+-------+------+--------------------------------------------------------------------+

> SET optimizer_switch='semijoin=on,firstmatch=on,materialization=on,loosescan=on';

> explain extended select * from table_1 where a in (select a from table_4 where e='XXXXX');
+----+-------------+---------+---+------+----------+-------------+
| id | select_type | table   | ･ | rows | filtered | Extra       |
+----+-------------+---------+---+------+----------+-------------+
|  1 | PRIMARY     | table_4 | ･ |  221 |   100.00 |             |
|  1 | PRIMARY     | table_1 | ･ |    1 |   100.00 | Using index |
+----+-------------+---------+---+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

> show warnings;
+-------+------+--------------------------------------------------------------------+
| Level | Code | Message                                                            |
+-------+------+--------------------------------------------------------------------+
| Note  | 1003 | select `test_db`.`table_1`.`a` AS `a` from `test_db`.`table_4`     |
|       |      | join `test_db`.`table_1` where ((`test_db`.`table_1`.`a` =         |
|       |      | `test_db`.`table_4`.`a`) and (`test_db`.`table_4`.`e` = 'XXXXX'))  |
+-------+------+--------------------------------------------------------------------+

②サブクエリキャッシュ

以下のオプションを使用することによりサブクエリ部分をキャッシュにのせる

set optimizer_switch='subquery_cache=on';

例）
以下の例では、実際に実行されているSQL文に<expr_cache><が追加され
キャッシュされアクセスされている。

> explain extended select * from table_1 where a in (select a from table_4 where e='XXXXX');
+----+--------------------+---------+---+--------------------------+
| id | select_type        | table   | ･ | Extra                    |
+----+--------------------+---------+---+--------------------------+
| 1  | PRIMARY            | table_1 | ･ | Using where; Using index |
| 2  | DEPENDENT SUBQUERY | table_4 | ･ | Using index; Using where |
+----+--------------------+---------+---+--------------------------+
2 rows in set, 1 warning (0.00 sec) 

> show warnings;
+-------+------+--------------------------------------------------------------------+
| Level | Code | Message                                                            |
+-------+------+--------------------------------------------------------------------+
| Note  | 1003 | select `test_db`.`table_1`.`a` AS `a` from `test_db`.`table_1`     |
|       |      |   where <in_optimizer>(`test_db`.`table_1`.`a`,<exists>            |
|       |      |  (<primary_index_lookup>(<cache>(`test_db`.`table_1`.`a`) in       |
|       |      |  table_4 on PRIMARY where ((`test_db`.`table_4`.`e` = 'XXXXX') and |
|       |      |  (<cache>(`test_db`.`table_1`.`a`) = `test_db`.`table_4`.`a`)))))  |
+-------+------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

> set optimizer_switch='subquery_cache=on';

> explain extended select * from table_1 where a in (select a from table_4 where e='XXXXX');
+----+--------------------+---------+---+--------------------------+
| id | select_type        | table   | ･ | Extra                    |
+----+--------------------+---------+---+--------------------------+
| 1  | PRIMARY            | table_1 | ･ | Using where; Using index |
| 2  | DEPENDENT SUBQUERY | table_4 | ･ | Using index; Using where |
+----+--------------------+---------+---+--------------------------+
2 rows in set, 1 warning (0.00 sec)

> show warnings;
+-------+------+--------------------------------------------------------------------+
| Level | Code | Message                                                            |
+-------+------+--------------------------------------------------------------------+
| Note  | 1003 | select `test_db`.`table_1`.`a` AS `a` from `test_db`.`table_1`     |
|       |      | where <expr_cache><`test_db`.`table_1`.`a`>(<in_optimizer>         |
|       |      | (`test_db`.`table_1`.`a`,<exists>(<primary_index_lookup>(<cache>   |
|       |      | (`test_db`.`table_1`.`a`) in table_4 on PRIMARY where              |
|       |      | ((`test_db`.`table_4`.`e` = 'XXXXX') and                           |
|       |      | (<cache>(`test_db`.`table_1`.`a`) = `test_db`.`table_4`.`a`))))))  |
+-------+------+--------------------------------------------------------------------+

③Index Condition Pushdown

通常インデックスの使用時はB-Tree indexを取得した後、tableのレコードにアクセスしデータを取得するが
『Index Condition Pushdown』機能はインデックスレコードが、それらをチェックすることができる
※B-Tree indexを取得した後、チェックを行い必要なデータのみ取得する
以下のオプションで有効になる

> set optimizer_switch='index_condition_pushdown=on';

例）実行計画が「Using index condition」と変化し、機能を使用している

> explain select * from table_4 where e between '20110901' and '20110930' and a=100;
+----+-------------+---------+-------+---+-------------+
| id | select_type | table   | type  | ･ | Extra       |
+----+-------------+---------+-------+---+-------------+
| 1  | SIMPLE      | table_4 | range | ･ | Using where |
+----+-------------+---------+-------+---+-------------+

> set optimizer_switch='index_condition_pushdown=on';

> explain select * from table_4 where e between '20110901' and '20110930' and a=100;
+----+-------------+---------+-------+---+-----------------------+
| id | select_type | table   | type  | ･ | Extra                 |
+----+-------------+---------+-------+---+-----------------------+
| 1  | SIMPLE      | table_4 | range | ･ | Using index condition |
+----+-------------+---------+-------+---+-----------------------+

④Multi Range Read

先にインデックスから読み込むべき行を特定し、ストレージエンジンにとって
都合の良い順序（ROWIDなど）で行を読み込むことによって高速化を図る
※MySQL5.6にて導入(実行計画では「Using MRR」と表示される)
　MariaDBでは「Rowid-ordered scan」や「Key-ordered scan」と表示される
以下のオプションで有効になる

> set optimizer_switch='mrr=on,mrr_sort_keys=on,mrr_cost_based=off';

例）実行計画に「Rowid-ordered scan」が追加され、機能を使用している

> explain select * from table_4 where e between '20110901' and '20110930' and a=100;
+----+-------------+---------+-------+---+-------------+
| id | select_type | table   | type  | ･ | Extra       |
+----+-------------+---------+-------+---+-------------+
| 1  | SIMPLE      | table_4 | range | ･ | Using where |
+----+-------------+---------+-------+---+-------------+
1 row in set (0.00 sec) 

> set optimizer_switch='mrr=on,mrr_sort_keys=on,mrr_cost_based=off';

> explain select * from table_4 where e between '20110901' and '20110930' and a=100;
+----+-------------+---------+-------+---+---------------------------------+
| id | select_type | table   | type  | ･ | Extra                           |
+----+-------------+---------+-------+---+---------------------------------+
| 1  | SIMPLE      | table_4 | range | ･ | Using where; Rowid-ordered scan |
+----+-------------+---------+-------+---+---------------------------------+

何点か『MariaDB』の拡張機能や新機能を見てみましたが、
『MySQL』の機能より良くなっている点が何点か確認できました。
他にも、機能はあり、今後も追加されていくと思います。
しかし、今後『MySQL』もバージョンが上がると新しい機能が付いてくるとも思います。

今後も引き続き、いろいろなオープンソース・データベースの機能を見ていき、
サービスにあったものを導入できればと考えています。

サイバーエージェント公式エンジニアブログ

CyberAgent Engineer Blog.

MariaDB