關鍵字:視覺化運維平臺、如何自動備份MySQL資料庫、資料訂閱
前面介紹了 B 樹索引、雜湊索引,接下來看看 MySQL 全文索引。
在講全文索引之前,可以看看如下很常見的一類 SQL 語句:
select count(*) from fx where s1 like ‘%cluster%’
這條語句從表 fx 中檢索欄位 s1,過濾條件為 ‘%cluster%’,這樣的模糊查詢語句效能很差,即使在欄位 s1 上有索引也因無法找到切入點從而對錶 fx 進行全表掃描,特別是對於一張大表,這類 SQL 的效能無疑致命。
全文索引則很好地解決了這類低效 SQL 的效能問題。全文索引的理念和普通 B 樹索引的理念剛好相反,B 樹索引的構建是基於某個欄位值的全部或者一部分;全文索引是把某個欄位值的全部資料按照一定的分隔符(停止詞)與字元長度(也叫分詞長度)一起組成各種排列,進而在索引中記錄這些字元出現的位置,次數等靜態資訊。我簡單畫了張圖,如下:
從這張圖可以看到,全文索引(也叫倒排索引)有點類似於 HASH 索引的儲存,只不過 KEY 為單詞,VALUE 為關鍵詞所屬的文件 ID 與對應位置資訊。比如 “YTT” 一詞出現在 4 個文件裡的某個位置,也就是 4 行記錄裡某個位置,FTS_DOC_ID 指的是文件的 ID,每條記錄對應一個 ID,類似於表的主鍵。
接下來,從幾個方面來詳細闡述全文索引,本篇所示例子基於以下表:
CREATE TABLE ft_sample (
id INT PRIMARY KEY,
s1 VARCHAR(200),
log_time DATETIME,
s2 TEXT,
KEY idx_log_time (log_time)
);
輔助表
先給表 ft_sample 新增全文索引
mysql> alter table ft_sample add fulltext ft_s1(s1);
Query OK, 0 rows affected (0。35 sec)
Records: 0 Duplicates: 0 Warnings: 0
對錶建立全文索引後,MySQL 用一些輔助表來儲存全文索引欄位的相關資料指向。如果表 ft_sample 不屬於共享表空間,那對應磁碟目錄上也能看到這些表。如下:
mysql> SELECT table_id, name, space from INFORMATION_SCHEMA。INNODB_TABLES WHERE name LIKE ‘ytt/fts%’;
+——————+——————————————————————————-+————-+
| table_id | name | space |
+——————+——————————————————————————-+————-+
| 1219 | ytt/fts_00000000000004c2_being_deleted | 162 |
| 1220 | ytt/fts_00000000000004c2_being_deleted_cache | 163 |
| 1221 | ytt/fts_00000000000004c2_config | 164 |
| 1222 | ytt/fts_00000000000004c2_deleted | 165 |
| 1223 | ytt/fts_00000000000004c2_deleted_cache | 166 |
| 1230 | ytt/fts_00000000000004c2_00000000000001ba_index_1 | 173 |
| 1231 | ytt/fts_00000000000004c2_00000000000001ba_index_2 | 174 |
| 1232 | ytt/fts_00000000000004c2_00000000000001ba_index_3 | 175 |
| 1233 | ytt/fts_00000000000004c2_00000000000001ba_index_4 | 176 |
| 1234 | ytt/fts_00000000000004c2_00000000000001ba_index_5 | 177 |
| 1235 | ytt/fts_00000000000004c2_00000000000001ba_index_6 | 178 |
+——————+——————————————————————————-+————-+
11 rows in set (0。00 sec)
下面來詳細介紹下這些表:
以 _index_1-6 為字尾的被稱為輔助表,裡面順序存放倒排索引的真實資料。至於分了六張表的原因,可以理解為對欄位新增全文索引並且對資料分詞的並行化。參考引數 innodb_ft_sort_pll_degree,可以控制併發數量。
例如表名為:ytt/fts_00000000000004c2_00000000000001ba_index_1,其中 ytt 代表資料庫名,fts_ 開頭和 _index_1 結尾表示輔助表,00000000000004c2 代表對應的表 ID 的十六進位制值,00000000000001ba 代表加 fulltext 索引欄位 ID 對應的十六進位制值。
查看錶 ft_sample 對應的 ID,
mysql> SELECT
a。table_id,
HEX(a。table_id),
a。index_id,
HEX(a。index_id),
a。name
FROM
information_schema。innodb_indexes a,
information_schema。innodb_tables b
WHERE
a。table_id = b。table_id
AND b。name = ‘ytt/ft_sample’
AND a。name = ‘ft_s1’;
+——————+————————-+——————+————————-+————-+
| table_id | hex(a。table_id) | index_id | hex(a。index_id) | name |
+——————+————————-+——————+————————-+————-+
| 1218 | 4C2 | 442 | 1BA | ft_s1 |
+——————+————————-+——————+————————-+————-+
1 row in set (0。00 sec)
剩下的不包含全文索引欄位 ID 的表為通用輔助表,記錄索引表的配置資訊、以及有關索引刪除的資訊。
ytt/fts_00000000000004c2_deleted
ytt/fts_00000000000004c2_deleted_cache
這兩表內容一樣,都包含了標記為刪除,但是實際上還沒有從之前的六張索引表裡刪除的文件 ID(DOC_ID) 列表;不同的是 ytt/fts_00000000000004c2_deleted_cache 是 ytt/fts_00000000000004c2_deleted 在記憶體中的一個複製。
ytt/fts_00000000000004c2_being_deleted
ytt/fts_00000000000004c2_being_deleted_cache
這兩表的內容也一樣,也都包含了標記為刪除,並且正在從之前的六張索引表裡刪除對應的 DOC_ID。同樣表 tt/fts_00000000000004c2_being_deleted_cache 是表 ytt/fts_00000000000004c2_being_deleted 的記憶體複製。
上面這四張表存在的意義在於可以避免在全文索引欄位頻繁的寫入操作導致對應的六張磁碟索引表成為熱點。由此帶來的問題是刪除的記錄被儲存多份,沒有及時的刪除,佔用額外的磁碟空間。不過可以用 MySQL 語句 “optimize table” 來手動提前釋放這些空間,optimize table 語句預設只對 B+ 樹聚簇索引進行整理,不會對全文索引做整理。這裡MySQL 提供了一個引數 innodb_optimize_fulltext_only,預設關閉,開啟這個引數後,語句 optimize table 只會對全文索引整理磁碟空間。
ytt/fts_00000000000004c2_config
這張表包含了全文索引的內部狀態資訊,欄位 FTS_SYNCED_DOC_ID 不同於 FTS_DOC_ID,表示已經被解析完並且刷盤的索引記錄。
全文索引緩衝池
全文索引有一個緩衝池:information_schema。innodb_ft_index_cache。用來快取全文索引欄位的寫入操作(insert/update),標記分詞以及其他相關資訊,和 MySQL 其他的快取一樣,目的是把多次頻繁刷盤變為按照定義的緩衝池大小寫滿後合併一次性刷盤(重新整理到之前的六張輔助表)。刷盤後表 information_schema。innodb_ft_index_cache 被清空,下次根據全文索引欄位來過濾時,直接查詢對應的磁碟索引表;如果此時對全文索引欄位值有更新但是還沒有觸發刷盤,MySQL 會把緩衝池的資料和磁碟索引表的資料一起返回給客戶端。
其中控制單表緩衝池大小的變數為:innodb_ft_cache_size,預設8MB,最小 1。6MB,最大 80MB。
控制整個 MySQL 例項緩衝池大小的變數為:innodb_ft_total_cache_size,預設 640M,最小 32MB,最大 1。6GB。
文件 ID,DOC_ID
DOC_ID 是關鍵詞對映的索引表記錄 ID,每條記錄被當作一個文件, 對映為 MySQL 全文索引表的一個欄位 FTS_DOC_ID。如果全文索引表沒有顯式指定這個欄位,MySQL 預設建立一個隱藏欄位。為了避免後期加列的開銷,這個欄位不會隨著全文索引的銷燬而刪除。也就是說這個欄位會一直存在,除非這張表被刪掉。
本篇開始的示例表 ft_sample ,用 show extended columns 語句檢視隱藏欄位:
mysql> show extended columns from fx;
+——————-+————————+————+——-+————-+————————+
| Field | Type | Null | Key | Default | Extra |
+——————-+————————+————+——-+————-+————————+
| id | int | NO | PRI | NULL | auto_increment |
| s1 | varchar(200) | YES | MUL | NULL | |
| log_time | datetime | YES | MUL | NULL | |
| s2 | varchar(200) | YES | | NULL | |
| s3 | text | YES | | NULL | |
| FTS_DOC_ID | | NO | | NULL | |
| DB_TRX_ID | | NO | | NULL | |
| DB_ROLL_PTR | | NO | | NULL | |
+——————-+————————+————+——-+————-+————————+
8 rows in set (0。01 sec)
如果想顯式自定義這個欄位,並且手動維護值的唯一性,在建表的時候,或者是在全文索引沒有建立之前,可以指定一個名字為 FTS_DOC_ID 欄位,型別為無符號 INT64(注意,這個欄位必須為大寫)。比如:
mysql> alter table ft_sample add FTS_DOC_ID bigint unsigned not null, add unique key idx_FTS_DOC_ID (FTS_DOC_ID);
Query OK, 0 rows affected (0。13 sec)
Records: 0 Duplicates: 0 Warnings: 0
全文索引事務處理
全文索引的事務處理這塊有點特殊,和 INNODB 的事務處理這塊有點不一樣。比如對全文索引表的 INSERT/UPDATE 操作,必須等待全部 COMMIT 後,才能檢索剛才更新的資料,就算在一個事務裡也看不到剛才更新但是還沒有 COMMIT 的資料。舉個例子:
mysql> begin;
Query OK, 0 rows affected (0。00 sec)
mysql> insert into ft_sample values (1,‘mysql oracle postgresql’,‘2020-01-16 09:32:58’,‘’);
Query OK, 1 row affected (0。00 sec)
mysql> insert into ft_sample values (2,‘mysql oracle postgresql’,‘2020-04-20 09:32:58’,‘’);
Query OK, 1 row affected (0。00 sec)
mysql> insert into ft_sample values (3,‘mysql oracle postgresql’,‘2020-09-30 09:32:58’,‘’);
Query OK, 1 row affected (0。01 sec)
mysql> insert into ft_sample values (4,‘xfs ntfs’,‘2020-10-30 09:32:58’,‘’);
Query OK, 1 row affected (0。00 sec)
mysql> select * from ft_sample where match (s1) against (‘mysql’);
Empty set (0。00 sec)
mysql> commit;
Query OK, 0 rows affected (0。02 sec)
mysql> select * from ft_sample where match (s1) against (‘mysql’);
+——+————————————-+——————————-+————+
| id | s1 | log_time | s2 |
+——+————————————-+——————————-+————+
| 1 | mysql oracle postgresql | 2020-01-16 09:32:58 | |
| 2 | mysql oracle postgresql | 2020-04-20 09:32:58 | |
| 3 | mysql oracle postgresql | 2020-09-30 09:32:58 | |
+——+————————————-+——————————-+————+
3 rows in set (0。00 sec)
從上面例子可以看到,在 commit 之前,查詢關鍵詞 ‘mysql’ 的記錄不存在,commit 後,就可以正常查詢。
透過本篇介紹,我把全文索引的結構以及在 MySQL 中的表現形式做一個大概的介紹,下一篇接著講如何更好的使用全文索引。