溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
一、MySQL有哪些索引類型
索引的分類可以從多個角度進行，下面分別從數據結構，物理存儲和業務邏輯三個維度進行劃分。
關于B+樹索引，后面會深入解析
現在MyISAM和InnoDB引擎都支持了
用于對GIS數據類型創建SPATIAL索引
索引是通過二叉樹的數據結構來描述的，我們可以這么理解聚簇索引：索引的葉節點就是數據節點。而非聚簇索引的葉節點仍然是索引節點，只不過有一個指針指向對應的數據塊。
舉個例子說明下：
create table student (
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(255),
PRIMARY KEY(`id`),
KEY(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;該表中主鍵id是該表的聚集索引、name為非聚集索引；表中的每行數據都是按照聚集索引id排序存儲的；比如要查找name='Arla'和name='Arle'的兩個同學，他們在name索引表中位置可能是相鄰的，但是實際存儲位置可能差的很遠。name索引表節點按照name排序，檢索的是每一行數據的主鍵。聚集索引表按照主鍵id排序，檢索的是每一行數據的真實內容。
主鍵索引是一種特殊的唯一索引，不允許有空值
復合索引指多個字段上創建的索引，只有在查詢條件中使用了創建索引時的第一個字段，索引才會被使用。使用復合索引時遵循最左前綴集合
空間索引是對空間數據類型的字段建立的索引，MYSQL中的空間數據類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL關鍵字進行擴展，使得能夠用于創建正規索引類型的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲引擎為MYISAM的表中創建.
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)
);查看表結構
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。這樣做的目的還是為了提高查詢速度。對于字符型的數據，可以不用查詢全部信息，只查詢其前面的若干字符信息。
create table index5(
id int,
name varchar(20),
sex char(4),
index index5_ns(name.sex)
);這是我們可以看到，name 和sex字段上已經創建了index_ns索引。
在example0() 表中的id 創建名為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字段上創建名為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;目前大部分數據庫系統及文件系統都采用B-Tree或其變種B+Tree作為索引結構，那么索引樹是如何維護的？
查找是數據結構和算法中一個非常重要的概念。
B-Tree是一種多路搜索樹（并不是二叉的）：
B-樹的搜索，從根結點開始，對結點內的關鍵字（有序）序列進行二分查找，如果命中則結束，否則進入查詢關鍵字所屬范圍的兒子結點；重復，直到所對應的兒子指針為空，或已經是葉子結點；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樹中每一個內部節點會包含一定數量的鍵值。通常，鍵值的數量被選定在d和2d之間。在實際中，鍵值占用了節點中大部分的空間。因數2將保證節點可以被拆分或組合。如果一個內部節點有2d個鍵值，那么添加一個鍵值給此節點的過程，將會拆分2d鍵值為2個d鍵值的節點，并把此鍵值添加給父節點。每一個拆分的節點需要最小數目的鍵值。相似地，如果一個內部節點和他的鄰居兩者都有d個鍵值，那么將通過它與鄰居的合并來刪除一個鍵值。刪除此鍵值將導致此節點擁有d-1個鍵值;與鄰居的合并則加上d個鍵值，再加上從鄰居節點的父節點移來的一個鍵值。結果為完全填充的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實現其索引結構。
與B-Tree相比，B+Tree有以下不同點：
B+的搜索與B-樹也基本相同，區別是B+樹只有達到葉子結點才命中（B-樹可以在非葉子結點命中），其性能也等價于在關鍵字全集做一次二分查找；
下面是往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
一般來說，索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上。
這樣的話，索引查找過程中就要產生磁盤I/O消耗，相對于內存存取，I/O存取的消耗要高幾個數量級，所以評價一個數據結構作為索引的優劣最重要的指標就是在查找過程中磁盤I/O操作次數的漸進復雜度。換句話說，索引的結構組織要盡量減少查找過程中磁盤I/O的存取次數。
假如每個盤塊可以正好存放一個B樹的結點（正好存放2個文件名）。那么一個BTNODE結點就代表一個盤塊，而子樹指針就是存放另外一個盤塊的地址。
下面，咱們來模擬下查找文件29的過程：
B+-tree的內部結點并沒有指向關鍵字具體信息的指針。因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。
舉個例子，假設磁盤中的一個盤塊容納16bytes，而一個關鍵字2bytes，一個關鍵字具體信息指針2bytes。一棵9階B-tree(一個結點最多8個關鍵字)的內部結點需要2個盤快。而B+
樹內部結點只需要1個盤快。當需要把內部結點讀入內存中的時候，B 樹就比B+樹多一次盤塊查找時間(在磁盤中就是盤片旋轉的時間)。
由于非終結點并不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。
索引查詢是數據庫中重要的記錄查詢方法，要不要進入索引以及在那些字段上建立索引都要和實際數據庫系統的查詢要求結合來考慮，下面給出實際中的一些通用的原則：
一個單獨的索引掃描只能用于這樣的條件子句：使用被索引字段和索引操作符類中的操作符， 并且這些條件以AND連接。
假設在(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上的獨立掃描，每個掃描使用一個條件， 最后將這些掃描的結果OR 在一起，生成最終結果。
另外一個例子是，如果我們在x 和y上有獨立的索引，一個類似WHERE x = 5 AND y = 6 這樣的查詢可以分解為幾個使用獨立索引的子句，然后把這幾個結果AND 在一起，生成最終結果。
聯合索引又叫復合索引。兩個或更多個列上的索引被稱作復合索引。
對于復合索引：Mysql從左到右的使用索引中的字段，一個查詢可以只使用索引中的一部份，但只能是最左側部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 .當最左側字段是常量引用時，索引就十分有效。
所以說創建復合索引時，應該仔細考慮列的順序。對索引中的所有列執行搜索或僅對前幾列執行搜索時，復合索引非常有用；僅對后面的任意列執行搜索時，復合索引則沒有用處。
這里記錄兩種方式，分別是
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)
————————————————分析這幾個值，我們可以查看當前索引的使用情況：
查詢 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語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。
新建一張表，
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`)
);執行查詢，
EXPLAIN
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1響應數據如下，
*************************** 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 里還出現了 Using filesort,也是最壞的情況。
MySQL 在表里找到所需行的方式。包括（由左至右，由最差到最好）：
| All | index | range | ref | eq_ref | const,system | null |
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
