一,MySQL端口
默认端口:3306
查看端口命令:show global variables like ‘port’;
修改端口,编辑/etc/my.cnf文件,重启MySQL即可。
二,MySQL事务
数据库事务(Database Transaction) ,是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。
事务处理可以确保除非事务性单元内的所有操作都成功完成,否则不会永久更新面向数据的资源。
MySQL事务主要用于处理操作量大,复杂度高的数据
。比如需要同时操作多个数据,执行多个sql,这些操作sql就构成了一个事务。
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性,保证多个SQL语句要么全部执行,要么全部不执行。
- 事务用来管理insert,update,delete语句
事务属性(ACID)
原子性(Atomicity,或称不可分割性)
:事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。一致性(Consistency)
:事务前后数据的完整性必须保持一致。隔离性(Isolation)
:数据库允许多个事务同时对其数据进行读写和修改的能力,隔离性保证多个事务独立执行性,不互相干扰。事务隔离级别分为,读未提交,读已提交,可重复读和串行化
。持久性(Durability)
:事务处理结束后,对数据的修改是永久的。
三,MySQL的事务隔离级别
默认的事务隔离级别为:可重复读
读未提交:在该隔离级别,所有事务都可以看到其他未提交事务的执行结果
。本隔离级别很少用于实际应用,因为他的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读。读已提交:这是大多数数据库系统的默认级别(但不是MySQL默认的)
。它满足了隔离的简单定义:一个事务只能看见已提交事务所做的改变。这种隔离级别也支持所谓的不可重复读。因为同一事务的其他实例在该实例处理期间肯能会有新的提交(commit),所以同一查询(select)可能返回不同结果。可重复读:这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行
。可重复读会导致幻读
,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影”行。InnoDB和Falcon存储引擎通过多版本并发控制机制解决了该问题
。
串行化:这是最高的隔离级别,它通过加强事务排序,使之不可能相互冲突,从而解决幻读问题
。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。
四,事务的并发问题
脏读
:事务A读取了事务B更新的数据,然后事务B回滚操作,那么事务A读取的数据就是脏数据。不可重复读
:事务A多次读取同一数据,事务B在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。幻读
:事务A读取了事务B已经提交的新增数据。更新丢失
:当两个或多个事务选择同一行,然后基于最初选定的值更新改行时,由于每个事务都不知道其他事务的存在,就会发生更新问题:最后的更新覆盖了由其他事务所做的更新。
注意:幻读是新增,不可重复读是更新。
这两种情况对策是不一样的,对于不可重复读,只需要采取行级锁防止该记录数据被更改或删除,然而对于幻读必须加表级锁,防止在这个表中新增一条数据。
五,MySQL事务处理的两种方式
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
六,事务控制语句
- BEGIN 或 START TRANSACTION 显式地开启一个事务;
- COMMIT 也可以使用 COMMIT WORK,不过二者是等价的。COMMIT 会提交事务,并使已对数据库进行的所有修改成为永久性的;
- ROLLBACK 也可以使用 ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
- SAVEPOINT identifier,SAVEPOINT 允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT;
- RELEASE SAVEPOINT identifier 删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
- ROLLBACK TO identifier 把事务回滚到标记点;
- SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
七,MySQL索引
1,什么是索引
索引(Index)是帮助MySQL高效获取数据的数据结构,通俗来讲索引就好比书本的目录,加快数据库的查询速度。
索引原理:平衡二叉树
2,索引的作用
索引能够提高select查询和where子句的速度,但是却降低了包含update语句和insert语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。
3,索引的使用
创建索引
create index 索引名 on 表名(字段名);
修改表结构(添加索引)
alter table 表名 add index 索引名(字段名);
创建表的时候直接指定
create table 表名(
id int not null,
username varchar(20) not null,
index 索引名(字段名)
);
基本的语法格式如下 :
CREATE TABLE 表名(
字段名 数据类型 [完整性约束条件],
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC])
);
- UNIQUE:可选。表示索引为唯一性索引。
- FULLTEXT;可选。表示索引为全文索引。
- SPATIAL:可选。表示索引为空间索引。
- INDEX和KEY:用于指定字段为索引,两者选择其中之一就可以了,作用是一样的。
- 索引名:可选。给创建的索引取一个新名称。
- 字段名1:指定索引对应的字段的名称,该字段必须是前面定义好的字段。
- 长度:可选。指索引的长度,必须是字符串类型才可以使用。
- ASC:可选。表示升序排列。
- DESC:可选。表示降序排列。
删除索引
drop index 索引名 on 表名;
4,MySQL 索引一般使用什么数据结构
- MyISAM是MySQL 5.5之前版本默认的存储引擎,从5.5之后,InnoDB开始成为MySQL默认的存储引擎。
- MyISAM使用B-Tree实现主键索引、唯一索引和非主键索引。
- InnoDB中非主键索引使用的是B-Tree数据结构,而主键索引使用的是B+Tree。
B-Tree
八,索引的使用
什么情况下使用索引
- 在经常需要搜索的列上,可以加快搜索的速度;
- 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
- 在经常需要根据范围进行搜索的列上创建索引;
- 在经常需要排序的列上创建索引;
- 在经常使用where字句的列上创建索引;
什么情况下应不建或少建索引
- 对于那些在查询中很少使用或参考的列不应该创建索引;
- 对于那些只有很少数据值的列也不应该增加索引;
- 对于那些定义为text,image和bit数据类型的列不应该增加索引;
- 当修改性能远远大于检索性能时,不应该创建索引;
- 表记录太少 、经常插入、删除、修改的表
- 数据重复且分布平均的表字段
- 经常和主字段一块查询但主字段索引值比较多的表字段
九,为什么使用数据索引能提高效率
1》数据索引的存储是有序的;
2》在有序的情况下,通过索引查询一个数据是无需遍历全部索引记录的;
3》极端情况下,数据索引的查询效率为二分法查询效率;
不管数据表有无索引,首先在数据库缓冲器中查找所需的数据,如果数据缓冲器中没有需要的数据时,服务器进程才去读磁盘。
1、无索引,直接去读取表数据存放的磁盘块,读到数据缓冲区中在查找需要的数据;
2、有索引,先读入索引表,通过索引表直接找到所需数据的物理地址,并把数据读入数据缓冲区中;
十,索引的优缺点
创建索引可以大大提高系统的性能(优点):
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性;
- 可以大大加快数据的检索速度,这也是创建索引的最主要的原因;
- 可以加快表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义;
- 在使用分组和排序字句进行检索时,同样可以显著减少查询中分组和排序的时间;
- 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能;
增加索引也有许多不利的方面(缺点):
- 创建索引和维护索引需要耗时间,这种时间随着数据量的增加而增加;
- 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚集索引,那么需要的空间就会更大;
- 当对表中的数据进行增加,删除和修改的时候,索引也要动态维护,这样就降低了数据的维护速度;
十一,乐观锁和悲观锁
乐观锁
总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下再次期间别人有没有去更新这个数据,可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型
,这样可以提高吞吐量,像数据库提供的类似于write_condition机制,其实都是提供的乐观锁。在JAVA中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。
版本号机制参考于:https://blog.csdn.net/weixin_43250623/article/details/96191901
悲观锁
总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会堵塞直到它拿到锁(共享资源每次只给一个线程使用,其它线程堵塞,用完后再把资源转让给其他线程)。传统的关系型数据库里面就用到了很多这种锁机制,如何行锁,表锁,读锁,写锁等,都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。
乐观锁适用于写比较少的情况下(多读场景)
,即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。但如果是多写的情况,一般会经常产生冲突,这就会导致上层应用会不断的进行复审,这样反倒是降低了性能,所以一般多写的场景下用悲观锁就比较合适
。
Mysql两种引擎的区别
十二,MySQL两种引擎的区别
- InnoDB支持事务,MyISAM不支持
- InnoDB支持外键,MyISAM不支持
- MyISAM支持全文类型索引,而InnoDB在5.6以后才有全文索引
- MyISAM只支持表级锁,InnoDB支持行锁
应用场景:
- MyISAM不支持事务处理等高级功能,但它提供高速存储和检索,以及全文搜索能力。如果应用中需要执行大量的select查询,那么MyISAM是更好的选择。
- InnoDB用于需要事务处理的应用程序,包括ACID事务支持。如果应用中需要执行大量的insert或update操作,则应该使用InnoDB,这样可以提高多用户并发操作的性能。
十三,数据库的三范式
什么是数据库范式:
简言之就是,数据库设计对数据的存储性能,还有开发人员对数据的操作都有莫大的关系。所以建立科学的,规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。
第一范式 1NF:
当关系模式R的所有属性都不能在分解为更基本的数据单位
时,称R是满足第一范式,简记为1NF。(满足第一范式是关系模式规范的最低要求)
- 每一列属性都是不可再分的属性值,确保每一列的原子性;
- 两列的属性相近或相似或一样,尽量合并属性一样的列,确保不产生冗余数据;
第二范式 2NF:
前提:满足第一范式
如果关系模式R满足第一范式,并且R的所有非主属性都完全依赖于R的每一个候选关键属性,称R满足第二范式,简记为2NF。
每一列的数据只能与其中一列相关,即一行数据只做一件事。只要数据列汇总出现数据重复,就要把表拆分开来
。
第三范式 3NF
前提:满足第一范式和第二范式
设R是一个满足第一范式条件的关系模式,X是R的任意属性集,如果X非传递依赖于R的任意一个候选关键字,称R满足第三范式,简记为3NF。
数据不能存在传递关系,即每个属性都跟主键有直接关系而不是间接关系
。
三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求跟性能,需求>性能>表结构。所以不能一味的去追求范式建立数据库。
其实严格地说,数据库是有6个范式,除了基本的三范式,还有三个范式
BC范式 BCFN
定义:关系模式R<U,F>中,若每一个决定因素都包含码,则R<U,F>属于BCFN;
第四范式 4NF
定义:限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖;
第五范式 5NF
1,必须满足第四范式;
2,表必须可以分解为较小的表,除非那些表在逻辑上拥有与原始表相同的主键;