九色国产,午夜在线视频,新黄色网址,九九色综合,天天做夜夜做久久做狠狠,天天躁夜夜躁狠狠躁2021a,久久不卡一区二区三区

MySQL系列-InnoDB索引介紹和管理

運(yùn)維少年 運(yùn)維少年

系列文章說明

MySQL系列文章包含了軟件安裝、具體使用、備份恢復(fù)等內(nèi)容，主要用于記錄個(gè)人的學(xué)習(xí)筆記，主要使用的MySQL版本為5.7.28，服務(wù)器系統(tǒng)版本為CentOS 7.5。本章節(jié)內(nèi)容為InnoDB索引管理。

索引

索引 索引的定義

---

索引是一種數(shù)據(jù)結(jié)構(gòu)，能夠幫助我們快速的檢索數(shù)據(jù)庫(kù)中的數(shù)據(jù)。通俗來講，索引就像一本書的目錄一樣，我們可以通過目錄快速地查找我們需要的內(nèi)容。

索引 索引的優(yōu)缺點(diǎn)

---

索引的優(yōu)點(diǎn)：

1、可以加快數(shù)據(jù)的檢索速度
2、通過索引，可以在使用的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能

索引的缺點(diǎn)：

1、當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改時(shí)，索引需要?jiǎng)討B(tài)維護(hù)，會(huì)減低增/改/刪的執(zhí)行效率
2、索引需要占用物理空間

索引 索引的分類

---

在MySQL InnoDB中，索引可以分為聚簇索引、輔助索引（非聚簇索引）兩類。

聚簇索引

索引 B Tree 的葉子節(jié)點(diǎn)存儲(chǔ)了整行數(shù)據(jù)的是主鍵索引，也被稱之為聚簇索引。聚簇索引是對(duì)磁盤上實(shí)際數(shù)據(jù)重新組織以按指定的一個(gè)或多個(gè)列的值排序的算法。特點(diǎn)是存儲(chǔ)數(shù)據(jù)的順序和索引順序一致。一般情況下主鍵會(huì)默認(rèn)創(chuàng)建聚簇索引，且一張表只允許存在一個(gè)聚簇索引 ， 因?yàn)閿?shù)據(jù)一旦存儲(chǔ)，順序只能有一種。找到了索引就找到了需要的數(shù)據(jù)，那么這個(gè)索引就是聚簇索引，所以主鍵就是聚簇索引，修改聚簇索引其實(shí)就是修改主鍵。

一般來說，一個(gè)表一定有聚簇索引，就算不定義，InnoDB也會(huì)自動(dòng)選擇列生成索引：
1) 有主鍵時(shí)，根據(jù)主鍵創(chuàng)建聚簇索引
2) 沒有主鍵時(shí)，會(huì)用一個(gè)唯一且不為空的索引列作為主鍵，成為此表的聚簇索引
3) 如果以上兩個(gè)都不滿足那innodb自己創(chuàng)建一個(gè)虛擬的聚集索引

如有下表：

CREATE TABLE world.student(
`id` INT AUTO_INCREMENT NOT NULL COMMENT 'id',
`name` VARCHAR(10) NOT NULL COMMENT '姓名',
`age` INT  NOT NULL COMMENT '年齡',
PRIMARY KEY(id),
INDEX idx_name(NAME)
)ENGINE=INNODB DEFAULT CHARSET='utf8mb4';

INSERT INTO world.`student`(NAME,age) VALUES('張三',24),('李四',20),('王五',21),('運(yùn)維少年',18);

則聚簇索引結(jié)構(gòu)如下：

聚簇索引查找過程：

輔助索引

索引B Tree 的葉子節(jié)點(diǎn) 只 存儲(chǔ)了主鍵的值和索引列的是非主鍵索引，也被稱之為非聚簇索引。一個(gè)表可以有多個(gè)非聚簇索引 。 非聚簇 索引的存儲(chǔ)和數(shù)據(jù)的存儲(chǔ)是分離的，也就是說可能找到了索引但沒找到數(shù)據(jù)，需要根據(jù)索引上的值（主鍵）再次回表查詢，非聚簇索引也叫做輔助索引。

輔組索引在student表中的數(shù)據(jù)結(jié)構(gòu)：

輔組索引查找數(shù)據(jù)過程（不回表）：

輔組索引查找數(shù)據(jù)過程（回表）：

輔助索引 單列索引

---

單列索引，即使用一列作為輔助索引列，但查詢條件使用到輔助索引列時(shí)，會(huì)使用索引。

輔助索引 多列索引（聯(lián)合索引）

---

聯(lián)合索引，即多列索引，在創(chuàng)建索引時(shí)，將多列作為索引列，如：

alter table student add index idx_na(name,age);

輔助索引 多列索引（聯(lián)合索引）

---

前綴索引是針對(duì)于，我們所選擇的索引列值長(zhǎng)度過長(zhǎng)，會(huì)導(dǎo)致索引數(shù)高度增高，會(huì)導(dǎo)致索引應(yīng)用時(shí)，需要讀取更多的索引數(shù)據(jù)頁，MySQL中建議索引樹高度3-4層。所以可以選擇大字段的前面部分字符作為索引生成條件。
類似于書的標(biāo)題不能過長(zhǎng)，如果標(biāo)題太長(zhǎng)，生成目錄的時(shí)候，目錄占的位置就很多，目錄頁就會(huì)增加，前綴索引一般用于模糊查詢的時(shí)候。

如有下表，需要使用sno列作為索引列，通過對(duì)比可知前面6位字符可以確定一個(gè)唯一的值，所以在創(chuàng)建索引時(shí)，可以將前綴長(zhǎng)度設(shè)置為6，減少索引樹的高度。

alter table xxx add index index_name(sno(2));

索引管理

查看索引：

show index from world.student;

刪除索引：

drop index idx_name on world.student;

創(chuàng)建索引：

常用辦法1：創(chuàng)建表時(shí)創(chuàng)建

CREATE TABLE world.student(
`id` INT AUTO_INCREMENT NOT NULL COMMENT 'id',
`name` VARCHAR(10) NOT NULL COMMENT '姓名',
`age` INT  NOT NULL COMMENT '年齡',
PRIMARY KEY(id),  # 聚簇索引
INDEX idx_name(name)  # 輔助索引
)ENGINE=INNODB DEFAULT CHARSET='utf8mb4';

常用辦法2：使用alter語句

alter table world.student add index idx_name(name);

什么時(shí)候創(chuàng)建索引?

1）按照業(yè)務(wù)語句的需求創(chuàng)建合適的索引，并不是將所有列都建立索引
2）并不是將所有的列都建立索引，不是索引越多越好
3）將索引建立在進(jìn)程做where group by order by join on 條件的列

亂建索引的后果？

1）如果冗余索引過多，表數(shù)據(jù)變化的時(shí)候，很有可能導(dǎo)致索引頻繁更新。會(huì)阻塞很多正常的業(yè)務(wù)請(qǐng)求
2）索引過多，會(huì)導(dǎo)致優(yōu)化器選擇出現(xiàn)偏差

索引應(yīng)用規(guī)范：

1、建表時(shí)一定要有主鍵，主鍵最好是數(shù)字列，如果沒有，可以自定義一個(gè)無關(guān)列，然后定義為自增長(zhǎng)的
2、選擇唯一性索引
唯一性索引的值時(shí)唯一的，可以更快速的通過該索引來確定某條記錄
例如學(xué)生表中學(xué)號(hào)時(shí)具有唯一性的字段，為該字段建立唯一索引可以很快的確定某個(gè)學(xué)生的信息，如果使用姓名的化，可能存在同名現(xiàn)象，從而降低查詢速度。
3、為經(jīng)常需要where、order by、group by ,join on等操作的字段，排序會(huì)浪費(fèi)很多多時(shí)間，可以建立索引，優(yōu)化查詢，如果經(jīng)常作為條件的列，重復(fù)值特別多，可以建立聯(lián)合索引
4、盡量使用前綴索引，如果索引字段的值很長(zhǎng)，最好使用值的前綴來索引
5、限制索引的數(shù)目
索引的數(shù)目不是越多越好，可能會(huì)產(chǎn)生的問題
1）每個(gè)索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大
2）修改表時(shí)，對(duì)索引的重構(gòu)和更新很麻煩，越多的索引，會(huì)使表更新變得很浪費(fèi)時(shí)間
3）優(yōu)化器的負(fù)擔(dān)會(huì)很重，有可能會(huì)影響到優(yōu)化器的選擇
percona-toolkit中有個(gè)工具，專門分析索引是否有用
6、刪除不再使用或者很少使用的索引
7、大表加索引，要在業(yè)務(wù)不繁忙期間操作
8、盡量少在更新值的列上建索引

索引補(bǔ)充

補(bǔ)充準(zhǔn)備表

---

CREATE TABLE world.student(
`id` INT AUTO_INCREMENT NOT NULL COMMENT 'id',
`name` VARCHAR(10) NOT NULL COMMENT '姓名',
`age` CHAR(3)  NOT NULL COMMENT '年齡',
`address` VARCHAR(20) NOT NULL COMMENT '地址',
`phone` VARCHAR(11) NOT NULL COMMENT '手機(jī)號(hào)碼',
PRIMARY KEY(id),
INDEX idx_info(age,NAME,address)
)ENGINE=INNODB DEFAULT CHARSET='utf8mb4';

INSERT INTO world.`student`(NAME,age,address,phone) VALUES('張三',24,'北京市','10086'),('李四',20,'上海市','10000'),('王五',21,'重慶市','10010'),('運(yùn)維少年',18,'天津市','13800138000');

補(bǔ)充如何查看SQL是否走索引？

---

辦法1：explain

explain 執(zhí)行的語句
explain select * from student where id=1;

辦法2：desc

desc 執(zhí)行的語句
desc select * from student where name='張三'

補(bǔ)充索引掃描類型

---

1）index 全索引掃描 -- 需要掃描整個(gè)索引樹

index全索引掃描一般出現(xiàn)在查詢列為索引列時(shí)

select id from student;

2）range -- 范圍掃描

當(dāng)查詢條件為>、<、in、like 時(shí)，類型會(huì)為range

3）ref -- 輔助索引等值查詢

輔助索引等值查詢一般出現(xiàn)在使用輔助索引列做等值查詢時(shí)。

4）const 主鍵等值查詢

聚簇索引等值查詢一般出現(xiàn)在使用聚簇索引列做等值查詢時(shí)。

補(bǔ)充什么情況下會(huì)不走索引？

---

1）沒有查詢條件的

2）查詢結(jié)果集時(shí)原表中的大部分?jǐn)?shù)據(jù)，應(yīng)該時(shí)15%-30%，如果超過，優(yōu)化器覺得沒有必要走索引了，可以使用limi分頁

3）查詢條件屬用函數(shù)在索引列上，或者對(duì)索引列進(jìn)行運(yùn)算，運(yùn)算包括（ -/!等）
錯(cuò)誤的例子：select from student id-1=2;
正確的例子：select * from student id=3;

4）隱式轉(zhuǎn)換導(dǎo)致索引失效，這一點(diǎn)應(yīng)當(dāng)引起重視，很多時(shí)候會(huì)犯這個(gè)錯(cuò)。如定義了char，查詢時(shí)使用數(shù)字類型。

5）not in 不走輔助索引。or或in 可以改成union

6）like "%_"百分號(hào)在前面不走

補(bǔ)充多列索引（聯(lián)合索引）

---

使用多列組合一個(gè)索引idx(age,name,address)

有效索引: age age,name age,name,address (只能以age開頭，并且連續(xù)的)
無效索引: name address name,address

索引只能走等于和不能走范圍值:

1、age= and name= and address=  #索引能到address
2、age= and name> and address= # 索引能到name
3、age< and name = and address= # 索引能到a

來源：https://www.icode9.com/content-2-887101.html