技術 分享:SQL語言之索引
SQL語言之索引
前言
本章我們將學習MySQL中的索引,本文將從索引的作用、索引的分類、創建索引的語法、索引的使用策略以及索引的實現原理等方面帶大家了解索引。
索引的作用
索引的作用就是加快查詢速度,如果把使用了索引的查詢看做是法拉利跑車的話,那麼沒有用索引的查詢就相當於是自行車。目前實際項目中表的數據量越來越大,動輒上百萬上千萬級別,沒有索引的查詢會變得非常緩慢,使用索引成為了查詢優化的必選項目。
索引的概念
索引其實是一種特殊的數據,也保存在資料庫文件中,索引數據保存著數據表中實際數據的位置。類似書籍前面的目錄,這個目錄就保存了書中各個章節的頁數,通過查看目錄我們可以快速定位章節的頁數,從而加快查找速度。
我們來看一段查詢語句:
select * from book where id = 1000000;
假設書籍表中有幾百萬行數據,沒索引的查詢會遍歷前面的100萬行數據找到結果,如果我們在id上建立主鍵索引,則直接在索引上定位結果,速度要快得多。
索引的優缺點
優點:提高查詢速度
缺點:本身也是數據,會佔用磁碟空間;索引的創建和維護也需要時間成本;進行刪除、更新和插入操作時,因為要維護索引,所以速度會降低。
使用索引的語法
創建索引
建表的同時創建索引
create table 表名
(
欄位名 類型,
...
欄位名 類型,
index 索引名稱 (欄位名)
);
建表後添加索引
alter table 表名 add index 索引名(欄位名);
或
create index 索引名 on 表名(欄位名);
刪除索引
alter table 表名 drop index 索引名;
或
drop index 索引名 on 表名;
查看錶中的索引
show index from 表名;
查看查詢語句使用的索引
explain 查詢語句;
索引的分類
索引按功能分為:
普通索引,在普通欄位上建立的索引,沒有任何限制
主鍵索引,創建主鍵時,自動創建的索引,不能為空,不能重複
唯一索引,建立索引的欄位數據必須是唯一的,允許空值
全文索引,在大文本類型(Text)欄位上建立的索引
組合索引,組合多個列創建的索引,多個列不能有空值
代碼示例:
-- 創建書籍表
create table tb_book
(
-- 創建主鍵索引
id int primary key,
-- 創建唯一索引
title varchar(100) unique,
author varchar(20),
content Text,
time datetime,
-- 普通索引
index ix_title (title),
-- 全文索引
fulltext index ix_content(content),
-- 組合索引
index ix_title_author(title,author)
);
-- 建表後添加主鍵索引
ALTER TABLE tb_book ADD PRIMARY KEY pk_id(id);
-- 建表後添加唯一索引
ALTER TABLE tb_book ADD UNIQUE index ix_title(title);
-- 建表後添加全文索引
ALTER TABLE tb_book ADD FULLTEXT index ix_content(content);
-- 查詢時使用全文索引
SELECT * FROM tb_book MATCH(content) ANGAINST(『勝利』);
-- 建表後添加組合索引
ALTER TABLE tb_book ADD INDEX ix_book(title,author);
注意:創建組合索引時,要遵循」最左前綴」原則,把最常查詢、排序的欄位放左邊,按重要性依次遞減。
索引的使用策略
什麼情況下要建立索引?
在經常需要查詢和排序的欄位上建立索引
數據特別多
什麼情況下不要建立索引?
欄位數據存在大量的重複,如:性別
數據很少
經常需要增刪改的欄位
什麼情況下索引會失效?
模糊查詢時,使用like 『%張%』會失效,而like 『張%』不會
使用is null或is not null查詢時
使用組合索引時,某個欄位為null
使用or查詢多個條件時
在函數中使用欄位時,如where year(time) = 2019
索引的結構
不同的存儲引擎使用不同結構的索引:
聚簇索引,InnoDB支持,索引的順序和數據的物理順序一致,類似新華字典中的拼音目錄排列和漢字排列順序一致,聚簇索引一個表中只能有一個。
非聚簇索引,MyISAM支持,索引順序和數據的物理順序不一致,類似新華字典中的偏旁部首目錄和漢字排列順序不一致,非聚簇索引表可以有多個。
索引的數據結構主要是:BTree和B Tree
BTree的數據結構如下,是一種平衡搜索多叉樹,每個節點由key和data組成,key是索引的鍵,data是鍵對應的數據,在節點的兩邊是兩個指針,指向另外的索引位置,而所有的鍵都是排序過的,這樣在搜索索引時,可以使用二分查找,速度比較快,時間複雜度是h*log(n),h是樹的高度,BTree是一種比較高效的搜索結構。
B Tree的數據結構如下,是BTree的升級版,區別是非葉子節點不在存儲具體的數據,只保存索引的鍵,數據保存到葉子節點中,並且葉子節點中沒有指針只有鍵和數據。B Tree的優點是:搜索效率更高,因為非葉子節點中沒有保存數據,就可以保存更多的鍵,每一層的鍵越多,樹的高度就會減少,這樣查詢速度就會提升。
總結
索引是提高查詢速度的重要手段,本章我們學習了索引的分類和創建語法,以及使用索引的策略,不是所有的表都適合創建索引,最後我們還學習了索引的內部結構,這樣大家對索引會有一個基本的認識。
※Kotlin:命名參數、默認參數值、可變參數、局部函數
※Kotlin技術分享-運算符重載
TAG:千鋒JAVA開發學院 |