第5节 MySQL 性能优化篇 2021-12-28

最新推荐文章于 2024-07-15 19:44:21 发布

偶入编程深似海

最新推荐文章于 2024-07-15 19:44:21 发布

阅读量647

点赞数

分类专栏： Java 组件使用文章标签： mysql 性能优化数据库

本文链接：https://blog.csdn.net/qq_21438267/article/details/122197785

版权

Java 组件使用专栏收录该内容

22 篇文章 2 订阅

订阅专栏

Java组件总目录

文章目录

Java组件总目录
一、性能优化的思路
二、慢查询日志
三、查看执行计划
- 参数说明
四、SQL语句优化
五、profile分析语句
- Profile功能使用
六、服务器层面优化
- 1 缓冲区优化
- 2 降低磁盘写入次数

一、性能优化的思路

首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
查看执行计划，查看有问题的SQL的执行计划
针对查询慢的SQL语句进行优化
使用【show profile[s]】查看有问题的SQL的性能使用情况
调整操作系统参数优化
升级服务器硬件

二、慢查询日志

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的SQL。MySQL数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL语句，这将极大程度帮助我们快速定位到症结所在，以便对症下药。

至于查询时间的多少才算慢，每个项目、业务都有不同的要求。

MySQL的慢查询日志功能默认是关闭的，需要手动开启。

1 开启慢查询功能

【slow_query_log】：是否开启慢查询日志，1为开启，0为关闭。
【log-slow-queries】：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
【slow-query-log-file】：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
【long_query_time】：慢查询阈值，当查询时间多于设定的阈值时，记录日志，【单位为秒】。

# 在 MySQL 执行 SQL 语句设置，但是如果重启 MySQL 的话将失效
set global slow_query_log = ON; 
set global long_query_time = 1;

# 修改/etc/my.cnf配置文件，重启 MySQL, 这种永久生效.
[mysqld] 
slow_query_log=ON 
long_query_time=1

2 慢查询日志格式

查看慢查询日志：

Query_time,这条SQL执行的时间,越长则越慢
Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
Rows_sent,查询返回的行数
Rows_examined,查询检查的行数，越长就当然越费时间

3 分析慢查询日志的工具

使用mysqldumpslow工具，mysqldumpslow是MySQL自带的慢查询日志工具。可以使用mysqldumpslow工具搜索慢查询日志中的SQL语句。

# 得到按照时间排序的前10条里面含有左连接的查询语句：
[root@localhost mysql]# mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

三、查看执行计划

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.

使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 explain 就可以了

参数说明

1 id

每个单位查询的SELECT语句都会自动分配的一个唯一标识符，表示查询中操作表的顺序.

id相同：执行顺序由上到下
id不同：如果是子查询，id号会自增，id越大，优先级越高。
两者情况同时存在

2 select_type（重要）

单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。

simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple。
primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。
union： union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union。
union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null。
subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
derived： from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。
dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响。
dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。

3 table

显示的单位查询的表名，有如下几种情况：

如果查询使用了别名，那么这里显示的是别名
如果不涉及对数据表的操作，那么这显示为null
如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

4 partitions

使用的哪些分区（对于非分区表值为null）。较少使用。
5.7之后的版本默认会有 partitions 和 filtered两列，但是5.6版本中是没有的
也更多的是自己分库分表，而不是使用分区表。
1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
2）一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难
3）自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控
4）运维复杂

5 type（重要）

除了all之外，其他的type都可以使用到索引
除了index_merge之外，其他的type只可以用到一个索引。
性能按顺序依次递减。

1 const（重要）system

system是const类型的特列，一般不会出现。const 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库
也叫做唯一索引扫描

select * from user where id = 1

2 eq_ref（重要）

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

select * from user a left join user b on a.id = b.id

3 ref

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

select * from user a left join user b on a.name = b.name

4 fulltext

全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

5 ref_or_null

与ref方法类似，只是增加了null值的比较。实际用的不多。

6 unique_subquery

用于where中的in形式子查询，子查询返回不重复值唯一值

7 index_subquery

用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

8 range（重要）

索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

9 index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range.

10 index（重要）

select结果列中使用到了索引，type会显示为index。

全部索引扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。（覆盖索引）

11 all（重要）

这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

6 possible_keys

此次查询中可能选用的索引，一个或多个

7 key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

8 key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如- 果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。
留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

  key_len的长度计算公式：
  varchar(10)变长字段且允许NULL = 10 * ( character set： utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) 
  varchar(10)变长字段且不允许NULL = 10 *( character set： tf8=3,gbk=2,latin1=1)+2(变长字段) 
  char(10)固定字段且允许NULL = 10 * ( character set： utf8=3,gbk=2,latin1=1)+1(NULL) 
  char(10)固定字段且不允许NULL = 10 * ( character set： utf8=3,gbk=2,latin1=1) 
  bigint的长度是8bytes 
  int key_len长度是4 ，
  tinyint的长度是1 
  smallint 长度是2 
  middleint长度是3

9 ref

如果是使用的常数等值查询，这里会显示const
如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这可能显示为func

10 rows

这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB里面使用了MVCC并发机制）

11 filtered

filtered列指示将由mysql server层需要对存储引擎层返回的记录进行筛选的估计百分比，也就是说存储引擎层返回的结果中包含有效记录数的百分比。最大值为100，这意味着没有对行进行筛选。值从100减小表示过滤量增加。rows显示检查的估计行数，rows×filtered显示将与下表联接的行数。

例如，如果rows为1000，filtered为50.00（50%），则要与下表联接的行数为1000×50%=500。

12）extra（重要）

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种

1 Using filesort

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

2 Using temporary

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。需要优化SQL

3 using index（重要）

查询时不需要回表查询，直接通过索引就可以获取查询的结果数据。
表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错！
如果同时出现Using Where ，说明索引被用来执行查找索引键值
如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作。

4 using where（重要）

表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；

5 Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

impossible where

where子句的值总是false ，不能用来获取任何元组。 可能SQL写的存在问题

四、SQL语句优化

1 索引优化

2 LIMIT优化

如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。
处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。 LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。

3 其他查询优化

小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数。
避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描
避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典型的场景就是数据量少的时候）
JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。
WHERE条件中尽量不要使用not in语句（建议使用not exists）
合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

1 not in 与 not Exist

not in 逻辑上不完全等同于not exists.

not in, 如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。

create table t1(c1 int,c2 int);
create table t2(c1 int,c2 int);

insert into t1 values(1,2);
insert into t1 values(1,3);

insert into t2 values(1,2);
insert into t2 values(1,null);

select * from t1 where c2 not in(select c2 from t2);　　#-->执行结果：无
select * from t1 where not exists(select 1 from t2 where t2.c2=t1.c2);　　#-->执行结果：1　　3

2 in和exists

如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

# 表A(小表)，表B(大表)
select * from A where cc in(select cc from B)　　-->效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)　　-->效率高，用到了B表上cc列的索引。

五、profile分析语句

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能瓶颈在什么地方。

通常我们是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MySQL 5.0.37以及以上版本中才有实现。

默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。

Profile功能使用

Profile 功能由MySQL会话变量 : profiling控制,默认是OFF关闭状态。
查看是否开启了Profile功能:

show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源使用情况.
show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况.
显示的记录数由变量:profiling_history_size 控制,默认15条

select @@profiling; 
-- 或者 
show variables like '%profil%';

set profiling=1; --1是开启、0是关闭

show profiles # 查看分析列表
show profile for query 2; # 查询第二条语句的执行情况
show profile cpu,swaps for query 2;  # 可指定资源类型查询

六、服务器层面优化

1 缓冲区优化

将数据保存在内存中，保证从内存读取数据

设置足够大的 innodb_buffer_pool_size ，将数据读取到内存中。
怎样确定 innodb_buffer_pool_size 足够大。数据是从内存读取而不是硬盘？

show variables like 'innodb_buffer_pool_pages_%';

在这里插入图片描述

2 降低磁盘写入次数

对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志、慢查询日志、错误日志
使用足够大的写入缓存 innodb_log_file_size。推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size。过大，实例恢复时间长；过小，造成日志切换频繁。
设置合适的innodb_flush_log_at_trx_commit，和日志落盘有关系。

偶入编程深似海

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
第5节 MySQL 性能优化篇 2021-12-28

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档文章目录一、性能优化的思路二、慢查询日志开启慢查询功能慢查询日志格式分析慢查询日志的工具三、查看执行计划参数说明1 id2 select_type（重要）3 table4 partitions5 type（重要）1 const（重要）system2 eq_ref（重要）3 ref4 fulltext5 ref_or_null6 unique_subquery7 index_subquery8 range（重要）9 index_merge10
复制链接

扫一扫