MySQL数据库知识

MySQL

MySQL 是一种关系型数据库,并且是开源免费的，方便扩展。MySQL是开放源代码的，任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL的默认端口号是3306。

事务相关 

定义：事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的特性（ACID）：

1. 原子性：事务是最小的执行单元，不能分割。确保动作要么全部完成，要么完全不起作用；
2. 一致性：多个事务对同一个数据读取的结果是相同的；
3. 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性：一个事务被提交之后。它对数据库中数据的改变是持久的；

并发事务带来哪些问题：

1. 脏读（Dirty read）： B事务在A事务提交修改数据之前访问该数据。因为该数据是未提交数据，所以B事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
2. 丢失修改（Lost to modify）：A和B事务同时访问并修改同一个数据，可能造成某个事务的修改丢失。
3. 不可重复读（Unrepeatableread）: A事务期间多次重复读同一数据，期间B事务提交更改数据，导致A读的数据不一致；（修改）
4. 幻读（Phantom read）:A事务读取多个时数据，B事务插入新的数据，造成A事务多读数据；（插入和删除）

事务隔离级别以及MySQL的默认隔离级

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读），可以避免脏读和不可重复读，另外在该隔离级别下，使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生。即达到可串行化级别。

索引相关 

InnoDB存储结构：

• 各个数据页可以组成一个双向链表 

• 每个数据页中的记录又可以组成一个单向链表

• 每个数据页为内部存储记录生成一个页目录，通过主键查找某条记录时，在页目录中使用二分法快速定位到对应的页，然后再遍历该页对应分组中的记录即可快速找到指定的记录；以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

select * from user where indexname = 'xxx'

• 首先需要遍历双向链表，找到记录所在的页，然后只能遍历所在页的单链表找到记录。（非主键查找）

最左前缀原则

联合索引：MySQL中的索引可以以一定顺序引用多列

最左前缀原则：如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到

select * from user where name=xx and city=xx ; //可以命中索引	
select * from user where name=xx ; // 可以命中索引	
select * from user where city=xx ; // 无法命中索引

//查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
//由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

MySQL表字段添加索引

//添加主键
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

//添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )

//添加INDEX(普通索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

//添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

//添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。

唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一。

全文索引：FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列。 

MyISAM和InnoDB区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）：

• 优点：性能极佳（每次查询具有原子性,其执行数度比InnoDB类型更快），而且提供了大量的特性，包括全文索引、压缩、空间函数等。
• 缺点：MyISAM不支持事务和行级锁，仅支持表级锁，而且最大的缺陷就是崩溃后无法安全恢复，不支持外键

InnoDB是MySQL的默认数据库引擎（5.5版之后）

• 优点：支持行级锁和表级锁，默认行级锁。能够安全恢复。支持外键

乐观锁与悲观锁

悲观锁

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。

范式（https://www.cnblogs.com/ktao/p/7775100.html）

第一范式：所有属性都是不可分割的原子值。（1NF是所有关系型数据库的最基本要求）

属性进货（销售）可以分为数量和单价 ，所以不符合第一范式

第二范式：在第一范式的基础上，属性完全依赖于主键

    第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存        储各个实例的惟一标识。惟一属性列被称为主关键字或主键、主码

    第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是属性完全依赖于主键。

 第三范式（3NF）：要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

     简而言之，第三范式就是属性不依赖于其它非主属性。 也就是说， 如果存在非主属性对于码的传递函数依赖，则不符         合3NF的要求。

主键外键 （https://blog.csdn.net/f45056231p/article/details/81070437）

主键：某一个属性组(注意是组)能唯一标识一条记录的属性即为该表的主键

外键：外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

例：学生表(学号，姓名，性别，班级) ： 主键（学号）

       课程表(课程编号,课程名,学分) ： 主键（课程编号）

       成绩表(学号,课程号,成绩) ：  主键（学号+课程号）    成绩表中的学号是学生表的外键 

区别：