MySQL-day6|青训营笔记

数据库三大范式

第一范式：每个列都应该是原子的，是不可再拆分的。 第二范式：在第一范式的基础上，非主键列完全依赖于主键而不是依赖于主键的一部分。否则容易出现数据的冗余。 第三范式：在第二范式的基础上，非主键列必须依赖于主键，而不能依赖于非主键。

MySQL权限管理

都有何种权限类型：MySQL提供了多种权限类型

1. 全局权限 如CREATE USER等
2. 数据库级权限 CREATE DROP等
3. 表级权限 对表数据SELECT、DELETE、INSERT等
4. 列级权限 对列数据进行SELECT、DELETE、INSERT等

如何进行权限分配：

1. 可以利用GRANT ON TO分配用户权限
2. 可以利用REVOKE ON FROM 收回用户权限
3. 也可以直接更改权限表进行权限的分配和回收

如何进行权限检查： 可以查看information_schema数据库中的对应的权限表，有：user_privileges、schema_previleges、table_privileges、column_privileges等。

Binlog的三种录入格式

有三种：statement、row、mixed：

1. statement会将魅影修改数据的SQL以及执行SQL执行的上下文记录在日志中，不需要记录每一行的改动，有效减少了日志量和IO次数。但是SQL中的函数之类改动无法记录。
2. row，不记录上下文信息，而是完整地记录单元行的每一处改动，但是这种记录方式，在进行较多单元行数据改动的时候，会产生极大的日志量，如在Alter table时。
3. mixed：属于两者的折中方案。在无法使用statement的场景下使用row。

主从复制的问题解决

问题一：主库宕机后，数据可能会丢失

因为主库在将数据写入binlog后，就返回给客户端，事务提交成功了。这是主库如果宕机，从库的中继日志中不包含最新的记录，就发生了数据层丢失问题，解决该问题的方案是在等待从库写入中继日志完成后，再返回客户端，提交事务，这样从库就只剩最后一步，SQL线程执行中继日志中的内容 没有去做了，数据不会丢失，但是因为增加了等待时间也会相应的影响数据库的性能，并且有可能因为从库宕机而卡住主库，直到超时。

问题二：从库的SQL线程是单线程的，当主库的写并发很大，且从库的读并发很大时，很容易发生主从复制延时

将SQL线程演化成多个WORKER的形式，通过slave_parallel_workers参数控制并行复制的WORKER数量，实现并行复制。

MyIsam容易忽略的几个特点

1. MyIsam没有Redolog日志，不具备自动的崩溃恢复功能。
2. 在删除操作多时有会触发重建表，不利于并发写。
3. 支持全文索引，全文索引是一种针对模糊查询设计的索引结构，可以解决模糊查询慢的问题，但是其结构也更复杂，占用空间更大。
4. 因为它不支持行级锁，出现死锁的概率会低一点。

主键和候选键有什么区别

1. 候选键是指可以唯一标识行数据的属性或属性集。
2. 主键一定是非空唯一的，因此主键一定是候选键。
3. 候选键可以为Null但是主键不可以为空。
4. 由于候选键的唯一性，因此可以利用其建立唯一索引和外键使用，保证数据的唯一性。

UNIX和MySQL时间戳之间如何进行转换

UNIX_TIMESTAMP和 FROM_UNIXTIME

MyIsam的表在磁盘上是如何存储的

1. .frm存储表定义
2. .MYD存放数据文件
3. .MYI存放索引文件

MySQL记录货币应该用什么数据类型

对精度要求比较高，可以用NUMERIC和DECIMAL

salary DECIMAL(9,2)

标识可以存储-9999999.99到9999999.99之间的数

创建索引字段应该注意什么

1. 非空：对于含有空值的列很难进行查询优化
2. 取离散程度大的字段：可以使用COUNT(字段名)查看字段的所有可能的取值数量，越大越好
3. 索引字段长度尽量小一点，但是也不要太小：这样可以减小索引文件的大小，如果太小的话索引的区分度将会降低

百万级别的数据如何删除

由于在进行数据删除的时候涉及到索引的维护，这将会耗费大量的时间和性能。 为了避免这种情况的发生，可以这样去做： 禁用索引，禁止事务的自动提交 删除索引文件，大概耗时三分钟 删除数据，大概耗时2分钟 重建索引，10分钟左右

最左前缀原则

1. 在使用联合索引时，where条件中必须出现联合索引最左边的字段（出现即可，至于这个字段是否出现在where后的最左侧，不用去管，mySQL是有优化器去做这件事的）。
2. 值得注意的是联合索引在遇到where后的>、<、=、like 都会中断。后面的字段将会用不上联合索引。 比如where a = 1 and b = 1 and c > 1 and d = 1;如果联合索引是abcd 则d是用上索引的。但是若联合索引是abdc则d可以用上索引。

MySQL连接器

mysql -u username -p password

MySQL查询缓存

1. MySQL的查询缓存和自适应hash索引是两件事，自适应hash索引只有InnoDB存储引擎支持，而查询缓存InnoDB和MyIsam都支持。
2. 查询缓存是指查询时会先去其查询缓存中去查找，如果未找到，则将其查询结果存储于查询缓存中。优化下一次的查询速度。
3. 自适应hash索引是在某些字段被频繁访问，则对其建立自适应hash索引，自适应hash索引也是一种索引，并且其位于内存中，根据hash算法可以快速地找到对应的bucket，在进行等值查询时极快。

SQL执行流程

分析器：词法分析语法分析，我们传入的SQL语句其实是由多个字符和空格组成的，经过词法分析可以得知他们是什么么、有什么作用（比如占位符？）。语法分析会得出语法是否有误。如果语法有误会得出You have an error in your syntax。

优化器：经过词法分析和语法分析，MySQL就知道你这条SQL语句要做什么了。然后由优化器决定使用哪种索引和连接，优化器将会选定一种其认为最高效的方案。

执行器：执行器首先会确定是否有执行权限，如果没有权限就会返回没有权限的错误。如果权限就会打开表执行操作。操作时会调用引擎层提供的接口。

什么是临时表？如何删除？

临时表产生的场景：

1. 再进行Union时因为涉及到去重的操作，会产生临时表，对资源的占用较大，执行速度偏慢。因此建议使用Union All。
2. GROUP BY的字段没有添加索引也会产生临时表。
3. FROM子句中进行子查询也会产生临时表。

如何删除：

• 这些执行SQL时生成的临时表，都存在于内存中，并且只对当前连接课件，当当前连接关闭，临时表也就被删除了。