mysql的优化-系统状态查看-定位问题，分析sql，索引（1）

最新推荐文章于 2022-07-11 23:44:06 发布
最新推荐文章于 2022-07-11 23:44:06 发布
阅读量257 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/FMSCOTT/article/details/99095898
版权

系统开发的初期阶段注重功能实现，数据比较少，随着数据量的增加，性能问题会慢慢突显

查询系统状态信息

show  [sesison |global ] status;
mysqladmin extended-status

show  status like 'Com_%';
mysql> show  status like 'Com_%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Com_admin_commands          | 0     |
| Com_delete                  | 0     | 执行删除的次数，执行一次累加一次
| Com_insert                  | 0     |  执行insert的次数，执行一次累加一次，批量执行 也只累加一次
| Com_savepoint               | 0     |
| Com_select                  | 1     |  执行select的次数，执行一次增加一次
| Com_unlock_tables           | 0     |
| Com_update                  | 0     | 执行update的次数，执行一次累加一次
。。。。。。。。。。
+-----------------------------+-------+
149 rows in set (2.43 sec)

innnodb的表的操作记录

mysql> show  status like 'Innodb_row_%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
| Innodb_rows_deleted           | 0     | 执行删除操作的行数
| Innodb_rows_inserted          | 0     |执行insert的行数
| Innodb_rows_read              | 8     |  select查询返回的行数
| Innodb_rows_updated           | 0     |执行updata操作的行数
+-------------------------------+-------+
9 rows in set (0.00 sec)

连接mysql服务器的次数

mysql> show  status like 'Connections%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 5     |
+---------------+-------+
1 row in set (0.00 sec)

linux命令  uptime   服务器的工作时间

[root@localhost ~]# uptime
22:54:13 up  3:54,  3 users,  load average: 0.08, 0.02, 0.01

mysql数据库慢查询的次数

mysql> show  status like 'Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.01 sec)

打开慢查询监控，定位慢查询的sql

     41 #开启慢查询
     42 slow_query_log = 1
     43 long_query_time=2
     44 slow_query_log_file = /var/lib/mysql/slow.log

通过show  processlist;定位sql正在执行的情况

 

 

通过执行计划expalin,对慢sql进行执行计划分析

mysql> explain select  * from  complain where  complain_code='TSD20190515006';
+----+-------------+----------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys       | key                 | key_len | ref   | rows  | filtered | Extra |
+----+-------------+----------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | complain | NULL       | ref  | complain_code_index | complain_code_index | 195     | const | 54570 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
select_type类型说明
    SIMPLE:简单表查询
    PRIMARY:主查询，即外出查询
    UNION:union中的第二个或者后面的查询语句
    SUBQUERY:子查询中的第一个select

table:输出结果的表
type：mysql在表中查询所需行的方式， 访问类型
    ALL:全表扫描，遍历全表
    index:索引全扫描， 遍历整个索引
    ref:使用非唯一扫描或者唯一索引的前缀扫描
    eq_ref:类似ref,区别在于索引是使用唯一索引，对于每个索引键值，表中只有一条记录匹配
            多表中使用primary key或者 unique index 作为关联条件
    const/system 单表中最多有一个匹配行，查询速度快
    NULL:不访问表或者索引，直接能够得到结果

通过show  profile分析sql

验证profile是否打开
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (2.23 sec)


profiling默认关闭，并通过set命令打开
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  count(1) from  complain;
+----------+
| count(1) |
+----------+
|  1638400 |
+----------+
1 row in set (1.32 sec)

查询sql语句执行情况
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 2.22956925 | show tables                     |
|        2 | 0.00213900 | show databases                  |
|        3 | 0.00025525 | SELECT DATABASE()               |
|        4 | 0.00040425 | show tables                     |
|        5 | 0.00005675 | selcet  count(1) from  complain |
|        6 | 1.31990750 | select  count(1) from  complain |
+----------+------------+---------------------------------+
6 rows in set, 1 warning (0.00 sec)

查看具体sql的执行情况
mysql> show profile for query 6;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000265 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000538 |
| init                 | 0.000089 |
| System lock          | 0.000043 |
| optimizing           | 0.000021 |
| statistics           | 0.000018 |
| preparing            | 0.000043 |
| executing            | 0.000004 |
| Sending data         | 1.318605 |   mysql线程开始访问数据，并返回给客户端的时间
| end                  | 0.000014 |
| query end            | 0.000014 |
| closing tables       | 0.000030 |
| freeing items        | 0.000201 |
| cleaning up          | 0.000017 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

sending data 表示mysql线程开始访问数据行并把结果返回给客户端的时间，而不仅仅是把结果返回给客户端的时间；
sending data的状态下，mysql线程往往需要做大量的磁盘读取操作经常都是最耗时的操作


profiling 相关的信息存在了information_schema.profiling表中
select  * from  information_schema.profiling
where  query_id=1




mysql> show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000072 | 0.000000 |   0.000000 |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |
| Opening tables       | 0.000013 | 0.000000 |   0.000000 |
| init                 | 0.000026 | 0.000000 |   0.000000 |
| System lock          | 0.000007 | 0.000000 |   0.000000 |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |
| statistics           | 0.000011 | 0.000000 |   0.000000 |
| preparing            | 0.000008 | 0.000000 |   0.000000 |
| executing            | 0.000002 | 0.000000 |   0.000000 |
| Sending data         | 0.204742 | 0.203969 |   0.000000 |
| end                  | 0.000012 | 0.000000 |   0.000000 |
| query end            | 0.000009 | 0.000000 |   0.000000 |
| closing tables       | 0.000007 | 0.000000 |   0.000000 |
| freeing items        | 0.000086 | 0.001000 |   0.000000 |
| cleaning up          | 0.000016 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
15 rows in set, 1 warning (0.01 sec)


mysql> show profile all for query 1;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file          | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting             | 0.000072 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         802 |
| Opening tables       | 0.000013 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5714 |
| init                 | 0.000026 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         121 |
| System lock          | 0.000007 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc              |         323 |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         151 |
| statistics           | 0.000011 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         367 |
| preparing            | 0.000008 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         475 |
| executing            | 0.000002 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         119 |
| Sending data         | 0.204742 | 0.203969 |   0.000000 |                 0 |                   2 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         195 |
| end                  | 0.000012 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         199 |
| query end            | 0.000009 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4946 |
| closing tables       | 0.000007 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4998 |
| freeing items        | 0.000086 | 0.001000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5610 |
| cleaning up          | 0.000016 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc         |        1924 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
15 rows in set, 1 warning (0.00 sec)




mysql> show profile page faults for query 1;
+----------------------+----------+-------------------+-------------------+
| Status               | Duration | Page_faults_major | Page_faults_minor |
+----------------------+----------+-------------------+-------------------+
| starting             | 0.000072 |                 0 |                 0 |
| checking permissions | 0.000006 |                 0 |                 0 |
| Opening tables       | 0.000013 |                 0 |                 0 |
| init                 | 0.000026 |                 0 |                 0 |
| System lock          | 0.000007 |                 0 |                 0 |
| optimizing           | 0.000003 |                 0 |                 0 |
| statistics           | 0.000011 |                 0 |                 0 |
| preparing            | 0.000008 |                 0 |                 0 |
| executing            | 0.000002 |                 0 |                 0 |
| Sending data         | 0.204742 |                 0 |                 0 |
| end                  | 0.000012 |                 0 |                 0 |
| query end            | 0.000009 |                 0 |                 0 |
| closing tables       | 0.000007 |                 0 |                 0 |
| freeing items        | 0.000086 |                 0 |                 0 |
| cleaning up          | 0.000016 |                 0 |                 0 |
+----------------------+----------+-------------------+-------------------+
15 rows in set, 1 warning (0.00 sec)


mysql> show profile block io for query 1;
+----------------------+----------+--------------+---------------+
| Status               | Duration | Block_ops_in | Block_ops_out |
+----------------------+----------+--------------+---------------+
| starting             | 0.000072 |            0 |             0 |
| checking permissions | 0.000006 |            0 |             0 |
| Opening tables       | 0.000013 |            0 |             0 |
| init                 | 0.000026 |            0 |             0 |
| System lock          | 0.000007 |            0 |             0 |
| optimizing           | 0.000003 |            0 |             0 |
| statistics           | 0.000011 |            0 |             0 |
| preparing            | 0.000008 |            0 |             0 |
| executing            | 0.000002 |            0 |             0 |
| Sending data         | 0.204742 |            0 |             0 |
| end                  | 0.000012 |            0 |             0 |
| query end            | 0.000009 |            0 |             0 |
| closing tables       | 0.000007 |            0 |             0 |
| freeing items        | 0.000086 |            0 |             0 |
| cleaning up          | 0.000016 |            0 |             0 |
+----------------------+----------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

 

 

FMSCOTT
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL高级知识-查询与索引优化分析
02-20
手写手写SQL顺序机读顺序总结-SQL解析顺序SQL解析SQLJOINs七种JOIN图解实验：练习1、A、B两表共有2、A、B两表共有+A的独有3、A、B两表共有+B的独有4、A的独有5、B的独有6、AB全有MySQLFullJoin的实现因为MySQL不支持...
分析Mysql表读写、索引等操作的sql语句效率优化问题
12-16
上次我们说到mysql的一些sql查询方面的优化，包括查看explain执行计划，分析索引等等。今天我们分享一些 分析mysql表读写、索引等等操作的sql语句。 闲话不多说，直接上代码： 反映表的读写压力 SELECT file_name ...
MySQL 性能优化技巧
weixin_33757609的博客
10-07 743
原文地址：MySQL 性能优化技巧博客地址：www.extlight.com 一、背景 最近公司项目添加新功能，上线后发现有些功能的列表查询时间很久。原因是新功能用到旧功能的接口，而这些旧接口的 SQL 查询语句关联5,6张表且编写不够规范，导致 MySQL 在执行 SQL 语句时索引失效，进行全表扫描。原本负责优化的同事有事请假回家，因此优化查询数据的问题落在笔者手中。笔者在查阅网上 SQL...
mysql optimizing_MySQL：COUNT(*) profile optimizing阶段慢
weixin_34526152的博客
01-19 355
简单记录一下，以供后面分析一、问题一个朋友@问心问我为什么在optimizing 阶段会慢mysql>showprofiles;+----------+------------+----------------------------------------+|Query_ID|Duration|Query...
Mysql高级SQL语句
LI_MINGXUAN的博客
02-10 508
Mysql高级SQL语句二.SQL语句1.SELECT2.DISTINCT3.WHERE4.AND、OR5.IN6.BETWEEN7.通配符8.LIKE9.ORDER BY三.函数1.数学函数2.聚合函数3.字符串函数 二.SQL语句 1.SELECT 显示表格中一个或数个字段的所有资料 语法：SELECT 字段 FROM 表名 select region from REGION; 2.DISTINCT 不显示重复的资料（去重） 语法：SELECT DISTINCT 字段 FROM 表名 select d
Mysql数据库基本概念
LI_MINGXUAN的博客
01-27 239
Mysql数据库基础操作一.数据库的基本概念1.数据（Data）2.表3.数据库4.数据库管理系统（DBMS）5.数据库系统二.数据库系统发展史1.第一代数据库2.第二代数据库3.第三代数据库三.当今主流数据库1.SQL Server（微软公司产品）2.Oracle（甲骨文公司产品）3.DB2（IBM公司产品）4.MySQL（甲骨文公司收购）四.数据库分类1.关系数据库2.关系数据库存储结构3.关系数据库的应用4.非关系数据库5.MySQL数据库介绍6.MYSQL商业版与社区版7.MySQL产品阵营 一.数
【MySQL】总结
qq_70770395的博客
07-11 208
SELECT SQL在windows 下除了 字符串 不区分大小写MySQL 都不区分 也不区分 "" ''运算符
MYSQL专题-查询优化-使用索引-安全隐患-事务与锁.docx
最新发布
11-28
MYSQL专题-查询优化-使用索引-安全隐患-事务与锁 MYSQL专题中最重要的一环节就是查询优化，如何优化数据库查询以提高查询速度和数据库空间利用率变得尤为重要。本文将从多个方面对查询优化进行分析和讲解。 一、...
Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE
09-10
总的来说，理解覆盖索引和SQL_NO_CACHE在MySQL性能优化中的作用是至关重要的。创建合适的索引以覆盖查询所需的所有字段，可以减少磁盘I/O，提高查询速度。同时，合理使用SQL_NO_CACHE可以帮助开发者在测试和分析中...
mysql的优化-系统状态查看-定位问题，分析sql，索引(2)
FMSCOTT
08-10 317
通过trace分析sql 1打开trace 设置格式为json set optimizer_trace="enabled=on",end_markers_in_json=on; 设置trace最大使用内存 set optimizer_trace_max_mem_size=10000000; 做trace的语句 select * from complain limit 100000,...
Mysql-使用show profiles分析你的SQL
12-14
文章目录是什么如何用工具-SHOW PROCESSLIST工具-SHOW PRIFILES 是什么 慢查询：执行超过指定时间的SQL语句;分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询” 慢查询日志：MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中 long_query_time参数的默认值为10s，我们可以调整这个参数，超过这个参数值的SQL将被定义为慢查询语句
mysql优化之：show Profiles详解
hnjsjsac的博客
07-13 5223
前言 前几篇文章我们讲了什么是 MySQL 索引，explain分析SQL语句是否用到索引，以及索引的优化等一系列的文章，今天我们来讲讲Show profiles，看看SQL耗时到底出现在哪个环节。 Show profiles 是什么 是MySQL提供可以用来分析当前会话中SQL语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果 开启 profiles 查看是否开启 show variables like "%profiling%"; 开启
MySQL调优利器【show profiles】
我就是我的博客
03-01 1806
show profiles 是mysql提供可用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量。 默认情况下，参数处于关闭状态，并保存最近15次的运行结果 分析步骤 1、查看当前版本sql是否支持show profiles mysql> show variables like 'profiling%'; +------------------------+-------+ ...
MySQL调优--03---show profiles 和 show processlist
时光里的博客
12-25 1539
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录show profile show profile https://blog.csdn.net/sj349781478/article/details/78224713? https://blog.csdn.net/littleboyandgirl/article/details/68486642? https://blog.csdn.net/Baisitao_/article/details/104171800? https:/
mysql 执行sql文件_一文看完mysql数据库SQL执行分析，优化前必备分析
weixin_39775976的博客
11-30 141
概述一般我们在对mysql数据库做优化，肯定需要对慢sql去做分析才能开始优化，那么有什么分析的方法呢？下面通过对sql执行时间和执行情况来做分析。一、SQL 执行时间分析通过找到执行时间长的 SQL 语句，可以直观的发现数据层的效率问题。1.通过 show processlist 来查看系统的执行情况mysql> show processlist;2.通过 profiling 来进行查看这...
MySQL性能分析工具之PROFILE
cuilun4325的博客
03-07 145
分析SQL执行带来的开销是优化SQL的常用手段，在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。 它只能在session级别来设置，设置后影响当前session；当它开启后，后续执行的SQL语句都将记...
mysql show profile详解_SQL 性能分析利器 show profile
weixin_32868705的博客
02-07 1058
本文首发个人公众号《andyqian》, 期待你的关注～前言在之前的文章中，我们提到过一些慢SQL优化的步骤。其中就包括：使用 explain 关键字来查看执行计划，是否命中索引。通过计算某列的区分度，来判断该列是否适合新建索引。这都是一些比较好的方法。但我们还是不知道，慢SQL，到底慢在哪个环节？在每个环节中耗时是多少？根据木桶定理，我们应该找到耗时最长，性能最低，也就是最慢的环节，才能分析出慢...
常用的sql语句
一寸一寸光阴
09-24 418
from_unixtime和unix_timestamp函数使用 表 <insert id="offerClickStatistics"> insert into clickstatistics ( insuranceClickRate, offerClickRate, busDate, createTime, updatetime) values (0,1,FROM_UNIXTIM..
mysql show profiles 使用分析sql 性能
热门推荐
aeoluspu的专栏
08-01 4万+
Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。 查看一下我的数据库版本 mysql> Select  version(); +---------------------+ | version()           | +---------------------+ | 5.0.82-community-nt | +--------
MySQL优化分享.pptx
12-10
MySQL优化分享pptx介绍了MySQL优化的相关内容，包括MySQL架构分析、慢查询解决和索引分析等方面。在MySQL优化中，首先进行了MySQL架构分析，其中连接器起着重要作用。连接器主要负责用户登录数据库，进行用户的身份...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值