一、索引介紹

1.為什么要做索引

讓獲取的數(shù)據(jù)更具有目的性，從而提高數(shù)據(jù)庫檢索數(shù)據(jù)的性能

2.索引類型介紹

1)BTREE:樹索引 B+樹索引 B*樹索引

2)HASH：HASH索引

3)FULLTEXT：全文索引

4)RTREE：R樹索引

innodb存儲引擎使用BETREE

tree索引一個節(jié)點(diǎn)是一次I/O

mysql5.6/5.7用的tree和b+tree

2.1：樹索引

樹索引與B+樹索引對比

? 1.與tree算法在查單點(diǎn)數(shù)據(jù)上I/O相同

? 2..優(yōu)化了范圍查詢

? 3.在葉子節(jié)點(diǎn)添加了相鄰節(jié)點(diǎn)的指針

2.2：B+樹索引

2.3：B*樹索引

3.索引管理

索引建立在表的列上(字段)的。

在where后面的列建立索引才會加快查詢速度。

pages

3.1：索引分類

主鍵索引

? 創(chuàng)建語法：alter table 表名 add primary key 定義索引名(對添加索引的列名);

普通索引

? 創(chuàng)建語法：alter table 表名 add index 定義索引名(對添加索引的列名);

唯一索引

? 創(chuàng)建語法：alter table 表名 add unique key 定義索引名(對添加索引的列名);

3.2：添加索引

#添加普通索引

mysql> alter table student4 add index idx_name(name);

#查看表結(jié)構(gòu)看索引

mysql> desc student4;

#查看建表語句，看索引

mysql> show create table student4;

#查看索引

mysql> show index from student4;

key_name(索引名) column_name(索引的列名) index_type(使用的算法)

#刪除索引

mysql> alter table student4 drop index idx_name;

#添加主鍵索引

mysql> alter table abc add primary key pri_id(id);

#添加唯一索引

mysql> alter table student add unique key uni_age(age);

注意：添加唯一索引前需要先去重看數(shù)據(jù)是否唯一

#計算某列有多少行數(shù)據(jù)

mysql> select count(name) from student2;

#去重

mysql> select distinct(name) from student2;

#去重后計算某列有多少行數(shù)據(jù)

mysql> select count(distinct(name)) from student2;

3.3：前綴索引和聯(lián)合索引

注意：添加索引會占據(jù)磁盤空間，需避免對大列建立索引，默認(rèn)情況是按照BTREE算法排序，若必須要建立索引，可以選擇給其加前綴，以減少索引所需的時間

應(yīng)用場景：如需對列中較長的數(shù)據(jù)加索引，如對博客系統(tǒng)中的博文加索引

前綴索引

根據(jù)字段的前N個字符建立索引

alter table 表名 add index 定義索引名(對添加索引的列名(前綴字符數(shù)));

#添加前綴索引(以前四個字符為建立索引)

mysql> alter table student3 add index idx_name(name(4));

聯(lián)合索引

多個字段建立一個索引(查詢時有順序要求)

例：

where a.女生 and b.身高 and c.體重 and d.身材好

index(a,b,c)

特點(diǎn)：前綴生效特性

a,ab,ac,abc,abcd 可以走索引或部分走索引

b bc bcd cd c d ba … 不走索引

#添加聯(lián)合索引：對多個字段創(chuàng)建索引(如對age,sex,date創(chuàng)建索引 索引順序?yàn)閯?chuàng)建時的順序 )

mysql> alter table student3 add index idx_all(age,sex,date);

總結(jié)：

? 1)需嚴(yán)格按照創(chuàng)建索引時的SQL語句中的索引順序查詢才可生效索引

? 2)創(chuàng)建聯(lián)合索引前需先調(diào)查用戶查詢數(shù)據(jù)的需求量再進(jìn)行添加聯(lián)合索引

二、explain詳解

explain命令使用方法

語法：explain 查詢語句;

mysql> explain select name,countrycode from city where id=1;

type(索引掃描類型)

explain命令應(yīng)用

查詢數(shù)據(jù)的方式

1.全表掃描：

? 1)在explain語句結(jié)果中type為ALL

? 2)什么時候出現(xiàn)全表掃描

? a)業(yè)務(wù)確實(shí)要獲取所有數(shù)據(jù)

? b)不走索引導(dǎo)致的全表掃描

? c)沒有索引

? d)索引創(chuàng)建有問題

? e)語句有問題

生產(chǎn)中,mysql在使用全表掃描時的性能是極其差的，所以MySQL盡量避免出現(xiàn)全表掃描

mysql> explain select * from city where district='heilongjiang';

2.索引掃描

常見的索引掃描類型:

? 1)index

? 2)range

? 3)ref

? 4)eq_ref

? 5)const

? 6)system

? 7)null

從上到下，性能從最差到最好，性能至少要達(dá)到range級別

index：Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹。

mysql> alter table city add index idx_dis(district);

mysql> explain select District from city;

range：索引范圍掃描，對索引的掃描開始于某一點(diǎn)，返回匹配值域的行。顯而易見的索引范圍掃描是帶有between或者where子句里帶有查詢。

mysql> alter table city add index idx_pop(population);

mysql> explain select * from city where population>30000000;

mysql> alter table city drop index idx_pop;

mysql> explain select * from city where population>30000000;

ref：使用唯一索引掃描或者唯一索引的前綴掃描，返回匹配某個單獨(dú)值的記錄行。

mysql> alter table student3 add index idx_name(name(4));

mysql> explain select * from student3 where name='bgx';

eq_ref：類似ref，區(qū)別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件A

const、system：當(dāng)MySQL對查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個常量時，使用這些類型訪問。

如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量(主鍵或唯一建)

NULL：MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨(dú)索引查找完成。

Extra(擴(kuò)展)

Using temporary

Using filesort 使用了默認(rèn)的文件排序(如果使用了索引，會避免這類排序)

Using join buffer

如果出現(xiàn)Using filesort請檢查order by ,group by ,distinct,join 條件列上沒有索引

mysql> explain select * from city where countrycode='CHN' order by population;

當(dāng)order by語句中出現(xiàn)Using filesort，那就盡量讓排序值在where條件中出現(xiàn)

mysql> alter table city add index idx_pop(Population);

mysql> explain select * from city where population>2870300 and countrycode='CHN' order by population;

mysql> explain select * from city where population=2870300 and countrycode='CHN' order by population;

key_len(索引長度): 越小越好

前綴索引去控制

rows(行數(shù)，與結(jié)果集相關(guān)): 越小越好

案例：優(yōu)化rows

一：

mysql> alter table city add index idx_name(name);

二：案例場景：比如說在電商平臺中，商品索引展示，商品過多影響查詢速率，可以使用索引加limit

查詢結(jié)果集是原表中的大部分?jǐn)?shù)據(jù)，應(yīng)該是25％以上才能走索引

解決：

總結(jié)

以上是生活随笔為你收集整理的mysql索引执行计划_第六章· MySQL索引管理及执行计划的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。