MySQL索引

1，索引

索引是幫助數(shù)據(jù)庫（關(guān)系型捺萌、非關(guān)系型數(shù)據(jù)庫）高效獲取數(shù)據(jù)的 排好序的數(shù)據(jù)結(jié)構(gòu)鹿寨。
索引的作用：用于快速找出在某個列中有一特定值的行新博。
例如：查詢千萬條數(shù)據(jù)的表單：字段沒有索引需要幾十秒。有索引只要幾百毫秒

不使用索引脚草，MySQL必須從第一條記錄開始遍歷整個表赫悄，直到找出相關(guān)的行，表越大查詢數(shù)據(jù)所花費的時間就越多馏慨。如果表中查詢的列有索引埂淮，MySQL能夠快速的定位到一個位置去搜索數(shù)據(jù)文件，而不必查看所有數(shù)據(jù)写隶，那么將會節(jié)省很大一部分時間竿奏。

例如：有一張person表荆几，其中有2W條記錄，記錄著2W個人的信息。有一個Phone的字段記錄每個人的電話號碼匹耕，現(xiàn)在想要查詢出電話號碼為xxxx的人的信息沮协。

如果沒有索引住册，那么將從表中第一條記錄一條條往下遍歷牢屋，直到找到該條信息為止。

如果有了索引耙册，那么會將 Phone 字段给僵，通過一定的方法進(jìn)行存儲（如B+tree），快速查到該號碼所在的行在表單中存儲的位置详拙，拿到位置直接去讀取該行的信息想际，就不用遍歷了，節(jié)省大量的便利時間溪厘。

其中MySQL中的索引的存儲類型有兩種：BTREE胡本、HASH。 也就是用樹或者Hash值來存儲該字段畸悬，更詳細(xì)的查找邏輯就需要會算法的知識了侧甫。

2，索引優(yōu)蹋宦、缺點

優(yōu)點：

　　1披粟、所有的MySql列類型(字段類型)都可以被索引，也就是可以給任意字段設(shè)置索引冷冗。
　　2守屉、大大加快數(shù)據(jù)的查詢速度。

缺點：

1蒿辙、創(chuàng)建索引和維護(hù)索引要耗費時間拇泛，并且隨著數(shù)據(jù)量的增加所耗費的時間也會增加滨巴。
2、索引也需要占空間俺叭，我們知道數(shù)據(jù)表中的數(shù)據(jù)也會有最大上線設(shè)置的恭取，如果我們有大量的索引，索引文件可能會比數(shù)據(jù)文件更快達(dá)到上線值熄守。
3蜈垮、當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除裕照、修改時攒发，索引也需要動態(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度晋南。

使用原則：

通過上面說的優(yōu)點和缺點惠猿，我們應(yīng)該可以知道，并不是每個字段都設(shè)置為索引好搬俊，也不是索引越多越好紊扬，而是需要自己合理的使用蜒茄。

1唉擂、對經(jīng)常更新的表就避免對其設(shè)置過多的索引，對經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引檀葛。
2玩祟、數(shù)據(jù)量小的表最好不要使用索引，因為由于數(shù)據(jù)較少屿聋，可能查詢?nèi)繑?shù)據(jù)花費的時間比遍歷索引的時間還要短空扎，索引就可能不會產(chǎn)生優(yōu)化效果。
3润讥、在一個列上(字段上)不同值較少的不要建立索引转锈，比如在學(xué)生表的"性別"字段上只有男，女兩個不同值楚殿。相反的撮慨，在一個字段上不同值較多的可以建立索引。

3脆粥，索引的分類

索引是在存儲引擎中實現(xiàn)的砌溺，也就是說不同的存儲引擎，會使用不同的索引：

MyISAM和InnoDB存儲引擎：只支持BTREE索引变隔， 也就是說默認(rèn)使用BTREE规伐，不能夠更換。（但是innoDB存儲引擎支持hash索引是自適應(yīng)的匣缘，innoDB存儲引擎會根據(jù)表的使用情況自動為表生成hash索引猖闪，不能人為干預(yù)是否在一張表中生成hash索引鲜棠。后續(xù)再整理）

MEMORY、HEAP存儲引擎：支持HASH和BTREE索引萧朝。

存儲引擎的類型及特點：

索引我們分為四類：單列索引(普通索引原押，唯一索引，主鍵索引)偎血、組合索引诸衔、全文索引、空間索引颇玷。

• 單列索引：一個索引只包含單個列笨农，但一個表中可以有多個單列索引。 這里不要搞混淆了帖渠。

  1谒亦、普通索引：MySQL中基本索引類型，沒有什么限制空郊，允許在定義索引的列中插入重復(fù)值和空值份招，純粹為了查詢數(shù)據(jù)更快一點。

  2狞甚、唯一索引：索引列中的值必須是唯一的锁摔，但是允許為空值，

  3哼审、主鍵索引：是一種特殊的唯一索引谐腰，不允許有空值。

• 組合索引：一個的索引包含多個列棺蛛，只有在查詢條件中使用了這些字段的左邊字段時怔蚌，索引才會被使用，使用組合索引時遵循最左前綴旁赊。會在后面的例子細(xì)說桦踊。

• 全文索引：要求只有在MyISAM引擎上才能使用，只能在CHAR终畅、VARCHAR籍胯、TEXT類型字段上使用全文索引竟闪。就是在一堆文字中，通過其中的某個關(guān)鍵字等杖狼，就能找到該字段所屬的記錄行炼蛤，比如有"你是個大煞筆，二貨 ..." 通過大煞筆蝶涩，可能就可以找到該條記錄理朋。這里說的是可能，因為全文索引的使用涉及了很多細(xì)節(jié)绿聘。

• 空間索引：空間索引是對空間數(shù)據(jù)類型的字段建立的索引嗽上，MySQL中的空間數(shù)據(jù)類型有四種，GEOMETRY熄攘、POINT兽愤、LINESTRING、POLYGON挪圾。

在創(chuàng)建空間索引時浅萧，使用SPATIAL關(guān)鍵字。

要求哲思，引擎為MyISAM洼畅，創(chuàng)建空間索引的列，必須將其聲明為NOT NULL也殖。具體細(xì)節(jié)看下面

4土思，MySQL索引使用

1务热、在創(chuàng)建表時創(chuàng)建索引

創(chuàng)建索引：單列索引（普通忆嗜、唯一、主鍵）崎岂、組合索引捆毫、全文索引和空間索引。

格式：CREATE TABLE 表名[字段名 數(shù)據(jù)類型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length])

1冲甘、創(chuàng)建普通索引：

創(chuàng)建普通索引绩卤，創(chuàng)建索引時未指定索引的名，會自動幫我們用字段名當(dāng)作索引名

CREATE TABLE book(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
author VARCHAR(20) NOT NULL,
info VARCHAR(255) NULL,
INDEX(author));

2江醇，在創(chuàng)建表后創(chuàng)建索引

# MySQL中可以使用alter table這個SQL語句來為表中的字段添加索引濒憋。基本語法如下：
ALTER TABLE <表名> ADD INDEX (<字段1>);

# 舉例：
# 1.添加PRIMARY KEY（主鍵索引） 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 
# 2.添加UNIQUE(唯一索引) 
ALTER TABLE `table_name` ADD UNIQUE (`column`) 
# 3.添加INDEX(普通索引) 陶夜，添加多列索引 
ALTER TABLE `table_name` ADD INDEX index_name (`column`) 
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)
# 4.添加FULLTEXT(全文索引) 
ALTER TABLE `table_name` ADD FULLTEXT (`column`) 

3凛驮、刪除索引

刪除索引是指將表中已經(jīng)存在的索引刪除掉。不用的索引建議進(jìn)行刪除条辟，因為它們會降低表的更新速度黔夭，影響數(shù)據(jù)庫的性能宏胯。

1，使用 DROP INDEX 語句刪除索引

# 語法格式：<索引名>：要刪除的索引名本姥。<表名>：指定該索引所在的表名肩袍。
DROP INDEX <索引名> ON <表名>

# 舉例：刪除表 tb_stu_info 中的索引，輸入的 SQL 語句和執(zhí)行結(jié)果如下所示婚惫。
mysql> DROP INDEX height ON tb_stu_info;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_stu_info\G
Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

2氛赐， 使用 ALTER TABLE 語句刪除索引

# 語法如下：
DROP PRIMARY KEY：表示刪除表中的主鍵。一個表只有一個主鍵先舷，主鍵也是一個索引鹰祸。
DROP INDEX index_name：表示刪除名稱為 index_name 的索引。
DROP FOREIGN KEY fk_symbol：表示刪除外鍵密浑。
# 注意：如果刪除的列是索引的組成部分蛙婴，那么在刪除該列時，也會將該列從索引中刪除尔破；如果組成索引的所有列都被刪除街图，那么整個索引將被刪除。

# 舉例：刪除表 tb_stu_info2 中名稱為 id 的索引懒构，輸入的 SQL 語句和執(zhí)行結(jié)果如下所示餐济。
mysql> ALTER TABLE tb_stu_info2 DROP INDEX height;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_stu_info2\G
Table: tb_stu_info2
Create Table: CREATE TABLE `tb_stu_info2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

5，總結(jié)

1胆剧、索引是干嘛的絮姆？為什么要有索引？

很重要秩霍，用來提高查詢效率的篙悯。

2、索引的分類：單列索引（普通索引铃绒、唯一索引和主鍵索引）鸽照、組合索引、全文索引和空間索引颠悬。

3矮燎、索引的使用：給表中創(chuàng)建索引，添加索引赔癌，刪除索引诞外。

2，索引數(shù)據(jù)結(jié)構(gòu)/原理：

1灾票，二叉樹：

二叉樹索引：

當(dāng)單邊增長的時候峡谊，二叉樹就相當(dāng)于鏈表結(jié)構(gòu)。

binary.png

左邊的值小于右邊的值。

二叉樹作為索引方法/存儲結(jié)構(gòu)：

key為索引字段值靖苇，value為對應(yīng)行的磁盤文件位置指針席噩。

優(yōu)點：相對于沒有索引算法，明顯提升查詢效率贤壁。
缺點：數(shù)據(jù)單邊增長的場景下悼枢，二叉樹結(jié)構(gòu)，就演變成了鏈表結(jié)構(gòu)脾拆，查詢和直接輪詢沒什么區(qū)別馒索，效率低。

2名船，紅黑樹：

樹的高度太高绰上，查找麻煩：

redTree.png

本質(zhì)上也是二叉樹，是一種二叉平衡樹渠驼，不讓一邊增長太過分了蜈块。

紅黑樹作為索引方法/存儲結(jié)構(gòu)：

優(yōu)點：
1，相對二叉樹迷扇，解決單邊增長的問題百揭。
缺點：
1，樹的高度太高蜓席。希望3~5層放千萬級別的數(shù)據(jù)器一。
2，范圍查詢性能差厨内。

3祈秕，Hash：

對字段取hashcode值。

優(yōu)點：Hash索引的查詢速度雏胃，非常非城朊快。
缺點：Hash索引方法對范圍查找支持的很差丑掺。

4获印，B-tree：

B-tree：

B-Tree.png

根節(jié)點或葉節(jié)點的key對應(yīng)的value直接存有 行數(shù)據(jù)對應(yīng)的磁盤文件位置指針述雾。

優(yōu)點：
相對于紅黑樹街州，層級少，查詢次數(shù)少玻孟。索引節(jié)點可以存儲多個索引值唆缴。
缺點：
B-Tree 對范圍查詢的支持也是很差的。（葉節(jié)點之間沒有指針黍翎，葉節(jié)點也不是有序的）

為什么不只設(shè)置為一層面徽？

放在一層，即所有的索引數(shù)據(jù)都在一個節(jié)點，不安全而且趟紊，占用內(nèi)存多氮双。

5，B+tree：

B+Tree.png

B+Tree是B-Tree的變種霎匈，3層戴差。每個節(jié)點16k（根節(jié)點、葉節(jié)點都是16k）铛嘱。

只有葉節(jié)點有data數(shù)據(jù)/行的磁盤文件位置索引暖释。

根節(jié)點存儲的內(nèi)容：索引字段+下一節(jié)點的索引。

根節(jié)點一般會存放到內(nèi)存/ram里面去墨吓。葉節(jié)點肯定存在磁盤球匕。

B+tree ：索引的枝葉部分，value為：存儲的直接就是 所在行的內(nèi)容帖烘。

MySQL在設(shè)計的時候亮曹，它的底層原理就是按照B+Tree組織的一個索引結(jié)構(gòu)文件。如果在創(chuàng)建表的時候秘症，沒有創(chuàng)建主鍵乾忱，后臺會默認(rèn)幫你創(chuàng)建主鍵并幫你維護(hù)。

為什么innoDB表必須要有主鍵历极，并且推薦使用整型的自增主鍵窄瘟？

主鍵：一般是整型數(shù)據(jù)、字符串（如：UUID等）趟卸。

選擇整型的原因：

1蹄葱，整型數(shù)據(jù)比較大小的效率遠(yuǎn)遠(yuǎn)高于字符串比較大小的效率。
2锄列，UUID長度比較長图云，占用的空間大。

自增的原因：

1邻邮，B+tree 的特點竣况，根節(jié)點、葉節(jié)點筒严，它的索引丹泉，從左到右都是遞增有序的。葉節(jié)點之間的指針也是有序的鸭蛙。
2摹恨，范圍查詢的 效率特別高。（如：uid>49娶视，只要定位到 uid=49的葉節(jié)點指針晒哄，就馬上拿到結(jié)果了）
3睁宰，主鍵自增可以降低節(jié)點之間分裂的概率節(jié)約資源開銷。（如果中間插入某一個數(shù)字的主鍵寝凌，節(jié)點之間的分裂的概率比較高）

6柒傻，聚集索引、非聚集索引：

注意:聚集索引较木，非聚集索引诅愚，索引算法使用的都是B+tree算法。

非聚集索引（myisam）：

myisam.png

聚集索引（innoDB）：

innoDB.png

聚集索引查詢效率更高：
非聚集索引（如：myisam 存儲引擎的索引）：數(shù)據(jù)和索引分開存儲劫映。
聚集索引（innoDB 存儲引擎的索引）：數(shù)據(jù)和索引存在一起违孝。
innoDB的：主鍵是聚集索引，二級索引就是非聚集索引泳赋，其中非聚集索引的葉子節(jié)點雌桑，存儲的是主鍵值，去主鍵的表里查數(shù)據(jù)祖今。
一張表有且只有一個聚集索引校坑。即使沒有創(chuàng)建主鍵，innoDB也會根據(jù)某一列幫助創(chuàng)建聚集索引千诬。
innoDB：一定要建主鍵耍目，否則innoDB會自己維護(hù)一個聚集索引，浪費資源徐绑。

7邪驮，聯(lián)合索引：

最好不要建立單值索引，最好建聯(lián)合索引：

聯(lián)合索引的底層存儲結(jié)構(gòu)長什么樣傲茄？索引是排好序的數(shù)據(jù)結(jié)構(gòu)毅访，聯(lián)合索引內(nèi)部是怎么排序的？

比如3個字段作為索引：
三個字段放在一個節(jié)點盘榨，容易排序的字段放在第一個喻粹，優(yōu)先按照第一個字段的value進(jìn)行排序，如果第一個字段的值相同草巡，則按照第二個字段排序守呜，如果第二個字段值也相同，則按照第三個字段排序山憨。以此類推查乒。

葉節(jié)點：三個字段存在key位置，此行其他字段存在value位置萍歉。

最左前綴原理：

# 不能跳過最左邊的字段去查后面的字段侣颂。
select *from employee name="Bill" and age=31;   # 走索引
# 不走索引，因為單純的跳過name直接去查age枪孩，age已經(jīng)不是排好序的了（如上圖所示），所以不走索引，而是全表查詢蔑舞。
select *from employee age=31 and position="dev";    
select *from employee position="manager";   # 不走索引

JointIndex.png

理解之后就可以理解MySQL的所有索引優(yōu)化的原因拒担。

myisam 數(shù)據(jù)表查找的流程：

select *from t where  uid = 49;

# 如果uid是索引字段：讀取t表的**MYI文件**，找到uid=49所在的節(jié)點攻询，節(jié)點的key為49从撼，value為uid=49 所在行的 磁盤文件地址指針》根據(jù)查到的磁盤文件地址指針 去t表的**MYD文件**，直接定位到到所在的行钧栖。讀取數(shù)據(jù)低零。

索引方法可以選擇Btree方法也可以選擇hash方法：

Hash方法：

將key經(jīng)過hash之后存起來：key：列值的hashcode值，value：列支所在行的磁盤文件地址指針拯杠。
Hash運(yùn)算優(yōu)點：非常非程蜕簦快。
hash算法應(yīng)用舉例：
MD5加密潭陪、CRC32/16雄妥。

一般不選用hash方法的原因：

hash算法不支持范圍查詢。（如：uid = 49很好查依溯，但是uid>49 就很難查到）

3老厌，MySQL存儲引擎：

MySQL創(chuàng)建表單，默認(rèn)是innoDB存儲引擎

存儲引擎是針對表的黎炉，不同的表可以選擇不同的存儲引擎枝秤。

myisam和innodb引擎中，表的索引慷嗜，使用的都是B+tree算法宿百；不同的是，葉節(jié)點data部分：myisam存的是索引值洪添，innodb存的直接就是數(shù)據(jù)值垦页。

1，myisam存儲引擎

myisam存儲引擎干奢，創(chuàng)建一個表痊焊，會產(chǎn)生3個文件。

# 如：創(chuàng)建test表會產(chǎn)生3個文件忿峻。
test.frm 文件：    # 表結(jié)構(gòu)文件薄啥。
test.MYD文件：     # 表內(nèi)容。
test.MYI文件：     # 表索引文件逛尚。

2垄惧，innodb存儲引擎

innodb存儲引擎，創(chuàng)建一個表绰寞，產(chǎn)生2個文件：

# 如：創(chuàng)建test表會產(chǎn)生2個文件到逊。
test.frm 文件：    # 表結(jié)構(gòu)文件
test.ibd 文件：    # 表內(nèi)容+索引文件

4铣口，數(shù)據(jù)的存儲

表數(shù)據(jù)存儲：

MySQL-save.png

一個數(shù)據(jù)庫，存放到磁盤上的一個對應(yīng)的文件夾里觉壶。文件夾里存放表的文件脑题。

# innodb存儲引擎創(chuàng)建的表，一個表對應(yīng)兩個文件铜靶。
# myisam存儲引擎創(chuàng)建的表叔遂，一個表對應(yīng)三個文件。

5争剿，MySQL事務(wù)

主要用到的是：讀已提交已艰。

6，緩存池Buffer Pool原理

理解了Buffer Pool原理蚕苇，各種各樣的日志哩掺，就都了解了。

7捆蜀，日志

8疮丛，MySQL優(yōu)化準(zhǔn)則

懂得底層原理才能更好的優(yōu)化。懂的底層辆它，分分鐘就能找到有效的方法誊薄，不懂原理，只能慢慢去試锰茉。

Java優(yōu)化：

虛擬機(jī)優(yōu)化：

MySQL優(yōu)化：索引優(yōu)化呢蔫。

某些中間件的優(yōu)化。

表的關(guān)聯(lián)查詢：大部分只查詢1張表飒筑，最多不超過3張表片吊。

Just Do It.