地情网站建设方案,网站制作寻找客户,公司建站花费,成都学校网站建#x1f44f;作者简介#xff1a;大家好#xff0c;我是爱吃芝士的土豆倪#xff0c;24届校招生Java选手#xff0c;很高兴认识大家#x1f4d5;系列专栏#xff1a;Spring源码、JUC源码#x1f525;如果感觉博主的文章还不错的话#xff0c;请#x1f44d;三连支持作者简介大家好我是爱吃芝士的土豆倪24届校招生Java选手很高兴认识大家系列专栏Spring源码、JUC源码如果感觉博主的文章还不错的话请三连支持一下博主哦博主正在努力完成2023计划中源码溯源一探究竟联系方式nhs19990716加我进群大家一起学习一起进步一起对抗互联网寒冬 文章目录 常见思路15.数据库优化索引SQL没加索引索引不生效查看索引使用情况explain详解id列select_type列table列partitions列type列possible_keys列key列key_len列ref列rows列Extra列索引优化的过程 不满足最左匹配索引列上有计算索引列上有函数字段类型不同like左边包含%列对比or和in和exist和not in和not exist成本计算全表扫描成本计算 索引设计不合理索引总结 SQL优化join优化in元素过多order by文件排序order by文件排序效率为什么较低如何优化order by的文件排序 拿不到锁delete in子查询不走索引group bygroup by的执行流程group by可能会慢在哪里如何优化group by呢 select避免使用orInner join 、left join、right join在适当的时候使用覆盖索引慎用distinct关键字删除冗余和重复索引where子句中考虑使用默认值代替nullexistin的合理利用索引不适合建在有大量重复数据的字段上如性别这类型数据库字段。 深分页limit深分页为什么会变慢如何优化深分页问题? 大事务海量数据MySQL 换 ElasticSearchMySQL 换 HBase 冗余设计历史数据归档分库分表单表数据量太大一棵B树可以存多少数据量如何解决单表数据量太大查询变慢的问题多表联查问题聚合操作跨库join问题分布式事务问题Seata事务处理最终一致性事务模式 可靠消息分布式事务的选择分页问题ID主键唯一性问题 读写分离物理机 相关优秀博客 常见思路
15.数据库优化
索引
提到接口优化相信最直接的就是会想到添加索引。没错添加索引就是成本最小的优化而且一般优化效果都很不错。
索引优化这块的话一般从这几个维度去思考
你的SQL加索引了没你的索引是否真的生效你的索引建立是否合理
SQL没加索引
我们开发的时候容易疏忽而忘记给SQL添加索引。所以我们在写完SQL的时候就顺手查看一下 explain执行计划。
explain select * from user_info where userId like %123;你也可以通过命令show create table 整张表的索引情况。
show create table user_info;如果某个表忘记添加某个索引可以通过alter table add index命令添加索引
alter table user_info add index idx_name (name);一般就是SQL的where条件的字段或者是order by 、group by后面的字段需需要添加索引。
索引不生效
查看索引使用情况
在sql前面加上explain关键字就能够看到它的执行计划通过执行计划我们可以清楚的看到表和索引执行的情况索引有没有执行、索引执行顺序和索引的类型等。
explain详解
用一条简单的sql看看使用explain关键字的效果
explain select * from test1;id列
该列的值是select查询中的序号比如1、2、3、4等它决定了表的执行顺序。
某条sql的执行计划中一般会出现三种情况
id相同id不同id相同和不同都有 那么这三种情况表的执行顺序是怎么样的呢
id相同:
执行sql如下
explain select * from test1 t1 inner join test1 t2 on t1.idt2.id结果 图中我们看到执行结果中的两条数据id都是1是相同的。
这种情况表的执行顺序是怎么样的呢
答案从上到下执行先执行表t1再执行表t2。
id不同:
执行sql如下
explain select * from test1 t1 where t1.id (select id from test1 t2 where t2.id2);结果 我们看到执行结果中两条数据的id不同第一条数据是1第二条数据是2。
这种情况表的执行顺序是怎么样的呢
答案序号大的先执行这里会从下到上执行先执行表t2再执行表t1。
id相同和不同都有:
执行sql如下
explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.idt2.mid结果 我们看到执行结果中三条数据前面两条数据的的id相同第三条数据的id跟前面的不同。
这种情况表的执行顺序又是怎么样的呢
答案先执行序号大的先从下而上执行。遇到序号相同时再从上而下执行。所以这个列子中表的顺序顺序是test1、t1
select_type列
常用的其实就是下面几个 下面看看这些SELECT类型具体是怎么出现的
SIMPLE
执行sql如下
explain select * from test1;结果 它只在简单SELECT查询中出现不包含子查询和UNION这种类型比较直观就不多说了。
PRIMARY 和 SUBQUERY
explain select * from test1 t1 where t1.id (select id from test1 t2 where t2.id2);结果 我们看到这条嵌套查询的sql中最外层的t1表是PRIMARY类型而最里面的子查询t2表是SUBQUERY类型。
DERIVED
执行sql如下
explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.idt2.mid结果 最后一条记录就是衍生表它一般是FROM列表中包含的子查询这里是sql中的分组子查询。
UNION 和 UNION RESULT
执行sql如下
explain
select * from test1
union
select* from test2结果 test2表是UNION关键字之后的查询所以被标记为UNIONtest1是最主要的表被标记为PRIMARY。而union1,2表示id1和id2的表union其结果被标记为UNION RESULT。
UNION 和 UNION RESULT一般会成对出现。
此外回答上面的问题id列的值允许为空吗
如果仔细看上面那张图会发现id列是可以允许为空的并且是在SELECT类型为 UNION RESULT的时候。
table列
该列的值表示输出行所引用的表的名称比如前面的test1、test2等。
但也可以是以下值之一
unionM,N具有和id值的行的M并集N。 用于与该行的派生表结果id的值N。派生表可能来自例如FROM子句中的子查询 。 子查询的结果其id值为N
partitions列
该列的值表示查询将从中匹配记录的分区
type列
该列的值表示连接类型是查看索引执行情况的一个重要指标。
执行结果从最好到最坏的的顺序是从上到下。
我们需要重点掌握的是下面几种类型
system const eq_ref ref range index ALL
在演示之前先说明一下test2表中只有一条数据 并且code字段上面建了一个普通索引 下面逐一看看常见的几个连接类型是怎么出现的
system
这种类型要求数据库表中只有一条数据是const类型的一个特例一般情况下是不会出现的。
const
通过一次索引就能找到数据一般用于主键或唯一索引作为条件的查询sql中执行sql如下
explain select * from test2 where id1;结果 eq_ref
常用于主键或唯一索引扫描。执行sql如下
explain select * from test2 t1 inner join test2 t2 on t1.idt2.id;结果 此时有人可能感到不解const和eq_ref都是对主键或唯一索引的扫描有什么区别
答const只索引一次而eq_ref主键和主键匹配由于表中有多条数据一般情况下要索引多次才能全部匹配上。
ref
常用于非主键和唯一索引扫描。执行sql如下
explain select * from test2 where code 001;结果 range
常用于范围查询比如between … and 或 In 等操作执行sql如下
explain select * from test2 where id between 1 and 2;结果 index
全索引扫描。执行sql如下
explain select code from test2;结果 ALL
全表扫描。执行sql如下
explain select * from test2;结果 possible_keys列
explain select * from test1 t1 where t1.id (select id from test1 t2 where t2.id2);该列表示可能的索引选择。 key列
该列表示实际用到的索引。
可能会出现possible_keys列为NULL但是key不为NULL的情况。
演示之前先看看test1表结构 test1表中数据 使用的索引 code和name字段使用了联合索引。
explain select code from test1;结果 这条sql预计没有使用索引但是实际上使用了全索引扫描方式的索引。
key_len列
该列表示使用索引的长度。上面的key列可以看出有没有使用索引key_len列则可以更进一步看出索引使用是否充分。不出意外的话它是最重要的列。 有个关键的问题浮出水面key_len是如何计算的
决定key_len值的三个因素
字符集长度是否为空
常用的字符编码占用字节数量如下 目前我的数据库字符编码格式用的UTF8占3个字节。
mysql常用字段占用字节数 此外如果字段类型允许为空则加1个字节。
上图中的 184是怎么算的
184 30 * 3 2 30 * 3 2
再把test1表的code字段类型改成char并且改成允许为空 执行sql如下
explain select code from test1;结果 怎么算的 183 30 * 3 1 30 * 3 2
还有一个问题为什么这列表示索引使用是否充分呢还有使用不充分的情况
执行sql如下
explain select code from test1 where code001;结果 上图中使用了联合索引idx_code_name如果索引全匹配key_len应该是183但实际上却是92这就说明没有使用所有的索引索引使用不充分。
当EXPLAIN语句中的key_len显示使用索引不充分时意味着查询在执行时没有充分利用表的索引可能会导致以下后果
查询性能下降由于没有充分利用索引查询执行时需要扫描更多的数据行因此查询的性能可能会降低。(查询性能下降索引的主要作用是加速查询操作当查询没有充分利用索引时数据库需要扫描更多的数据行来获取所需的数据。这将导致查询的执行时间增加性能下降。对于复杂的查询或者大数据量的表来说性能下降可能会非常明显。在极端情况下如果查询始终无法充分利用索引可能会导致系统陷入繁忙状态无法及时响应其他查询和事务。)系统资源浪费当查询没有充分利用索引时可能会导致更多的系统资源被占用如CPU、内存和磁盘I/O等从而影响其他查询和事务的执行。(系统资源浪费当查询没有充分利用索引时数据库可能需要使用更多的系统资源来完成查询操作。这可能导致CPU、内存和磁盘I/O等资源的使用率上升从而影响其他查询和事务的执行。例如如果CPU使用率过高可能会导致其他查询和事务的执行被阻塞从而降低系统的并发处理能力。同样如果内存使用率过高可能会引发内存泄漏或者OOM内存溢出等异常。)增加数据库负载如果长时间存在索引不充分的情况可能会导致数据库的负载增加进一步影响数据库的稳定性和可用性。(增加数据库负载如果长时间存在索引不充分的情况可能会导致数据库的负载增加。在高负载的情况下数据库可能需要处理更多的查询和事务从而影响数据库的稳定性和可用性。此外高负载还可能导致数据库的响应时间变长进一步影响用户体验和业务处理速度。在极端情况下过高的负载可能会导致数据库崩溃从而导致数据丢失和系统崩溃。)影响业务处理由于查询性能下降可能会导致业务处理速度变慢影响用户体验甚至可能导致业务系统崩溃。因此当EXPLAIN语句中的key_len显示使用索引不充分时应尽快采取措施优化查询和索引以提高查询性能、降低系统资源消耗并保障数据库的稳定性和可用性。(影响业务处理由于查询性能下降可能会导致业务处理速度变慢影响用户体验。例如在电子商务网站上如果商品搜索查询的性能下降可能会导致用户无法快速找到所需的商品从而降低购物体验。在某些情况下如果查询性能下降严重可能会导致业务系统崩溃从而导致数据丢失和系统崩溃。因此对于关键业务系统来说确保查询充分利用索引非常重要。)
ref列
该列表示索引命中的列或者常量。
执行sql如下
explain select * from test1 t1 inner join test1 t2 on t1.idt2.id where t1.code001;结果 我们看到表t1命中的索引是const(常量)而t2命中的索引是列sue库的t1表的id字段。
rows列
该列表示MySQL认为执行查询必须检查的行数。
对于InnoDB表此数字是估计值可能并不总是准确的。
Extra列
该字段包含有关MySQL如何解析查询的其他信息这列还是挺重要的但是里面包含的值太多就不一一介绍了只列举几个常见的。
Impossible WHERE
表示WHERE后面的条件一直都是false
explain select code from test1 where a b;Using filesort
表示按文件排序一般是在指定的排序和索引排序不一致的情况才会出现。
explain select code from test1 order by name desc; 这里建立的是code和name的联合索引顺序是code在前name在后这里直接按name降序跟之前联合索引的顺序不一样。
Using index
表示是否用了覆盖索引说白了它表示是否所有获取的列都走了索引。
Using temporary
表示是否使用了临时表一般多见于order by 和 group by语句。
explain select name from test1 group by name;Using where
表示使用了where条件过滤。
索引优化的过程
先用慢查询日志定位具体需要优化的sql
使用explain执行计划查看索引使用情况
重点关注
key查看有没有使用索引key_len查看索引使用是否充分type查看索引类型Extra查看附加信息排序、临时表、where条件为false等
一般情况下根据这4列就能找到索引问题。 以一个慢sql工单举例大概是这样的 “select xxx from tabel where type 1”。
咦type字段明明有索引啊为啥是慢sql呢
通过执行explain发现实际上数据库执行了全表扫描从而被系统判定为慢sql。所以这就要分析有索引的情况索引为什么失效了。
不满足最左匹配
假如表中有个组合索引idx_start_org_code_start_province_id_trans_type,它的索引顺序如下
start_org_code,
start_province_id,
trans_type
当我们从第二个索引字段开始查询时就不会走索引 因为不满足索引的 最左匹配原则该原则只适用于 1 12 123 这种查询情况。
索引列上有计算
当我们用主键做条件时走索引了 而当id列上面有计算比如 可以看到走了全表扫描
索引列上有函数
有时候我们在某条sql语句的查询条件中需要使用函数比如截取某个字段的长度 有没有发现在使用该函数之后该sql语句竟然走了全表扫描索引失效了
字段类型不同
在sql语句中因为字段类型不同而导致索引失效的问题很容易遇到可能是我们日常工作中最容易忽略的问题。
到底怎么回事呢
我们看下表里的start_org_code字段它是varchar字符类型的
在sql语句查询数据时查询条件我们可以写成这样 从上图中看到该字段走了索引
但如果在写sql时不小心把引号丢了 咦该sql语句居然变成全表扫描了为什么索引失效了
答因为这个索引列是varchar类型而传参的类型是intmysql在比较两种不同类型的字段时会尝试把这两个转化为同一种类型再进行比较。这样就可以理解为在字段上加了函数根据上面分析索引列加了函数会索引失效。
比较有意思的是如果int类型的id字段在查询时加了引号条件却还可以走索引 从图中看出该sql语句确实走了索引。int类型的参数不管在查询时加没加引号都能走索引。
答MySQL发现如果是int类型字段作为查询条件时它会自动将该字段的传参进行隐式转换把字符串转换成int类型。
MySQL会把上面列子中的字符串12348转换成数字12348所以仍然能走索引。
事实上索引列上对字段做任何操作都会导致索引失效因为mysq认为任何计算或者函数都会改变索引的实际效果如果继续使用索引可能会造成结果不准确。
like左边包含%
答其实很好理解索引就像字典中的目录。一般目录是按字母或者拼音从小到大从左到右排序是有顺序的。
我们在查目录时通常会先从左边第一个字母进行匹对如果相同再匹对左边第二个字母如果再相同匹对其他的字母以此类推。
通过这种方式我们能快速锁定一个具体的目录或者缩小目录的范围。
但如果你硬要跟目录的设计反着来先从字典目录右边匹配第一个字母这画面你可以自行脑补一下你眼中可能只剩下绝望了哈哈
列对比
假如我们现在有这样一个需求过滤出表中某两列值相同的记录。例如 索引失效了吧惊不惊喜
答表里create_time和update_time都建了索引单独查询某一字段时都会走索引。但如果把两个单独建了索引的列用来做列对比时索引会失效。这其实和在索引列上加函数一个原理MySQL认为索引无法满足需求。
or和in和exist和not in和not exist
该部分主要参考来自 京东云开发者团队
这几个有异曲同工之处就放一起说了。这里就不像上面几种情况100%不走索引了而是有时候会走索引有时候不走索引。到底走不走成本计算说了算。
成本计算
查询优化器是 MySQL 的核心子系统之一成本计算又是查询优化器的核心逻辑。
全表扫描成本作为参照物用于和表的其它访问方式的成本做对比。任何一种访问方式只要成本超过了全表扫描成本就不会被使用。
基于全表扫描成本的重要地位要讲清楚 MySQL 的成本计算逻辑从全表扫描成本计算开始是个不错的选择。
全表扫描成本计算
我们先来看一下Mysql源码里成本计算的定义
class Cost_estimate {private:// cost of I/O operationsdouble io_cost;// cost of CPU operationsdouble cpu_cost;// cost of remote operationsdouble import_cost;// memory used (bytes)double mem_cost;......
}从上面代码可以看到MySQL 成本计算模型定义了四种成本
IO 成本从磁盘或内存读取数据页的成本。CPU 成本访问记录需要消耗的 CPU 成本。导入成本这一项一直没被使用先忽略。内存成本这一项指的是占用内存字节数计算 MRRMulti Range Read方式读取数据的成本时才会用到也先忽略。
全表扫描的成本就只剩 IO 成本、CPU 成本这两项了
计算成本
我们先从整体计算公式开始然后逐步拆解。
全表扫描成本 io_cost 1.1 cpu_cost 1
io_cost 后面的1.1是硬编码直接加到 IO 成本上的cpu_cost 后面的1也是硬编码的直接加到 CPU 成本上。代码里长这样
int test_quick_select(...) {......double scan_time cost_model-row_evaluate_cost(static_castdouble(records)) 1 /* cpu_cost 后面的 1 */; Cost_estimate cost_est table-file-table_scan_cost();// io_cost 后面的 1.1cost_est.add_io(1.1);......
}关于这两个硬编码的值代码里没有注释为什么要加不过它们是个固定值不影响我们理解成本计算逻辑先忽略它们。
io_cost cluster_page_count*avg_single_page_cost。
cluster_page_count 是主键索引数据页数量从表的统计信息中得到
avg_single_page_cost 是读取一个数据页的平均成本通过计算得到公式如下
avg_single_page_cost pages_in_memory_percent * 0.25 pages_on_disk_percent * 1.0
pages_in_memory_percent 是主键索引已经加载到 Buffer Pool中的叶结点占所有叶结点的比例用小数表示取值范围 0.0 ~ 1.0例如80% 表示为 0.8。
pages_on_disk_percent 是主键索引在磁盘文件中的叶结点占所有叶结点的比例通过1 - pages_in_memory_percent计算得到。
0.25是成本常数 memory_block_read_cost的默认值表示从 Buffer Pool 中的一个数据页读取数据的成本。
1.0是成本常数io_block_read_cost的默认值表示把磁盘文件中的一个数据页加载到 Buffer Pool 的成本加上从 Buffer Pool 中的该数据页读取数据的成本。 cpu_cost n_rows * 0.1。
n_rows 是表中记录的数量从表的统计信息中得到在统计信息小节会介绍。
0.1是成本常数row_evaluate_cost的默认值表示访问一条记录的 CPU 成本。
有了上面这些公式我们通过一个具体例子走一遍全表扫描成本计算的过程。
假设一个表有 15228 条记录主键索引数据页的数量为 739主键索引数据页已经全部加载到 Buffer Poolpages_in_memory_percent 1.0下面我们开始计算过程
pages_on_disk_percent 1 -pages_in_memory_percent(1.0) 0.0。avg_single_page_cost pages_in_memory_percent(1.0) *0.25pages_on_disk_percent(0.0) *1.00.25。io_cost cluster_page_count(739) *avg_single_page_cost(0.25) 184.75。cpu_cost n_rows(15228) * 0.1 1522.8。全表扫描成本 io_cost(184.75) 1.1cpu_cost(1522.8) 11709.55
这样其实在涉及到有索引的查询索引失效很有可能是因为成本超过了全表扫描成本就不会被使用。
索引设计不合理
我们的索引不是越多越好需要合理设计。比如 删除冗余和重复索引。 索引一般不能超过5个 索引不适合建在有大量重复数据的字段上、如性别字段 适当使用覆盖索引 如果需要使用force index强制走某个索引那就需要思考你的索引设计是否真的合理了
索引总结
查询和更新SQL必须命中索引。查询SQL如果没命中索引在访问量较大时会出现大量慢查询严重时会导致整个MySQL集群雪崩影响到其他表、其他数据库。所以一定要严格审查SQL是否命中索引。可以使用explain命令查看索引使用情况。
在SQL更新场景MySQL会在索引上加锁如果没有命中索引会对全表加锁全表的更新操作都会被阻塞住。所以更新SQL更要确保命中索引。
解释 在SQL更新场景中MySQL会在索引上加锁这是为了保证数据的一致性和并发控制。当执行更新操作时MySQL会先检查是否存在适用的索引来定位需要更新的数据行。如果存在适用的索引MySQL会在该索引上加锁只锁定需要更新的数据行其他数据行不会被阻塞。这样可以提高并发性能减少锁冲突。
然而如果更新操作没有命中索引即没有适用的索引可以定位需要更新的数据行MySQL会执行全表扫描来查找需要更新的数据行。在这种情况下MySQL会对整个表加锁这会导致全表的更新操作被阻塞住其他查询和更新操作也会受到影响。
因此为了避免这种情况的发生需要严格审查SQL是否命中索引。可以使用explain命令来查看SQL的执行计划从而判断是否有使用索引。这样可以及早发现潜在的问题并及时采取措施进行优化和调整。
除此之外最好索引字段能够完全覆盖查询需要的字段。MySQL索引分主键索引和普通索引。普通索引命中后往往需要再查询主键索引获取记录的全部字段。如果索引字段完全包含查询的字段即索引覆盖查询就无需再回查主键索引可以有效提高查询性能。
SQL优化
SQL优化是一个大的章节本质上是因为抛开索引外就sql语句本身 就有很多优化的点。
join优化
一般来说不建议使用子查询可以把子查询改成join来优化。
是因为子查询导致无法评估子查询的成本导致选择了不够高效的执行计划这可能导致查询性能下降特别是当子查询返回的结果集较大或者较复杂时。其次在某些情况下数据库优化器可能无法正确地利用索引来优化子查询的执行计划。这可能会导致查询性能下降特别是当子查询涉及到复杂的条件或者无法直接使用索引的情况下。
当你需要从两个相关的表中检索数据时可以使用 JOIN 或者子查询。以下是一个示例分别展示了使用 JOIN 和子查询来实现相同的功能
假设我们有两个表orders订单信息和 customers顾客信息它们之间通过 customer_id 进行关联。
使用 JOIN 的示例
sqlCopy CodeSELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id customers.customer_id;使用子查询的示例
sqlCopy CodeSELECT order_id, (SELECT customer_name FROM customers WHERE customer_id orders.customer_id) AS customer_name
FROM orders;在这个示例中两种方法都可以用来获取订单信息以及对应的顾客名称。然而通常情况下使用 JOIN 会更有效率因为数据库通常能够更好地优化和处理 JOIN 操作而且语句也更易读和维护。
当然并不是所有情况都适合使用 JOIN有时候子查询是更合适的选择特别是在需要处理复杂逻辑或者特定场景下。但总体来说尽量优先考虑使用 JOIN 来进行表关联操作。
数据库有个规范约定就是尽量不要有超过3个以上的表连接。为什么要这么建议呢? 我们来聊聊join哪些方面可能导致慢查询吧。
MySQL中join的执行算法分别是Index Nested-Loop Join和Block Nested-Loop Join。
Index Nested-Loop Join这个join算法跟我们写程序时的嵌套查询类似并且可以用上被驱动表的索引。Block Nested-Loop Join这种join算法被驱动表上没有可用的索引,它会先把驱动表的数据读入线程内存join_buffer中再扫描被驱动表把被驱动表的每一行取出来跟join_buffer中的数据做对比满足join条件的作为结果集的一部分返回。
join过多的问题
一方面过多的表连接会大大增加SQL复杂度。另外一方面如果可以使用被驱动表的索引那还好并且使用小表来做驱动表查询效率更佳。
如果被驱动表没有可用的索引join是在join_buffer内存做的如果匹配的数据量比较小或者join_buffer设置的比较大速度也不会太慢。但是如果join的数据量比较大时mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配这种显然效率就极低本来磁盘的 IO 就不快还要关联。
一般情况下如果业务需要的话关联2~3个表是可以接受的但是关联的字段需要加索引哈。如果需要关联更多的表建议从代码层面进行拆分在业务层先查询一张表的数据然后以关联字段作为条件查询关联表形成map然后在业务层进行数据的拼装。
in元素过多
如果使用了in即使后面的条件加了索引还是要注意in后面的元素不要过多哈。in元素一般建议不要超过500个如果超过了建议分组每次500一组进行。
反例
select user_id,name from user where user_id in (1,2,3...1000000); 如果我们对in的条件不做任何限制的话该查询语句一次性可能会查询出非常多的数据很容易导致接口超时。
主要会造成以下三个问题
性能问题当 IN 子句中包含大量元素时数据库引擎需要逐个匹配每个元素这可能会导致性能下降。数据库需要对每个元素进行比较这将增加查询的执行时间。特别是在大型数据集上这种性能下降可能会非常显著。内存消耗IN 子句中包含的元素越多数据库系统需要分配更多的内存来处理这些元素。如果内存不足可能会导致查询失败或者影响其他正在执行的查询。查询优化问题对于包含大量元素的 IN 子句数据库优化器可能无法有效地选择最佳的查询执行计划。这可能导致查询性能不佳甚至可能选择了低效的查询方式。
尤其有时候我们是用的子查询in后面的子查询你都不知道数量有多少那种更容易采坑所以我把in元素过多抽出来作为一个小节
select * from user where user_id in (select author_id from artilce where type 1);正例是分批进行每批500个
select user_id,name from user where user_id in (1,2,3...500);如果传参的ids太多还可以做个参数校验什么的
if (userIds.size() 500) {throw new Exception(单次查询的用户Id不能超过200);
}order by
order by就一定会导致慢查询吗不是这样的哈因为order by平时用得多并且数据量一上来还是走文件排序的话很容易有慢SQL的。
文件排序
我们平时经常需要用到order by 主要就是用来给某些字段排序的。比如以下SQL:
select name,age,city from staff where city 深圳 order by age limit 10;它表示的意思就是查询前10个来自深圳员工的姓名、年龄、城市并且按照年龄小到大排序。 查看explain执行计划的时候可以看到Extra这一列有一个Using filesort它表示用到文件排序。
order by文件排序效率为什么较低
order by用到文件排序时为什么查询效率会相对低呢
order by排序分为全字段排序和rowid排序。它是拿max_length_for_sort_data(系统参数)和结果行数据长度对比如果结果行数据长度超过max_length_for_sort_data (系统参数)这个值就会走rowid排序相反则走全字段排序。
max_length_for_sort_data参数的值只在运行时确定并且不允许手动更改。它的主要作用是限制排序操作所需的临时存储空间大小以避免对系统资源的过度占用。在进行大规模排序操作时如果超出了max_length_for_sort_data的限制可能会导致排序失败或者性能下降。rowid排序
rowid排序一般需要回表去找满足条件的数据所以效率会慢一点。以下这个SQL使用rowid排序执行过程是这样
select name,age,city from staff where city 深圳 order by age limit 10;MySQL 为对应的线程初始化sort_buffer放入需要排序的age字段以及主键id从索引树idx_city 找到第一个满足 city深圳’条件的主键id也就是图中的id9到主键id索引树拿到id9的这一行数据 取age和主键id的值存到sort_buffer从索引树idx_city拿到下一个记录的主键id即图中的id13重复步骤 3、4 直到city的值不等于深圳为止前面5步已经查找到了所有city为深圳的数据在sort_buffer中将所有数据根据age进行排序遍历排序结果取前10行并按照id的值回到原表中取出city、name 和 age三个字段返回给客户端。 全字段排序
同样的SQL如果是走全字段排序是这样的
select name,age,city from staff where city 深圳 order by age limit 10;MySQL 为对应的线程初始化sort_buffer放入需要查询的name、age、city字段从索引树idx_city 找到第一个满足 city深圳’条件的主键 id也就是图中的id9到主键id索引树拿到id9的这一行数据 取name、age、city三个字段的值存到sort_buffer从索引树idx_city 拿到下一个记录的主键id即图中的id13重复步骤 3、4 直到city的值不等于深圳为止前面5步已经查找到了所有city为深圳的数据在sort_buffer中将所有数据根据age进行排序按照排序结果取前10行返回给客户端。 sort_buffer的大小是由一个参数控制的sort_buffer_size。
如果要排序的数据小于sort_buffer_size排序在sort_buffer内存中完成如果要排序的数据大于sort_buffer_size则借助磁盘文件来进行排序。
借助磁盘文件排序的话效率就更慢一点。因为先把数据放入sort_buffer当快要满时。会排一下序然后把sort_buffer中的数据放到临时磁盘文件等到所有满足条件数据都查完排完再用归并算法把磁盘的临时排好序的小文件合并成一个有序的大文件。
如何优化order by的文件排序
order by使用文件排序效率会低一点。我们怎么优化呢
因为数据是无序的所以就需要排序。如果数据本身是有序的那就不会再用到文件排序啦。而索引数据本身是有序的我们通过建立索引来优化order by语句。我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化
拿不到锁
有时候我们查询一条很简单的SQL但是却等待很长的时间不见结果返回。一般这种时候就是表被锁住了或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放。 举一个生活的例子哈你和别人合租了一间房子这个房子只有一个卫生间的话。假设某一时刻你们都想去卫生间但是对方比你早了一点点。那么此时你只能等对方出来后才能进去。 delete in子查询不走索引
一个生产慢SQL问题当delete遇到in子查询时即使有索引也是不走索引的。而对应的select in子查询却可以走索引。
delete from account where name in (select name from old_account);查看执行计划发现不走索引 但是如果把delete换成select就会走索引。如下 为什么select in子查询会走索引delete in子查询却不会走索引呢
我们执行以下SQL看看
explain select * from account where name in (select name from old_account);
show WARNINGS; //可以查看优化后,最终执行的sql结果如下
select test2.account.id AS id,test2.account.name AS name,test2.account.balance AS balance,test2.account.create_time AS create_time,test2.account.update_time AS update_time from test2.account
semi join (test2.old_account)
where (test2.account.name test2.old_account.name)可以发现实际执行的时候MySQL对select in子查询做了优化把子查询改成join的方式所以可以走索引。但是很遗憾对于delete in子查询MySQL却没有对它做这个优化。
group by
group by一般用于分组统计它表达的逻辑就是根据一定的规则进行分组。日常开发中我们使用得比较频繁。如果不注意很容易产生慢SQL。
group by的执行流程
explain select city ,count(*) as num from staff group by city;Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表Extra 这个字段的Using filesort表示使用了文件排序
group by是怎么使用到临时表和排序了呢我们来看下这个SQL的执行流程
select city ,count(*) as num from staff group by city;创建内存临时表表里有两个字段city和num
全表扫描staff的记录依次取出city X的记录。
判断临时表中是否有为 cityX的行没有就插入一个记录 (X,1);如果临时表中有cityX的行就将X这一行的num值加 1
遍历完成后再根据字段city做排序得到结果集返回给客户端。这个流程的执行图如下 临时表的排序是怎样的呢
就是把需要排序的字段放到sort buffer排完就返回。在这里注意一点哈排序分全字段排序和rowid排序
如果是全字段排序需要查询返回的字段都放入sort buffer根据排序字段排完直接返回如果是rowid排序只是需要排序的字段放入sort buffer然后多一次回表操作再返回。
group by可能会慢在哪里
group by使用不当很容易就会产生慢SQL 问题。因为它既用到临时表又默认用到排序。有时候还可能用到磁盘临时表。
如果执行过程中会发现内存临时表大小到达了上限控制这个上限的参数就是tmp_table_size会把内存临时表转成磁盘临时表。如果数据量很大很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。
如何优化group by呢
从哪些方向去优化呢
方向1既然它默认会排序我们不给它排是不是就行啦。方向2既然临时表是影响group by性能的X因素我们是不是可以不用临时表
可以有这些优化方案
group by 后面的字段加索引
如何保证group by后面的字段数值一开始就是有序的呢当然就是加索引啦。
order by null 不用排序
select city ,count(*) as num from staff group by city order by null尽量只使用内存临时表
如果group by需要统计的数据不多我们可以尽量只使用内存临时表因为如果group by 的过程因为数据放不下导致用到磁盘临时表的话是比较耗时的。因此可以适当调大tmp_table_size参数来避免用到磁盘临时表。
使用SQL_BIG_RESULT优化
如果数据量实在太大怎么办呢总不能无限调大tmp_table_size吧但也不能眼睁睁看着数据先放到内存临时表随着数据插入发现到达上限再转成磁盘临时表吧这样就有点不智能啦。
因此如果预估数据量比较大我们使用SQL_BIG_RESULT 这个提示直接用磁盘临时表。MySQl优化器发现磁盘临时表是B树存储存储效率不如数组来得高。因此会直接用数组来存
select SQL_BIG_RESULT city ,count(*) as num from staff group by city;执行计划的Extra字段可以看到执行没有再使用临时表而是只有排序
select
查询SQL尽量不要使用select *而是select具体字段。只取需要的字段节省资源减少网络开销
select * 进行查询时很可能就不会使用到覆盖索引了就会造成回表查询
避免使用or
假设现在需要查询userid为1或者年龄为18岁的用户很容易有以下SQL
select * from user where userid 1 or age 18主要是因为使用or可能会使索引失效从而全表扫描
对于or没有索引的age这种情况假设它走了userId的索引但是走到age查询条件时它还得全表扫描也就是需要三步过程全表扫描索引扫描合并 如果它一开始就走全表扫描直接一遍扫描就完事。mysql是有优化器的处于效率与成本考虑遇到or条件索引可能失效看起来也合情合理。
Inner join 、left join、right join
Inner join 、left join、right join优先使用Inner join如果是left join左边表结果尽量小
Inner join 内连接在两张表进行连接查询时只保留两张表中完全匹配的结果集left join 在两张表进行连接查询时会返回左表所有的行即使在右表中没有匹配的记录。right join 在两张表进行连接查询时会返回右表所有的行即使在左表中没有匹配的记录。
在SQL查询中Inner join、Left join和Right join是三种主要的连接方式。它们的优先级并没有固定的规定但在很多情况下我们首选Inner join。原因如下
结果集较小Inner join返回的是两个表中共同拥有的记录因此结果集相对较小查询效率较高。而Left join和Right join会返回两个表中所有的记录结果集较大对于大数据量的查询性能会受到影响。准确性Inner join只返回两个表中确实存在的记录避免了可能出现的重复数据或错误数据。而Left join和Right join可能会返回一个表中的所有记录即使它们在另一个表中没有匹配的记录这可能会导致数据不一致或错误。易于理解和维护Inner join是最简单和直观的连接方式容易理解和实现。相比之下Left join和Right join在某些情况下可能需要额外的处理或解释增加了代码的复杂性和维护成本。当然这并不是说Inner join在所有情况下都是最佳选择。
根据实际需求和数据特点Left join和Right join在某些场景下可能更合适。例如当需要查询一个表中的所有记录以及对另一个表进行关联查询时可以使用Left join而在需要查询一个表中的特定记录以及对另一个表进行关联查询时可以使用Right join。总之要根据具体需求和数据特点来选择合适的连接方式。
都满足SQL需求的前提下推荐优先使用Inner join内连接如果要使用left join左边表数据结果尽量小如果有条件的尽量放到左边处理。
反例:
select * from tab1 t1 left join tab2 t2 on t1.size t2.size where t1.id 2;正例
select * from (select * from tab1 where id 2) t1 left join tab2 t2 on t1.size t2.size;在适当的时候使用覆盖索引
覆盖索引能够使得你的SQL语句不需要回表仅仅访问索引就能够得到所有需要的数据大大提高了查询效率。
慎用distinct关键字
distinct 关键字一般用来过滤重复记录以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时给查询带来优化效果。但是在字段很多的时候使用却会大大降低查询效率。
反例
SELECT DISTINCT * from user;正例
select DISTINCT name from user;理由
带distinct的语句cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时如果使用distinct数据库引擎就会对数据进行比较过滤掉重复数据然而这个比较、过滤的过程会占用系统资源cpu时间。
删除冗余和重复索引
反例
KEY idx_userId(userId)
KEY idx_userId_age(userId,age)正例:
//删除userId索引因为组合索引AB相当于创建了A和AB索引
KEY idx_userId_age(userId,age)理由
重复的索引需要维护并且优化器在优化查询的时候也需要逐个地进行考虑这会影响性能的。
where子句中考虑使用默认值代替null
select * from user where age is not null正例
//设置0为默认值
select * from user where age0;理由
并不是说使用了is null 或者 is not null 就会不走索引了这个跟mysql版本以及查询成本都有关。
如果mysql优化器发现走索引比不走索引成本还要高肯定会放弃索引这些条件 isnullisnotnull经常被认为让索引失效其实是因为一般情况下查询的成本高优化器自动放弃索引的。
如果把null值换成默认值很多时候让走索引成为可能同时表达意思会相对清晰一点。
existin的合理利用
假设表A表示某企业的员工表表B表示部门表查询所有部门的所有员工很容易有以下SQL:
select * from A where deptId in (select deptId from B);这样写等价于
先查询部门表B
select deptId from B
再由部门deptId查询A的员工
select * from A where A.deptId B.deptId可以抽象成这样的一个循环
List resultSet;
for(int i 0; i B.length ; i){for(int j 0; j A.length ; j){if(A[i].id B[j].id){resultSet.add(A[i]);break;}}
}显然除了使用in我们也可以用exists实现一样的查询功能如下
select * from A where exists (select 1 from B where A.deptId B.deptId);因为exists查询的理解就是先执行主查询获得数据后再放到子查询中做条件验证根据验证结果true或者false来决定主查询的数据结果是否得意保留。
那么这样写就等价于
select * from A,先从A表做循环
select * from B where A.deptId B.deptId,再从B表做循环.同理可以抽象成这样一个循环
List resultSet;
for(int i 0; i A.length ; i){for(int j 0; j B.length ; j){if(A[i].id B[j].id){resultSet.add(A[i]);break;}}
}数据库最费劲的就是跟程序链接释放。假设链接了两次每次做上百万次的数据集查询查完就走这样就只做了两次相反建立了上百万次链接申请链接释放反复重复这样系统就受不了了。即mysql优化原则就是小表驱动大表小的数据集驱动大的数据集从而让性能更优。
因此我们要选择最外层循环小的也就是如果B的数据量小于A适合使用in如果B的数据量大于A即适合选择exist。
索引不适合建在有大量重复数据的字段上如性别这类型数据库字段。
因为SQL优化器是根据表中数据量来进行查询优化的如果索引列有大量重复数据Mysql查询优化器推算发现不走索引的成本更低很可能就放弃索引了。
深分页
limit深分页问题会导致慢查询。
limit深分页为什么会变慢
limit深分页为什么会导致SQL变慢呢假设我们有表结构如下
CREATE TABLE account (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键Id,name varchar(255) DEFAULT NULL COMMENT 账户名,balance int(11) DEFAULT NULL COMMENT 余额,create_time datetime NOT NULL COMMENT 创建时间,update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间,PRIMARY KEY (id),KEY idx_name (name),KEY idx_create_time (create_time) //索引
) ENGINEInnoDB AUTO_INCREMENT1570068 DEFAULT CHARSETutf8 ROW_FORMATREDUNDANT COMMENT账户表;你知道以下SQL执行过程是怎样的嘛
select id,name,balance from account where create_time 2020-09-19 limit 100000,10;这个SQL的执行流程
通过普通二级索引树idx_create_time过滤create_time条件找到满足条件的主键id。通过主键id回到id主键索引树找到满足记录的行然后取出需要展示的列回表过程扫描满足条件的100010行然后扔掉前100000行返回。 limit深分页导致SQL变慢原因有两个
limit语句会先扫描offsetn行然后再丢弃掉前offset行返回后n行数据。也就是说limit 100000,10就会扫描100010行而limit 0,10只扫描10行。limit 100000,10 扫描更多的行数也意味着回表更多的次数。
如何优化深分页问题?
我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。
标签记录法
就是标记一下上次查询到哪一条了下次再来查的时候从该条开始往下扫描。就好像看书一样上次看到哪里了你就折叠一下或者夹个书签下次来看的时候直接就翻到啦。
假设上一次记录到100000则SQL可以修改为
select id,name,balance FROM account where id 100000 limit 10;这样的话后面无论翻多少页性能都会不错的因为命中了id索引。但是这种方式有局限性需要一种类似连续自增的字段。
延迟关联法
延迟关联法就是把条件转移到主键索引树然后减少回表。如下
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time 2020-09-19 limit 100000, 10) AS acct2 on acct1.id acct2.id;优化思路就是先通过idx_create_time二级索引树查询到满足条件的主键ID再与原表通过主键ID内连接这样后面直接走了主键索引了同时也减少了回表。
大事务
为了保证数据库数据的一致性在涉及到多个数据库修改操作时我们经常需要用到事务。而使用spring声明式事务又非常简单只需要用一个注解就行Transactional如下面的例子
Transactional
public int createUser(User user){//保存用户信息userDao.save(user);passCertDao.updateFlag(user.getPassId());return user.getUserId();
}这块代码主要逻辑就是创建个用户然后更新一个通行证pass的标记。如果现在新增一个需求创建完用户调用远程接口发送一个email消息通知很多小伙伴会这么写
Transactional
public int createUser(User user){//保存用户信息userDao.save(user);passCertDao.updateFlag(user.getPassId());sendEmailRpc(user.getEmail());return user.getUserId();
}这样实现可能会有坑事务中嵌套RPC远程调用即事务嵌套了一些非DB操作。如果这些非DB操作耗时比较大的话可能会出现大事务问题。
所谓大事务问题就是就是运行时间长的事务。由于事务一致不提交就会导致数据库连接被占用即并发场景下数据库连接池被占满影响到别的请求访问数据库影响别的接口性能。
大事务引发的问题主要有接口超时、死锁、主从延迟等等。因此为了优化接口我们要规避大事务问题。我们可以通过这些方案来规避大事务
RPC远程调用不要放到事务里面一些查询相关的操作尽量放到事务之外事务中避免处理太多数据
海量数据
MySQL 换 ElasticSearch
在后台管理页面中通常需要对列表页进行多条件检索。MySQL 无法满足多条件检索的需求原因有两点。第一点是拼接条件检索的查询SQL非常复杂且需要进行定制化难以进行维护和管理。第二点是条件检索的查询场景非常灵活很难设计合适的索引来提高查询性能并且难以保证查询能够命中索引。
相比之下ElasticSearch是一种天然适合于条件检索场景的解决方案。无论数据量的大小对于列表页查询和检索等场景推荐首选ElasticSearch。
可以将多个表的数据异构到ElasticSearch中建立宽表并在数据更新时同步更新索引。在进行检索时可以直接从ElasticSearch中获取数据无需再查询数据库提高了检索性能。
MySQL 换 HBase
MySQL并不适合大数据量存储若不对数据进行归档数据库会一直膨胀从而降低查询和写入的性能。针对大数据量的读写需求可以考虑以下方法来存储订单数据。
首先将最近1年的订单数据存储在MySQL数据库中。这样可以保证较高的数据库查询性能因为MySQL对于相对较小的数据集来说是非常高效的。
其次将1年以上的历史订单数据进行归档并将这些数据异构转储到HBase中。HBase是一种分布式的NoSQL数据库可以存储海量数据并提供快速的读取能力。
在订单查询接口上可以区分近期数据和历史数据使得上游系统能够根据自身的需求调用适当的订单接口来查询订单详情。
在将历史订单数据存储到HBase时可以设置合理的RowKey。RowKey是HBase中数据的唯一标识在查询过程中可以通过RowKey来快速找到目标数据。通过合理地设置RowKey可以进一步提高HBase的查询性能。
通过将订单数据分别存储在MySQL和HBase中并根据需求进行区分查询可以满足大数据量场景的读写需求。MySQL用于存储近期数据以保证查询性能而HBase用于存储归档的历史数据并通过合理设置的RowKey来提高查询性能。
冗余设计
通过冗余更多的数据我们可以提高查询性能这是常见的优化方案。除了引入新的表外还可以在表中冗余其他表的字段以减少关联查询的次数。
历史数据归档
MySQL并不适合存储大数据量如果不对数据进行归档数据库会持续膨胀从而降低查询和写入的性能。为了满足大数据量的读写需求需要定期对数据库进行归档。
在进行数据库设计时需要事先考虑到对数据归档的需求为了提高归档效率可以使用ctime创建时间进行归档例如归档一年前的数据。
在将数据库数据归档之前如果有必要一定要将数据同步到Hive中这样以后如果需要进行统计查询可以使用Hive中的数据。如果归档的数据还需要在线查询可以将过期数据同步到HBase中这样数据库可以提供近期数据的查询而HBase可以提供历史数据的查询。可参考上述MySQL转HBase的内容。
分库分表
单表数据量太大
一个表的数据量达到好几千万或者上亿时加索引的效果没那么明显啦。性能之所以会变差是因为维护索引的B树结构层级变得更高了查询一条数据时需要经历的磁盘IO变多因此查询性能变慢。
一棵B树可以存多少数据量
大家是否还记得一个B树大概可以存放多少数据量呢
InnoDB存储引擎最小储存单元是页一页大小就是16k。
B树叶子存的是数据内部节点存的是键值指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中进而再去数据页中找到需要的数据 假设B树的高度为2的话即有一个根结点和若干个叶子结点。这棵B树的存放总记录数为根结点指针数*单个叶子节点记录行数。
如果一行记录的数据大小为1k那么单个叶子节点可以存的记录数 16k/1k 16.非叶子节点内存放多少指针呢我们假设主键ID为bigint类型长度为8字节(面试官问你int类型一个int就是32位4字节)而指针大小在InnoDB源码中设置为6字节所以就是8614字节16k/14B 16*1024B/14B 1170
因此一棵高度为2的B树能存放1170 * 1618720条这样的数据记录。同理一棵高度为3的B树能存放1170 *1170 *16 21902400也就是说可以存放两千万左右的记录。B树高度一般为1-3层已经满足千万级别的数据存储。
如果B树想存储更多的数据那树结构层级就会更高查询一条数据时需要经历的磁盘IO变多因此查询性能变慢。
如何解决单表数据量太大查询变慢的问题
一般超过千万级别我们可以考虑分库分表了。
分库分表可能导致的问题
多表联查问题
之前在库中只存在一张表所以非常轻松的就能进行联表查询获取数据但是此时做了水平分表后同一张业务的表存在多张小表这时再去连表查询时具体该连接哪张呢
①如果分表数量是固定的直接对所有表进行连接查询但这样性能开销较大还不如不分表。②如果不想用①或分表数量会随时间不断变多那就先根据分表规则去确定要连接哪张表后再查询。
第一条好理解第二条是啥意思呢好比现在是按月份来分表那在连表查询前就先确定要连接哪几张月份的表才能得到自己所需的数据确定了之后再去查询对应表即可
聚合操作
之前因为只有一张表所以进行sum()、count()....、order by、gorup by....等各类聚合操作时可以直接基于单表完成但此刻似乎行不通了呀对于这类聚合操作的解决方案如下
①放入第三方中间件中然后依赖于第三方中间件完成如ES。②定期跑脚本查询出一些常用的聚合数据然后放入Redis缓存中后续从Redis中获取。③首先从所有表中统计出各自的数据然后在Java中作聚合操作。
前面两种操作比较好理解第三种方案是什么意思呢比如count()函数就是对所有表进行统计查询最后在Java中求和好比分组、排序等工作先从所有表查询出符合条件的数据然后在Java中通过Stream流进行处理。
上述这三种方案都是比较合理且常规的方案最好是选择第一种这也是一些大企业选用的方案。
跨库join问题
因为将不同业务的表拆分到了不同的库中而往往有些情况下可能会需要其他业务的表数据在单库时直接join连表查询相应字段数据即可但此时已经将不同的业务表放到不同库了这时咋办
①在不同的库需要数据的表中冗余字段把常用的字段放到需要要数据的表中避免跨库连表。②Java系统中组装数据通过调用对方服务接口的形式获取数据然后在程序中组装后返回。
往往垂直分库的场景中第二种方案是最常用的因为分库分表的项目中Java业务系统那边也绝对采用了分布式架构因此通过调用对端API接口来获取数据是分布式系统最为常见的一种现象。
分布式事务问题
分布式事务应该是分布式系统中最核心的一个问题这个问题绝对不能出现一般都要求零容忍也就是所有分布式系统都必须要解决分布式事务问题否则就有可能造成数据不一致性。
在之前单机的MySQL中数据库自身提供了完善的事务管理机制通过begin、commit/rollback的命令可以灵活的控制事务的提交和回滚在Spring要对一组SQL操作使用事务时也只需在对应的业务方法上加一个Transactional注解即可但这种情况在分布式系统中就不行了。
为什么说MySQL的事务机制会在分布式系统下失效呢因为InnoDB的事务机制是建立在Undo-log日志的基础上完成的以前只有一个Undo-log日志所以一个事务的所有变更前的数据都可以记录在同一个Undo-log日志中当需要回滚时就直接用Undo-log中的旧数据覆盖变更过的新数据即可。
但垂直分库之后会存在多个MySQL节点这自然也就会存在多个Undo-log日志不同库的变更操作会记录在各自的Undo-log日志中当某个操作执行失败需要回滚时仅能够回滚自身库变更过的数据对于其他库的事务回滚权当前节点是不具备该能力的所以此时就必须要出现一个事务管理者来介入从而解决分布式事务问题。
其中常用的可能有 Seata 和 最终一致性事务模式 的方案。
Seata
是一款开源的分布式事务解决方案致力于在微服务架构下提供高性能和简单易用的分布式事务服务。
整体的事务逻辑是两阶段提交的模型主要由三个重要的组件组成
TCTransaction Coordinator 事务协调器管理全局的分支事务的状态用于全局性事务的提交和回滚。
TMTransaction Manager 事务管理器用于开启、提交或者回滚【全局事务】。
RMResource Manager 资源管理器用于分支即每一个微服务它是嵌在服务中的事务上的资源管理向TC注册分支事务上报分支事务的状态接受TC的命令来提交或者回滚分支事务
传统XA协议实现2PC方案的 RM 是在数据库层RM本质上就是数据库自身Seata的RM是以jar包的形式嵌入在应用程序里面
TC 为单独部署的 Server 服务端TM 和 RM 为嵌入到应用中的 Client 客户端
Seata事务处理
AT模式
该模式适合的场景
基于支持本地 ACID 事务的关系型数据库。Java 应用通过 JDBC 访问数据库。 生命周期描述
A服务的TM 向 TC 申请一个全局事务全局事务创建成功并生成一个全局唯一的 XID。A服务的RM向TC注册分支事务A服务执行分支事务对数据库做操作A服务开始远程调用B服务并把XID 在微服务调用链路的上下文中传播。A服务会调用B服务形成调用链接这也是分布式事务形成的由来。B服务的RM向TC注册分支事务并将其纳入XID对应的全局事务的管辖。B服务执行分支事务向数据库做操作B服务又开始调用C服务又形成一个调用链这样ABC三个服务形成调用链。C服务的RM也向TC注册分支事务并将其XID纳入全局事务管理中这样TC会把A、B、C服务串联起来保证在一个事务里管理。全局事务调用链处理完毕TM 根据有无异常向 TC 发起针对 XID 的全局提交(Commit)或回滚(Rollback)决议。TC 调度 XID 下管辖的全部分支事务完成提交(Commit)或回滚(Rollback)请求。
但是分布式事务还是存在一个问题就是微服务场景下配置了统一全局异常处理导致seata在AT模式下无法正常回滚问题。
原因服务A调用服务B 服务B发生异常由于全局异常处理的存在ControllerAdvice, seata 无法拦截到B服务的异常从而导致分布式事务未生效。
解决思路配置了全局异常处理所以rpc一定会有返回值, 所以在每个全局事务方法最后 需要判断rpc是否发生异常发生异常则抛出 RuntimeException。
场景实例
简单的用户下单场景4个子工程分别是**Bussiness** (事务发起者)、 **Order** (创建订单) 、**Product** (扣减库存) 和 **Account** (扣减账户余额)。
下图中黄色区域理解为各自独立的微服务被TC纳入了全局事务管理中整个流程变成了一个原子操作。 用户进行下单需要进行三个业务调用Bussiness表示的是业务的发起方也是我们的一个服务发起了一个全局事务TM接着调用商品服务开始扣减库存Bussiness在调用订单服务订单服务又会调用账户服务。此时假设商品服务扣减库存成功订单服务创建订单成功但是账户服务扣减账户余额失败出现了异常此时账户服务的RM即分支事务资源管理器向TC事务报告状态产生异常此时TC会通知其余的RM回滚事务。当所有的RM均正常则提交事务。
最终一致性事务模式 可靠消息
描述 可靠消息模式采用一个可靠的消息中间件作为中介事务的发起方在完成本地事务后向可靠的消息中间件发起消息事务消费方在收到消息后处理消息该方式强调的是双方最终的数据一致性。
流程 订单服务将消息发送给订单的消息队列库存服务去监听订阅订单服务的消息队列并从中消费消息。这种方式需要考虑消息的生产者发送到消息队列再由消费者去消费消息中间都有可能因为网络原因导致数据的不一致性。 本地事务提交后可以使用主动触发方式对本地消息表进行保存与推送。 库存服务在接收到消息并且处理完业务逻辑后通过消息确认机制回复ACK保证消息的消费成功。如果库存服务没有回复ACK则消息中间件在没收到ACK是将进行重复投递。 当消息被成功消费库存服务可以回调一个订单服务的确认API订单服务从本地消息表中删除或者更新其状态 在订单服务中如果重复性把本地消息发到库存服务则需要消息的消费者库存服务提供消息的幂等性支持。 分布式事务的选择
关于一致性
一致性就是数据保持一致性在分布式系统中可以理解为多个节点中的数据的值是一致的而一致性分为强一致性和弱一致性/最终一致性本身也是弱一致性的特殊表现形式。
强一致性是程度最高的一种要求也是最难实现的。系统中的某个数据被更新后后续任何对该数据的操作都是及时更新后的值。弱一致性系统中某个数据被更新后后续对该数据的操作可能得到更新后得知也可能是更新前的值但经过“不一致的时间窗口”后后续对该数据的操作都是更新后的值。最终一致性在一段时间后数据会最终达到一致性状态这个状态时弱一致性的特殊形式。
场景对比
模拟一个简单个新用户注册送福利即营销拉新活动。用户服务与营销服务作为两个独立的服务假设选择以seata作为分布式事务的解决方案此时发生的场景在用户注册环节或者营销服务发放福利环节任意一个环节出异常那么都会导致用户的注册失败这就是很不友好了我们期望的是尽管营销服务出现问题那么应该不会影响用户的注册福利可以通过后期补发所以这种场景消息事务方案更具有优势。
分页问题
以MySQL数据库为例如果是在之前的单库环境中可以直接通过limit index,n的方式来做分页而水平分库后由于存在多个数据源因此分页又成为了一个难题比如10条数据为1页那如果想要拿到某张表的第一页数据就必须通过如下手段获取 这种方式可以是可以但略微有些繁杂同时也会让拓展性受限比如原本有两个水平分库的节点因此只需要从两个节点中拿到第一页数据然后再做一次过滤即可但如果水平库从两节点扩容到四节点这时又要从四个库中各自拿10条数据然后做过滤操作读取前十条数据显示这显然会导致每次扩容需要改动业务代码对代码的侵入性有些强所以合理的解决方案如下
①常用的分页数据提前聚合到ES或中间表运行期间跑按时更新其中的分页数据。②利用大数据技术搭建数据中台将所有子库数据汇聚到其中后续的分页数据直接从中获取。③上述聊到的那种方案从所有字库中先拿到数据然后在Service层再做过滤处理。
上述第一种方案是较为常用的方案但这种方案对数据实时性会有一定的影响使用该方案必须要能接受一定延时。第二种方案是最佳的方案但需要搭建完善的大数据系统作为基础成本最高。第三种方案成本最低但对拓展性和代码侵入性的破坏比较严重。
ID主键唯一性问题
在之前的单库环境时对于一张表的主键通常会选用整数型字段然后通过数据库的自增机制来保证唯一性但在水平分库多节点的情况时假设还是以数据库自增机制来维护主键唯一性这就绝对会出现一定的问题可能会导致多个库中出现ID相同、数据不同的情况如下 上述两个库需要存储不同的数据当插入数据的请求被分发到对应节点时如果再依据自增机制来确保ID唯一性因为这里有两个数据库节点两个数据库各自都维护着一个自增序列因此两者ID值都是从1开始往上递增的这就会导致前面说到的ID相同、数据不同的情况出现那此时又该如何解决呢如下 这时可以根据水平库节点的数量来设置自增步长假设此时有两个库那自增步长为2两个库的ID起始值为{DB1:1}、{DB2:2}最终达到上图中的效果无论在插入数据的操作落入哪个节点都能够确保ID的唯一性。当然保障分布式系统下ID唯一性的解决方案很多如下
①通过设置数据库自增机制的起始值和步长来控制不同节点的ID交叉增长保证唯一性。②在业务系统中利用特殊算法生成有序的分布式ID比如雪花算法、Snowflake算法等。③利用第三方中间件生产ID如使用Redis的incr命令、或创建独立的库专门做自增ID工作。
上述这几种方案都是较为主流的分布式ID生成的方案同时也能够保证ID的有序性能够最大程度上维护索引的性能相对来说第一种方案成本最低但是会限制节点的拓展性也就是当后续扩容时数据要做迁移同时要重新修改起始值和自增步长。
一般企业中都会使用第二种方案也就是通过分布式ID生成的算法在业务系统中生成有序的分布式ID
读写分离
增加MySQL数据库的从节点来实现负载均衡减轻主节点的查询压力让主节点专注于处理写请求保证读写操作的高性能。
物理机
如果数据库服务器内存、硬件资源或者网络资源配置不是很好就会慢一些哈。这时候可以升级配置。这就好比你的计算机有时候很卡你可以加个内存条什么的一个道理。如果数据库压力本身很大比如高并发场景下大量请求到数据库来数据库服务器CPU占用很高或者IO利用率很高这种情况下所有语句的执行都有可能变慢的哈。
相关优秀博客
后端接口性能优化分析-问题发现问题定义-CSDN博客
后端接口性能优化分析-多线程优化-CSDN博客
后端接口性能优化分析-程序结构优化-CSDN博客
