以下の内容はhttps://interdb.hatenablog.com/entry/20131020/1382280437より取得しました。

長年の議論に終止符 -- MySQL、MariaDB、PostgreSQLのオプティマイザ/エクゼキュータ比較

PostgreSQL MySQL MariaDB

https://mariadb.com/kb/en/optimizer-switch/にあるように、MariaDBのオプティマイザはかなり改良されている。
では、MariaDBのオプティマイザ/エクゼキュータはどの程度優秀か、４つのSELECT文の実行を通してMySQLと(ついでにPostgreSQLと)比較してみる。


(2014.12.3追記：オプティマイザについては省略してますが、こんな本がでます。)

結論を先にいえば「MySQLは検索が速い」というのは都市伝説。MariaDBはがんばってるけどPostgreSQLにはまだまだ及ばず。

＊念のため。これはベンチマークじゃないよ、オプティマイザ/エクゼキュータの機能比較です。



自分で再確認したい場合はこちらにスクリプト群と実験のやり方を簡単に書いたので参照のこと。

調査環境

同一マシンにMySQL5.6.14、MariaDB10.0.4、PostgreSQL9.3.1をインストールし、同一テーブルとデータで比較。

テーブル定義

国名country、市町村の人口city、市町村の病院数hospital、市町村の学校数schoolをテーブル定義する。
インデックスは”素直に”張ったつもり。

CREATE TABLE `country` (
  `code` int(11) NOT NULL,
  `name` text,
  `continent` text,
  PRIMARY KEY (`code`),
  KEY `continent` (`continent`(10)),
  KEY `name` (`name`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `city` (
  `code` int(11) NOT NULL,
  `country` int(11) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  PRIMARY KEY (`code`),
  KEY `population` (`population`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `hospital` (
  `code` int(11) NOT NULL,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `school` (
  `code` int(11) NOT NULL,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

データは乱数で発生させた。

mysql> SELECT count(*) FROM country;
+----------+
| count(*) |
+----------+
|      180 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM city;
+----------+
| count(*) |
+----------+
|   202133 |
+----------+
1 row in set (0.13 sec)

mysql> SELECT count(*) FROM hospital;
+----------+
| count(*) |
+----------+
|   202133 |
+----------+
1 row in set (0.15 sec)

mysql> SELECT count(*) FROM school;
+----------+
| count(*) |
+----------+
|   202133 |
+----------+
1 row in set (0.15 sec)
パラメータ設定

パラメータはデフォルト。バッファプール＝シェアードバッファは128Mを設定。

上記テーブルデータはせいぜい60M程度なので、バッファサイズが検索に影響することはない。

その他、この程度のSQLならチューニングしなければならないようなことはないはず。逆に、この程度でパラメータチューニングが必要ならちょっとマズいだろう。

innodb_buffer_pool_size = shared_buffer = 128M
比較実験の妥当性

予備実験としてテーブルcityの全件検索を実行し、MySQLとMariaDBがどちらも0.15sec程度で処理したこと、つまり同じ実行計画ならばほぼ同じ結果を出すことを確認。
よって、以下の比較も妥当な結果を返していると思われる。

実行SQL

SQL１

この例をアレンジして実行する：
https://mariadb.com/kb/en/derived-table-merge-optimization/

SELECT count(*) FROM  (SELECT * FROM city WHERE population > 3000000) AS big_city 
	WHERE big_city.country = '85';

ついでに等価なSQL1'も実験してみる。

SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
SQL2

この例をアレンジして実行する：
https://mariadb.com/kb/en/firstmatch-strategy/

SELECT count(*) FROM country WHERE country.code IN 
	(SELECT city.country FROM city WHERE city.population > 5*1000*1000)
      AND country.continent='Europe';
SQL３

SQL1に一つテーブルをJOINする。

SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
WHERE  big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
SQL４

さらに一つテーブルをJOINする。

SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, 
hospital, school, country
WHERE  big_city.country = country.code AND country.name = 'Japan' 
AND big_city.code = hospital.code AND hospital.num = 100
AND hospital.code = school.code AND school.num < 100;

ついでにサブクエリを書き換えたSQL4'も試す。

SELECT count(*) FROM city, hospital, school, country 
WHERE population > 100*1000
AND city.country = country.code AND country.name = 'Japan' 
AND city.code = hospital.code AND hospital.num = 100
AND hospital.code = school.code AND school.num < 100;

測定結果と結論

実行結果は２度目のSQLの時間を計測している。よってデータはバッファ上にあるのでI/Oアクセスは生じていない。

測定結果 MySQL5.6 MariaDB10.0 PostgreSQL9.3
SQL１ 0.55 sec 0.15 sec 0.07 sec
SQL１' 0.16 sec 0.15 sec 0.07 sec
SQL２ 0.24 sec 0.21 sec 0.10 sec
SQL３ 0.30 sec 0.15 sec 0.13 sec
SQL４ 0.68 sec 0.21 sec 0.09 sec
SQL４' 0.20 sec 0.20 sec 0.09 sec

測定結果とEXPLAINのスナップショット？は付録として下にある。

  • SQL1　& SQL1'
    • ここで”タブー”というだけあって、MySQLのサブクエリ処理のダメな点がはっきりした例。
    • PostgreSQLはSQLを書き換えて、ただの単一テーブルのシーケンシャルスキャンになっている。MariaDBも同じような感じ。SQL1とSQL1'のEXPLAINを比較参照のこと。
      • 単なるシーケンシャルスキャンでPostgreSQLがMySQLやMariaDBの２倍の速度を出してるのは、測った本人が驚いている。なぜ差がでるのか、分析中。
      • クラスタドインデックスが云々という話もあるので、試しにインデックスを張り替えてみたけど変わり無し（シーケンシャルスキャンなので当たり前か）。
      • 自分で調査したい場合はこちらにスクリプト群と実験のやり方を簡単に書いたので追試可能。
  • SQL2
    • MySQLとMariaDBのEXPLAINの結果はここと違う*1。処理時間はMariaDBが１５％程度短い。
    • PostgreSQLはサブクエリを書き換えて２つのテーブルのJOINにし、HASH結合で高速処理している。
  • SQL3
    • MySQLも(SQL1にひとつテーブルJOINしたにも関らず)SQL1より高速化している。多分WHERE条件が良かったのだろう(hospital.num=10固定なので、絞り込みできたようだ。)。しかしMariaDBとPostgreSQLには遠く及ばない。
  • SQL4 & SQL4'
    • サブクエリ＋テーブル３つのJOINとなるとMySQLは絶望的。
    • MariaDBはサブクエリをうまくクリアして、MySQLよりも高速に処理を行っている。
    • PostgreSQLはこういう複雑なものほど速い。

＊再度、念のため。これはベンチマークじゃないよ、オプティマイザ/エクゼキュータの機能比較です。


MySQLは評判通り、サブクエリもJOINも弱い。ただしSQL3のようにWHERE句の条件がよければサブクエリの弱点を隠蔽できる場合もある模様。


MariaDBはかなり頑張っている（特にサブクエリの処理）ように見える。


PostgreSQLはこの程度のSQLならなんの問題もなく高速に実行できる。サブクエリの書き換えも優秀だし、JOINもネステッドループ以外にHASHとMergeSortの計３種類ある。


個人的には全般的な性能と頑健性からPostgreSQLを薦めるけれども、せいぜい２、３個のテーブルJOINをスレッドプールで高速に捌くのであれば、MariaDBでもいいんじゃないかと思う*2。




付録：実行結果とEXPLAINのまとめ

SQL１ & SQL1'
  • MySQL5.6
mysql> SELECT count(*) FROM  (SELECT * FROM city WHERE population > 3000000) 
    -> AS big_city WHERE   big_city.country = '85';
+----------+
| count(*) |
+----------+
|     1007 |
+----------+
1 row in set (0.55 sec)

mysql> EXPLAIN SELECT count(*) FROM  (SELECT * FROM city WHERE population > 3000000) 
    -> AS big_city WHERE   big_city.country = '85';
+----+-------------+------------+------+---------------+-------------+---------+-------+--------+-------------+
| id | select_type | table      | type | possible_keys | key         | key_len | ref   | rows   | Extra       |
+----+-------------+------------+------+---------------+-------------+---------+-------+--------+-------------+
|  1 | PRIMARY     | <derived2> | ref  | <auto_key0>   | <auto_key0> | 5       | const |     10 | NULL        |
|  2 | DERIVED     | city       | ALL  | population    | NULL        | NULL    | NULL  | 192815 | Using where |
+----+-------------+------------+------+---------------+-------------+---------+-------+--------+-------------+
2 rows in set (0.00 sec)


mysql> SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
+----------+
| count(*) |
+----------+
|     1007 |
+----------+
1 row in set (0.16 sec)

mysql> EXPLAIN SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | city  | ALL  | population    | NULL | NULL    | NULL | 192815 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
  • MariaDB10.0
MariaDB [big]> SELECT count(*) FROM  (SELECT * FROM city WHERE population > 3000000) 
    -> AS big_city WHERE   big_city.country = '85';
+----------+
| count(*) |
+----------+
|     1007 |
+----------+
1 row in set (0.15 sec)

MariaDB [big]> EXPLAIN SELECT count(*) FROM  (SELECT * FROM city WHERE population > 3000000) 
    -> AS big_city WHERE   big_city.country = '85';
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | city  | ALL  | population    | NULL | NULL    | NULL | 202407 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

MariaDB [big]> SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
+----------+
| count(*) |
+----------+
|     1007 |
+----------+
1 row in set (0.15 sec)

MariaDB [big]> EXPLAIN SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | city  | ALL  | population    | NULL | NULL    | NULL | 186544 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)
  • PostgreSQL9.3
big=# SELECT count(*) FROM  (SELECT * FROM city WHERE population > 3000000) 
big-#  AS big_city WHERE   big_city.country = '85';
 count 
-------
  1007
(1 row)

Time: 71.316 ms
big=# EXPLAIN SELECT count(*) FROM  (SELECT * FROM city WHERE population > 3000000) AS big_city WHERE   big_city.country = '85';
                          QUERY PLAN                           
---------------------------------------------------------------
 Aggregate  (cost=4025.42..4025.43 rows=1 width=0)
   ->  Seq Scan on city  (cost=0.00..4022.98 rows=976 width=0)
         Filter: ((population > 3000000) AND (country = 85))
(3 rows)


Time: 71.672 ms
big=# SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
 count 
-------
  1007
(1 row)

Time: 71.765 ms
big=# EXPLAIN SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
                          QUERY PLAN                           
---------------------------------------------------------------
 Aggregate  (cost=4025.42..4025.43 rows=1 width=0)
   ->  Seq Scan on city  (cost=0.00..4022.98 rows=976 width=0)
         Filter: ((population > 3000000) AND (country = 85))
(3 rows)

SQL2

  • MySQL5.6
mysql> SELECT count(*) FROM country WHERE country.code IN  
    -> (SELECT city.country  FROM city WHERE city.population > 5*1000*1000)       AND country.continent='Europe';
+----------+
| count(*) |
+----------+
|       39 |
+----------+
1 row in set (0.24 sec)

mysql> EXPLAIN SELECT count(*) FROM country WHERE country.code IN 
    -> (SELECT city.country FROM city WHERE city.population > 5*1000*1000)
    ->       AND country.continent='Europe';
+----+--------------+-------------+--------+-------------------+------------+---------+------------------+--------+------------------------------------+
| id | select_type  | table       | type   | possible_keys     | key        | key_len | ref              | rows   | Extra                              |
+----+--------------+-------------+--------+-------------------+------------+---------+------------------+--------+------------------------------------+
|  1 | SIMPLE       | country     | ref    | PRIMARY,continent | continent  | 33      | const            |     39 | Using index condition; Using where |
|  1 | SIMPLE       | <subquery2> | eq_ref | <auto_key>        | <auto_key> | 5       | big.country.code |      1 | NULL                               |
|  2 | MATERIALIZED | city        | ALL    | population        | NULL       | NULL    | NULL             | 192815 | Using where                        |
+----+--------------+-------------+--------+-------------------+------------+---------+------------------+--------+------------------------------------+
3 rows in set (0.00 sec)
  • MariaDB10.0
MariaDB [big]> SELECT count(*) FROM country WHERE country.code 
    -> IN  (SELECT city.country FROM city WHERE city.population > 5*1000*1000)   AND country.continent='Europe';
+----------+
| count(*) |
+----------+
|       39 |
+----------+
1 row in set (0.21 sec)

MariaDB [big]> EXPLAIN SELECT count(*) FROM country WHERE country.code IN 
    -> (SELECT city.country FROM city WHERE city.population > 5*1000*1000)
    ->       AND country.continent='Europe';
+------+--------------+-------------+--------+-------------------+--------------+---------+-------+--------+-------------+
| id   | select_type  | table       | type   | possible_keys     | key          | key_len | ref   | rows   | Extra       |
+------+--------------+-------------+--------+-------------------+--------------+---------+-------+--------+-------------+
|    1 | PRIMARY      | country     | ref    | PRIMARY,continent | continent    | 33      | const |     39 | Using where |
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key      | distinct_key | 4       | func  |      1 |             |
|    2 | MATERIALIZED | city        | ALL    | population        | NULL         | NULL    | NULL  | 202407 | Using where |
+------+--------------+-------------+--------+-------------------+--------------+---------+-------+--------+-------------+
3 rows in set (0.00 sec)
  • PostgreSQL9.3
big=# SELECT count(*) FROM country WHERE country.code IN 
(SELECT city.country FROM city WHERE city.population > 5*1000*1000)
      AND country.continent='Europe';
 count 
-------
    39
(1 row)

Time: 95.607 ms
big=# EXPLAIN SELECT count(*) FROM country WHERE country.code IN 
(SELECT city.country FROM city WHERE city.population > 5*1000*1000)
      AND country.continent='Europe';
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Aggregate  (cost=3816.62..3816.63 rows=1 width=0)
   ->  Hash Join  (cost=3814.85..3816.52 rows=39 width=0)
         Hash Cond: (city.country = country.code)
         ->  HashAggregate  (cost=3810.11..3811.16 rows=105 width=4)
               ->  Seq Scan on city  (cost=0.00..3517.65 rows=116985 width=4)
                     Filter: (population > 5000000)
         ->  Hash  (cost=4.25..4.25 rows=39 width=4)
               ->  Seq Scan on country  (cost=0.00..4.25 rows=39 width=4)
                     Filter: (continent = 'Europe'::text)
(9 rows)

SQL3

  • MySQL5.6
mysql> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) 
    -> AS big_city, hospital WHERE  big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.30 sec)

mysql> EXPLAIN 
    -> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
    -> WHERE  big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
+----+-------------+------------+--------+---------------+---------+---------+---------------+--------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref           | rows   | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+---------------+--------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL          |  96407 | Using where |
|  1 | PRIMARY     | hospital   | eq_ref | PRIMARY       | PRIMARY | 4       | big_city.code |      1 | Using where |
|  2 | DERIVED     | city       | ALL    | population    | NULL    | NULL    | NULL          | 192815 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+---------------+--------+-------------+
3 rows in set (0.00 sec)
  • MariaDB10.0
MariaDB [big]> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
    -> WHERE  big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.15 sec)

MariaDB [big]> EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
    -> WHERE  big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------+
| id   | select_type | table    | type   | possible_keys      | key     | key_len | ref           | rows   | Extra       |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------+
|    1 | SIMPLE      | city     | ALL    | PRIMARY,population | NULL    | NULL    | NULL          | 202407 | Using where |
|    1 | SIMPLE      | hospital | eq_ref | PRIMARY            | PRIMARY | 4       | big.city.code |      1 | Using where |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------+
2 rows in set (0.00 sec)
  • PostgreSQL9.3
big=# SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
WHERE  big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
 count 
-------
    12
(1 row)

Time: 129.348 ms
big=# EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
big-# WHERE  big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Aggregate  (cost=7487.46..7487.47 rows=1 width=0)
   ->  Hash Join  (cost=3455.58..7487.41 rows=19 width=0)
         Hash Cond: (city.code = hospital.code)
         ->  Seq Scan on city  (cost=0.00..4022.98 rows=1387 width=4)
               Filter: ((population > 100000) AND (country = 85))
         ->  Hash  (cost=3421.64..3421.64 rows=2715 width=4)
               ->  Seq Scan on hospital  (cost=0.00..3421.64 rows=2715 width=4)
                     Filter: (num = 10)
(8 rows)
SQL４ & SQL4'
  • MySQL5.6
mysql> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, 
    -> country WHERE  big_city.country = country.code AND country.name = 'Japan'  AND big_city.code = hospital.code 
    -> AND hospital.num = 100 AND hospital.code = school.code AND school.num < 100;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.68 sec)

mysql> EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country
    -> WHERE  big_city.country = country.code AND country.name = 'Japan' 
    -> AND big_city.code = hospital.code AND hospital.num = 100
    -> AND hospital.code = school.code AND school.num < 100;
+----+-------------+------------+--------+---------------+-------------+---------+------------------+--------+-------------+
| id | select_type | table      | type   | possible_keys | key         | key_len | ref              | rows   | Extra       |
+----+-------------+------------+--------+---------------+-------------+---------+------------------+--------+-------------+
|  1 | PRIMARY     | country    | ref    | PRIMARY,name  | name        | 33      | const            |      1 | Using where |
|  1 | PRIMARY     | <derived2> | ref    | <auto_key3>   | <auto_key3> | 5       | big.country.code |    773 | NULL        |
|  1 | PRIMARY     | hospital   | eq_ref | PRIMARY       | PRIMARY     | 4       | big_city.code    |      1 | Using where |
|  1 | PRIMARY     | school     | eq_ref | PRIMARY       | PRIMARY     | 4       | big_city.code    |      1 | Using where |
|  2 | DERIVED     | city       | ALL    | population    | NULL        | NULL    | NULL             | 192815 | Using where |
+----+-------------+------------+--------+---------------+-------------+---------+------------------+--------+-------------+
5 rows in set (0.00 sec)


mysql> SELECT count(*) FROM city, hospital, school, country 
    -> WHERE population > 100*1000
    -> AND city.country = country.code AND country.name = 'Japan' 
    -> AND city.code = hospital.code AND hospital.num = 100
    -> AND hospital.code = school.code AND school.num < 100;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.20 sec)

mysql> EXPLAIN SELECT count(*) FROM city, hospital, school, country 
    -> WHERE population > 100*1000
    -> AND city.country = country.code AND country.name = 'Japan' 
    -> AND city.code = hospital.code AND hospital.num = 100
    -> AND hospital.code = school.code AND school.num < 100;
+----+-------------+----------+--------+--------------------+---------+---------+---------------+--------+----------------------------------------------------+
| id | select_type | table    | type   | possible_keys      | key     | key_len | ref           | rows   | Extra                                              |
+----+-------------+----------+--------+--------------------+---------+---------+---------------+--------+----------------------------------------------------+
|  1 | SIMPLE      | country  | ref    | PRIMARY,name       | name    | 33      | const         |      1 | Using where                                        |
|  1 | SIMPLE      | city     | ALL    | PRIMARY,population | NULL    | NULL    | NULL          | 192815 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | hospital | eq_ref | PRIMARY            | PRIMARY | 4       | big.city.code |      1 | Using where                                        |
|  1 | SIMPLE      | school   | eq_ref | PRIMARY            | PRIMARY | 4       | big.city.code |      1 | Using where                                        |
+----+-------------+----------+--------+--------------------+---------+---------+---------------+--------+----------------------------------------------------+
4 rows in set (0.00 sec)
  • MariaDB10.0
MariaDB [big]> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country 
    -> WHERE  big_city.country = country.code AND country.name = 'Japan'  AND big_city.code = hospital.code 
    -> AND hospital.num = 100 AND hospital.code = school.code AND school.num < 100;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.21 sec)

MariaDB [big]> EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country
    -> WHERE  big_city.country = country.code AND country.name = 'Japan' 
    -> AND big_city.code = hospital.code AND hospital.num = 100
    -> AND hospital.code = school.code AND school.num < 100;
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
| id   | select_type | table    | type   | possible_keys      | key     | key_len | ref           | rows   | Extra                                           |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
|    1 | SIMPLE      | country  | ref    | PRIMARY,name       | name    | 33      | const         |      1 | Using where                                     |
|    1 | SIMPLE      | city     | ALL    | PRIMARY,population | NULL    | NULL    | NULL          | 202407 | Using where; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | hospital | eq_ref | PRIMARY            | PRIMARY | 4       | big.city.code |      1 | Using where                                     |
|    1 | SIMPLE      | school   | eq_ref | PRIMARY            | PRIMARY | 4       | big.city.code |      1 | Using where                                     |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [big]> SELECT count(*) FROM city, hospital, school, country 
    -> WHERE population > 100*1000
    -> AND city.country = country.code AND country.name = 'Japan' 
    -> AND city.code = hospital.code AND hospital.num = 100
    -> AND hospital.code = school.code AND school.num < 100;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.20 sec)

MariaDB [big]> EXPLAIN SELECT count(*) FROM city, hospital, school, country 
    -> WHERE population > 100*1000
    -> AND city.country = country.code AND country.name = 'Japan' 
    -> AND city.code = hospital.code AND hospital.num = 100
    -> AND hospital.code = school.code AND school.num < 100;
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
| id   | select_type | table    | type   | possible_keys      | key     | key_len | ref           | rows   | Extra                                           |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
|    1 | SIMPLE      | country  | ref    | PRIMARY,name       | name    | 33      | const         |      1 | Using where                                     |
|    1 | SIMPLE      | city     | ALL    | PRIMARY,population | NULL    | NULL    | NULL          | 186544 | Using where; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | hospital | eq_ref | PRIMARY            | PRIMARY | 4       | big.city.code |      1 | Using where                                     |
|    1 | SIMPLE      | school   | eq_ref | PRIMARY            | PRIMARY | 4       | big.city.code |      1 | Using where                                     |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
4 rows in set (0.00 sec)
  • PostgreSQL9.3
big=# SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country
WHERE  big_city.country = country.code AND country.name = 'Japan' 
AND big_city.code = hospital.code AND hospital.num = 100
AND hospital.code = school.code AND school.num < 100;
 count 
-------
     1
(1 row)

Time: 89.733 ms
big=# EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country
big-# WHERE  big_city.country = country.code AND country.name = 'Japan' 
big-# AND big_city.code = hospital.code AND hospital.num = 100
big-# AND hospital.code = school.code AND school.num < 100;
                                           QUERY PLAN                                    
       
-----------------------------------------------------------------------------------------
-------
 Aggregate  (cost=4793.19..4793.20 rows=1 width=0)
   ->  Nested Loop  (cost=5.10..4793.19 rows=2 width=0)
         ->  Nested Loop  (cost=4.68..4791.75 rows=3 width=8)
               ->  Hash Join  (cost=4.26..4268.71 rows=1090 width=4)
                     Hash Cond: (city.country = country.code)
                     ->  Seq Scan on city  (cost=0.00..3517.65 rows=196239 width=8)
                           Filter: (population > 100000)
                     ->  Hash  (cost=4.25..4.25 rows=1 width=4)
                           ->  Seq Scan on country  (cost=0.00..4.25 rows=1 width=4)
                                 Filter: (name = 'Japan'::text)
               ->  Index Scan using hospital_pkey on hospital  (cost=0.42..0.47 rows=1 wi
dth=4)
                     Index Cond: (code = city.code)
                     Filter: (num = 100)
         ->  Index Scan using school_pkey on school  (cost=0.42..0.47 rows=1 width=4)
               Index Cond: (code = city.code)
               Filter: (num < 100)
(16 rows)

big=# SELECT count(*) FROM city, hospital, school, country 
WHERE population > 100*1000
AND city.country = country.code AND country.name = 'Japan' 
AND city.code = hospital.code AND hospital.num = 100
AND hospital.code = school.code AND school.num < 100;
 count 
-------
     1
(1 row)

Time: 91.021 ms
big=# EXPLAIN SELECT count(*) FROM city, hospital, school, country 
WHERE population > 100*1000
AND city.country = country.code AND country.name = 'Japan' 
AND city.code = hospital.code AND hospital.num = 100
AND hospital.code = school.code AND school.num < 100;
                                           QUERY PLAN                                         
  
----------------------------------------------------------------------------------------------
--
 Aggregate  (cost=4793.19..4793.20 rows=1 width=0)
   ->  Nested Loop  (cost=5.10..4793.19 rows=2 width=0)
         ->  Nested Loop  (cost=4.68..4791.75 rows=3 width=8)
               ->  Hash Join  (cost=4.26..4268.71 rows=1090 width=4)
                     Hash Cond: (city.country = country.code)
                     ->  Seq Scan on city  (cost=0.00..3517.65 rows=196239 width=8)
                           Filter: (population > 100000)
                     ->  Hash  (cost=4.25..4.25 rows=1 width=4)
                           ->  Seq Scan on country  (cost=0.00..4.25 rows=1 width=4)
                                 Filter: (name = 'Japan'::text)
               ->  Index Scan using hospital_pkey on hospital  (cost=0.42..0.47 rows=1 width=4
)
                     Index Cond: (code = city.code)
                     Filter: (num = 100)
         ->  Index Scan using school_pkey on school  (cost=0.42..0.47 rows=1 width=4)
               Index Cond: (code = city.code)
               Filter: (num < 100)
(16 rows)

*1:サブクエリをマテリアライズドしている模様。だからPostgreSQLと比較して遅いのかも

*2:今回はシングルトランザクションだけれども、WEB系システムのように数100コネクションで連続的かつ並行的にトランザクションが走る場合は、MariaDBのスレッドプールがとても効果的なはず。PostgreSQLはプロセスモデルだからコネクションが多いと検索性能でMariaDBに逆転される可能性がある。本当はベンチマークしてみたいところである。



以上の内容はhttps://interdb.hatenablog.com/entry/20131020/1382280437より取得しました。
このページはhttp://font.textar.tv/のウェブフォントを使用してます
不具合報告/要望等はこちらへお願いします。
モバイルやる夫Viewer Ver0.14