MySQL學習：
https://blog.csdn.net/2301_80220607/category_12971838.html?spm=1001.2014.3001.5482
前言：
在前面我們基本上已經把MySQL的基礎知識都進行了學習，但是我們之前處理的數據都是十分少的，但是如果當我們的數據量很大的時候，比如一張表中有一百萬個數據，我們要對其中一個進行查詢的時候，效率就會很慢了，這個時候我們就可以借助索引來幫我們完成提高效率的工作，索引簡單點來說就是將數據以特定的數據結構組織起來，從而方便查詢和管理
1. 索引的概念和作用
1.1 索引的概念
索引是數據庫中一種特殊的數據結構，它類似于書籍的目錄，能夠幫助數據庫系統快速定位到表中的特定數據，而不必掃描整個表。索引本質上是通過額外的數據結構（如B+樹、哈希表等）對表中的一個或多個列的值進行排序和組織，從而加速數據檢索操作。
1.2 索引的作用
索引可以提高數據庫的性能，而且索引不用加內存，不用改程序，不用調sql，只需要執行正確的創建索引語句，就可以很方便的幫助我們在大量的數據中進行查找工作，它的本質就是通過特定的數據結果對數據庫中的數據進行管理，主要是通過B+樹
1.3 索引的局限
沒有什么東西是百利而無一弊的，索引雖然可以提高我們的查詢速度，但是插入、更新、和刪除的速度降低為代價的，因為本來這些操作就涉及大量的IO操作，索引的創建會增加IO操作次數，IO操作會大大影響這些操作的效率。同時索引的創建還會消耗額外的內存空間。
但是對于擁有海量數據的數據庫，索引的創建仍是很有必要的。
2. 索引的底層數據結構
關于索引的使用其實還是比較簡單的，關鍵是我們需要明白索引的實現原理是什么，這里我們就講一下索引的底層實現
2.1 MySQL與磁盤交互基本單位
在計算機硬盤的實現中，硬盤是由多個扇面組成的，每個扇面上又被劃成不同的扇區，每個扇區的大小都是512字節，即我們存儲在磁盤中的內容都是以512字節作為存儲單元的。
那這是不是意味著我們所有的I/O操作都是以512字節為單位的呢？
答案其實是否定的。不同的服務進行I/O操作的單位其實是不同的，比如我們的MySQL的操作單位是16KB，那為什么不是512字節而是16KB呢？這其實與MySQL服務的所在層級有關
如圖，MySQL服務實際上是作為一個服務進程在應用層跑動的，所以MySQL并不是直接與磁盤或內存進行交互的，它是通過操作系統（OS）提供的接口與磁盤進行數據的傳送的，雖然磁盤的操作單位為512字節，但是MySQL服務綜合考慮速度、容量等各方面因素，它所選擇的操作單位是16KB
實現方法就是：在操作系統層次和MySQL服務應用層上實際上都有一個文件緩沖區的存在，MySQL服務寫入的內容在將buffer pool寫滿之后傳給操作系統，操作系統再將這部分內容傳給磁盤；同理，磁盤的操作也是這樣的，磁盤將MySQL服務所需數據傳給操作系統，操作系統再將數據傳給MySQL服務
這個基本的存儲單元就叫做MySQL的頁
還遺漏了一個重要的知識點是在MySQL的底層實現中，不同的存儲引擎的實現是不同的，但是不同的地方主要體現在頁的管理上，上面的內容基本上實現還都是一樣的
2.2 建立共識
- MySQL中的數據文件，是以page為單位保存在磁盤中的
- MySQL的CURD操作，都是通過計算，找到對應的插入位置，或者找到對應的要查詢或修改的數據
- 只要涉及到運算，就需要CPU參與，為了方便CPU的參與，一定要先將數據移動到內存中
- 所以在特定的時間內，數據一定是磁盤中有，內存中也有。后續操作完內存數據之后，以特定的刷新策略，刷新到磁盤。而這時，就涉及到磁盤和內存的數據交互，也就是IO了。而此時IO的基本單位就是Page
- 為了更好的進行上面的操作， MySQL 服務器在內存中運行的時候，在服務器內部，就申請了被稱為 BufferPool 的的大內存空間，來進行各種緩存。其實就是很大的內存空間，來和磁盤數據進行IO交互。
- 為了提高效率，一定要盡可能的減少系統和磁盤IO的次數
2.3 索引的理解
建立測試表：
create table if not exists user (
id int primary key, --一定要添加主鍵哦，只有這樣才會默認生成主鍵索引
age int not null,
name varchar(16) not null
);
插入多條測試記錄：
--插入多條記錄，注意，我們并沒有按照主鍵的大小順序插入哦
mysql> insert into user (id, age, name) values(3, 18, '楊過');
Query OK, 1 row affected (0.01 sec)mysql> insert into user (id, age, name) values(4, 16, '小龍女');
Query OK, 1 row affected (0.00 sec)mysql> insert into user (id, age, name) values(2, 26, '黃蓉');
Query OK, 1 row affected (0.01 sec)mysql> insert into user (id, age, name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)mysql> insert into user (id, age, name) values(1, 56, '歐陽鋒');
Query OK, 1 row affected (0.00 sec)查看插入結果：
select * from user;
觀察這個插入結果，我們可以發現，我們并沒有按照id順序來插入數據，但是最后卻發現插入后的數據是按照id來排序的，id是主鍵，其實原因是建表時被設置為主鍵的列會默認建立索引，索引通過這樣排序的方式就可以幫助我們更快的查找到我們想要的數據
但是僅僅知道這些還是不夠的，我們需要知道索引工作的原理，下面我們就來看一下
2.4 索引的底層實現
關于page的認識
上面我們講過MySQL與磁盤的交互單位為page（16KB），但是為什么呢？為什么不用多少，加載多少呢？
這其實是為了提高效率，比如上面我們插入的數據，我們現在如果要查找id=1的記錄，按照用多少取多少的方式，我們就需要直接把它從磁盤加載到MySQL服務端，進行依次I/O操作，如果又想查id=2的記錄，就有需要再進行這樣一次的I/O
需要注意的是這樣的I/O操作在計算機運行中，會消耗大量的空間，所以為了提高效率我們必須想辦法減少I/O操作的次數，所以我們就可以一次直接I/O更多的數據（page），比如把五條記錄全部取了，這樣不管要哪條記錄，我們的服務端都可以直接在自己的緩沖區中找就可以了，這樣就節省的大量的時間
當然并不是每次要取的數據都能在同一個page頁，但是根據局部性原理，還是能夠保證我們在大部分情況下都是效率更高的
理解單個page
上面講了page的概念后，實際上我們就應該認識到page作為MySQL的存儲單元一定會伴隨著許多的設計的。
MySQL中有很多表，這些表中存放著大量的數據，我們可以理解成這些表中的數據是存放在一個或多個page中的，由于大量page表的存在，所以我們需要對page進行組織管理
如上，就是page的基本構成，page中的數據是以鏈表的形式存放的，同時page自身也是通過鏈表的形式進行組織的，它里面有兩個指針分別指向前一個page和后一個page
理解多個page
- 通過上面的分析，我們知道，上面頁模式中，只有一個功能，就是在查詢某條數據的時候直接將一整頁的數據加載到內存中，以減少硬盤IO次數，從而提高性能。但是，我們也可以看到，現在的頁模式內部，實際上是采用了鏈表的結構，前一條數據指向后一條數據，本質上還是通過數據的逐條比較來取出特定的數據。
- 如果有 1 千萬條數據，一定需要多個 Page 來保存 1 千萬條數據，多個 Page 彼此使用雙鏈表鏈接起來，而且每個 Page內部的數據也是基于鏈表的。那么，查找特定一條記錄，也一定是線性查找。這效率也太低了。
頁目錄
現在有一本書，我們找到其中某些內容的時候，一定是先看目錄，找到這些內容對應的頁數，然后再根據頁數再去找這部分內容，這樣可以幫助我們節省很多時間。
頁目錄會占用幾頁，這是一種空間換時間的做法，但是仍然是十分值得去做的
page頁目錄
為了方便我們的page進行高效查找，我們可以對page頁也進行添加目錄操作，即可以添加在page內對page中內容進行管理，也可以添加在page外對page進行管理
添加在page內
那么當前，在一個Page內部，我們引入了目錄。比如，我們要查找id=4記錄，之前必須線性遍歷4次，才能拿到結果。現在直接通過目錄2[3]，直接進行定位新的起始位置，提高了效率。現在我們可以再次正式回答上面的問題了，為何通過鍵值 MySQL 會自動排序？
- 可以很方便引入目錄
添加在page外
MySQL 中每一頁的大小只有 16KB ，單個Page大小固定，所以隨著數據量不斷增大， 16KB 不可能存下所有的數據，那么必定會有多個頁來存儲數據。
我們前面講過page頁中會有兩個指針的，這兩個指針就是幫助我們建立這樣的雙鏈表的結構的
在單表數據不斷被插入的情況下， MySQL 會在容量不足的時候，自動開辟新的Page來保存新的數據，然后通過指針的方式，將所有的Page組織起來。
需要注意，上面的圖，是理想結構，大家也知道，目前要保證整體有序，那么新插入的數據，不一定會在新Page上面，這里僅僅做演示。
這樣，我們就可以通過多個Page遍歷，Page內部通過目錄來快速定位數據。可是，貌似這樣也有效率問題，在Page之間，也是需要 MySQL 遍歷的，遍歷意味著依舊需要進行大量的IO，將下一個Page加載到內存，進行線性檢測。這樣就顯得我們之前的Page內部的目錄，有點杯水車薪了。
那么解決方法是什么呢？解決方法，其實就是我們之前的思路，給page也帶一個目錄
存在一個目錄頁來管理頁目錄，目錄頁中的數據存放的就是指向的那一頁中最小的數據。有數據，就可通過比較，找到該訪問那個Page，進而通過指針，找到下一個Page。
其實目錄頁的本質也是頁，普通頁中存的數據是用戶數據，而目錄頁中存的數據是普通頁的地址。
可是，我們每次檢索數據的時候，該從哪里開始呢？雖然頂層的目錄頁少了，但是還要遍歷啊？不用擔心，可以在加目錄頁
我們觀察就可以發現這就是數據結構中的B+樹啊！！至此，我們就給我們的user表建立了主鍵索引。現在隨便找一個id=？的數據，我們會發現查詢速度會快很多。
復盤一下
- Page分為目錄頁和數據頁。目錄頁只放各個下級Page的最小鍵值。
- 查找的時候，自頂向下找，只需要加載部分目錄頁到內存，即可完成算法的整個查找過程，大大減少了IO次數
以上就是索引的底層實現，但是需要注意的是我們講的這種B+樹的底層實現方式，適用的主要是MyISAM存儲引擎，不同的存儲引擎的底層實現可能是不同的，比如我們還常用的另一種存儲引擎InnoDB就是常用B樹來作為底層數據結構，用B樹實現的存儲引擎它的用戶數據和索引數據不會分離，被稱為聚簇索引；而用B+樹實現的索引類型一般為非聚簇索引
3. 索引操作
3.1 創建索引
3.1.1 創建主鍵索引
- 第一種方法
-- 在創建表的時候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));- 第二種方法
-- 在創建表的最后，指定某列或某幾列為主鍵索引
create table user2(id int, name varchar(30), primary key(id));- 第三種方法
create table user3(id int, name varchar(30));
-- 創建表以后再添加主鍵
alter table user3 add primary key(id);主鍵索引的特點：
- 一個表中，最多有一個主鍵索引，當然可以使符合主鍵
- 主鍵索引的效率高（主鍵不可重復）
- 創建主鍵索引的列，它的值不能為null，且不能重復
- 主鍵索引的列基本上是int
3.1.2?創建唯一索引
- 第一種方式
-- 在表定義時，在某列后直接指定unique唯一屬性。
create table user4(id int primary key, name varchar(30) unique);- 第二種方式
-- 創建表時，在表的后面指定某列或某幾列為unique
create table user5(id int primary key, name varchar(30), unique(name));- 第三種方式
create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);唯一索引的特點：
- 一個表中，可以有多個唯一索引
- 查詢效率高
- 如果在某一列建立唯一索引，必須保證這列不能有重復數據
- 如果一個唯一索引上指定not null，等價于主鍵索引
3.1.3?創建普通索引
- 第一種方式
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定義最后，指定某列為索引
);- 第二種方式
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); --創建完表以后指定某列為普通索引- 第三種方式
create table user10(id int primary key, name varchar(20), email varchar(30));
-- 創建一個索引名為 idx_name 的索引
create index idx_name on user10(name);普通索引的創建：
- 一個表中可以有多個普通索引，普通索引在實際開發中用的比較多
- 如果某列需要創建索引，但是該列有重復的值，那么我們就應該使用普通索引
3.1.4?創建全文索引
當對文章字段或有大量文字的字段進行檢索時，會使用到全文索引。MySQL提供全文索引機制，但是有要求，要求表的存儲引擎必須是MyISAM，而且默認的全文索引支持英文，不支持中文。如果對中文進行全文檢索，可以使用sphinx的中文版(coreseek)。
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');- 查找有沒有database數據
如果使用如下查詢方式，雖然查詢出數據，但是沒有使用到全文索引
select * from articles where body like '%database%';
可以用explain工具看一下，是否使用到索引
explain select * from articles where body like '%database%'\G
- 如何使用全文索引
SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
通過explain來分析這個sql語句
explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')\G
