Mysql知识点，更好的理解和学习mysql。在工作和面试中深入理解和使用mysql

mysql三范式（3NF）

1NF：保证每个数据列的原子性。每个列都不可再分解（根据业务的需求进行判断是否列已经是不可分解的原子值）

2NF：保证每张表止描述一件事情。在满足第一范式的基础上，表中的每个非主键列完全依赖于主键，而不是只依赖主键的一一部分

3NF：保证表中非主键的列和主键直接相关。在满足第二范式的基础上，标注的非主键列只依赖主键

mysql数据类型

1.整数类型：tinyint,samllint,mediumint,int,bigint;分别占用1,2,3,4,8 字节

2.实数类型：fload，double,decimal;

3.字符串类型：char,varchar,text,bolb,tinytext,meduumbolb,longblob,varbinary,binary

​ char 是定长的，

​ varchar是变长的。存储可变自负比char更节省空间，如果数据长度小且相似使用char，因为char不易产生碎片，效率上char要比varchar更好

​ text，blob 使用会产生临时表会增加额外的开销

​ varbinary 变长二进制字符串

​ binary 定长二进制字符串

4.枚举类型：enum，把不重复的数据存储为一个预定义的集合。例如状态

5.日期时间类型：timestamp

mysql中关于实数计算时精度丢失的问题

在存储需要高精度的数据时可使用decimal 类型进行数据存储，不会出现四舍五入等情况，原理是:decimal 底层其实使用字符进行准确存储的，所以不会出现丢失的情况（不是绝对的），但是在各个语言中表现都比较良好

mysql 存储引擎 myisam 和innodb 区别

索引区别：

1. myisam 是非聚簇索引，innodb是聚簇索引。
2. myisam 索引的叶子结点存储的是行数据的地址而innodb的主键索引叶子结点存储的行数据；因此innodb 的主键索引非常高效；innodb 的非主键索引叶子结点存贮的是主键和其他带索引的列数据，因此查询时命中索引会让查询非常高效

结构区别：

1. myisan 存储存放在三个文件中：frm-表定义，myd-数据文件，myi：索引文件;innodb 存储在一个文件中 ibd 文件
2. myisam 可以被压缩，存储空间小；innodb 表需要更多的内存和空间，它会在主内存中建立专用的内存缓冲池用于高速缓冲数据和索引
3. myisam 按照插入的书序进行保存数据，innodb 按照主键大小有序插入

特性区别：

1. Myisam 的数据存储格式的过在单文件中分别存储，在迁移备份恢复会更方便，innodb 择需要单独拷贝数据文件或者备份binlog 进行恢复；myisam时堆表，innodb是索引组织表
2. myisam 事务，外键，哈希索引都不支持；支持全文索引，b+tree 索引；innodb 则支持事务，外键，哈希，b+tree 索引但不支持全文索引
3. myisam 支持表级锁，innodb 支持行锁，表锁，页锁
4. 在查询操作（select）myisam效率更好，在操作数据（insert，uodate，delete）innodb 效率更好
5. innodb的4大特性:插入缓存，二次写，自适应哈希索引，预读

mysql索引

索引有点

1. 加速数据检索速度
2. 通过使用索引在查询过程中，使用优化隐藏器提高系统系统

索引缺点

1. 创建索引和维护索引需要耗费时间，影响性能；当对数据表进行数据操作时索引页需要动态维护，会降低数据增删改的执行效率
2. 索引需要占用物理空间

索引分类

1. 逻辑分类：单列索引，复合索引，唯一索引，非唯一索引，函数索引
2. 物理分类：b树索引，反向键索引，位图索引

索引类型：

1. 主键索引：数据列不允许重复，不允许null，每张表有且只有有一个
2. 唯一索引：数据列不允许重复，允许null，表中可以有多个
3. 普通索引：基本的索引类型
4. 全文索引：所有引擎目前常用的索引技术

mysql中的索引有：唯一索引，主键索引，复合索引（联合索引），全文索引

mysql数据库事务

mysql事务:是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性的状态改变为另外一种一致性状态

事务的4大特性

1. 原子性（A）：最小执行单位，不可分割。
2. 一致性（C）：执行前后，数据保持一致
3. 隔离性（I）：并发访问时，一个事务不允许被其他事物干扰，相互独立
4. 持久性（D）：提交后对数据库的改变时持久的，即时出现故障也无影响

什么是脏读，幻读，不可重复读

脏读：事务A更新了一个数据，事务B此时读取了这条数据，由于某些原因事务A进行了rollback 回滚操作，那么这时事物B读取到的数据就是错误的。

幻读：在一个事务中同一个获取方式而两次获取的数据量不同，则称为幻读。

不可重复读：在同一个事务中相同sql两次或多次获取的数据值是不一致的

事务的隔离级别

1. READ-UNCOMMITED(读取未提交)：允许读取没有提交的数据
2. REASD-COMMITTED(读取已提交):允许读取事务已经提交的数据，可阻止脏读
3. REPEATABLE-READ(可重复读):一个事务对同一个字段多次读取的结果都一致，除非事务本身就行了修改，可阻止脏读和不可重复读
4. SERIALIZABLE(可串行话)：完全服从acid，事务需要逐个执行，每个事务不会进行相互干扰，可阻止脏读，幻读，不可重复读

mysql的默认的隔离级别是：可重复读。事务的实现原理是基于锁机制和并发调度。并发采用mvvc（多版本并发控制），保存修改的旧的信息来支持回滚和并发一致性。当需要分布式事务的时候采用串行话隔离级别

Mysql的锁

锁的类别

1. 共享锁：也称为读锁。当需要进行数据访问时，对数据加上共享锁，共享锁就是让多个线程同时读取一个锁。
2. 排他锁：也称之为写锁。当需要进行数据操作时，对数据加上排他锁。一个锁在一个时刻只能有一个线程占用，其他线程必须等待锁释放才可获取锁。

锁与事务隔离的关系

1. 未提交读：事务处理，查询语句，操作语句不会加任何锁
2. 提交读：查询会加共享锁，但是语句执行后会立即释放锁，这样在语句查询的时刻其他事务无法对查询的数据进行修改；这样避免了其他事务给此刻的数据造成脏读
3. 可重复读：事务处理中，查询操作会加共享锁，在事务没有提交前不会释放共享锁；那么在事务没提交的时候任何其他对该数据的操作都会被拒绝；这样就避免了脏读和不可重复读；在本事务中不管几次查询数据都是一样的。
4. 可串行话：在串行话中，会对数据范围进行加锁；在这个范围内其他事务或者操作都是不可的。这样就避免了脏读，幻读，不可重复读。因为对范围进行了加锁，所以其他事务或者新增的语句也是无法执行的。

锁的粒度区分锁的类型

1. 行级锁（innodb 支持）粒度最小的锁，并发也最高，发生锁冲突概率最低
2. 表级锁 （innodb，myisam 支持）实现简单，资源消耗少，加锁快
3. 页级锁（bob 支持）

如何加锁

1.共享锁：

1.lock in shar mode;
demo:select id from tableA where id = 1 lock in share model
           

2.排他锁

2.for update
demo:select id from tableB where id =2 for update
           

加锁原则：

1. 加锁的基本单位是 next-key lock ,前开后闭原则
2. 查询过程中访问到的才会进行加锁

在加锁的时候，mysql 会默认进行范围加锁，如果数据存在则会退化为数据行锁定。需要注意的是非唯一索引锁是不会退化的。

死锁

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

1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以降低死锁概率；例如：都是以Ta>Tb>Tc 顺序来进行数据表的数据访问。
2. 在一个事务，尽量一次锁定所需要的所有数据。
3. 对于经常出现死锁的业务，可以升级锁粒度，使用表级锁进行锁定

常用的sql语句

数据定义DDL：create, drop,alter,truncate

数据查询 DQL:select

数据操作DML：insert，update,delete,

数据控制DCL:commit,rollback,grant,revoke

常用的字段约束

NOT NULL :不能为空

UNIQUE：唯一不可控

PRIMARY KEY：主键

FOREIGN KEY：外键

CHECK：控制范围检查

mysql查询执行过程

1. 建立通信链接：客户端和服务端建立通信。这个过程中mysql连击器会检查用户身份，校验账户密码权限等，链接建立之后只要链接不断开就算此时修改了账户密码，该链接用户也是不会收到影响的
2. 查询缓存：query cache;链接建立后mysql执行语句时会先查询缓存，校验sql语句之前是否执行过。之前执行过的sql语句和结果会以key-val 方式存储在内存中；key时语句，val 是结果；如果语句命中key，那么val会直接返回给客户端。表操作的时候缓存会被清空
3. 分析器：如果没有命中缓存则需要进行真正的查询，这个时候Mysql需要对语句进行解析优化。分析器会根据语句进行语法解析，查询优化最后组成一个查询的数据结构。
4. 优化器：在开始执行的时候需要对语句进行优化处理，索引等都是在这个地方进行
5. 执行器：真正执行语句 获取数据。

mysql日志相关

1. 普通日志：general_log 开启后会记录所有执行的sql；开启后很容易造成日志爆满，一般临时使用
2. 慢查询日志：sql查询超过设置的阈值时间，则记录为慢查询sql。主要用于优化，性能排查
3. 错误日志：记录执行过程产生的错误信息，告警信息；记录mysql启动关闭过程信息；复制一些io信息；事件调度器运行产生的信息
4. binlog：binlog日志记录mysql数据库执行更高的所有操作，主要作用是复制，恢复，审计。
5. 中继日志：relay log日志文件和binlog日志文件格式相同；主要用于从库的复制。slave 从master读取二进制日志数据，写入从库本地后继续异步有sql线程读取解析relaylog为对应的sql命令执行。

Mysql 性能优化

经验之谈：

1. 有外键约束的数据操作会影响sql的操作性能，如果数据列或者程序可以保证数据完整性的时候尽量不使用外键
2. sql语句尽量大写，因为mysql在解析sql语句的时候会吧sql便衣成大写之后再执行，直接大写不需要重新转换
3. 如果数据业务可以保证数据的完整性，可以不需要遵守三大范式
4. 在不是特别频繁查询的字段上不建立索引，索引会增加数据操作的效率，也会占用更多的硬盘空间
5. 在写sql的时候用多少拿多少，不要过多的索取数据量，条件中尽量使用主键或者唯一索引
6. 尽量避免全表扫描，尽量不用逻辑运算和表达式作为条件，这样会放弃索引

如何查找sql语句的性能

1. 通过慢日志，查看效率慢的sql语句
2. 使用explain 模拟优化器执行sql；可以看到sql语句是如何运行的，是否命中索引。然后分析语句或者表结构造成性能瓶颈