当前位置: 澳门新濠3559 > 数据库 > 正文

命令行执行参数查看分析的结果,如何索取有性

时间:2019-12-09 01:06来源:数据库
如何获取有性能问题的SQL 1、通过用户反馈获取存在性能问题的SQL2、通过慢查询日志获取性能问题的SQL3、实时获取存在性能问题的SQL MySQL SQL语句分析与查询优化详解,mysqlsql 如何获取

如何获取有性能问题的SQL

1、通过用户反馈获取存在性能问题的SQL
2、通过慢查询日志获取性能问题的SQL
3、实时获取存在性能问题的SQL

MySQL SQL语句分析与查询优化详解,mysqlsql

如何获取有性能问题的SQL

1、通过用户反馈获取存在性能问题的SQL
2、通过慢查询日志获取性能问题的SQL
3、实时获取存在性能问题的SQL

使用慢查询日志获取有性能问题的SQL

首先介绍下慢查询相关的参数

1、slow_query_log 启动定制记录慢查询日志
设置的方法,可以通过MySQL命令行设置set global slow_query_log=on
或者修改/etc/my.cnf文件,添加slow_query_log=on

2、slow_query_log_file 指定慢查询日志的存储路径及文件
建议日志存储和数据存储分开存储

3、long_query_time 指定记录慢查询日志SQL执行时间的阈值
① 记录所有符合条件的SQL
② 数据修改语句
③ 包括查询语句
④ 已经回滚的SQL

注意:
时间可以精确到微秒,存储的单位是秒,默认值为10秒,例如我们想查询1微秒的值,这里就要设置成0.001秒

4、log_queries_not_using_indexes 是否记录未使用索引的SQL

5、log_output 设置慢日志查询的保存格式(如果需要保存为文件请修改成FILE)

慢查询使用日志中记录的信息

澳门新濠3559 1

1、第一行记录的信息为使用sbtest做的测试
2、第二行记录的信息为慢查询日志的时间
3、第三行记录的信息为所使用锁的时间
4、第四行记录的信息为返回的数据行数
5、第五行记录的信息为扫描数据的行数
6、第六行记录的信息为时间戳
7、第七行记录的信息为查询的SQL语句

使用慢查询获取有性能问题的SQL

常使用的慢查询日志分析工具(mysqldumpslow)
介绍:汇总除查询条件外其他完全相同的SQL,并将分析结果按照参数中所指定的顺序输出

澳门新濠3559 2
澳门新濠3559 3

慢查询日志实例

慢查询的相关配置设置

澳门新濠3559 4

命令行执行参数查看分析的结果

]# cd /var/lib/mysql/log
]# mysqldumpslow -s r -t 10 slow-mysql

澳门新濠3559 5

常使用的慢查询日志分析工具(pt-query-digest)

使用工具前,需要先安装该工具,如果已有,可略过下面的安装步骤
1、perl模块
]# yum install -y perl-CPAN perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-mysql perl-Digest-MD5
2、切换至src目录下载rpm包
]#澳门新濠3559, cd /usr/local/src
]# wget

3、安装工具包
]# rpm -ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm

执行命令分析慢查询日志

]# pt-query-digest --user=root --password=redhat --host=127.0.0.1 slow-mysql > slow.rep
分析的结果如下

澳门新濠3559 6

MySQL服务器处理查询请求的整个过程

1、客户端发送SQL请求给服务器
2、服务器检查是否存在在缓存服务器中命中该SQL
3、服务器端进行SQL解析,预处理,再由优化器对应执行计划
4、根据执行计划,调用存储引擎API来查询数据
5、将结果返回给客户端

查询缓存对SQL性能的影响

1、优先检查整个查询是否命中查询缓存中的数据
2、通过一个对大小写敏感的哈希查找实现的

查询缓存的优化参数

query_cache_type 设置查询缓存是否可用
ON,OFF,DEMAND

注意:DEMAND表示只有在查询语句中使用SQL——CACHE和SQL_NO_CACHE来控制是否需要缓存

query_cache_size 设置查询缓存的内存大小

query_cache_limit 设置查询缓存可用存储的最大值

query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据(默认是关闭的,建议也是关闭的此选项)

query_cache_min_res_unit 设置查询缓存分配的内存块最小的值

会造成MySQL生成错误的执行计划的原因

1、统计信息不准确
2、执行计划中的成本估算不等同于实际的执行计划的成本
3、MySQL优化器所认为的最优可能与你所认为的最优不一样
4、MySQL从不考虑其他并发的查询,这可能会影响当前查询数据
5、MySQL有时候也会基于一些固定的规则来生成执行计划
6、MySQL不会考虑不受其控制的成本

MySQL优化器可优化的SQL类型

1、重新定义表的关联顺序
优化器会根据统计信息来决定表的关联顺序

2、将外链接转换成内连接
where条件和库表结构等

3、使用等价变换规则
(5=5 and a > 5)将会被改写成 a > 5

4、优化count(), min()和max()
select tables optimized away
优化器已经从执行计划中移除了该表,并以一个常数取而代之

5、将一个表达式转换为常数表达式

6、使用等价变换规则

7、子查询优化

8、对in()条件进行优化

如何确定查询处理各个阶段所消耗的时间

使用profile

set profiling = 1;
执行查询:
show profiles;

show profile for query N;

查询的每个阶段所消耗的时间

使用profile查看语句所消耗的时间

澳门新濠3559 7

特定的SQL查询优化

1、利用主从切换的原理进行大表的表结构修改,例如,现在从服务器上修改,修改完毕以后,进行主从切换,再在原来老的主上进行大表的修改,存在一定的风险。
2、在主服务器上创建于一个新的表,表结构就是将要修改大表后表结构,再把老表的数据重新导入到新表中,并在老表中建立一系列的触发器,把老表的数据同步更新到新表中,当老表中的数据全部同步到新表以后,再对老表加排它锁,把新表改成老表的名称,删除重命名的老表,如下图所示

澳门新濠3559 8
澳门新濠3559 9

使用pt-online-schema-change命令来修改大表,具体操作如下图所示

澳门新濠3559 10

上图的参数解释

--alter 所使用的sql语句
--user 数据库的登录用户
--password 登录用户的密码
D 指定所有修改表的数据库名称
t 表的名称
--charset 指定数据库的字符串
--excute 执行

原创作品,转载请注明出处

SQL语句分析与查询优化详解,mysqlsql 如何获取有性能问题的SQL 1、通过用户反馈获取存在性能问题的SQL 2、通过慢查询日志获取性能问题...

澳门新濠3559 11

使用慢查询日志获取有性能问题的SQL

首先介绍下慢查询相关的参数

1、slow_query_log      启动定制记录慢查询日志
    设置的方法,可以通过MySQL命令行设置set global slow_query_log=on
    或者修改/etc/my.cnf文件,添加slow_query_log=on

2、slow_query_log_file 指定慢查询日志的存储路径及文件
    建议日志存储和数据存储分开存储

3、long_query_time   指定记录慢查询日志SQL执行时间的阈值
    ① 记录所有符合条件的SQL
    ② 数据修改语句
    ③ 包括查询语句
    ④ 已经回滚的SQL

    注意:
        时间可以精确到微秒,存储的单位是秒,默认值为10秒,例如我们想查询1微秒的值,这里就要设置成0.001秒

4、log_queries_not_using_indexes 是否记录未使用索引的SQL

5、log_output 设置慢日志查询的保存格式(如果需要保存为文件请修改成FILE)

SQL语句的优化

慢查询使用日志中记录的信息

澳门新濠3559 12

1、第一行记录的信息为使用sbtest做的测试
2、第二行记录的信息为慢查询日志的时间
3、第三行记录的信息为所使用锁的时间
4、第四行记录的信息为返回的数据行数
5、第五行记录的信息为扫描数据的行数
6、第六行记录的信息为时间戳
7、第七行记录的信息为查询的SQL语句

如何索取有性能问题SQL的渠道

使用慢查询获取有性能问题的SQL

通过用户反馈获取存在性能问题的SQL

常使用的慢查询日志分析工具(mysqldumpslow)
介绍:汇总除查询条件外其他完全相同的SQL,并将分析结果按照参数中所指定的顺序输出

澳门新濠3559 13
澳门新濠3559 14

通过慢查日志获取存在性能问题的SQL

慢查询日志实例

实时获取存在性能问题的SQL

慢查询的相关配置设置

澳门新濠3559 15

命令行执行参数查看分析的结果

]# cd /var/lib/mysql/log
]# mysqldumpslow -s r -t 10 slow-mysql 

澳门新濠3559 16

慢查询日志介绍

常使用的慢查询日志分析工具(pt-query-digest)
使用工具前,需要先安装该工具,如果已有,可略过下面的安装步骤
1、perl模块
    ]# yum install -y perl-CPAN perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-mysql perl-Digest-MD5
2、切换至src目录下载rpm包
    ]# cd /usr/local/src
    ]# wget https://www.percona.com/downloads/percona-toolkit/3.0.7/binary/redhat/7/x86_64/percona-toolkit-3.0.7-1.el7.x86_64.rpm

3、安装工具包
    ]# rpm -ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm 

slow_quey_log=on 启动记录慢查询日志

执行命令分析慢查询日志
]# pt-query-digest --user=root --password=redhat --host=127.0.0.1 slow-mysql > slow.rep

slow_query_log_file 指定慢查询日志的存储路径及文件(默认情况下保存在MySQL的数据目录中)

分析的结果如下

澳门新濠3559 17

long_query_time 指定记录慢查询日志sql执行的阈值(默认为10秒,通常改为0.001秒比较合适)

MySQL服务器处理查询请求的整个过程
1、客户端发送SQL请求给服务器
2、服务器检查是否存在在缓存服务器中命中该SQL
3、服务器端进行SQL解析,预处理,再由优化器对应执行计划
4、根据执行计划,调用存储引擎API来查询数据
5、将结果返回给客户端

log_queries_not_using_indexes 是否记录未使用索引的SQL

查询缓存对SQL性能的影响
1、优先检查整个查询是否命中查询缓存中的数据
2、通过一个对大小写敏感的哈希查找实现的

set global sql_query_log=on;

查询缓存的优化参数
query_cache_type  设置查询缓存是否可用
    ON,OFF,DEMAND

    注意:DEMAND表示只有在查询语句中使用SQL——CACHE和SQL_NO_CACHE来控制是否需要缓存

query_cache_size   设置查询缓存的内存大小

query_cache_limit   设置查询缓存可用存储的最大值

query_cache_wlock_invalidate    设置数据表被锁后是否返回缓存中的数据(默认是关闭的,建议也是关闭的此选项)

query_cache_min_res_unit   设置查询缓存分配的内存块最小的值

sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=tests --mysql-user=sbtest --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/usr/local/mysql/data/mysql.sock run

会造成MySQL生成错误的执行计划的原因
1、统计信息不准确
2、执行计划中的成本估算不等同于实际的执行计划的成本
3、MySQL优化器所认为的最优可能与你所认为的最优不一样 
4、MySQL从不考虑其他并发的查询,这可能会影响当前查询数据
5、MySQL有时候也会基于一些固定的规则来生成执行计划
6、MySQL不会考虑不受其控制的成本

慢查询日志分析工具

MySQL优化器可优化的SQL类型

1、重新定义表的关联顺序
    优化器会根据统计信息来决定表的关联顺序

2、将外链接转换成内连接
    where条件和库表结构等

3、使用等价变换规则
    (5=5 and a > 5)将会被改写成 a > 5

4、优化count(), min()和max()
    select tables optimized away
    优化器已经从执行计划中移除了该表,并以一个常数取而代之

5、将一个表达式转换为常数表达式

6、使用等价变换规则

7、子查询优化

8、对in()条件进行优化

mysqldumpslow

如何确定查询处理各个阶段所消耗的时间

使用profile

    set profiling = 1;
执行查询:
    show profiles;

    show profile for query N;
    查询的每个阶段所消耗的时间

汇总除查询条件外其它完全相同的SQL并将分析结果按照参数中所指定的顺序输出

使用profile查看语句所消耗的时间

澳门新濠3559 18

mysqldumpslow -s r -t 10 slow-mysql.log

特定的SQL查询优化

1、利用主从切换的原理进行大表的表结构修改,例如,现在从服务器上修改,修改完毕以后,进行主从切换,再在原来老的主上进行大表的修改,存在一定的风险。
2、在主服务器上创建于一个新的表,表结构就是将要修改大表后表结构,再把老表的数据重新导入到新表中,并在老表中建立一系列的触发器,把老表的数据同步更新到新表中,当老表中的数据全部同步到新表以后,再对老表加排它锁,把新表改成老表的名称,删除重命名的老表,如下图所示

澳门新濠3559 19
澳门新濠3559 20

-s order(c,t,l,r,at,al,ar)[指定按照哪种排序方式输出结果]

使用pt-online-schema-change命令来修改大表,具体操作如下图所示

澳门新濠3559 21

t top[指定取前几条作为结束输出]

上图的参数解释
    --alter       所使用的sql语句
    --user        数据库的登录用户
    --password    登录用户的密码
    D             指定所有修改表的数据库名称
    t             表的名称
    --charset     指定数据库的字符串
    --excute      执行

c按照查询的次数排序

原创作品,转载请注明出处:

t按照查询的总时间排序

l按照查询中锁的时间来排序

r按照查询中返回总的数据行来排序

at、al、ar平均数量来排序

pt-query-digest

pt-query-digest

--explain h=127.0.0.1,u=root,p=p@ssWord

slow-mysql.log

pt-query-digest --explain h=127.0.0.1 slow-mysql.log > slow.rep

实时获取存在性能问题的SQL

select id,user,host,db,command,time,state,info

FROM information_schema.processlist

WHERE time>=60

查询速度为什么会这麽慢?

客户端发送SQL请求给服务器

服务器检查是否可以在查询缓存中命中该SQL

服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划

根据执行计划,调用存储引擎API来查询数据

将结果返回给客户端

》 对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率,建议大家不要使用查询缓存

2.其中涉及的参数: query_cache_type 设置查询缓存是否可用[ON,OFF,DEMAND] DEMAND表示只有在查询语句中使用了SQL_CACHE和SQL_NO_CACHE来控制是否需要进行缓存 query_cache_size 设置查询缓存的内存的大小 query_cache_limit 设置查询缓存可用的存储的最大值(加上SQL_NO_CACHE可以提高效率) query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据 query_cache_min_res_unit 设置查询缓存分配的内存块最小单位 3.MySQL依照这个执行计划和存储引擎进行交互 解析SQL,预处理。优化SQL的查询计划 语法解析阶段是通过关键字对MySQL语句进行解析,并生成一颗对应的解析树 MySQL解析器将使用MySQL语法规则验证和解析查询,包括检查语法是否使用了正确的关键走;关键字的顺序是否正确等等; 预处理阶段是根据MySQL规则进一步检查解析树是否合法 检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等等 语法检查通过了,查询优化器就可以生成查询计划了 优化器SQL的查询计划阶段对上一步所生成的执行计划进行选择基于成本模型的最优的执行计划【下面是影响选择最优的查询计划的7因素】 1.统计信息不准确 2.执行计划中的成本估算不等于实际的执行计划的成本 3.MySQL优化器认为的最优的可能与你认为最优的不一样【基于成本模型选择最优的执行计划】 4.MySQL从不考虑其他的并发的查询,这可能会影响当前查询的速度 5.MySQL有时候也会基于一些固定的规则来生成执行计划 6.MySQL不会考虑不受其控制的成本 查询优化器在目前的版本中可以进行优化的SQL的类型: 1.重新定义表的关联顺序 2.将外连接转化为内连接 3.使用等价变换规则 4.优化count和max()[select tables optimozed away] 5.将一个表达式转化为一个常数表达式 6.子查询优化 7.提前终止查询 8.对in()条件进行优化

如何确定查询处理各个阶段所消耗的时间

使用profile[不建议使用,未来mysql中将被移除]

set profiling = 1;[启动profile,这是一个session级别的配置]

执行查询

show profiles;[查看每一个查询所消耗的总的时间的信息]

show profile for query N;[查询的每个阶段所消耗的时间]

show profile cpu for query N;[查看每个阶段所消耗的时间信息和所消耗的cpu的信息]

使用performance_schema

启动所需要的监控和历史记录表的信息

update setup_instruments set enabled='yes',timed='yes' where name like 'stage%';

update setup_consumers set enabled='yes' where name like 'events%';

SELECT

a.thread_id,

sql_text,

c.event_name,

(c.timer_end - c.timer_start) / 1000000000 AS 'duration'

FROM

events_statements_history_long a

JOIN threads b on a.thread_id=b.thread_id

JOIN events_stages_history_long c ON c.thread_id=b.thread_id

AND c.event_id between a.event_id and a.end_event_id

WHERE b.processlist_id=CONNECTION_ID()

AND a.event_name='statement/sql/select'

ORDER BY a.thread_id,c.event_id

特定的SQL查询优化

大表的更新和删除

delimiter $$ use 'imooc'$$ drop procedure if exists 'p_delete_rows'$$ create definer='root'@'127.0.0.1' procedure 'p_delete_rows'() begin declare v_rows int; set v_rows int, while v_rows=1, while v_rows>0 do delete from test where id>=9000 and id<=19000 limit 5000; select row_count() into v_rows; select sleep; end while; end $$ delimiter;

如何修改大表的表结构

1.对表中的列的字段类型进行修改改变字段的宽度时还是会进行锁表

2.无法解决主从数据库延迟的问题

修改的方法:

pt-online-schema-change --alter="modify c varchar not null default''" --user=root --password=PassWord D=testDataBaseName,t=tesTableName --charset=utf-8 --execute

如何优化not in和<>查询

#原始的SQL语句 SELECT customer_id, first_name, last_name, email FROM customer WHERE customer_id NOT IN ( SELECT customer_id FROM payment ) #优化后的SQL语句 SELECT a.customer_id, a, first_name, a.last_name, a.email FROM customer a LEFT JOIN payment b ON a.customer_id = b.customer_id WHERE b.customer_id IS NULL

使用汇总表的方法进行优化

#统计商品的评论数[优化前的SQL] select count from product_comment where product_id=999; #汇总表就是提前以要统计的数据进行汇总并记录到数据库中以备后续的查询使用 create table product_comment_cnt(product_id int,cnt int); #统计商品的评论数[优化后的SQL] select sum from( select cnt from product_comment_cnt where product_id=999 union all select count from product_comment where product_id=999 and timestr>DATE a

编辑:数据库 本文来源:命令行执行参数查看分析的结果,如何索取有性

关键词: