SQL语句:
1、唯一键与主键的区别:
唯一键允许空值而主键不允许,并且主键作为每个元组的身份证;
唯一键(添加约束为unique的字段)的设置:
1、通过sql语句设置
2、可视化界面通过添加索引方式设置:
2、主键重复:
当插入行数据主键重复,则会报异常,不会覆盖。要想出现主键重复覆盖,可以使用replace语句代替insert。
3、Count(1)、Count(*)与Count(非关键字段)区别:
Count(1)与Count(*)作用相等,获取符合条件的记录数,根据Mysql优化器确认所走的索引。Count(非主键字段),只获取符合条件记录中该非关键字段不为NULL的记录数。
Mysql优化器根据表索引为Count(*)、Count(主键)与Count(1)选择索引:有二级索引则优化选择二级索引,无则走主索引。
参考文献:使用Count(*),count(主键),count(1)时,是走主键索引还是二级索引
4、Select Distinct 与 Distinct
Select Distinct表示查询到的记录不重复(记录中的所有值作为一个非重复主体)
Distinct标注在函数中的字段前,如Count(Distinct name)表示只计算符合条件的所有记录中name字段不为null的记录数。
5、整数类型与Int(x)中的x表示
int(x)中x表示数据最多显示的位数宽度,而存储值大小不变,与类型相关。
为字段加上了zerofill属性默认带上unsigned属性。表示对显示值位数宽度未达到x,则填0补充。如对于int(3)的字段age赋予10,则select查询该字段值显示结果为010。
6、对TIMESTAMP与DATETIME自动设置时间配置:
对需要自动设置时间的字段添加上相应属性:
- 对创建时时间字段create_time/update_time自动设置时间为当前时间,需对字段加上 DEFAULT CURRENT_TIMESTAMP属性
- 对更新时时间字段update_time自动更新时间为当前时间,需对字段加上 ON UPDATE CURRENT_TIMESTAMP属性
对于加上自动设置时间的字段,只有在插入或者更新时不被涉及到方可生效。若新建时对时间字段create_time设置了值(包括null),则DEFAULT CURRENT_TIMESTAMP属性失效。
6.1、表创建各字段属性设置:
CREATE TABLE `deal_table` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`account_pay` int NULL COMMENT '扩展字段2',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
6.2、自动时间设置失效:
insert into deal (id, account_pay
create_time,update_time
) values ("11",111,null,NULL
);
6.3、自动时间设置生效:
insert into deal (id, account_pay
) values ("111",111
);
6.4、结果显示,失效的为null,生效的为当前时间:
7、批量语句部分执行出现异常:
sql批量语句执行过程中报异常(如主键冲突),则该批量语句回滚,即不执行语句所有内容,直接抛出异常(若主键冲突则抛出主键冲突异常)。
MySQL层面:
1、Innodb数据库引擎:
Mysql中,Innodb数据库引擎默认只在通过索引条件查询数据时加行级锁,否则加表级锁。Oracle则直接加行级锁。加锁方式:mysql中语句加锁属于自动加锁(select语句加共享锁,insert、delete、update语句加排他锁)。手动加锁可以在sql语句后加“lock in share mode”与“for update”,分别表示共享锁与排他锁。
2、超时等待时间:
在数据库中进行锁等待时间设置,若超时则执行的sql语句报异常,如
set global innodb_lock_wait_timeout=100;//数据库中设置锁超时时间
3、@Transaction:
导入了Spring-boot-starter-jdbc与Spring-boot-starter-jpa依赖包即可在SpringBoot容器中引入事务管理器。通过@EnableTransactionManager启动事务管理。
@Transaction标注在方法上,默认方法执行完毕,执行commit提交事务。
@Transaction可以通过属性设置,设置隔离级别、超时等待时间、传播行为等。
@Transaction标注的方法中也可手动调用提交与回滚。
4、Mysql--Canal--RabbitMQ(kakfa、RocketMQ)--SpringCloud串联监听:
Canal:基于 MySQL 数据库增量日志解析,提供增量数据订阅和消费。通过Mysql的归档日志binlog,Canal将消息队列与Mysql进行了归档日志订阅,即Mysql进行操作,就会发送消息到消息队列。SpringCloud对消息队列进行监听即可获取数据库操作信息。
参考博文1:SpringBoot下数据源事务使用
参考博文2:mysql:InnoDB行/表级锁实现/事务
参考博文3:Mysql实现加锁机制
参考博文4:Mysql--Canal--RabbitMQ实时同步监听配置示例
Canal官方使用说明Github地址:Canal官方项目说明
5、MySQL日志
日志:redo log重做日志、bin log归档日志、undo log撤销日志。
redo log、bin log、undo log共同点:都存储了数据库已提交与未提交的修改数据。
redo log:用于系统宕机后,进行数据的恢复,大小有限,循环使用,记录有刷盘点。
bin log:常用于主从复制与数据恢复,大小不限,未记录有刷盘点。
undo log:用于事务异常或调用rollback执行的数据回滚。
数据库存储---日志与磁盘:
日志和磁盘做为数据库数据存储,使用了 MySQL 里的 WAL 技术,WAL 的全称是 Write-Ahead Logging,它的关键点就是先写日志,再写磁盘。
具体来说,当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log(redolog buffer)里面,并更新内存(buffer pool),这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时候(如系统空闲时),将这个操作记录更新到磁盘里面,更新到磁盘前会先记录binlog日志。日志记录位置如下:
参考博文:为什么 redo log 具有 crash-safe 的能力,是 binlog 无法替代的?
参考博文:MySQL日志系统:redo log、binlog、undo log 区别与作用
6、MySQL主从复制原理图:
7、MySQL执行原理图:
8、Mysql的性能瓶颈:
基本限制在三个部分:CPU、磁盘、网络。
并不是数据库连接池越大越好,连接池在理想情况下与CPU核心数相同即能发挥最大性能。但现实开发过程中往往因磁盘与网络的限制,连接池连接数需要设置大于CPU核心数才能发挥最大性能。一般连接池连接数设置公式如下:连接数=(CPU核心数*2)+有效磁盘数。
参考文献:线程池和数据库连接池设定合适 的大小
9、Mysql隔离级别:
- read uncommitted可读未提交
- read committed只读已提交
- repeatable read可重复读
- serializable串行化
大部分事务隔离级别默认为read commited,Mysql事务隔离级别默认为repeatable read
通过如下代码可以设置事务隔离级别(read uncommitted可同等替换为read committed等其他隔离级别) :
set session transaction isolation level read uncommitted;
事务安全级别最高的是Serializable串行化,但其发挥作用的场景是当一个事务访问时,其他事务的写操作会被挂起。缺点也很明显,这是隔离级别中最严格的,但是这样做势必对性能造成影响。
参考文献:五分钟搞清楚MySQL事务隔离级别
10、select……where……for update 与select……where……lock in share mode
select……where……for update与select……where……lock in share mode是按照查询条件获取数据,其中对查询数据或整表进行加行锁或表锁操作【注:for update为排他锁、lock in share mode为共享锁】。若where中条件不属于主键或索引,则对全记录加锁【注:隔离级别为RR,则既全记录加锁,也对间隙加间隙锁;隔离级别为RC,则只对全记录加锁】;若where中条件属于主键或索引,则对行或间隙加锁。
11、事务隐式提交
- DDL与DCL会触发事务的隐式提交。因此事务中要注意只包含DML才能使事务按照完整事务顺序执行。
- 事务中加表锁、解表锁也会触发事务的隐式提交。
- flush privileges刷新权限、optimize table优化表、repair table修复表操作也会触发事务的隐式提交。
12、事务显式提交
- Mysql参数autocommit默认为1。此时将每个语句都当作一个事务,执行结束即提交。通过begin;/start transaction;命令可以将autocommit挂起,把begin;与commit;/rollback;间作为一个事务执行。
- Mysql参数autocommit设置为0。此时每个语句都需要显示执行commit;/rollback;才会提交。不用显示调用begin;与start transaction;开启事务。
13、查询服务器与客户端各连接状态命令
show processlist;
14、查询innodb存储引擎使用情况命令
show engine innodb status;
15、mysql存储引擎Innodb相比MyIsam改进之处
- Innodb支持事务而MyIsam不支持事务。
- Innodb最小锁粒度支持行锁,MyIsam最小锁粒度支持表锁。
- Innodb支持外键而MyIsam不支持外键。
- Innodb是聚簇索引,索引最终都基于主键索引,而MyIsam是非聚簇索引。
- Innodb索引类型与MyIsam一样都是B+树,但Innodb主键索引叶子节点是具体数据,而MyIsam索引叶子节点是指针。
16、行锁与表锁
当表中还有行锁时,加表锁操作进行阻塞等待阶段。直至没行锁才能完成表锁添加。
17、Mysql间隙锁
间隙锁是可叠加的锁。间隙存在间隙锁,可保证了该间隙不可插入新行,
Mysql中innodb为解决幻读问题,为RR模式增加了间隙锁【间隙锁只有RR模式下才生效】。间隙锁解决了幻读问题。
间隙锁+行锁合称next-key lock,每个next-key lock是前开后闭区间。