Mysql知识点整理

一、关系型数据库

mysql属于关系型数据库，它具备以下特点
关系模型：数据以二维表格形式存储，易于理解和使用。
数据一致性：通过事务处理机制（ACID特性：原子性、一致性、隔离性、持久性）保证数据的一致性和完整性。
数据完整性：支持定义各种约束条件（如主键、外键、唯一性、非空等），确保数据的正确性和有效性。
SQL支持：使用SQL（Structured Query Language）作为标准的数据库查询语言，可以方便地进行数据的检索、更新和管理。
并发控制：允许多个用户同时访问数据库，通过锁机制等手段防止数据冲突。
安全性：支持用户权限管理和认证机制，保护敏感数据的安全。

二、MySQL的字段类型

数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等。
日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

三、MySQL的存储引擎

默认引擎：InnoDB
特点：支持事务、支持外键、支持行级锁、但保存不了具体的行数、支持数据崩溃的数据恢复（redo log）、支持MVCC
Mysql事务
是一组针对数据库操作的集合，要么全部执行完成，要么不执行。
事务的特性ACID
1.原子性：一个数据库事务的操作全部完成，或者全部不完成
2.隔离性：并发的数据库事务之间相互独立
3.持久性：事务对数据库的改变是持久的，即使发生故障也不影响
4.一致性：事务操作，对数据保持一致

并发事务影响
多个事务并发运行，可能会互相造成影响，A事务操作为完成时，B进行了某种操作，可能会对A事务的结果造成一定的影响。
1.脏读（Dirty Read）：
发生情况:一个事务读取了另一个未提交事务的数据。
影响：如果后续事务回滚，那么先前事务读取的数据就会变得无效，这可能导致不一致的数据状态。
2.幻读（Phantom Read）：
发生情况：一个事务读取同一范围的数据多次，但每次读取的结果不同，因为在两次读取之间有另一个事务插入了新的记录。
影响：即使两次查询的条件完全相同，但结果却不同，就像出现了“幻影”记录一样。
3.丢失更新（Lost Update）：
发生情况：两个或多个事务同时对同一数据项进行修改，但其中一个事务所做的修改被另一个事务覆盖，导致第一个事务的修改丢失。
影响：最新的数据可能不会反映所有事务所做的修改。
4.不可重复读（Non-repeatable Read）：
发生情况：一个事务内多次读取同一数据，在两次读取之间另一个事务更新了该数据，导致第一次读取和第二次读取的结果不一致。
影响：同一事务内的不同时间点读取同一数据可能得到不同的结果。

隔离级别
读未提交（Read Uncommitted）：最低的隔离级别，允许脏读、幻读和不可重复读。
读已提交（Read Committed）：允许幻读和不可重复读，但禁止脏读。
可重复读（Repeatable Read）：禁止脏读和不可重复读，但可能允许幻读。
序列化（Serializable）：最高的隔离级别，完全避免了脏读、幻读和不可重复读，但可能导致更多的锁定，从而影响性能。

默认级别为可重复读，实现原理为**多版本并发控制（MVCC，Multi-Version Concurrency Control）**来实现可重复读隔离级别。MVCC 允许事务看到不同时间点的数据版本，并且这些版本可以在事务的生命周期内保持不变。

四、MySQL的索引

1.索引介绍
索引是一种数据结构，合理利用便于提升Mysql性能
优点：加快数据的检索速度和IO次数
缺点：创建和维护索引需要时间，消耗资源。索引占用物理内存空间。
2.索引的底层结构
B+树
B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
而 B+树的检索效率更加稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显
B+树的范围查询，对链表进行遍历。
3.索引的分类
按照底层存储方式角度划分：
聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。
按照应用维度划分：
主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。
最左匹配原则
最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。
如何选取字段创建索引
不为 NULL 的字段、被频繁查询的字段、被作为条件查询的字段、频繁需要排序的字段、被经常频繁用于连接的字段
索引失效的场景
创建了组合索引，但查询条件未遵守最左匹配原则;
在索引列上进行计算、函数、类型转换等操作;
以 % 开头的 LIKE 查询比如 LIKE ‘%abc’;
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;.
如何知道语句是否命中索引.
在sql语句前加上explain命令，利用查询优化器、运行后可展示语句的详情

五、MySQL的日志

错误日志（Error Log）
记录 MySQL 服务的启动、关闭过程以及运行过程中发生的错误信息。如果 MySQL 在运行中遇到故障或错误信息，错误日志会详细记录这些问题，便于排查故障。
通用查询日志（General Query Log）
记录客户端对 MySQL 所发出的每一条 SQL 语句。这些日志会详细记录所有的查询内容，通常用于排查 SQL 执行问题或监控查询行为。
慢查询日志（Slow Query Log）
记录执行时间超过指定阈值的 SQL 语句，常用于优化性能。可以通过配置参数 long_query_time 来设置慢查询的时间标准。
二进制日志（Binary Log）
记录所有导致数据更改的语句（如 INSERT、UPDATE、DELETE），通常用于数据恢复、主从复制和审计目的。
中继日志（Relay Log）
这是从库在主从复制中使用的日志。从库会从主库获取二进制日志，并将其存储为中继日志，从中读取并执行数据更改操作。
撤销日志（Undo Log）
主要用于事务的回滚和 MVCC（多版本并发控制），记录事务未提交前的数据状态，用于在事务回滚时恢复数据。
事务日志（Transaction Log / Redo Log）
用于记录事务提交后的更改，主要用于崩溃恢复，在系统重启时重新执行已提交的事务以确保数据一致性。

六、MySQL读写分离

单个数据库压力过大，导致性能降低，此时需要多个数据库进行读写分离。将多个数据库分为主从库，将读操作和写操作分离，提高数据库的性能。

一般数据库组由一个数据库负责向数据库进行写操作，其余数据库负责完成读操作。

实现原理
通过二进制日志文件MySQL的binlog日志、从库的relay log日志实现主从复制，文件中记录了影响MySQL数据变化的所有语句。根据日志对数据进行同步。
1.主库将数据库中数据的变化写入到 binlog
2.从库连接主库
3.从库会创建一个 I/O 线程向主库请求更新的 binlog
4.主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
5.从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
6.从库的 SQL 线程读取 relay log 同步数据到本地（也就是再执行一遍 SQL ）。

七、分库分表

1.分库
垂直分库：根据业务内容将数据划分为多个库
水平分库：根据业务内容将数据表进行切分，并放置在不同的数据库中
2.分表
垂直分表：拆分数据表中的字段，放置在新表中
水平分表：拆分数据表中的行，放置在多张表中，解决单一表数据过大的问题。