MySQL语句性能分析与优化

目录

SQL性能分析

SQL执行频率

SQL慢查询日志

Profile

Explain

SQL优化

插入数据的优化

主键优化

Order By优化

Group By优化

Limit 优化

Count 优化

Update 优化

多表连接查询优化

---

SQL性能分析

通过SQL性能分析来做SQL的优化，主要是优化SQL的查询语句（其中索引的优化占主要部分）

SQL执行频率

通过以下命令来查看INSERT、UPDATE、DELETE、SELECT的访问频次（查看不同语句所占的比例）

根据不同语句的执行频率来进行不同的优化

SHOW SESSION/GLOBAL STATUS;    查看当前会话/全局服务器的状态信息

SHOW SESSION/GLOBAL STATUS LIKE ‘Com_______’      #(7个下划线)查看当前会话/全局的SQL增删改查的执行频率（就是从服务器的状态信息中提取关于增删改查的信息）

show global status like 'Com_______'; #查看全局的SQL语句插入、更新、删除、查询的执行频率

SQL慢查询日志

查看MySQL的慢查询日志的开关是否打开

SHOW VARIABLES LIKE 'slow_query_log';

慢查询日志的作用

通过慢查询日志可以定位到哪些查询语句需要进行优化

慢查询日志是记录了所有执行时间超过指定参数（long-query-time，默认10s）的SQL语句的日志（即：当SQL语句的执行时间超过10s，我们就会认为此语句是慢查询）

慢查询日志的路径

Linux系统关于MySQL慢查询日志

Linux关于MySQL的配置文件在/etc/my.cnf中

Linux中默认MySQL的慢日志查询没有开启，需要配置MySQL的配置文件中配置如下信息:

       #开启MySQL慢日志查询开关

       slow_query_log = 1

       #设置慢日志的时间为2s，SQL执行时间超过2s就会被视为慢查询

       log_query_time = 2

       #保存之后然后重启mysql服务

Linxu会自动生成相关的日志文件存放慢日志查询中记录的信息，存放的位置在/var/lib/mysql/localhost-slow.log中

Windows系统关于MySQL慢查询日志

Windows默认开启了慢查询日志的开关，可以在MySQL的配置问价查看慢查询日志信息

Windows关于MySQL的配置文件在C:\ProgramData\MySQL\MySQL Server 8.0\my.ini中（以下是关于慢查询日志的配置信息）

       slow-query-log=1       #开启MySQL慢日志查询开关

slow_query_log_file="慢查询日志的存放位置"  #指定慢查询日志文件的存放位置

long_query_time=10    #设置慢日志的时间为10s

如果还是找不到对应的慢查询日志的存储位置

可以通过show variables like 'slow_%'来查看

慢查询日志的格式

Time：执行SQL的时间

User@Host：通过哪个用户，在哪个主机上登录到此SQL的

Query_time：当前命令执行耗时的时间

Lock_time： 表锁的时间（InnoDB的行锁等待不会反应在这里）

Rows_sent： 返回多少行

Rows_examined：检查了多少行

USE：使用的哪个数据库

SET timestamp：执行SQL的时间戳

ApplicationName：此字段会注释掉，用于告知此用户是通过什么软件连接到数据库的（此处为DataGrip）

以及查询语句

如果Rows_examined很大，而Rows_sent很小，则说明此查询需要优化

Profile

通过Profile可以帮助我们了解SQL语句执行的时间都耗费到哪里去了（也会记录错误的语句）

查看当前MySQL是否支持profile操作以及是否启用了此操作

show variables like '%profil%';

在MySQL中开启Profile功能

set session/global profiling = True;

Window和Linux默认Profile是关闭的（不同的版本可能有区别）

Profile相关语句

SHOW PROFILES; #查看会话中每一条SQL的耗时基本情况（每条SQL语句都有唯一的query_id）

SHOW PROFILE FOR QUERY query_id;         #查看此query_id对应的SQL语句各个阶段的耗时情况

SHOW PROFILE CPU FOR QUERY query_id; #查看此query_id对应的SQL语句的CPU使用情况

Explain

前面的分析语句都是通过时间粗略分析；explan可以看到Sql语句的执行计划（如何执行select语句信息，select语句执行过程中如何连接以及连接的顺序），经常通过此判断SQL语句的性能

MySQL索引3——Explain关键字和索引优化（SQL提示、索引失效、索引使用规则）_静下心来敲木鱼的博客-CSDN博客

---

SQL优化

插入数据的优化

Inster 优化

1、当插入多条数据时使用批量插入

INSTER INTO 表名 VALUES(值1.1 , 值2.1),(值1.2 , 值2.2)……;

2、当要插入的数据超过百条时，建议使用事务来进行优化（手动提交事务，通过事务来优化）

START TRANSCANTION;

INSERT INTO 表名 VALUES(值1.1 , 值2.1),(值1.2 , 值2.2)……;

INSERT INTO 表名 VALUES(值1.100, 值2.100),(值1. 101, 值2.101)……;

COMMIT;

3、当要大批量的插入数据时，使用INSTER语句插入性能低，可以使用MySQL提供的LOAD进行进行插入（将符合一定规则的文件直接导入到Mysql生成相应的表）

1. 客户端连接MySQL时加上参数--local-infile；表示当前客户端连接服务端时，需要加载客户端本地文件

mysql --local-infile -u root -p

     2、设置全局参数local_infile为1；表示开启从本地加载文件导入数据的开关

       show variables like '%infile%';       #查看是否开启load开关

set global local_infile=1;            #开启load开关

     3、执行load指令将准备好的本地文件中的数据加载到表结构中

       load data local infile ‘本地文件的路径’  into table 表名  fields terminated by ‘每个字段对应值的分隔符’ lines terminated by ‘每一行数据的分隔符’；

建议分隔符都使用英文符号

模拟load插入

员工信息.txt文件（路径为C:/Users/123/Desktop/员工信息.txt）

创建user123表

create table User123(
  id int auto_increment primary key,
  name varchar(10),
  age int,
  origo varchar(10)
);

将.txt文件导入表中

load data local infile 'C:/Users/Tdemo/Desktop/员工信息.txt' into table user123 fields terminated by ',' lines terminated by ';';

此时查看表的数据

select * from user123;

主键优化

在对主键进行插入数据时，顺序插入性能高于乱序插入

InnoDB关于主键的数据组织方式

表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)，我们再进行主键存放时就是根据顺序存放的

页分裂——乱序插入可能出现

页时InnoDB磁盘管理的最小单元，一个页的大小默认为16k，叶子节是有序的

每个页包含了2-N行数据（如果一行数据过大，就会行溢出）

页可以为空、也可以填充一半、也可以填充100%

主键顺序插入

主键乱序插入——可能会出现页分裂

50找到要插入的位置在第1个页的末尾；

发现此时第1页没有空间进行插入，此时会新建1个页；

然后找到第1个页的中间位置，将其右边的数据都迁移到新建的页中，然后再将50添加到新建的页中

此时再对三个页重新排序（需要消耗较多的性能）

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除；并且它的空间变得允许被其它记录声明使用

当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%）,InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间

Merge_threshold 合并页的阈值，可以自己设置（在创建表或索引时指定）

主键设计原则

1、满足业务需求的情况下，尽量降低主键的长度（此操作可以降低二级索引占用的空间）

2、插入数据时，建议主键进行顺序插入；可以使用AUTO_INCREMENT自增主键

3、尽量不要使用无序的值来作为主键，导致插入主键时可能乱序，在检索时也会耗费大量的IO

4、在进行业务操作时，尽量避免对主键的修改

Order By优化

进行排序时通过Explain显示的Extra字段的内容类型如果为Using Filsort，则需要进行优化，尽量将其优化为Using Index（通过满足覆盖索引实现）

Using Filesort和Index讲解

1、Using Filesort：通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作；所有不是通过索引直接返回排序结果的排序都叫FileSort排序

2、Using Index：查找使用了索引，并且通过索引可以接返回有序的数据，不需要额外排序，操作效率高

3、Backward index scan：反向扫描索引，进行降序排序时会出现；也是单独子啊排序缓冲区完成排序操作；需要进行优化

如何优化Backward index scan

在创建索引时，MySQL8之前的版本默认只是针对升序创建了索引；我们可以额外对字段创建关于降序的索引

格式如下（在字段后面跟上排序方式）

create index 索引名 on 表名(字段1 asc , 字段2 desc);

为字段1创建升序索引，为字段2创建倒序索引

总结

1、当创建联合索引后，在进行排序时如果一部分按照升序排序，一部分按照降序排序，则会产生using filesort；此时可以为此单独再创建索引

2、在使用索引时尽量实现覆盖索引

3、在使用是要避免索引失效

4、如果不可避免的出现filesort，在大数据排序时，可以适当增大排序缓冲区的大小sort_buffer_size（默认256k）

5、Show variables like ‘sort_buffer_size’;  #查看排序缓冲区的大小；当排序缓冲区占满之后会占用磁盘进行排序

Group By优化

主要研究索引对分组查询的影响，思路和排序优化是一致的

进行分组时通过Explain显示的Extra字段的内容类型如果为Using Temporary，则需要进行优化，尽量将其优化为Using Index（通过满足覆盖索引实现）

Using Temporary讲解

Using temporary：查找使用到了临时表，性能比较低

总结

1、在分组操作时可以通过索引来提高效率

2、在分组操作时，索引的使用也是满足最左前缀法则的

3、在分组操作时，尽量实现覆盖索引，避免回表查询

4、在分组操作时，避免索引失效

Limit 优化

在大数据量情况下，越往后分页，耗时越长，可能也会造成资源浪费

当我们查询limit 10000,10时，我们需要排序MySQL前10010条记录，但是只范围10000-10010之间的10条记录，其它记录丢弃，此时查询的代价就很大（排序是为了保证每次通过limit查到的记录都一样）

可以通过覆盖索引+子查询的方式优化

通过子查询找到10000-10010对应数据的主键值，然后再通过主键值来查找对应的数据

需要注意有些版本Mysql不支持在in之后使用子查询的语法，所以以下的语句无法实现

Select * from 表名 where 主键值 in (select 主键值 from 表名 order by limit 10000,10);

方式二 我们可以将子查询的结果看作一张表，通过多表查询来实现

Select a.* from 表1 a, (select 主键值 from 表1 order by limit 10000,10)  b where a.主键值=b.主键值;

Count 优化

不同存储引擎对于Count(*)的不同处理方式

MyISAM引擎中会把一个表的总行数存在磁盘上，因此执行count(*)的时候才会直接返回这个数，效率很高（前提是查询时没有where条件）

InnoDB引擎在执行count()*时，需要把遍历整张表，把数据一行一行地从引擎里面读出来，然后积累计数（如果为Null则不加1，如果为非Null值则计数加1），最后将积累的计数输出（无论是否有where条件，InnoDB都是这样操作的）

Count的不同用法以及性能差异

Count的主要用法有count(*)、count(主键)、count(字段)、count(X)

Count(主键)   ——主键的总记录行数

会遍历整张表，把每一行的主键值提取出来返回给服务层，服务层拿到主键后直接按照行数进行累加（不进行判断是否为Null）

Count(字段)   ——统计此字段有多少值不为Null，不一定是总记录数

分为加了Not Null约束和没有加Null约束两种情况

没有加Not Null约束：需要判断值是否为Null

把每一行的字段对应的值提取出来返回给服务层，服务层判断是否为Null，如果为Null则不加1，如果为非Null值则计数加1

加了Not Null约束：不需要判断是否为Null

把每一行的字段对应的值提取出来返回给服务层，服务层进行累加

Count(x) ——x为数字，查询表的总记录行数

InnoDB会遍历整张表，不过不会进行取值；服务层对于返回的每一行放一个数字x进去，直接按照行进行累加

Count(*) ——表当中的总记录行数

Mysql对InnoDB引擎做了优化，InnoDB引擎在执行count()*时，需要把遍历整张表，不过不会进行取值，直接按照行数进行累加，最后将积累的计数输出；底层count(*)=count(0)

性能比较

Count(*) ≈ Count(x) > Count(主键) > Count(字段)

尽量使用Count(*)

如何优化InnoDB引擎对Count关键字的处理

没有较好的优化策略，想要优化的话可以在插入数据或删除数据时自行计数，只不过比较繁琐

Update 优化

对于InnoDB引擎来说，默认事务隔离级别是通过行锁实现的；并且该行锁是针对索引加的锁，不是针对记录加的锁；如果该索引失效了，则会从行锁升级为表锁

即：对于建立了索引的字段使用的是行锁；没有建立索引的字段使用的是表锁；一旦锁表，就会降低并发操作

因此，优化建议为

1、使用Update时使用索引列作为更新条件，避免表锁

2、避免出现索引失效，将行锁升级为表锁

多表连接查询优化

内连接时，Mysql会自动把输出结果小的表选为驱动表，所以大表（输出结果多的表）的字段最好加上索引

左外连接时，左表会全表查询（不可避免地，因为要显示左表的全部数据），建议右边表的字段最好加上索引

右外连接时，右表会全表查询，建议左边表的字段最好加上索引

优化手段

1、在外连接时，尽量将小表作为驱动表，并保证被驱动表上的字段建立了索引

3、内连接时，尽量将大表的字段加上索引