一昭娩、數(shù)據(jù)庫索引介紹
索引是一種特殊的文件(MySql數(shù)據(jù)表上的索引是表空間的一個組成部分)咧欣，它們包含著對數(shù)據(jù)表里所有記錄的引用指針臭墨，直接在索引中查找符合條件的選項藏鹊，加快數(shù)據(jù)庫的查詢速度润讥，而不是一行一行去遍歷數(shù)據(jù)后才選擇出符合條件的。如果沒有索引盘寡，執(zhí)行查詢時MySQL必須從第一個記錄開始掃描整個表的所有記錄楚殿，直至找到符合要求的記錄。表里面的記錄數(shù)量越多竿痰，這個操作的代價就越高脆粥。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無需掃描任何記錄即可迅速得到目標記錄所在的位置影涉。
索引的本質是什么变隔？索引有什么優(yōu)點，缺點是什么常潮？
索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結構弟胀。因此，索引的本質是一種數(shù)據(jù)結構。
在數(shù)據(jù)之外孵户，數(shù)據(jù)庫系統(tǒng)還可以維護滿足特定查找算法的數(shù)據(jù)結構萧朝，這些數(shù)據(jù)結構以某種方式指向真實數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結構上實現(xiàn)高級查找算法夏哭，這種數(shù)據(jù)結構就是索引检柬。
優(yōu)點：
1、提高數(shù)據(jù)檢索效率竖配，降低數(shù)據(jù)庫的IO成本何址；
2、通過索引對數(shù)據(jù)進行排序进胯，降低了數(shù)據(jù)排序的成本用爪，降低了CPU的利用率；
缺點：
1胁镐、索引實際上也是一張表偎血，索引會占用一定的存儲空間；
2盯漂、更新數(shù)據(jù)表的數(shù)據(jù)時颇玷，需要同時維護索引表，因此就缆，會降低insert帖渠、update、delete的速度竭宰；
二空郊、MySQL索引類型包括哪些？
1羞延、普通索引
這是最基本的索引渣淳，它沒有任何限制。它有以下幾種創(chuàng)建方式：
◆ 創(chuàng)建索引
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR伴箩，VARCHAR類型入愧，length可以小于字段實際長度；如果是BLOB和TEXT類型嗤谚，必須指定 length棺蛛，下同。
◆ 修改表結構
ALTER mytable ADD INDEX [indexName] ON (username(length))
◆ 創(chuàng)建表的時候直接指定
CREATE TABLE mytable(?
ID INT NOT NULL,?
username VARCHAR(16) NOT NULL,?
INDEX [indexName] (username(length))?
);?
刪除索引的語法：
DROP INDEX [indexName] ON mytable;
2巩步、唯一索引
它與前面的普通索引類似旁赊，不同的就是：索引列的值必須唯一，但允許有空值椅野。如果是組合索引终畅，則列值的組合必須唯一籍胯。它有以下幾種創(chuàng)建方式：
◆創(chuàng)建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
◆修改表結構
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
◆創(chuàng)建表的時候直接指定
CREATE TABLE mytable(?
ID INT NOT NULL,?
username VARCHAR(16) NOT NULL,?
UNIQUE [indexName] (username(length))?
);?
3、主鍵索引
它是一種特殊的唯一索引离福，不允許有空值杖狼。一般是在建表的時候同時創(chuàng)建主鍵索引：
CREATE TABLE mytable(?
ID INT NOT NULL,?
username VARCHAR(16) NOT NULL,?
PRIMARY KEY(ID)?
);?
當然也可以用 ALTER 命令。記籽：一個表只能有一個主鍵蝶涩。
4、組合索引
為了形象地對比單列索引和組合索引絮识，為表添加多個字段：
CREATE TABLE mytable(?
ID INT NOT NULL,?
username VARCHAR(16) NOT NULL,?
city VARCHAR(50) NOT NULL,?
age INT NOT NULL
);?
為了進一步榨取MySQL的效率绿聘，就要考慮建立組合索引。就是將 name, city, age建到一個索引里：
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
建表時次舌，usernname長度為 16熄攘，這里用 10。這是因為一般情況下名字的長度不會超過10垃它，這樣會加速索引查詢速度鲜屏，還會減少索引文件的大小烹看，提高INSERT的更新速度国拇。
如果分別在 usernname，city惯殊，age上建立單列索引酱吝，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣土思，遠遠低于我們的組合索引务热。雖然此時有了三個索引，但MySQL只能用到其中的那個它認為似乎是最有效率的單列索引己儒。
建立這樣的組合索引崎岂，其實是相當于分別建立了下面三組組合索引：
usernname,city,age?
usernname,city?
usernname?
為什么沒有 city，age這樣的組合索引呢闪湾？這是因為MySQL組合索引“最左前綴”的結果冲甘。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引途样，下面的幾個SQL就會用到這個組合索引：
SELECT * FROM mytable WHREE username="admin" AND city="鄭州"
SELECT * FROM mytable WHREE username="admin"
而下面幾個則不會用到：
SELECT * FROM mytable WHREE age=20 AND city="鄭州"
SELECT * FROM mytable WHREE city="鄭州"
三江醇、 InnoDB存儲索引
在數(shù)據(jù)庫中，如果索引太多何暇，應用程序的性能可能會受到影響陶夜；如果索引太少，又會對查詢性能產(chǎn)生影響煞肾。所以流昏，我們要追求兩者的一個平衡點，足夠多的索引帶來查詢性能提高溯祸，又不因為索引過多導致修改數(shù)據(jù)等操作時負載過高羽嫡。
InnoDB支持3種常見索引：
● 哈希索引
● B+ 樹索引
● 全文索引
我們接下來要詳細講解的就是B+ 樹索引和全文索引纠修。
哈希索引
學習哈希索引之前，我們先了解一些基礎的知識：哈希算法厂僧。哈希算法是一種常用的算法扣草，時間復雜度為O(1)。它不僅應用在索引上颜屠，各個數(shù)據(jù)庫應用中也都會使用辰妙。
哈希表
哈希表（Hash Table）也稱散列表，由直接尋址表改進而來甫窟。
在該表中U表示關鍵字全集密浑，K表示實際存在的關鍵字，右邊的數(shù)組（哈希表）表示在內存中可以直接尋址的連續(xù)空間粗井，哈希表中每個插槽關聯(lián)的單向鏈表中存儲實際數(shù)據(jù)的真實地址尔破。
如果右邊的數(shù)組直接使用直接尋址表，那么對于每一個關鍵字K都會存在一個h[K]且不重復浇衬，這樣存在一些問題懒构，如果U數(shù)據(jù)量過大，那么對于計算機的可用容量來說有點不實際耘擂。而如果集合K占比U的比例過小胆剧，則分配的大部分空間都要浪費。
因此我們使用哈希表醉冤，我們通過一些函數(shù)h(k)來確定映射關系秩霍，這樣讓離散的數(shù)據(jù)盡可能均勻分布的利用數(shù)組中的插槽，但會有一個問題蚁阳，多個關鍵字映射到同一個插槽中铃绒，這種情況稱為碰撞（collision），數(shù)據(jù)庫中采用最簡單的解決方案：鏈接法（chaining）螺捐。也就是每個插槽存儲一個單項鏈表颠悬，所有碰撞的元素會依次形成鏈表中的一個結點，如果不存在归粉，則鏈表指向為NULL椿疗。
而使用的函數(shù)h(k)成為哈希函數(shù)，它必須能夠很好的進行散列糠悼。最好能夠避免碰撞或者達到最小碰撞届榄。一般為了更好的處理哈希的關鍵字，我們會將其轉換為自然數(shù)倔喂，然后通過除法散列铝条、乘法散列或者全域散列來實現(xiàn)靖苇。數(shù)據(jù)庫一般使用除法散列，即當有m個插槽時班缰，我們對每個關鍵字k進行對m的取模：h(k) = k % m贤壁。
InnoDB存儲引擎中的哈希算法
InnoDB存儲引擎使用哈希算法來查找字典，沖突機制采用鏈表埠忘，哈希函數(shù)采用除法散列脾拆。對于緩沖池的哈希表，在緩存池中的每頁都有一個chain指針莹妒，指向相同哈希值的頁名船。對于除法散列，m的值為略大于2倍緩沖池頁數(shù)量的質數(shù)旨怠。如當前innodb_buffer_pool_size大小為10M渠驼，則共有640個16KB的頁，需要分配1280個插槽鉴腻，而略大于的質數(shù)為1399迷扇，因此會分配1399個槽的哈希表，用來哈希查詢緩沖池中的頁爽哎。
而對于將每個頁轉換為自然數(shù)蜓席，每個表空間都有一個space_id，用戶要查詢的是空間中某個連續(xù)的16KB的頁倦青，即偏移量（offset）瓮床，InnoDB將space_id左移20位，再加上space_id和offset产镐，即K=space_id<<20+space_id+offset，然后使用除法散列到各個槽中踢步。
自適應哈希索引
自適應哈希索引采用上面的哈希表實現(xiàn)癣亚，屬于數(shù)據(jù)庫內部機制，DBA不能干預获印。它只對字典類型的查找非呈鑫恚快速，而對范圍查找等卻無能為力兼丰，如：
select * from t where f='100'玻孟；
我們可以查看自適應哈希索引的使用情況：
mysql> show engine innodb status\G;
*************************** 1. row ***************************
? Type: InnoDB
? Name:
Status:
=====================================
2019-05-13 23:32:21 7f4875947700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 32 seconds
...
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1226, seg size 1228, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 1288 buffer(s)
0.16 hash searches/s, 16.97 non-hash searches/s
我們可以看到自適應哈希的使用情況，可以通過最后一行的hash searches/non-hash searches來判斷使用哈希索引的效率鳍征。
我們可以使用innodb_adaptive_hash_index參數(shù)來禁用或啟用此特性黍翎，默認開啟。
B+ 樹索引
B+ 樹索引是目前關系型數(shù)據(jù)庫系統(tǒng)中查找最為常用和有效的索引艳丛，其構造類似于二叉樹匣掸，根據(jù)鍵值對快速找到數(shù)據(jù)趟紊。B+ 樹（balance+ tree）由B樹（banlance tree 平衡二叉樹）和索引順序訪問方法（ISAM: Index Sequence Access Method）演化而來，這幾個都是經(jīng)典的數(shù)據(jù)結構碰酝。而MyISAM引擎最初也是參考ISAM數(shù)據(jù)結構設計的霎匈。
基礎數(shù)據(jù)結構
想要了解B+ 樹數(shù)據(jù)結構，我們先了解一些基礎的知識送爸。
（1）二分查找法
又稱為折半查找法铛嘱，指的是將數(shù)據(jù)順序排列，通過每次和中間值比較袭厂，跳躍式查找弄痹，每次縮減一半的范圍，快速找到目標的算法嵌器。其算法復雜度為log2(n)肛真，比順序查找要快上一些。
如圖所示爽航，從有序列表中查找48蚓让，只需要3步：
詳細的算法可以參考二分查找算法。
（2）二叉查找樹
二叉查找樹的定義是在一個二叉樹中讥珍，左子樹的值總是小于根鍵值历极，根鍵值總是小于右子樹的值。在我們查找時衷佃，每次都從根開始查找趟卸，根據(jù)比較的結果來判斷繼續(xù)查找左子樹還是右子樹。其查找的方法非常類似于二分查找法氏义。
（3）平衡二叉樹
二叉查找樹的定義非常寬泛锄列，可以任意構造，但是在極端情況下查詢的效率和順序查找一樣惯悠，如只有左子樹的二叉查找樹邻邮。
若想構造一個性能最大的二叉查找樹，就需要該樹是平衡的克婶，即平衡二叉樹（由于其發(fā)明者為G. M. Adelson-Velsky 和 Evgenii Landis筒严，又被稱為AVL樹）。其定義為必須滿足任何節(jié)點的兩個子樹的高度最大差為1的二叉查找樹情萤。平衡二叉樹相對結構較優(yōu)鸭蛙，而最好的性能需要建立一個最優(yōu)二叉樹，但由于維護該樹代價高筋岛，因此一般平衡二叉樹即可娶视。
平衡二叉樹查詢速度很快，但在樹發(fā)生變更時泉蝌，需要通過一次或多次左旋和右旋來達到樹新的平衡歇万。這里不發(fā)散講揩晴。
B+ 樹
了解了基礎的數(shù)據(jù)結構后，我們來看下B+ 樹的實現(xiàn)贪磺，其定義十分復雜硫兰，簡單來說就是在B樹上增加規(guī)定：
1、葉子結點存數(shù)據(jù)寒锚，非葉子結點存指針
2劫映、所有葉子結點從左到右用雙向鏈表記錄
目標是為磁盤或其他直接存取輔助設備設計的一種平衡查找樹。在該樹中刹前，所有的記錄都按鍵值的大小放在同一層的葉子節(jié)點上泳赋，各葉子節(jié)點之間有指針進行連接（非連續(xù)存儲），形成一個雙向鏈表喇喉。索引節(jié)點按照平衡樹的方式構造祖今，并存在指針指向具體的葉子節(jié)點，進行快速查找拣技。
下面的B+ 樹為數(shù)據(jù)較少時千诬，此時高度為2，每頁固定存放4條記錄膏斤，扇出固定為5（圖上灰色部分）徐绑。葉子節(jié)點存放多條數(shù)據(jù)，是為了降低樹的高度莫辨，進行快速查找傲茄。
當我們插入28、70沮榜、95 3條數(shù)據(jù)后盘榨，B+ 樹由于數(shù)據(jù)滿了，需要進行頁的拆分敞映。此時高度變?yōu)?较曼，每頁依然是4條記錄，雙向鏈表未畫出但是依然是存在的振愿，現(xiàn)在可以看出來是一個平衡二叉樹的雛形了。
InnoDB的B+ 樹索引
InnoDB的B+ 樹索引的特點是高扇出性弛饭，因此一般樹的高度為2~4層冕末，這樣我們在查找一條記錄時只用I/O 2~4次。當前機械硬盤每秒至少100次I/O/s侣颂，因此查詢時間只需0.02~0.04s档桃。
數(shù)據(jù)庫中的B+ 樹索引分為聚集索引（clustered index）和輔助索引（secondary index）。它們的區(qū)別是葉子節(jié)點存放的是否為一整行的完整數(shù)據(jù)憔晒。
聚集索引
聚集索引就是按照每張表的主鍵（唯一）構造一棵B+ 樹藻肄，同時葉子節(jié)點存放整行的完整數(shù)據(jù)蔑舞，因此將葉子節(jié)點稱為數(shù)據(jù)頁。由于定義了數(shù)據(jù)的邏輯順序嘹屯，聚集索引也能快速的進行范圍類型的查詢攻询。
聚集索引的葉子節(jié)點按照邏輯順序連續(xù)存儲，葉子節(jié)點內部物理上連續(xù)存儲州弟，作為最小單元钧栖，葉子節(jié)點間通過雙向指針連接，物理存儲上不連續(xù)婆翔，邏輯存儲上連續(xù)拯杠。
聚集索引能夠針對主鍵進行快速的排序查找和范圍查找，由于是雙向鏈表啃奴，因此在逆序查找時也非程杜悖快。
我們可以通過explain命令來分析MySQL數(shù)據(jù)庫的執(zhí)行計劃：
# 查看表的定義最蕾，可以看到id為主鍵依溯，name為普通列
mysql> show create table dimensionsConf;
| Table? ? ? ? ? | Create Table? ?
| dimensionsConf | CREATE TABLE `dimensionsConf` (
? `id` int(11) NOT NULL AUTO_INCREMENT,
? `name` varchar(20) DEFAULT NULL,
? `remark` varchar(1024) NOT NULL,
? PRIMARY KEY (`id`),
? FULLTEXT KEY `fullindex_remark` (`remark`)
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
# 先測試一個非主鍵的name屬性排序并查找，可以看到?jīng)]有使用到任何索引揖膜，且需要filesort（文件排序）誓沸，這里的rows為輸出行數(shù)的預估值
mysql> explain select * from dimensionsConf order by name limit 10\G;
*************************** 1. row ***************************
? ? ? ? ? id: 1
? select_type: SIMPLE
? ? ? ? table: dimensionsConf
? ? ? ? type: ALL
possible_keys: NULL
? ? ? ? ? key: NULL
? ? ? key_len: NULL
? ? ? ? ? ref: NULL
? ? ? ? rows: 57
? ? ? ? Extra: Using filesort
1 row in set (0.00 sec)
# 再測試主鍵id的排序并查找，此時使用主鍵索引壹粟，在執(zhí)行計劃中沒有了filesort操作拜隧，這就是聚集索引帶來的優(yōu)化
mysql> explain select * from dimensionsConf order by id limit 10\G;
*************************** 1. row ***************************
? ? ? ? ? id: 1
? select_type: SIMPLE
? ? ? ? table: dimensionsConf
? ? ? ? type: index
possible_keys: NULL
? ? ? ? ? key: PRIMARY
? ? ? key_len: 4
? ? ? ? ? ref: NULL
? ? ? ? rows: 10
? ? ? ? Extra: NULL
1 row in set (0.00 sec)
# 再查找根據(jù)主鍵id的范圍查找，此時直接根據(jù)葉子節(jié)點的上層節(jié)點就可以快速得到范圍趁仙，然后讀取數(shù)據(jù)
mysql> explain select * from dimensionsConf where id>10 and id<10000\G;
*************************** 1. row ***************************
? ? ? ? ? id: 1
? select_type: SIMPLE
? ? ? ? table: dimensionsConf
? ? ? ? type: range
possible_keys: PRIMARY
? ? ? ? ? key: PRIMARY
? ? ? key_len: 4
? ? ? ? ? ref: NULL
? ? ? ? rows: 56
? ? ? ? Extra: Using where
1 row in set (0.00 sec)
輔助索引
輔助索引又稱非聚集索引洪添，其葉子節(jié)點不包含行記錄的全部數(shù)據(jù)，而是包含一個書簽（bookmark）雀费，該書簽指向對應行數(shù)據(jù)的聚集索引干奢，告訴InnoDB存儲引擎去哪里查找具體的行數(shù)據(jù)。輔助索引與聚集索引的關系就是結構相似盏袄、獨立存在忿峻，但輔助索引查找非索引數(shù)據(jù)需要依賴于聚集索引來查找。
全文索引
我們通過B+ 樹索引可以進行前綴查找辕羽，如：
select * from blog where content like 'xxx%';
只要為content列添加了B+ 樹索引（聚集索引或輔助索引）逛尚，就可快速查詢。但在更多情況下刁愿，我們在博客或搜索引擎中需要查詢的是某個單詞绰寞，而不是某個單詞開頭，如：
select * from blog where content like '%xxx%';
此時如果使用B+ 樹索引依然是全表掃描，而全文檢索（Full-Text Search）就是將整本書或文章內任意內容檢索出來的技術滤钱。
倒排索引
全文索引通常使用倒排索引（inverted index）來實現(xiàn)觉壶，倒排索引和B+ 樹索引都是一種索引結構，它需要將分詞（word）存儲在一個輔助表（Auxiliary Table）中件缸，為了提高全文檢索的并行性能铜靶，共有6張輔助表。輔助表中存儲了單詞和單詞在各行記錄中位置的映射關系停团。它分為兩種：
inverted file index（倒排文件索引）旷坦，表現(xiàn)為{單詞，單詞所在文檔ID}
full inverted index（詳細倒排索引）佑稠，表現(xiàn)為{單詞秒梅，(單詞所在文檔ID, 文檔中的位置)}
對于這樣的一個數(shù)據(jù)表：
倒排文件索引類型的輔助表存儲為：
詳細倒排索引類型的輔助表存儲為，占用更多空間舌胶，也更好的定位數(shù)據(jù)捆蜀，比提供更多的搜索特性：
全文檢索索引緩存
輔助表是存在與磁盤上的持久化的表，由于磁盤I/O比較慢幔嫂，因此提供FTS Index Cache（全文檢索索引緩存）來提高性能辆它。FTS Index Cache是一個紅黑樹結構，根據(jù)（word, list）排序履恩，在有數(shù)據(jù)插入時锰茉，索引先更新到緩存中，而后InnoDB存儲引擎會批量進行更新到輔助表中切心。
當數(shù)據(jù)庫宕機時飒筑，尚未落盤的索引緩存數(shù)據(jù)會自動讀取并存儲，配置參數(shù)innodb_ft_cache_size控制緩存的大小绽昏，默認為32M协屡，提高該值，可以提高全文檢索的性能全谤，但在故障時肤晓，需要更久的時間恢復。
在刪除數(shù)據(jù)時认然，InnoDB不會刪除索引數(shù)據(jù)补憾，而是保存在DELETED輔助表中，因此一段時間后卷员，索引會變得非常大余蟹，可以通過optimize table命令手動刪除無效索引記錄。如果需要刪除的內容非常多子刮，會影響應用程序的可用性，參數(shù)innodb_ft_num_word_optimize控制每次刪除的分詞數(shù)量，默認為2000挺峡，用戶可以調整該參數(shù)來控制刪除幅度葵孤。
全文檢索限制
全文檢索存在一個黑名單列表（stopword list），該列表中的詞不需要進行索引分詞橱赠，默認共有36個尤仍，如the單詞。你可以自行調整：
mysql> select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a? ? |
| about |
| an? ? |
| are? |
| as? ? |
| at? ? |
| be? ? |
| by? ? |
| com? |
| de? ? |
| en? ? |
| for? |
| from? |
| how? |
| i? ? |
| in? ? |
| is? ? |
| it? ? |
| la? ? |
| of? ? |
| on? ? |
| or? ? |
| that? |
| the? |
| this? |
| to? ? |
| was? |
| what? |
| when? |
| where |
| who? |
| will? |
| with? |
| und? |
| the? |
| www? |
+-------+
36 rows in set (0.00 sec)
其他限制還有：
● 每張表只能有一個全文檢索索引
● 多列組合的全文檢索索引必須使用相同的字符集和字符序狭姨，不了解的可以參考MySQL亂碼的原因和設置UTF8數(shù)據(jù)格式
● 不支持沒有單詞界定符（delimiter）的語言宰啦，如中文、日語饼拍、韓語等
全文檢索
我們創(chuàng)建一個全文索引：
mysql> create fulltext index fullindex_remark on dimensionsConf(remark);
Query OK, 0 rows affected, 1 warning (0.39 sec)
Records: 0? Duplicates: 0? Warnings: 1
mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level? | Code | Message? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+---------+------+--------------------------------------------------+
| Warning |? 124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)
全文檢索有兩種方法：
● 自然語言（Natural Language）赡模，默認方法，可省略：（IN NATURAL LANGUAE MODE）
● 布爾模式（Boolean Mode）：（IN BOOLEAN MODE）
自然語言還支持一種擴展模式师抄，后面加上：（WITH QUERY EXPANSION）漓柑。
其語法為MATCH()...AGAINST()，MATCH指定被查詢的列叨吮，AGAINST指定何種方法查詢辆布。
自然語言檢索
mysql> select remark from dimensionsConf where remark like '%baby%';
+-------------------+
| remark? ? ? ? ? ? |
+-------------------+
| a baby like panda |
| a baby like panda |
+-------------------+
2 rows in set (0.00 sec)
mysql> select remark from dimensionsConf where match(remark) against('baby' IN NATURAL LANGUAGE MODE);
+-------------------+
| remark? ? ? ? ? ? |
+-------------------+
| a baby like panda |
| a baby like panda |
+-------------------+
2 rows in set (0.00 sec)
# 查看下執(zhí)行計劃，使用了全文索引排序
mysql> explain select * from dimensionsConf where match(remark) against('baby');
+----+-------------+----------------+----------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table? ? ? ? ? | type? ? | possible_keys? ? | key? ? ? ? ? ? ? | key_len | ref? | rows | Extra? ? ? |
+----+-------------+----------------+----------+------------------+------------------+---------+------+------+-------------+
|? 1 | SIMPLE? ? ? | dimensionsConf | fulltext | fullindex_remark | fullindex_remark | 0? ? ? | NULL |? ? 1 | Using where |
+----+-------------+----------------+----------+------------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
我們也可以查看各行數(shù)據(jù)的相關性茶鉴，是一個非負的浮點數(shù)锋玲，0代表沒有相關性：
mysql> select id,remark,match(remark) against('baby') as relevance from dimensionsConf;
+-----+-----------------------+--------------------+
| id? | remark? ? ? ? ? ? ? ? | relevance? ? ? ? ? |
+-----+-----------------------+--------------------+
| 106 | c? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? ? ? ? 0 |
| 111 | 運營商? ? ? ? ? ? |? ? ? ? ? ? ? ? ? 0 |
| 115 | a baby like panda? ? | 2.1165735721588135 |
| 116 | a baby like panda? ? | 2.1165735721588135 |
+-----+-----------------------+--------------------+
4 rows in set (0.01 sec)
布爾模式檢索
MySQL也允許用修飾符來進行全文檢索，其中特殊字符會有特殊含義：
● +: 該word必須存在
● -: 該word必須排除
● （no operator）: 該word可選涵叮，如果出現(xiàn)惭蹂，相關性更高
● @distance: 查詢的多個單詞必須在指定范圍之內
● >: 出現(xiàn)該單詞時增加相關性
● <: 出現(xiàn)該單詞時降低相關性
● ~: 出現(xiàn)該單詞時相關性為負
● *: 以該單詞開頭的單詞
● ": 表示短語
# 代表必須有a baby短語，不能有man围肥，可以有l(wèi)ik開頭的單詞剿干，可以有panda，
select remark from dimensionsConf where match(remark) against('+"a baby" -man lik* panda' IN BOOLEAN MODE);
擴展查詢
當查詢的關鍵字太短或不夠清晰時穆刻，需要用隱含知識來進行檢索置尔，如database關聯(lián)的MySQL/DB2等。但這個我并沒太明白怎么使用氢伟，后續(xù)補充吧榜轿。
類似的使用是：
select * from articles where match(title,body) against('database' with query expansion);
如果任何問題或者建議，歡迎留言交流朵锣。
以上內容希望幫助到大家谬盐，很多PHPer在進階的時候總會遇到一些問題和瓶頸，業(yè)務代碼寫多了沒有方向感诚些，不知道該從那里入手去提升飞傀，對此我整理了一些資料皇型，包括但不限于：分布式架構、高可擴展砸烦、高性能弃鸦、高并發(fā)、服務器性能調優(yōu)幢痘、TP6唬格，laravel，YII2颜说，Redis购岗，Swoole、Swoft门粪、Kafka喊积、Mysql優(yōu)化、shell腳本庄拇、Docker注服、微服務、Nginx等多個知識點高級進階干貨需要的可以免費分享給大家措近，需要的可以加入我的PHP技術交流群點擊此處
