欢迎来到尧图网

客户服务 关于我们

您的位置:首页 > 科技 > 名人名企 > Mysql知识点整理

Mysql知识点整理

2024/12/1 0:39:29 来源:https://blog.csdn.net/qq_45742383/article/details/142664419  浏览:    关键词: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.分表
垂直分表:拆分数据表中的字段,放置在新表中
水平分表:拆分数据表中的行,放置在多张表中,解决单一表数据过大的问题。

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com