SQL优化

SQL分析主要有两个切入点

EXPLAIN执行计划分析

数据库慢SQL查询

EXPLAIN执行计划分析

id

id是查询序列编号，每张表都是单独访问的，一个SELECT就会有一个序号。

在连接查询中，先查询的叫做驱动表，后查询的叫做被驱动表。在实际应用场景中，尽可能的把小表放在前面查询，大表放后。（小表驱动大表的思想）

总结：id值不同时，表的查询顺序是先大后小。id值相同时，表的查询顺序是从上往下执行。

select_type

select_type表示查询类型，它的常用取值类型如下：

  • SIMPLE：简单查询，表示此查询不包含 UNION 查询或子查询
  • PRIMARY：表示SQL语句中的主查询，也就是最外层的查询
  • DERIVED：衍生查询，表示得到最终结果之前会用到临时表
  • UNION：表示此查询是 UNION 的第二或随后的查询
  • UNION RESULT：UNION 的结果，table列显示是哪些序列的UNION结果
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • SUBQUERY：子查询中的所有内查询，即SELECT或者WHERE中包含的子查询
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果

DERIVED 和 UNION 的执行顺序：先执行UNION右边的查询，再执行UNION左边的查询，此时左边表的查询类型是DERIVED。

type连接类型

type字段比较重要，它提供了判断查询是否高效的重要依据，可以判断查询是全表扫描还是索引扫描等。常见的类型有 const、system、eq_ref、ref、range、index、all

const：针对主键或者唯一索引的等值查询，最多返回一条数据。const查询速度非常快，因为它仅需读取一次即可。

system：查询表中只有一条数据时，是特殊的const类型。对于MyISAM 和 Memory的表，只查询到一条数据，也是system。

eq_ref：在多表连接查询中，被驱动表通过唯一索引（UNIQUE KEY 或者 PRIMARY KEY）进行访问时，被驱动表的访问方式就是eq_ref。

例如：

-- ALTER TABLE teacher_contact DROP PRIMARY KEY; 
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid); --增加主键索引 
explain select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

执行计划如下：

mysql 执行计划 filtered_数据

ref：查询用到了非唯一索引，或者关联操作只使用了索引的最左前缀，则连接类型是ref。

range：范围查询。如果where后面是 between and 或 <或 > 或 >= 或 <=或in这些，type类型就为range。 MySQL中in的参数个数不受限制，但是长度会受到限制，一般为64M。in通常会走索引，但是in的个数较多时，就不会再走索引，而是进行全表扫描。

index：Full Index Scan，全索引扫描。

index 和 all 的区别是，index只扫描索引树即可，所以它比all的查询效率要高。

例如：EXPLAIN SELECT tid FROM teacher; -- tid为主键

all：Full Table Scan，全表扫描。

null：不用访问表或者索引就能得到结果，例如：EXPLAIN select 1 from table1;

possible_key、key

possible_key表示可能用到的索引，key表示实际用到的索引。如果这一列为空，表示没有用到索引。 possible_key可以有一个或者多个，当然，可能用到索引不代表一定用到索 引。

key_len

索引的长度（使用的字节数）。跟索引字段的类型、长度有关。

例如：varchar(255)，在utf8mb4编码中，一个字符占了4个字节，所以是255 * 4 = 1020。另外使用可变长度varchar，需要额外增加2个字节，允许为null需要额外增加1个字节，所以一共是1023个字节长度。

因此，key_len越长，表示使用的索引范围越广。

rows

表示扫描多少行才能返回请求的数据，是一个预估值，一般来说行数越少越好。

filtered

filtered表示返回结果的行数占需读取行数的百分比，它只对index 和 all 的扫描有效。如果比例很低，说明存储引擎层返回的数据需要经过大量过滤，这个会消耗性能的，需要关注。

ref

表示使用哪个 列 或者 常数 或者 索引

例如：表示用到了两个常量进行数据的筛选。

mysql 执行计划 filtered_数据库_02

 

Extra

EXPLAIN中的很多额外信息都会在改字段中进行显示。

  • Using filesort： 表示 MySQL 需额外的排序操作，不能通过索引顺序达到排序效果。一般如果有 Using filesort,，都建议优化去掉， 因为这样的查询 CPU 资源消耗大。
  • Using index ：覆盖索引扫描，表示查询在索引树中就可查找所需数据,，不用扫描表数据文件，往往说明性能不错。
  • Using temporary：查询有使用临时表，一般出现于排，分组和多表 join 的情况，查询效率不高，建议优化。

EXPLAIN执行计划官方地址：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

数据库慢查询日志

慢查询的监控：

  • Druid包提供了慢查询的监控
  • Mysql提供了慢查询日志

MySQL慢查询开启

show variables like 'slow_query%' ;

mysql 执行计划 filtered_数据_03

show_query_log： 默认是关闭的（开启它需要代价，因为需要写文件）

show_query_log_file: 慢日志存放的目录

慢查询判定配置

show variables like '%long_query%' ;

默认是10s，也就是记录查询时间大于10s的sql，这三个参数可以在 my.cnf 文件中修改。

slow_query_log = ON 
long_query_time=2 
slow_query_log_file =/var/lib/mysql/localhost-slow.log

慢查询日志分析

例如：查询user_innodb这个表（300W数据），在没有任何索引的情况下查询（SELECT * FROM user_innodb where phone='180'; ），会耗时较长，于是我们在/var/lib/mysql/localhost-show.log 目录下的文件，内容如下：

# Time: 2022-03-08T19:15:54.086977Z 
# User@Host: root[root] @ [192.168.8.16] Id: 12 
# Query_time: 2.800553 Lock_time: 0.000517 Rows_sent: 3000001 
Rows_examined: 3000001 
SET timestamp=1646766951; 
select * from user_innodb;

如果文件内容较大，可以通过日志监控的方式，或者是用mysql提供的mysqldumpslow工具来分析。mysqldumpshow是MySQL提供的分析工具，在/usr/bin/mysqldumpshow目录下。https://dev.mysql.com/doc/refman/8.0/en/mysqldumpslow.html

mysqldumpshow使用：

例如查询用时最多的10条慢SQL：mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-

slow .log。 其中 -s 表示如何排序， t 表示查询时间或者平均查询时间 ； -t 查询条数 ； - g 相当于 grep ，从目标文件中匹配的关键字。

结果如下:

mysql 执行计划 filtered_数据库_04

参数说明：

  • Count代表这个SQL执行了多少次；
  • Time代表执行的时间，括号里面是累计时间；
  • Lock表示锁定的时间，括号是累计；
  • Rows表示返回的记录数，括号是累计。