1、什么是數(shù)據(jù)庫中的索引？索引有什么作用？

引入索引的目的是為了加快查詢速度。如果數(shù)據(jù)量很大，大的查詢要從硬盤加載數(shù)據(jù)到內(nèi)存當(dāng)中。

2、InnoDB中的索引原理是怎么樣的？

InnoDB是MySQL的默認存儲引擎,InnoDB有兩種索引：B+樹索引和哈希索引，其中哈希索引是自適應(yīng)性的，存儲引擎會根據(jù)表的使用情況，自動創(chuàng)建哈希索引，不能人為的干涉。

B樹、B-樹、B+樹、B*樹四種數(shù)據(jù)結(jié)構(gòu)在索引中的運用，這四種數(shù)據(jù)結(jié)構(gòu)的順序必須是這樣的。分別闡述如下：

B樹：二叉樹，每個結(jié)點只存儲一個關(guān)鍵字，等于則命中，小于走左結(jié)點，大于走右結(jié)點；

B-樹：多路搜索樹，每個結(jié)點存儲M/2到M個關(guān)鍵字，非葉子結(jié)點存儲指向關(guān)鍵字范圍的子結(jié)點；所有關(guān)鍵字在整顆樹中出現(xiàn)，且只出現(xiàn)一次，非葉子結(jié)點可以命中；

B+樹：在B-樹基礎(chǔ)上，為葉子結(jié)點增加鏈表指針，所有關(guān)鍵字都在葉子結(jié)點中出現(xiàn)，非葉子結(jié)點作為葉子結(jié)點的索引；B+樹總是到葉子結(jié)點才命中；

B*樹：在B+樹基礎(chǔ)上，為非葉子結(jié)點也增加鏈表指針，將結(jié)點的最低利用率從1/2提高到2/3；

首先，B樹也叫作二叉搜索樹，字如其義。B樹有如下三個特點：所有非葉子節(jié)點至多擁有兩個兒子；所有節(jié)點存儲一個關(guān)鍵字；非葉子節(jié)點的左指針指向小于其關(guān)鍵字的子樹，右指針指向大于其關(guān)鍵字的子樹。

B樹的搜索，從根結(jié)點開始，如果查詢的關(guān)鍵字與結(jié)點的關(guān)鍵字相等，那么就命中，否則，如果查詢關(guān)鍵字比結(jié)點關(guān)鍵字小，就進入左兒子；如果比結(jié)點關(guān)鍵字大，就進入右兒子；如果左兒子或右兒子的指針為空，則報告找不到相應(yīng)的關(guān)鍵字。如果B樹的所有非葉子結(jié)點的左右子樹的結(jié)點數(shù)目均保持差不多(平衡)，那么B樹的搜索性能逼近二分查找；但它比連續(xù)內(nèi)存空間的二分查找的優(yōu)點是，改變B樹結(jié)構(gòu)(插入與刪除結(jié)點)不需要移動大段的內(nèi)存數(shù)據(jù)，甚至通常是常數(shù)開銷。最右邊也是一個B樹，但它的搜索性能已經(jīng)是線性的了；同樣的關(guān)鍵字集合有可能導(dǎo)致不同的樹結(jié)構(gòu)索引；所以，使用B樹還要考慮盡可能讓B樹保持左圖的結(jié)構(gòu)，和避免右圖的結(jié)構(gòu)，也就是所謂的“平衡”問題；實際使用的B樹都是在原B樹的基礎(chǔ)上加上平衡算法，即“平衡二叉樹”；如何保持B樹結(jié)點分布均勻的平衡算法是平衡二叉樹的關(guān)鍵；平衡算法是一種在B樹中插入和刪除結(jié)點的策略；

其次，B-樹。數(shù)據(jù)量越大，B樹的高度會越高，之所以會越高，主要是因為二叉引起的。所以在此基礎(chǔ)上我們定義了B-樹的規(guī)范如下：B-樹不是二叉的，所以又叫作多路搜索樹。

B-樹是一種多路搜索樹(并不是二叉的)：

1.定義任意非葉子結(jié)點最多只有M個兒子；且M>2；

2.根結(jié)點的兒子數(shù)為[2, M]；除根結(jié)點以外的非葉子結(jié)點的兒子數(shù)為[M/2, M]；

3.每個結(jié)點存放至少M/2-1(取上整)和至多M-1個關(guān)鍵字；(至少2個關(guān)鍵字)

4.非葉子結(jié)點的關(guān)鍵字個數(shù)=指向兒子的指針個數(shù)-1；

5.非葉子結(jié)點的關(guān)鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

6.非葉子結(jié)點的指針：P[1], P[2], …, P[M]；其中P[1]指向關(guān)鍵字小于K[1]的子樹，P[M]指向關(guān)鍵字大于K[M-1]的子樹，其它P[i]指向關(guān)鍵字屬于(K[i-1], K[i])的子樹；

7.所有葉子結(jié)點位于同一層；如圖所示中(M=3)

B-樹的搜索，從根結(jié)點開始，對結(jié)點內(nèi)的關(guān)鍵字(有序)序列進行二分查找，如果命中則結(jié)束，否則進入查詢關(guān)鍵字所屬范圍的兒子結(jié)點重復(fù)，直到所對應(yīng)的兒子指針為空，或已經(jīng)是葉子結(jié)點。

B-樹的特性：

1.關(guān)鍵字集合分布在整顆樹中；

2.任何一個關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個結(jié)點中；

3.搜索有可能在非葉子結(jié)點結(jié)束；

4.其搜索性能等價于在關(guān)鍵字全集內(nèi)做一次二分查找；

5.自動層次控制；

由于限制了除根結(jié)點以外的非葉子結(jié)點，至少含有M/2個兒子，確保了結(jié)點的至少利用率，其最底搜索性能如圖，

其中，M為設(shè)定的非葉子結(jié)點最多子樹個數(shù)，N為關(guān)鍵字總數(shù)；

所以B-樹的性能總是等價于二分查找(與M值無關(guān))，也就沒有B樹平衡的問題；由于M/2的限制，在插入結(jié)點時，如果結(jié)點已滿，需要將結(jié)點分裂為兩個各占M/2的結(jié)點；刪除結(jié)點時，需將兩個不足M/2的兄弟結(jié)點合并；

其次，B+樹。B樹、B-樹、B+樹、B*樹。B樹是二叉搜索樹，B-樹、B+樹、B*樹都是多路搜索樹。B-樹定義了基本的規(guī)范，它有個特點，關(guān)鍵字出現(xiàn)在非葉子節(jié)點或者葉子節(jié)點，子樹的指針比關(guān)鍵字個數(shù)大一個。B+樹在這兩方面分別做了升級，定義如下：

B+樹是B-樹的變體，也是一種多路搜索樹：

1.其定義基本與B-樹同，除了：

2.非葉子結(jié)點的子樹指針與關(guān)鍵字個數(shù)相同；

3.非葉子結(jié)點的子樹指針P[i]，指向關(guān)鍵字值屬于[K[i], K[i+1])的子樹

(B-樹是開區(qū)間)；

5.為所有葉子結(jié)點增加一個鏈指針；

6.所有關(guān)鍵字都在葉子結(jié)點出現(xiàn)；

B+的搜索與B-樹也基本相同，區(qū)別是B+樹只有達到葉子結(jié)點才命中(B-樹可以在非葉子結(jié)點命中)，其性能也等價于在關(guān)鍵字全集做一次二分查找；

B+的特性：

1.所有關(guān)鍵字都出現(xiàn)在葉子結(jié)點的鏈表中(稠密索引)，且鏈表中的關(guān)鍵字恰好是有序的；

2.不可能在非葉子結(jié)點命中；

3.非葉子結(jié)點相當(dāng)于是葉子結(jié)點的索引(稀疏索引)，葉子結(jié)點相當(dāng)于是存儲(關(guān)鍵字)數(shù)據(jù)的數(shù)據(jù)層；

4.更適合文件索引系統(tǒng)；

最后B*樹，它是B+樹的變體，在B+樹的非根和非葉子結(jié)點再增加指向兄弟的指針。

B*樹定義了非葉子結(jié)點關(guān)鍵字個數(shù)至少為(2/3)*M，即塊的最低使用率為2/3(代替B+樹的1/2)；

B+樹的分裂：當(dāng)一個結(jié)點滿時，分配一個新的結(jié)點，并將原結(jié)點中1/2的數(shù)據(jù)復(fù)制到新結(jié)點，最后在父結(jié)點中增加新結(jié)點的指針；B+樹的分裂只影響原結(jié)點和父結(jié)點，而不會影響兄弟結(jié)點，所以它不需要指向兄弟的指針；

B*樹的分裂：當(dāng)一個結(jié)點滿時，如果它的下一個兄弟結(jié)點未滿，那么將一部分數(shù)據(jù)移到兄弟結(jié)點中，再在原結(jié)點插入關(guān)鍵字，最后修改父結(jié)點中兄弟結(jié)點的關(guān)鍵字(因為兄弟結(jié)點的關(guān)鍵字范圍改變了)；

如果兄弟也滿了，則在原結(jié)點與兄弟結(jié)點之間增加新結(jié)點，并各復(fù)制1/3的數(shù)據(jù)到新結(jié)點，最后在父結(jié)點增加新結(jié)點的指針；

所以，B*樹分配新結(jié)點的概率比B+樹要低，空間使用率更高；

3、如何在Navicat中對表添加索引？

#刪除表

DROP TABLE test.idc_work_order_main

# 創(chuàng)建表結(jié)構(gòu)idc_work_order_main

CREATE TABLE `idc_work_order_main` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',

`creator` varchar(128) NOT NULL DEFAULT '0' COMMENT '創(chuàng)建人',

`gmt_create` timestamp NULL DEFAULT NULL COMMENT '創(chuàng)建時間',

`modifier` varchar(128) DEFAULT '0' COMMENT '修改人',

`gmt_modified` timestamp NULL DEFAULT NULL COMMENT '修改時間',

`title` varchar(64) DEFAULT NULL COMMENT '工單標(biāo)題',

`category` varchar(32) DEFAULT NULL COMMENT '工單類別',

`subject` varchar(32) DEFAULT NULL COMMENT '工單類型',

`demander` varchar(30) DEFAULT NULL COMMENT '需求方',

`is_atomic` char(1) DEFAULT 'y' COMMENT '是否原子工單',

`atomic_id` int(11) DEFAULT NULL COMMENT '當(dāng)前原子工單在列表中ID',

`site` varchar(50) DEFAULT NULL COMMENT '工單所在機房',

`operationer` varchar(32) DEFAULT NULL COMMENT '當(dāng)前處理人',

`operation_role` varchar(50) DEFAULT NULL COMMENT '當(dāng)前處理角色',

`state` varchar(50) DEFAULT NULL COMMENT '工單狀態(tài)',

`sub_state` varchar(50) DEFAULT NULL COMMENT '工單子狀態(tài)',

`expect_time` timestamp NULL DEFAULT NULL COMMENT '預(yù)期結(jié)單時間',

`sla` bigint(20) DEFAULT NULL COMMENT 'sla',

`evaluation` varchar(200) DEFAULT NULL COMMENT '評價',

`create_source` varchar(32) DEFAULT 'TBOSS' COMMENT '創(chuàng)建源',

`source_key` varchar(32) DEFAULT NULL COMMENT '創(chuàng)建源唯一標(biāo)示',

`is_deleted` char(1) DEFAULT 'n' COMMENT '是否已刪除y,n',

`remark` varchar(500) DEFAULT NULL COMMENT '備注',

`parent_id` int(11) DEFAULT '0' COMMENT '父工單ID',

`asset_total` int(11) DEFAULT '0' COMMENT '設(shè)備總數(shù)',

`sla_standard` double DEFAULT NULL COMMENT '標(biāo)準時間',

`sla_unit` char(1) DEFAULT NULL COMMENT 'sla 單位',

`effective_date` timestamp NULL DEFAULT NULL COMMENT '提單時間(生效時間)',

`is_timeout` char(1) DEFAULT 'n' COMMENT '是否超時，‘y’超時，‘n’未超時',

`statement_date` timestamp NULL DEFAULT NULL COMMENT '結(jié)單的時間',

`source_creator` varchar(32) DEFAULT NULL COMMENT '第三方創(chuàng)建人信息(域賬號)',

`atomic_order_id` int(11) DEFAULT NULL COMMENT '當(dāng)前原子工單編號',

`order_device_type` varchar(50) DEFAULT 'SERVER' COMMENT '工單設(shè)備類型(server=服務(wù)器,network_serve等)',

`finish_asset_total` int(11) DEFAULT '0' COMMENT '完成設(shè)備數(shù)',

PRIMARY KEY (`id`),

KEY `idx_statement_date` (`statement_date`),

KEY `idx_parent_id` (`parent_id`),

KEY `idx_gmt_modified` (`gmt_modified`),

KEY `idx_gmt_create` (`gmt_create`)

) ENGINE=InnoDB AUTO_INCREMENT=182431 DEFAULT CHARSET=utf8 COMMENT='工單主表';

#顯示建表信息

SHOW CREATE TABLE idc_work_order_main

#添加索引

ALTER TABLE idc_work_order_main ADD INDEX atomic_order_id (atomic_order_id)

SHOW INDEX FROM idc_work_order_main

EXPLAIN SELECT * FROM idc_work_order_main WHERE atomic_order_id = '9956'

#添加主鍵 (唯一)

ALTER TABLE idc_work_order_main ADD PRIMARY KEY source_creator (source_creator)

#添加唯一索引

ALTER TABLE idc_work_order_main ADD UNIQUE source_creator (source_creator)

SHOW INDEX FROM idc_work_order_main

#添加聯(lián)合索引

ALTER TABLE idc_work_order_main ADD INDEX id_source_parent_create_atomic (id,source_creator,parent_id,gmt_create,atomic_order_id)

SHOW INDEX FROM idc_work_order_main

關(guān)于索引：

1.一本書光目錄就占半本書，目錄(索引)還有意義嗎？索引過多一定情況下會導(dǎo)致索引文件過大(指數(shù)增長)，系統(tǒng)在尋址時查詢時間增長。

2.性別字段就男女兩個，加索引純浪費。一個索引會在 update 或 insert 時增加一次 I/O，對于操作系統(tǒng)底層來說是非常損耗性能的。

3.首先mysql是B+樹索引，這種作為索引的好處是可以對有序的記錄作logN級的查找，不過對于沒有大小之分的數(shù)據(jù)來說，還是建立哈希索引更好，因為哈希索引的時間復(fù)雜度基本是log1的。(注意此處有序和無序的概念)。

4.索引的命名規(guī)則：表名_字段名，需要加索引的字段，要在where條件中，數(shù)據(jù)量少的字段不需要加索引，如果where條件中是OR關(guān)系，加索引不起作用，符合最左原則。

4、索引中的index和key的使用

key 是數(shù)據(jù)庫的物理結(jié)構(gòu)，處于模型層面的，它包含兩層意義和作用，一是約束(偏重于約束和規(guī)范數(shù)據(jù)庫的結(jié)構(gòu)完整性)，二是索引(輔助查詢用的)。包括primary key, unique key, foreign key 等。

primary key 有兩個作用，一是約束作用(constraint)，用來規(guī)范一個存儲主鍵和唯一性，但同時也在此key上建立了一個index；

unique key 也有兩個作用，一是約束作用(constraint)，規(guī)范數(shù)據(jù)的唯一性，但同時也在這個key上建立了一個index；

foreign key也有兩個作用，一是約束作用(constraint)，規(guī)范數(shù)據(jù)的引用完整性，但同時也在這個key上建立了一個index；

可見，mysql的key是同時具有constraint和index的意義，這點和其他數(shù)據(jù)庫表現(xiàn)的可能有區(qū)別。index是數(shù)據(jù)庫的物理結(jié)構(gòu)，處于實現(xiàn)層面的，它只是輔助查詢的，它創(chuàng)建時會在另外的表空間(mysql中的innodb表空間)以一個類似目錄的結(jié)構(gòu)存儲。索引只是索引，它不會去約束索引的字段的行為(那是key要做的事情)。Mysql常見索引有：主鍵索引、唯一索引、普通索引、全文索引、組合索引。

總結(jié)

以上是生活随笔為你收集整理的mysql索引的使用及优化方法_MySQL中索引和优化的用法总结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。