Home » Posts tagged "index"

MySQL 5.7 的 VIRTUAL column 與 index

看到 Percona 的「Using ProxySQL and VIRTUAL Columns to Solve ORM Issues」這篇後去找 VIRTUAL 的資料,發現其實以前就寫過了,而且是兩年前寫的了:「MySQL 5.7 的 JSON、Virtual Column 以及 Index」。

2NF 的規範中會禁止資料的重複性以及可推導性。以這樣的資料結構開始:

CREATE TABLE t1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birth DATE
);

與後者這樣延伸出來的資料結構:

CREATE TABLE t2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birth DATE,
    year INT,
    month INT,
    day INT
);

其中 t2 裡的 yearmonthday 都可以被 birth 推導,這就卡到 2NF... 會有 t2 這樣的資料結構通常都是因為效能而需要的設計。

像是 SELECT * FROM t1 WHERE MONTH(birth) = 12; 這樣的 SQL query,即使在 birth 加上 index 也沒用,因為查詢條件不是某個連續的區間。另外建出 month 欄位,再對 month 建立 index 後,SELECT * FROM t2 WHERE month = 12; 才能利用這組 index 提昇效能。

但後者的設計會導致兩個問題,一個是空間的增加,另外一個是資料一致性管理的成本。

空間的增加還蠻好解釋的,來自於多了 yearmonthday 這些欄位要儲存。而資料一致性管理的成本是因為你沒有強制性的方式讓 yearmonthday 的值與 birth 的內容一致,也就是資料庫內有可能會有 birth2018-01-01,但 month 裡卻是 2 之類的數字。

一致性在 PostgreSQL 有 constraint 與 function 計算可以擋下,但對應到 MySQL 的 constraint 就沒辦法用 function 判斷條件,變成需要在 MySQL 外的地方 workaround 確保一致性...

而這次標題提到的 VIRTUAL column 算是 MySQL 5.7 推出來解這個問題的想法,我們可以這樣設計資料結構:

CREATE TABLE t3 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birth DATE,
    year INT AS (YEAR(birth)) VIRTUAL,
    month INT AS (MONTH(birth)) VIRTUAL,
    day INT AS (DAY(birth)) VIRTUAL
);

然後對 month 建立 index:

ALTER TABLE t3 ADD INDEX idx__month (month);

接著塞資料進去測試:

INSERT INTO t3 (birth) VALUES ('2018-01-02');
INSERT INTO t3 (birth) VALUES ('2018-01-03');

拉資料可以看到,雖然塞資料進去時沒有指定 yearmonthday,但拉資料時會計算出來:

mysql> SELECT * FROM t3;
+----+------------+------+-------+------+
| id | birth      | year | month | day  |
+----+------------+------+-------+------+
|  1 | 2018-01-02 | 2018 |     1 |    2 |
|  2 | 2018-01-03 | 2018 |     1 |    3 |
+----+------------+------+-------+------+
2 rows in set (0.00 sec)

也可以看到 VIRTUAL column 的唯讀特性:

mysql> INSERT INTO t3 (year) VALUES (2018);
ERROR 3105 (HY000): The value specified for generated column 'year' in table 't3' is not allowed.

當你資料量夠多時,可以用 EXPLAIN 看 MySQL 的 optimizer 會使用哪個 index (太少的時候會 table scan...):

mysql> EXPLAIN SELECT * FROM t3 WHERE month = 2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: idx__month
          key: idx__month
      key_len: 5
          ref: const
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

在這個例子裡用的欄位比較簡單,但如果在更複雜的案例裡面,應該會有更多地方可以發揮 (因為可以用 function 計算,這使得很多可能性跑出來),像是 Percona 的原文是以 application 沒辦法修改程式碼的前提下,可以在 ProxySQL 與 MySQL 端做出哪些改變讓效能變好。

應該是有不少情境可以用,再多想看看好了...

Amazon Aurora (MySQL) 推出相容於 MySQL 5.7 的版本

Amazon Aurora (MySQL) 推出相容於 MySQL 5.7 的版本了:「Amazon Aurora is Compatible with MySQL 5.7」。

不過網站上的介紹還沒更新:

Amazon Aurora is a relational database service that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. The MySQL-compatible edition of Aurora delivers up to 5X the throughput of standard MySQL running on the same hardware, and is designed to be compatible with MySQL 5.6, enabling existing MySQL applications and tools to run without requiring modification.

5.7 其中一個賣點在於支援 Spatial index (透過 R-tree),不過 AWS 的版本看起來是自己用 B-tree 加上 Z-order curve 實做:「Amazon Aurora under the hood: indexing geospatial data using Z-order curves」。

我覺得看看就好啦,拿 244GB RAM 的 r3.8xlarge 跑 1GB 的 data set,當大家是傻逼嗎...

Amazon Aurora 的 MySQL-Compatible Edition 開始提供相容於 MySQL 5.7 的服務

Amazon AuroraMySQL-Compatible Edition 開始支援相容 MySQL 5.7 的服務:「Announcing Preview of Amazon Aurora with MySQL 5.7 Compatibility」。

Amazon Aurora with MySQL 5.7 compatibility offers enhancements such as JSON support, spatial indexes, generated columns and performance improvement of 5x over MySQL 5.7, and up to 10x for spatial datasets.

雖然目前還在 Preview (需要申請才能用),而且開放區域有限,但總算是有消息了:

The preview is currently available in US East (N. Virginia), US East (Ohio), US West (Oregon), and Canada (Montreal).

會一定要 5.7 的,主要的需求應該是在 spatial index 上吧... JSON 反倒還好。

InnoDB 的 MVCC 繁忙時的效能問題

Facebook 上看到 Percona 的人修正了 InnoDB 的 MVCC 在繁忙時會有 O(n^2) 的效能問題:

MySQL 官方的 bug tracking system 是「InnoDB's MVCC has O(N^2) behaviors」這個,可以看到給的重製範例是在 transaction 內大量塞 INSERT 進去後,另外一個 transaction 使用 secondary index 就會受到影響。

裡面也有提到「Secondary index updates make consistent reads do O(N^2) undo page lookups」,雖然修正了,但看起來跟當時實做的規劃有關?所以導致許多地方都是 O(n^2)...

這個 bug 感覺是批次作業的行為?因為批次作業可能會用 transaction 包起來,一次寫入萬筆資料後再 COMMIT 進去。而這個行為很有機會觸發這個 bug,導致影響到線上的服務...

機器學習與情色產業的問題

Bruce Schneier 提到了最近幾個剛好相關的議題,關於機器學習在情色產業使用時遇到的隱私議題:「Technology to Out Sex Workers」。

第一個提到的是 PornHub 用機器學習辨識演員以及各種「其他資訊」,這邊引用的報導是 TechCrunch 的「PornHub uses computer vision to ID actors, acts in its videos」:

PornHub is using machine learning algorithms to identify actors in different videos, so as to better index them.

The computer vision system can identify specific actors in scenes and even identifies various positions and… attributes.

第二個提到的是花名與真實身份連在一起的問題:

People are worried that it can really identify them, by linking their stage names to their real names.

最後是提到 Facebook 已經有能力這樣做,而且已經發生了:

Facebook somehow managed to link a sex worker's clients under her fake name to her real profile.

Her sex-work identity is not on the social network at all; for it, she uses a different email address, a different phone number, and a different name. Yet earlier this year, looking at Facebook’s “People You May Know” recommendations, Leila (a name I’m using using in place of either of the names she uses) was shocked to see some of her regular sex-work clients.

這個議題與 Mass surveillance 有點像...。

MySQL 8.0 的功能

之前陸陸續續寫了一些關於 MySQL 8.0 的新改善 (參考「MySQL 8.0 的 performance_schema 加上 index 了...」、「MySQL 8.0 將會實作「真正的」Descending Indexes」、「MySQL 8.0 對 4 bytes UTF-8 的效能改善」),官方在 RC1 的時候整理了一篇出來:「MySQL 8.0 RC1 – Highlights」。

我覺得比較值得看的是「Better Handling of Hot Rows」、「Invisible Indexes」這兩個吧,前面這點對於效能可以有些幫助 (針對某些情境不要 waiting,直接 skip lock),後面這點對於維運應該也有不錯的幫助 (像是拔掉 index 的過渡驗證階段)。

當 MySQL 8.0 真的出了之後,Percona 應該也會出文章,到時候可以看出從不同面向的觀察與想法...

ScyllaDB 2.0 要引入 Cassandra 3.0+ 的 Materialized View

最近 ScyllaDB 的網站改版了... (有種不習慣的感覺)

ScyllaDB 2.0 打算要引入 Materialized View (出自 Apache Cassandra 3.0+):「Materialized Views preview in Scylla 2.0」。

一般 Materialized View 的實做方式是另外存一份,所以你可以在上面加 Index 之類的設定讓存取速度變快...

不過 Cassandra 不是本來就以讀慢寫快為優勢嗎,要速度可以考慮用 cache 疊出來,或是其他方式,當初 Cassandra 會開發這個功能就有點... XDDD

Pinboard 放出使用的 Database Schema

Twitter 上看到 Pinboard 放出他們的 DB Schema,可以看出他怎麼設計一個 bookmark site 的:

檔案在 Pinboard Database Schema 這邊可以看到。

比較好奇的是沒有用 utf8mb4,這代表 4 bytes 的 UTF-8 資料存不進去。另外是 user_tags 這個表格的設計方式,當初在使用 MySQL 設計 tag 架構也有類似的作法...

然後有些 index key 是多餘的 XDDD

MongoDB 的 Index 種類

Percona 的人寫了一篇簡單的文章介紹 MongoDB 的 Index:「MongoDB Indexing Types: How, When and Where Should They Be Used?」。

官方文件的「Indexes」算清楚,不過有些地雷沒講,像是 Unique Indexes 只這樣說:

The unique property for an index causes MongoDB to reject duplicate values for the indexed field. Other than the unique constraint, unique indexes are functionally interchangeable with other MongoDB indexes.

但 Percona 的人就有說出「地雷」,算是不錯的補充教材 XDDD

Unique indexes work as in relational databases. They guarantee that the value doesn’t repeat and raise an error when we try to insert a duplicated value. Unique doesn’t work across shards.

話說前陣子聽到傳言抱怨,MongoDB 的文件是行銷 & 團隊寫出來的,一堆限制都沒寫在明顯的地方... XD

在 Production 上惡搞 PostgreSQL,建立一千萬筆 Index...

在「Running 10 Million PostgreSQL Indexes In Production (And Counting)」這邊看到 Heap 他們在 PostgreSQL 上惡搞的方式。

為了效能,所以在上面建立了大量的 Partial index。像是這樣的條件 (所以其實每個都不算太大):

CREATE INDEX ON events (time) WHERE type = ‘click’ AND text = ‘login’

要注意的是 MySQL 的 Partial index 其實是 prefix index,用以減少 index 的空間要求,這在 VARCHAR(255) 以及 MD5 後的欄位還蠻有幫助的。而 PostgreSQL 的 Partial index 則是指條件式,兩者不太一樣。

不過他們還是有遇到「建立 index」這件事情的效能問題 (以及 workaround 的方式)。

不太常看到這樣惡搞,看到這篇的時候在電腦前笑的頗開心的 XDDD

Archives