MySQL基础知识

最新推荐文章于 2025-03-11 21:54:00 发布

little猫钓鱼

最新推荐文章于 2025-03-11 21:54:00 发布

阅读量271

点赞数

本文链接：https://blog.youkuaiyun.com/xizonghu/article/details/106500962

版权

文章目录

前言

问题：
- 数据怎么存，为什么要这么存，如何不丢数据？
- 数据怎么查？
- 数据怎么保证事务（与文件系统的最大区别，目前NewSQL解决的最重要的问题）？
存储模型是：B Tree。然后被组织为table, table space, database
事务的本质其实是并发和锁（一般的关系数据库的实现），锁的粒度关系到一致性的表现和性能，所以MVCC（朴素的理解为copyOnWrite）带来了性能上的提升，数据库的操作说白了就有两种，read write,所有发生的事情都是这些操作的组合（操作有先后，有时序），RR, RW, WR, WW，数据库的性能就是如何最大程度保证两类操作的并发性。
如果真的想深入的了解数据库，不仅仅只是停留在应用层面，我想不外乎几个方面：并发控制，数据库日志系统恢复技术，查询调度以及优化(逻辑查询计划以及物理查询计划)，如何保障高可用和高性能。希望能带来更多的思考。

基础知识

主键、外键、超键、候选键¹
- 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
- 候选键：是最小超键，即没有冗余元素的超键。
- 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）
- 外键：在一个表中存在的另一个表的主键称此表的外键。
存储过程：

存储过程是一个经过预编译的SQL语句块。放在数据库中，比单纯SQL语句执行要快，但不灵活。
通过命令对象、外部程序来调用存储过程名

触发器：

触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

视图：

是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作。通常是有一个表或者多个表的行或列的子集。

游标：

是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般情况下不使用游标，但如果需要逐条处理数据的时候，游标显得十分重要。

索引：

是数据库管理系统中一个排序的数据结构，由B树及其变种B+树实现。以某种方式引用（指向）数据，这样就可以在索引上实现高级查找算法。
是对数据库表中一个或多个列的值进行排序的结构。在创建表时指定创建。加速了查找，但额外花费空间和时间(数据变动时需随之变动)
索引通常选用唯一、不为空、经常被查询的字段

聚合索引(clustered index)：表记录的排列顺序和索引的排列顺序一致，查询效率快，记录在物理上连续存放。修改慢，会对数据页重新排序。
非聚合索引(nonclustered index)：指定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，
新华字典的正文是按a~z拼音。聚集索引相当于拼音索引。非聚集索引相当于偏旁索引。

事务：

是对数据库中一系列操作进行统一的回滚或者提交的操作。主要用来保证数据的完整性和一致性。

四大特性（ACID）：
原子性（Atomicity）：事务的所有操作要么全部成功，要么全部失败回滚。因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
隔离性（Isolation）：隔离性是当多个用户并发访问数据库时，相互不被干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
持久性（Durability）：事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务的并发?事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别?
从理论上来说, 事务应该彼此完全隔离, 以避免并发事务所导致的问题，然而, 那样会对性能产生极大的影响, 因为事务必须按顺序运行，在实际开发中, 为了提升性能, 事务会以较低的隔离级别运行，事务的隔离级别可以通过隔离事务属性指定。

事务的并发问题:
脏读：事务A读取了事务B更新（插入）的数据，然后B回滚操作，那么A读取的数据是脏数据。即尚未提交（commit）的数据，被其他的事务读取了。
不可重复读：事务A多次读取同一数据（同一条记录），事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读：事务A删除了数据，然后事务B插入了以删除的数据，事务A好像没有操作一样。查询的结果都是事务开始时的状态（一致性）。在同一个事务中，执行两次相同的SQL，得到不同的结果集，（新增了部分记录或者缺失了部分记录）（与不可重复读操作对象不一样，此处，不是同一条记录）。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务的隔离级别：要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持
读未提交(READ UNCIMMITTED)：写数据会锁住相应的行。引起：脏读、不可重复读、幻读
读提交(READ COMMITTED)：读写数据会锁住相应的行。引起：不可重复读、幻读
可重复读(REPEATABLE READ)：间隙锁。引起：幻读
可串行化(SERIALIZABLE)：读写数据都会锁住整张表。最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样
注意：隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

临时表

关键字TEMPORARY。只在当前连接可见，当关闭连接时自动删除并释放。

非关系型数据库和关系型数据库：

非关系型：NOSQL是基于键值对的，性能非常高。容易水平扩展。
关系型：基于表的。可以复杂查询。支持事务(安全)。

范式：

1NF：所有字段值都是不可分解的原子值。是最基本的范式。【不合起来存】
2NF：表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。【表中字段都和主键相关】
3NF：表中字段都和主键直接相关，而不能间接相关。
比如订单数表可以将客户编号作为一个外键和订单表建立相应的关系。但不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）。
BCNF：符合3NF，并且，主属性不依赖于主属性。
123NF描述起来就是，不能拆-要相关-要直接相关

连接：

内连接：只连接匹配的行
左外连接：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
右外连接：包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行。如SELECT a.,b. FROM luntan LEFT JOIN usertable as b ON a.username=b.username
全外连接：包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
交叉连接：生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配。如SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

char的长度是不可变的，而varchar的长度是可变的

SQL语言

SQL语言共分为四大类：

数据查询语言DQL
数据操纵语言DML
数据定义语言DDL
数据控制语言DCL。

数据查询语言DQL：

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块【三段论】

查询中用到的关键词主要包含六个，顺序依次为 select–from–where–group by–having–order by，其中select和from是必须的，执行顺序与语句书写不同，按下面的顺序来执行：
from:需要从哪个数据表检索数据。自右向左解析
where:过滤表中数据的条件。条件自下而上解析
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据

数据操纵语言DML：(可以回滚)

插入：INSERT
更新：UPDATE
删除：DELETE

数据定义语言DDL：(不可回滚)

操作各种定义结构：表、视图、索引、同义词、聚簇
CREATE
ALTER
DROP
TRUNCATE
如：CREATE TABLE(表)/VIEW(视图)/INDEX(索引)/SYN(同义)/CLUSTER(聚簇)
DDL操作是隐性提交的！不能rollback

数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

GRANT：授权。

ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。回滚—ROLLBACK；回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;

COMMIT [WORK]：提交。
在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：
显式提交：用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；
隐式提交：用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
自动提交：若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON；

通配符：

“%”：表示任何字符出现任意次数(可以是0次)
“_”：表示只能匹配单个字符
“like”：表示后面的搜索模式是利用通配符而不是直接相等匹配进行比较

count():

count(*)或count(1)对行的数目进行计算,包含NULL
count(column)对特定列的值具有的行数进行计算,不包含NULL值。

DELETE：

从表中删除一行。并且作为事务记录保存在日志中以便回滚。

TRUNCATE:

从表中删除所有行。且不可恢复，不激活触发器。执行速度快。

DROP:

删除表。

表连接

内连接(join)：

外间接(left,right)：有左连接(内容以左表为主)和右连接(内容以右表为主)

交叉连接(cross)：内容是连接表的笛卡尔积(3*3=9即笛卡尔积)

对于复杂、效率低的sql语句，我们通常是使用explain sql 来分析sql语句
回滚(rollback)的实现

数据库在写入数据之前是先讲对数据的改动写入 redo log 和 undo log，然后在操作数据，如果成功提交事务就会讲操作写入磁盘；如果失败就会根据redo log 和 undo log 逆向还原到事务操作之前的状态。

知识清单

需要对Mysql、Oracle、SqlServer这三个常用的数据库熟悉了解。
在数据量比较大或者请求数比较高的情况下，需要了解一些特定数据库针对性优化。
熟悉表、字段、记录、索引等概念。
熟悉SQL的约束，非空、默认值、唯一值等约束。熟悉主键、外键、检查约束等。
熟悉数据类型，文本，数字，日期/时间，二进制等。
熟练使用数据操作语言 (DML)，SELECT、UPDATE、DELETE、INSERT INTO等语句，这些数据操作语法，在多数功能需求中，是基本组成部分。
熟练使用数据定义语言 (DDL)，DATABASE的CREATE 、ALTER；TABLE的CREATE 、ALTER、DROP；INDEX的CREATE、DROP。是系统的数据库设计必需的定义语法。也会是系统迭代升级阶段常常进行的数据结构变更操作。
熟悉SQL连接的使用，INNER JOIN，LEFT JOIN，RIGHT JOIN，FULL JOIN，UNION，UNION ALL。
熟悉聚集函数的使用，sum，avg，count，max，min，以及group by和having 的配合。以及DISTINCT去重。
熟练使用子查询。
掌握数据库规范化，熟悉第一范式(1NF)，第二范式(2NF)，第三范式(3NF)。
熟悉数据库理论中数据模型，及关系模型。
完成模块功能需求的数据库设计。
熟练使用index优化查询性能。
了解存储过程、自定义函数、触发器、视图的使用。
熟悉程序对数据库事务处理。
了解各数据库对数字、字符串、时间的处理函数。
熟练使用PowerDesigner进行表结构设计。
熟练进行系统数据库设计。
了解各数据库对数据类型处理上的差异。
优化表的设计提高性能需求。
熟练使用分表分区等特性。
了解索引机制及类型。
熟练运用数据控制 (DCL)，GRANT、REVOKE。
熟练使用explain分析SQL的执行计划。数据库系统相关
主要针对常用的数据库，mysql，oracle，SQLServer。当然其它数据库的要求也类似。入门开发环境的数据库的安装配置。
熟练使用数据库对应的客户端，Mysql常用SQLyog，oracle常用的是PL/SQL Developer，SQLServer自带的客户端。另外通用的客户端navicat和Toad都比较强大。
程序连接数据库。
掌握了解各数据库系统的组成部分。
Mysql了解innodb文件组成，及结构。oracle的文件，表空间，数据块，区，段等。
了解各数据库的SQL的执行过程。熟练
熟悉各数据库的体系结构。
熟悉分表分库的方案。
了解集群的部署。如Oracle的RAC，MySQL集群的各种方案。
了解读写分离的方案（主备），了解binlog。
了解数据库系统层级的优化方案。