您好,登錄后才能下訂單哦!
這篇文章主要介紹“MySQL數(shù)據(jù)庫索引原理及優(yōu)化策略是什么”的相關(guān)知識,小編通過實(shí)際案例向大家展示操作過程,操作方法簡單快捷,實(shí)用性強(qiáng),希望這篇“MySQL數(shù)據(jù)庫索引原理及優(yōu)化策略是什么”文章能幫助大家解決問題。
索引是一種特殊的文件,包含著對數(shù)據(jù)表里所有記錄的引用指針??梢詫Ρ碇械囊涣谢蚨嗔袆?chuàng)建索引,并指定索引的類型,各類索引有各自的數(shù)據(jù)結(jié)構(gòu)實(shí)現(xiàn)。
數(shù)據(jù)庫中的表、數(shù)據(jù)、索引之間的關(guān)系,類似于書架上的圖書、書籍內(nèi)容和書籍目錄的關(guān)系,索引所起的作用類似書籍目錄,可用于快速定位、檢索數(shù)據(jù)。索引可以極大地提高數(shù)據(jù)庫的性能。
要考慮對數(shù)據(jù)庫表的某列或某幾列創(chuàng)建索引,需要考慮以下幾點(diǎn):
數(shù)據(jù)量較大,且經(jīng)常對這些列進(jìn)行條件查詢。
該數(shù)據(jù)庫表的插入操作,及對這些列的修改操作頻率較低。
索引會占用額外的磁盤空間。
從索引存儲結(jié)構(gòu)劃分:BTree索引、Hash索引、FULLTEXT全文索引、RTree索引
從應(yīng)用層次劃分:普通索引,唯一索引,主鍵索引,復(fù)合索引
從索引鍵值類型劃分,主鍵索引,輔助索引(二級索引)
從數(shù)據(jù)存儲和索引鍵值邏輯關(guān)系劃分:聚集索引(聚簇索引)非聚集泰引(非聚簇索)
從索引列數(shù)量劃分:單列索引,復(fù)合索引
區(qū)別:
數(shù)據(jù)的保存位置不同:B+樹保存在葉子節(jié)點(diǎn),B樹保存在所有的節(jié)點(diǎn)中
體現(xiàn)出B+樹優(yōu)勢:節(jié)點(diǎn)不存儲data,這樣一個(gè)節(jié)點(diǎn)就可以存儲更多的key??梢允沟脴涓?,所以IO操作次數(shù)更少。 查詢性能穩(wěn)定:每次查詢都是從根節(jié)點(diǎn)遍歷到葉子節(jié)點(diǎn),查詢路徑長度相同,即每次查詢效率相當(dāng),時(shí)間復(fù)雜度固定是O(log(n))
葉子節(jié)點(diǎn)的指向:B+樹相鄰的葉子節(jié)點(diǎn)通過指針相連,B樹沒有
體現(xiàn)出B+樹優(yōu)勢:所有葉子節(jié)點(diǎn)形成有序鏈表,便于范圍查找
-- 在創(chuàng)建表的時(shí)候,直接在字段名后指定 primary key create table user1(id int primary key, name varchar(30)); -- 在創(chuàng)建表的最后,指定某列或某幾列為主鍵索引 create table user2(id int, name varchar(30), primary key(id)); -- 創(chuàng)建表以后再添加主鍵 create table user3(id int, name varchar(30)); alter table user3 add primary key(id);
主鍵索引的特點(diǎn):
一個(gè)表中,最多有一個(gè)主鍵索引,當(dāng)然可以使符合主鍵
主鍵索引的效率高(主鍵不可重復(fù))
創(chuàng)建主鍵索引的列,它的值不能為null,且不能重復(fù)
主鍵索引的列基本上是int
-- 在表定義時(shí),在某列后直接指定unique唯一屬性。 create table user4(id int primary key, name varchar(30) unique); -- 創(chuàng)建表時(shí),在表的后面指定某列或某幾列為unique create table user5(id int primary key, name varchar(30), unique(name)); -- 創(chuàng)建表以后再添加unique create table user6(id int primary key, name varchar(30)); alter table user6 add unique(name);
唯一索引的特點(diǎn):
一個(gè)表中,可以有多個(gè)唯一索引
查詢效率高
如果在某一列建立唯一索引,必須保證這列不能有重復(fù)數(shù)據(jù)
如果一個(gè)唯一索引上指定not null,等價(jià)于主鍵索引
--在表的定義最后,指定某列為索引 create table user8(id int primary key, name varchar(20), email varchar(30), index(name) ); --創(chuàng)建完表以后指定某列為普通索引 create table user9(id int primary key, name varchar(20), email varchar(30)); alter table user9 add index(name); -- 創(chuàng)建一個(gè)索引名為 idx_name 的索引 create table user10(id int primary key, name varchar(20), email varchar(30)); create index idx_name on user10(name);
普通索引的特點(diǎn):
一個(gè)表中可以有多個(gè)普通索引,普通索引在實(shí)際開發(fā)中用的比較多
如果某列需要創(chuàng)建索引,但是該列有重復(fù)的值,那么我們就應(yīng)該使用普通索引
show keys from 表名
mysql> show keys from goods\G
*********** 1. row ***********
Table: goods <= 表名
Non_unique: 0 <= 0表示唯一索引
Key_name: PRIMARY <= 主鍵索引
Seq_in_index: 1
Column_name: goods_id <= 索引在哪列
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE <= 以二叉樹形式的索引
Comment:
1 row in set (0.00 sec)
show index from 表名;
desc 表名
刪除主鍵索引: alter table 表名 drop primary key;
其他索引的刪除: alter table 表名 drop index 索引名; 索引名就是show keys from 表名中的Key_name 字段
mysql> alter table user10 drop index idx_name;
drop index 索引名 on 表名
mysql> drop index name on user8
比較頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
唯一性太差的字段不適合單獨(dú)創(chuàng)建索引,即使頻繁作為查詢條件
更新非常頻繁的字段不適合作創(chuàng)建索引
不會出現(xiàn)在where子句中的字段不該創(chuàng)建索引
關(guān)于“MySQL數(shù)據(jù)庫索引原理及優(yōu)化策略是什么”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識,可以關(guān)注億速云行業(yè)資訊頻道,小編每天都會為大家更新不同的知識點(diǎn)。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。