MySQL相关知识点

一，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存储引擎通过多版本并发控制机制解决了该问题。
  串行化：这是最高的隔离级别，它通过加强事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

四，事务的并发问题

1. 脏读：事务A读取了事务B更新的数据，然后事务B回滚操作，那么事务A读取的数据就是脏数据。
2. 不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
3. 幻读：事务A读取了事务B已经提交的新增数据。
4. 更新丢失：当两个或多个事务选择同一行，然后基于最初选定的值更新改行时，由于每个事务都不知道其他事务的存在，就会发生更新问题：最后的更新覆盖了由其他事务所做的更新。

注意：幻读是新增，不可重复读是更新。
这两种情况对策是不一样的，对于不可重复读，只需要采取行级锁防止该记录数据被更改或删除，然而对于幻读必须加表级锁，防止在这个表中新增一条数据。

五，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

八，索引的使用

什么情况下使用索引

1. 在经常需要搜索的列上，可以加快搜索的速度；
2. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
3. 在经常需要根据范围进行搜索的列上创建索引；
4. 在经常需要排序的列上创建索引；
5. 在经常使用where字句的列上创建索引；

什么情况下应不建或少建索引

1. 对于那些在查询中很少使用或参考的列不应该创建索引；
2. 对于那些只有很少数据值的列也不应该增加索引；
3. 对于那些定义为text，image和bit数据类型的列不应该增加索引；
4. 当修改性能远远大于检索性能时，不应该创建索引；
5. 表记录太少 、经常插入、删除、修改的表
6. 数据重复且分布平均的表字段
7. 经常和主字段一块查询但主字段索引值比较多的表字段

九，为什么使用数据索引能提高效率

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两种引擎的区别

1. InnoDB支持事务，MyISAM不支持
2. InnoDB支持外键，MyISAM不支持
3. MyISAM支持全文类型索引，而InnoDB在5.6以后才有全文索引
4. MyISAM只支持表级锁，InnoDB支持行锁

应用场景：

• MyISAM不支持事务处理等高级功能，但它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的select查询，那么MyISAM是更好的选择。
• InnoDB用于需要事务处理的应用程序，包括ACID事务支持。如果应用中需要执行大量的insert或update操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

十三，数据库的三范式

什么是数据库范式：

简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。

第一范式 1NF：

当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式，简记为1NF。（满足第一范式是关系模式规范的最低要求）

1. 每一列属性都是不可再分的属性值，确保每一列的原子性；
2. 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据；

第二范式 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，表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键；