MySQL學習之索引入門

一.索引基礎

1.什么是索引？
索引是一個單獨的，存盤在磁盤上的資料庫結構，它包含著對資料庫里所有記錄的參考指標，使用索引可以讓我們不必在查找資料時按順序一個一個往下找，而是一次到位，方便快捷，

MySQL中索引的存盤型別有兩種：BTREE和HASH(這里涉及資料結構，先了解即可)

2.使用索引的優點：
1）通過創建唯一索引保證每行資料的一致性
2）可以大大加快資料的查詢速度
3）使用分組和排序子句進行資料查詢時，可以顯著減少查詢中分組和排序的時間，
4）加速表和表之間的連接

3.索引的不利：
1）需維護
2）占用磁盤空間

4.索引的分類：
1）普通索引：允許在索引的列中插入重復值和空值，
2）唯一索引：索引列必須唯一，允許空值，但主鍵索引是一種特殊的唯一索引，不允許空值
3）單列索引：顧名思義，以一個列所作為索引列，一個表中可以有多個單列索引
4）組合索引：以多個列的組合作為索引，組合索引的查詢遵循最左前綴原則（只有在查詢條件中使用了這些列的左邊列時，索引列才會被使用）
5）全文索引：在定義索引的列上支持值的全文范圍內的查找，允許在這些索引列中插入重復值和空值，

6）空間索引：對空間資料型別的欄位建立的索引，（初學了解即可）

5.索引設計原則： 這些原則讓我們建立良好的索引避免性能浪費

1）盡量使用最少的索引達到最好的效果，太多的索引不僅占用磁盤空間，而且會影響插入，洗掉，更新等操作（因為表中資料更改的同時存盤索引的磁盤的資料也會發生變化）

2）避免對經常更新的表進行太多索引，且盡量用單列索引，對經常用于查詢的欄位應該創建索引，

3）索引適合于資料量大的表，

4）在不同值少的列上不要使用索引（索引失效）

5）當唯一性是某種資料的特征時，可以指定唯一索引

6）在頻繁進行排序或分組的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引，

二.索引SQL陳述句

1.創建索引：

1）在創建表時指定索引

例子：
CREATE TABLE book
( bookid INT NOT NULL,
bookname VARCHAR(225) NOT NULL,
authors VARCHAR(255) NOT NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication) //建立普通單列索引
);

同理：
UNIQUE INDEX(year_publication) //建立唯一索引
INDEX MultiId(bookid,bookname) //建立組合索引 ，查詢遵循最左原則
FULLTEXT INDEX fullText(bookname) //建立全文索引

2）向已存在表中加入索引

ALTER TABLE book ADD INDEX bookIdx(bookid) 或者
CREATE INDEX bookIdx ON book(bookid)
其他同理

2.洗掉索引

ALTER TABLE book DROP INDEX bookIdx; 或者
DROP INDEX bookIdx ON book;

三.索引案例實戰

1.首先我們先創建一個表，不指定索引：

CREATE TABLE book (
  bookid int(11) NOT NULL,
  bookname varchar(225) NOT NULL,
  authors varchar(225) NOT NULL,
  info varchar(225) DEFAULT NULL,
  comment varchar(225) DEFAULT NULL,
  year_publication year(4) NOT NULL
);

表內欄位如下：

然后向里面插入二十條資料:

use study;
INSERT INTO book VALUES(1,'資料庫原理與應用教程','張三','本書可作為資料庫入門書籍，里面詳細介紹了資料庫原理和操作','此書簡單易懂，實為佳作',2020);
INSERT INTO book VALUES(2,'計算機網路','張四','本書詳細介紹了計算機網路的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(3,'java核心技術卷一','張五','本書可作為java基礎知識的學習書籍','此書深入淺出，適合初學者',2020);
INSERT INTO book VALUES(4,'演算法導論','張五','本書詳細介紹了演算法的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(5,'計算機作業系統','張四','本書詳細介紹了作業系統的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(6,'資料結構','張六','本書詳細介紹了資料結構的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(7,'計算機組成','張七','本書詳細介紹了計算機組成的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(8,'C語言程式設計','張四','本書詳細介紹了C語言程式設計的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(9,'web程式開發基礎','張八','本書詳細介紹了web開發的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(10,'web程式開發進階','張四','本書詳細介紹了web開發進階的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(11,'java核心技術卷二','張九','本書詳細介紹了java高級特性的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(12,'spring cloud','張四','本書詳細介紹了spring cloud的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(12,'java并發編程詳解','張九','本書詳細介紹了java并發編程的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(14,'java多執行緒編程實戰指南','張四','本書詳細介紹了java多執行緒的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(15,'深入淺出spring boot','張十','本書詳細介紹了spring boot的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(16,'java并發編程實戰','張二','本書詳細介紹了java并發編程的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(17,'spring入門經典','張四','本書詳細介紹了spring學習的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(18,'設計模式','張三','本書詳細介紹了設計模式的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(19,'編譯原理','張四','本書詳細介紹了編譯原理的相關知識','此書廣度很好',2020);
INSERT INTO book VALUES(20,'C++程式設計','張六','本書詳細介紹了C++程式設計的相關知識','此書廣度很好',2020);
select * from book;

下面我們對它進行操作：
1.通過表的結構我們很容易知道bookid具有唯一性，我們可是將其設定為索引：

然后我們以bookid作為查詢條件時會一步到位(時間復雜度為1)：

如果沒有bookid索引的話，時間復雜度為10

2.從表的結構我們知道，除了bookid之外，書名和作者一起也構成了唯一性：
于是：

然后進行查詢操作：

下面演示最左原則：
下面的查詢不會用到索引：

但下面的查詢會用到：