mysql-索引及執(zhí)行計劃

一： 索引作用：

提供類似書中目錄的作用，目的是為了優(yōu)化查詢

二：索引 的種類：

B樹索引

Hash索引

R樹

full text

GIS


三： B樹基于不同的查找算法分類介紹


'''

B-tree：

B+tree?在范圍查詢方面提供了更好的性能（> < >= <=）

B*Tree

'''


四： 在功能上的分類

44.1 輔助索引(S)怎么構(gòu)建B樹結(jié)構(gòu)的?

(1). 索引是基于表中,列(索引鍵)的值生成的B樹結(jié)構(gòu)

(2). 首先提取此列所有的值,進行自動排序

(3). 將排好序的值,均勻的分布到索引樹的葉子節(jié)點中(16K)

(4). 然后生成此索引鍵值所對應得后端數(shù)據(jù)頁的指針

(5). 生成枝節(jié)點和根節(jié)點,根據(jù)數(shù)據(jù)量級和索引鍵長度,生成合適的索引樹高度

id? name? age? gender

select? *? from? t1 where id=10;

問題: 基于索引鍵做where查詢,對于id列是順序IO,但是對于其他列的查詢,可能是隨機IO.

alter table t1 add index idx(id)

4.2 聚集索引(C)

4.2.1 前提

(1)表中設置了主鍵,主鍵列就會自動被作為聚集索引.

(2)如果沒有主鍵,會選擇唯一鍵作為聚集索引.

(3)聚集索引必須在建表時才有意義,一般是表的無關(guān)列(ID)

4.2.2 輔助索引(S)怎么構(gòu)建B樹結(jié)構(gòu)的?

(1) 在建表時,設置了主鍵列(ID)

(2) 在將來錄入數(shù)據(jù)時,就會按照ID列的順序存儲到磁盤上.(我們又稱之為聚集索引組織表)

(3) 將排好序的整行數(shù)據(jù),生成葉子節(jié)點.可以理解為,磁盤的數(shù)據(jù)頁就是葉子節(jié)點

4.2.3 聚集索引和輔助索引構(gòu)成區(qū)別

聚集索引只能有一個,非空唯一,一般是主鍵

輔助索引,可以有多個,時配合聚集索引使用的

聚集索引葉子節(jié)點,就是磁盤的數(shù)據(jù)行存儲的數(shù)據(jù)頁

MySQL是根據(jù)聚集索引,組織存儲數(shù)據(jù),數(shù)據(jù)存儲時就是按照聚集索引的順序進行存儲數(shù)據(jù)

輔助索引,只會提取索引鍵值,進行自動排序生成B樹結(jié)構(gòu)

五：輔助索引細分：

1：普通的單列輔助索引

2：聯(lián)合索引： 多個列作為索引條件，生成索引樹，理論上設計是好的，可以減少大量額回表查詢

3：唯一索引： 索引的值都是唯一的。

六：關(guān)于索引樹的高度受什么影響：

1：數(shù)據(jù)量級，解決方法：分表，分庫，分布式

2：索引列值過長：解決方法； 前綴索引

3：數(shù)據(jù)類型： 變長長度字符串，使用了char， 解決方案：變長字符串使用varchar

enum類型的使用enum（’山東‘，’河北‘.....）

七：索引的基本管理

7.1 索引建立前

db01 [world]>desc city;

+-------------+----------+------+-----+---------+----------------+

| Field? ? ? | Type? ? | Null | Key | Default | Extra? ? ? ? ? |

+-------------+----------+------+-----+---------+----------------+

| ID? ? ? ? ? | int(11)? | NO? | PRI | NULL? ? | auto_increment |

| Name? ? ? ? | char(35) | NO? |? ? |? ? ? ? |? ? ? ? ? ? ? ? |

| CountryCode | char(3)? | NO? | MUL |? ? ? ? |? ? ? ? ? ? ? ? |

| District? ? | char(20) | NO? |? ? |? ? ? ? |? ? ? ? ? ? ? ? |

| Population? | int(11)? | NO? |? ? | 0? ? ? |? ? ? ? ? ? ? ? |

+-------------+----------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

Field :列名字

key? :有沒有索引,索引類型

PRI: 主鍵索引

UNI: 唯一索引

MUL: 輔助索引(單列,聯(lián)和,前綴)

7.1 單列普通輔助索引

7.1.1 創(chuàng)建索引

alter table city add index idx_name(name);

create index idx_name1 oncity(name);

show indexfromcity;

alter table city drop index idx_name1; 刪除索引

7.2 覆蓋索引(聯(lián)合索引)

lter table city add index? idx_co_po(countrycode,population);

7.3 前綴索引：

alter table city add index idx_di(district(5));

注意：數(shù)字列不能用作前綴索引。

7.4 唯一索引

db01 [world]>alter table city add unique index idx_uni1(name);

ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'

selectdistrict,count(id)fromcitygroupby district;

需求:找到world下,city表中 name列有重復值的行,最后刪掉重復的行

db01[world]>selectname,count(id)ascidfromcitygroupby name having cid>1order by cid desc;

db01[world]>select*fromcitywherename='suzhou';


通過存儲過程 創(chuàng)建一張100w數(shù)據(jù)量的表（mysql函數(shù)）：

use school;

create table t_100w(id int,num int, k1 char(2),k2 char(4),dt TIMESTAMP);

create procedure rand_data(in num int)

BEGIN

declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

declare str2 char(2);

declare str4 char(4);

declare i int default 0;

while i< num do

set

str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));

set

str4=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),2));

set i=i+1;

insert into t_100w values(i, floor(rand()*num),str2,str4,NOW());

end while;

end;

call rand_data(1000000);? ??

8：執(zhí)行計劃及獲取

8.0 介紹：

(1)獲取到的是優(yōu)化器選擇完成的,他認為代價最小的執(zhí)行計劃.作用:語句執(zhí)行前,先看執(zhí)行計劃信息,可以有效的防止性能較差的語句帶來的性能問題.如果業(yè)務中出現(xiàn)了慢語句，我們也需要借助此命令進行語句的評估，分析優(yōu)化方案。

(2)select獲取數(shù)據(jù)的方法

1.全表掃描(應當盡量避免,因為性能低)

2.索引掃描

3.獲取不到數(shù)據(jù)

8.1 執(zhí)行計劃獲取

獲取優(yōu)化器選擇后的執(zhí)行計劃

desc select * from t_100w WHERE k2='780p';

explain select * from t_100w WHERE k2='780p';

兩種獲取方式都可以

結(jié)果：

主要關(guān)注點：

table: 查詢的表

type: 查詢的類型：全表， 索引

possible_keys： 可能會用到的索引

key: 使用到的索引

key_len: 應用索引的長度

rows: 查詢結(jié)果集的長度

extra:? 額外的信息

8.1.1： type?查詢的類型：全表， 索引

ALL: 全表掃描，不走索引

有哪些情況：

在 where 語句后面查詢 <>(不等于) ， not? in， like '%..'? 在輔助索引中不走索引， 在聚集索引列還是會走索引

1:index: 全索引掃描

????1：查詢需要獲取整個索引樹種的值時：

????????desc select countcode from city;

? ? ? ?2:聯(lián)合索引中：任何一個非最左列作為查詢條件時:

????????idx_a_b_c(a, b,c)? ---> a, ab, abc

????????select * from t1 where b

? ? ? ? ?use world;

????????desc city;

????????alter table city add index idx_c_p(countrycode, population);

????????desc select * FROM city where countrycode="CHN"

2: range: 索引范圍掃描?

輔助索引：> < >= <= like in or

主鍵索引： not in

1: desc select * from city where id<5;

3: ref: 非唯一性索引， 等值查詢

desc select * from city where countrycode='chn'

4: eq_ref: 在多表連接時， 連接條件使用了唯一索引（uk， pk）

desc select b.name, a.name from city as a join country as b on a.countrycode=b.code where a.population <100;

5:system, const? 唯一索引的等值查詢

desc select * from city where id=10;

索引性能遞增

8.2.2?extra:? filesort， 文件排序

desc select * from city where countrycode='chn' order by population;

ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);

desc select * from city where countrycode='chn' order by population;?

結(jié)論:
?1.當我們看到執(zhí)行計劃extra位置出現(xiàn)filesort,說明由文件排序出現(xiàn)
?2.觀察需要排序(ORDER BY,GROUP BY ,DISTINCT )的條件,有沒有索引
3. 根據(jù)子句的執(zhí)行順序,去創(chuàng)建聯(lián)合索引


8.2.3 explain(desc)使用場景（面試題）

題目意思:? 我們公司業(yè)務慢,請你從數(shù)據(jù)庫的角度分析原因

1.mysql出現(xiàn)性能問題,我總結(jié)有兩種情況:

（1）應急性的慢：突然夯住

應急情況:數(shù)據(jù)庫hang(卡了,資源耗盡)

處理過程:

1.show processlist;? 獲取到導致數(shù)據(jù)庫hang的語句

2. explain 分析SQL的執(zhí)行計劃,有沒有走索引,索引的類型情況

3. 建索引,改語句

（2）一段時間慢(持續(xù)性的):

(1)記錄慢日志slowlog,分析slowlog

(2)explain 分析SQL的執(zhí)行計劃,有沒有走索引,索引的類型情況

(3)建索引,改語句

9. 索引應用規(guī)范

9.1 建立索引的原則（DBA運維規(guī)范）

9.1.0 說明

為了使索引的使用效率更高，在創(chuàng)建索引時，必須考慮在哪些字段上創(chuàng)建索引和創(chuàng)建什么類型的索引。那么索引設計原則又是怎樣的?

9.1.1 (必須的) 建表時一定要有主鍵,一般是個無關(guān)列

9.1.2 選擇唯一性索引

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。

例如，學生表中學號是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個學生的信息。

如果使用姓名的話，可能存在同名現(xiàn)象，從而降低查詢速度。

9.1.3(必須的) 為經(jīng)常需要where 、ORDER BY、GROUP BY,join on等操作的字段

為其建立索引，優(yōu)化查詢

注：如果經(jīng)常作為條件的列，重復值特別多，可以建立聯(lián)合索引

9.1.4 盡量使用前綴來索引：

如果索引字段的值很長，最好使用值的前綴來索引。

9.1.5 限制索引的數(shù)目

索引的數(shù)目不是越多越好。

可能會產(chǎn)生的問題:

(1) 每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大。

(2) 修改表時，對索引的重構(gòu)和更新很麻煩。越多的索引，會使更新表變得很浪費時間。

(3) 優(yōu)化器的負擔會很重,有可能會影響到優(yōu)化器的選擇.

percona-toolkit中有個工具,專門分析索引是否有用

9.1.6 刪除不再使用或者很少使用的索引(percona toolkit)

pt-duplicate-key-checker

表中的數(shù)據(jù)被大量更新，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。數(shù)據(jù)庫管理

員應當定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

9.1.7 大表加索引,要在業(yè)務不繁忙期間操作

9.1.8 盡量少在經(jīng)常更新值的列上建索引

9.1.9 建索引原則

(1)必須要有主鍵,如果沒有可以做為主鍵條件的列,創(chuàng)建無關(guān)列
(2)經(jīng)常做為where條件列 order bygroupbyjoinon,distinct 的條件(業(yè)務:產(chǎn)品功能+用戶行為)
(3)最好使用唯一值多的列作為索引,如果索引列重復值較多,可以考慮使用聯(lián)合索引
(4)列值長度較長的索引列,我們建議使用前綴索引.
(5)降低索引條目,一方面不要創(chuàng)建沒用索引,不常使用的索引清理,perconatoolkit(xxxxx)
(6)索引維護要避開業(yè)務繁忙期

9.2 不走索引的情況（開發(fā)規(guī)范）

9.2.1 沒有查詢條件，或者查詢條件沒有建立索引

select * from tab;? ? ? 全表掃描。

select? * from tab where 1=1;

在業(yè)務數(shù)據(jù)庫中，特別是數(shù)據(jù)量比較大的表。

是沒有全表掃描這種需求。

1、對用戶查看是非常痛苦的。

2、對服務器來講毀滅性的。

（1）

select * from tab;

SQL改寫成以下語句：

select? * from? tab? order by? price? limit 10 ;? ? 需要在price列上建立索引

（2）

select? * from? tab where name='zhangsan'? ? ? ? ? name列沒有索引

改：

1、換成有索引的列作為查詢條件

2、將name列建立索引

9.2.2 查詢結(jié)果集是原表中的大部分數(shù)據(jù)，應該是25％以上。

查詢的結(jié)果集，超過了總數(shù)行數(shù)25%，優(yōu)化器覺得就沒有必要走索引了。

假如：tab表 id，name? ? id:1-100w? ，id列有(輔助)索引

select * from tab? where id>500000;

如果業(yè)務允許，可以使用limit控制。

怎么改寫 ？

結(jié)合業(yè)務判斷，有沒有更好的方式。如果沒有更好的改寫方案

盡量不要在mysql存放這個數(shù)據(jù)了。放到redis里面。

9.2.3 索引本身失效，統(tǒng)計數(shù)據(jù)不真實

索引有自我維護的能力。

對于表內(nèi)容變化比較頻繁的情況下，有可能會出現(xiàn)索引失效。

一般是刪除重建

現(xiàn)象:

有一條select語句平常查詢時很快,突然有一天很慢,會是什么原因

select?? --->索引失效,，統(tǒng)計數(shù)據(jù)不真實

DML ?? --->鎖沖突

9.2.4 查詢條件使用函數(shù)在索引列上，或者對索引列進行運算，運算包括(+，-，*，/，! 等)

例子：

錯誤的例子：select * from test where id-1=9;

正確的例子：select * from test where id=10;

算術(shù)運算

函數(shù)運算

子查詢

9.2.5 隱式轉(zhuǎn)換導致索引失效.這一點應當引起重視.也是開發(fā)中經(jīng)常會犯的錯誤.

這樣會導致索引失效. 錯誤的例子：

mysql> alter table tab add index inx_tel(telnum);

Query OK, 0 rows affected (0.03 sec)

Records: 0? Duplicates: 0? Warnings: 0

mysql>

mysql> desc tab;

+--------+-------------+------+-----+---------+-------+

| Field? | Type? ? ? ? | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| id? ? | int(11)? ? | YES? |? ? | NULL? ? |? ? ? |

| name? | varchar(20) | YES? |? ? | NULL? ? |? ? ? |

| telnum | varchar(20) | YES? | MUL | NULL? ? |? ? ? |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

mysql> select * from tab where telnum='1333333';

+------+------+---------+

| id? | name | telnum? |

+------+------+---------+

|? ? 1 | a? ? | 1333333 |

+------+------+---------+

1 row in set (0.00 sec)

mysql> select * from tab where telnum=1333333;

+------+------+---------+

| id? | name | telnum? |

+------+------+---------+

|? ? 1 | a? ? | 1333333 |

+------+------+---------+

1 row in set (0.00 sec)

mysql> explain? select * from tab where telnum='1333333';

+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

| id | select_type | table | type | possible_keys | key? ? | key_len | ref? | rows | Extra? ? ? ? ? ? ? ? |

+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

|? 1 | SIMPLE? ? ? | tab? | ref? | inx_tel? ? ? | inx_tel | 63? ? ? | const |? ? 1 | Using index condition |

+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

1 row in set (0.00 sec)

mysql> explain? select * from tab where telnum=1333333;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key? | key_len | ref? | rows | Extra? ? ? |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|? 1 | SIMPLE? ? ? | tab? | ALL? | inx_tel? ? ? | NULL | NULL? ? | NULL |? ? 2 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> explain? select * from tab where telnum=1555555;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key? | key_len | ref? | rows | Extra? ? ? |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|? 1 | SIMPLE? ? ? | tab? | ALL? | inx_tel? ? ? | NULL | NULL? ? | NULL |? ? 2 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> explain? select * from tab where telnum='1555555';

+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

| id | select_type | table | type | possible_keys | key? ? | key_len | ref? | rows | Extra? ? ? ? ? ? ? ? |

+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

|? 1 | SIMPLE? ? ? | tab? | ref? | inx_tel? ? ? | inx_tel | 63? ? ? | const |? ? 1 | Using index condition |

+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

1 row in set (0.00 sec)

mysql>

9.2.6 <> ，not in 不走索引（輔助索引）

EXPLAIN? SELECT * FROM teltab WHERE telnum? <> '110';

EXPLAIN? SELECT * FROM teltab WHERE telnum? NOT IN ('110','119');

mysql> select * from tab where telnum <> '1555555';

+------+------+---------+

| id? | name | telnum? |

+------+------+---------+

|? ? 1 | a? ? | 1333333 |

+------+------+---------+

1 row in set (0.00 sec)

mysql> explain select * from tab where telnum <> '1555555';

單獨的>,<,in 有可能走，也有可能不走，和結(jié)果集有關(guān)，盡量結(jié)合業(yè)務添加limit

or或in? 盡量改成union

EXPLAIN? SELECT * FROM teltab WHERE telnum? IN ('110','119');

改寫成：

EXPLAIN SELECT * FROM teltab WHERE telnum='110'

UNION ALL

SELECT * FROM teltab WHERE telnum='119'

9.2.7 like "%_" 百分號在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'? 走range索引掃描

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'? 不走索引

%linux%類的搜索需求，可以使用elasticsearch+mongodb 專門做搜索服務的數(shù)據(jù)庫產(chǎn)品

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

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

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