澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

特定情景SQL优化,特定情景下SQL的优化

2019-09-11 作者:数据库网络   |   浏览(134)

1.大表的数据修改最好分批处理。

特定场景下SQL的优化,特定场景SQL优化

1.大表的数据修改最好分批处理。

1000万行的记录表中删除更新100万行记录,一次只删除或更新5000行数据。每批处理完成后,暂停几秒中,进行同步处理。

2.如何修改大表的表结构。

对表的列的字段类型进行修改,改变字段宽度时还是会锁表,无法解决主从数据库延迟的问题。

解决办法:

1.创建一个新表。

特定情景SQL优化,特定情景下SQL的优化。2.在老表上创建触发器同步老表数据到新表。

3.同步老表数据到新表。

4.删除老表。

5.将新表重新命名为老表。

可以使用命令,完成上面的工作:

pt-online-schema-change –alter=”modify c varchar(150) not null default ‘’” –user=root –password=root d=sakia, t=表名 –charset=utf8 –execute

3.优化not in 和 <> 的查询

例子:

select customer_id ,firstname,email from customer where customer_id

not in (select customer_id from payment)

会多次查询payment 表,如果payment表记录很多效率将很低。

改写后的sql

select a.customer_id ,a.firstname,a.email from customer a left join payment b

on a.customer_id=b.customer_id where b.customer_id is null;

4.对汇总表的优化查询

select count(1) from product_comment where product_id=999;

创建汇总表:

create table product_comment_cnt (product_id ,cnt int);

select sum(cnt) from (

select cnt from product_comment_cnt where product_id=999 union all

select count(1) from product_comment where product_id =999 and timestr>date(now())

) a

每天定时更新汇总表,再加上当天的数据。

 

1.大表的数据修改最好分批处理。 1000万行的记录表中删除更新100万行记录,一次只删除或更新5000行...

图片 1

图片 2

1000万行的记录表中删除更新100万行记录,一次只删除或更新5000行数据。每批处理完成后,暂停几秒中,进行同步处理。

SQL语句的优化

SQL语句的优化

2.如何修改大表的表结构。

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

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

对表的列的字段类型进行修改,改变字段宽度时还是会锁表,无法解决主从数据库延迟的问题。

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

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

解决办法:

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

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

1.创建一个新表。

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

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

2.在老表上创建触发器同步老表数据到新表。

慢查询日志介绍

慢查询日志介绍

3.同步老表数据到新表。

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

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

4.删除老表。

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

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

5.将新表重新命名为老表。

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

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

可以使用命令,完成上面的工作:

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

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

pt-online-schema-change –alter=”modify c varchar(150) not null default ‘’” –user=root –password=root d=sakia, t=表名 –charset=utf8 –execute

set global sql_query_log=on;

set global sql_query_log=on;

3.优化not in 和 <> 的查询

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

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

例子:

慢查询日志分析工具

特定情景SQL优化,特定情景下SQL的优化。慢查询日志分析工具

select customer_id ,firstname,email from customer where customer_id

mysqldumpslow

mysqldumpslow

not in (select customer_id from payment)

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

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

会多次查询payment 表,如果payment表记录很多效率将很低。

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

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

改写后的sql

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

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

select a.customer_id ,a.firstname,a.email from customer a left join payment b

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

c按照查询的次数排序

on a.customer_id=b.customer_id where b.customer_id is null;

c按照查询的次数排序

t按照查询的总时间排序

4.对汇总表的优化查询

t按照查询的总时间排序

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

select count(1) from product_comment where product_id=999;

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

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

创建汇总表:

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

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

create table product_comment_cnt (product_id ,cnt int);

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

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

select sum(cnt) from (

pt-query-digest

pt-query-digest

select cnt from product_comment_cnt where product_id=999 union all

pt-query-digest

pt-query-digest

select count(1) from product_comment where product_id =999 and timestr>date(now())

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

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

) a

slow-mysql.log

slow-mysql.log

每天定时更新汇总表,再加上当天的数据。

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

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

 

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

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

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

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

FROM information_schema.processlist

FROM information_schema.processlist

WHERE time>=60

WHERE time>=60

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

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

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

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

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

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

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

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

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

根据执行计划,调用存储引擎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()条件进行优化

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因素】   java学习交流群: 478052716里面有Java高级大牛直播讲解知识点 是免费的 不喜勿入

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

1.统计信息不准确  

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

2.执行计划中的成本估算不等于实际的执行计划的成本        

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

3.MySQL优化器认为的最优的可能与你认为最优的不一样【基于成本模型选择最优的执行计划】        

执行查询

4.MySQL从不考虑其他的并发的查询,这可能会影响当前查询的速度        

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

5.MySQL有时候也会基于一些固定的规则来生成执行计划        

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

6.MySQL不会考虑不受其控制的成本    

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

查询优化器在目前的版本中可以进行优化的SQL的类型:       

使用performance_schema

 1.重新定义表的关联顺序       

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

 2.将外连接转化为内连接        

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

3.使用等价变换规则        

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

4.优化count(),min()和max()[select tables optimozed away]       

SELECT

 5.将一个表达式转化为一个常数表达式        

a.thread_id,

6.子查询优化       

sql_text,

7.提前终止查询       

c.event_name,

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

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

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

FROM

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

events_statements_history_long a

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

JOIN threads b on a.thread_id=b.thread_id

执行查询

JOIN events_stages_history_long c ON c.thread_id=b.thread_id

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

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

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

WHERE b.processlist_id=CONNECTION_ID()

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

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

使用performance_schema

ORDER BY a.thread_id,c.event_id

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

特定的SQL查询优化

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

大表的更新和删除

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

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;

SELECT

如何修改大表的表结构

a.thread_id,

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

sql_text,

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

c.event_name,

修改的方法:

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

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

FROM

如何优化not in和<>查询

events_statements_history_long a

#原始的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

JOIN threads b on a.thread_id=b.thread_id

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

JOIN events_stages_history_long c ON c.thread_id=b.thread_id

#统计商品的评论数[优化前的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

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 procedureifexists'p_delete_rows'$$  create definer='root'@'127.0.0.1'procedure'p_delete_rows'()  begindeclarev_rows int;setv_rows int,whilev_rows=1,whilev_rows>0dodelete fromtestwhereid>=9000 and id<=19000limit5000;      select row_count() into v_rows;      select sleep(5);  endwhile;  end $$  delimiter;

如何修改大表的表结构

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

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

修改的方法:

pt-online-schema-change  --alter="modify c varchar(150) 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_commentwhereproduct_id=999;#汇总表就是提前以要统计的数据进行汇总并记录到数据库中以备后续的查询使用create table product_comment_cnt(product_id int,cnt int);#统计商品的评论数[优化后的SQL]select sum(cnt) from(      select cnt from product_comment_cntwhereproduct_id=999      union all      select count(*) from product_commentwhereproduct_id=999      and timestr>DATE(NOW())  ) a

  如果你在学习Java的过程中或者在工作中遇到什么问题都可以来群里提问,阿里Java高级大牛直播讲解知识点,分享知识,多年工作经验的梳理和总结,带着大家全面、科学地建立自己的技术体系和技术认知!可以加群找我要课堂链接 注意:是免费的 没有开发经验误入哦! 非喜勿入! 学习交流QQ群:478052716

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:特定情景SQL优化,特定情景下SQL的优化

关键词: