MySQL基础总结

最新推荐文章于 2024-08-14 14:47:29 发布

徐梓泳

最新推荐文章于 2024-08-14 14:47:29 发布

阅读量167

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_42781810/article/details/106708923

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL基础总结

1、安装MySQL环境

https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484182&idx=1&sn=6a04b10ebac95da1a66abcefa1fa442c&scene=19#wechat_redirect

2、操作数据库的基本命令

# 修改密码
update user set password = password('123456')where user='root';
flush privileges; 	#刷新数据库
show databases; 	#显示所有数据库
use dbname;  		#打开某个数据库
show tables; 		#显示当前数据库下的所有表
describe user;		#显示表mysql数据库中的user列信息
create database name; 	#创建数据库

exit; 		#退出MySQL
? 命令关键词 #寻求帮助
-- 表示注释

3、数据库操作

3.1 SQL语句分类

DDL ：CREATE、DROP、ALTER（定义和管理数据对象）
DML：INSERT、UPDATE、DELETE（用于操作数据库对象中包含的数据）
DQL：SELECT（用于查询数据库对象）

DCL：GRANT、COMMIT、ROLLBACK（用于管理数据库的语言，包括管理权限和数据更改）

DDL: 数据库操作

create database [if not exists] 数据库名;
drop database [if exists] 数据库名;
show databases;
use 数据库名;

DDL: 表操作

# 创建表
create table [if not exists] `表名`(
	`字段名1` 列类型 [属性][索引][注释],
  	`字段名2` 列类型 [属性][索引][注释],
  #...
  	`字段名n` 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];

# 修改表
ALTER TABLE 旧表名 RENAME AS 新表名; #修改表名
ALTER TABLE 表名 ADD 字段名 列属性[属性]; #添加字段
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 MODIFY 旧字段名 新字段名 列属性[属性] #修改字段
ALTER TABLE 表名 DROP 字段名; #删除字段

MySQL的数据表类型（Engine）：MyISAM, InnoDB

MyISAM: 节约空间及相应速度；

InnoDB: 安全性、事务处理和多用户操作表；

设置数据表字符集：

通过命令设置：CHARSET=utf8
根据MySQL数据库配置文件my.ini中的参数设定

DML：操作表数据

外键概念：如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。

主表：以另一个关系的外键做主关键字的表；
从表：有此外键的表

外键作用：保持数据的一致性，完整性，使两张表形成管理。

# 添加数据
INSERT INTO 表名[(字段1，字段2，...)] VALUES("值1"，"值2"，...)

# 修改数据
UPDATE 表名 SET 列名='值' [WHERE 条件]

# 删除数据
DELETE FROM 表名 [WHERE 条件]

# 清空表，用于完全清空表数据，但表结构、索引、约束等不变
TRUNCATE [TABLE] 表名;

DQL: 数据查询

SELECT 语法

SELECT [ALL | DISTINCT]		-- 去重复
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2]  -- 联合查询
[WHERE ...]  		-- 指定结果需满足的条件
[GROUP BY ...]  	-- 指定结果按照哪几个字段来分组
[HAVING] 			-- 过滤分组的记录必须满足的次要条件
[ORDER BY ...]  	-- 指定查询记录按一个或多个条件排序
[LIMIT 0,5];		-- 指定当前页面起始记录，页面的总记录数

# 指定查询字段
SELECT * FROM 表名;
SELECT 列1,列2,FROM 表名;

AS: 取别名；

DISTINCT: 去除重复项；

WHERE：条件查询

模糊查询：比较操作符

LIKE通配符：%（代表0到任意个字符），_(代表一个字符)

连接查询：如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询

内连接 inner join
查询两个表中的结果集中的交集
外连接 outer join
- 左外连接 left join
  (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
- 右外连接 right join
  (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
通过等值条件连接和非等值条件连接两（多）个表。

子查询：数据表与自身进行连接查询。

需求：数据表中必须有关联的父id和子id，将表通过父子id进行关联查询。

排序：ORDER BY 指定列或结果集进行排序

ASC：升序
DESC：降序

分页：LIMIT 0, 5

公式：limit (pageNo-1)*pageSzie,pageSzie

3.2 事务

事务概念

事务就是将一组SQL语句放在同一批里执行。如果全部语句执行成功，则事务提交，否则事务将回滚。
MySQL事务处理只支持InnoDB和BDB数据表类型。

**事务ACID原则：**原子性、一致性、隔离性、持久性

原子性：一个事务的所有操作要么全部完成，要么全部不完成。
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。
隔离性：并发访问数据库时，一个用户不被其他事务所干扰，各并发事务之间数据库是独立的。
持久性：一个事务提交后，该事务对数据库所做的更改要持久保存到数据库中。

并发事务带来问题：

脏读：当一个事务访问数据并且对数据进行修改，而且还没提交到数据库中，这时另一个事务也访问了这个数据，并使用了该数据。因为这个数据是还没有提交的数据，另一个事务读到的这个数据是“脏数据”。
丢失修改：指在一个事务读取一个数据时，另一个事务也访问了该数据，第一个事务修改了这个数据，另一个也修改了这个数据，这样第一个事务的修改结果就被丢失，因此称为丢失修改。
不可重复读：指在一个事务内多次读同一数据，在这个事务还没有结束时，另一个事务也访问了该数据，在第一个事务的两次访问中间，第二个事务的访问修改导致第一个事务两次读取的数据不一样，因此称为不可重复读。
幻读：与不可重复读类似。

不可重复读与幻读的区别：

不可重复读：重点是修改。
幻读：重点在与新增或删除。

事务的隔离级别

读未提交：允许脏读取。当一个事务开始写操作，则另一个事务不允许同时进行写操作，但允许读此行数据。
读已提交：允许不可重复读取，但不允许脏读取。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
可重复读：不允许不可重复读和脏读取，但是有时可能出现幻读。读取数据的事务会禁止写事务（但允许读事务），写事务禁止其他任何事务。
序列化：提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个的执行，但不能并发执行。

注：事务的隔离等级越高，数据的完整性和一致性越有保证，但对并发操作影响也越大。MySQL事务的默认隔离级别是可重复读。

4、索引

索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据表中的特定信息，如同一本书的目录一样，可以加快查询的速度。InnoDB存储引擎的所有底层实现数据结构是B+树，所有数据都存储在B+树中。

MySQL索引使用的数据结构

BTree索引：使用的是B树中的B+树。
哈希(hash)索引：使用哈希表，单条记录查询时，使用哈希索引，查询性能最快。

为什么底层数据结构使用B+树，而不是B树
- B+树是B树的变种，B+树的非叶子节点只用来存储索引，不存储数据，所有数据都保存在叶子节点；而B树的非叶子节点也会保存数据。这样就使得B+树的查询效率更加稳定，均为从根节点到叶子节点的路径。
- B+树的内部节点没有指向关键字具体信息的指针，因此其内部节点相对B树更小，同样的空间可以读入更多的节点，所有B+树的磁盘读写代价更低。
聚簇索引和非聚簇索引
- 聚簇索引：也称为主键索引，其索引树的叶子节点中存储的是整行数据，表中行的物理顺序与键值的索引顺序相同。
- 非聚簇索引（普通索引）：叶子节点内容是主键的值，非主键索引也被称为二级索引。

MySQL回表

对应普通索引查询时，需要先搜索普通索引树，得到其对应的主键值，在通过主键索引树搜索一次

覆盖索引

如果普通索引树上的查询已经可以直接提供结果，不需要回表操作，这样的普通索引叫做覆盖索引。
覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

索引的最左前缀原则

在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀（可以是联合索引的最左N个字符，也可以是字符串索引的最左M个字符），就可以利用索引加快查询的速度。是常见的MySQL性能优化手段。

索引下推

在MySQL5.6前，只能根据最左前缀查询到ID开始一个个回表，到主键索引上找出数据行，再对比字段值。MySQL5.6引入索引下推优化，联合索引前提下可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤不满足条件的记录，减少回表次数，提高查询效率。

MySQL常见的存储引擎

InnoDB：支持事务、支持行级锁和表级锁（默认行级锁）、支持外键、可以处理巨大数据量时的最大性能设计的存储引擎、不支持全文索引（FULLTEXT）。
MyISAM：不支持事务、不支持外键、支持表级锁、支持全文索引。

MySQL基本逻辑架构图

图片说明

MySQL逻辑架构包括Server层和存储引擎层。其中Server层包括连接器、分析器、优化器、执行器；存储引擎包括多种支持的存储引擎：

连接器：验证客户端权限，建立和断开MySQL连接；
分析器：进行SQL语句的语法分析；
优化器：选择索引，生成具体的SQL语句执行计划；
执行器：操作存储引擎，执行SQL，返回执行结果；
存储引擎层：各个不同的存储引擎都提供一些读写接口来操作数据库。

5、锁机制

MyISAM和InnoDB存储引擎使用的锁：

MyISAM：表级锁（table-level locking）
InnoDB：行级锁（row-level locking）和表级锁，默认行级

表级锁和行级锁区别：

表级锁：对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低。
行级锁：只针对当前操作的行进行加锁，加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行锁又可以分为独占锁和共享锁。
- 独占锁（排他锁：Exclusive Lock）：
- 共享锁（S锁：Shared Lock）

InnoDB存储引擎的锁算法

Record lock：单个记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap锁定一个范围，包含记录本身

悲观锁和乐观锁

**悲观锁：**利用数据库的锁机制，在整个数据处理过程中都加入了锁，以保证排他性。
乐观锁：在操作数据的时候进行一个比较，按照当前事务中的数据和数据库表中的该数据是否一致来决定是否执行本次操作。

乐观锁的ABA问题：

问题描述: 指在当前事务读取该行数据时为A，经过别的事务修改为B，但是当前事务要更新数据的时候，该行数据又被其他事务修改为A，事实上该行数据是发生改变的，存在并发问题。

解决：通过基于数据版本记录机制来解决，也就是为数据增加一个版本标识，读取数据时，将版本号一同读出，之后更新时，对此版本号加1，根据当前事务的版本号与数据库中的版本号对比，来决定是否更新数据。

也可通过时间戳机制来解决ABA问题，通过时间戳来记录当前数据行变化。

池化设计思想

在连接池中，创建连接后，将其放在池中，再次使用时，不需要建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。

6、MySQL日志模块

在MySQL的使用中，更新操作是很频繁的，如果每一次更新操作都根据条件找到对应的记录，然后将记录更新，再写回磁盘，那么IO成本以及查找记录的成本都很高。

日志模块：我们的更新操作是先写日志，在合适的时间才回去写磁盘，日志更新完就将执行的结果返回给客户端。

MySQL的日志模块主要有redo log（重做日志）和binlog（归档日志）

**redo log：**InnoDB存储引擎特有的日志模块，是物理日志，记录某个数据页上做了哪些修改。InnoDB的redo log是固定大小的。
**binlog：**Server层自带的日志模块，binlog是逻辑日志，记录本次修改的SQL语句。binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。binlog日志文件的格式：
- statement格式：binlog记录的是完整的SQL语句，优点是日志文件小、性能较好；缺点是准确性差，例如函数now()
- row格式：binlog中记录的是数据行的实际数据变更，优点是数据记录准确，缺点是日志文件较大。
- mixed格式：前两种的混合模式。

日志模块更新数据库：

通过redo log和binlog的两阶段提交，可以确保数据库异常崩溃之后数据的正常恢复。

参考文献：

1、【狂神说Java】MySQL最新教程通俗易懂：https://www.bilibili.com/video/BV1NJ411J79W?p=1

2、https://gitee.com/SnailClimb/JavaGuide/blob/master/docs/database/MySQL.md

3、牛客专刊：我是祖国的花朵https://www.nowcoder.com/tutorial/10014/index

徐梓泳

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础总结

MySQL基础总结1、安装MySQL环境https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484182&idx=1&sn=6a04b10ebac95da1a66abcefa1fa442c&scene=19#wechat_redirect2、操作数据库的基本命令# 修改密码update user set password = password('123456')where user='root';
复制链接

扫一扫