MySQL基础知识

MySql

概述：MySQL是一个关系型数据库管理系统

1、数据库三大范式：

1.1 第一范式：每个列都不可以再拆分

1.2 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分

1.3 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

2、数据类型

2.1 MySQL有哪些数据类型

分类	类型名称	说明
整数类型	tinyInt	很小的整数(8位二进制)
	smallint	小的整数(16位二进制)
	mediumint	中等大小的整数(24位二进制)
	int(integer)	普通大小的整数(32位二进制)
小数类型	float	单精度浮点数
	double	双精度浮点数
	decimal(m,d)	压缩严格的定点数
日期类型	year	YYYY 1901~2155
	time	HH :MM :SS -838 :59 :59~838 :59 :59
	datetime	YYYY-MM-DD HH :MM :SS 1000-01-01 00 :00 :00~ 9999-12-31 23: 59 :59
	timestamp	YYYY-MM-DD HH :MM :SS 19700101 00 :00 :01 U TC~2038-01-19 03 :14 :07UTC
文本、二进制类型	CHAR(M)	M为0~255之间的整数
	VARCHAR(M)	M为0~65535之间的整数
	TINYBLOB	允许长度0~255字节
	BLOB	允许长度0~65535字节
	MEDIUMBLOB	允许长度0~167772150字节
	LONGBLOB	允许长度0~4294967295字节
	TINYTEXT	允许长度0~255字节
	TEXT	允许长度0~65535字节
	MEDIUMTEXT	允许长度0~167772150字节
	LONGTEXT	允许长度0~4294967295字节
	VARBINARY(M)	允许长度0~M个字节的变长字节字符串
	BINARY(M)	允许长度0~M个字节的定长字节字符串

3、引擎

3.1 MySQL存储引擎MyISAM与InnoDB的区别

• InnoDB引擎：InnoDB引擎提供了对数据库ACID事物的支持，并且还提供了行级锁和外键的约束，它的设计目的就是处理大数据容量的数据库系统
• MyISAM引擎：不提供事物的支持，也不提供行级锁和外键
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高

3.2 MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效
• MyISAM叶子节点存储的是行数据地址，需要在寻址一次才能得到数据
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

3.3 InnoDB引擎的4大特性

1. 插入缓存（insert buffer）
2. 二次写（double write）
3. 自适应哈希索引（ahi）
4. 预读（read ahead）

3.4 存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

InnoDB：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如 OA 自动化办公系统。

4、事物

概念：事物是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事物是逻辑上的一组操作，要么都执行，要么都不执行。
假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败

4.1 事物的四大特性

1. 原子性：事物是最小的执行单位，不允许分割，事物的原子性确保动作要么全部完成，要么完全不起作用
2. 一致性：执行事物后，数据保持一致，多个
3. 隔离性：并发访问数据时，一个用户事物不被其他事物所干扰，各并发事物之间数据库是独立的
4. 持久性：一个事物被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

4.2 什么是脏读、幻读、不可重复读

• 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
• 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新了原有的数据。
• 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

4.3 什么是死锁，怎么解决

​ 死锁是指两个或多个事物在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象

常见的解决死锁的方法：

1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

   如果业务处理不好可以用分布式事务锁或者使用乐观锁

乐观锁：

​ 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
实现方式： 乐观锁一般会使用版本号机制或CAS算法实现。

悲观锁：

​ 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
实现方式：使用数据库中的锁机制

两种锁的使用场景：

乐观锁适用于写比较少的情况下

如果是写多的情况下，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的情况下用悲观锁比较合适。