多維資料查詢效率分析（2）

上次我們分析了在附加屬性表這樣表結構設計下的PostgreSQL查詢效率。由於PostgreSQL眾所周知的所謂“效能”問題，所以有必要再用使用MyISAM引擎的MySQL再來實驗一遍。在我們詳細分析了兩種常見的開來源資料庫之後，話題將會進一步引申到按行儲存的資料庫結構以及索引對於查詢效率的影響。以下實驗中的MySQL為MariaDB發行版本。還是從建表開始：

MariaDB [veil]> show create table cc2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| cc2   | CREATE TABLE `cc2` (
  `contact_id` int(11) NOT NULL,
  `cad_id` int(11) NOT NULL,
  `value` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [veil]> select count(*) from cc2;
+----------+
| count(*) |
+----------+
|  5904385 |
+----------+
1 row in set (0.00 sec)

MySQL在表上緩衝了count(*)的結果，所以查詢是不需要花費時間的。再來建立必要的索引：

MariaDB [veil]> create index contact_id_idx on cc2(contact_id);
Query OK, 5904385 rows affected (3.11 sec)             
Records: 5904385  Duplicates: 0  Warnings: 0

MariaDB [veil]> create index cad_id_value_idx on cc2(cad_id, value);
Query OK, 5904385 rows affected (8.17 sec)             
Records: 5904385  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from cc2 where cad_id = 101 and value = 5;
+----------+
| count(*) |
+----------+
|   998839 |
+----------+
1 row in set (0.18 sec)

我們可以看到，這速度那是剛剛的。比較PostgreSQL中的相同的查詢，速度要快上4倍。但是：

MariaDB [veil]> select count(contact_id) from cc2 where cad_id = 101 and value = 5;
+-------------------+
| count(contact_id) |
+-------------------+
|            998839 |
+-------------------+
1 row in set (0.41 sec)

這個查詢比前一個慢，是因為count(*)是數返回行的rowid，而count(contact_id)是數真正的contact_id列，而這個列的值是不包含在cad_id_value_idx中的，如果建立更多的索引的話，速度就要更快一些了：

MariaDB [veil]> create index cad_id_value_contact_id_idx on cc2(cad_id, value, contact_id);
Query OK, 5904385 rows affected (13.37 sec)            
Records: 5904385  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(contact_id) from cc2 where cad_id = 101 and value = 5;
+-------------------+
| count(contact_id) |
+-------------------+
|            998839 |
+-------------------+
1 row in set (0.21 sec)

這個在MySQL中被稱作covering index。大概PostgreSQL 9.2還沒發布的index only query也是這個意思吧，我猜的。
現在，讓我們來看看MySQL是否能夠在兩個條件的情況下表現得比PostgreSQL強。首先嘗試INTERSECT吧：

MariaDB [veil]> select count(*) from (select contact_id from cc as a1 where a1.cad_id = 101 and a1.value = 5 intersect select contact_id from cc as a2 where a2.cad_id = 102 and a2.value = 7) as temp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'intersect select contact_id from cc as a2 where a2.cad_id = 102 and a2.value = 7' at line 1

我靠，MySQL居然不支援INTERSECT。由於我們知道IN + SUBQUERY肯定是更慢的，所以就只剩INNER JOIN這一種寫法了。

MariaDB [veil]> select count(*) from cc as a1, cc as a2 where a1.contact_id = a2.contact_id and a1.cad_id = 101 and a1.value = 5 and a2.cad_id = 102 and a2.value = 7;
+----------+
| count(*) |
+----------+
|   164788 |
+----------+
1 row in set (6.56 sec)

MariaDB [veil]> select count(a1.contact_id) from cc as a1, cc as a2 where a1.contact_id = a2.contact_id and a1.cad_id = 101 and a1.value = 5 and a2.cad_id = 102 and a2.value = 7;
+----------------------+
| count(a1.contact_id) |
+----------------------+
|               164788 |
+----------------------+
1 row in set (6.67 sec)

你沒看錯，這結果就是這麼慘不忍睹。它甚至比PostgreSQL用IN + SUBQUERY實現得還要慢。看看到底是啥狀況吧：

MariaDB [veil]> explain extended select count(a1.contact_id) from cc as a1, cc as a2 where a1.contact_id = a2.contact_id and a1.cad_id = 101 and a1.value = 5 and a2.cad_id = 102 and a2.value = 7;
+----+-------------+-------+------+------------------------------------+-------------------------+---------+--------------------------------+--------+----------+--------------------------+
| id | select_type | table | type | possible_keys                      | key                     | key_len | ref                            | rows   | filtered | Extra                    |
+----+-------------+-------+------+------------------------------------+-------------------------+---------+--------------------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | a1    | ref  | contact_id,cad_id_value_contact_id | cad_id_value_contact_id | 10      | const,const                    | 808542 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | a2    | ref  | contact_id,cad_id_value_contact_id | cad_id_value_contact_id | 15      | const,const,veil.a1.contact_id |      1 |   100.00 | Using index              |
+----+-------------+-------+------+------------------------------------+-------------------------+---------+--------------------------------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

雖然不像PostgreSQL的分析結果那般詳細。但是至少還是可以看出，索引確實是被利用上了。所以我感覺，MySQL真的不擅長複雜的join。這就是問題了，join處理不好，多條件的情況就沒法支援好了。

對於我們的第一次嘗試，可以總結出以下幾點：

1. 基於磁碟的方案行不通，必須放在記憶體中
2. 無條件：PostgreSQL慢，MySQL慢
3. 一個條件：PostgreSQL快，MySQL更快（拜covering index所賜）
4. 兩個條件：PostgreSQL慢，MySQL更慢。INNER JOIN稍慢於INTERSECT。
5. 三個條件：PostgreSQL快於MySQL，而且INNER JOIN要快於INTERSECT。

總體來說，要把請求回應時間控制在一秒之內那難度是相當的大啊。所以建模思路必須變化。那我們再來看看把行變列會不會好一些吧：

MariaDB [veil]> show create table spike;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| spike | CREATE TABLE `spike` (
  `contact_id` int(11) NOT NULL,
  `a1` int(11) DEFAULT NULL,
  `a2` int(11) DEFAULT NULL,
  `a3` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [veil]> select * from spike limit 10;
+------------+------+------+------+
| contact_id | a1   | a2   | a3   |
+------------+------+------+------+
|     800001 |    4 | NULL | NULL |
|     800003 |    5 |    7 |    1 |
|     800004 |    3 | NULL | NULL |
|     800005 |    3 | NULL | NULL |
|     800006 |    5 | NULL | NULL |
|     800007 | NULL |    6 | NULL |
|     800008 |    5 | NULL | NULL |
|     800009 |    4 |    8 | NULL |
|     800011 | NULL |    7 | NULL |
|     800012 |    3 |    6 |    1 |
+------------+------+------+------+
10 rows in set (0.01 sec)

列a1來自於cad_id=101的值，a2來自於cad_id=102的值，a3來自於cad_id=6的值。

首先試試一個條件的效能吧：

MariaDB [veil]> select count(*) from spike where a1 = 5;
+----------+
| count(*) |
+----------+
|   998839 |
+----------+
1 row in set (0.26 sec)

速度不錯，即便我們還沒給a1加索引呢。這是因為PostgreSQL和MySQL這樣的按行儲存的資料庫，在需要做sequential scan的時候，效能很大程度上決定於行的大小。如果我們有100行，這效能就肯定不會這麼好了。不信的話可以試試：

MariaDB [veil]> alter table spike add column dummy1 TEXT;
Query OK, 4074980 rows affected (1.28 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5;
+----------+
| count(*) |
+----------+
|   998839 |
+----------+
1 row in set (0.64 sec)

不出意料，僅僅只是添加了一個TEXT欄位，查詢就慢了這麼多。為了克服按行儲存的查詢速度隨行的列數增加和越來越慢的問題，索引變得特別必要：

MariaDB [veil]> create index a1_idx on spike(a1);        
Query OK, 4074980 rows affected (4.97 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5;
+----------+
| count(*) |
+----------+
|   998839 |
+----------+
1 row in set (0.22 sec)

我們可以看到，查詢時間又變得正常了。因為a1_idx是一個covering index，所以count無需真的讀取行，只需要查詢索引就可以知道有多少行了。而且MYISAM又不像PostgreSQL那樣，因為MVCC還需要對原始行更新hint bit，所以整個查詢就和行的大小一點關係都沒有了。

MariaDB [veil]> alter table spike add column dummy2 TEXT;
Query OK, 4074980 rows affected (5.00 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> alter table spike add column dummy3 TEXT;
Query OK, 4074980 rows affected (5.30 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5;
+----------+
| count(*) |
+----------+
|   998839 |
+----------+
1 row in set (0.23 sec)

對吧，沒騙你吧。
那麼兩個條件如何？先把表結構回到正常的狀態，把索引都去掉：

MariaDB [veil]> alter table spike drop column dummy1;
Query OK, 4074980 rows affected (5.19 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> alter table spike drop column dummy2;
Query OK, 4074980 rows affected (4.98 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> alter table spike drop column dummy3;
Query OK, 4074980 rows affected (3.41 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> alter table spike drop index a1_idx;
Query OK, 4074980 rows affected (0.46 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> show create table spike;             
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| spike | CREATE TABLE `spike` (
  `contact_id` int(11) NOT NULL,
  `a1` int(11) DEFAULT NULL,
  `a2` int(11) DEFAULT NULL,
  `a3` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

接下來用兩個條件做查詢：

MariaDB [veil]> select count(*) from spike where a1 = 5 and a2 = 7;
+----------+
| count(*) |
+----------+
|   164788 |
+----------+
1 row in set (0.30 sec)

速度不錯嘛！給a1建立一個索引，應該速度會更快吧：

MariaDB [veil]> create index a1_idx on spike(a1);                  
Query OK, 4074980 rows affected (3.06 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5 and a2 = 7;
+----------+
| count(*) |
+----------+
|   164788 |
+----------+
1 row in set (0.45 sec)

尼瑪居然更慢了……原因不是索引沒有被使用，而是因為用了索引所以更慢了

MariaDB [veil]> explain select count(*) from spike where a1 = 5 and a2 = 7;
+----+-------------+-------+------+---------------+--------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows    | Extra       |
+----+-------------+-------+------+---------------+--------+---------+-------+---------+-------------+
|  1 | SIMPLE      | spike | ref  | a1_idx        | a1_idx | 5       | const | 1420338 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+---------+-------------+
1 row in set (0.01 sec)

a1索引確實被使用了，但是a2並沒有在同一個索引之中。所以這不是一個covering index，查詢就不能僅僅在索引中查詢了，還要回到原始的行儲存中去檢查a2=7。

MariaDB [veil]> create index a2_idx on spike(a2);
Query OK, 4074980 rows affected (5.71 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5 and a2 = 7;
+----------+
| count(*) |
+----------+
|   164788 |
+----------+
1 row in set (0.40 sec)

給a2_idx建立了一個單獨的索引也於事無補，因為在掃描a1_idx的時候，還是沒有a2。查詢仍然需要回到原始的行儲存中去獲得a2。

MariaDB [veil]> create index a1_a2_idx on spike(a1, a2);
Query OK, 4074980 rows affected (9.47 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5 and a2 = 7;
+----------+
| count(*) |
+----------+
|   164788 |
+----------+
1 row in set (0.06 sec)

現在，我們才看到什麼叫做速度！它是完全基於索引的。

MariaDB [veil]> alter table spike add column dummy1 TEXT;
Query OK, 4074980 rows affected (12.33 sec)            
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> alter table spike add column dummy2 TEXT;
Query OK, 4074980 rows affected (12.87 sec)            
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> alter table spike add column dummy3 TEXT;
Query OK, 4074980 rows affected (13.15 sec)            
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5 and a2 = 7;
+----------+
| count(*) |
+----------+
|   164788 |
+----------+
1 row in set (0.06 sec)

把行的大小增大也沒有關係，因為壓根就不會去讀真正的行。但是一旦把covering index移除，情況就回到原來那個德行了：

MariaDB [veil]> alter table spike drop index a1_a2_idx;
Query OK, 4074980 rows affected (8.74 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5 and a2 = 7;
+----------+
| count(*) |
+----------+
|   164788 |
+----------+
1 row in set (0.62 sec)

MariaDB [veil]> alter table spike drop index a1_idx;
Query OK, 4074980 rows affected (5.31 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> alter table spike drop index a2_idx;
Query OK, 4074980 rows affected (1.93 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5 and a2 = 7;
+----------+
| count(*) |
+----------+
|   164788 |
+----------+
1 row in set (0.72 sec)

所以嘛，wide table的表設計確實會是一個問題。一旦索引罩不住了，情況就糟糕了。

如果covering index這麼棒的話，那為何不給所有的查詢都建立covering index呢？這樣問題可不就解決了麼。

真是牛逼的想法，試試吧：

MariaDB [veil]> create index a1_a2_a3_idx on spike(a1, a2, a3);
Query OK, 4074980 rows affected (7.03 sec)             
Records: 4074980  Duplicates: 0  Warnings: 0

MariaDB [veil]> select count(*) from spike where a1 = 5 and a2 = 7 and a3 = 1;
+----------+
| count(*) |
+----------+
|     6748 |
+----------+
1 row in set (0.01 sec)

真的耶！covering index又立功了。似乎一旦建立了這樣的一個索引，所有的查詢問題都解決了。

MariaDB [veil]> select count(*) from spike where a1 = 5;                      
+----------+
| count(*) |
+----------+
|   998839 |
+----------+
1 row in set (0.28 sec)

看見沒，它確實管用！

MariaDB [veil]> select count(*) from spike where a3 = 1;
+----------+
| count(*) |
+----------+
|   207228 |
+----------+
1 row in set (1.06 sec)

我靠，高興太早了。栽了。原因是索引的順序是a1，a2，a3，對於a1的查詢因為和索引建立的順序相符，所以用的上。類似的a1，a2的查詢也能用得上。但是a3和建立順序不符，就無法利用上。如果要真正的達到覆蓋所有的情況，那麼將是一個排列組合的結果。對於十幾列的表來說，這個數字會大得讓系統無法承受。

總結起來就是，無論是磁碟還是記憶體，無論是附加的屬性工作表，還是把屬性建模成列，無論是PostgreSQL還是MySQL都沒有辦法提供Responsive Query。傳統的按行儲存的資料庫無法滿足多維資料的高速查詢需求。