MySQL数据库要点汇总

浅尝即止何来突破

已于 2022-09-30 14:31:28 修改

阅读量436

点赞数

分类专栏：数据库杂点文章标签：数据库 sql

于 2020-08-10 00:09:13 首次发布

本文链接：https://blog.csdn.net/TIMFannie/article/details/107903021

版权

数据库杂点专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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;开启事务。