MySQL中的索引應(yīng)用

索引的定義

• 什么是索引(Index)?

官方的定義索引是一種數(shù)據(jù)結(jié)構(gòu),從生活維度講,假如將一本書看成是一張表,這本書的目錄就是表中的索引.

• 為什么要使用索引(Index)

數(shù)據(jù)量比較大時,為了快速找到們需要的數(shù)據(jù)可以使用索引,這樣可以提高查詢的效率.

• 索引(Index)有什么弊端?

1. 會額外的占用空間
2. 對更新操作會帶來一定的復(fù)雜度.

• 使用索引(Index)的應(yīng)用場景?

1. on 子句
2. where 子句
3. group by 子句
4. having 子句
5. order by 子句

索引的分類

• 邏輯應(yīng)用維度 (主鍵,普通,聯(lián)合,唯一,空間索引,…)
• 物理存儲維度 (聚簇索引,非聚簇索引)
• 數(shù)據(jù)存儲結(jié)構(gòu)維度(hash,B+樹,…)

索引創(chuàng)建及應(yīng)用

• 如何查看表中的索引?

show index from student;

• 如何創(chuàng)建索引?

1. 創(chuàng)建表的同時創(chuàng)建索引.(例如 create table tablename(…,index 索引名 (字段名)))
2. 創(chuàng)建表后通過create語句創(chuàng)建索引(例如 create index 索引名 on 表名(字段名))
3. 創(chuàng)建表后通過alter語句創(chuàng)建索引(例如 alter table add index 索引名(字段名))

• 創(chuàng)建普通索引案例分享.

create index index_first_name on student(first_name);
alter table student add index index_last_name (last_name);

• 創(chuàng)建唯一索引案例分享

create unique index index_first_name on student(first_name);
alter student add unique index index_first_name (last_name)

• 創(chuàng)建組合索引案例分享

create index index_first_last on student(first_name,last_name);
alter table student add index index_first_last (first_name,last_name);

• 如何刪除索引?

drop index 索引名 on 表名;

例如

drop index index_first_last on student;

索引存儲結(jié)構(gòu)分析

• MySQL中索引支持哪些存儲結(jié)果?
• hash,B+樹,…
• MySQL中InnoDB默認(rèn)的索引存儲結(jié)構(gòu)是什么?
• B+樹
• MySQL中的B+樹有什么特點?

1. 樹中的非葉子節(jié)點只存儲索引和指針
2. 樹中的葉子節(jié)點存儲索引和數(shù)據(jù)
3. 樹中的葉子節(jié)點處于相同層，并且之間會使用雙向鏈表連接,可以更好的支持范圍查詢.

• MySQL中B+樹相對于B樹有什么優(yōu)點?

1. 一個磁盤可以存儲索引數(shù)量會更多.
2. 相同數(shù)據(jù)量的B+樹相對于B樹的高度相對會比較低(因為分叉多了)
3. 葉子節(jié)點之間B+樹有雙向鏈表的連接,可以支持快速的范圍查詢.

• 你覺得Hash索引有什么優(yōu)勢和劣勢?
• hash索引查詢效率比較高,但是不支持范圍查詢.

聚簇索引和非聚簇索引

• 什么是聚簇索引？

聚簇索引也叫聚集索引，索引和數(shù)據(jù)存儲在一起，也就是索引與數(shù)據(jù)是不分離。
InnoDB存儲引擎就是聚簇索引。

• 聚簇索引都有哪些類型？

InnoDB中，一張表只有一個聚集索引（即主鍵索引），其他索引都是二級索引。
在實際應(yīng)用中我們可以直接通過主鍵聚集索引查詢到具體記錄。假如是二級索引，這個
索引中存儲是索引以及對應(yīng)主鍵，我們使用二級索引執(zhí)行查詢時，是先基于二級索引找
到主鍵，再基于主鍵去查詢具體記錄(這個過程是回表查詢)。

select first_name,salary
from employees
where employee_id=206

create index index_hire_date on employees(hire_date);
select first_name,salary,email
from employees
where hire_date='1987-07-09'

create index index_hire_date_salary on employees(hire_date,salary);
select first_name,salary,email
from employees
where hire_date='1987-07-09' and salary<8000

• 什么是非聚簇索引？
  非聚簇索引是索引與數(shù)據(jù)是分離的，索引和數(shù)據(jù)是單獨存儲的。MyISAM存儲引擎是非聚簇索引。

例如：

[10,0x1001] 0x1001表示記錄地址
[12,0x1002]
0x1001 [10,Jack,J@t.com]
0x1002 [12,Mike,M@t.com]

• 非聚簇索引存儲的是什么？

非聚簇索引在創(chuàng)建時，存儲的是索引值以及索引對應(yīng)的記錄的地址。
基于非聚簇索引查詢數(shù)據(jù)時，可以先基于索引找到數(shù)據(jù)的一個地址，然后基于地址再去查找數(shù)據(jù)。
單從索引角度來說，非聚集索引查找速度不如聚集索引，非聚集索引找到索引位置后還需要根據(jù)索引找到數(shù)據(jù)對應(yīng)的位置

到此這篇關(guān)于MySQL中的索引創(chuàng)建及應(yīng)用的文章就介紹到這了,更多相關(guān)mysql索引應(yīng)用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論