欢迎进入UG环球官网(环球UG)!

usdt无需实名买卖(www.caibao.it):睡得正香,数据库突然挂了!大量慢SQL突袭怎么破

admin3周前213

USDT自动充值接口

菜宝钱包(caibao.it)是使用TRC-20协议的Usdt第三方支付平台,Usdt收款平台、Usdt自动充提平台、usdt跑分平台。免费提供入金通道、Usdt钱包支付接口、Usdt自动充值接口、Usdt无需实名寄售回收。菜宝Usdt钱包一键生成Usdt钱包、一键调用API接口、一键无实名出售Usdt。

原题目:睡得正香,数据库突然挂了!大量慢SQL突袭怎么破

这天我正在午休呢,公司DBA就把我喊醒了,说某库泛起大量慢SQL,很快啊,很快,我还没反映过来,库就挂了,我心想现在的用户不讲武德啊,怎么在我睡觉的时刻大量请求呢。

这是很常见的一个场景哈,由于许多营业最先数据量级不大,以是写SQL的时刻就没注重性能,等量级上去,许多营业就需要做调优了,在电商公司事情的这几年我也总结了不少,下面就分享给人人吧。

在代码开发历程中,我们都市遵照一些SQL开发规范去编写高质量SQL,来提高接口的Response Time(RT),对一些焦点接口要求RT在100ms以内甚至更低。

由于营业前期数据量对照小,基本都能知足这个要求,但随着营业量的增进,数据量也随之增添,对应接口的SQL耗时也在变长,直接影响了用户的体验,这时刻就需要对SQL举行优化。

优化点主要包罗SQL规范性检查,表结构索引检查,SQL优化案例剖析,下面从这三方面结合现实案例聊聊若何优化SQL。

SQL规范性检查

每个公司都有自己的MySQL开发规范,基本上大同小异,这里枚举一些对照主要的,我事情时代经常接触的给人人。

select检查

1)UDF用户自定义函数

SQL语句的select后面使用了自定义函数UDF,SQL返回若干行,那么UDF函数就会被挪用若干次,这是异常影响性能的。

#getOrderNo是用户自定义一个函数用户来凭据order_sn来获取订单编号

select id, payment_id, order_sn, getOrderNo(order_sn) from payment_transaction where status = 1 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';

2)text类型检查

若是select泛起text类型的字段,就会消耗大量的网络和IO带宽,由于返回的内容过大跨越max_allowed_packet设置会导致程序报错,需要评估郑重使用。

#表request_log的中content是text类型。

select user_id, content, status, url, type from request_log where user_id = 32121;

3)group_concat郑重使用

gorup_concat是一个字符串聚合函数,会影响SQL的响应时间,若是返回的值过大跨越了max_allowed_packet设置会导致程序报错。

select batch_id, group_concat(name) from buffer_batch where status = 0 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';

4)内联子查询

在select后面有子查询的情形称为内联子查询,SQL返回若干行,子查询就需要执行过若干次,严重影响SQL性能。

select id,(select rule_name from member_rule limit 1) as rule_name, member_id, member_type, member_name, status from member_info m where status = 1 and create_time between '2020-09-02 10:00:00' and '2020-10-01 10:00:00';

from检查

1)表的链接方式

在MySQL中不建议使用Left Join,纵然ON过滤条件列索引,一些情形也不会走索引,导致大量的数据行被扫描,SQL性能变得很差,同时要清晰ON和Where的区别。

SELECT a.member_id,a.create_time,b.active_time FROM operation_log a LEFT JOIN member_info b ON a.member_id = b.member_id where b.`status` = 1

and a.create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' limit 100, 0;

2) 子查询

由于MySQL的基于成本的优化器CBO对子查询的处置能力对照弱,不建议使用子查询,可以改写成Inner Join。

select b.member_id,b.member_type, a.create_time,a.device_model from member_operation_log a inner join (select member_id,member_type from member_base_info where `status` = 1

and create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00') as b on a.member_id = b.member_id;

where检查

1) 索引列被运算

当一个字段被索引,同时泛起where条件后面,是不能举行任何运算,会导致索引失效。

#device_no列上有索引,由于使用了ltrim函数导致索引失效

select id, name , phone, address, device_no from users where ltrim(device_no) = 'Hfs1212121';

#balance列有索引,由于做了运算导致索引失效

select account_no, balance from accounts where balance + 100 = 10000 and status = 1;

2) 类型转换

对于Int类型的字段,传varchar类型的值是可以走索引,MySQL内部自动做了隐式类型转换;相反对于varchar类型字段传入Int值是无法走索引的,应该做到对应的字段类型传对应的值总是对的。

#user_id是bigint类型,传入varchar值发生了隐式类型转换,可以走索引。select id, name , phone, address, device_no from users where user_id = '23126';

#card_no是varchar(20),传入int值是无法走索引

select id, name , phone, address, device_no from users where card_no = 2312612121;

3) 列字符集

从MySQL 5.6最先建议所有工具字符集应该使用用utf8mb4,包罗MySQL实例字符集,数据库字符集,表字符集,列字符集。制止在关联查询Join时字段字符集不匹配导致索引失效,同时现在只有utf8mb4支持emoji脸色存储。

character_set_server = utf8mb4 #数据库实例字符集

character_set_connection = utf8mb4 #毗邻字符集

character_set_database = utf8mb4 #数据库字符集

character_set_results = utf8mb4 #效果集字符集

group by检查

1)前缀索引

group by后面的列有索引,索引可以消除排序带来的CPU开销,若是是前缀索引,是不能消除排序的。

#device_no字段类型varchar(200),建立了前缀索引。

mysql> alter table users add index idx_device_no(device_no(64));

mysql> select device_no, count(*) from users where create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' group by device_no;

2)函数运算

假设需要统计某月天天的新增用户量,参考如下SQL语句,虽然可以走create_time的索引,然则不能消除排序,可以思量冗余一个字段stats_date date类型来解决这种问题

select DATE_FORMAT(create_time, '%Y-%m-%d'), count(*) from users where create_time between '2020-09-01 00:00:00' and '2020-09-30 23:59:59' group by DATE_FORMAT(create_time, '%Y-%m-%d');

order by检查

1)前缀索引

order by后面的列有索引,索引可以消除排序带来的CPU开销,若是是前缀索引,是不能消除排序的。

2)字段顺序

排序字段顺序,asc/desc升降要跟索引保持一致,充分利用索引的有序性来消除排序带来的CPU开销。

limit检查

limit m,n要稳重

对于limit m, n分页查询,越往后面翻页即m越大的情形下SQL的耗时会越来越长,对于这种应该先取出主键id,然后通过主键id跟原表举行Join关联查询。

表结构检查

表&列名关键字

在数据库设计建模阶段,对表名及字段名设置要合理,不能使用MySQL的关键字,如desc, order, status, group等。同时建议设置lower_case_table_names = 1表名不区分大小写。

表存储引擎

对于OLTP营业系统,建议使用InnoDB引擎获取更好的性能,可以通过参数default_storage_engine控制。

AUTO_INCREMENT属性

建表的时刻主键id带有AUTO_INCREMENT属性,而且AUTO_INCREMENT=1,在InnoDB内部是通过一个系统全局变量dict_sys.row_id来计数,row_id是一个8字节的bigint unsigned,InnoDB在设计时只给row_id保留了6个字节的长度,这样row_id取值局限就是0到2^48 - 1,若是id的值达到了最大值,下一个值就从0最先继续循环递增,在代码中克制指定主键id值插入。

#新插入的id值会从10001最先,这是纰谬的,应该从1最先。

create table booking( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',......) engine = InnoDB auto_increment = 10000;

#指定了id值插入,后续自增就会从该值最先+1,索引克制指定id值插入。

insert into booking(id, book_sn) values(1234551121, 'N12121');

NOT NULL属性

凭据营业寄义,只管将字段都添加上NOT NULL DEFAULT VALUE属性,若是列值存储了大量的NULL,会影响索引的稳定性。

DEFAULT属性

在建立表的时刻,建议每个字段只管都有默认值,克制DEFAULT NULL,而是对字段类型填充响应的默认值。

COMMENT属性

字段的备注要能明确该字段的作用,尤其是某些示意状态的字段,要显式的写出该字段所有可能的状态数值以及该数值的寄义。

TEXT类型

不建议使用Text数据类型,一方面由于传输大量的数据包可能会跨越max_allowed_packet设置导致程序报错,另一方面表上的DML操作都市变的很慢,建议接纳es或者工具存储OSS来存储和检索。

索引检查

索引属性

索引基数指的是被索引的列唯一值的个数,唯一值越多靠近表的count(*)说明索引的选择率越高,通过索引扫描的行数就越少,性能就越高,例如主键id的选择率是100%,在MySQL中只管所有的update都使用主键id去更新,由于id是群集索引存储着整行数据,不需要回表,性能是最高的。

mysql> select count(*) from member_info;

+----------+

| count(*) |

,

Usdt第三方支付平台

菜宝钱包(caibao.it)是使用TRC-20协议的Usdt第三方支付平台,Usdt收款平台、Usdt自动充提平台、usdt跑分平台。免费提供入金通道、Usdt钱包支付接口、Usdt自动充值接口、Usdt无需实名寄售回收。菜宝Usdt钱包一键生成Usdt钱包、一键调用API接口、一键无实名出售Usdt。

,

+----------+

| 148416 |

+----------+

1 row in set (0.35 sec)

mysql> show index from member_base_info;

+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| member_info | 0 | PRIMARY | 1 | id | A | 131088 | NULL | NULL | | BTREE | | |

| member_info | 0 | uk_member_id | 1 | member_id | A | 131824 | NULL | NULL | | BTREE | | |

| member_info | 1 | idx_create_time | 1 | create_time | A | 6770 | NULL | NULL | | BTREE | | |

+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

#Table:表名

#Non_unique :是否为unique index,0-是,1-否。

#Key_name:索引名称

#Seq_in_index:索引中的顺序号,单列索引-都是1;复合索引-凭据索引列的顺序从1最先递增。

#Column_name:索引的列名

#Collation:排序顺序,若是没有指定asc/desc,默认都是升序ASC。

#Cardinality:索引基数-索引列唯一值的个数。

#sub_part:前缀索引的长度;例如index (member_name(10),长度就是10。

#Packed:索引的组织方式,默认是NULL。

#Null:YES:索引列包罗Null值;'':索引不包罗Null值。

#Index_type:默认是BTREE,其他的值FULLTEXT,HASH,RTREE。

#Comment:在索引列中没有被形貌的信息,例如索引被禁用。

#Index_comment:建立索引时的备注。

前缀索引

对于变长字符串类型varchar(m),为了削减key_len,可以思量建立前缀索引,然则前缀索引不能消除group by, order by带来排序开销。若是字段的现实最大值比m小许多,建议缩小字段长度。

alter table member_info add index idx_member_name_part(member_name(10));

复合索引顺序

有许多人喜欢在建立复合索引的时刻,总以为前导列一定是唯一值多的列,例如索引index idx_create_time_status(create_time, status),这个索引往往是无法掷中,由于扫描的IO次数太多,总体的cost的比全表扫描还大,CBO最终的选择是走full table scan。

MySQL遵照的是索引最左匹配原则,对于复合索引,从左到右依次扫描索引列,到遇到第一个局限查询(>=, >,<, <=, between ….. and ….)就住手扫描,索引准确的索引顺序应该是index idx_status_create_time(status, create_time)。

select account_no, balance from accounts where status = 1 and create_time between '2020-09-01 00:00:00' and '2020-09-30 23:59:59';

时间列索引

对于默认字段created_at(create_time)、updated_at(update_time)这种默认就应该建立索引,这一样平常来说是默认的规则。

SQL优化案例

通过对慢查询的监控告警,经常发现一些SQL语句where过滤字段都有索引,然则由于SQL写法的问题导致索引失效,下面两个案例告诉人人若何通过SQL改写来查询。可以通过以下SQL来捞取最近5分钟的慢查询举行告警。

select CONCAT( '# Time: ', DATE_FORMAT(start_time, '%y%m%d %H%i%s'), '\n', '# User@Host: ', user_host, '\n', '# Query_time: ', TIME_TO_SEC(query_time), ' Lock_time: ', TIME_TO_SEC(lock_time), ' Rows_sent: ', rows_sent, ' Rows_examined: ', rows_examined, '\n', sql_text, ';' ) FROM mysql.slow_log where start_time between current_timestamp and date_add(CURRENT_TIMESTAMP,INTERVAL -5 MINUTE);

慢查询SQL

| 2020-10-02 19:17:23 | w_mini_user[w_mini_user] @ [10.200.20.11] | 00:00:02 | 00:00:00 | 9 | 443117 | mini_user | 0 | 0 | 168387936 | select id,club_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or applicant_id=12395) order by created_time desc limit 0,10; | 1219921665 |

从慢查询slow_log可以看到,执行时间2s,扫描了443117行,只返回了9行,这是不合理的。

SQL剖析

#原始SQL,频仍接见的接口,现在执行时间2s。

select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;

#执行计划

+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+

| 1 | SIMPLE | t_user_msg | index | invite_id,app_id,team_id | created_time | 5 | NULL | 10 | Using where |

+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+

1 row in set (0.00 sec)

从执行计划可以看到,表上有单列索引invite_id,app_id,team_id,created_time,走的是create_time的索引,而且type=index索引全扫描,由于create_time没有泛起在where条件后,只泛起在order by后面,只能是type=index,这也预示着表数据量越大该SQL越慢,我们期望是走三个单列索引invite_id,app_id,team_id,然后type=index_merge操作。

根据通例思绪,对于OR条件拆分两部门,划分举行剖析。

select id, ……. from t_user_msg where 1 and **(team_id in (3212) and app_id is not null)** order by created_time desc limit 0,10;

从执行计划看走的是team_id的索引,没有问题。

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+------+----------------------+---------+---------+-------+------+-----------------------------+

| 1 | SIMPLE | t_user_msg | ref | app_id,team_id | team_id | 8 | const | 30 | Using where; Using filesort |

再看另外一个sql语句:

select id, ……. from t_user_msg where 1 and **(invite_id=12395 or app_id=12395)** order by created_time desc limit 0,10;

从执行计划上看,划分走的是invite_id,app_id的单列索引,同时做了index_merge合并操作,也没有问题。

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------------------+

| 1 | SIMPLE | t_user_msg | index_merge | invite_id,app_id | invite_id,app_id | 9,9 | NULL | 2 | Using union(invite_id,app_id); Using where; Using filesort |

通过上面的剖析,第一部门SQL走的执行计划走team_id索引没问题,第二部门SQL划分走invite_id,app_id索引而且index_merge也没问题,为什么两部门SQL举行OR关联之后走create_time的单列索引呢,不应该是三个单列索引的index_merge吗?

index_merge默认是在优化器选项是开启的,主要是将多个局限扫描的效果聚集并成一个,可以通过变量查看。

mysql >select @@optimizer_switch;

|index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,

其他三个字段都传入的是详细的值,而且都走了响应的索引,只能嫌疑app_id is not null这个条件影响了CBO对最终执行计划的选择,去掉这个条件来看执行计划,竟然走了三个单列索引且type=index_merge,那下面只要搞定app_id is not null这个条件就OK了吧。

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+-------------+---------------------------------+---------------------------------+---------+------+------+---------------------------------------------------------------------------+

| 1 | SIMPLE | t_user_msg | index_merge | invite_id,app_id,teadm_id | team_id,invite_id,app_id | 8,9,9 | NULL | 32 | Using union(team_id,invite_id,app_id); Using where; Using filesort |

SQL改写

通过上面剖析得知,条件app_id is not null影响了CBO的选择,下面举行革新。

1) 改写优化1

凭据SQL开发规范改写,将OR改写成Union All方式即可,最终的SQL如下:

select id, ……. from (

select id, ……. from t_user_msg where **1 and (club_id in (5821) and applicant_id is not null)**

**union all** select id, ……. from t_user_msg where **1 and invitee_id='146737'**

**union all** select id, ……. from t_user_msg where **1 and app_id='146737'**

) as a order by created_time desc limit 0,10;

一样平常情形下,Java代码和SQL是离开的,SQL是设置在xml文件中,凭据营业需求,除了team_id是必填,其他两个都是可选的,以是这种改写虽然能提高SQL执行效率,但不适合这种营业场景。

2)改写优化2

app_id is not null 改写为IFNULL(app_id, 0) >0),最终的SQL为:

select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **IFNULL(app_id, 0) >0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;

3) 改写优化3

将字段app_id bigint(20) DEFAULT NULL,变更为app_id bigint(20) NOT NULL DEFAULT 0,同时更新将app_id is null的时刻所有更新成0,就可以将条件app_id is not null 转换为app_id > 0,最终的SQL为:

select id,team_id,reason,status,type,created_at,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **app_id > 0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;

从执行计划看,两种改写优化方式都走三个单列索引,执行时间从2s降低至10ms,线上接纳的是优化1的方式,若是一最先能遵照MySQL开发规范就就会制止问题的发生。

总结

上面先容了SQL规范性检查,表结构检查,索引检查以及通过SQL改写来优化查询,在编写代码的历程,若是能提前做这些规范性检查,评估出自己以为理想的执行计划,然后通过explain解析出MySQL CBO的执行计划,两者做对比剖析差异,弄清晰自己的选择和CBO的差别,不只能够编写高质量的SQL,同时也能清晰CBO的事情原理。

作者丨三太子敖丙

泉源丨民众号:三太子敖丙(ID:JavaAudition)

上一篇 下一篇

猜你喜欢

网友评论

  • 2021-03-21 00:00:15

    每天的精神食粮啊。

  • 2021-07-18 00:00:30

    赵玉学告诉总书记,已往在山上种了两亩地玉米,生涯很难题。现在伉俪二人在福建莆田打工,一年收入4万多元。3个孩子划分在上初中、小学,学费、住宿、用饭都免费。 脑洞100分

  • 2021-09-07 00:00:32

    中时新闻网提醒您:看文一直爽啊

随机文章
热门文章
热评文章
热门标签