MySQL中索引的定義以及操作新手教程
索引的定義
數(shù)據(jù)庫中的索引就像一本書的目錄,可以據(jù)此快速定位數(shù)據(jù)庫中相關(guān)數(shù)據(jù)的所在位置。
在數(shù)據(jù)庫中,索引被定義為一種特殊的數(shù)據(jù)結(jié)構(gòu),由數(shù)據(jù)庫中的一列或多列組合而成,可以用來快速查詢數(shù)據(jù)表中某一特定值的記錄。
索引是在表的字段的基礎(chǔ)上建立的一種數(shù)據(jù)庫對(duì)象,它由DBA或者表的擁有者創(chuàng)建或撤銷,他是創(chuàng)建表與表之間關(guān)聯(lián)關(guān)系的基礎(chǔ)。
索引的類型
普通、唯一、主鍵和全文索引
普通索引(INDEX)
普通索引是MySQL中的基本索引類型,允許在定義索引的列中插入空值和重復(fù)值。
創(chuàng)建普通索引的幾種方式:
# 1.直接創(chuàng)建索引 create index index_name on table(column(length)); # 2. 以修改表結(jié)構(gòu)創(chuàng)建 alter table table_name add index index_name on column(length); # 3. 創(chuàng)建表的同時(shí)創(chuàng)建索引 create table user( id CHAR(6) not null, name CHAR(255) not null, primary key(id), index user_name(name(length)); )
唯一索引(UNIQUE INDEX)
唯一索引指索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。
創(chuàng)建唯一索引的幾種方式:
# 1.直接創(chuàng)建索引 create unique index index_name on table(column(length)); # 2. 以修改表結(jié)構(gòu)創(chuàng)建 alter table table_name add unique index index_name on column(length); # 3. 創(chuàng)建表的同時(shí)創(chuàng)建索引 create table user( id CHAR(6) not null, name CHAR(255) not null, primary key(id), unique index user_name(name(length)); )
主鍵索引(PRIMARY KEY)
主鍵索引是一種特殊的唯一索引,一個(gè)表只能有一個(gè)主鍵,不允許有空值。
全文索引(FULLTEXT)
全文索引的類型為FULLTEXT,表示在定義索引的列上支持值的全文查找,允許插入重復(fù)值或空值。
全文索引主要用來查找文本中的關(guān)鍵字,而不是直接與索引中的值相比較。
MySQL中只有MyISAM存儲(chǔ)引擎支持全文索引。
全文索引可以在進(jìn)行創(chuàng)建表、更新表和創(chuàng)建索引時(shí)使用,目前只支持CHAR、VARCHAR、TEXT列創(chuàng)建全文索引。
單列索引和組合索引
- 單列索引
單列索引即一個(gè)索引只包含單個(gè)列,一個(gè)表可以有多個(gè)單列索引。
- 組合索引
組合索引是指組合表的多個(gè)字段創(chuàng)建的索引,,只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段,索引才會(huì)被使用。遵循最左前綴匹配原則
聚集索引和非聚集索引
- 聚集索引
聚集索引是索引結(jié)構(gòu)和數(shù)據(jù)一起存放的索引,索引結(jié)構(gòu)的葉子節(jié)點(diǎn)上包含著該行的所有信息,找到索引也就找到了數(shù)據(jù)。
- 非聚集索引
非聚集索引是索引結(jié)構(gòu)和數(shù)據(jù)分開存放的索引,索引結(jié)構(gòu)的葉子節(jié)點(diǎn)指向了數(shù)據(jù)的對(duì)應(yīng)行,當(dāng)需要訪問數(shù)據(jù)時(shí),在內(nèi)存中先搜索索引,然后通過索引找到磁盤相應(yīng)數(shù)據(jù)。
聚集索引 | 非聚集索引 |
---|---|
一個(gè)表只能有一個(gè) | 一個(gè)表可以有多個(gè) |
物理連續(xù) | 邏輯連續(xù),物理不連續(xù) |
查詢快,插入慢(需要移動(dòng)物理位置) | 回表查詢 |
索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn) | 索引的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn),通過一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊。 |
索引的創(chuàng)建原則
1. 創(chuàng)建索引由專人完成
- 索引由DBA或表的擁有者負(fù)責(zé)創(chuàng)建和撤銷,其他用戶不能隨意操作。
- 索引由系統(tǒng)自動(dòng)選擇,或由用戶打開,用戶可執(zhí)行重建索引操作。
2.是否創(chuàng)建索引取決于表的數(shù)據(jù)量
- 基本表中記錄的數(shù)量越多,記錄越長,越有必要?jiǎng)?chuàng)建索引。創(chuàng)建索引后,查詢速度的提升效果會(huì)很明顯。要避免對(duì)經(jīng)常更新的表創(chuàng)建過多的索引,索引中的列也要盡可能少。
- 數(shù)據(jù)量小的表最好不要使用索引。由于數(shù)據(jù)較少,查詢花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要短,因此,創(chuàng)建索引可能不會(huì)產(chǎn)生優(yōu)化效果。對(duì)經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引,但要避免添加不必要的字段。
- 索引要根據(jù)數(shù)據(jù)查詢或數(shù)據(jù)處理的要求確定是否創(chuàng)建。對(duì)于查詢頻度高、實(shí)時(shí)性要求高的數(shù)據(jù)一定要建立索引。
3.索引數(shù)量要適度
- 索引文件占用文件目錄和存儲(chǔ)空間,因此索引過多會(huì)加重系統(tǒng)負(fù)擔(dān)。
- 索引需要自身維護(hù)。當(dāng)基本表的數(shù)據(jù)增加、刪除或修改時(shí),索引也會(huì)進(jìn)行調(diào)整和更新,索引文件也要隨之變化,以保持與基本表一致。
- 索引過多會(huì)影響數(shù)據(jù)增、刪、改的速度。索引并非越多越好,-張表中如果有大量的索引,不僅占用磁盤空間,而且還會(huì)影響INSERT、DELETE、UPDATE等操作的性能。
4.避免使用索引的情形
(1)包含太多重復(fù)值的字段。
(2)查詢中很少被引用的字段。
(3)值特別長的字段。
(4)查詢返回率很高的字段。
(5)具有很多NULL值的字段。
(6)需要經(jīng)常增、刪、改的字段。
(7)記錄較少的基本表。
(8)需要頻繁、大批量進(jìn)行數(shù)據(jù)更新的基本表。
索引操作
創(chuàng)建索引
create table <table_name> [<fields_name> type] [UNIQUE|FULLTEXT][INDEX|KEY] <index_name> (<column_name > [length]) [ASC|DESC]
查看索引
show index from <table_name> [from <database_name>]
刪除索引
1.刪除索引
drop index <index_name> from <table_name>
2.通過修改表結(jié)構(gòu)刪除
alter table <table_name> drop index <index_name>
總結(jié)
到此這篇關(guān)于MySQL中索引的定義以及操作的文章就介紹到這了,更多相關(guān)MySQL索引定義及操作內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL存儲(chǔ)過程的權(quán)限問題小結(jié)
這篇文章主要介紹了MySQL存儲(chǔ)過程的權(quán)限問題小結(jié)及mysql 創(chuàng)建存儲(chǔ)過程權(quán)限問題,感興趣的朋友一起看看吧2018-04-04Mysql單文件存儲(chǔ)刪除數(shù)據(jù)文件容量不會(huì)減少的bug與解決方法
這篇文章主要給大家介紹了Mysql單文件存儲(chǔ)刪除數(shù)據(jù)文件時(shí)容量不會(huì)減少的bug與解決方法,文中給出了詳細(xì)的解決方法,相信對(duì)遇到這個(gè)問題的朋友們能帶來一定的幫助,下面來一起看看吧。2016-12-12Mac OS系統(tǒng)下mysql 5.7.20安裝教程圖文詳解
這篇文章主要介紹了Mac OS系統(tǒng)下mysql 5.7.20安裝教程圖文詳解,本文給大家介紹的非常詳細(xì),具有參考借鑒價(jià)值,需要的朋友可以參考下2017-11-11MySQL使用Replace操作時(shí)造成數(shù)據(jù)丟失的問題解決
這篇文章主要給大家介紹了關(guān)于MySQL使用Replace操作時(shí)造成數(shù)據(jù)丟失問題的解決方法,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09mysql高效查詢left join和group by(加索引)
這篇文章主要給大家介紹了關(guān)于mysql高效查詢left join和group by,這個(gè)的前提是加了索引,以及如何在MySQL高效的join3個(gè)表 的相關(guān)資料,需要的朋友可以參考下2021-06-06MySQL實(shí)例精講單行函數(shù)以及字符數(shù)學(xué)日期流程控制
SQL函數(shù)即數(shù)據(jù)庫的內(nèi)置函數(shù),可以運(yùn)用在SQL語句中實(shí)現(xiàn)特定的功能。SQL單行函數(shù)對(duì)于每一行數(shù)據(jù)進(jìn)行計(jì)算后得到一行輸出結(jié)果。SQL單行函數(shù)根據(jù)數(shù)據(jù)類型分為字符函數(shù)、數(shù)字函數(shù)、日期函數(shù)、轉(zhuǎn)換函數(shù),另外還有一些別的函數(shù)2021-10-10