* mysql - 索引与执行计划，sql 优化_索引和执行计划进行sql调优-CSDN博客

本文链接：https://blog.csdn.net/qq_38946877/article/details/106312215

mysql - 索引与执行计划，sql 优化

1、索引

索引的分类：
在这里插入图片描述
基础语法：

2、执行计划

在这里插入图片描述

2.1 执行计划的作用：

在这里插入图片描述
执行计划包含的消息：

2.2 执行计划 - id

可以知道 SQL 语句执行的顺序
在这里插入图片描述
* id相同，执行顺序由上往下

id 不同，id 数值大的先加载执行
id 有相同的，也有不同的。优先加载执行 id 数值大的，id 数值一样的从上往下加载

2.3 执行计划 - select type

查询的类型，主要用于区别，普通查询、联合查询、子查询等的复杂查询

分类有：
在这里插入图片描述
simple 示例：

explain select * from testdemo;

在这里插入图片描述
primary 和 subquery

primary 查询中包含任务复杂的字部分，最外层查询则被标记为主要的

explain 
select t1.*, (select a from t_lock_1 where a = 10) s2 from testdemo t1;

在这里插入图片描述
derived：衍生表示例：

explain 
select t1.* from testdemo t1, (select a from t_lock_1 where a = 10) s2 where t1.id = s2.a;

在这里插入图片描述
union 示例：

explain
select t1.* from testdemo t1
union
select t1.* from testdemo t1

在这里插入图片描述

执行计划 - type 总结
在这里插入图片描述

2.4 执行计划 - table

显示这一行的数据时关于哪张表的
在这里插入图片描述

2.5 执行计划 - type

type 显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依是：

在这里插入图片描述

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或 唯一索引扫描

ref：

非唯一性索引扫描，返回匹配某个单独值的所有行
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个复合条件的行，所以他应该属于查找和扫描的混合体

range
只检索给定范围的行，使用一个索引来选择行，key 列显示使用的那个索引
一般就是在你的where语句中出现 between、<、>、in 等的查询
这种范围扫描索引，比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

示例：
在这里插入图片描述

index

全索引表扫描，效率不高

explain
select id from testdemo t1

在这里插入图片描述

all

full table scan，将遍历全表以找到匹配的行

explain
select t1.* from testdemo t1

在这里插入图片描述
覆盖索引
查询出来的所有列，都是索引字段，这种情况叫覆盖索引。
查询的时候，不会真实去查询表的数据，而是查询索引的数据

2.6 执行计划 - key

实际使用的索引。如果为 null，则没有使用索引。
查询中若使用了覆盖索引，则该索引和查询的select字段重叠

通过 key 可以知道一条 SQL 语句是否用到索引
在这里插入图片描述

2.7 执行计划 - key_len

通过 key_len 判断是否充分使用到索引。

如果充分用到索引比没有充分用到索引的 key_len 更大

key_len 和 varchar char、字符集、长度、是否为null 有关
在这里插入图片描述
字符集 utf-8 占3个字节
允许为 null 时加 1

在这里插入图片描述

在这里插入图片描述
字符类型：

字符类型-索引字段为char类型+不可为Null时

字符类型-索引字段为char类型+允许为Null时

索引字段为varchar类型+不可为Null时

varchar(n)变长字段+不允许Null=n*(utf8=3,gbk=2,latin1=1)+2

索引字段为varchar类型+允许为Null时
在这里插入图片描述
varchar(n)变长字段+允许Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2

数值类型：

日期和时间类型：

datetime类型在5.6中字段长度是5个字节，
datetime类型在5.5中字段长度是8个字节

整数/浮点数/时间类型的索引长度
在这里插入图片描述
key_len 总结

2.8 执行计划 - ref

ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
在这里插入图片描述

2.9 执行计划 - rows

rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

在这里插入图片描述

2.10 执行计划 - Extra

Extra
包含不适合在其他列中显示但十分重要的额外信息
在这里插入图片描述
Using filesort：文件排序
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”

Using temporary
使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
在这里插入图片描述

USING index

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找;
在这里插入图片描述
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

USING index

覆盖索引（Covering Index）,一说为索引覆盖。
理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
在这里插入图片描述
Using where
表明使用了where过滤

using join buffer
使用了连接缓存：
在这里插入图片描述
impossible where
where子句的值总是false，不能用来获取任何元素

3、SQL 优化

3.1 优化实战：

策略1.尽量全值匹配

对于复合索引，在编写SQL语句时尽量在where条件中都使用上复合索引中的列

策略2.最佳左前缀法则

如果索引了多列（复合索引），要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

策略3.不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

策略4.范围条件放最后

存储引擎不能使用索引中范围条件右边的列
例如复合索引(a,b,c)，如果在where语句中在最后将列作为范围查询，则 b 和后面的 c 都会失效，只有复合索引中的 a 起作用。

策略5.覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

策略6.不等于要甚用

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

策略7.Null/Not 有影响

注意null/not null对索引的可能影响
表字段设置为 不能为 null 时，SQL 语句中使用了 is null 或 is not null 都会导致索引会失效
表字段设置为 允许为 null 时，SQL 语句中使用了 is null 会使用到索引，但是 is not null 索引会失效

策略8.Like查询要当心

like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

策略9.字符类型加引号

字符串不加单引号索引失效

策略10.OR改UNION效率高
在这里插入图片描述

总结：
在这里插入图片描述

3.2 批量导入

3.2.1 insert 语句优化

在这里插入图片描述

3.2.2 load data inflie

LOAD DATA INFLIE；
使用LOAD DATA INFLIE ,比一般的insert语句快20倍

大批量数据迁移时，比较好的做法，步骤如下
1，在要迁移的数据库中，执行以下 SQL 语句，把数据导到本地

select * into OUTFILE 'D:\\product.txt' from product_info

注意：
执行到处 SQL 语句时，可能会报错 [Err] 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

执行如下 SQL 语句解决

show variables like 'secure_file_priv';

在这里插入图片描述
secure_file_priv 为 null 时，表示限制 mysqld 不允许导入或导出
secure_file_priv 为 /tmp 时，表示限制 mysqld 只能在 /tmp 目录中执行导入导出，其他目录不能执行
secure_file_priv 没有值时，表示不限制 mysqld 在任意目录的导入导出。

调整后的导出 sql 语句为：

select * into outfile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\product2.txt' 
from testdemo where id <= 3;

ProgramData 文件夹可能是隐藏文件，需要设置可以查看隐藏文件。
导出来的文件内容如下：
在这里插入图片描述

2，把这个文件上传到 Linux 中，执行如下 SQL 语句，即可将文件中的数据插入到目标表中

load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\product.txt' into table testdemo;

* mysql - 索引 与 执行计划，sql 优化