溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
一、MySQL有哪些索引類型
索引的分類可以從多個角度進(jìn)行，下面分別從數(shù)據(jù)結(jié)構(gòu)，物理存儲和業(yè)務(wù)邏輯三個維度進(jìn)行劃分。
關(guān)于B+樹索引，后面會深入解析
現(xiàn)在MyISAM和InnoDB引擎都支持了
用于對GIS數(shù)據(jù)類型創(chuàng)建SPATIAL索引
索引是通過二叉樹的數(shù)據(jù)結(jié)構(gòu)來描述的，我們可以這么理解聚簇索引：索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)。而非聚簇索引的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只不過有一個指針指向?qū)?yīng)的數(shù)據(jù)塊。
舉個例子說明下：
create table student (
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(255),
PRIMARY KEY(`id`),
KEY(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;該表中主鍵id是該表的聚集索引、name為非聚集索引；表中的每行數(shù)據(jù)都是按照聚集索引id排序存儲的；比如要查找name='Arla'和name='Arle'的兩個同學(xué)，他們在name索引表中位置可能是相鄰的，但是實(shí)際存儲位置可能差的很遠(yuǎn)。name索引表節(jié)點(diǎn)按照name排序，檢索的是每一行數(shù)據(jù)的主鍵。聚集索引表按照主鍵id排序，檢索的是每一行數(shù)據(jù)的真實(shí)內(nèi)容。
主鍵索引是一種特殊的唯一索引，不允許有空值
復(fù)合索引指多個字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段，索引才會被使用。使用復(fù)合索引時遵循最左前綴集合
空間索引是對空間數(shù)據(jù)類型的字段建立的索引，MYSQL中的空間數(shù)據(jù)類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展，使得能夠用于創(chuàng)建正規(guī)索引類型的語法創(chuàng)建空間索引。創(chuàng)建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲引擎為MYISAM的表中創(chuàng)建.
CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]create table table_name(
id int(11),
name varchar(20),
sex boolean,
INDEX(id)
);查看表結(jié)構(gòu)
show create table table_name;
可以使 EXPLAIN 語句查看索引是否被使用
explain select * from table_name where id = 1\Gcreate table index2(
id int unique,
name varchar(20),
unique INDEX index_2(id asc)
);全文索引只能在char，varchar或者text 類型的字段上。而且，只有MyISAM 儲存引擎支持全文索引。
create table idnex3(
id int,
info varchar(20),
FULLTEXT INDEX index3_info(info)
)ENGINE=MyISAM;create table index4(
id int,
subject varchar(255),
index index4_st(subject(10))
);這里需要注意的，subject 的長度為255，但是index4_st索引只有10。這樣做的目的還是為了提高查詢速度。對于字符型的數(shù)據(jù)，可以不用查詢?nèi)啃畔?，只查詢其前面的若干字符信息?/p>
create table index5(
id int,
name varchar(20),
sex char(4),
index index5_ns(name.sex)
);這是我們可以看到，name 和sex字段上已經(jīng)創(chuàng)建了index_ns索引。
在example0() 表中的id 創(chuàng)建名為index7_id 的索引。
create index index7_id on example0(id);create UNIQUE index index_name on table_name(name);create FULLTEXT index index_name on table_name(info);create INDEX index_name ON table_name(name(10));create INDEX index_name ON table_name(name,sex);在name字段上創(chuàng)建名為indx_name 的索引
alter table table_name ADD INDEX index_name(name(20));alter table table_name ADD UNIQUE INDEX index_name(id);alter table table_name ADD FULLTEXT INDEX index_name(info);alter table table_name ADD INDEX index_name(name(4));alter tabel table_name ADD INDEX index_name(name.sex);DROP INDEX index_name ON table_name;目前大部分?jǐn)?shù)據(jù)庫系統(tǒng)及文件系統(tǒng)都采用B-Tree或其變種B+Tree作為索引結(jié)構(gòu)，那么索引樹是如何維護(hù)的？
查找是數(shù)據(jù)結(jié)構(gòu)和算法中一個非常重要的概念。
B-Tree是一種多路搜索樹（并不是二叉的）：
B-樹的搜索，從根結(jié)點(diǎn)開始，對結(jié)點(diǎn)內(nèi)的關(guān)鍵字（有序）序列進(jìn)行二分查找，如果命中則結(jié)束，否則進(jìn)入查詢關(guān)鍵字所屬范圍的兒子結(jié)點(diǎn)；重復(fù)，直到所對應(yīng)的兒子指針為空，或已經(jīng)是葉子結(jié)點(diǎn)；B-Tree上查找算法的偽代碼如下：
BTree_Search(node, key) { if(node == null) return null; foreach(node.key) { if(node.key[i] == key) return node.data[i]; if(node.key[i] > key) return BTree_Search(point[i]->node); } return BTree_Search(point[i+1]->node); } data = BTree_Search(root, my_key);B樹中每一個內(nèi)部節(jié)點(diǎn)會包含一定數(shù)量的鍵值。通常，鍵值的數(shù)量被選定在d和2d之間。在實(shí)際中，鍵值占用了節(jié)點(diǎn)中大部分的空間。因數(shù)2將保證節(jié)點(diǎn)可以被拆分或組合。如果一個內(nèi)部節(jié)點(diǎn)有2d個鍵值，那么添加一個鍵值給此節(jié)點(diǎn)的過程，將會拆分2d鍵值為2個d鍵值的節(jié)點(diǎn)，并把此鍵值添加給父節(jié)點(diǎn)。每一個拆分的節(jié)點(diǎn)需要最小數(shù)目的鍵值。相似地，如果一個內(nèi)部節(jié)點(diǎn)和他的鄰居兩者都有d個鍵值，那么將通過它與鄰居的合并來刪除一個鍵值。刪除此鍵值將導(dǎo)致此節(jié)點(diǎn)擁有d-1個鍵值;與鄰居的合并則加上d個鍵值，再加上從鄰居節(jié)點(diǎn)的父節(jié)點(diǎn)移來的一個鍵值。結(jié)果為完全填充的2d個鍵值。
下面是往B樹中依次插入
6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4
B-Tree有許多變種，其中最常見的是B+Tree，MySQL就普遍使用B+Tree實(shí)現(xiàn)其索引結(jié)構(gòu)。
與B-Tree相比，B+Tree有以下不同點(diǎn)：
B+的搜索與B-樹也基本相同，區(qū)別是B+樹只有達(dá)到葉子結(jié)點(diǎn)才命中（B-樹可以在非葉子結(jié)點(diǎn)命中），其性能也等價于在關(guān)鍵字全集做一次二分查找；
下面是往B+樹中依次插入
6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4
一般來說，索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲的磁盤上。
這樣的話，索引查找過程中就要產(chǎn)生磁盤I/O消耗，相對于內(nèi)存存取，I/O存取的消耗要高幾個數(shù)量級，所以評價一個數(shù)據(jù)結(jié)構(gòu)作為索引的優(yōu)劣最重要的指標(biāo)就是在查找過程中磁盤I/O操作次數(shù)的漸進(jìn)復(fù)雜度。換句話說，索引的結(jié)構(gòu)組織要盡量減少查找過程中磁盤I/O的存取次數(shù)。
假如每個盤塊可以正好存放一個B樹的結(jié)點(diǎn)（正好存放2個文件名）。那么一個BTNODE結(jié)點(diǎn)就代表一個盤塊，而子樹指針就是存放另外一個盤塊的地址。
下面，咱們來模擬下查找文件29的過程：
B+-tree的內(nèi)部結(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針。因此其內(nèi)部結(jié)點(diǎn)相對B 樹更小。如果把所有同一內(nèi)部結(jié)點(diǎn)的關(guān)鍵字存放在同一盤塊中，那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多。一次性讀入內(nèi)存中的需要查找的關(guān)鍵字也就越多。相對來說IO讀寫次數(shù)也就降低了。
舉個例子，假設(shè)磁盤中的一個盤塊容納16bytes，而一個關(guān)鍵字2bytes，一個關(guān)鍵字具體信息指針2bytes。一棵9階B-tree(一個結(jié)點(diǎn)最多8個關(guān)鍵字)的內(nèi)部結(jié)點(diǎn)需要2個盤快。而B+
樹內(nèi)部結(jié)點(diǎn)只需要1個盤快。當(dāng)需要把內(nèi)部結(jié)點(diǎn)讀入內(nèi)存中的時候，B 樹就比B+樹多一次盤塊查找時間(在磁盤中就是盤片旋轉(zhuǎn)的時間)。
由于非終結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路。所有關(guān)鍵字查詢的路徑長度相同，導(dǎo)致每一個數(shù)據(jù)的查詢效率相當(dāng)。
索引查詢是數(shù)據(jù)庫中重要的記錄查詢方法，要不要進(jìn)入索引以及在那些字段上建立索引都要和實(shí)際數(shù)據(jù)庫系統(tǒng)的查詢要求結(jié)合來考慮，下面給出實(shí)際中的一些通用的原則：
一個單獨(dú)的索引掃描只能用于這樣的條件子句：使用被索引字段和索引操作符類中的操作符， 并且這些條件以AND連接。
假設(shè)在(a, b)上有一個索引， 那么類似WHERE a = 5 AND b = 6的條件可以使用索引，但是像WHERE a = 5 OR b = 6的條件就不能直接使用索引。
一個類似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 這樣的查詢可以分解成四個在x上的獨(dú)立掃描，每個掃描使用一個條件， 最后將這些掃描的結(jié)果OR 在一起，生成最終結(jié)果。
另外一個例子是，如果我們在x 和y上有獨(dú)立的索引，一個類似WHERE x = 5 AND y = 6 這樣的查詢可以分解為幾個使用獨(dú)立索引的子句，然后把這幾個結(jié)果AND 在一起，生成最終結(jié)果。
聯(lián)合索引又叫復(fù)合索引。兩個或更多個列上的索引被稱作復(fù)合索引。
對于復(fù)合索引：Mysql從左到右的使用索引中的字段，一個查詢可以只使用索引中的一部份，但只能是最左側(cè)部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3種組合進(jìn)行查找，但不支持 b,c進(jìn)行查找 .當(dāng)最左側(cè)字段是常量引用時，索引就十分有效。
所以說創(chuàng)建復(fù)合索引時，應(yīng)該仔細(xì)考慮列的順序。對索引中的所有列執(zhí)行搜索或僅對前幾列執(zhí)行搜索時，復(fù)合索引非常有用；僅對后面的任意列執(zhí)行搜索時，復(fù)合索引則沒有用處。
這里記錄兩種方式，分別是
show status like ‘Handler_read%';大家可以注意：
+-----------------------+--------------+
| Variable_name | Value |
+-----------------------+--------------+
| Handler_read_first | 153 |
| Handler_read_key | 364 |
| Handler_read_next | 425 |
| Handler_read_prev | 598 |
| Handler_read_rnd | 605 |
| Handler_read_rnd_next | 860571 |
+-----------------------+--------------+
6 rows in set (0.00 sec)
————————————————分析這幾個值，我們可以查看當(dāng)前索引的使用情況：
查詢 schema_unused_indexes庫。
root@localhost [sys]>select * from schema_unused_indexes;
+-------------------+-------------+------------+
| object_schema | object_name | index_name |
+-------------------+-------------+------------+
| sysbench_testdata | sbtest1 | k_1 |
| sysbench_testdata | sbtest10 | k_10 |
| sysbench_testdata | sbtest3 | k_3 |
| sysbench_testdata | sbtest4 | k_4 |
| sysbench_testdata | sbtest5 | k_5 |
| sysbench_testdata | sbtest6 | k_6 |
| sysbench_testdata | sbtest7 | k_7 |
| sysbench_testdata | sbtest8 | k_8 |
| sysbench_testdata | sbtest9 | k_9 |
+-------------------+-------------+------------+
9 rows in set (0.00 sec)explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優(yōu)化的查詢語句。
新建一張表，
CREATE TABLE IF NOT EXISTS `article` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);執(zhí)行查詢，
EXPLAIN
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1響應(yīng)數(shù)據(jù)如下，
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: article
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where; Using filesort
1 row in set (0.00 sec)type 是 ALL,即最壞的情況。Extra 里還出現(xiàn)了 Using filesort,也是最壞的情況。
MySQL 在表里找到所需行的方式。包括（由左至右，由最差到最好）：
| All | index | range | ref | eq_ref | const,system | null |
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼