一、沒有索引，可能會(huì)有什么問題
索引：提高數(shù)據(jù)庫的性能，索引是物美價(jià)廉的東西了。不用加內(nèi)存，不用改程序，不用調(diào)sql，只要執(zhí)行正確的 create index ，查詢速度就可能提高成百上千倍。但是天下沒有免費(fèi)的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價(jià)的，這些寫操作，增加了大量的IO。所以它的價(jià)值，在于提高一個(gè)海量數(shù)據(jù)的檢索速度。
常見索引分為：
主鍵索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)–解決中子文索引問題。
案例：
先整一個(gè)海量表，在查詢的時(shí)候，看看沒有索引時(shí)有什么問題？
drop database if exists `bit_index`;
create database if not exists `bit_index` default character set utf8;
use `bit_index`;
-- 構(gòu)建一個(gè)8000000條記錄的數(shù)據(jù)
-- 構(gòu)建的海量表數(shù)據(jù)需要有差異性，所以使用存儲(chǔ)過程來創(chuàng)建， 拷貝下面代碼就可以了，暫時(shí)不用理解
-- 產(chǎn)生隨機(jī)字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
-- 產(chǎn)生隨機(jī)數(shù)字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;
-- 創(chuàng)建存儲(chǔ)過程，向雇員表添加海量數(shù)據(jù)
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;
-- 雇員表
CREATE TABLE `EMP` (
`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號(hào)',
`ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',
`job` varchar(9) DEFAULT NULL COMMENT '雇員職位',
`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎo)編號(hào)',
`hiredate` datetime DEFAULT NULL COMMENT '雇傭時(shí)間',
`sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪',
`comm` decimal(7,2) DEFAULT NULL COMMENT '獎(jiǎng)金',
`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門編號(hào)'
);
-- 執(zhí)行存儲(chǔ)過程，添加8000000條記錄
call insert_emp(100001, 8000000);
上面的內(nèi)容創(chuàng)建了一個(gè)表，插入了800 0000條數(shù)據(jù)，我們只需要將上面的代碼放入一個(gè)文件中，然后使用sources指令即可創(chuàng)建，使用call insert_emp(100001, 8000000)即可插入800 0000條數(shù)據(jù)。到此，已經(jīng)創(chuàng)建出了海量數(shù)據(jù)的表了。
source 文件路徑+文件名
mysql> source /home/hdp/index_data.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (7 min 56.80 sec)
mysql>
這里我們花了7分鐘將8000000條數(shù)據(jù)插入到表中了。
查詢員工編號(hào)為998877的員工
select * from EMP where empno=998877;
這里我們進(jìn)行多次查詢，插入的時(shí)間在4.8秒左右，這還是在本機(jī)一個(gè)人來操作，在實(shí)際項(xiàng)目中，如果放在公網(wǎng)中，假如同時(shí)有1000個(gè)人并發(fā)查詢，那很可能就死機(jī)。
解決方法，創(chuàng)建索引
alter table EMP add index(empno);
創(chuàng)建索引會(huì)改變存儲(chǔ)數(shù)據(jù)的方式，即存儲(chǔ)的數(shù)據(jù)結(jié)構(gòu)，索引在建立索引的時(shí)候也想需要花費(fèi)一定的時(shí)間的
換一個(gè)員工編號(hào)，測(cè)試看看查詢時(shí)間
select * from EMP where empno=123456;
這里我們看到，每次查詢的時(shí)間都是0秒，查詢的速度明顯的提高了
二、認(rèn)識(shí)磁盤
1.MySQL與存儲(chǔ)
MySQL 給用戶提供存儲(chǔ)服務(wù)，而存儲(chǔ)的都是數(shù)據(jù)，數(shù)據(jù)在磁盤這個(gè)外設(shè)當(dāng)中。磁盤是計(jì)算機(jī)中的一個(gè)機(jī)械設(shè)備，相比于計(jì)算機(jī)其他電子元件，磁盤效率是比較低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一個(gè)重要話題。
MySQL的服務(wù)器，本質(zhì)是存儲(chǔ)在內(nèi)存中的，所有的數(shù)據(jù)庫的CURD操作，全部都是在內(nèi)存中進(jìn)行的–索引也是如此
提高算法效率的因素：1.組織數(shù)據(jù)的方式 2.算法本身
2.磁盤
先來研究一下磁盤：
在看看磁盤中一個(gè)盤片
從上圖可以看出來，在半徑方向上，距離圓心越近，扇區(qū)越小，距離圓心越遠(yuǎn)，扇區(qū)越大
那么，所有扇區(qū)都是默認(rèn)512字節(jié)嗎？目前是的，我們也這樣認(rèn)為。因?yàn)楸ＷC一個(gè)扇區(qū)多大，是由比特位密度決定的。
不過最新的磁盤技術(shù)，已經(jīng)慢慢的讓扇區(qū)大小不同了，不過我們現(xiàn)在暫時(shí)不考慮。
扇區(qū)
數(shù)據(jù)庫文件，本質(zhì)其實(shí)就是保存在磁盤的盤片當(dāng)中。也就是上面的一個(gè)個(gè)小格子中，就是我們經(jīng)常所說的扇區(qū)。當(dāng)然，數(shù)據(jù)庫文件很大，也很多，一定需要占據(jù)多個(gè)扇區(qū)。
我們?cè)谑褂肔inux，所看到的大部分目錄或者文件，其實(shí)就是保存在硬盤當(dāng)中的。(當(dāng)然，有一些內(nèi)存文件系統(tǒng)，如： proc ， sys 之類，我們不考慮)
數(shù)據(jù)庫文件，本質(zhì)其實(shí)就是保存在磁盤的盤片當(dāng)中，就是一個(gè)一個(gè)的文件
我們可以使用如下指令查看目前MySQL中的文件
ls /var/lib/mysql -l
所以，最基本的，找到一個(gè)文件的全部，本質(zhì)，就是在磁盤找到所有保存文件的扇區(qū)。而我們能夠定位任何一個(gè)扇區(qū)，那么便能找到所有扇區(qū)，因?yàn)椴檎曳绞绞且粯拥摹?/p>
定位扇區(qū)
柱面(磁道): 多盤磁盤，每盤都是雙面，大小完全相等。那么同半徑的磁道，整體上便構(gòu)成了一個(gè)柱面
每個(gè)盤面都有一個(gè)磁頭，那么磁頭和盤面的對(duì)應(yīng)關(guān)系便是1對(duì)1的
所以，我們只需要知道，磁頭（Heads）、柱面(Cylinder)(等價(jià)于磁道)、扇區(qū)(Sector)對(duì)應(yīng)的編號(hào)。即可在磁盤上定位所要訪問的扇區(qū)。這種磁盤數(shù)據(jù)定位方式叫做 CHS 。不過實(shí)際系統(tǒng)軟件使用的并不是 CHS （但是硬件是），而是 LBA ，一種線性地址，可以想象成虛擬地址與物理地址。系統(tǒng)將 LBA 地址最后會(huì)轉(zhuǎn)化成為 CHS ，交給磁盤去進(jìn)行數(shù)據(jù)讀取。不過，我們現(xiàn)在不關(guān)心轉(zhuǎn)化細(xì)節(jié)，知道這個(gè)東西，讓我們邏輯自洽起來即可。
結(jié)論
我們現(xiàn)在已經(jīng)能夠在硬件層面定位，任何一個(gè)基本數(shù)據(jù)塊了(扇區(qū))。那么在系統(tǒng)軟件上，就直接按照扇區(qū)(512字節(jié)，部分4096字節(jié)),進(jìn)行IO交互嗎？不是，原因如下：
如果操作系統(tǒng)直接使用硬件提供的數(shù)據(jù)大小進(jìn)行交互，那么系統(tǒng)的IO代碼，就和硬件強(qiáng)相關(guān)，換言之，如果硬件發(fā)生變化，系統(tǒng)必須跟著變化
從目前來看，單次IO 512字節(jié)，還是太小了。IO單位小，意味著讀取同樣的數(shù)據(jù)內(nèi)容，需要進(jìn)行多次磁盤訪問，會(huì)帶來效率的降低。
之前學(xué)習(xí)文件系統(tǒng)，就是在磁盤的基本結(jié)構(gòu)下建立的，文件系統(tǒng)讀取基本單位，就不是扇區(qū)，而是數(shù)據(jù)塊。
故，系統(tǒng)讀取磁盤，是以塊為單位的，基本單位是 4KB 。
磁盤隨機(jī)訪問(Random Access)與連續(xù)訪問(Sequential Access)
隨機(jī)訪問：本次IO所給出的扇區(qū)地址和上次IO給出扇區(qū)地址不連續(xù)，這樣的話磁頭在兩次IO操作之間需要作比較大的移動(dòng)動(dòng)作才能重新開始讀/寫數(shù)據(jù)。
連續(xù)訪問：如果當(dāng)次IO給出的扇區(qū)地址與上次IO結(jié)束的扇區(qū)地址是連續(xù)的，那磁頭就能很快的開始這次IO操作，這樣的多個(gè)IO操作稱為連續(xù)訪問。
因此盡管相鄰的兩次IO操作在同一時(shí)刻發(fā)出，但如果它們的請(qǐng)求的扇區(qū)地址相差很大的話也只能稱為隨機(jī)訪問，而非連續(xù)訪問。
磁盤是通過機(jī)械運(yùn)動(dòng)進(jìn)行尋址的，隨機(jī)訪問不需要過多的定位，故效率比較高。
三、MySQL與磁盤交互基本單位
而 MySQL 作為一款應(yīng)用軟件，可以想象成一種特殊的文件系統(tǒng)。它有著更高的IO場(chǎng)景，所以，為了提高基本的IO效率， MySQL 進(jìn)行IO的基本單位是 16KB (后面統(tǒng)一使用 InnoDB 存儲(chǔ)引擎講解)
我們可以如下指令查看MYSQL進(jìn)行IO的基本單位大小：
SHOW GLOBAL STATUS LIKE 'innodb_page_size';
16384 = 16*1024 所以MYSQL進(jìn)行IO的基本大小為16KB
也就是說，磁盤這個(gè)硬件設(shè)備的基本單位是 512 字節(jié)，而 MySQL InnoDB引擎 使用 16KB 進(jìn)行IO交互。即， MySQL 和磁盤進(jìn)行數(shù)據(jù)交互的基本單位是 16KB 。這個(gè)基本數(shù)據(jù)單元，在 MySQL 這里叫做page（注意和系統(tǒng)的page區(qū)分）
MySQL進(jìn)行IO的基本單位是16KB，MySQL服務(wù)是在運(yùn)用層，并不是MySQL直接與磁盤進(jìn)行16KB的IO交互，因?yàn)檐浖荒軌蛑苯釉L問硬件，而是需要通過操作系統(tǒng)來訪問硬件，而操作系統(tǒng)與硬件IO的基本單位是4KB，所以MySQL IO的時(shí)候，與操作系統(tǒng)IO的基本單位是16KB，操作系統(tǒng)再與磁盤進(jìn)行4次IO即可，讀取數(shù)據(jù)是這樣，將數(shù)據(jù)刷新到磁盤的時(shí)候，操作系統(tǒng)時(shí)候fsync函數(shù)將數(shù)據(jù)刷新到磁盤。
總結(jié)：
1.MySQL 中的數(shù)據(jù)文件，是以page為單位保存在磁盤當(dāng)中的。
2.MySQL 的 CURD 操作，都需要通過計(jì)算，找到對(duì)應(yīng)的插入位置，或者找到對(duì)應(yīng)要修改或者查詢的數(shù)據(jù)。
3.而只要涉及計(jì)算，就需要CPU參與，而為了便于CPU參與，一定要能夠先將數(shù)據(jù)移動(dòng)到內(nèi)存當(dāng)中。
4.所以在特定時(shí)間內(nèi)，數(shù)據(jù)一定是磁盤中有，內(nèi)存中也有。后續(xù)操作完內(nèi)存數(shù)據(jù)之后，以特定的刷新策略，刷新到磁盤。而這時(shí)，就涉及到磁盤和內(nèi)存的數(shù)據(jù)交互，也就是IO了。而此時(shí)IO的基本單位就是Page。
5.為了更好的進(jìn)行上面的操作， MySQL 服務(wù)器在內(nèi)存中運(yùn)行的時(shí)候，在服務(wù)器內(nèi)部，就申請(qǐng)了被稱為 Buffer Pool 的的大內(nèi)存空間，來進(jìn)行各種緩存。其實(shí)就是很大的內(nèi)存空間，來和磁盤數(shù)據(jù)進(jìn)行IO交互。
6.為何更高的效率，一定要盡可能的減少系統(tǒng)和磁盤IO的次數(shù)
四、索引的理解
建立測(cè)試表
一定要添加主鍵，只有這樣才會(huì)默認(rèn)生成主鍵索引
create table if not exists user (
id int primary key,
age int not null,
name varchar(16) not null
);
默認(rèn)就是InnoDB存儲(chǔ)引擎
mysql> show create table user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
插入多條記錄
插入多條記錄，注意，我們并沒有按照主鍵的大小順序插入
insert into user (id, age, name) values(3, 18, '楊過');
insert into user (id, age, name) values(4, 16, '小龍女');
insert into user (id, age, name) values(2, 26, '黃蓉');
insert into user (id, age, name) values(5, 36, '郭靖');
insert into user (id, age, name) values(1, 56, '歐陽鋒');
查看插入結(jié)果
select * from user;
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 1 | 56 | 歐陽鋒 |
| 2 | 26 | 黃蓉 |
| 3 | 18 | 楊過 |
| 4 | 16 | 小龍女 |
| 5 | 36 | 郭靖 |
+----+-----+-----------+
5 rows in set (0.00 sec)
發(fā)現(xiàn)竟然默認(rèn)是有序的！是誰干的呢？排序有什么好處呢？這是MySQL自己做的，這樣可以很方便引入目錄
我們先理解為什么IO交互要是Page
為何MySQL和磁盤進(jìn)行IO交互的時(shí)候，要采用Page的方案進(jìn)行交互呢?用多少，加載多少不香嗎?
如上面的5條記錄，如果MySQL要查找id=2的記錄，第一次加載id=1，第二次加載id=2，一次一條記錄，那么就需要2次IO。如果要找id=5，那么就需要5次IO。
但，如果這5條(或者更多)都被保存在一個(gè)Page中(16KB，能保存很多記錄),那么第一次IO查找id=2的時(shí)候，整個(gè)Page會(huì)被加載到MySQL的Buffer Pool中，這里完成了一次IO。但是往后如果在查找id=1,3,4,5等，完全不需要進(jìn)行IO了，而是直接在內(nèi)存中進(jìn)行了。所以，就在單Page里面，大大減少了IO的次數(shù)。
你怎么保證，用戶一定下次找的數(shù)據(jù)，就在這個(gè)Page里面？我們不能嚴(yán)格保證，但是有很大概率，因?yàn)橛芯植啃栽怼?/p>
往往IO效率低下的最主要矛盾不是IO單次數(shù)據(jù)量的大小，而是IO的次數(shù)
下面我們重新談?wù)損age
mysql內(nèi)部，一定需要并且存在大量的page，也就決定了，mysql必須要將多個(gè)同時(shí)存在的page管理起來，要管理所有的mysql內(nèi)部的page，需要先描述后組織。
所有不要簡(jiǎn)單的將page認(rèn)為是一個(gè)內(nèi)存塊，page內(nèi)部也必須寫入對(duì)應(yīng)的管理信息
struct page
{
struct page* next;
struct page* prev;
char buffer[NUM];
}; --16KB
我們只需要new 一個(gè)page即可，將所有的page使用"鏈表"的形式管理起來--在buffer pool 內(nèi)部，對(duì)mysql中的page進(jìn)行了一個(gè)建模
理解單個(gè)Page
MySQL 中要管理很多數(shù)據(jù)表文件，而要管理好這些文件，就需要 先描述，在組織 ,我們目前可以簡(jiǎn)單理解成一個(gè)個(gè)獨(dú)立文件是有一個(gè)或者多個(gè)Page構(gòu)成的
不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 構(gòu)成雙向鏈表
因?yàn)橛兄麈I的問題， MySQL 會(huì)默認(rèn)按照主鍵給我們的數(shù)據(jù)進(jìn)行排序，從上面的Page內(nèi)數(shù)據(jù)記錄可以看出，數(shù)據(jù)是有序且彼此關(guān)聯(lián)的。
為什么數(shù)據(jù)庫在插入數(shù)據(jù)時(shí)要對(duì)其進(jìn)行排序呢？我們按正常順序插入數(shù)據(jù)不是也挺好的嗎？
插入數(shù)據(jù)時(shí)排序的目的，就是優(yōu)化查詢的效率。
頁內(nèi)部存放數(shù)據(jù)的模塊，實(shí)質(zhì)上也是一個(gè)鏈表的結(jié)構(gòu)，鏈表的特點(diǎn)也就是增刪快，查詢修改慢，所以優(yōu)化查詢的效率是必須的。
正式因?yàn)橛行?，在查找的時(shí)候，從頭到后都是有效查找，沒有任何一個(gè)查找是浪費(fèi)的，而且，如果運(yùn)氣好，是可以提前結(jié)束查找過程的
理解多個(gè)Page
通過上面的分析，我們知道，上面頁模式中，只有一個(gè)功能，就是在查詢某條數(shù)據(jù)的時(shí)候直接將一整頁的數(shù)據(jù)加載到內(nèi)存中，以減少硬盤IO次數(shù)，從而提高性能。但是，我們也可以看到，現(xiàn)在的頁模式內(nèi)部，實(shí)際上是采用了鏈表的結(jié)構(gòu)，前一條數(shù)據(jù)指向后一條數(shù)據(jù)，本質(zhì)上還是通過數(shù)據(jù)的逐條比較來取出特定的數(shù)據(jù)。
如果有1千萬條數(shù)據(jù)，一定需要多個(gè)Page來保存1千萬條數(shù)據(jù)，多個(gè)Page彼此使用雙鏈表鏈接起來，而且每個(gè)Page內(nèi)部的數(shù)據(jù)也是基于鏈表的。那么，查找特定一條記錄，也一定是線性查找。這效率也太低了。
頁目錄
我們?cè)诳础蹲T浩強(qiáng)C程序設(shè)計(jì)》這本書的時(shí)候，如果我們要看<指針章節(jié)>，找到該章節(jié)有兩種做法
從頭逐頁的向后翻，直到找到目標(biāo)內(nèi)容。通過書提供的目錄，發(fā)現(xiàn)指針章節(jié)在234頁(假設(shè))，那么我們便直接翻到234頁。同時(shí)，查找目錄的方案，可以順序找，不過因?yàn)槟夸浛隙ㄉ?，所以可以快速提高定位本質(zhì)上，書中的目錄，是多花了紙張的，但是卻提高了效率所以，目錄，是一種“空間換時(shí)間的做法”
單頁情況
針對(duì)上面的單頁P(yáng)age，我們能否也引入目錄呢？當(dāng)然可以
那么當(dāng)前，在一個(gè)Page內(nèi)部，我們引入了目錄。比如，我們要查找id=4記錄，之前必須線性遍歷4次，才能拿到結(jié)果。現(xiàn)在直接通過目錄2[3]，直接進(jìn)行定位新的起始位置，提高了效率?，F(xiàn)在我們可以再次正式回答上面的問題了，為何通過鍵值 MySQL 會(huì)自動(dòng)排序？可以很方便引入目錄
多頁情況
MySQL 中每一頁的大小只有 16KB ，單個(gè)Page大小固定，所以隨著數(shù)據(jù)量不斷增大， 16KB 不可能存下所有的數(shù)據(jù)，那么必定會(huì)有多個(gè)頁來存儲(chǔ)數(shù)據(jù)。
在單表數(shù)據(jù)不斷被插入的情況下， MySQL 會(huì)在容量不足的時(shí)候，自動(dòng)開辟新的Page來保存新的數(shù)據(jù)，然后通過指針的方式，將所有的Page組織起來。
需要注意，上面的圖，是理想結(jié)構(gòu)，大家也知道，目前要保證整體有序，那么新插入的數(shù)據(jù)，不一定會(huì)在新Page上面，這里僅僅做演示。這樣，我們就可以通過多個(gè)Page遍歷，Page內(nèi)部通過目錄來快速定位數(shù)據(jù)?？墒?，貌似這樣也有效率問題，在Page之間，也是需要 MySQL 遍歷的，遍歷意味著依舊需要進(jìn)行大量的IO，將下一個(gè)Page加載到內(nèi)存，進(jìn)行線性檢測(cè)。這樣就顯得我們之前的Page內(nèi)部的目錄，有點(diǎn)杯水車薪了。
那么如何解決呢？解決方案，其實(shí)就是我們之前的思路，給Page也帶上目錄。
使用一個(gè)目錄項(xiàng)來指向某一頁，而這個(gè)目錄項(xiàng)存放的就是將要指向的頁中存放的最小數(shù)據(jù)的鍵值。和頁內(nèi)目錄不同的地方在于，這種目錄管理的級(jí)別是頁，而頁內(nèi)目錄管理的級(jí)別是行。其中，每個(gè)目錄項(xiàng)的構(gòu)成是：鍵值+指針。圖中沒有畫全。
存在一個(gè)目錄頁來管理頁目錄，目錄頁中的數(shù)據(jù)存放的就是指向的那一頁中最小的數(shù)據(jù)。有數(shù)據(jù)，就可通過比較，找到該訪問那個(gè)Page，進(jìn)而通過指針，找到下一個(gè)Page。
其實(shí)目錄頁的本質(zhì)也是頁，普通頁中存的數(shù)據(jù)是用戶數(shù)據(jù)，而目錄頁中存的數(shù)據(jù)是普通頁的地址。
可是，我們每次檢索數(shù)據(jù)的時(shí)候，該從哪里開始呢？雖然頂層的目錄頁少了，但是還要遍歷??？不用擔(dān)心，可以在加目錄頁
上圖為mysql innodb 下的索引結(jié)構(gòu)，一般我們建表的時(shí)候，就是在該結(jié)構(gòu)下進(jìn)行CURD操作，我們表沒有主鍵也是這樣的，此時(shí)mysql會(huì)創(chuàng)建一個(gè)虛擬的主鍵。
索引的本質(zhì)就是數(shù)據(jù)結(jié)構(gòu)–B+樹
這貨就是傳說中的B+樹??！沒錯(cuò)，至此，我們已經(jīng)給我們的表user構(gòu)建完了主鍵索引。
隨便找一個(gè)id=？我們發(fā)現(xiàn)，現(xiàn)在查找的Page數(shù)一定減少了，也就意味著IO次數(shù)減少了，那么效率也就提高了。
Page分為目錄頁和數(shù)據(jù)頁。目錄頁只放各個(gè)下級(jí)Page的最小鍵值。查找的時(shí)候，自定向下找，只需要加載部分目錄頁到內(nèi)存，即可完成算法的整個(gè)查找過程，大大減少了IO次數(shù)
1.葉子節(jié)點(diǎn)保存有數(shù)據(jù)，路上節(jié)點(diǎn)沒有，非葉子節(jié)點(diǎn)，不要數(shù)據(jù)，只要目錄項(xiàng)，非葉子結(jié)點(diǎn)，不存數(shù)據(jù)，可以存儲(chǔ)更多的目錄項(xiàng)，目錄頁，可以管理更多的葉子page，這棵樹一定是一個(gè)矮胖的樹
每一個(gè)節(jié)點(diǎn)都有目錄項(xiàng)，可以大大提高搜索效率，路上的節(jié)點(diǎn)很少，找到目標(biāo)數(shù)據(jù)只要更少的page IO次數(shù)就更少，IO層面，提高了效率。二者結(jié)合就整體提高了效率
2.葉子節(jié)點(diǎn)全部用鏈表級(jí)聯(lián)起來，這是B+的特點(diǎn)，此外我們也比較希望進(jìn)行范圍查找
InnoDB 在建立索引結(jié)構(gòu)來管理數(shù)據(jù)的時(shí)候，其他數(shù)據(jù)結(jié)構(gòu)為何不行？
鏈表？線性遍歷
二叉搜索樹？退化問題，可能退化成為線性結(jié)構(gòu)
AVL &&紅黑樹？雖然是平衡或者近似平衡，但是畢竟是二叉結(jié)構(gòu)，相比較多階B+，意味著樹整體過高，大家都是自頂向下找，層高越低，意味著系統(tǒng)與硬盤更少的IO Page交互。雖然你很秀，但是有更秀的。
Hash？官方的索引實(shí)現(xiàn)方式中， MySQL 是支持HASH的，不過 InnoDB 和 MyISAM 并不支持.Hash跟進(jìn)其算法特征，決定了雖然有時(shí)候也很快(O(1))，不過，在面對(duì)范圍查找就明顯不行，另外還有其他差別，有興趣可以查一下。
B樹？最值得比較的是 InnoDB 為何不用B樹作為底層索引？
數(shù)據(jù)結(jié)構(gòu)演示鏈接：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B+ vs B
B樹
B+樹
目前這兩棵樹，對(duì)我們最有意義的區(qū)別是：
B樹節(jié)點(diǎn)，既有數(shù)據(jù)，又有Page指針，而B+，只有葉子節(jié)點(diǎn)有數(shù)據(jù)，其他目錄頁，只有鍵值和Page指針
B+葉子節(jié)點(diǎn)，全部相連，而B沒有
為何選擇B+
節(jié)點(diǎn)不存儲(chǔ)data，這樣一個(gè)節(jié)點(diǎn)就可以存儲(chǔ)更多的key?？梢允沟脴涓?，所以IO操作次數(shù)更少。
葉子節(jié)點(diǎn)相連，更便于進(jìn)行范圍查找
聚簇索引 VS 非聚簇索引
MyISAM 存儲(chǔ)引擎-主鍵索引
MyISAM 引擎同樣使用B+樹作為索引結(jié)果，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。下圖為 MyISAM表的主索引， Col1 為主鍵。
其中， MyISAM 最大的特點(diǎn)是，將索引Page和數(shù)據(jù)Page分離，也就是葉子節(jié)點(diǎn)沒有數(shù)據(jù)，只有對(duì)應(yīng)數(shù)據(jù)的地址。
相較于 InnoDB 索引， InnoDB 是將索引和數(shù)據(jù)放在一起的。
我們分別以InnoDB 和MyISAM 作為存儲(chǔ)引擎創(chuàng)建兩張表，觀察其創(chuàng)建的文件
// 文件路徑
cd /var/lib/mysql/數(shù)據(jù)庫名/
InnoDB
tset1.frm --表結(jié)構(gòu)數(shù)據(jù)
tset1.ibd --該表對(duì)應(yīng)的主鍵索引和用戶數(shù)據(jù)，雖然現(xiàn)在一行數(shù)據(jù)沒有，但是該表并不為0，因?yàn)橛兄麈I索引數(shù)據(jù)
其中， InnoDB 這種用戶數(shù)據(jù)與索引數(shù)據(jù)在一起索引方案，叫做聚簇索引
MyISAM
test2.frm --表結(jié)構(gòu)數(shù)據(jù)
test2.MYD --該表對(duì)應(yīng)的數(shù)據(jù)，當(dāng)前沒有數(shù)據(jù)，所以是0
test2.MYI --該表對(duì)應(yīng)的主鍵索引數(shù)據(jù)
其中， MyISAM 這種用戶數(shù)據(jù)與索引數(shù)據(jù)分離的索引方案，叫做非聚簇索引
當(dāng)然， MySQL 除了默認(rèn)會(huì)建立主鍵索引外，我們用戶也有可能建立按照其他列信息建立的索引，一般這種索引可以叫做輔助（普通）索引。
對(duì)于 MyISAM ,建立輔助（普通）索引和主鍵索引沒有差別，無非就是主鍵不能重復(fù)，而非主鍵可重復(fù)。
下圖就是基于 MyISAM 的 Col2 建立的索引，和主鍵索引沒有差別
同樣， InnoDB 除了主鍵索引，用戶也會(huì)建立輔助（普通）索引，我們以上表中的 Col3 建立對(duì)應(yīng)的輔助
索引如下圖：
可以看到， InnoDB 的非主鍵索引中葉子節(jié)點(diǎn)并沒有數(shù)據(jù)，而只有對(duì)應(yīng)記錄的key值。
所以通過輔助（普通）索引，找到目標(biāo)記錄，需要兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。這種過程，就叫做回表查詢
為何 InnoDB 針對(duì)這種輔助（普通）索引的場(chǎng)景，不給葉子節(jié)點(diǎn)也附上數(shù)據(jù)呢？原因就是太浪費(fèi)空間了。
五、索引操作
1.創(chuàng)建主鍵索引
第一種方式—在創(chuàng)建表的時(shí)候，直接在字段名后指定 primary key
create table user1(
id int primary key,
name varchar(32)
);
第二種方式-- 在創(chuàng)建表的最后，指定某列或某幾列為主鍵索引
create table user2(
id int,
name varchar(32),
primary key(id)
);
第三種方式-- 創(chuàng)建表以后再添加主鍵
create table user3(
id int,
name varchar(32)
);
alter table user3 add primary key(id);
主鍵索引的特點(diǎn)：
一個(gè)表中，最多有一個(gè)主鍵索引，當(dāng)然可以使符合主鍵
主鍵索引的效率高（主鍵不可重復(fù)）
創(chuàng)建主鍵索引的列，它的值不能為null，且不能重復(fù)
主鍵索引的列基本上是int
2.創(chuàng)建唯一索引
第一種方式-- 在表定義時(shí)，在某列后直接指定unique唯一屬性。
create table user4(
id int primary key,
name varchar(32) unique
);
第二種方式-- 創(chuàng)建表時(shí)，在表的后面指定某列或某幾列為unique
create table user5(
id int primary key,
name varchar(32),
unique(name)
);
第三種方式–創(chuàng)建表以后再添加唯一鍵
create table user6(
id int primary key,
name varchar(32)
);
alter table user6 add unique(name);
唯一索引的特點(diǎn)：
一個(gè)表中，可以有多個(gè)唯一索引
查詢效率高
如果在某一列建立唯一索引，必須保證這列不能有重復(fù)數(shù)據(jù)
如果一個(gè)唯一索引上指定not null，等價(jià)于主鍵索引
3.創(chuàng)建普通索引
第一種方式–在表的定義最后，指定某列為索引
create user7(
id int primary key,
name varchar(32),
index(name)
);
第二種方式–創(chuàng)建完表以后指定某列為普通索引
create table user8(
id int primary key,
name varchar(32)
);
alter tabele user8 add index(name);
第三種方式-- 創(chuàng)建一個(gè)索引名為 idx_name 的索引
create table user9(
id int primary key,
name varchar(32)
);
create index index_name on user9(name);
普通索引的特點(diǎn)：
一個(gè)表中可以有多個(gè)普通索引，普通索引在實(shí)際開發(fā)中用的比較多
如果某列需要?jiǎng)?chuàng)建索引，但是該列有重復(fù)的值，那么我們就應(yīng)該使用普通索引
4.查詢索引
第一種方法： show keys from 表名
第二種方法: show index from 表名;
第三種方法（信息比較簡(jiǎn)略）： desc 表名；
5.刪除索引
第一種方法-刪除主鍵索引： alter table 表名 drop primary key;
第二種方法-其他索引的刪除： alter table 表名 drop index 索引名； 索引名就是show keys from 表名中的 Key_name 字段
alter table user10 drop index idx_name;
第三種方法方法： drop index 索引名 on 表名
drop index idx_name on user8;
6.使用案例
我們創(chuàng)建了如下的表：
create table test1(
id int primary key,
name varchar(32)
);
由于我們指定了id為主鍵，所以該表就創(chuàng)建了主鍵索引
我們使用如下指令來查看主鍵索引：
show keys from test1\G;
我們使用如下指令刪除主鍵索引
alter table test1 drop primary key;
此時(shí)就沒有了索引，并且也沒有了主鍵
我們可以使用如下指令創(chuàng)建唯一索引
alter table tset1 add unique(name);
由于沒有主鍵，此時(shí)那么也就成為了主鍵，下面我們把id設(shè)置為主鍵
下面我們將表增加一個(gè)email屬性，并且將email設(shè)置了普通索引
alter table test1 add email varchar(32) not null after name;
create index myindex on test1(email);
此時(shí)就有三個(gè)索引，然后我們刪除email普通索引和name唯一索引，然后以name和email為鍵值創(chuàng)建復(fù)合索引
alter table drop index myindex;
drop index name on test1;
create index myindex on test1(name,email);
此時(shí)的索引名myindex，它的行名有兩兩列，共同作為索引，這個(gè)索引可以用第一個(gè)key值查詢另一個(gè)key值，這樣就可以在路徑上查詢到結(jié)果直接返回，而不再需要查詢到葉子節(jié)點(diǎn)再通過主鍵為索引的表中再進(jìn)行查詢一次。
補(bǔ)充知識(shí)：
我們可以使用如下指令查看有哪些用戶在使用我們的數(shù)據(jù)庫
show processlist;
7.創(chuàng)建全文索引
當(dāng)對(duì)文章字段或有大量文字的字段進(jìn)行檢索時(shí)，會(huì)使用到全文索引。MySQL提供全文索引機(jī)制，但是有要求，要求表的存儲(chǔ)引擎必須是MyISAM，而且默認(rèn)的全文索引支持英文，不支持中文。如果對(duì)中文進(jìn)行全文檢索，可以使用sphinx的中文版(coreseek)。
我們建立如下的表：存儲(chǔ)引擎為MyISAM
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;
插入如下的數(shù)據(jù)：
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數(shù)據(jù)：我們使用模糊查詢即可
select * from articles where body like '%database%';
可以用explain工具看一下，是否使用到索引
explain select * from articles where body like '%database%'\G
key為null表示沒有用到索引。雖然查詢出數(shù)據(jù)，但是沒有使用到全文索引
如何使用全文索引呢？我們使用如下指令：
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');
通過explain來分析這個(gè)sql語句
explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST('database')\G
8.索引創(chuàng)建原則
比較頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件
更新非常頻繁的字段不適合作創(chuàng)建索引
不會(huì)出現(xiàn)在where子句中的字段不該創(chuàng)建索引
其他概念:
復(fù)合索引
在MySQL中，復(fù)合索引是指由多個(gè)列組合而成的索引。它可以提高查詢的效率，特別是在涉及多個(gè)列的查詢時(shí)。
與單列索引相比，復(fù)合索引可以通過對(duì)多個(gè)列進(jìn)行排序和搜索來加快查詢速度。它可以幫助MySQL更有效地定位并檢索符合查詢條件的數(shù)據(jù)行。
索引最左匹配原則
索引最左匹配原則是指在使用復(fù)合索引進(jìn)行查詢時(shí)，如果查詢條件只涉及到了索引的前綴列（即索引的最左邊的列），那么MySQL可以利用該復(fù)合索引進(jìn)行高效的搜索和匹配。復(fù)合索引的列是按順序存儲(chǔ)的，而且索引的前綴列包含了后面的列。所以，在查詢時(shí)，MySQL可以根據(jù)最左邊的列進(jìn)行快速定位，然后再通過索引的后面列進(jìn)行進(jìn)一步的篩選。需要注意的是，在使用復(fù)合索引時(shí)，索引的順序和查詢條件的順序是相關(guān)的。如果查詢條件中的列順序與復(fù)合索引的順序不一致，MySQL可能無法使用該索引進(jìn)行高效查詢。
總結(jié)起來，索引最左匹配原則指的是在使用復(fù)合索引進(jìn)行查詢時(shí)，如果查詢條件只涉及到了索引的前綴列，MySQL可以利用該復(fù)合索引進(jìn)行高效的搜索和匹配。但需要注意索引的順序和查詢條件的順序的一致性。
索引覆蓋
索引覆蓋是指查詢的結(jié)果可以完全通過索引來獲取，而無需訪問數(shù)據(jù)表的實(shí)際行。當(dāng)一個(gè)查詢所需要的列都包含在某個(gè)索引中時(shí)，MySQL可以直接從索引中獲取相關(guān)數(shù)據(jù)，從而避免了額外的IO操作和數(shù)據(jù)行的訪問，提高了查詢的性能。
通過索引覆蓋，可以減少對(duì)磁盤的讀取次數(shù)，提高查詢效率，特別是對(duì)于大型數(shù)據(jù)表和復(fù)雜查詢的情況下，其優(yōu)勢(shì)尤為明顯。文章來源：http://www.zghlxwxcb.cn/news/detail-839776.html
要實(shí)現(xiàn)索引覆蓋，需要滿足以下條件：文章來源地址http://www.zghlxwxcb.cn/news/detail-839776.html
- 查詢語句只包含從索引中檢索的列，而不包含其他未涉及的列。
- 索引包含所有查詢所需的列，以及用于篩選和排序的列（如果有的話）。
到了這里，關(guān)于【MySQL】MySQL索引特性的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
