select * from user;
select ... lock in share mode;
select ... for share; MySQL 8.0
update ...
delete ...
insert ...
select ... for update
垂直分表:一个表,列比较多,把不同的字段分到不同的表中,降低单表大小来提高性能。 水平分表:以某个字段按照一定的规则,将一个表的数据分到多个表中。特点是表结构一样。 分表策略
联接练习题
组合两个表
部门工资最高的员工
从不订购的客户
alter table 表名 alter column 字段名 set default 默认值;
开启事务
set autocommit = 0
begin
start transaction
group by 分组
having作用于组
order by对某一列进行排序
where后不能有聚合
limit row: offset
7个关键字执行顺序:
from
where
group by
having
select
order by
limit
explain执行计划
limit优化
性能监控show profile
读写分离存在的问题:
主从集群也就是读写分离,读写分离只是分担了访问的压力,存储的压力并没有解决。数据库集群环境后都是多台slave,基本满足了读取操作,但是频繁写入堆master性能影响比较大,这个时候,单库并不能解决大规模并发写入的问题。
分库分表带来的问题:1. 联合查询问题,join不再适用。2.事务问题,变成了分布式事务。好处:减少大量数据写入时锁对查询的影响。按照存储类别分:用户库,业务库,内存库,图片库,日志库,统计库。
分表:垂直分表和水平分表。解决单张表记录太多的问题。切分策略和导航路由。单表的容量超过500W时建议水平拆分。不到最后一步,不要轻易进行水平分表。
开源方案:1. msyql fabric 2.atlas 3.TDDL 4.mysql proxy 小巧精干,能力有限。+ master/slave 构成一个简单版的读写分离和负载均衡。
主从复制,读写分离—> 垂直分库(每个库可以带slave)—>分区—->水平分表。中间各种通信,调度,维护和编码要求更高。
一主多从。 mysql复制是异步并串行化的。原理:slave从master读取binlog进行数据同步。主要分为3步:
锁可以对有限的资源进行保护,解决隔离和并发的矛盾。通过锁机制可以实现事务的隔离性要求,使得事务可以并发地工作。
按操作分:读锁(共享锁) 写锁(排他锁)
按粒度分:行锁(偏写)表锁(偏读)
锁使用的考虑点:开销,加锁速度,死锁,粒度,并发性能。
行锁:innoDB 开销大,加锁慢,会出现死锁,粒度小,锁冲突概率低,并发高。
表锁:myisam 开销小,加锁快,无死锁,粒度大,锁冲突概率高,并发性低。
行锁的三种算法:1. record lock 2.gap lock 3. next-key lock
锁带来的三种问题:1. 脏读 2. 不可重复读 3. 丢失更新
意向锁是将锁定的对象分为多个层次,对最细粒度的对象进行上锁,首先需要对粗粒度的对象上锁。
触发器只能创建在永久表上,不能对临时表创建触发器。触发器是行触发的。
视图的主要用途是被用作一个抽象装置,只需要按照视图定义来取数据或更新数据。视图是一种虚拟存在的表。
数据库的IO瓶颈和CPU瓶颈都会导致数据库活跃连接数增加,进而逼近数据库可承载连接数的最大值。 IO瓶颈:磁盘读IO瓶颈,热点数据太多,数据库缓存放不下,每次查询时产生大量的IO,降低查询数据。解决方案【分库和垂直分表】
CPU瓶颈:SQL问题,包含join,group by,order by,非索引字段查询等,增加CPU运算的操作。解决方案【SQL优化,建立适当的索引,在业务Service层进行业务计算】单表数据量太大,查询时扫描的行太多,SQL效率低,CPU率先出现瓶颈。解决方案【水平分表】
分库分表工具:
分库分表总结:
另一种分类方式
行锁分为三级,粒度从小到大依次是