Mysql SQL分析优化

最新推荐文章于 2024-04-28 10:15:00 发布
最新推荐文章于 2024-04-28 10:15:00 发布
阅读量285 收藏
点赞数
分类专栏： Mysql 文章标签： mysql 索引 sql优化 mysql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013246898/article/details/109360558
版权

慢SQL诱因

  1. 无索引、索引失效
  2. 锁等待
    1. InnoDB支持行锁，MyISAM支持表锁
    2. InnoDB支持行锁更适合高并发场景，但行锁有可能会升级为表锁
      1. 一种情况是在批量更新时
      2. 行锁是基于索引加的锁，如果在更新操作时，条件索引失效，那么行锁会升级为表锁
    3. 基于表锁的数据库操作，会导致SQL阻塞等待，影响执行速度
      1. 在写大于读的情况下，不建议使用MyISAM
    4. 行锁相对于表锁，虽然粒度更细，并发能力提升，但也带来了新的问题，那就是死锁
  3. 不恰当的SQL
    1. SELECT *
    2. SELECT COUNT(*)
    3. 大表中使用LIMIT M,N
    4. 对非索引字段进行排序

SQL诊断

EXPLAIN

  1. id：每个执行计划都有一个id，如果是一个联合查询，会有多个id
  2. select_type:
    1. SIMPLE：普通查询，即没有联合查询、子查询
    2. PRIMARY：主查询
    3. UNION：UNION中后面的查询
    4. SUBQUERY：子查询
  3. table：当前执行计划查询的表，如果表有别名，则显示别名
  4. partitions：分区表信息
  5. type
    1. 从表中查询到行所执行的方式
    2. 由好到坏：system > const > eq_ref > ref > range > index > ALL
    3. system/const： 表中只有一行数据匹配，根据索引查询一次就能找到对应的数据
    4. eq_ref： 使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件
    5. ref：使用非唯一索引扫描，还可见于唯一索引最左原则匹配扫描
    6. range：索引范围扫描，如<、>、between等操作
    7. index：索引全表扫描，遍历整个索引树
    8. ALL：全表扫描，遍历全表来找到对应的行
  6. possible_keys：可能使用到的索引
  7. key：实际使用到的索引
  8. key_len：当前使用的索引的长度，单位Byte
  9. ref：关联id等信息
  10. rows：查找到记录所扫描的行数
  11. filtered：查找到所需记录占总扫描记录数的比例
  12. Extra：额外信息

Show Profile

  1. 通过EXPLAIN分析执行计划，仅仅停留在分析SQL的外部执行情况
    1. 如果需要深入MySQL内核，从执行线程的状态和时间来分析，就需要选择Profile
  2. Profile除了可以分析执行线程的状态和时间
    1. 还支持查询在ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES上所消耗的时间
  3. MySQL是从5.0.37才开始支持Show Profile
  4. Show Profile只显示最新发给服务器的SQL语句，默认记录最新15条
    1. 可以设置profiling_history_size，最大值为100
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]

type参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示CPU的相关开销信息 
| IPC：接收和发送消息的相关开销信息
| MEMORY：显示内存相关的开销，目前无用
| PAGE FAULTS：显示页面错误相关开销信息
| SOURCE：列出相应操作对应的函数名及其在源码中的调用位置(行数) 
| SWAPS：显示swap交换次数的相关开销信息

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.37-log |
+------------+

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+

mysql> select @@profiling_history_size;
+--------------------------+
| @@profiling_history_size |
+--------------------------+
|                       15 |
+--------------------------+

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+

mysql> set profiling = 1;

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+

mysql> show profiles;
+----------+------------+---------------------------+
| Query_ID | Duration   | Query                     |
+----------+------------+---------------------------+
|        1 | 0.03954925 | SELECT @@profiling        |
|        2 | 0.01086300 | SELECT COUNT(1) FROM XXXX |
+----------+------------+---------------------------+

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000032 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000012 |
| init                 | 0.000009 |
| System lock          | 0.000009 |
| optimizing           | 0.000014 |
| statistics           | 0.000013 |
| preparing            | 0.000012 |
| executing            | 0.000008 |
| Sending data         | 0.010665 |
| end                  | 0.000009 |
| query end            | 0.000008 |
| closing tables       | 0.000038 |
| freeing items        | 0.000016 |
| cleaning up          | 0.000012 |
+----------------------+----------+

SQL优化

优化分页查询

  1. 经常使用LIMIT M,N+ORDER BY来实现分页查询
    1. 在没有任何索引条件支持的情况下，需要做大量的文件排序操作（file sort），性能很差
    2. 即便有对应的索引，也只是在刚开始时效率比较理想，越往后，性能越差
      1. 使用LIMIT M,N时，偏移量M越大，数据库检索的数据也会越多
      2. 例如LIMIT 10000,10，数据库需要检索10010条记录，但最后只返回10条记录
  2. 优化方案：子查询 + 覆盖索引
-- 使用了索引，扫描了100010行
mysql> explain select * from prop_action_reward order by create_time limit 100000,10;
+----+-------------+--------------------+-------+---------------+-----------------+---------+------+--------+-------+
| id | select_type | table              | type  | possible_keys | key             | key_len | ref  | rows   | Extra |
+----+-------------+--------------------+-------+---------------+-----------------+---------+------+--------+-------+
|  1 | SIMPLE      | prop_action_reward | index | NULL          | idx_create_time | 5       | NULL | 100010 | NULL  |
+----+-------------+--------------------+-------+---------------+-----------------+---------+------+--------+-------+

-- 耗费了0.19S，性能不太理想
mysql> select * from prop_action_reward order by create_time limit 100000,10;
....
10 rows in set (0.19 sec)

-- 查询获取到的100010条记录都返回给客户端了，耗时主要集中在Sending data阶段
mysql> show profile for query 21;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000037 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000016 |
| init                 | 0.000028 |
| System lock          | 0.000008 |
| optimizing           | 0.000006 |
| statistics           | 0.000010 |
| preparing            | 0.000011 |
| Sorting result       | 0.000005 |
| executing            | 0.000004 |
| Sending data         | 0.192705 |
| end                  | 0.000018 |
| query end            | 0.000008 |
| closing tables       | 0.000010 |
| freeing items        | 0.000029 |
| cleaning up          | 0.000085 |
+----------------------+----------+

-- 子查询用到了覆盖索引（Using index），无需回表
mysql> explain select * from prop_action_reward where id > (select id from prop_action_reward order by create_time limit 100000,1) limit 10;
+----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+
| id | select_type | table              | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |
+----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+
|  1 | PRIMARY     | prop_action_reward | range | PRIMARY       | PRIMARY         | 8       | NULL | 47244120 | Using where |
|  2 | SUBQUERY    | prop_action_reward | index | NULL          | idx_create_time | 5       | NULL | 94488240 | Using index |
+----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+

-- 耗费了0.03S，提升很大
mysql> select * from prop_action_reward where id > (select id from prop_action_reward order by create_time limit 100000,1) limit 10;
...
10 rows in set (0.03 sec)

-- 只会返回10条记录给客户端，所以快很多
mysql> show profile for query 24;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000064 |
| checking permissions | 0.000007 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000019 |
| init                 | 0.000030 |
| System lock          | 0.000009 |
| optimizing           | 0.000008 |
| statistics           | 0.000022 |
| optimizing           | 0.000007 |
| statistics           | 0.000011 |
| preparing            | 0.000015 |
| Sorting result       | 0.000005 |
| executing            | 0.000004 |
| Sending data         | 0.028916 |
| preparing            | 0.000013 |
| executing            | 0.000005 |
| Sending data         | 0.000055 |
| end                  | 0.000006 |
| query end            | 0.000007 |
| closing tables       | 0.000009 |
| freeing items        | 0.000022 |
| cleaning up          | 0.000013 |
+----------------------+----------+

优化SLECT COUNT(*)

  1. COUNT()是一个聚合函数，用来统计行数或某一列的行数量（不包括NULL值）
  2. 常用的是COUNT(*)和COUNT(1)，两者没有本质区别，在InnoDB，都会利用主键列实现行数的统计
  3. 通常没有任何查询条件下的COUNT(*)，MyISAM的查询速度要明显快于InnoDB
    1. 这是因为MyISAM记录了整个表的行数，无需遍历计算，直接获取即可，而InnoDB需要扫描表来统计具体的行数
    2. 如果带上查询条件，MyISAM和InnoDB都需要扫描表来进行行数的统计
  4. 优化方案：
    1. 使用近似值，借助EXPLAIN中的rows
    2. 增加汇总统计，使用汇总统计表或缓存

优化SLECT *

  1. 尽量使用覆盖索引

记录慢SQL

mysql> show variables like '%slow_query%';
+---------------------+-----------------------------------------------------+
| Variable_name       | Value                                               |
+---------------------+-----------------------------------------------------+
| slow_query_log      | ON                                                  |
| slow_query_log_file | /data_db3/mysql/3323/slowlog/slowlog_2019102209.log |
+---------------------+-----------------------------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
遥不可及梦
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySql基础知识总结（SQL优化篇）
学Java，找哪吒
07-12 4万+
1、建表语句 + 联合索引 CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `age` int(10) NOT NULL, `sex` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `phone` varchar(100) DEFAULT NULL, `create_time` timestamp .
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL慢SQL优化技术深度学习指南
最新发布
聚焦于深度解析最新的编程语言特性、框架升级、架构设计、开发工具和最佳实践，涵盖Web前端（如Vue3, React, Angular等）、后端开发（如Node.js, Java, Python等）、移动端开发（iOS, Android原生及跨平台开发）
04-28 2万+
MySQL慢SQL优化是一项系统工程，涉及查询设计、索引策略、JOIN操作、数据库配置等多个层面。通过深入理解查询执行原理，运用恰当的优化技术和工具，结合持续的性能监控与调优实践，能够有效提升MySQL数据库的查询性能，保障业务系统的高效稳定运行。
MySQL explain SQL分析工具详解与最佳实践
kerwin的博客
09-28 7060
MySQL explain SQL分析工具详解与最佳实践
MySQL高级篇（SQL优化、索引优化、锁机制、主从复制）
努力让自己发光，对的人才能迎着光而来
01-24 13万+
目录0 存储引擎介绍1 SQL性能分析2 常见通用的JOIN查询SQL执行加载顺序七种JOIN写法3 索引介绍3.1 索引是什么3.2 索引优劣势3.3 索引分类和建索引命令语句3.4 索引结构与检索原理3.5 哪些情况适合建索引3.6 哪些情况不适合建索引4 性能分析4.1 性能分析前提知识4.2 Explain使用简介4.3 执行计划包含的信息字段解释（重中之重）id（表的读取顺序）select_type（ 数据读取操作的操作类型）table（显示执行的表名）type（访问类型排列）possible_k
mysql之sql语句优化
qq_40068304的博客
11-24 1918
这篇文章从15个方面，分享了sql优化的一些小技巧，希望对你有所帮助。 1 避免使用select * 很多时候，我们写sql语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。 反例： select*fromuserwhereid=1; 在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。 此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。 还有一个最重要的问...
MYSQL篇--sql优化高频面试题
m0_37900506的博客
01-07 2664
其实对于性能比较低的sql语句定位，最重要的也是最有效的方法其实还是看sql的执行计划，而对于mysql来说 它其实也是提供了explain这样的命令可以便于查询sql的执行计划，并且通过执行计划 我们能够看到sql的执行情况，包括是否使用索引，使用了什么样的索引，以及使用索引的一些相关信息对于执行计划来说 它里面有几个非常关键的字段，比如说有，这个字段就表示是否用了索引，如果没用索引，key字段就为null；
MySQL-SQL优化
m0_53222084的博客
02-09 3212
MySQL性能调优的相关知识，包括SQL性能分析和SQL优化等。
【Mysql】SQL性能分析
Decade_Faiz的博客
02-05 973
在控制台中通过命令命令可以提供服务器状态信息。通过上述命令，我们发现当前数据库是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。如果是以增删改为主，我们可以考虑不对其进行索引的优化；如果是以查询为主，那么就要考虑对数据库的索引进行优化了。如果当前数据库是以查询为主，那么我们可以借助于慢查询日志来定位针对于哪些查询语句进行优化。
MySQL数据库性能优化由浅入深（表设计、慢查询、SQL索引优化、Explain分析、Show Profile分析、配置优化）
努力让自己发光，对的人才能迎着光而来
09-21 1万+
mysql简介
MySql Sql 优化技巧分享
09-09
MySQL SQL 优化技巧是数据库管理员和开发者提升数据库性能的关键技能。在本文中，我们将深入探讨一个具体的案例，涉及一个带有INNER JOIN操作的SQL查询优化。该查询在执行时并未达到预期的速度，但在经过一系列分析...
mysql的sql优化
04-27
在这个过程中，通过EXPLAIN命令可以查看SQL的执行计划，帮助分析优化方向。 其次，索引的合理使用至关重要。索引可以极大地提高查询速度，但也会占用存储空间并可能影响写操作的速度。B-Tree、Hash、Full-text等...
MySQL SQL语句优化的10条建议
09-10
MySQL SQL语句优化是提升数据库性能的关键步骤，尤其对于大型高并发网站，高效的SQL操作能够显著提高系统响应速度，减少服务器资源消耗。以下是对标题和描述中提到的10条SQL语句优化建议的详细解释： 1. **创建索引...
传智播客mysql的sql优化
04-27
MySQL是世界上最受欢迎的关系型数据库管理系统之一，SQL优化是提高数据库性能的关键步骤，尤其对于有大量数据和高并发访问的应用来说更是如此。传智播客的MySQL SQL优化课程针对已有数据库开发经验的开发者，旨在...
MySQL SQL语句分析与查询优化详解
09-09
MySQL SQL语句分析与查询优化是提升数据库性能的关键环节，特别是在大数据量的场景下，有效优化SQL查询可以显著提高系统响应速度，降低资源消耗。以下将详细介绍如何分析和优化SQL语句。 首先，识别存在性能问题的...
count()这么慢，我该怎么办读后总结
辛明辉的专栏
08-19 4452
计算一个表的行数语句： select count(*) from t; innodb中这条语句的查询时间会随着表记录增加而增加。 count()是实现方式 count()是在引擎层面实现的，不同的引擎有不同的实现。 1.MYISAM是将表的总行数存在磁盘上。count()会直接返回这个值，很高效，但是如果加上了where条件，也需要遍历。 2.Innodb在执行count()的时候需要一行一行从引...
MySQL 全表扫描
辛明辉的专栏
10-10 1194
Server层 -- db1.t有200GB mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file 查询数据 InnoDB的数据是保存在主键索引上，全表扫描实际上是直接扫描表t的主键索引 获取一行，写到net_buffer中，默认为16K，控制参数为net_buffer_length 重复获取行，直到写满net_buffer，然后调用网络接口发出去 如果发送成功，就清空net_buffer，然后继
为什么表数据删除一半，表文件大小不变？读后总结
辛明辉的专栏
08-16 578
现象：删除了表数据，但是表文件大小不变 针对的引擎是innodb 表数据既可以放在共享表空间里，也可以是单独的文件。由innodb_file_per_table控制。 off为将表数据放在系统共享表空间，on将表数据存储在一个.ibd为后缀的文件中。 建议是将innodb_file_per_table设置为on。 数据删除流程 innodb数据是由b+树组织的，当我们删除某一条记录，引擎只将标记该...
MySQL -- 空间回收
辛明辉的专栏
07-20 506
InnoDB的物理存储 InnoDB表的组成：表结构（frm）+数据（ibd） MySQL 8.0开始，允许将表结构定义（占用空间很小）放在系统数据表中 控制参数innodb_file_per_table ON：每个InnoDB表数据存储在一个以.ibd为后缀的文件中，推荐。更容易管理，DROP TABLE会直接删除这个文件 OFF：InnoDB表数据存储在共享表空间，DROP TABLE，空间也是不会回收的 SHOW VARIABLES LIKE '%innodb_file_per_table%
mysql sql性能优化
09-19
在进行MySQL SQL性能优化时，可以采取以下几个方法： 1. 索引优化：通过合理地创建和使用索引，可以加快查询的速度。可以考虑为经常用于查询条件的列创建索引，避免使用过多的索引以减少维护成本。 2. 优化查询...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值