在线播放国产一区,精品综合日韩,99久久在线不卡

索引類型

索引的建立對于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。mysql索引大致可以分為普通索引、唯一索引、主鍵索引、組合索引、全文索引，下面我們就來具體了解下各個索引的區(qū)別：

普通索引

基本的索引類型，值可以為空，沒有唯一性的限制。

- 直接創(chuàng)建索引
CREATE INDEX index_name ON table_name(col_name);
-- 修改表結(jié)構(gòu)的方式添加索引
ALTER TABLE table_name ADD INDEX index_name(col_name);

-- 創(chuàng)建表的時候同時創(chuàng)建索引
CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` varchar(255)  NOT NULL ,
    `age` int(4)  NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (age(4))
)

-- 刪除索引
DROP INDEX index_name ON table_name;
--  或
alter table `表名` drop index 索引名;

唯一索引

索引列中的值必須是唯一的，但是允許為空值（只允許存在一條空值）。

-- 創(chuàng)建單個索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
-- 創(chuàng)建多個索引
CREATE UNIQUE INDEX index_name on table_name(col_name,...);

-- 修改表結(jié)構(gòu)
-- 單個
ALTER TABLE table_name ADD UNIQUE index index_name(col_name);
-- 多個
ALTER TABLE table_name ADD UNIQUE index index_name(col_name,...);

主鍵索引

主鍵是一種唯一性索引，但它必須指定為PRIMARY KEY，每個表只能有一個主鍵。

-- 主鍵索引(創(chuàng)建表時添加)
CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` varchar(255)  NOT NULL ,
    `age` int(4)  NULL ,
    PRIMARY KEY (`id`)
)

-- 主鍵索引(創(chuàng)建表后添加)
CREATE TABLE `order` (
    `orderId` varchar(36) NOT NULL,
    `productId` varchar(36)  NOT NULL ,
    `time` varchar(20) NULL DEFAULT NULL
)
alter table `order` add primary key(`orderId`);

組合索引

在多個字段上創(chuàng)建的索引。組合索引遵守“最左前綴”原則，即在查詢條件中使用了復(fù)合索引的第一個字段，索引才會被使用。因此，在復(fù)合索引中索引列的順序至關(guān)重要。

-- 創(chuàng)建一個復(fù)合索引
create index index_name on table_name(col_name1,col_name2,...);

-- 修改表結(jié)構(gòu)的方式添加索引
alter table table_name add index index_name(col_name,col_name2,...);

全文索引

全文索引的索引類型為FULLTEXT。全文索引可以在varchar、char、text類型的列上創(chuàng)建?？梢酝ㄟ^ALTER TABLE或CREATE INDEX命令創(chuàng)建。對于大規(guī)模的數(shù)據(jù)集，通過ALTER TABLE（或CREATE INDEX）命令創(chuàng)建全文索引要比把記錄插入帶有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。全文索引不支持中文需要借sphinx(coreseek)或迅搜<、code>技術(shù)處理中文。

-- 創(chuàng)建表的適合添加全文索引
CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` text  NOT NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
     PRIMARY KEY (`id`),
    FULLTEXT (content)
)

-- 修改表結(jié)構(gòu)添加全文索引
ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(col_name);

索引實(shí)現(xiàn)

下面我們來具體看下載不同的數(shù)據(jù)庫引擎下上述索引的實(shí)現(xiàn)原理：

innodb索引

主鍵索引

每個InnoDB表都有一個主鍵索引(也叫聚簇索引) ，聚簇索引使用B+樹構(gòu)建，葉子節(jié)點(diǎn)存儲的數(shù)據(jù)是整行記錄。。InnoDB創(chuàng)建索引的具體規(guī)則如下：

在表上定義主鍵PRIMARY KEY，InnoDB將主鍵索引用作聚簇索引
如果表沒有定義主鍵，且存在非空unique列，則選擇第一個非空unique列為主鍵，構(gòu)建聚簇索引；
如果以上兩個都沒有，InnoDB 會使用一個6 字節(jié)長整型的隱式字段 ROWID字段構(gòu)建聚簇索引。該ROWID字段會在插入新行時自動遞增

除聚簇索引之外的所有索引都稱為輔助索引。在中InnoDB，輔助索引中的葉子節(jié)點(diǎn)存儲的數(shù)據(jù)是該行的主鍵值。在檢索時，InnoDB使用此主鍵值在聚簇索引中搜索行記錄。主鍵索引的葉子節(jié)點(diǎn)會存儲數(shù)據(jù)行（所以一般進(jìn)行的非主鍵索引檢索至少需要經(jīng)歷兩次磁盤IO），輔助索引只會存儲主鍵值。具體主鍵索引存儲結(jié)構(gòu)如下：

202010241146330.png

等值查詢數(shù)據(jù)

先在主鍵樹中從根節(jié)點(diǎn)開始檢索，將根節(jié)點(diǎn)加載到內(nèi)存，比較28<75，走左路。（1次磁盤IO）
將左子樹節(jié)點(diǎn)加載到內(nèi)存中，比較16<28<47，向下檢索。（1次磁盤IO）
檢索到葉節(jié)點(diǎn)，將節(jié)點(diǎn)加載到內(nèi)存中遍歷，比較16<28，18<28，28=28。查找到值等于28的索引項(xiàng)，直接可以獲取整行數(shù)據(jù)。將改記錄返回給客戶端。（1次磁盤IO）

磁盤IO數(shù)量：3次。

20201024114716460 (1).png

輔助索引

除聚簇索引之外的所有索引都稱為輔助索引，InnoDB的輔助索引只會存儲主鍵值而非磁盤地址。以表user的age列為例，age索引的索引結(jié)果如下圖：

20201024114750255.png

底層葉子節(jié)點(diǎn)的按照（age，id）的順序排序，先按照age列從小到大排序，age列相同時按照id列從小到大排序。使用輔助索引需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后使用主鍵到主索引中檢索獲得記錄。

場景一：等值查詢的情況

select * from t_user_innodb where age=19;

2020102411481097.png

根據(jù)在輔助索引樹中獲取的主鍵id，到主鍵索引樹檢索數(shù)據(jù)的過程稱為回表查詢。

磁盤IO數(shù)：輔助索引3次+獲取記錄回表3次

組合索引

還是以自己創(chuàng)建的一個表為例：表 abc_innodb，id為主鍵索引，創(chuàng)建了一個聯(lián)合索引idx_abc(a,b,c)。

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

組合索引的數(shù)據(jù)結(jié)構(gòu)：

20201024114900213.png

組合索引的查詢過程：

select * from abc_innodb where a = 13 and b = 16 and c = 4;

20201024115012887.png

最左匹配原則

最左前綴匹配原則和聯(lián)合索引的索引存儲結(jié)構(gòu)和檢索方式是有關(guān)系的。

在組合索引樹中，最底層的葉子節(jié)點(diǎn)按照第一列a列從左到右遞增排列，但是b列和c列是無序的，b列只有在a列值相等的情況下小范圍內(nèi)遞增有序，而c列只能在a，b兩列相等的情況下小范圍內(nèi)遞增有序。

就像上面的查詢，B+樹會先比較a列來確定下一步應(yīng)該搜索的方向，往左還是往右。如果a列相同再比較b列。但是如果查詢條件沒有a列，B+樹就不知道第一步應(yīng)該從哪個節(jié)點(diǎn)查起。

可以說創(chuàng)建的idx_abc(a,b,c)索引，相當(dāng)于創(chuàng)建了(a)、（a,b）（a,b,c）三個索引。、

組合索引的最左前綴匹配原則：使用組合索引查詢時，mysql會一直向右匹配直至遇到范圍查詢(>、<、between、like)就停止匹配。

覆蓋索引

覆蓋索引并不是說是索引結(jié)構(gòu)，覆蓋索引是一種很常用的優(yōu)化手段。因?yàn)樵谑褂幂o助索引的時候，我們只可以拿到主鍵值，相當(dāng)于獲取數(shù)據(jù)還需要再根據(jù)主鍵查詢主鍵索引再獲取到數(shù)據(jù)。但是試想下這么一種情況，在上面abc_innodb表中的組合索引查詢時，如果我只需要abc字段的，那是不是意味著我們查詢到組合索引的葉子節(jié)點(diǎn)就可以直接返回了，而不需要回表。這種情況就是覆蓋索引?？梢钥匆幌聢?zhí)行計(jì)劃：

MyIsam索引

MyISAM的數(shù)據(jù)文件和索引文件是分開存儲的。MyISAM使用B+樹構(gòu)建索引樹時，葉子節(jié)點(diǎn)中存儲的鍵值為索引列的值，數(shù)據(jù)為索引所在行的磁盤地址。

主鍵索引

20201024114325883.png

表user的索引存儲在索引文件user.MYI中，數(shù)據(jù)文件存儲在數(shù)據(jù)文件 user.MYD中。簡單分析下查詢時的磁盤IO情況：

場景一：根據(jù)主鍵等值查詢數(shù)據(jù)

20201024114404727.png

先在主鍵樹中從根節(jié)點(diǎn)開始檢索，將根節(jié)點(diǎn)加載到內(nèi)存，比較28<75，走左路。（1次磁盤IO）
將左子樹節(jié)點(diǎn)加載到內(nèi)存中，比較16<28<47，向下檢索。（1次磁盤IO）
檢索到葉節(jié)點(diǎn)，將節(jié)點(diǎn)加載到內(nèi)存中遍歷，比較16<28，18<28，28=28。查找到值等于30的索引項(xiàng)。（1次磁盤IO）
從索引項(xiàng)中獲取磁盤地址，然后到數(shù)據(jù)文件user.MYD中獲取對應(yīng)整行記錄。（1次磁盤IO）
將記錄返給客戶端。

磁盤IO次數(shù)：3次索引檢索+記錄數(shù)據(jù)檢索。

場景二：根據(jù)主鍵范圍查詢數(shù)據(jù)

20201024114510253.png

先在主鍵樹中從根節(jié)點(diǎn)開始檢索，將根節(jié)點(diǎn)加載到內(nèi)存，比較28<75，走左路。（1次磁盤IO）
將左子樹節(jié)點(diǎn)加載到內(nèi)存中，比較16<28<47，向下檢索。（1次磁盤IO）
檢索到葉節(jié)點(diǎn)，將節(jié)點(diǎn)加載到內(nèi)存中遍歷比較16<28，18<28，28=28<47。查找到值等于28的索引項(xiàng)。
- 根據(jù)磁盤地址從數(shù)據(jù)文件中獲取行記錄緩存到結(jié)果集中。（1次磁盤IO）
- 我們的查詢語句時范圍查找，需要向后遍歷底層葉子鏈表，直至到達(dá)最后一個不滿足篩選條件。
向后遍歷底層葉子鏈表，將下一個節(jié)點(diǎn)加載到內(nèi)存中，遍歷比較，28<47=47，根據(jù)磁盤地址從數(shù)據(jù)文件中獲取行記錄緩存到結(jié)果集中。（1次磁盤IO）
最后得到兩條符合篩選條件，將查詢結(jié)果集返給客戶端。

磁盤IO次數(shù)：4次索引檢索+記錄數(shù)據(jù)檢索。

備注：以上分析僅供參考，MyISAM在查詢時，會將索引節(jié)點(diǎn)緩存在MySQL緩存中，而數(shù)據(jù)緩存依賴于操作系統(tǒng)自身的緩存，所以并不是每次都是走的磁盤，這里只是為了分析索引的使用過程。

輔助索引

在 MyISAM 中，輔助索引和主鍵索引的結(jié)構(gòu)是一樣的，沒有任何區(qū)別，葉子節(jié)點(diǎn)的數(shù)據(jù)存儲的都是行記錄的磁盤地址。只是主鍵索引的鍵值是唯一的，而輔助索引的鍵值可以重復(fù)。查詢數(shù)據(jù)時，由于輔助索引的鍵值不唯一，可能存在多個擁有相同的記錄，所以即使是等值查詢，也需要按照范圍查詢的方式在輔助索引樹中檢索數(shù)據(jù)。

索引失效情況

情況一：where語句中包含or時，可能會導(dǎo)致索引失效

使用or并不是一定會使索引失效，你需要看or左右兩邊的查詢列是否命中相同的索引。

-- 假設(shè)user表中的user_id列有索引，age列沒有索引
-- 能命中索引
select * from user where user_id = 1 or user_id = 2;
-- 無法命中索引
select * from user where user_id = 1 or age = 20;
-- 假設(shè)age列也有索引的話，依然是無法命中索引的
select * from user where user_id = 1 or age = 20;

可以根據(jù)情況盡量使用union all或者in來代替，這兩個語句的執(zhí)行效率也比or好些。

情況二：where語句中索引列使用了負(fù)向查詢，可能會導(dǎo)致索引失效

負(fù)向查詢包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。其實(shí)負(fù)向查詢并不絕對會索引失效，這要看MySQL優(yōu)化器的判斷，全表掃描或者走索引哪個成本低了。

情況三：索引字段可以為null，使用is null或is not null時，可能會導(dǎo)致索引失效

其實(shí)單個索引字段，使用is null或is not null時，是可以命中索引的。

情況四：在索引列上使用內(nèi)置函數(shù)，一定會導(dǎo)致索引失效

比如下面語句中索引列l(wèi)ogin_time上使用了函數(shù)，會索引失效：

select * from user where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;

情況五：隱式類型轉(zhuǎn)換導(dǎo)致的索引失效

如下面語句中索引列user_id為varchar類型，不會命中索引：

select * from user where user_id = 12;

情況六：對索引列進(jìn)行運(yùn)算，一定會導(dǎo)致索引失效

運(yùn)算如+，-，*，/等，如下：

select * from user where age - 1 = 10;

優(yōu)化的話，要把運(yùn)算放在值上，或者在應(yīng)用程序中直接算好，比如：

select * from user where age = 10 - 1;

情況七：like通配符可能會導(dǎo)致索引失效

like查詢以%開頭時，會導(dǎo)致索引失效。解決辦法有兩種：

將%移到后面，如：

select * from user where `name` like '李%';

利用覆蓋索引來命中索引：

select name from user where `name` like '%李%';

情況八：MySQL優(yōu)化器的最終選擇，不走索引
上面有提到，即使完全符合索引生效的場景，考慮到實(shí)際數(shù)據(jù)量等原因，最終是否使用索引還要看MySQL優(yōu)化器的判斷。當(dāng)然你也可以在sql語句中寫明強(qiáng)制走某個索引。

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

淺談mysql數(shù)據(jù)庫索引

淺談mysql數(shù)據(jù)庫索引

索引類型

普通索引

唯一索引

主鍵索引

組合索引

全文索引

索引實(shí)現(xiàn)

innodb索引

主鍵索引

輔助索引

組合索引

覆蓋索引

MyIsam索引

主鍵索引

輔助索引

索引失效情況

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

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

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

淺談mysql數(shù)據(jù)庫索引

索引類型

普通索引

唯一索引

主鍵索引

組合索引

全文索引

索引實(shí)現(xiàn)

innodb索引

主鍵索引

輔助索引

組合索引

覆蓋索引

MyIsam索引

主鍵索引

輔助索引

索引失效情況

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

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

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