• MySQL调优学习笔记(二、MySQL调优基础)


    目录:

    • 如何优化性能
    • MySQL慢查询日志
    • 如何分析一条SQL
    • EXPLAIN执行计划

    如何优化性能

    性能优化的目标是降低响应时间,那么就需要理解为什么服务器执行查询需要这么多时间,然后减少或者消除那些对获得查询结果来说不必要的工作

    对MySQL查询进行性能剖析有两种方式(从整理到局部):

    • 剖析整个数据库服务器,这样可以分析出哪些查询是主要的压力来源。
    • 定位具体需要优化的查询后,可以对这些查询进行单独的剖析,分析哪些子任务是影响时间的主要消耗者。

    MySQL慢查询日志

    1、查看是否开启慢查询日志,1/on表示开启,0/off表示关闭。

    SHOW VARIABLES like 'slow_query_log'; 

    ———————————————————————————————————————————————————————

    2、未使用索引的查询也被记录到慢查询日志中,on表示开启,off表示关闭

    SHOW VARIABLES like 'log_queries_not_using_indexes'; 

    ———————————————————————————————————————————————————————

    3、慢查询阈值(秒级),当查询时间大于设定的阈值时,记录日志

    SHOW VARIABLES like 'long_query_time';

    ———————————————————————————————————————————————————————

    4、慢查询日志存储路径

    ———————————————————————————————————————————————————————

    SHOW VARIABLES like 'slow_query_log_file';

    关于慢查询日志的工具有官方和第三方的,官方是mysqldumpslow,但并不是很好用,所以需要更精确的分析慢查询的话大多数使用pt-query-digest。

    分析慢查询日志中SQL的统计结果:pt-query-digest /var/lib/mysql/localhost-slow.log | more

    如何分析一条SQL

    使用SHOW PROFILE命令,该命令是在MySQL5.1以后的版本中引入的,默认是禁用的,通过SET profiling = 1;命令开启。

    1、查看开启工具后的每条SQL执行总体情况:SHOW PROFILES;

    2、根据query_id查看某个查询的详细时间耗费:SHOW PROFILE FOR QUERY 1;

    3、查看cpu、IO等信息:SHOW PROFILE BLOCK IO,CPU FOR QUERY 1;

    4、查询哪些开销花费了多少时间 :SELECT state, SUM(duration) AS Total_R, ROUND(100 * SUM(duration) / (SELECT SUM(duration) FROM information_schema.profiling WHERE query_id = 1), 2) AS Pct_R, COUNT(*) as Calls,SUM(duration) /COUNT(*) AS "R/Call" FROM information_schema.profiling WHERE query_id = 1 GROUP BY state ORDER BY total_r DESC;

    EXPLAIN执行计划

    table:对应的表。

    type:连接类型(system、const、eq_ref、ref、range、index、all)。

    • ALL:全表扫描
    • index:按索引顺序进行全表扫描
    • range:按索引范围查找
    • ref:是一种索引访问,返回所有匹配某个值的行
    • eq_ref:是一种索引访问,MySQL知道最多只返回一条符合条件的记录
    • constsystem:使用常量对主键索引或唯一索引扫描

    possible_keys:可能使用的索引。

    key:实际使用的索引。

    key_len:使用索引长度。

    rows:预计扫描行数。

    Extra:解析查询的额外信息(using index、using where、using temporary、using filesort)。

    • Using index:列数据仅仅使用了索引中的信息而没有读取实际的表
    • Using where:MySQL服务器将在存储引擎检索行后,通过Where子句条件进行过滤
    • Using temporary:MYSQL需要创建一个临时表来存储结果,用于排序
    • Using filesort:MySQL将对结果进行外部排序
  • 相关阅读:
    show parameters和强制索引失效
    ALTER INDEX 始末
    ADMIN OPTION和GRANT ANY OBJECT PRIVILEGE的问题
    oracle正则表达式函数简单总结(一)
    简单说一下Native Dynamic SQL(动态sql)和包DBMS_SQL
    金山快盘的文件上传
    桶中取黑白球问题
    读书笔记:编程之美求二进制数中1的个数
    vim配置文件.vimrc
    JVM内存管理
  • 原文地址:https://www.cnblogs.com/bzfsdr/p/12313983.html
Copyright © 2020-2023  润新知