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 字段，通過一定的方法進行存儲（如B+tree），快速查到該號碼所在的行在表單中存儲的位置，拿到位置直接去讀取該行的信息，就不用遍歷了，節(jié)省大量的便利時間。

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

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

優(yōu)點：

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

缺點：

1、創(chuàng)建索引和維護索引要耗費時間，并且隨著數(shù)據(jù)量的增加所耗費的時間也會增加。
2、索引也需要占空間，我們知道數(shù)據(jù)表中的數(shù)據(jù)也會有最大上線設(shè)置的，如果我們有大量的索引，索引文件可能會比數(shù)據(jù)文件更快達到上線值。
3、當對表中的數(shù)據(jù)進行增加、刪除、修改時，索引也需要動態(tài)的維護，降低了數(shù)據(jù)的維護速度。

使用原則：

通過上面說的優(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、在一個列上(字段上)不同值較少的不要建立索引，比如在學生表的"性別"字段上只有男，女兩個不同值。相反的，在一個字段上不同值較多的可以建立索引。

3，索引的分類

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

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

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

存儲引擎的類型及特點：

索引我們分為四類：單列索引(普通索引，唯一索引，主鍵索引)、組合索引、全文索引、空間索引。

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

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

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

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

• 組合索引：一個的索引包含多個列，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用，使用組合索引時遵循最左前綴。會在后面的例子細說。

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

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

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

要求，引擎為MyISAM，創(chuàng)建空間索引的列，必須將其聲明為NOT NULL。具體細節(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)建索引時未指定索引的名，會自動幫我們用字段名當作索引名

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語句來為表中的字段添加索引?；菊Z法如下：
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)存在的索引刪除掉。不用的索引建議進行刪除，因為它們會降低表的更新速度，影響數(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，二叉樹：

二叉樹索引：

當單邊增長的時候，二叉樹就相當于鏈表結(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)建主鍵，后臺會默認幫你創(chuàng)建主鍵并幫你維護。

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

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

選擇整型的原因：

1，整型數(shù)據(jù)比較大小的效率遠遠高于字符串比較大小的效率。
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會自己維護一個聚集索引，浪費資源。

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

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

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

比如3個字段作為索引：
三個字段放在一個節(jié)點，容易排序的字段放在第一個，優(yōu)先按照第一個字段的value進行排序，如果第一個字段的值相同，則按照第二個字段排序，如果第二個字段值也相同，則按照第三個字段排序。以此類推。

葉節(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ōu)點：非常非?？?。
hash算法應(yīng)用舉例：
MD5加密、CRC32/16。

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

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

3，MySQL存儲引擎：

MySQL創(chuàng)建表單，默認是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)化準則

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

Java優(yōu)化：

虛擬機優(yōu)化：

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

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

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

Just Do It.