mysql全文索引怎么用,mysql全文索引作用

MySQL的全文索引Fulltext Index | 包括ngram

InnoDB的全文索引使用反向索引的設計。反向索引存儲了一個單詞（word）列表，對于每個單詞，都有一個文檔的列表，來標識這個單詞出現(xiàn)的地方。為了支持臨近搜索（proximity search），每個單詞的位置信息也以字節(jié)偏移的方式存儲。

我們提供的服務有：成都網(wǎng)站制作、成都做網(wǎng)站、外貿(mào)營銷網(wǎng)站建設、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認證、解放ssl等。為1000+企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務，是有科學管理、有技術(shù)的解放網(wǎng)站制作公司

當創(chuàng)建了InnoDB全文索引，一系列的索引表會一同被創(chuàng)建，見下面的例子：

最前面的六個表包含了反向索引，它們被稱作附屬索引表（auxiliary index table）。當輸入的表被索引（tokenized）后，每個獨立的單詞（亦稱作“tokens”）會被攜帶其DOC_ID和位置信息插入到索引表中。根據(jù)單詞第一個字符的字符集排序權(quán)重，在六個索引表中對單詞進行完全排序和分區(qū)。

反向索引分區(qū)到六個附屬索引表以支持并行的索引創(chuàng)建。默認有2個線程復制索引（Tokenize）、排序、插入單詞和關聯(lián)數(shù)據(jù)到索引表中。工作的線程的數(shù)量由 innodb_ft_sort_pll_degree 配置項控制的。對于大表的全文索引，可以考慮增加線程數(shù)量。

如果主表創(chuàng)建在 xx表空間，索引表存儲在它們自己的表空間中。反之，索引表存儲于其索引的表空間中。

前面例子展示的另外一種索引表被稱作通用索引表，它們被用于全文索引的“刪除處理（deletion handing）”和存儲內(nèi)部狀態(tài)。不同于為每個全文索引都各自創(chuàng)建的反向索引表，這組表對特定表的所有全文索引都是共用的。

即使全文索引刪掉了，通用索引（Common Index）也會被保留，當全文索引刪除后，為這個索引而創(chuàng)建的FTS_DOC_ID列依然保留，因為移除FTS_DOC_ID列會導致重構(gòu)之前被索引的表。管理FTS_DOC_ID列需要用到通用索引表。

為了防止大量并發(fā)讀寫附屬表，InnoDB使用全文索引緩存去臨時緩存最近的插入行。在存滿并刷入磁盤之前，緩存的內(nèi)容一直存儲在內(nèi)存之中，可以通過查詢 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 表去查看最近緩存的插入行。

緩存和批處理刷新行為避免了對輔助索引表的頻繁更新，頻繁更新可能會在繁忙的插入和更新期間導致并發(fā)訪問問題。批處理還避免了對同一個word的多次插入，最大化的減少了重復的條目。相同的word會先merge再刷入到磁盤中，而不是為每個word單獨插入，這樣提高了插入效率并且使得索引附屬表盡可能的小。

全文索引緩存只緩存最近插入的行，查詢時，已經(jīng)刷入磁盤（附屬索引表）的數(shù)據(jù)不會再回到索引緩存中。附屬索引表中的內(nèi)容是直接查詢的，最終返回的結(jié)果返回前需要將附屬索引表的結(jié)果和索引緩存中的結(jié)果合并。

InnoDB使用被稱作DOC_ID的唯一文件描述符，將全文索引中的單詞與該單詞在文檔中的記錄映射起來。映射關系需要索引表中的 FTS_DOC_ID 列。在創(chuàng)建全文索引時，如果沒有定義 FTS_DOC_ID 列，InnoDB會自動的加入一個隱藏的 FTS_DOC_ID 列。下面是一個例子，

CREATE FULLTEXT INDEX ft_index ON xxxxxxxx(CONTEXT)

[2021-11-12 18:14:04] [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID

重點看一下這一行： [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID ，InnoDB重新構(gòu)建了這個表，并且添加了一個列 FTS_DOC_ID 。

在CREATE TABLE的過程中添加 FTS_DOC_ID 的時間成本要低于在已經(jīng)有數(shù)據(jù)的表上建立全文索引。如果在表加載數(shù)據(jù)之前定義 FTS_DOC_ID 列，這個表和它的索引都不需要為了新增列而重新構(gòu)建。如果你不需要考慮 CREATE FULLTEXT INDEX 的性能，可以讓InnoDB為你創(chuàng)建 FTS_DOC_ID 列。InnoDB會新增一個隱藏的 FTS_DOC_ID 列，并且在 FTS_DOC_ID 上建立唯一索引（FTS_DOC_ID_INDEX）。如果你想自行創(chuàng)建 FTS_DOC_ID 列，這個列必須定義為 BIGINT UNSIGNED NOT NULL 且命名為FTS_DOC_ID（全大寫），如下例子：

如果你自行定義 FTS_DOC_ID 列的話，你需要負責管理這個列，避免空值（empty）或者重復值。 FTS_DOC_ID 的值是不能被重復利用的，所以也就是說 FTS_DOC_ID 的值是需要一直增加的。

或者，你可以在 FTS_DOC_ID 列上創(chuàng)建所必須的唯一索引FTS_DOC_ID_INDEX（全大寫）。

mysql CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);

如果你沒有創(chuàng)建FTS_DOC_ID_INDEX，InnoDB會自動創(chuàng)建。

在MySQL 5.7.13前，允許最大FTS_DOC_ID與最新的FTS_DOC_ID之間的間隔為10000，在MySQL 5.7.13及之后的版本中，這個允許的間隔為65535。

為了避免重新構(gòu)建表，F(xiàn)TS_DOC_ID列在刪除了全文索引之后依然被保留。

刪除被索引文件的一個記錄，可能會在附屬索引表中產(chǎn)生非常多的小的刪除項，在并發(fā)訪問時，會產(chǎn)生熱點問題。為了避免這個問題，每當被索引表中的記錄被刪除時，會將被刪文檔的DOC_ID記錄在一個特別的 FTS_*_DELETED 表中，同時全文索引中已經(jīng)索引了的記錄依然被保存。在返回查詢結(jié)果前，使用 FTS_*_DELETED 中的信息去過濾掉已經(jīng)刪除掉了的DOC_ID。這種設計的優(yōu)勢在于刪除速度快且消耗低。不好的地方在于索引的大小不能隨著記錄的刪除而立即減少。為了刪除已刪除記錄在全文索引中的項，需要對被索引的表執(zhí)行OPTIMIZE TABLE，配合[ innodb_optimize_fulltext_only=ON ]，去重構(gòu)全文索引。

細節(jié)略，有例子：

全文搜索只能看到已經(jīng)提交了的數(shù)據(jù)。

你可以通過查詢下面的INFORMATION_SCHEMA表，來監(jiān)控或測試InnoDB的一些特殊文本處理。

默認的分詞器不支持中文，不能檢索到中文中的英文單詞。

InnoDB默認的Stopwords：

select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;

SQL中的關鍵詞（保留關鍵字）：

Shell中的關鍵詞：for，while，echo

其他：###, ***, --,

被索引表數(shù)據(jù)量、索引表數(shù)據(jù)量

模糊匹配與嚴格匹配的性能差距

需要將 innodb_optimize_fulltext_only 配置為ON，這里是否需要DBA在MySQL鏡像中修改？

innodb_optimize_fulltext_only 設置為ON后，對系統(tǒng)有何影響需要評估。

innodb_optimize_fulltext_only

執(zhí)行的時間、頻率。

MySQL內(nèi)建的全文檢索解析器使用單詞之間的空白作為分隔符以標識單詞的頭尾，但是這里有個限制，對于表意文字，它是沒有單詞分隔符的。為了解決這個限制，MySQL提供了支持中文、日語、韓語的 ngram 解析器。ngram解析器支持InnoDB和MyISAM。

Ngram是內(nèi)建在服務中的插件，像其他自建在服務中的插件一樣，服務啟動時會自動加載它。全文檢索的語法參考上面（ Section 12.10, “Full-Text Search Functions” ），這里只討論一些不同的地方。除了單詞的最小、最大長度配置項（[ innodb_ft_min_token_size ]innodb_ft_max_token_size,ft_min_word_len,ft_max_word_len，全文檢索依賴一些配置項都是可以使用的。

Ngram默認索引的單詞（token）的大小為2（ 2bigram ）。例如，索引的大小為2，Ngram解析器解析字符串“abc def”為四個單詞元素(tokens)：“ab”, “bc”, “de” and “ef”。

ngram token size is configurable using the ngram_token_size configuration option, which has a minimum value of 1 and maximum value of 10.

作為只讀變量, ngram_token_size 只能在啟動配置或者配置文件中指定

與默認的解析器相差不大，多了一句： xxx WITH PARSER ngram

Ngram在解析時去除空格，如

MySQL內(nèi)建的默認全文檢索解析器將單詞與Stopword列表中的做對比，如果單詞與Stopword列表中的元素相同的話，這個單詞則不會被索引。對于Ngram解析器，Stopword的處理方式不同。Ngram解析器不排除與stopword列表中的條目相等的token，而是排除包含stopwords的token。例如，假設 ngram_token_size=2 ，包含“a,b”的文檔將被解析為 “a,” h和“,b”。如果將逗號（“,”）定義為停止字，則 “a,”和“,b”都將不會加入索引中，因為它們包含逗號。

例子：

默認Ngram解析器使用默認的Stopword列表，這里面含有英文的Stopword。如果需要中文的Stopword，需要你自己創(chuàng)建。

Stopword的長度超過 ngram_token_size則會被忽略。

有兩個文檔，一個包含“ab”，另一個包含“abc”。對于搜索文本“abc”將轉(zhuǎn)換成“ab”，“bc”。

略。

For example, The search phrase “abc” is converted to “ab bc”, which returns documents containing “abc” and “ab bc”.

The search phrase “abc def” is converted to “ab bc de ef”, which returns documents containing “abc def” and “ab bc de ef”. A document that contains “abcdef” is not returned.

使用Ngram解析器好處是支持了中文的檢索

全文搜索之MySQL與ElasticSearch搜索引擎

MySQL支持全文索引和搜索功能。在MySQL中可以在CHAR、VARCHAR或TEXT列使用FULLTETXT來創(chuàng)建全文索引。

FULLTEXT索引主要用MATCH()...AGAINST語法來實現(xiàn)搜索：

MySQL的全文搜索存在以下局限：

通常來說MySQL自帶的全文搜索使用起來局限性比較大，性能和功能都不太成熟，主要適用于小項目，大項目還是建議使用elasticsearch來做全文搜索。

ElasticSearch是一個分布式的開源搜索和分析引擎，適用于所有類型的數(shù)據(jù)，包括文本、數(shù)字、地理空間、結(jié)構(gòu)化和非結(jié)構(gòu)化數(shù)據(jù)，以下簡稱ES。

Elasticsearch 在 Apache Lucene 的基礎上開發(fā)而成，Elasticsearch 以其簡單的 REST 風格 API、分布式特性、速度和可擴展性而聞名，是 Elastic Stack 的核心組件。Elastic Stack 是適用于數(shù)據(jù)采集、充實、存儲、分析和可視化的一組開源工具。

Elasticsearch 的實現(xiàn)原理主要分為以下幾個步驟，首先用戶將數(shù)據(jù)提交到Elasticsearch 數(shù)據(jù)中心，再通過分詞控制器去將對應的數(shù)據(jù)分詞，將其權(quán)重和分詞結(jié)果一并存入數(shù)據(jù)，當用戶搜索數(shù)據(jù)時候，再根據(jù)權(quán)重將結(jié)果排名，打分，再將返回結(jié)果呈現(xiàn)給用戶。

由于ES是基于RESTfull Web接口的，因此我們直接按照慣例傳遞JSON參數(shù)調(diào)用接口即可實現(xiàn)增刪改查，并且不需要我們做額外的管理操作就可以直接索引文檔，ES已經(jīng)內(nèi)置了所有的缺省操作，可以自動幫我們定義類型。

再次執(zhí)行PUT，會對庫中已有的id為1的數(shù)據(jù)進行覆蓋，每修改一次_version字段的版本號就會加1。

默認搜索會返回前10個結(jié)果：

返回的幾個關鍵詞：

查詢字符串搜索，可以像傳遞URL參數(shù)一樣傳遞查詢語句。

精確查詢：

全文搜索：

以上兩種方法都需要考慮數(shù)據(jù)更改后如何與ES進行同步。

如何使用mysql的全文索引搜索

你有沒有想過如何使用搜索功能在所有整站中實現(xiàn)！互聯(lián)網(wǎng)博客和網(wǎng)站，大多數(shù)都采用MySQL數(shù)據(jù)庫。MySQL提供了一個美妙的方式實施一個小的搜索引擎，在您的網(wǎng)站（全文檢索）。所有您需要做的是擁有的MySQL 4.x及以上。MySQL提供全文檢索功能，我們可以用它來 ??實現(xiàn)搜索功能。

首先，讓我們?yōu)槲覀兊睦又性O置一個示例表。我們將創(chuàng)建一個名為第一個表。

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

);

在此表中還可以添加一些示例數(shù)據(jù)。執(zhí)行后，插入查詢。

INSERT INTO articles (title,body) VALUES

('MySQL Tutorial','DBMS stands for DataBase ...'),

('How To Use MySQL Well','After you went through a ...'),

('Optimizing MySQL','In this tutorial we will show ...'),

('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

('MySQL vs. YourSQL','In the following database comparison ...'),

('MySQL Security','When configured properly, MySQL ...');

一旦樣本數(shù)據(jù)是準備好，我們可以開始我們的全文檢索功能。

自然語言全文搜索

嘗試我們的示例表上執(zhí)行下面的SELECT查詢。

SELECT * FROM articles

WHERE MATCH (title,body) AGAINST ('database');

你就能看到結(jié)果如下：

在下面的數(shù)據(jù)庫比較5 MySQL與YourSQL的...

MySQL教程DBMS 1代表數(shù)據(jù)庫...

我們在上面的SQL查詢（標題，正文）反對（“數(shù)據(jù)庫”）的比賽，選擇所有的記錄，列標題和正文進行全文搜索。

您可以修改該查詢，并創(chuàng)建您自己的版本，以自己的數(shù)據(jù)庫中執(zhí)行全文搜索。

布爾全文搜索

它可能發(fā)生，你要指定某些關鍵字在您的搜索條件。此外，您可能要忽略某些關鍵字。布爾全文搜索可以用來執(zhí)行這些要求的全文檢索。

檢查下面的SELECT查詢。

SELECT * FROM articles WHERE MATCH (title,body)

AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

如果您發(fā)現(xiàn)上述選擇查詢，我們增加了布爾MODE反對（）。這個查詢將獲取MySQL的關鍵字，但不YourSQL關鍵字的所有記錄。請注意+和-我們以前指定的關鍵字！

在執(zhí)行此功能，MySQL使用什么有時也被稱為布爾邏輯作為暗示，其中：+代表與-代表不是[無操作員]暗示或

以下是幾個例子布爾搜索條件。

“蘋果香蕉

查找行至少包含兩個詞之一。

“+蘋果+果汁”

尋找包含兩個單詞的行。

“+蘋果Macintosh

查找行包含“蘋果”，但排名的行，如果它們也包含“麥金塔”。

“+蘋果Macintosh的”

查找行包含“蘋果”這個詞，而不是“麥金塔”。

'+蘋果Macintosh的“

查找包含單詞“蘋果”的行，但如果該行也包含單詞“麥金塔”，速度比如果行不低。這是“軟”比“+蘋果Macintosh電腦”，為“麥金塔”的存在，導致該行不能在所有返回的搜索。

'+蘋果+（營業(yè)額餡餅）“

行包含“蘋果”和“營業(yè)額”，或“蘋果”和“餡餅”（任何順序）的話，但排名“蘋果的營業(yè)額”比“蘋果餡餅“。

限制

支持全文檢索的MyISAM表只。MySQL 4.1中，使用多個字符設置一個單一的表內(nèi)的支持。然而，在一個FULLTEXT索引的所有列，必須使用相同的字符集和校對規(guī)則。MATCH（）列列表必須匹配完全在一些列清單表的FULLTEXT索引定義，除非這場比賽（）是在布爾模式。布爾模式搜索，可以做非索引列，雖然他們很可能是緩慢的。

怎么MySql添加全文索引

使用索引是數(shù)據(jù)庫性能優(yōu)化的必備技能之一。在MySQL數(shù)據(jù)庫中，有四種索引：聚集索引(主鍵索引)、普通索引、唯一索引以及我們這里將要介紹的全文索引(FULLTEXT INDEX)。

全文索引(也稱全文檢索)是目前搜索引擎使用的一種關鍵技術(shù)。它能夠利用「分詞技術(shù)「等多種算法智能分析出文本文字中關鍵字詞的頻率及重要性，然后按照一定的算法規(guī)則智能地篩選出我們想要的搜索結(jié)果。在這里，我們就不追根究底其底層實現(xiàn)原理了，現(xiàn)在我們來看看在MySQL中如何創(chuàng)建并使用全文索引。

在MySQL中，創(chuàng)建全文索引相對比較簡單。例如，我們有一個文章表(article)，其中有主鍵ID(id)、文章標題(title)、文章內(nèi)容(content)三個字段?，F(xiàn)在我們希望能夠在title和content兩個列上創(chuàng)建全文索引，article表及全文索引的創(chuàng)建SQL語句如下：

--創(chuàng)建article表

CREATE TABLE article (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

content TEXT,

FULLTEXT (title, content) --在title和content列上創(chuàng)建全文索引

);

上面就是在創(chuàng)建表的同時建立全文索引的SQL示例。此外，如果我們想要給已經(jīng)存在的表的指定字段創(chuàng)建全文索引，同樣以article表為例，我們可以使用如下SQL語句進行創(chuàng)建：

--給現(xiàn)有的article表的title和content字段創(chuàng)建全文索引

--索引名稱為fulltext_article

ALTER TABLE article

ADD FULLTEXT INDEX fulltext_article (title, content)

在MySQL中創(chuàng)建全文索引之后，現(xiàn)在就該了解如何使用了。眾所周知，在數(shù)據(jù)庫中進行模糊查詢是使用LIKE關鍵字進行查詢，例如：

SELECT * FROM article WHERE content LIKE '%查詢字符串%'

那么，我們使用全文索引也是這樣用的嗎？當然不是，我們必須使用特有的語法才能使用全文索引進行查詢。例如，我們想要在article表的title和content列中全文檢索指定的查詢字符串，可以如下編寫SQL語句：

SELECT * FROM article WHERE MATCH(title, content) AGAINST('查詢字符串')

強烈注意：MySQL自帶的全文索引只能用于數(shù)據(jù)庫引擎為MyISAM的數(shù)據(jù)表，如果是其他數(shù)據(jù)引擎，則全文索引不會生效。此外，MySQL自帶的全文索引只能對英文進行全文檢索，目前無法對中文進行全文檢索。如果需要對包含中文在內(nèi)的文本數(shù)據(jù)進行全文檢索，我們需要采用Sphinx(斯芬克斯)/Coreseek技術(shù)來處理中文。本站將會在后續(xù)文章中對Sphinx以及Coreseek進行介紹。

備注1：目前，使用MySQL自帶的全文索引時，如果查詢字符串的長度過短將無法得到期望的搜索結(jié)果。MySQL全文索引所能找到的詞的默認最小長度為4個字符。另外，如果查詢的字符串包含停止詞，那么該停止詞將會被忽略。

備注2：如果可能，請盡量先創(chuàng)建表并插入所有數(shù)據(jù)后再創(chuàng)建全文索引，而不要在創(chuàng)建表時就直接創(chuàng)建全文索引，因為前者比后者的全文索引效率要高。

mySQL的索引功能

索引是一種特殊的文件（InnoDB 數(shù)據(jù)表上的索引是表空間的一個組成部分），它們包含著對數(shù)據(jù)表里所有記錄的引用指針。索引不是萬能的，索引可以加快數(shù)據(jù)檢索操作，但會使數(shù)據(jù)修改操作變慢。每修改數(shù)據(jù)記錄，索引就必須刷新一次。為了在某種程度上彌補這一缺陷，許多 SQL 命令都有一個 DELAY_KEY_WRITE 項。這個選項的作用是暫時制止 MySQL 在該命令每插入一條新記錄和每修改一條現(xiàn)有之后立刻對索引進行刷新，對索引的刷新將等到全部記錄插入/修改完畢之后再進行。在需要把許多新記錄插入某個數(shù)據(jù)表的場合，DELAY_KEY_WRITE 選項的作用將非常明顯。另外，索引還會在硬盤上占用相當大的空間。因此應該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。注意，如果某個數(shù)據(jù)列包含許多重復的內(nèi)容，為它建立索引就沒有太大的實際效果。

從理論上講，完全可以為數(shù)據(jù)表里的每個字段分別建一個索引，但 MySQL 把同一個數(shù)據(jù)表里的索引總數(shù)限制為16個。

1．InnoDB 數(shù)據(jù)表的索引

與 InnoDB數(shù)據(jù)表相比，在 InnoDB 數(shù)據(jù)表上，索引對 InnoDB 數(shù)據(jù)表的重要性要大得多。在 InnoDB 數(shù)據(jù)表上，索引不僅會在搜索數(shù)據(jù)記錄時發(fā)揮作用，還是數(shù)據(jù)行級鎖定機制的苊、基礎。“數(shù)據(jù)行級鎖定”的意思是指在事務操作的執(zhí)行過程中鎖定正在被處理的個別記錄，不讓其他用戶進行訪問。這種鎖定將影響到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE 命令以及 INSERT、UPDATE 和 DELETE 命令。出于效率方面的考慮，InnoDB 數(shù)據(jù)表的數(shù)據(jù)行級鎖定實際發(fā)生在它們的索引上，而不是數(shù)據(jù)表自身上。顯然，數(shù)據(jù)行級鎖定機制只有在有關的數(shù)據(jù)表有一個合適的索引可供鎖定的時候才能發(fā)揮效力。

2．限制

如果 WHERE 子句的查詢條件里有不等號（WHERE coloum !=），MySQL 將無法使用索引。類似地，如果 WHERE 子句的查詢條件里使用了函數(shù)（WHERE DAY（column）=），MySQL 也將無法使用索引。在 JOIN 操作中（需要從多個數(shù)據(jù)表提取數(shù)據(jù)時），MySQL 只有在主鍵和外鍵的數(shù)據(jù)類型相同時才能使用索引。

如果 WHERE 子句的查詢條件里使用比較操作符 LIKE 和 REGEXP，MySQL 只有在搜索模板的第一個字符不是通配符的情況下才能使用索引。比如說，如果查詢條件是 LIKE 'abc%‘，MySQL 將使用索引；如果查詢條件是 LIKE '%abc’，MySQL 將不使用索引。

在 ORDER BY 操作中，MySQL 只有在排序條件不是一個查詢條件表達式的情況下才使用索引。（雖然如此，在涉及多個數(shù)據(jù)表查詢里，即使有索引可用，那些索引在加快 ORDER BY 方面也沒什么作用）。如果某個數(shù)據(jù)列里包含許多重復的值，就算為它建立了索引也不會有很好的效果。比如說，如果某個數(shù)據(jù)列里包含的凈是些諸如 “0/1” 或 “Y/N” 等值，就沒有必要為它創(chuàng)建一個索引。 1．普通索引

普通索引（由關鍵字 KEY 或 INDEX 定義的索引）的唯一任務是加快對數(shù)據(jù)的訪問速度。因此，應該只為那些最經(jīng)常出現(xiàn)在查詢條件（WHERE column =）或排序條件（ORDER BY column）中的數(shù)據(jù)列創(chuàng)建索引。只要有可能，就應該選擇一個數(shù)據(jù)最整齊、最緊湊的數(shù)據(jù)列（如一個整數(shù)類型的數(shù)據(jù)列）來創(chuàng)建索引。

2．唯一索引

普通索引允許被索引的數(shù)據(jù)列包含重復的值。比如說，因為人有可能同名，所以同一個姓名在同一個“員工個人資料”數(shù)據(jù)表里可能出現(xiàn)兩次或更多次。

如果能確定某個數(shù)據(jù)列將只包含彼此各不相同的值，在為這個數(shù)據(jù)列創(chuàng)建索引的時候就應該用關鍵字UNIQUE 把它定義為一個唯一索引。這么做的好處：一是簡化了 MySQL 對這個索引的管理工作，這個索引也因此而變得更有效率；二是 MySQL 會在有新記錄插入數(shù)據(jù)表時，自動檢查新記錄的這個字段的值是否已經(jīng)在某個記錄的這個字段里出現(xiàn)過了；如果是，MySQL 將拒絕插入那條新記錄。也就是說，唯一索引可以保證數(shù)據(jù)記錄的唯一性。事實上，在許多場合，人們創(chuàng)建唯一索引的目的往往不是為了提高訪問速度，而只是為了避免數(shù)據(jù)出現(xiàn)重復。

3．主索引

在前面已經(jīng)反復多次強調(diào)過：必須為主鍵字段創(chuàng)建一個索引，這個索引就是所謂的“主索引”。主索引與唯一索引的唯一區(qū)別是：前者在定義時使用的關鍵字是 PRIMARY 而不是 UNIQUE。

4．外鍵索引

如果為某個外鍵字段定義了一個外鍵約束條件，MySQL 就會定義一個內(nèi)部索引來幫助自己以最有效率的方式去管理和使用外鍵約束條件。

5．復合索引

索引可以覆蓋多個數(shù)據(jù)列，如像 INDEX (columnA, columnB) 索引。這種索引的特點是 MySQL 可以有選擇地使用一個這樣的索引。如果查詢操作只需要用到 columnA 數(shù)據(jù)列上的一個索引，就可以使用復合索引 INDEX(columnA, columnB)。不過，這種用法僅適用于在復合索引中排列在前的數(shù)據(jù)列組合。比如說，INDEX (A，B，C) 可以當做 A 或 (A,B) 的索引來使用，但不能當做 B、C 或 (B,C) 的索引來使用。 在為 CHAR 和 VARCHAR 類型的數(shù)據(jù)列定義索引時，可以把索引的長度限制為一個給定的字符個數(shù)（這個數(shù)字必須小于這個字段所允許的最大字符個數(shù)）。這么做的好處是可以生成一個尺寸比較小、檢索速度卻比較快的索引文件。在絕大多數(shù)應用里，數(shù)據(jù)庫中的字符串數(shù)據(jù)大都以各種各樣的名字為主，把索引的長度設置為10~15 個字符已經(jīng)足以把搜索范圍縮小到很少的幾條數(shù)據(jù)記錄了。在為 BLOB 和 TEXT 類型的數(shù)據(jù)列創(chuàng)建索引時，必須對索引的長度做出限制；MySQL 所允許的最大索引全文索引文本字段上的普通索引只能加快對出現(xiàn)在字段內(nèi)容最前面的字符串（也就是字段內(nèi)容開頭的字符）進行檢索操作。如果字段里存放的是由幾個、甚至是多個單詞構(gòu)成的較大段文字，普通索引就沒什么作用了。這種檢索往往以的形式出現(xiàn)，這對 MySQL 來說很復雜，如果需要處理的數(shù)據(jù)量很大，響應時間就會很長。

這類場合正是全文索引（full-textindex）可以大顯身手的地方。在生成這種類型的索引時，MySQL 將把在文本中出現(xiàn)的所有單詞創(chuàng)建為一份清單，查詢操作將根據(jù)這份清單去檢索有關的數(shù)據(jù)記錄。全文索引即可以隨數(shù)據(jù)表一同創(chuàng)建，也可以等日后有必要時再使用下面這條命令添加：

ALTER TABLE tablename ADD FULLTEXT（column1，column2）有了全文索引，就可以用 SELECT 查詢命令去檢索那些包含著一個或多個給定單詞的數(shù)據(jù)記錄了。下面是這類查詢命令的基本語法：

SELECT * FROM tablename

WHERE MATCH (column1,column2) AGAINST('word1','word2','word3')

上面這條命令將把 column1 和 column2 字段里有 word1、word2 和 word3 的數(shù)據(jù)記錄全部查詢出來。

注解：InnoDB 數(shù)據(jù)表不支持全文索引。 只有當數(shù)據(jù)庫里已經(jīng)有了足夠多的測試數(shù)據(jù)時，它的性能測試結(jié)果才有實際參考價值。如果在測試數(shù)據(jù)庫里只有幾百條數(shù)據(jù)記錄，它們往往在執(zhí)行完第一條查詢命令之后就被全部加載到內(nèi)存里，這將使后續(xù)的查詢命令都執(zhí)行得非?？?-不管有沒有使用索引。只有當數(shù)據(jù)庫里的記錄超過了 1000 條、數(shù)據(jù)總量也超過了 MySQL 服務器上的內(nèi)存總量時，數(shù)據(jù)庫的性能測試結(jié)果才有意義。

在不確定應該在哪些數(shù)據(jù)列上創(chuàng)建索引的時候，人們從 EXPLAIN SELECT 命令那里往往可以獲得一些幫助。這其實只是簡單地給一條普通的 SELECT 命令加一個 EXPLAIN 關鍵字作為前綴而已。有了這個關鍵字，MySQL 將不是去執(zhí)行那條 SELECT 命令，而是去對它進行分析。MySQL 將以表格的形式把查詢的執(zhí)行過程和用到的索引等信息列出來。

在 EXPLAIN 命令的輸出結(jié)果里，第1列是從數(shù)據(jù)庫讀取的數(shù)據(jù)表的名字，它們按被讀取的先后順序排列。type列指定了本數(shù)據(jù)表與其它數(shù)據(jù)表之間的關聯(lián)關系（JOIN）。在各種類型的關聯(lián)關系當中，效率最高的是 system，然后依次是 const、eq_ref、ref、range、index 和 All（All 的意思是：對應于上一級數(shù)據(jù)表里的每一條記錄，這個數(shù)據(jù)表里的所有記錄都必須被讀取一遍——這種情況往往可以用一索引來避免）。

possible_keys 數(shù)據(jù)列給出了 MySQL 在搜索數(shù)據(jù)記錄時可選用的各個索引。key 數(shù)據(jù)列是 MySQL 實際選用的索引，這個索引按字節(jié)計算的長度在 key_len 數(shù)據(jù)列里給出。比如說，對于一個 INTEGER 數(shù)據(jù)列的索引，這個字節(jié)長度將是4。如果用到了復合索引，在 key_len 數(shù)據(jù)列里還可以看到 MySQL 具體使用了它的哪些部分。作為一般規(guī)律，key_len 數(shù)據(jù)列里的值越小越好。

ref 數(shù)據(jù)列給出了關聯(lián)關系中另一個數(shù)據(jù)表里的數(shù)據(jù)列的名字。row 數(shù)據(jù)列是 MySQL 在執(zhí)行這個查詢時預計會從這個數(shù)據(jù)表里讀出的數(shù)據(jù)行的個數(shù)。row 數(shù)據(jù)列里的所有數(shù)字的乘積可以大致了解這個查詢需要處理多少組合。

最后，extra 數(shù)據(jù)列提供了與 JOIN 操作有關的更多信息，比如說，如果 MySQL 在執(zhí)行這個查詢時必須創(chuàng)建一個臨時數(shù)據(jù)表，就會在 extra 列看到 usingtemporary 字樣。

mysql 生成索引之后怎么用

1.添加PRIMARY KEY（主鍵索引）

mysqlALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

mysqlALTER TABLE `table_name` ADD UNIQUE (

`column`

)

3.添加INDEX(普通索引)

mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

mysqlALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

文章名稱：mysql全文索引怎么用,mysql全文索引作用
新聞來源：http://muchs.cn/article2/phipoc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設、、面包屑導航、網(wǎng)頁設計公司、網(wǎng)站排名、網(wǎng)站營銷

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)