分类导航

程序笔记发布时间：2022-07-19 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了[MySQL]-06MySQL索引及执行计划，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

第1章索引介绍

1.介绍

索引相当于一本书的目录，可以优化查询。

2.索引查找算法

1 --> 100 盒子 
谁最快猜到数字，礼品归谁。
我会给大家提示。
1. 遍历
2. 二分法 ---> 二叉树 ---> 红黑树 ---> Balance tree(平衡多叉树，简称为BTREE)

3.bTREE查找算法演变

1.b-TREE ： 		     普通 BTREE
2.b+TREE ： 		     叶子节点双向指针
3.b++TREE（B*TREE）：枝节点的双向指针

B-TREE示意图：

[MySQL]-06MySQL索引及执行计划

B+TREE示意图：

[MySQL]-06MySQL索引及执行计划

B++TREE示意图:

[MySQL]-06MySQL索引及执行计划

第2章聚簇(区)索引

1.前提

1.如果表中设置了主键（例如ID列），自动根据ID列生成索引树。
2.如果没有设置主键，自动选择第一个唯一键的列作为聚簇索引
3.自动生成隐藏的聚簇索引。

2.建议

在建表时，显示的创建主键，最好是数字自增列

3.功能

1.录入数据时，按照聚簇索引组织存储数据，在磁盘上有序存储数据行。
2.加速查询。基于ID作为条件的判断查询。

4.构建过程

1.叶子节点: 存储数据行时就是有序的，直接将数据行的page作为叶子节点（相邻的叶子结点，有双向指针）
2.枝节点: 提取叶子节点ID的范围+指针，构建枝节点（相邻枝节点，有双向指针）
3.根节点: 提取枝节点的ID的范围+指针，构建根节点

第3章辅助索引

1.前提

需要人为创建辅助索引，将经常作为查询条件的列创建辅助索引，起到加速查询的效果。

2.功能

按照辅助索引列，作为查询条件时。1.查找辅助索引树，得到ID值2.拿着ID值回表（聚簇索引）查询

3.构建过程

1.叶子节点：提取主键（ID）+辅助索引列，按照辅助索引列进行从小到大排序后，生成叶子节点。（相邻的叶子结点，有双向指针）2.枝节点  ：提取叶子节点辅助索引列的范围+指针，构建枝节点（相邻枝节点，有双向指针）3.根节点  ：提取枝节点的辅助索引列的范围+指针，构建根节点

第4章索引考虑事项

1.回表是什么？回表会带来什么问题？怎么减少回表？

a. 按照辅助索引列，作为查询条件时，先查找辅助索引树，再到聚簇索引树查找数据行的过程。b. IO量多、IO次数多、随机IO会增多减少回表：1. 辅助索引能够完全覆盖查询结果，可以使用联合索引。2. 尽量让查询条件精细化，尽量使用唯一值多的列作为查询条件3. 优化器：MRR（Multi-Range-Read）， 锦上添花的功能。mysql> SELEct @@optimizer_switch;mysql> set global optimizer_switch='mrr=on';功能： 	1. 辅助索引查找后得到ID值，进行自动排序2. 一次性回表，很有可能受到B+TREE中的双向指针的优化查找。

2.索引树高度的影响因素？如何解决？

a. 高度越低越好b. 数据行越多，高度越高。   1. 分区表。一个实例里管理。   2. 按照数据特点，进行归档表。   3. 分布式架构。针对海量数据、高并发业务主流方案。   4. 在设计方面，满足三大范式。   c. 主键规划：长度过长。   1. 主键，尽量使用自增数字列。   d. 列值长度越长，数据量大的话，会影响到高度。   1. 使用前缀索引   100字符  只取前10个字符，构建索引树。e. 数据类型的选择。   选择合适的、简短的数据类性。   例如：    1. 存储人的年龄，使用 Tinyint 和 char(3)哪个好一些   2. 存储人名，char(20)和varchar(20)的选择哪一个好。      a. 站在数据插入性能角度思考，应该选：char    	    b. 从节省空间角度思考,应该选：varchar	    c. 从索引树高度的角度思考，应该选：varchar  建议使用varchar类型存储变长列值。

第5章索引应用

1.压测

source /root/t100w.sqlmysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-scheR_73_11845@a='test' --query="SELEct * FROM test.t100w where k2='780P'" ENGIne=innodb --number-of-queries=2000 -uroot -p123 -verbose -S /tmp/mysql_3306.sock--concurrency=100  ：  模拟同时100会话连接--create-scheR_73_11845@a='test' ： 操作的库是谁--query="SELEct * FROM test.t100w where k2='780P'"  ：做了什么操作--number-of-queries=2000 ： 一共做了多少次查询Running for ENGIne rboseAverage number of seconds to run all queries: 648.657 secondsMinimum number of seconds to run all queries: 648.657 secondsmaximum number of seconds to run all queries: 648.657 secondsnumber of clients running queries: 100Average number of queries per client: 20

2.查询表的索引

查看索引：

desc t100w;  show index from t100w;

索引类型：

-----Key -----PK     --> 主键（聚簇索引）     MUL    --> 辅助索引   UK     --> 唯一索引

3.创建索引

3.1 单列辅助索引

查询语句:

SELEct * FROM test.t100w where k2='780P'

优化方法：

alter table 表名 add index 索引名(列名);	alter table t100w add index idx_k2(k2);

3.2 创建联合索引

@H_778_5@mysql> alter table t100w add index idx_k1_num(k1,num);

3.3 前缀索引创建

SELEct count(disTinct(left(name,5)))  from city ;SELEct count(disTinct Name)  from city ;创建前缀索引mysql> alter table city add index idx_n(name(5));

4.删除索引

alter table city drop index idx_n;

第6章执行计划获取和分析

1.命令介绍

explain desc

2.使用方法

@H_778_5@mysql> desc SELEct * from city where countrycode='CHN';mysql> explain  SELEct * from city where countrycode='CHN';+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+| id | SELEct_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

3.执行计划信息介绍

table         ：此次查询访问的表type          ：索引查询的类型（ALL、index、range、ref、eq_ref、const(system)、NULL）possible_keys ：可能会应用的索引key           : 最终选择的索引key_len       ：索引覆盖长度，主要是用来判断联合索引应用长度。rows          ：需要扫描的行数Extra         ：额外信息

4.type信息详解

4.1 ALL 没有使用到索引

a. 查询条件没建立索引R_73_11845@ysql> desc SELEct * from city where diStrict='shandong';b. 有索引不走mysql> desc SELEct * from city where countrycode != 'CHN';mysql> desc SELEct * from city where countrycode not in ('CHN','USA');mysql> desc SELEct * from city where countrycode like '%CH%';

4.2 index 全索引扫描

@H_778_5@mysql> desc SELEct countrycode from city;

4.3 range 索引范围扫描

会受到： B+TREE额外优化，叶子节点双向指针mysql> desc SELEct * from city where id<10;mysql> desc SELEct * from city where countrycode like 'CH%';以下两种查询，大几率受不到叶子节点双向指针优化。mysql> desc SELEct * from city where countrycode in ('CHN','USA');mysql> desc SELEct * from city where countrycode='CHN' or countrycode='USA';建议： 如果查询列重复值少的话，我们建议改写为 union all desc SELEct * from city where countrycode='CHN'union allSELEct * from city where countrycode='USA';

4.4 ref 辅助索引等值查询

desc SELEct * from city where countrycode='CHN';

4.5 eq_ref : 多表连接查询中，非驱动表的连接条件是主键或唯一键时

@H_778_5@mysql> desc SELEct city.name,country.name from city left join country on city.countrycode=country.code where city.population<100;

4.6 const(system)：主键或唯一键等值查询

@H_778_5@mysql> desc SELEct * from city where id=1;

4.7 NULL

@H_778_5@mysql> desc SELEct * from city where id=1000000000000000;

5.key_len信息详解

5.1 作用

用来判断联合索引应用的部分。例如： idx(a,b,C) 我们希望应用联合索引的部分越多越好

5.2 如何计算

key_len=a+b+c 列的key_len长度，按照每列的最大预留长度来做的计算。create table t1 (id int,a int ,b char(10),c varchar(10));最大存储预留长度（字节）: -------------------------------------------------------------------------------	数据类型 ：	    占用字节量	            有not null           没有Not Null -------------------------------------------------------------------------------		数字类型：  	Tinyint      :  1字节                    1                   1+1	int          ： 4字节                    4                   4+1	bigint       ： 8字节                    8                   8+1-------------------------------------------------------------------------------	字符串类型： 	utf8:  	char(10)     ： 10*3字节 =30             30                  30+1	varchar(10)  ： 10*3+2字节=32            32                  32+1-------------------------------------------------------------------------------			    	utf8mb4: 	char(10)     ：10*4字节 =40              40                  40+1	varchar(10)  ：10*4字节+2 =42            42                  42+1-------------------------------------------------------------------------------use test;	create table test (id int not null priMary key auto_increment,a  int not null ,                            # 4  b  int ,                                     # 5c  char(1@R_450_2827@ ,                       # 40d  varchar(10),                              # 43e  varchar(1@R_450_2827@                      # 42)ENGIne=innodb charset=utf8mb4;alter table test add index idx(a,b,c,d,E);5个列覆盖： 4+5+40+43+42=1344个列覆盖：4+5+40+43=923个列覆盖： 4+5+40=49 2个列覆盖：4+5=9 应用1个列：4

5.3 测试

@H_778_5@mysql> desc SELEct * FROM test where a=10 and b=10 and  c='a' and d='a' and e='a';mysql> desc SELEct * FROM test where a=10 and b=10 and  c='a' and d='a';mysql> desc SELEct * FROM test where a=10 and b=10 and  c='a';mysql> desc SELEct * FROM test where a=10 and b=10;

5.4 联合索引应用细节

条件：

联合索引应用要满足最左原则a.建立联合索引时，选择重复值最少的列作为最左列。b.使用联合索引时，查询条件中，必须包含最左列，才有可能应用到联合索引。

联合索引不同覆盖场景:

@H_778_5@mysql> alter table t100w add index idx(num,k1,k2);num :  5k1  :  9k2  :  17

a.全部覆盖 (key_len:31)

@H_778_5@mysql> desc SELEct * FROM t100w where num=913759  and k1='ej' and k2='EFfg';mysql> desc SELEct * FROM t100w where k1='ej' and k2='EFfg' and  num=913759 ;mysql> desc SELEct * FROM t100w where num=913759  and k1='ej' and k2 in('EFfg','abcd');mysql> desc SELEct * FROM t100w where num=913759  and k1='ej' and k2 like 'EF%';

说明：

a= and b= and c=  b= and c= and a=

b.部分覆盖 idx(a,b,C)

where a =  and  b = where b =  and  a = where a = where a =  and   b> < >= <= in like between and   and  c= 例如： mysql> desc SELEct * FROM t100w where num=913759  and k1>'zz' and k2='EFfg';总结：如果联合索引中间出现了<>,between,like都会使得索引匹配截止于此。如何优化？ (num,k1,k2)   ---->  (num,k2,k1)mysql> desc SELEct * FROM t100w where num=913759 and k2='EFfg' and k1>'zz';

c. 完全不覆盖 idx(a,b,C)

where  b  c  where  b   where  c

6.extra 额外的信息

using filesort   ---> group by  order by disTinct  union all mysql> desc SELEct * from city where countrycode='CHN' order by population;注意: where+order by 一定要点联合索引优化：mysql> alter table city add index idx_1(CountryCode,population);mysql> show index from city;mysql> desc SELEct * from world.city where countrycode='CHN' order by population;

7.应用场景

数据库慢： a. 应急性的慢。	 show full processlist; ----> 慢语句 ----> explain SQL ---> 优化索引、改写语句b. 间歇性慢。	 slowlog   ---->  慢语句  --->  explain SQL ---> 优化索引、改写语句

第7章建立索引的原则

1.说明

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

2.降低索引树高度

(必须的)建表时一定要有主键,一般是个无关业务的自增列数字列。

3.选择唯一性索引

1.唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。2.例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。3.如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化方案:

1.如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分2.可以将此列和其他的查询类,做联和索引SELEct count(*) from world.city;SELEct count(disTinct countrycodE) from world.city;SELEct count(disTinct countrycode,population ) from world.city;

4.尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

5.限制索引的数目

索引的数目不是越多越好。可能会产生的问题:1.每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。2.修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。3.优化器的负担会很重,有可能会影响到优化器的选择.4.percona-toolkit中有个工具,专门分析索引是否有用

6.删除不再使用或很少使用的索引(percona toolkit)

1.表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。2.数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7.建索引原则总结

1.必须要有主键,如果没有可以做为主键条件的列,创建无关列2.经常做为where条件列  order by  group by  join on, disTinct 的条件(业务:产品功能+用户行为)3.最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引4.列值长度较长的索引列,我们建议使用前缀索引.5.降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxX)6.索引维护要避开业务繁忙期，建议用pt-osc

第8章不走索引的情况

1.没有查询条件或者查询条件没有建立索引

SELEct * from city; 
SELEct * from city where 1=1;

2.查询结果集是原表中的大部分数据，应该是15-25%以上

100w  num 有索引   
desc SELEct * FROM t100w where num>1;    ----> 全表

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
MySQL的预读功能有关。

可以通过精确查找范围，达到优化的效果。
1000000
desc SELEct * FROM t100w where num>50000 and num<60000;

3.索引本身失效，统计信息不真实（过旧）

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:
有一条SELEct语句平常查询时很快,突然有一天很慢,会是什么原因
SELEct?  --->索引失效,统计数据不真实
innodb_index_stats  
innodb_table_stats  

立即更新：
mysql> ANALYZE TABLE world.city;

4.查询条件使用函数在索引列上或者对索引列进行运算

错误的例子：SELEct * FROM test where id-1=9;
正确的例子：SELEct * FROM test where id=10;
算术运算
函数运算
子查询

5.隐式转换导致索引失效

这样会导致索引失效. 错误的例子：
mysql> create table `num` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(1@R_450_2827@,
  `num` char(1@R_450_2827@,
  PRIMARY KEY (`id`),
  KEY `inx` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

mysql> desc num;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | NO   |     | NULL    |                |
| num   | char(10) | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+

mysql> insert into num(name,num)
values
('z3','123456'),
('l4','123'),
('w5','321');

mysql> alter table num ADD INDEX inx(num);
mysql> SHOW INDEX FROM num;

mysql> DESC SELECT * FROM num WHERE num=123456;
mysql> DESC SELECT * FROM num WHERE num='123456';

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 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limitor或in 可以修改成union allEXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');改写成：EXPLAIN SELECT * FROM teltab WHERE telnum='110'union allSELECT * FROM teltab WHERE telnum='119'

7.like "%_" 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引
%linux%类的搜索需求，可以使用elasticsearch 或者 mongodb 专门做搜索服务的数据库产品

大佬总结

以上是大佬教程为你收集整理的[MySQL]-06MySQL索引及执行计划全部内容，希望文章能够帮你解决[MySQL]-06MySQL索引及执行计划所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。

标签：InnoDB linux list load mysql partition php sql 优化工具并发应用场景排序数据库查找程序员算法索引遍历重构

上一篇: IIS 发布定时任务程序会停止执行... 下一篇:jmeter正则匹配，json提取器、跨...

猜你在找的程序笔记相关文章

You can't specify target table 'xxx' for update in FROM clause的解决 2022-07-21
【UNIAPP】上传视频，进度条的前台与后端 2022-07-21
十款代码表白特效，一个比一个浪漫！ 2022-07-04
作业3 2022-07-06
linux系统下部署项目访问报404错误的解决方法 2022-05-30
C++带有指针成员的类处理方式详解 2022-06-07
Linux——（1）基本命令 2022-07-21
JavaScript之正则表达式学习笔记 2019-11-07
Redux源码学习笔记 2019-11-07
Webpack学习笔记 2019-11-07

[MySQL]-06MySQL索引及执行计划

第1章 索引介绍

1.介绍

2.索引查找算法

3.bTREE查找算法演变

第2章 聚簇(区)索引

1.前提

2.建议

3.功能

4.构建过程

第3章 辅助索引

1.前提

2.功能

3.构建过程

第4章 索引考虑事项

1.回表是什么？ 回表会带来什么问题？ 怎么减少回表？

2.索引树高度的影响因素？ 如何解决？

第5章 索引应用

1.压测

2.查询表的索引

3.创建索引

3.1 单列辅助索引

3.2 创建联合索引

3.3 前缀索引创建

4.删除索引

第6章 执行计划获取和分析

1.命令介绍

2.使用方法

3.执行计划信息介绍

4.type信息详解

4.1 ALL 没有使用到索引

4.2 index 全索引扫描

4.3 range 索引范围扫描

4.4 ref 辅助索引等值查询

4.5 eq_ref : 多表连接查询中，非驱动表的连接条件是主键或唯一键时

4.6 const(system)： 主键或唯一键等值查询

4.7 NULL

5.key_len信息详解

5.1 作用

5.2 如何计算

5.3 测试

5.4 联合索引应用细节

6.extra 额外的信息

7.应用场景

第7章 建立索引的原则

1.说明

2.降低索引树高度

3.选择唯一性索引

4.尽量使用前缀来索引

5.限制索引的数目

6.删除不再使用或很少使用的索引(percona toolkit)

7.建索引原则总结

第8章 不走索引的情况

1.没有查询条件或者查询条件没有建立索引

2.查询结果集是原表中的大部分数据，应该是15-25%以上

3.索引本身失效，统计信息不真实（过旧）

4.查询条件使用函数在索引列上或者对索引列进行运算

5.隐式转换导致索引失效

6. <>,not in 不走索引（辅助索引）

7.like "%_" 百分号在最前面不走

大佬总结

第1章索引介绍

第2章聚簇(区)索引

第3章辅助索引

第4章索引考虑事项

1.回表是什么？回表会带来什么问题？怎么减少回表？

2.索引树高度的影响因素？如何解决？

第5章索引应用

第6章执行计划获取和分析

4.6 const(system)：主键或唯一键等值查询

第7章建立索引的原则

第8章不走索引的情况