国产在线高潮视频,国产黄在线观看,手机福利视频日

12 MySQL索引

12.1 索引的概念

集合中的ArrayList集合的特點之一就是有索引。那么有索引會帶來的好處就是：查詢數(shù)據(jù)快！我們可以通過索引來快速查找到想要的數(shù)據(jù)。那么對于MySQL數(shù)據(jù)庫中的索引功能也是類似的！
MySQL數(shù)據(jù)庫中的索引：是幫助MySQL高效獲取數(shù)據(jù)的一種數(shù)據(jù)結(jié)構(gòu)！所以，索引的本質(zhì)就是數(shù)據(jù)結(jié)構(gòu)。
在表數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。
一張數(shù)據(jù)表，用于保存數(shù)據(jù)。一個索引配置文件，用于保存索引，每個索引都去指向了某一個數(shù)據(jù)(表格演示)
舉例，無索引和有索引的查找原理

MySQL-12-01.png

12.2 索引的分類

功能分類
- 普通索引：最基本的索引，它沒有任何限制。
- 唯一索引：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值組合必須唯一。
- 主鍵索引：一種特殊的唯一索引，不允許有空值。一般在建表時同時創(chuàng)建主鍵索引。
- 組合索引：顧名思義，就是將單列索引進行組合。
- 外鍵索引：只有InnoDB引擎支持外鍵索引，用來保證數(shù)據(jù)的一致性、完整性和實現(xiàn)級聯(lián)操作。
- 全文索引：快速匹配全部文檔的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
結(jié)構(gòu)分類
- B+Tree索引：MySQL使用最頻繁的一個索引數(shù)據(jù)結(jié)構(gòu)，是InnoDB和MyISAM存儲引擎默認的索引類型。
- Hash索引 : MySQL中Memory存儲引擎默認支持的索引類型。

12.3 索引的操作

數(shù)據(jù)準備

-- 創(chuàng)建db12數(shù)據(jù)庫
CREATE DATABASE db12;

-- 使用db12數(shù)據(jù)庫
USE db12;

-- 創(chuàng)建student表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    age INT,
    score INT
);
-- 添加數(shù)據(jù)
INSERT INTO student VALUES (NULL,'張三',23,98),(NULL,'李四',24,95),
(NULL,'王五',25,96),(NULL,'趙六',26,94),(NULL,'周七',27,99);

創(chuàng)建索引
- 注意：如果一個表中有一列是主鍵，那么就會默認為其創(chuàng)建主鍵索引！(主鍵列不需要單獨創(chuàng)建索引)

-- 標準語法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名稱
[USING 索引類型]  -- 默認是B+TREE
ON 表名(列名...);

-- 為student表中姓名列創(chuàng)建一個普通索引
CREATE INDEX idx_name ON student(NAME);

-- 為student表中年齡列創(chuàng)建一個唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);

查看索引

-- 標準語法
SHOW INDEX FROM 表名;

-- 查看student表中的索引
SHOW INDEX FROM student;

alter語句添加索引

-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名稱(列名);

-- 組合索引
ALTER TABLE 表名 ADD INDEX 索引名稱(列名1,列名2,...);

-- 主鍵索引
ALTER TABLE 表名 ADD PRIMARY KEY(主鍵列名); 

-- 外鍵索引(添加外鍵約束，就是外鍵索引)
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名 FOREIGN KEY (本表外鍵列名) REFERENCES 主表名(主鍵列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名稱(列名);

-- 全文索引(mysql只支持文本類型)
ALTER TABLE 表名 ADD FULLTEXT 索引名稱(列名);


-- 為student表中name列添加全文索引
ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);

-- 查看student表中的索引
SHOW INDEX FROM student;

刪除索引

-- 標準語法
DROP INDEX 索引名稱 ON 表名;

-- 刪除student表中的idx_score索引
DROP INDEX idx_score ON student;

-- 查看student表中的索引
SHOW INDEX FROM student;

12.4 索引效率的測試

-- 創(chuàng)建product商品表
CREATE TABLE product(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品id
    NAME VARCHAR(10),           -- 商品名稱
    price INT                           -- 商品價格
);

-- 定義存儲函數(shù)，生成長度為10的隨機字符串并返回
DELIMITER $

CREATE FUNCTION rand_string() 
RETURNS VARCHAR(255)
BEGIN
    DECLARE big_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
    DECLARE small_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= 10 DO
        SET small_str =CONCAT(small_str,SUBSTRING(big_str,FLOOR(1+RAND()*52),1));
        SET i=i+1;
    END WHILE;
    
    RETURN small_str;
END$

DELIMITER ;



-- 定義存儲過程，添加100萬條數(shù)據(jù)到product表中
DELIMITER $

CREATE PROCEDURE pro_test()
BEGIN
    DECLARE num INT DEFAULT 1;
    
    WHILE num <= 1000000 DO
        INSERT INTO product VALUES (NULL,rand_string(),num);
        SET num = num + 1;
    END WHILE;
END$

DELIMITER ;

-- 調(diào)用存儲過程
CALL pro_test();


-- 查詢總記錄條數(shù)
SELECT COUNT(*) FROM product;



-- 查詢product表的索引
SHOW INDEX FROM product;

-- 查詢name為OkIKDLVwtG的數(shù)據(jù)   (0.049)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';

-- 通過id列查詢OkIKDLVwtG的數(shù)據(jù)  (1毫秒)
SELECT * FROM product WHERE id=999998;

-- 為name列添加索引
ALTER TABLE product ADD INDEX idx_name(NAME);

-- 查詢name為OkIKDLVwtG的數(shù)據(jù)   (0.001)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';


/*
    范圍查詢
*/
-- 查詢價格為800~1000之間的所有數(shù)據(jù) (0.052)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;

/*
    排序查詢
*/
-- 查詢價格為800~1000之間的所有數(shù)據(jù),降序排列  (0.083)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;

-- 為price列添加索引
ALTER TABLE product ADD INDEX idx_price(price);

-- 查詢價格為800~1000之間的所有數(shù)據(jù) (0.011)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;

-- 查詢價格為800~1000之間的所有數(shù)據(jù),降序排列  (0.001)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;

12.5 索引的實現(xiàn)原則

索引是在MySQL的存儲引擎中實現(xiàn)的，所以每種存儲引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引類型。這里我們主要介紹InnoDB引擎的實現(xiàn)的B+Tree索引。
B+Tree是一種樹型數(shù)據(jù)結(jié)構(gòu)，是B-Tree的變種。通常使用在數(shù)據(jù)庫和操作系統(tǒng)中的文件系統(tǒng)，特點是能夠保持數(shù)據(jù)穩(wěn)定有序。我們逐步的來了解一下。

12.5.1 磁盤存儲

系統(tǒng)從磁盤讀取數(shù)據(jù)到內(nèi)存時是以磁盤塊（block）為基本單位的
位于同一個磁盤塊中的數(shù)據(jù)會被一次性讀取出來，而不是需要什么取什么。
InnoDB存儲引擎中有頁（Page）的概念，頁是其磁盤管理的最小單位。InnoDB存儲引擎中默認每個頁的大小為16KB。
InnoDB引擎將若干個地址連接磁盤塊，以此來達到頁的大小16KB，在查詢數(shù)據(jù)時如果一個頁中的每條數(shù)據(jù)都能有助于定位數(shù)據(jù)記錄的位置，這將會減少磁盤I/O次數(shù)，提高查詢效率。

12.5.2 BTree

BTree結(jié)構(gòu)的數(shù)據(jù)可以讓系統(tǒng)高效的找到數(shù)據(jù)所在的磁盤塊。為了描述BTree，首先定義一條記錄為一個二元組[key, data] ，key為記錄的鍵值，對應表中的主鍵值，data為一行記錄中除主鍵外的數(shù)據(jù)。對于不同的記錄，key值互不相同。BTree中的每個節(jié)點根據(jù)實際情況可以包含大量的關(guān)鍵字信息和分支，如下圖所示為一個3階的BTree：

MySQL-12-02.png

根據(jù)圖中結(jié)構(gòu)顯示，每個節(jié)點占用一個盤塊的磁盤空間，一個節(jié)點上有兩個升序排序的關(guān)鍵字和三個指向子樹根節(jié)點的指針，指針存儲的是子節(jié)點所在磁盤塊的地址。兩個關(guān)鍵詞劃分成的三個范圍域?qū)齻€指針指向的子樹的數(shù)據(jù)的范圍域。以根節(jié)點為例，關(guān)鍵字為17和35，P1指針指向的子樹的數(shù)據(jù)范圍為小于17，P2指針指向的子樹的數(shù)據(jù)范圍為17~35，P3指針指向的子樹的數(shù)據(jù)范圍為大于35。

查找順序：

模擬查找15的過程 : 

1.根節(jié)點找到磁盤塊1，讀入內(nèi)存?！敬疟PI/O操作第1次】
    比較關(guān)鍵字15在區(qū)間（<17），找到磁盤塊1的指針P1。
2.P1指針找到磁盤塊2，讀入內(nèi)存?！敬疟PI/O操作第2次】
    比較關(guān)鍵字15在區(qū)間（>12），找到磁盤塊2的指針P3。
3.P3指針找到磁盤塊7，讀入內(nèi)存。【磁盤I/O操作第3次】
    在磁盤塊7中找到關(guān)鍵字15。
    
-- 分析上面過程，發(fā)現(xiàn)需要3次磁盤I/O操作，和3次內(nèi)存查找操作。
-- 由于內(nèi)存中的關(guān)鍵字是一個有序表結(jié)構(gòu)，可以利用二分法查找提高效率。而3次磁盤I/O操作是影響整個BTree查找效率的決定因素。BTree使用較少的節(jié)點個數(shù)，使每次磁盤I/O取到內(nèi)存的數(shù)據(jù)都發(fā)揮了作用，從而提高了查詢效率。

12.5.3 B+Tree

B+Tree是在BTree基礎上的一種優(yōu)化，使其更適合實現(xiàn)外存儲索引結(jié)構(gòu)，InnoDB存儲引擎就是用B+Tree實現(xiàn)其索引結(jié)構(gòu)。
從上一節(jié)中的BTree結(jié)構(gòu)圖中可以看到每個節(jié)點中不僅包含數(shù)據(jù)的key值，還有data值。而每一個頁的存儲空間是有限的，如果data數(shù)據(jù)較大時將會導致每個節(jié)點（即一個頁）能存儲的key的數(shù)量很小，當存儲的數(shù)據(jù)量很大時同樣會導致B-Tree的深度較大，增大查詢時的磁盤I/O次數(shù)，進而影響查詢效率。在B+Tree中，所有數(shù)據(jù)記錄節(jié)點都是按照鍵值大小順序存放在同一層的葉子節(jié)點上，而非葉子節(jié)點上只存儲key值信息，這樣可以大大加大每個節(jié)點存儲的key值數(shù)量，降低B+Tree的高度。
B+Tree相對于BTree區(qū)別：
- 非葉子節(jié)點只存儲鍵值信息。
- 所有葉子節(jié)點之間都有一個連接指針。
- 數(shù)據(jù)記錄都存放在葉子節(jié)點中。
將上一節(jié)中的BTree優(yōu)化，由于B+Tree的非葉子節(jié)點只存儲鍵值信息，假設每個磁盤塊能存儲4個鍵值及指針信息，則變成B+Tree后其結(jié)構(gòu)如下圖所示：

MySQL-12-03.png

通常在B+Tree上有兩個頭指針，一個指向根節(jié)點，另一個指向關(guān)鍵字最小的葉子節(jié)點，而且所有葉子節(jié)點（即數(shù)據(jù)節(jié)點）之間是一種鏈式環(huán)結(jié)構(gòu)。因此可以對B+Tree進行兩種查找運算：

【有范圍】對于主鍵的范圍查找和分頁查找
【有順序】從根節(jié)點開始，進行隨機查找

實際情況中每個節(jié)點可能不能填充滿，因此在數(shù)據(jù)庫中，B+Tree的高度一般都在2_{4層。MySQL的InnoDB存儲引擎在設計時是將根節(jié)點常駐內(nèi)存的，也就是說查找某一鍵值的行記錄時最多只需要1}3次磁盤I/O操作。

12.6 總結(jié)：索引的設計原則

索引的設計可以遵循一些已有的原則，創(chuàng)建索引的時候請盡量考慮符合這些原則，便于提升索引的使用效率，更高效的使用索引。

創(chuàng)建索引時的原則
- 對查詢頻次較高，且數(shù)據(jù)量比較大的表建立索引。
- 使用唯一索引，區(qū)分度越高，使用索引的效率越高。
- 索引字段的選擇，最佳候選列應當從where子句的條件中提取，如果where子句中的組合比較多，那么應當挑選最常用、過濾效果最好的列的組合。
- 使用短索引，索引創(chuàng)建之后也是使用硬盤來存儲的，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率。假如構(gòu)成索引的字段總長度比較短，那么在給定大小的存儲塊內(nèi)可以存儲更多的索引值，相應的可以有效的提升MySQL訪問索引的I/O效率。
- 索引可以有效的提升查詢數(shù)據(jù)的效率，但索引數(shù)量不是多多益善，索引越多，維護索引的代價自然也就水漲船高。對于插入、更新、刪除等DML操作比較頻繁的表來說，索引過多，會引入相當高的維護代價，降低DML操作的效率，增加相應操作的時間消耗。另外索引過多的話，MySQL也會犯選擇困難病，雖然最終仍然會找到一個可用的索引，但無疑提高了選擇的代價。
聯(lián)合索引的特點

在mysql建立聯(lián)合索引時會遵循最左前綴匹配的原則，即最左優(yōu)先，在檢索數(shù)據(jù)時從聯(lián)合索引的最左邊開始匹配，
對列name列、address和列phone列建一個聯(lián)合索引

ALTER TABLE user ADD INDEX index_three(name,address,phone);

聯(lián)合索引index_three實際建立了(name)、(name,address)、(name,address,phone)三個索引。所以下面的三個SQL語句都可以命中索引。

SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '張三';
SELECT * FROM user WHERE name = '張三' AND address = '北京';
SELECT * FROM user WHERE name = '張三';

上面三個查詢語句執(zhí)行時會依照最左前綴匹配原則，檢索時分別會使用索引

(name,address,phone)
(name,address)
(name)

進行數(shù)據(jù)匹配。

索引的字段可以是任意順序的，如：

-- 優(yōu)化器會幫助我們調(diào)整順序，下面的SQL語句都可以命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '張三';

Mysql的優(yōu)化器會幫助我們調(diào)整where條件中的順序，以匹配我們建立的索引。

聯(lián)合索引中最左邊的列不包含在條件查詢中，所以根據(jù)上面的原則，下面的SQL語句就不會命中索引。

-- 聯(lián)合索引中最左邊的列不包含在條件查詢中，下面的SQL語句就不會命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345';

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

12 MySQL索引

12 MySQL索引

12 MySQL索引

12.1 索引的概念

12.2 索引的分類

12.3 索引的操作

12.4 索引效率的測試

12.5 索引的實現(xiàn)原則

12.5.1 磁盤存儲

12.5.2 BTree

12.5.3 B+Tree

12.6 總結(jié)：索引的設計原則

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

12 MySQL索引

12 MySQL索引

12.1 索引的概念

12.2 索引的分類

12.3 索引的操作

12.4 索引效率的測試

12.5 索引的實現(xiàn)原則

12.5.1 磁盤存儲

12.5.2 BTree

12.5.3 B+Tree

12.6 總結(jié)：索引的設計原則

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av