mysql 优化sql语句的几种方法

矫情吗；* 2022-01-20 13:15 472阅读 0赞

转载：https://www.cnblogs.com/kongzhongqijing/articles/3544822.html

优化sql语句的几种方法

1、通过show status命令了解SQL的执行效率

  • show [session|global]status like ‘com_%’;

2、定位执行效率较低的SQL语句

1）慢查询

　　开启方法

复制代码

  1. linux：
  2. 配置文件（/etc/my.cnf）的[mysqld]中增加
  3. log-slow-queries=/var/log/mysql/slowquery.log(指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log)
  4. long_query_time=2(记录超过的时间，默认为10s)
  5. log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
  6. log-long-format (如果设置了，所有没有使用索引的查询也将被记录)
  8. windows:
  9. 在my.ini的[mysqld]添加如下语句：
  10. log-slow-queries =E:\web\mysql\log\mysqlslowquery.log
  11. long_query_time = 2(其他参数如上)

复制代码

　　查看慢查询方法

复制代码

  1. 使用mysql自带命令mysqldumpslow查看
  2. mysqldumpslow -s c -t 20 -g "left join" host-slow.log
  3. -s,表示按照何种方式排序，c,t,l,r分别表示按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序；
  4. -t,是top n的意思，即为返回前面多少条的数据；
  5. -g,后边可以写一个正则匹配模式，大小写不敏感的；

mysqldumpslow -s c -t 20 host-slow.log

mysqldumpslow -s r -t 20 host-slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

mysqldumpslow -t 10 -s t -g “left join” host-slow.log这个是按照时间返回前10条里面含有左连接的sql语句。

复制代码

　　查看慢查询还有一个方法即使用mysqlsla工具，大部分都使用这个方法，方法可参考 mysqlsla快速入门

慢查询开启与方法可见mysqldumpslow和mysqlsla分析mysql慢查询日志

MySQL慢日志查询全解析：从参数、配置到分析工具

2）show processlist

3、使用explain分析SQL.在 explain的帮助下，您就知道什么时候该给表添加索引，以使用索引来查找记录从而让select 运行更快。

explain [extended] select … from … where …

如果使用了extended，那么在执行完explain语句后，可以使用show warnings语句查询相应的优化信息。

复制代码

  1. 执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000 得到如下结果：
  4. 显示结果分析：
  5. table |  type | possible_keys | key |key_len  | ref | rows | Extra 
  6. EXPLAIN列的解释： 
  7. table 
  8. 显示这一行的数据是关于哪张表的 
  9. type 
  10. 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL 
  11. possible_keys 
  12. 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 
  13. key 
  14. 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引 
  15. key_len 
  16. 使用的索引的长度。在不损失精确性的情况下，长度越短越好 
  17. ref 
  18. 显示索引的哪一列被使用了，如果可能的话，是一个常数 
  19. rows 
  20. MYSQL认为必须检查的用来返回请求数据的行数 
  21. Extra 
  22. 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢 
  24. extra列返回的描述的意义 
  25. Distinct 
  26. 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了 
  27. Not exists 
  28. MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 
  29. 就不再搜索了 
  30. Range checked for each 
  31. Record（index map:#） 
  32. 没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一 
  33. Using filesort 
  34. 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 
  35. Using index 
  36. 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候 
  37. Using temporary 
  38. 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上 
  39. Where used 
  40. 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题
  42. 不同连接类型的解释（按照效率高低的顺序排序） 
  43. system 
  44. 表只有一行：system表。这是const连接类型的特殊情况 
  45. const 
  46. 表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待 
  47. eq_ref 
  48. 在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用 
  49. ref 
  50. 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好 
  51. range 
  52. 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况 
  53. index 
  54. 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据） 
  55. ALL 
  56. 这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

复制代码

可参考：mysql explain语句查询索引效率

4、性能优化器 profile

可以先使用

mysql> SELECT @@profiling;

+——————-+

| @@profiling |

+——————-+

| 0 |

+——————-+

1 row in set (0.00 sec)来查看是否已经启用profile，如果profilng值为0，可以通过

mysql> SET profiling = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@profiling;

+——————-+

| @@profiling |

+——————-+

| 1 |

+——————-+

1 row in set (0.00 sec)

来启用。启用profiling之后，我们执行一条查询语句，比如：

SELECT * FROM res_user ORDER BY modifiedtimeLIMIT 0,1000

mysql> show profiles;

+—————+——————+——————————————————————————————-+

| Query_ID | Duration | Query |

+—————+——————+——————————————————————————————-+

| 1| 0.00012200 | SELECT @@profiling |

| 2| 1.54582000 | SELECT res_id FROM res_user ORDER BY modifiedtime LIMIT 0,3 |

+—————+——————+——————————————————————————————-+

2 rows in set (0.00 sec) 注意：Query_ID表示刚执行的查询语句

mysql> show profile for query 2;

+————————————————+—————+

| Status | Duration |

+————————————————+—————+

| starting | 0.000013 |

| checking query cache for query | 0.000035 |

| Opening tables | 0.000009 |

| System lock | 0.000002 |

| Table lock | 0.000015 |

| init | 0.000011 |

| optimizing | 0.000003 |

| statistics | 0.000006 |

| preparing | 0.000006 |

| executing | 0.000001 |

| Sorting result | 1.545565 |

| Sending data | 0.000038 |

| end | 0.000003 |

| query end | 0.000003 |

| freeing items | 0.000069 |

| storing result in query cache | 0.000004 |

| logging slow query | 0.000001 |

| logging slow query | 0.000033 |

| cleaning up | 0.000003 |

+————————————————+—————+

19 rows in set (0.00 sec)

结论：可以看出此条查询语句的执行过程及执行时间，总的时间约为1.545s。这时候我们再执行一次。

mysql> SELECT res_id FROM res_user ORDERBY modifiedtime LIMIT 0,3;

+————-+

| res_id |

+————-+

| 1000305 |

| 1000322 |

| 1000323 |

+————-+

3 rows in set (0.00 sec)

mysql> show profiles;

+—————+——————+——————————————————————————————-+

| Query_ID | Duration | Query |

+—————+——————+——————————————————————————————-+

| 1 | 0.00012200 | SELECT @@profiling |

| 2 | 1.54582000 | SELECT res_id FROM res_userORDER BY modifiedtime LIMIT 0,3 |

| 3 | 0.00006500 | SELECT res_id FROMres_user ORDER BY modifiedtime LIMIT 0,3 |

+—————+——————+——————————————————————————————-+

3 rows in set (0.00 sec)

mysql> show profile for query 3;

+————————————————+—————+

| Status | Duration |

+————————————————+—————+

| starting | 0.000013 |

| checking query cache for query | 0.000005|

| checking privileges on cached | 0.000003 |

| sending cached result to clien | 0.000040|

| logging slow query | 0.000002 |

| cleaning up | 0.000002 |

+————————————————+—————+

6 rows in set (0.00 sec)

结论：可以看出此次第二次查询因为前一次的查询生成了cache，所以这次无需从数据库文件中再次读取数据而是直接从缓存中读取，结果查询时间比第一次快多了（第一次查询用了1.5秒而本次用了不到5毫秒）。

5、统计信息

表统计信息 — SHOW TABLE STATUS

索引统计信息 — SHOW INDEX from table

mysql profile explain slow_query_log分析优化查询

在做性能测试中经常会遇到一些sql的问题，其实做性能测试这几年遇到问题最多还是数据库这块，要么就是IO高要么就是cpu高，所以对数据的优化在性能测试过程中占据着很重要的地方，msyql性能调优过程中经常用到的三件利器：

1、慢查询 （分析出现问题的sql）

2、Explain （显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句）

3、Profile（查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.）

日志相关命令

是否启用了日志： show variables like ‘log_%’;

日志状态： show　master status;

日志信息： show master logs;

参考资料：

1、mysqldumpslow和mysqlsla分析mysql慢查询日志

2、mysqlsla快速入门

3、如何用一款小工具大大加速MySQL SQL语句优化(附源码)

发表评论

表情：
评论列表 （有 0 条评论，472人围观）

还没有评论，来说两句吧...

相关阅读

    相关 SQL优化的几种方法

    本文主要介绍了SQL优化的几种方法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧 1.SQL语句中IN

    约定不等于承诺〃约定不等于承诺〃/ 0 赞/ 158 阅读

    相关 Mysql 优化的几种方法

    MySQL是当前最流行的一个关系型数据库管理系统，广泛应用于各种互联网应用场景中。然而在实际的开发和运维过程中，我们经常会遇到一些性能问题，如查询慢、请求堆积等，需要通过MyS

    - 日理万妓- 日理万妓/ 0 赞/ 118 阅读

    相关 sql优化的几种方法

    在sql查询中为了提高查询效率，我们常常会采取一些措施对查询语句进行sql优化，下面总结的一些方法，有需要的可以参考参考。 1.对查询进行优化，应尽量避免全表扫描，首先

    怼烎@怼烎@/ 0 赞/ 80 阅读

    相关 SQL - SQL优化的几种方法

    在sql查询中为了提高查询效率，我们常常会采取一些措施对查询语句进行sql优化，下面总结的一些方法，有需要的可以参考参考 1、对查询进行优化，应尽量避免全表扫描，首先应考虑在

    淡淡的烟草味﹌淡淡的烟草味﹌/ 0 赞/ 207 阅读

    相关 sql优化的几种方法

    在sql查询中为了提高查询效率，我们常常会采取一些措施对查询语句进行sql优化，下面总结的一些方法，有需要的可以参考参考。 1.对查询进行优化，应尽量避免全表扫描，首

    骑猪看日落骑猪看日落/ 0 赞/ 292 阅读

    相关 SQL 优化的几种方法

    SQL 优化的几种方法 1、对查询进行优化，应尽量避免全表扫描； 2、应尽量避免在where子句中对字段进行null值判断； 3、应尽量避免在where子句中使用!=或<...

    红太狼红太狼/ 0 赞/ 949 阅读