Mysql慢查询优化

分类:热门推荐日期:2024-05-28浏览:1评论:0

优化思路

  1. 分析慢查询日志，找出耗时最长的查询使用工具如mysqldumpslow和pt-query-digest进行分析
  2. 对耗时长的查询进行优化，如添加索引，修改查询条件等
  3. 对查询进行测试，验证优化效果
  4. 将优化后的查询更新到生产环境， 查看监控，监控生产环境，确认优化效果

简要命令的记录：

  1. mysqldumpslow慢查询日志的查看工具 —缺点：简陋， 它主要用的是-s order 去排序 查出慢查询

  2. pt-query-digest: 第三方工具， binlog, general log, slow log 。总之挺好的。

    1. perl脚本做的， 首先先install
    2. pt sumary进行全部统计
    3. pt-diskstats —看IO的繁忙程度 -跟top有关
    4. pt-mysql-summary —user=root —password=123456 主机的总体查看
    5. pt-slave-find 主要看主从复制的情况
    6. pt-deadlock-logger 检查死锁的
    7. pt-index-usage 分析索引的
    8. pt-duplicate-key 重复索引问题
    9. pt-ioprofile 查询io
    10. pt-config-diff 区分配置文件的区别。也是主要运维-主从的时候
    11. pt-find —xx…. —tablesize +1M 就是大于1M的表
    12. pt-kill mysql的进程， 比如干掉超过3s的 —busy-time =3s
    13. pt-show-grants 查授权的命令，主要在主从复制的时候用到

  3. 三大类有问题的SQL

    1. pt-query-digest /var/lib/mysql/查询次数多且每次查询占用时间长的sql
    2. IO 大的sql ： Rows examing这个指标比较大的
    3. 未命中的索引的sql ： 就是前面说的row sent 和 rows examing相差比较大的话

1. 分析慢查询日志，找出耗时最长的查询使用工具如mysqldumpslow和pt-query-digest进行分析

下面是举例子：

首先，我们需要安装pt-query-digest。你可以从Percona的官方网站下载Percona Toolkit，并按照说明进行安装。

然后，你可以使用以下命令来分析MySQL的慢查询日志：

pt-query-digest /path/to/your/slow-query.log

替换/path/to/your/slow-query.log为你的慢查询日志的实际路径。

运行这个命令后，pt-query-digest将会输出一个报告，其中包含了查询的数量，查询的类型（例如SELECT，INSERT，UPDATE，DELETE），查询的平均时间，以及每种查询的具体信息等内容。

此外，pt-query-digest还可以将分析结果保存到一个数据库表中，以便于后续进一步分析。你可以使用--review选项来指定一个数据库表，pt-query-digest会将分析结果保存到这个表中。例如：

pt-query-digest --review h=localhost,D=percona,t=query_review /path/to/your/slow-query.log

这个命令将会把慢查询日志的分析结果保存到percona.query_review这个表中。

注意，使用--review选项需要你有对指定数据库的写权限，并且在第一次使用前需要创建相应的表。你可以在Percona Toolkit的官方文档中找到创建这个表的SQL语句。

总的来说，pt-query-digest是一个非常强大的工具，可以帮助我们深入分析MySQL的慢查询日志，以便找出性能瓶颈并进行优化。

假设我们有一份名为slow_query.log的MySQL慢查询日志，我们可以使用mysqldumpslow工具来分析这份日志。

在命令行中，输入以下命令：

mysqldumpslow -s t -t 10 /path/to/your/slow_query.log

在这个命令中：

  • s t表示我们要按照查询时间排序结果
  • t 10表示我们只想看查询时间最长的前10个查询
  • /path/to/your/slow_query.log需要替换成你的慢查询日志文件的实际路径

    运行这个命令后，mysqldumpslow将会输出查询时间最长的前10个查询，以及这些查询的相关信息，如执行次数，查询时间，锁定时间，返回的行数等。这样我们就可以知道哪些查询可能是导致数据库性能瓶颈的原因，从而对它们进行优化。

    假设您已经使用mysqldumpslow工具生成了分析结果，它可能会显示类似如下的输出：

    Count: 1  Time=10.00s (10s)  Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
  SELECT * FROM my_table WHERE id = N
Count: 2  Time=5.00s (10s)  Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
  UPDATE my_other_table SET column = 'value' WHERE id = N

    这个结果中，"Count"代表某个查询的执行次数，"Time"表示单次查询的平均耗时（括号中的数字代表总耗时），"Lock"表示单次查询的平均锁定耗时（括号中的数字代表总锁定耗时），"Rows"表示单次查询返回的平均行数（括号中的数字代表总行数）。最后一行则显示了实际的SQL语句。

    通过分析这个结果，我们可以看出：

    1. 第一个查询（SELECT）只执行了一次，但是耗时10秒，这是相当长的时间，说明这个查询可能存在性能问题，需要进行优化。具体的优化方法可能包括添加索引、修改查询条件等。
    2. 第二个查询（UPDATE）执行了两次，每次平均耗时5秒，同样说明这个查询可能存在性能问题。我们可以进一步分析这个查询，看看是否可以通过修改更新的内容、添加索引等方式来优化。

    总的来说，通过mysqldumpslow工具，我们可以找出耗时最长的查询，然后针对这些查询进行优化，从而提高MySQL的性能。

     2. 添加索引

    优化耗时长的查询可以通过以下方法：

    1. 添加索引：索引可以大大提高查询的速度。如果你发现某个字段经常用于搜索，那么可以考虑为这个字段添加索引。
    2. 修改查询条件：有时候，查询条件可能会影响查询的速度。例如，使用LIKE操作符来进行模糊查询通常会比直接比较慢。如果可能，尝试修改查询条件或者使用其它方式进行搜索。
    3. 优化表结构：表的结构也会影响查询的速度。例如，如果一个表有太多的字段，或者字段的大小设置得过大，那么查询可能会变慢。可以考虑删除不必要的字段，或者减小字段的大小。
    4. 使用EXPLAIN：EXPLAIN是MySQL提供的一个工具，它可以显示MySQL是如何执行查询的。通过分析EXPLAIN的输出，可以找到可能的性能瓶颈，并进行优化。
    5. 分析并优化SQL语句：有时候，问题可能出在SQL语句本身。可能是因为SQL语句写得不够高效，或者使用了一些慢的操作（如全表扫描）。在这种情况下，可能需要重新写SQL语句，或者找到替代的方法来完成相同的任务。

    例如，我们有一个名为orders（订单）的表，里面存储了大量的订单数据。现在，我们的应用程序需要频繁地查询某个客户的所有订单。原始的SQL语句可能如下：

    SELECT * FROM orders WHERE customer_id = 123;

    在没有索引的情况下，MySQL需要扫描整个orders表，找出所有customer_id为123的行。如果orders表中的数据量很大，这将会是一个非常耗时的操作。

    为了解决这个问题，我们可以在customer_id字段上添加一个索引。创建索引的SQL语句如下：

    CREATE INDEX idx_customer ON orders(customer_id);

    这样，当我们再次执行查询时，MySQL可以直接通过索引找到所有customer_id为123的行，而无需扫描整个表。这将大大提高查询的速度。

    但是，也需要注意，虽然添加索引可以提高查询速度，但是它也会占用更多的存储空间，并且会在插入、更新、删除数据时降低性能。因此，在决定添加索引时，需要综合考虑查询频率、数据修改频率以及存储空间等因素。

    然后你接着去使用这个语句去查一下，有没有提高速度， 或者索引有没有命中。比如使用 explain执行计划去做：

    3. 对查询进行测试，验证优化效果

    在MySQL中，我们可以使用EXPLAIN命令来查看查询的执行计划，包括是否使用了索引，使用的是哪个索引，以及索引的使用效果如何。

    EXPLAIN的基本用法是在要查看的查询语句前面加上EXPLAIN关键字。例如：

    EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

    运行这个命令后，MySQL会输出一个表，其中包含了查询的执行计划的详细信息。下面是可能的输出：

    +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | ref  | idx_customer  | idx_customer  | 4     | const |   1 | Using index |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

    在这个结果中，"key"列显示了MySQL实际使用的索引，"possible_keys"列显示了MySQL可能使用的所有索引，"type"列显示了索引的使用方式，"rows"列显示了MySQL需要读取的行数，这个数字越小，说明索引使用得越好。

    如果"key"列的内容和我们添加的索引名相同，说明MySQL使用了我们的索引。如果"rows"列的数字相对较小，说明索引使用得比较好，如果这个数字和表的行数几乎相同，说明索引可能没有使用，或者使用效果不佳。

    总的来说，EXPLAIN命令可以帮助我们查看索引的使用情况，并进行相应的优化。如果发现索引使用效果不佳，可能需要考虑修改查询语句，或者调整索引结构。

    4. 将优化后的查询更新到生产环境， 查看监控，监控生产环境，确认优化效果

    一般线上监控会有慢查询的列表， 在部署到 B 版的时候让测试测一下性能测试，然后看看有没有出现在慢查询列表中， 就可以确定我们的这个 sql语句符不符合生产环境的标准。

    这个流程不描述了，以后会谈一下性能测试。

    PS：：课外记录 ：

    MySQL的死锁主要出现在以下几种情况：

    1. 当两个或更多的事务相互等待对方释放资源时，会发生死锁。这通常在多个事务同时锁定相同资源时发生。
    2. 尝试获取已被其他事务锁定的行的排他锁，而该事务又尝试获取已被第一个事务锁定的行的排他锁，也会出现死锁。
    3. 在并发修改多行或多表时，如果事务的执行顺序不一致，也可能会造成死锁。
    4. 此外，索引页分裂或合并也可能导致死锁。

    提供一个简单的死锁示例：

    假设有两个事务T1和T2，它们正在操作同样的两个表A和B。

    T1的操作如下：

    START TRANSACTION;
SELECT * FROM tableA WHERE id = 1 FOR UPDATE;
-- 在此处暂停T1，开始执行T2

    T2的操作如下：

    START TRANSACTION;
SELECT * FROM tableB WHERE id = 1 FOR UPDATE;
-- 在此处暂停T2，回到T1

    继续T1的操作：

    SELECT * FROM tableB WHERE id = 1 FOR UPDATE;
-- 在此处，T1需要等待T2释放对tableB的锁

    回到T2，继续其操作：

    SELECT * FROM tableA WHERE id = 1 FOR UPDATE;
-- 在此处，T2需要等待T1释放对tableA的锁

    现在，T1正在等待T2释放对tableB的锁，而T2正在等待T1释放对tableA的锁，这就形成了一个死锁。

    实际上，MySQL会自动检测到这种死锁并终止其中一个事务，从而解除死锁状态。

    FOR UPDATE是MySQL中的一种锁定读语句。在一个事务内，如果你想读取行数据并对其进行更新，你可以使用FOR UPDATE来锁定这些行。这样，其他并发事务就不能修改或删除这些行，直到你的事务完成。这有助于防止出现“脏读”或其他并发问题。

    语法如下：

    SELECT * FROM table_name WHERE condition FOR UPDATE;

    在这个查询中，table_name是你要查询的表名，而condition是用来筛选哪些行被锁定的条件。这个查询会返回所有满足条件的行，并将它们锁定以防止其他事务修改或删除。

    例如，以下查询将锁定users表中id为1的行：

    SELECT * FROM users WHERE id = 1 FOR UPDATE;

    在事务结束时（例如，提交或回滚事务），所有的锁定行都会自动解锁。

    在数据库事务中，脏读、幻读和不可重复读是常见的并发问题。

    • 脏读：这是当一个事务读取了另一个还未提交的事务中的数据时发生的。例如，事务A修改了一条数据，然后事务B读取了这条数据，但是如果事务A最后回滚了操作，那么事务B读取的数据就是不存在的，这就是脏读。
    • 不可重复读：这是指在一个事务的生命周期内，多次读取同一数据返回的结果不一致。例如，事务A读取了一条数据，然后事务B修改了这条数据并提交，如果事务A再次读取这条数据，会发现数据已经被改变，这就是不可重复读。
    • 幻读：这是当一个事务读取几行数据后，另一个并发事务插入了一些数据时，原来的事务在读取相同的数据时，会发现有新的“幻影”行。幻读主要发生在插入操作中，而不可重复读主要发生在更新操作中。

      注意，FOR UPDATE可以锁定被选中的行，防止其他事务修改或删除这些行，从而避免脏读和不可重复读。但是，要避免幻读，我们需要使用可序列化（SERIALIZABLE）的隔离级别，这是最高的隔离级别，但可能会影响数据库性能。

相关推荐

关于我们常见问题广告服务免责声明联系我们
Powered By Z-BlogPHP 1.7.3 Theme By 优美主题
Copyright Your WebSite.Some Rights Reserved. 浙ICP备2023018783号