1.大表的数据修改最好分批处理,如何索取有性能

作者: www.9159.com  发布:2019-11-30

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

www.9159.com 1

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

SQL语句的优化

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

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

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

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

解决办法:

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

1.创建一个新表。

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

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

慢查询日志介绍

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

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

4.删除老表。

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

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

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

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

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;

www.9159.com ,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

例子:

慢查询日志分析工具

select customer_id ,firstname,email from customer where customer_id

mysqldumpslow

not in (select customer_id from payment)

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

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

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

改写后的sql

-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[指定取前几条作为结束输出]

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

c按照查询的次数排序

4.对汇总表的优化查询

t按照查询的总时间排序

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

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

创建汇总表:

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

create table product_comment_cnt (product_id ,cnt int);

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

select sum(cnt) from (

pt-query-digest

select cnt from product_comment_cnt where product_id=999 union all

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

) a

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

本文由9159.com发布于www.9159.com,转载请注明出处:1.大表的数据修改最好分批处理,如何索取有性能

关键词: