数据库相关

MySql

语句

执行顺序

⑧select id,name,class,subject,max(grade) as maxG
①from stu
②left join grades
③on stu.id=grades.id 
④where stu.state=1 
⑤group by name
⑥having max(grade)>60
⑦order by maxG
⑨limit 10；
-- 找出所有班级在校（where stu.state=1）同学所有科目成绩(from stu join grades on)中
-- 单科成绩最高(group by name)分的前10名（order by)（limit)同学
-- 且成绩必须是及格的(having)；

-- 执行顺序：from - join产生笛卡尔积表 - on条件过滤（left、rightjoin则把左、右表被过滤掉的部分添加回结果）- where条件过滤 - groupby将对应列按值组合成组 - having对分组后结果过滤 - order by排序 - select选出目标列 - limit 截断所需条数

语法

Rollup 分类汇总

适用于对group by 之后的字段再汇总，但该关键字与order by 互斥，需要使用order by field（）代替；
效率高于union all方式实现；

【with rollup 方式】
select 
ifnull(class,'SUM'), -- withrollup会在最后增加一个全部汇总行，这行的class为NULL，使其显示为SUM
sum(grade) class_sum  -- 求（按班级）成绩和
from grades 
group by class -- 按班级
with rollup -- 最后增加一个groupby之后的全汇总
order by field(class,'SUM'), -- 对最终结果排序，class字段SUM置底 
class_sum desc; -- 并且按班级的总成绩降序

【with rollup 方式：二级汇总】
select ifnull(class,'total_sum') ,chinese_sum,english_sum,math_sum from
(
select ifnull(class,'class_sum') class,
sum(if(subject='chinese',grade,0)) chinese_sum,
sum(if(subject='english',grade,0)) english_sum,
sum(if(subject='math',grade,0)) math_sum
from
grades
group by class
with rollup
) c -- 求每个班各科成绩汇总及班级总成绩汇总
group by class with Rollup -- 求所有班级各科成绩汇总
order by field(class,'total_sum'),math_sum desc;



【union all 方式】

select 
class,
sum(grade) sum  -- 求（按班级）成绩和
from grades 
group by class -- 按班级
union all -- union起来
select 
'SUM', -- 求总和，使其显示为SUM
sum(grade) sum  -- 求总成绩和
from grades  ;

效率优化：原因查询与分析

慢查询日志

1. 开启慢查询日志

use mysql;
set global slow_query_log=ON;
set global long_query_time=5;
set global log_queries_not_using_indexes = 1;
show variables like '%query%';

2. mysqldumpslow / pt-query-digest / mysqltop(天兔Lepus)等工具进行日志分析

SHOW PROCESSLIST

id	user	host	db	Command	Time	State	Info
5	event_scheduler	localhost		Daemon	343273	Waiting on empty queue
14	root	localhost:56676	test	Query	0	init	SHOW PROCESSLIST
17	root	localhost:56679	test	Sleep	11
42	root	localhost:57086	test	Sleep	3548
349	root	localhost:62675	test	Sleep	910
350	root	localhost:62679	test	Sleep	891
351	root	localhost:62680	test	Sleep	890
352	root	localhost:62682	test	Sleep	885
353	root	localhost:62683	test	Sleep	882
354	root	localhost:62686	test	Sleep	878
355	root	localhost:62689	test	Sleep	862
356	root	localhost:62692	test	Sleep	858
357	root	localhost:62693	test	Sleep	855
358	root	localhost:62695	test	Sleep	838

Explain 执行计划

explain UPDATE EmpBasic a
JOIN EmpLeadership b ON a.username = 'Zhang Qinjian' 
AND a.id = b.leaderid 
SET a.state = 0,
b.leaderid =- 1; 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	update	b		ALL					6	100	using where
1	update	a		eq_ref	primary	primary	8	test.b.leaderid	1	10	using where
操作表的顺序，ID相同即同级一组，ID不同即子查询，ID越大越先执行（深层子句）	查询类型			system>const>eq_ref>ref> fulltext>ref_or_null>index_merge> unique_subquery>index_subquery> range>index>ALL	涉及的索引	实际使用的索引	索引的最大可能长度	显示索引的那一列被使用了	扫描行数	using where/filesort/ temporart/index/buffer/ join buffer/distinct…

type

system : 表中只有一行记录，等于系统表，直接找到；
const : 通过索引一次找到这一行；比如查找条件和目标均为PK/Unique字段的单一值；
eq_ref : 目标为任意段，过滤条件为索引字段（PK/Unique等唯一索引），给定值；表中一定只有一条记录与其匹配；
ref 【目标水平】: 目标为任意段，过滤条件为索引字段（非唯一索引），给定值；表中可能有多条记录与其匹配；
range : 目标为任意字段，过滤条件为索引字段，给定范围选择；
index : 目标为索引字段，没有过滤条件；即遍历索引；（比ALL好在，index从索引文件读取，ALL从硬盘读取）
ALL ： 目标非索引字段且过滤条件非索引字段（或无过滤）；即遍历全表；

-- EmpLeadership 表 PK=id , foreignKey=empid+id(EmpBasic)
-- EmpBasic 表 PK=id 
EXPLAIN select  leaderid from EmpLeadership   where  id=2; -- const where 唯一索引=唯一值
EXPLAIN select  id from EmpLeadership   where  id=2; -- const where 唯一索引=唯一值

explain select a.* from EmpLeadership b left join EmpBasic a on a.id=b.id;-- a eq_ref b all ： a 唯一索引作为条件的值而不是变量：id ， b left join所以全表扫
explain select a.* from EmpLeadership a left join EmpBasic b on a.id=b.id; -- a all b eq_ref ： b 唯一索引作为条件的值而不是变量：id ， a  left join所以全表扫
explain select a.* from EmpLeadership a inner join EmpBasic b on a.id=b.id; -- a all b eq_ref ：b 唯一索引作为条件的值而不是变量：id ， a inner join所以全表扫
explain select a.* from EmpLeadership a right join EmpBasic b on a.id=b.id; -- a index b  eq_ref ：b 过滤唯一索引作为条件的值而不是变量：id ， a right join所以按条件遍历索引列：id与b扫出的结果再匹配
-- 写在from后面第一个表，是主表，作为条件，join的其他表是作为条件的值的；因此第一句中 a 的 id是条件的值，type即eq_ref；后面三句中都是b作为值，eq_ref

EXPLAIN select * from EmpLeadership where empid=1 ; -- ref where 非唯一索引+唯一值

EXPLAIN select  leaderid from EmpLeadership where id>3; -- range where 索引+范围

EXPLAIN select  leaderid from EmpLeadership where empid>3; -- range where 索引+范围

EXPLAIN select  id from EmpLeadership ; -- index where 无过滤，select 索引

EXPLAIN select  id from EmpLeadership   where  leaderid>2; -- ALL where 非索引
EXPLAIN select  leaderid from EmpLeadership   where  leaderid=2; -- ALL where 非索引

Extra

1. Using filesort
   不是按照表内索引进行排序，而是用户指定了其他的无法通过索引完成的order by
2. Using temporary
   使用了临时表保存中间结果，常见于order by 和 group by 的中间过程
3. Using index
   使用了覆盖索引（Covering Index），即索引覆盖了查询的字段，避免了访问表的数据行，效率高
   如果同时出现Using where，表明索引是查找条件，用于执行查找；
   如果没出现Using where，表明索引用来读取数据而非执行查找。
   注意：
   a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select *
   b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能
4. Using where
5. Using join buffer
   使用了链接缓存
6. Impossible WHERE
   where子句的值总是false
7. select tables optimized away
   在没有group by子句的情况下，基于索引 优化MIN/MAX操作 或
   对于MyISAM存储引擎 优化COUNT（*）操作，
   不必等到执行阶段再进行计算，生成执行计划时即可完成优化
8. distinct：
   优化distinct，在找到第一个匹配后即停止

优化解决

MYSQL事务配置 innodb_flush_log_at_trx_commit

1 ：事务提交时立即把事务日志flush写入磁盘，同时更新数据和索引，费性能。
0 ：每隔1秒把事务日志写入磁盘，MySQL Down时可能可能丢失事务数据。
2 【推荐】：事务提交时立即写入磁盘文件（系统内核缓冲区），但每隔1秒才刷新到磁盘，同时更新数据和索引，操作系统Down时可能丢数据，一般不会丢失超过1-2秒的更新。

条件语句

少用不等条件

索引

化简为繁

善用临时表，少用动态生成、复杂join；

Spring Transaction声明式事务

不可分割的工作单位，用户定义的一组绑定的操作序列。

实现原理：AOP

特征

Automicity原子性、Consistancy一致性、Durability永久性、Isolution隔离性

作用

保持业务流程完整性、数据完整准确
方便分布式事务，避免并发问题
提高性能

并发问题与隔离级别

	脏读	虚读（不可重复读）	幻读
含义	事务A能访问事务B未提交的数据（B随时会改变/发生异常回滚）	事务A能修改事务B正在读取的数据（两次查询结果值不一致）	事务A能增删事务B正在读取的数据（两次查询结果数量不一致）
读未提交	发生	发生	发生
读已提交	解决	发生	发生
可重复读	解决	解决	发生
串行化读	解决	解决	解决

传播行为

类型	字面理解	传播行为解释	使用场景
required	必须（有一个）	已有直接用，没有则自动创建后在其中运行	增删改常用，默认
required_new	必须用新的	无论有没有，自动创建一个事务并在其中运行
supports	仅仅是支持	有就用，没有就直接运行	查询行为可用
not supported	不支持用	有就挂起，没有就直接运行
never	决不能用	有就报异常
nested	嵌套	支持嵌套式事务
mandatory	强制	有就用，没有就抛异常

使用方式

xml 方式

bean节点 创建事务管理器

	配置class属性为DataSourcetransactionManager，创建实例
	配置dataSource属性，绑定当前项目中使用的数据源

tx:advise节点 配置事务

	配置transactionManager属性绑定事务管理器 
	子节点 tx:attributes
		  子节点 tx:method ：配置具体需要添加事务的方法 (包括 name方法名、isolation隔离级别、propagation传播行为 属性）

aop:config 节点 配置事务通知

	aop:pointcut 配置需要添加事务的切点
	aop:advisor 配置事务切面：绑定切点和事务 advice-ref="事务的id"  point-ref="切点的ID"）  

注解方式

bean节点 创建事务管理器

	配置class属性为DataSourcetransactionManager，创建实例
	配置dataSource属性，绑定当前项目中使用的数据源

aop:config 节点 配置事务通知

	aop:pointcut 配置需要添加事务的切点

tx:annotation-driven节点 打开事务注解开关（声明事务使用注解驱动）

	配置transactionManager属性绑定事务管理器 

Service层业务逻辑类/具体方法上 使用@Transactional(isolation、propagation)注解声明

Redis

问题

缓存穿透

访问Redis中不存在的数据，从而必须穿过Redis访问DB；
原因——解决方案
第一次访问——服务器启动时, 提前写入
恶意访问不存在的key——规范key的命名, 通过中间件拦截
Key过期——对某些高频访问的Key，设置合理的TTL或永不过期

雪崩

Redis整个挂掉，所有请求涌向DB
原因
硬件问题等——Redis集群、限流

Hadoop

离线和海量数据分析，核心是分布式文件系统HDFS和计算系统Map、Reduce；

HDFS

NameNode
Master，管理空间、文件-数据块映射；
可以通过配置集群，两个NN之间数据保持一致，一个NN挂掉自动切换其他的，实现HA；
SecondaryNamenode
DataNode
Slave
存储数据、执行计算、汇报信息给NN

MapReduce

输入任务-拆分成N个Map任务-在多个节点上执行这些任务-Reduce进行结果汇总

Yarn

服务器资源管理调度
ResourceManager：管理分配系统中的所有资源
NodeManaer：管理分配所在Node上的资源
ApplicationManager：运行时管理任务，运行结束退出ApplicationManager

外部组件：Hive、Spark、HBASE

Hive

通过H SQL进行MapReduce任务的开发，方便编写

Hive on Spark

把Spark作为Hive任务的一个计算引擎；
将Hive的查询作为Spark的任务提交到Spark集群上进行计算。
提高Hive查询的性能，提供了更加灵活的选择；

参数

spark.executor.cores

yarn.nodemanager.resource.cpu-vcores=服务器单节点核心数-基础工作需要的核心数=最多可启动的executor个数*spark.executor.cores

如28=32-4=7*4，即将yarn设置成28核，可分配给7个executor，每个可用4核

spark.executor.cores的值不能大于单个Container能申请到的最大核心数，即yarn.scheduler.maximum-allocation-vcores的值

spark.executor.memory
每个Executor可利用的堆内内存量
堆内内存越大，Executor就能缓存更多的数据，在做诸如map join之类的操作时就会更快，但同时也会使得GC变得更麻烦。
spark.yarn.executor.memoryOverhead
每个Executor可利用的堆外内存量

这两个内存参数相加的总量也要<=yarn.scheduler.maximum-allocation-mb；

Hive官方公式：yarn.nodemanager.resource.memory-mb * (spark.executor.cores / yarn.nodemanager.resource.cpu-vcores)；按核心数的比例分配。

在计算出来的总内存量中，80%~85%划分给堆内内存，剩余的划分给堆外内存。

假设集群中单节点有128G，yarn.nodemanager.resource.memory-mb（单NodeManager能够利用的内存）=120G，总内存量=120 * (4 / 28) * 1024 ≈ 17554MB。
再按8:2，最终spark.executor.memory=13166MB，spark.yarn.executor.memoryOverhead=4389MB。

spark.executor.instances

执行查询时整个集群一共启动多少个Executor实例；
取决于每个节点的资源分配情况以及集群的节点数；
若共有10台32Core/128G的节点，每个节点分配7Executor*4core，理论上spark.executor.instances=70可使集群资源最大化利用。但实际上考虑Driver占用的资源和其他业务，会设小一些（推荐理论值的一半左右）。

spark.dynamicAllocation.enabled

Hive集群面向很多用户提供分析服务的情况下，启用Executor动态分配。

spark.driver.cores

该参数表示每个Driver可利用的CPU核心数。绝大多数情况下设为1都够用。

spark.driver.memory
每个Driver可利用的堆内内存量
spark.driver.memoryOverhead
每个Driver可利用的堆外内存量

以上2个参数一般是总量=512MB~4G，使用“二八分配”。如spark.driver.memory=819MB，spark.driver.memoryOverhead=205MB，加起来=1G。

hive.auto.convert.join.noconditionaltask.size
Hive on MR下默认为10MB。
Hive on Spark下最好调到100~200MB。因为Spark引擎下使用内存中存储的近似大小，比HDFS上压缩过、序列化的数据要更大；

背景：Hive中join的一方是小表时，（当hive.auto.convert.join=true，hive.auto.convert.join.noconditionaltask=true（默认）时），会自动转换成比较高效的map-side join。hive.auto.convert.join.noconditionaltask.size这个参数就是map join转化的阈值。

hive.merge.sparkfiles

on MR时是hive.merge.mapredfiles
on Spark时会改成hive.merge.sparkfiles
小文件对HDFS不利，要把这个参数设为true。
小文件合并的参数：hive.merge.smallfiles.avgsize与hive.merge.size.per.task可以不变。

Spark

基于内存并行计算，减少JVM启停次数，运行更快；
可以用大量廉价硬件部署，以空间换时间；
SparkSQL提供了更多算子；

ZooKeeper

用于分布式服务的监控，leader选取，主从切换