索引的作用

類似于一本書的目錄，起到優(yōu)化查詢的功能

索引類型（筆試）

BTREE索引*****

RTREE索引

HASH索引

全文索引

image

BTREE的細(xì)分離（算法）

B-TREE

B +樹

B *樹

Btree索引的功能分類

聚集索引（集群索引）*****

輔助索引（二級索引）

B樹是如何構(gòu)建的

輔助索引
alter table t1 add index idx_name(name);
1.將name列的所有值取出來，進(jìn)行自動排序
2.將排完序的值均勻的落到16KB葉子節(jié)點(diǎn)數(shù)據(jù)頁中，并將索引鍵值所對應(yīng)的數(shù)據(jù)行的幾句索引列值
3.向上生成枝節(jié)點(diǎn)和根節(jié)點(diǎn)

聚集索引（IOT）
1.默認(rèn)是按照主鍵生成聚集索引，沒有主鍵，存儲引擎會使用唯一鍵。
如果都沒有，會自動生成隱藏的聚集索引
2.數(shù)據(jù)在存儲時，就會按照聚集索引打的順序存儲到磁盤的數(shù)據(jù)頁
3.由于本身書籍就是有序的，所以聚集索引構(gòu)建時，不需要進(jìn)行排序
4.聚集索引直接將磁盤的數(shù)據(jù)頁，作為葉子節(jié)點(diǎn)
5.枝節(jié)點(diǎn)和根節(jié)點(diǎn)只會調(diào)取下層節(jié)點(diǎn)主鍵的最小值

image

輔助索引和聚集索引區(qū)別

(1) 輔助索引：
葉子節(jié)點(diǎn)只保存主鍵值+索引鍵值的有序存儲
對索引鍵值會知道排序
需要手工創(chuàng)建
輔助索引可以有多個
任何列都可以創(chuàng)建
(2)
數(shù)據(jù)存儲時，就是按照聚集索引順序進(jìn)行有序存儲
葉子節(jié)點(diǎn)保存的是整個有序的數(shù)據(jù)行
葉子節(jié)點(diǎn)不需要單獨(dú)生成

輔助索引細(xì)分

單列輔助索引

select * from t1 where name=''

聯(lián)合索引

select * from t1 where a and b and c

唯一索引

索引樹的高度(越低越好)

1. 表的數(shù)據(jù)量級大

分區(qū)表

分庫分表(分布式架構(gòu))

2. 列的長度

盡可能選擇列值短的創(chuàng)建索引

采用前綴索引

3. 數(shù)據(jù)類型選擇

varchar 和 char

enum

索引管理

壓力測試準(zhǔn)備

create database test charset utf8mb4;
use test;
create table t100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
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),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;

插入100w條數(shù)據(jù)：

call rand_data(1000000);

commit;

MySQL [test]>select count(*) from t100w;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.47 sec)

索引命令操作

查詢索引

use school
show tables;
desc student;

Key : PRI（主鍵），UNI（唯一索引），MUL（輔助索引）

show index from student\G

image

創(chuàng)建單列索引

alter table student add index idx_name(sname);

image

創(chuàng)建聯(lián)合索引

alter table student add index idx_sname_sage_ssex(sname,sage,ssex);

idx_a_b_c -----> idx_a   idx_a_b   idx_a_b_c

創(chuàng)建前綴索引

alter table student add index idx(sname(5));

alter table student add telnum char(11) not null;

image

創(chuàng)建唯一索引

mysql[school]>alter table student add telnum char(11) not null ;
mysql[school]>alter table student add unique index idx_tel(telnum);

# 判斷某個列有或沒有重復(fù)值
MySQL [world]>select name,count(id) from city group by name having count(id)>1 order by count(id) desc ;
+---------------+-----------+
| name          | count(id) |
+---------------+-----------+
| San José      |         4 |
| Córdoba       |         3 |
| Springfield   |         3 |
...
.....
| Brest         |         2 |
| Manchester    |         2 |
| Santa Maria   |         2 |
| Colombo       |         2 |
| San Carlos    |         2 |

| Jinzhou       |         2 |
+---------------+-----------+
67 rows in set (0.00 sec)

MySQL [world]>select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

MySQL [world]>select count(distinct name) from city;
+----------------------+
| count(distinct name) |
+----------------------+
|                 3998 |
+----------------------+
1 row in set (0.00 sec)

刪除索引

mysql[school]>alter table student drop index idx;
mysql[school]>alter table student drop index idx_name;
mysql[school]>alter table student drop index idx_sname_sage_ssex;

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='VWtu'" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose

優(yōu)化前

image

use test;
show tables;
desc t100w;
alter table t100w add index idx_k2(k2);  #優(yōu)化

image

優(yōu)化后

image

explain (desc) ☆☆☆☆☆

explain select * from test.t100w where k2='VWtu';
or
desc select * from test.t100w where k2='VWtu';

作用 : 抓取優(yōu)化器優(yōu)化過的執(zhí)行計劃

執(zhí)行計劃的分析

MySQL [test]>explain select * from test.t100w where k2='VWtu';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ref  | idx_k2        | idx_k2 | 17      | const | 1120 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+

重點(diǎn)！??！

table：以上SQL語句設(shè)計到的表☆☆☆
type：查詢的類型(全表掃描(ALL)，索引掃描，查不到數(shù)據(jù)(NULL))☆☆☆☆☆
possible_keys：可能會用到的索引☆☆☆
key：使用到的索引☆☆☆☆
key_len：索引的覆蓋長度☆☆☆☆☆
Extra：額外的信息☆☆☆☆

image

type 詳細(xì)說明☆☆☆☆☆

ALL：全表掃描,不走索引

(1) 查詢條件，沒建索引
>use test;
>MySQL [test]>explain  select * from test.t100w where k2='VWtu';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ref  | idx_k2        | idx_k2 | 17      | const | 1120 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL [test]>desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL              |                             |
| num   | int(11)   | YES  |     | NULL              |                             |
| k1    | char(2)   | YES  |     | NULL              |                             |
| k2    | char(4)   | YES  | MUL | NULL              |                             |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

(2) 有索引不走
desc select * from t100w where k2 != 'asdf';
desc select * from t100w where k2 like '%aa%';
desc select * from t100w where k2 not in ('asda','asas');
desc select * from t100w;

注意: !=和not in 如果是主鍵列,是走range
mysql[test]>desc select * from world.city where id != 10;
mysql[test]>desc select * from world.city where id not in (10,20);

index 全索引掃描

MySQL [test]>desc select k2 from t100w;

image

=====從range開始，我們才認(rèn)為索引是有價值的======

range 索引范圍查詢

所有索引：
>, <, >=, <=, like, between and
mysql[world]>desc select * from city where id<10;
mysql[world]>desc select * from city where countrycode like 'CH%

in () or 
oldguo[world]>desc select * from city where countrycode in ('CHN','USA');

聚集索引：
!= not in
mysql[test]>desc select * from world.city where id != 10;
mysql[test]>desc select * from world.city where id not in (10,20);
========================================
說明：
B+tree 索引能額外優(yōu)化到:> ,<, >=, <= ,like , between and
in 和 or 享受不到b+tree額外的優(yōu)化效果的,所以我一般情況會將in , or 進(jìn)行改性
mysql[world]>desc select * from city where countrycode = 'CHN' union all  select * from city where countrycode = 'USA';

ref 輔助索引等值查詢

MySQL [world]>desc select * from city where countrycode='CHN';

image

eq_ref 多表連接查詢中, 非驅(qū)動表的on的調(diào)劑列是主鍵唯一鍵

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

const(system) 主鍵或唯一鍵的等值

desc select * from city where id=100000000000;

image

possible_keys：可能會用到的索引☆ ☆ ☆

NULL：沒有和查詢條件匹配的所有條目
有值：有和查詢條件匹配的索引條目，但是沒走，大部分原因是語句查詢方式不符合索引應(yīng)用條件

10.4 key：使用到的索引☆☆☆☆

最終使用的索引，可以幫助我們判斷是否走了合適的索引

10.5 key_len：索引的覆蓋長度☆☆☆☆☆

在聯(lián)合索引應(yīng)用的判斷時，會經(jīng)?？?/p>

單列索引:

字符集	字節(jié)個數(shù)	not null	沒有指定not null
utf8	3個字節(jié)
int	4個字節(jié)	4	4+1
tinyint	1個字節(jié)	1	2
utf8mb4	一個字符最大是4個字節(jié)
char(2)		2*4	2*4+1
varchar(2)		2*4+2	2*4+2+1

說明：

有非空約束時，key_length就是最大字節(jié)長度

在沒有非空約束時：字符最大長度+1

varchar類型，需要額外在最大字符長度+2(存儲字符長度的最長度占位)

create table t1 (
id int ,
num int not null, 
k1 char(2),
k2 char(2) not null,
k3 varchar(2),
k4 varchar(2) not null)charset utf8mb4;

聯(lián)合搜索優(yōu)化


MySQL [world]>desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  |     | NULL    |       |
| num   | int(11)    | NO   |     | NULL    |       |
| k1    | char(2)    | YES  |     | NULL    |       |
| k2    | char(2)    | NO   |     | NULL    |       |
| k3    | varchar(2) | YES  |     | NULL    |       |
| k4    | varchar(2) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

====================================
alter table t1 add index idx(id,num,k1,k2,k3,k4);
select 5+4+9+8+11+10:  \\ 47

(1)最完美的查詢情況

idx(id,num,k1,k2,k3,k4)
desc select * from t1 where id=1 and num=1 and k1='a' and k2'a' and k3='a' and k4='a';
desc select * from t1 where num=1 and id=1 and k1='a' and k2'a' and k3='a' and k4='a';

結(jié)論：
當(dāng)查詢條件中，包含了索引列中所有的列條件，并且都是等值的查詢，那么無關(guān)排列順序，都可以走全聯(lián)合索引優(yōu)化；
原因是優(yōu)化器會自動調(diào)整順序，達(dá)到最佳的優(yōu)化效果
所以，我們重點(diǎn)需要關(guān)注的是聯(lián)合索引建立的順序,從左到右，唯一值越多的列放在最左邊

(2) 查詢條件中，哪些因素會key_len長度

#安裝索引的建立順序，在查詢條件中，少了任意一個中間列，后續(xù)列都無法走索引
desc select * from t1 where id=1 and k1='a' and k2'a' and k3='a' and k4='a';

#在條件查詢中間,出現(xiàn)不等值查詢時,從不等值列開始,所有列都無法使用聯(lián)合索引 (暫存)
oldguo[world]>desc select * from t1 where  id=1 and num>10 and k1='a' and k2='a' and k3='a' and k4='a';

優(yōu)化方法:

將不等值列放在最后.
oldguo[world]>alter table t1 add index idx1(id,k1,k2,k3,k4,num);
oldguo[world]>alter table t1 drop index idx;

(3) 如果有多子句的條件查詢(必須是聯(lián)合索引)
按照子句的執(zhí)行順序,建立聯(lián)合索引.

Extra：額外的信息 ☆☆☆☆

Using filesort :  原因是 在 group  by ,order by,distinct等.
一般優(yōu)化的方法是,和where條件的列建立聯(lián)合索引

小結(jié)

索引的類型(算法)

BTree
HASH
RTREE

BTREE

B-tree
B+TREE
B*TREE

B+Tree有什么優(yōu)勢

在葉子節(jié)點(diǎn)增加了相鄰葉子節(jié)點(diǎn)雙向指針
為了優(yōu)化范圍查詢

輔助索引和聚集索引區(qū)別

葉子節(jié)點(diǎn) ☆☆☆☆☆

輔助索引是手工創(chuàng)建的，

聚集索引是自動跟主鍵生成

聚集索引只有一個

輔助索引可以有多個

輔助索引細(xì)分

單列
聯(lián)合
唯一
前綴

索引樹高度

數(shù)據(jù)量
數(shù)據(jù)類型
數(shù)據(jù)列的長度
聚集索引列的長度

索引基本管理

show index from t1;
desc t1;
alter table t1 add idnex 索引名(列名)
alter table t1 drop index 索引名

explain

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

分析執(zhí)行計劃

(1) 查詢條件沒有建索引

use test
desc select * from t100w where id=10;

(2) 有索引不走

desc select * from t100w  where 1=1;
desc select * from t100w where k2 !='aaaa';
desc select * from t100w where k2 like '%aa%';
desc select * from t100w where k2 not in ('aaaa','bbbb');

index:全索引掃描 
    desc select k2 from t100w;

Range: 索引范圍掃描
  >, <, >=, <=, like, between and
  or in 
  desc select * from world.city where countrycode in ('CHN','USA');
改寫:
  desc select * from world.city where countrycode='CHN' union all
  select * from world.city where countrycode='USA';     

ref : 輔助索引等值查詢 
       oldguo[test]>desc select * from world.city where countrycode='CHN';

eq_ref : 多表連接查詢,非驅(qū)動表on的條件列是主鍵或者唯一鍵 
       desc select city.name,country.name from city left join country on city.countrycode=country.code where city.population<100;   

const(system) : 主鍵或者唯一鍵的等值查詢
    oldguo[world]>desc select * from city where id=10; 

NULL:略

key_len:  utf8mb4 
                   not null       沒設(shè)置
    int            4              +1
    varchar(10)    4*10+2         +1
    char(10)       4*10           +1

====================================

聯(lián)合索引準(zhǔn)備

create table t1(a int not null ,b char(10) not null ,c varchar(10) not null )charset utf8mb4;
oldguo[test]>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   |     | NULL    |       |
| b     | char(10)    | NO   |     | NULL    |       |
| c     | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

idx(a,b,c) =  4 + 40 + 42 = 86

(1) 最理想的 
desc select * from t1 where a=1 and b='a' and c='a';
desc select * from t1 where b='1' and a=1 and c='a';
desc select * from t1 where c='1' and a=1 and b='a';
desc select * from t1 where c='1' and b='a' and a=1;
desc select * from t1 where a=1 and c='a' and b='a';
desc select * from t1 where b='1' and c='a' and a=1;

唯一值多的放在左邊

(2) 部分索引
desc select * from t1 where a=1 and c='a';
oldguo[test]>desc select * from t1 where a=1 and b like 'a%' and c='a';

(3) where  + order by
Extra:filesort  ===> 排序不走索引,走的額外排序
oldguo[test]>desc select * from world.city where countrycode='CHN' order by population;

擴(kuò)展:
oldguo[test]>desc format=json select * from t1 where a=1 and c='a' and b='a';

建立索引的原則(DBA規(guī)范)

image

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

選擇唯一性索引

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。
例如，學(xué)生表中學(xué)號是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個學(xué)生的信息。
如果使用姓名的話，可能存在同名現(xiàn)象，從而降低查詢速度。

優(yōu)化方案:

(1) 如果非得使用重復(fù)值較多的列作為查詢條件(例如:男女),可以將表邏輯拆分
(2) 可以將此列和其他的查詢類,做聯(lián)和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

為經(jīng)常需要where 、ORDER BY、GROUP BY,join on等操作的字段，排序操作會浪費(fèi)很多時間

如果為其建立索引，優(yōu)化查詢
注：如果經(jīng)常作為條件的列，重復(fù)值特別多，可以建立聯(lián)合索引。

盡量使用前綴來索引

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

限制索引的數(shù)目

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

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

(1) 每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大。
(2) 修改表時，對索引的重構(gòu)和更新很麻煩。越多的索引，會使更新表變得很浪費(fèi)時間。
(3) 優(yōu)化器的負(fù)擔(dān)會很重,有可能會影響到優(yōu)化器的選擇.
percona-toolkit中有個工具,專門分析索引是否有用

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

pt-duplicate-key-checker
表中的數(shù)據(jù)被大量更新，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。數(shù)據(jù)庫管理
員應(yīng)當(dāng)定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

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

https://www.cnblogs.com/TeyGao/p/7160421.html

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

建索引原則

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

面試題：有索引，為什么查詢效率還是很低？

1.有索引不走
2.聯(lián)合索引沒有完全覆蓋
3.索引失效或同級信息不真實(shí)

4.索引重復(fù)值太多

我一般遇到這樣的問題，排查和解決的方法有這幾種方法

1.先看看這個語句的執(zhí)行計劃
table 
type 
key_len   判斷聯(lián)合索引覆蓋長度
extra     額外排序
rows  重復(fù)值 ----->
2.沒索引建索引，改語句

建索引規(guī)范:

主鍵

where group by order by distinct join on
聯(lián)合最左側(cè),唯一值
盡量使用前綴索引
索引條目
頻繁更新的列,不適合做索引列,
避開業(yè)務(wù)繁忙期,pt-tools

不走索引:

本來全表掃描

25%

計算或函數(shù)

隱式轉(zhuǎn)換

like %xx%

!= ,not in

索引失效,統(tǒng)計信息不真實(shí)

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

索引及執(zhí)行計劃管理

索引的作用

索引類型（筆試）

BTREE的細(xì)分離（算法）

Btree索引的功能分類

B樹是如何構(gòu)建的

輔助索引和聚集索引區(qū)別

輔助索引細(xì)分

單列輔助索引

聯(lián)合索引

唯一索引

索引樹的高度(越低越好)

索引管理

壓力測試準(zhǔn)備

索引命令操作

explain (desc) ☆☆☆☆☆

執(zhí)行計劃的分析

重點(diǎn)！??！

type 詳細(xì)說明☆☆☆☆☆

ALL：全表掃描,不走索引

index 全索引掃描

range 索引范圍查詢

ref 輔助索引等值查詢

eq_ref 多表連接查詢中, 非驅(qū)動表的on的調(diào)劑列是主鍵唯一鍵

const(system) 主鍵或唯一鍵的等值

possible_keys：可能會用到的索引☆ ☆ ☆

10.4 key：使用到的索引☆☆☆☆

10.5 key_len：索引的覆蓋長度☆☆☆☆☆

Extra：額外的信息 ☆☆☆☆

小結(jié)

索引的類型(算法)

BTREE

B+Tree有什么優(yōu)勢

輔助索引和聚集索引區(qū)別

輔助索引細(xì)分

索引樹高度

索引基本管理

explain

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

分析執(zhí)行計劃

聯(lián)合索引準(zhǔn)備

建立索引的原則(DBA規(guī)范)

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

選擇唯一性索引

優(yōu)化方案:

為經(jīng)常需要where 、ORDER BY、GROUP BY,join on等操作的字段，排序操作會浪費(fèi)很多時間

盡量使用前綴來索引

限制索引的數(shù)目

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

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

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

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

建索引原則

面試題：有索引，為什么查詢效率還是很低？

我一般遇到這樣的問題，排查和解決的方法有這幾種方法

建索引規(guī)范:

不走索引:

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

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

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

重點(diǎn)！??！

為經(jīng)常需要where 、ORDER BY、GROUP BY,join on等操作的字段，排序操作會浪費(fèi)很多時間

面試題：有索引，為什么查詢效率還是很低？

我一般遇到這樣的問題，排查和解決的方法有這幾種方法