sql优化

2021-07-25 00:26:10

sql优化

一.定位问题

1.`show profiles`的使用

show profile 和 show profiles 语句可以展示当前会话(退出 session 后, profiling 重置为0) 中执行语句的资源使用情况.
Profiling 功能由MySQL会话变量 : profiling 控制,默认是OFF.关闭状态.

1.1 确定数据库版本

Select  version();

Show profiles 是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。

1.2 确定是否开启 `profiles` 功能

SHOW VARIABLES LIKE ‘profiling‘;
#或
select @@profiling;

如果off则

set profiling=ON;
#或
set profiling=1;

1.3执行要查询的sql

SELECT COUNT(id) FROM student;

1.4 执行 `show profiles` 查看分析列表

show profiles;

show profiles 显示最近发给服务器的多条语句，条数根据会话变量 profiling_history_size 定义，默认是15，最大值为100。由query_id 和 duration 以及query组成

1.5 执行 `show profile`

show profile all for query query_id;

根据query_id 查看某个查询的详细时间耗费
all可替换
- ALL：显示所有开销信息
- BLOCK IO：阻塞的输入输出次数
- CONTEXT SWITCHES：上下文切换相关开销信息
- CPU：显示CPU的相关开销信息
- IPC：接收和发送消息的相关开销信息.
- MEMORY ：显示内存相关的开销，目前无用
- PAGE FAULTS ：显示页面错误相关开销信息
- SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数)
- SWAPS：显示swap交换次数的相关开销信息

2. `explain` 的使用

根据 show profiles 的使用,问题 Sending data时间过长. Sending data状态包含 ( 收集数据->发送数据),怀疑没有走索引

explain SELECT COUNT(id) FROM student;

使用只需要在执行语句前添加 explain即可

expain出来的信息有10列，分别是

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

2.1 id、

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同
- 执行顺序由上至下
id不同
- id值越大，执行优先级越高
- 如果行引用其他行的并集结果，则该值可以为NULL
id相同不同,同时存在
- 可以认为相同id为一组,同组从上往下顺序执行
- 所有组,id值越大,优先级越高,越先执行

2.2 select_type

**simple：** 简单的select查询，没有union或者子查询
primary： 最外层的select查询
union： union中的第二个或随后的select查询，不依赖于外部查询的结果集
dependent union： union中的第二个或随后的select查询，依赖于外部查询的结果集
subquery： 子查询中的第一个select查询，不依赖与外部查询的结果集
dependent subquery： 子查询中的第一个select查询，依赖于外部查询的结果集
derived： 用于from子句中有子查询的情况，mysql会递归执行这些子查询，此结果集放在临时表中

3、table

table用来表示输出行所引用的表名

4、type（重要）

从好到坏

null > system > const > eq_ref > ref > range > index > all

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

explain
SELECT id
FROM teacher
WHERE id =
      (SELECT teacher_id
       FROM student
       WHERE student.id = 1 );

这里type 都是const

eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
all Full Table Scan 将遍历全表以找到匹配的行

2.5 possible_keys

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在 possible_keys 中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

2.6 key

key 列显示的是当前表实际使用的索引，如果没有选择索引，则此列为null，要想强制MySQL使用或忽视possible_keys 列中的索引，在查询中使用FORCE INDEX 、USE INDEX 或者 IGNORE INDEX

2.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

key len的长度还和字符集有关,latin1一个字符占用1个字节,gbk一个字符占用2个字节,utf8一个字符占用3个字节。key_len的计算法方法：

列类型	KEY_LEN	备注
id int	key_len = 4+1	int为4bytes,允许为NULL,加1byte
id bigint not null	key_len=8	bigint为8bytes
user char(30) utf8	key_len=30*3+1	utf8每个字符为3bytes,允许为NULL,加1byte
user varchar(30) not null utf8	key_len=30*3+2	utf8每个字符为3bytes,变长数据类型,加2bytes
user varchar(30) utf8	key_len=30*3+2+1	utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes
detail text(10) utf8	key_len=30*3+2+1	TEXT截取部分,被视为动态列类型。

2.8 ref

ref 列用来显示使用哪个列或常数与key一起从表中选择相应的行。它显示的列的名字（或const），此列多数时候为null

2.8 rows

rows列显示的是mysql解析器认为执行此SQL时必须扫描的行数。此数值为一个预估值，不是具体值，通常比实际值小,也就是说，用的越少越好

2.9 filtered

此参数为mysql 5.7 新加参数，指的是返回结果的行数所占需要读到的行（rows的值）的比例 . 对于使用join时，前一个表的结果集大小直接影响了循环的行数

Filtered 表示返回结果的行数占需读取行数的百分比 Filtered 列的值越大越好 Filtered列的值依赖于统计信息

2.10 extra（重要）

包含不适合在其他列中显式但十分重要的额外信息

using index： 该值表示这个SQL语句使用了覆盖索引（覆盖索引是指可以直接在索引列中得到想要的结果，而不用去回表），此时效率最高

如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

using where： 表示存储引擎搜到记录后进行了后过滤(POST-FILTER)，如果查询未能使用索引，using where的作用只是提醒我们mysql要用where条件过滤结果集
using temporary 表示mysql需要使用临时表来存储结果集，常见于排序和分组查询
using filesort： 是指mysql无法利用索引直接完成排序（排序的字段不是索引字段），此时会用到缓冲空间来进行排序

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

using join buffer： 强调在获取连接条件时没有用到索引，并且需要连接缓冲区来存储中间结果。（性能可以通过添加索引或者修改连接字段改进） Block Nested Loop是指Block Nested-Loop Join算法：将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.
impossible where： 表示where条件导致没有返回的行
using index condition： 是mysql 5.6 之后新加的特性，结合mysql的ICP（Index Condition Pushdown）特性使用。主要是优化了可以在索引（仅限二级索引）上进行 like 查找

如果extra中出现多个上面结果，则表示顺序使用上面的方法进行解析查询
distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

二.问题解决

我这里是因为没有添加索引,所以添加索引就完事了

CREATE INDEX index_name ON table_name ( 要添加索引的列名 );

索引的影响

优点

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

添加注意

对于那些在查询中很少使用或者参考的列不应该创建索引。

这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值的列也不应该增加索引。

这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
对于那些定义为text, image和bit数据类型的列不应该增加索引。

这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。

这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；

酌情参考