MySQL（6）性能分析和性能优化

最新推荐文章于 2022-08-03 12:41:58 发布
最新推荐文章于 2022-08-03 12:41:58 发布
阅读量333 收藏 2
点赞数 1
分类专栏： # MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Miaoshuowen/article/details/104964532
版权

性能分析的思路

  1. 首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
  2. 其次【查看执行计划】查看有问题的SQL的执行计划 explain
  3. 最后可以使用【show profile[s]】 查看有问题的SQL的性能使用情况

1、慢查询日志

修改my.cnf开启：

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

慢查询日志介绍

开启慢查询功能
在这里插入图片描述
慢查询日志格式

tail -100f /var/lib/mysql/slow.log

在这里插入图片描述
分析慢查询日志的工具
使用mysqldumpslow工具

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

得到按照时间排序的前10条里面含有左连接的查询语句：

[root@localhost mysql] mysqldumpslow -s t -t 10 -g “left join” 
/var/log/mysql/slow.log

常用参数说明：

参数参数说明
-s：是表示按照何种方式排序
c :访问计数
l :锁定时间
r :返回记录
al :平均锁定时间
ar :平均返回记录数
at :平均查询时间
-t :是top_n的意思，即返回前面n条数据
-g :后边可以写一个正则表达式，带小写不敏感

2、profile分析语句

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能瓶颈在什么地方。比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MySQL 5.0.37以及以上版本中才有实现。默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。

开启Profile功能

  • Profile 功能由MySQL会话变量 : profiling控制,默认是OFF关闭状态。
  • 查看是否开启了Profile功能:
select @@profiling; 
show variables like ‘%profil%’

在这里插入图片描述
开启profile功能

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

mysql> select @@profiling; 
+-------------+ 
| @@profiling |
+-------------+ 
|           0 | 
+-------------+ 
1 row in set, 1 warning (0.00 sec) 

mysql> set profiling=1; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
mysql> select @@profiling; 
+-------------+ 
| @@profiling | 
+-------------+ 
|           1 | 
+-------------+ 
1 row in set, 1 warning (0.00 sec) 

mysql> select count(id) from tuser; 
ERROR 1046 (3D000): No database selected 
mysql> use kkb_2; 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 

Database changed 
mysql> select count(id) from tuser; 
+-----------+ 
| count(id) | 
+-----------+ 
| 10000000  | 
+-----------+ 
1 row in set (4.62 sec) 

mysql> show profiles;
+----------+------------+-----------------------------+ 
| Query_ID | Duration   | Query                       | 
+----------+------------+-----------------------------+ 
| 1        | 0.00016275 | select @@profiling          | 
| 2        | 0.00009200 | select count(id) from tuser | 
| 3        | 0.00014875 | SELECT DATABASE()           | 
| 4        | 0.00066875 | show databases              | 
| 5        | 0.00021050 | show tables                 |
| 6        | 4.61513875 | select count(id) from tuser | 
+----------+------------+-----------------------------+
 6 rows in set, 1 warning (0.13 sec)

mysql> show profile for query 6; 
+----------------------+----------+ 
| Status               | Duration | 
+----------------------+----------+ 
| starting             | 0.000228 | 
| checking permissions | 0.000018 | 
| Opening tables       | 0.000035 | 
| init                 | 0.000204 | 
| System lock          | 0.000071 | 
| optimizing           | 0.000013 | 
| statistics           | 0.000067 | 
| preparing            | 0.000027 | 
| executing            | 0.000004 | 
| Sending data         | 4.614239 | 
| end                  | 0.000045 | 
| query end            | 0.000009 | 
| closing tables       | 0.000026 | 
| freeing items        | 0.000019 | 
| logging slow query   | 0.000124 | 
| cleaning up          | 0.000011 | 
+----------------------+----------+ 
16 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu,block io,swaps for query 6; 
+----------------------+----------+----------+------------+--------------+- --------------+-------+ 
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out  | Swaps | 
+----------------------+----------+----------+------------+--------------+- --------------+-------+ 
| starting             | 0.000228 | 0.000361 | 0.000000   | 0            | 0              | 0     | 
| checking permissions | 0.000018 | 0.000000 | 0.000000   | 0            | 0              | 0     | 
| Opening tables       | 0.000035 | 0.000000 | 0.000000   | 0            | 0              | 0     | 
| init                 | 0.000204 | 0.000224 | 0.000000   | 0            | 0              | 0     | 
| System lock          | 0.000071 | 0.000000 | 0.000000   | 0            | 0              | 0     | 
| optimizing           | 0.000013 | 0.000000 | 0.000000   | 0            | 0              | 0     | 
| statistics           | 0.000067 | 0.000131 | 0.000000   | 0            | 0              | 0     | 
| preparing            | 0.000027 | 0.000000 | 0.000000   | 0            | 0              | 0     | 
| executing            | 0.000004 | 0.000000 | 0.000000   | 0            | 0              | 0     |
| Sending data         | 4.614239 | 3.648639 | 0.543410   | 55280        | 0              | 0     |
| end                  | 0.000045 | 0.000202 | 0.000000   | 0            | 0              | 0     |
| query end            | 0.000009 | 0.000000 | 0.000000   | 0            | 0              | 0     |
| closing tables 	   | 0.000026 | 0.000000 | 0.000000   | 0            | 0              | 0     |
| freeing items        | 0.000019 | 0.000000 | 0.000000   | 0            | 0              | 0     |
| logging slow query   | 0.000124 | 0.000155 | 0.000000   | 0            | 8              | 0     | 
| cleaning up          | 0.000011 | 0.000000 | 0.000000   | 0            | 0              | 0     |
+----------------------+----------+----------+------------+--------------+- --------------+-------+

MySQL性能优化

1、服务器层面优化

服务器硬件优化

提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。

CPU的选择:

  • 对于数据库并发比较高的场景，CPU的数量比频率重要。
  • 对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好。

CentOS系统针对mysql的参数优化

MySQL数据库配置优化

配置文件

vim /etc/my.cnf

  • 表示缓冲池字节大小。（InnoDB的内存越大，会减少磁盘交互）
    推荐值为物理内存的50%~80%。默认128M。

innodb_buffer_pool_size

  • 用来控制redo log刷新到磁盘的策略。（0性能最好，主从机制的机器，从机器是不负责写操作，所以不牵扯事务提交，所以为了从数据库的性能提升，需要将此参数设置为0）

innodb_flush_log_at_trx_commit=1

  • 每提交1次事务同步写到磁盘中，可以设置为n。（主从机制的机器，从机器是不负责写操作，所以不牵扯事务提交，也不需要再重复写binlog，所以将此处设置为0）

sync_binlog=1

  • 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。

sql innodb_max_dirty_pages_pct=30

  • 后台进程最大IO性能指标。
    默认200，如果SSD，调整为5000~20000

innodb_io_capacity=200

  • 指定innodb共享表空间文件的大小。（分磁盘，这样可以减轻磁盘的IO操作性能压力）

innodb_data_file_path

  • 慢查询日志的阈值设置，单位秒。(慢查询日志，不要随意开启，开启的时候，要合理设置阈值)

long_qurey_time=0.3

  • mysql复制的形式，row为MySQL8.0的默认形式。(statement，row，mixed)

binlog_format=row

  • 调高该参数则应降低interactive_timeout、wait_timeout的值。

max_connections=200

  • 过大，实例恢复时间长；过小，造成日志切换频繁。（默认？）

innodb_log_file_size

  • 全量日志建议关闭。
    默认关闭。

general_log=0

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

  • 设置足够大的 innodb_buffer_pool_size ，将数据读取到内存中。

建议innodb_buffer_pool_size设置为总内存大小的3/4或者4/5

  • 怎样确定 innodb_buffer_pool_size 足够大。数据是从内存读取而不是硬盘？
    在这里插入图片描述

降低磁盘写入次数

  • 对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志、慢查询日志、错误日志
  • 使用足够大的写入缓存 innodb_log_file_size

推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

  • 设置合适的innodb_flush_log_at_trx_commit，和日志落盘有关系。

2、数据库设计层面优化

  • 业务字段（预留字段）
  • 系统字段（修改人、修改时间）
  • 流程字段（状态）

具体优化方案如下：

  • 设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）
  • 为减少关联查询，创建合理的冗余字段（考虑数据库的三范式和查询性能的取舍，创建冗余字段还需要注意数据一致性问题）逆范式
  • 对于字段太多的大表，考虑拆表（比如一个表有100多个字段）(查询的时候，是以整行为单位去
    加载的。)
  • Blob Clob Text类型的字段，建议拆到另一张表中。（商品表10-15列，商品详情介绍[text]—建议将商品详情介绍拆成单独的表）
  • 对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联）
  • 每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下）。

3、SQL语句优化

索引优化

  • 为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引，不过要考虑数据的业务场景：查询多还是增删多？
  • 尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边。
  • 尽量使用覆盖索引，SELECT语句中尽量不要使用 *。
  • order by、group by语句要尽量使用到索引
    name,age—组合索引
    select * from user where name = “james” order by age ---- 使用到索引
    select * from user order by name,age ---- 使用到索引

order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。

  • 索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多的索引键值。太长的列，可以选择建立前缀索引
    比如给name列加索引，但是name列长度为300。其实最多在使用索引搜索的时候，使用到前10个字节长度。这个时候建议建立前缀索引
  • 索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。

LIMIT优化

  • 如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。
SELECT * FROM user WHERE username=’全力詹’; 
-- username没有建立唯一索引 
SELECT * FROM user WHERE username=’全力詹’ LIMIT 1;
  • 处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。 LIMIT OFFSET , SIZE；

LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。

解决方案1：使用order by 和索引覆盖

  • 原SQL（如果 film 表中的记录有10020条）：
    SELECT id,name FROM user LIMIT 10000, 20;
  • 优化的SQL：
    SELECT id,name FROM user ORDER BY id desc LIMIT 20;

解决方案2：使用子查询

  • SELECT id,name FROM (select id,name from user order by id limit 10000,20) t

解决方案3：

  • 单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写rows

其他查询优化

  • 小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数。
    explain—select列的信息，显示查询顺序。
    A表10万条记录
    B表一千万条记录
    需求：关联A表和B表去查询数据。比如结果能匹配的也就10条记录
  • 避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用导致全表扫描。在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描
  • 避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典型的场景就是数据量少的时候）尽量不使用count(*)、尽量使用count（主键）

COUNT(*)\COUNT(1)\COUNT(列)，从索引使用情况来说，是一样的。如果COUNT(非索引列)，那么MySQL会选择该表中，最小的那颗索引树，去进行统计。

COUNT(*)以行为统计，最终的结果是包含null值
COUNT(1)，会过滤NULL值
COUNT(列)，会过滤NULL

  • JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的
SELECT * FROM orders o LEFT JOIN user u on o.user_id = u.id 

orders表中的user_id和user表中的id，类型要一致
  • WHERE条件中尽量不要使用1=1、not in语句（建议使用not exists）
  • 不用 MYSQL 内置的函数，因为内置函数不会建立查询缓存。
SQL查询语句和查询结果都会在第一次查询只会存储到MySQL的查询缓存中，
如果需要获取到查询缓存中的查询结果，
查询的SQL语句必须和第一次的查询SQL语句一致。 
SELECT * FROM user where birthday = now();
  • 合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。
Miaoshuowen
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql 性能优化分析
dosthing的博客
03-13 275
文章目录Mysql 性能优化分析思考1.硬件操作系统层面2.Mysql调优2.1性能瓶颈分析与优化2.2日志分析优化2.3查询优化 [连接](https://juejin.cn/post/6844903750839058446#heading-106)2.4建表优化2.5架构优化 Mysql 性能优化分析思考 性能分析主要是从硬件操作系统和应用软件方面着手： 1.硬件操作系统层面 硬件操作系统调优对其他应用软件也是一样的，主要从网络传输，运算速度、存储性能方面入手，涉及网卡带宽吞吐效率、CPU性能、内存容量
MySQL5：性能优化
weixin_34351321的博客
11-07 161
性能优化 优化MySQL数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化，一方面是找出系统的瓶颈，提高MySQL数据库的整体性能；一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时应该尽可能节省系统资源，以便系统可以提供更大负荷的服务。 MySQL数据库优化是多方面的，有几条优化原则： 1、减少系统的瓶颈 2、减少资源的占用 3、增加系统的反应速度   ...
MySQL数据库优化的八种方式(经典必看)
热门推荐
weixin_30521161的博客
12-21 1万+
引言： 关于数据库优化，网上有不少资料和方法，但是不少质量参差不齐，有些总结的不够到位，内容冗杂 偶尔发现了这篇文章，总结得很经典，文章流量也很大，所以拿到自己的总结文集中，积累优质文章，提升个人能力，希望对大家今后开发中也有帮助 1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表...
MySQL 执行计划-select_type（查询类型）
旭东怪的博客
03-06 1618
1 select_type 序号 Column 描述 1 SIMPLE 简单查询(不使用UNION或子查询) 2 PRIMARY 主查询、外查询 3 UNION UNION中第二个语句或后面的语句 4 UNION RESULT UNION每个结果集的取出来后，进行合并操作 5 DEPENDENT SUBQUERY 子查询中第一个SELECT 6 DEPENDENT UNIO
数据库索引系列三：EXPLAIN使用详解
青鱼入云的博客
02-16 1924
explain用于查看一条sql语句如何使用索引来查询以及连接表，旨在帮助我们使用好索引和写出高性能的查询sql。 例： EXPLAIN SELECT s.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address FROM uchome_space AS s,uchome_spacefield AS f
mysql explain执行计划详解
weixin_30849403的博客
04-17 1493
1）、id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。 2）、select_type列常见的有： A：simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个 B：primary：一个需要union操作或者含有子查询的sel...
MySQL limit性能分析与优化
12-16
结论：rows 相同条件下，offset 值越大，limit 语句性能越差 二、测试 执行测试: 5750000 条数据 sql 1 执行时间: sql 6执行时间: 三、优化 方式一：可根据主键ID等其他索引字段定位到数据位置，然后使用 limit 0...
mysql介绍和性能优化.docx
09-12
性能优化是MySQL使用中的关键环节，包括但不限于以下方面： 1. 查询优化：编写高效的SQL语句，避免全表扫描，合理使用索引。 2. 索引优化：创建合适的索引，如主键索引、唯一索引和复合索引，减少查询时间。 3. 表...
mysql5.6性能优化总结
08-15
因此，性能优化成为 MySQL 数据库管理员和开发者的首要任务。本文将总结 MySQL 5.6 的性能优化方法和技术。 什么是优化？ 优化是指合理安排资源、调整系统参数，使 MySQL 运行更快、更节省资源。优化是一个多方面...
MySQL性能优化详解.docx
最新发布
03-26
MySQL语句优化是提高数据库性能的关键步骤，它包括调整参数配置、优化SQL语句和分析执行计划等。以下是一些常见的优化策略： 使用EXPLAIN分析查询：通过在查询语句前加上EXPLAIN关键字，可以了解MySQL是如何处理该...
Mysql的Explain关键字
aaaPostcard的博客
08-06 1037
explain关键字的理解。
MySQL性能分析与优化调整
07-29
MySQL性能分析与优化调整_
MySQL查询生日是今天的用户
key_artist的博客
07-06 7151
MONTH(birthday) = MONTH(NOW()) and DAY(birthday) = DAY(NOW())如题，MySQL使用函数查询生日是今天的用户
温故MySQL - 12 性能分析工具：explain执行计划~id列、table列、select_type列
你今天真好看呀
03-14 1542
文章目录1. 数据准备2. table列3. id列 1. 数据准备 // 创建表s1 CREATE TABLE s1 ( id INT AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), INDEX
MySQL 性能调优和优化技巧
allway2的博客
08-03 1万+
如果您的查询不断备份，则更好的过程是使用。如果您的内存不足，您的服务器将缓存数据存储（如硬盘）以充当内存。在示例查询中，在末尾使用通配符有助于降低查询成本，因为要经过更少的表行。MySQL 是一种流行的开源数据库应用程序，它以一种有意义且易于访问的方式存储和构造数据。与tuning-primer 一样，它会分析您的数据库配置，寻找瓶颈和低效率。日志文件建议是列表中的第一个，但如果您滚动到底部，您可以看到提高 MySQL 性能的一般建议。如果您需要这些数据，那很好，但是包含不需要的数据会浪费性能。...
java面试题
喜欢捣鼓各种技术
05-20 416
Bean的生命周期： 创建 对bean进行配置 如果实现了BeanNameAware接口，会调用setBeanName方法，参数是spring配置文件中bean的id属性值 如果实现了BanFactoryAware接口，会调用setBeanFactory方法，传入工厂自身 如果实现了BeanApplicationContext接口，会调用setApplicationContext方法，传入spring上下文 如果实现了BeanPostProcessor接口，会调用器postProcessBeforeIni
mysql union性能变差和一个方案
weixin_45642669的博客
02-18 2218
前几天，搞了一个140M大小的表格，装在本机上。主键没定义，但是默认是INDOOB，肯定是有主索引的。 有一个业务逻辑,想要查两个表（比如把男生一个表，女生一个表，查询班里的学生） 由于mysql不支持outer join，只能用unoin。 然后查询(请原谅我喜欢给表起名t1)： select * from t1; 速度很快。0.4秒。 然后我用了union命令。 select * from t1 union select * from t1; 2秒。 由于我使用的是dbeaver，所以默认是有lim
MySQL数据库优化【笔记】_Explain的使用
雪小白
07-27 573
一、了解Explain explain的含义是执行计划，使用explain关键字可以模拟优化器执行sql查询语句，用于分析查询语句或表结构的性能瓶颈。显示了mysql如何使用索引来处理select语句以及连接表，从而知道MySQL是如何处理sql语句，可以帮助选择更好的索引和写出更优化的查询语句。 使用了explain关键子可以直到下面几项内容： 1、表的读取顺序。（对应id） 2、数据读取操作的操作类型。（对应select_type） 3、哪些索引可以使用...
MySQL性能分析与优化
weixin_43954951的博客
07-04 353
MySQL性能分析与优化
MySQL5.6性能优化指南
在MySQL 5.6的性能优化中，首要目标是提高查询效率和整体系统性能。了解优化意味着需要深入理解数据库的工作原理，合理分配资源，并调整系统参数。优化工作不仅限于SQL查询，还包括数据库设计、存储引擎的选择、索引...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值