MySQL索引具體有哪些功能

下文給大家?guī)鞰ySQL索引具體有哪些功能有關(guān)內(nèi)容，相信大家一定看過類似的文章。我們給大家?guī)淼挠泻尾煌?？一起來看看正文部分吧，相信看完MySQL索引具體有哪些功能你一定會(huì)有所收獲。

創(chuàng)新互聯(lián)建站提供成都網(wǎng)站建設(shè)、成都做網(wǎng)站、網(wǎng)頁設(shè)計(jì)，品牌網(wǎng)站設(shè)計(jì)，廣告投放等致力于企業(yè)網(wǎng)站建設(shè)與公司網(wǎng)站制作，10年的網(wǎng)站開發(fā)和建站經(jīng)驗(yàn),助力企業(yè)信息化建設(shè)，成功案例突破上千余家,是您實(shí)現(xiàn)網(wǎng)站建設(shè)的好選擇.

MySQL索引功能

1.索引的作用

大大大加快數(shù)據(jù)的檢索速度和增加查詢的效率
降低磁盤I/O成本
保證數(shù)據(jù)記錄的唯一性
使得應(yīng)用于表的SQL語句執(zhí)行的更快

2.索引簡(jiǎn)述

在關(guān)系數(shù)據(jù)庫中，索引是一種單獨(dú)的、物理的對(duì)數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種存儲(chǔ)結(jié)構(gòu)，它是某個(gè)表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識(shí)這些值的數(shù)據(jù)頁的邏輯指針清單。索引的作用相當(dāng)于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。
索引是一種特殊的文件（InnoDB 數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分），它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針。索引不是萬能的，索引可以加快數(shù)據(jù)檢索操作，但會(huì)使數(shù)據(jù)修改操作變慢。每修改數(shù)據(jù)記錄，索引就必須刷新一次。為了在某種程度上彌補(bǔ)這一缺陷，許多 SQL 命令都有一個(gè) DELAY_KEY_WRITE 項(xiàng)。這個(gè)選項(xiàng)的作用是暫時(shí)制止 MySQL 在該命令每插入一條新記錄和每修改一條現(xiàn)有之后立刻對(duì)索引進(jìn)行刷新，對(duì)索引的刷新將等到全部記錄插入/修改完畢之后再進(jìn)行。在需要把許多新記錄插入某個(gè)數(shù)據(jù)表的場(chǎng)合，DELAY_KEY_WRITE 選項(xiàng)的作用將非常明顯。另外，索引還會(huì)在硬盤上占用相當(dāng)大的空間。因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。注意，如果某個(gè)數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實(shí)際效果。
從理論上講，完全可以為數(shù)據(jù)表里的每個(gè)字段分別建一個(gè)索引，但 MySQL 把同一個(gè)數(shù)據(jù)表里的索引總數(shù)限制為16個(gè)。

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

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

3.索引的限制

如果 WHERE 子句的查詢條件里有不等號(hào)（WHERE coloum !=），MySQL 將無法使用索引。類似地，如果 WHERE 子句的查詢條件里使用了函數(shù)（WHERE DAY（column）=），MySQL 也將無法使用索引。在 JOIN 操作中（需要從多個(gè)數(shù)據(jù)表提取數(shù)據(jù)時(shí)），MySQL 只有在主鍵和外鍵的數(shù)據(jù)類型相同時(shí)才能使用索引。
如果 WHERE 子句的查詢條件里使用比較操作符 LIKE 和 REGEXP，MySQL 只有在搜索模板的第一個(gè)字符不是通配符的情況下才能使用索引。比如說，如果查詢條件是 LIKE 'abc%‘，MySQL 將使用索引；如果查詢條件是 LIKE '%abc’，MySQL 將不使用索引。
在 ORDER BY 操作中，MySQL 只有在排序條件不是一個(gè)查詢條件表達(dá)式的情況下才使用索引。（雖然如此，在涉及多個(gè)數(shù)據(jù)表查詢里，即使有索引可用，那些索引在加快 ORDER BY 方面也沒什么作用）。如果某個(gè)數(shù)據(jù)列里包含許多重復(fù)的值，就算為它建立了索引也不會(huì)有很好的效果。比如說，如果某個(gè)數(shù)據(jù)列里包含的凈是些諸如 “0/1” 或 “Y/N” 等值，就沒有必要為它創(chuàng)建一個(gè)索引。

4.索引的分類

（1）普通索引

最基本的索引類型，沒有唯一性之類的限制。

（2）唯一索引

唯一索引是不允許其中任何兩行具有相同索引值的索引。
當(dāng)現(xiàn)有數(shù)據(jù)中存在重復(fù)的鍵值時(shí)，大多數(shù)數(shù)據(jù)庫不允許將新創(chuàng)建的唯一索引與表一起保存。數(shù)據(jù)庫還可能防止添加將在表中創(chuàng)建重復(fù)鍵值的新數(shù)據(jù)。例如，如果在 employee 表中職員的姓 (lname) 上創(chuàng)建了唯一索引，則任何兩個(gè)員工都不能同姓。
對(duì)某個(gè)列建立UNIQUE索引后，插入新紀(jì)錄時(shí)，數(shù)據(jù)庫管理系統(tǒng)會(huì)自動(dòng)檢查新紀(jì)錄在該列上是否取了重復(fù)值，在CREATE TABLE 命令中的UNIQE約束將隱式創(chuàng)建UNIQUE索引。

（3）主鍵索引

簡(jiǎn)稱為主索引，數(shù)據(jù)庫表中一列或列組合（字段）的值唯一標(biāo)識(shí)表中的每一行。該列稱為表的主鍵。
在數(shù)據(jù)庫關(guān)系圖中為表定義主鍵將自動(dòng)創(chuàng)建主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個(gè)值都唯一。當(dāng)在查詢中使用主鍵索引時(shí)，它還允許對(duì)數(shù)據(jù)的快速訪問。
提示盡管唯一索引有助于定位信息，但為獲得最佳性能結(jié)果，建議改用主鍵索引。

（4）候選索引

與主索引一樣要求字段值的唯一性，并決定了處理記錄的順序。在數(shù)據(jù)庫和自由表中，可以為每個(gè)表建立多個(gè)候選索引。

（5）復(fù)合索引

兩個(gè)或更多個(gè)列上的索引被稱作復(fù)合索引。利用索引中的附加列，您可以縮小搜索的范圍，但使用一個(gè)具有兩列的索引不同于使用兩個(gè)單獨(dú)的索引。不允許數(shù)據(jù)記錄出現(xiàn)重復(fù)值和空值。

（6）全文索引

全文索引是目前實(shí)現(xiàn)大數(shù)據(jù)搜索的關(guān)鍵技術(shù)。能夠利用分詞技術(shù)等多種算法智能分析出文本文字中關(guān)鍵字詞的頻率及重要性，然后按照一定的算法規(guī)則智能地篩選出我們想要的搜索結(jié)果。用于多個(gè)列值，允許數(shù)據(jù)記錄出現(xiàn)重復(fù)值和空值
MySQL自帶的全文索引只能用于數(shù)據(jù)庫引擎為MyISAM的數(shù)據(jù)表

（7）空間索引

在MySQL 5.7.4實(shí)驗(yàn)室版本中， InnoDB存儲(chǔ)引擎新增了對(duì)于幾何數(shù)據(jù)空間索引的支持。在此之前，InnoDB將幾何數(shù)據(jù)存儲(chǔ)為BLOB（二進(jìn)制大對(duì)象）數(shù)據(jù)，在空間數(shù)據(jù)上只能創(chuàng)建前綴索引，當(dāng)涉及空間搜索時(shí)非常低效，尤其是在涉及復(fù)雜的幾何數(shù)據(jù)時(shí)。在大多數(shù)情況下，獲得結(jié)果的唯一方式是掃描表。新版本MySQL中，InnoDB支持空間索引，通過R樹來實(shí)現(xiàn)，使得空間搜索變得高效。

5.索引的使用

（1）創(chuàng)建索引

create [unique | fulltext | spatial] index 索引名 on 表名(字段)

詳解

unique：唯一索引

fulltext：全文索引，InnoDB不支持FULLTEXT類型的索引

spatial：空間索引

案例

mysql> create index hehe_1 on hehe.user(user_name); //user表中user_name字段新建普通索引

mysql> create unique index hehe_2 on hehe.user(user_passwd); //user表中user_passwd字段新建唯一索引

mysql> create index hehe_3 on hehe.user(user_name,user_passwd);

//user表中user_name、user_passwd字段組合為復(fù)合索引

（2）查看索引

show index from 數(shù)據(jù)庫名.表名;

（3）刪除索引

drop index 索引名 on 數(shù)據(jù)庫名.表名;

對(duì)于上文關(guān)于MySQL索引具體有哪些功能，大家覺得是自己想要的嗎？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。

本文名稱：MySQL索引具體有哪些功能
文章鏈接：http://m.jiaotiyi.com/article/gjgioj.html

網(wǎng)站建設(shè)知識(shí)