数据库基本概念
数据库基本概念
什么是事务
事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。
事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。
如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所有操作。
事务的 ACID
事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。
1 、原子性
事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
2 、一致性
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。
如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
A像B转账，不可能A扣了钱，B没有收到
3 、隔离性
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
比如A从一张银行卡取钱，在这个动作结束前，B不能向银行卡存钱
4 、持续性
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。
SQL 标准定义的四个隔离级别为
1、read uncommited ： (未提交读)read uncommited 事务中的修改，即使没有提交，对其他事务也都是可见的
2、read committed： (提交读)read commited 一个事务开始时，只能看到已经提交的事务所做的修改 这个级别有时候也叫做不可重复的（nonerepeatable read），因为两次执行同样的查询，可能不一样结果。
3、repeatable read： (可重复读)repeatable read 该级别保证了在同一个事务中多次读取同样的记录的结果是一致的，无法解决另一个幻读 （PhantomRead）的问题
4、serializable ： SERIALIZABLE(可串行化)serializable
事务的并发问题
- 脏读: 事务A读取了事务B更新的数据，然后事务B回滚，那么事务A读取的数据为脏数据
- 不可重复读: 事务A多次读取同一数据,事务B在事务A多次读取的过程中，对数据操作并提交，导致事务A读取到的数据不一致
- 幻读: 系统管理员A将学生成绩从具体分数划分为ABCDE等级，但是这个时候系统管理B又插入了一条具体分数的记录，当A操作结束后发现还有一条记录没有改过来，就好像发生了幻觉
不可重复读侧重于修改，幻读侧重于新增或者删除操作，解决不可重复读需要锁住满足条件的行，解决幻读是锁表
完整性约束包括哪些？
数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。
分为以下四类：
1、实体完整性： 规定表的每一行在表中是惟一的实体。
2、域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括 取值范围、精度等规定。
3、参照完整性： 是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
4、用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需 要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件， 它反映某一具体应用必须满足的语义要求。
与表有关的约束：包括列约束(NOT NULL（ 非空约束）)和表约束(PRIMARY KEY、foreign key、check、UNIQUE)
候选键 主键 可选键
1.候选键（candidate key）：一个或者多个属性的组合，能够唯一确定实体的一个实例。候选键可以是单列键，也可以是复合键
单键（Simple key）:主键如果是一个属性，成为单键
复合键（Composite key）： 主键如果是多个属性的组合，称为复合键
- 单列键
例如下列学生表中"学号"或"图书证号"都能唯一标识一个元组，则"学号"和"图书证号"都可作为学生关系的候选键 - 复合键
而在学生课代表的数据表中，只有属性组"学号"和"课程号"才能唯一地标识一个元组，则候选键为(学号，课程号)
2.主键（primary key）:从候选键中，选中用来作为唯一标识的属性或者属性组，被称为主键。（员工的编号可以作为主键）
3.可选键（alternative key）：候选键中，没有选中的其他键，可以称之为可选键。（员工电子邮件）
主键的特点
1.唯一性，不可重复
2.强制性，不可以为空
3.永久性，不可以改变
4.最小集合，不可以参杂多余的属性
主键和候选键有什么区别
表格的每一行都由主键唯一标识,一个表只有一个主键。
主键也是候选键。按照惯例， 候选键可以被指定为主键， 并且可以用于任何外键引用
外键
什么是外键
如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）
外键的作用
①为了一张表记录的数据不要太过冗余。
②保持数据的一致性、完整性
外键添加的四种方式
- 创建表的同时添加外键
create table score(
score int(3),
st_id int(16),
cs_id int(16),
primary key(st_id,cs_id),
FOREIGN KEY (st_id) REFERENCES student(id),
FOREIGN KEY (cs_id) REFERENCES classes(id)
);
```2. 已经创建表了怎么办：在表的定义外进行添加
```sql
alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
3.直接在属性值后面添加
create table score(
cscore int(11),
st_id int(50) references student(id),
cs_id int(30) references classes(id),
primary key(st_id,cs_id)
);
4.添加约束
create table score(
cscore int(11),
st_id int(50),
cs_id int(30),
primary key(st_id,cs_id),
CONSTRAINT `FK_ID_ST` FOREIGN KEY (st_id) REFERENCES student(id),
CONSTRAINT `FK_ID_CS` FOREIGN KEY (cs_id) REFERENCES classes(id)
);
MySQL常见基础问题
索引是通过以下方式为表格定义的：
SHOW INDEX FROM
模糊查询LIKE 后的％和_代表什么
通配符
％ 代表 0 或更多字符 % 包含零个或更多字符的任意字符串。
WHERE title LIKE ‘%computer%’
将查找处于书名任意位置的包含单词 computer 的所有书名
_ 代表 1 个字符，_（下划线） 任何单个字符。
WHERE au_fname LIKE ‘_ean’
将查找以 ean 结尾的所有 4 个字母的名字（Dean、Sean 等）
列对比运算符是什么
在 SELECT 语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND， OR 或 LIKE 运算符
NOW（）和 CURRENT_DATE（）有什么区别
NOW（） 命令用于显示当前年份， 月份， 日期， 小时， 分钟和秒。CURRENT_DATE（） 仅显示当前年份， 月份和日期
什么是通用 SQL 函数
1、CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
2、FORMAT(X, D)- 格式化数字 X 到 D 有效数字。
3、CURRDATE(), CURRTIME()- 返回当前日期或时间。
4、NOW（） – 将当前日期和时间作为一个值返回。
5、MONTH（）， DAY（ ）， YEAR（）， WEEK（）， WEEKDAY（） – 从日期值中提取给定数据。
6、HOUR（）， MINUTE（）， SECOND（） – 从时间值中提取给定数据。
7、DATEDIFF（ A， B） – 确定两个日期之间的差异， 通常用于计算年龄
8、SUBTIMES（ A， B） – 确定两次之间的差异。
9、FROMDAYS（ INT） – 将整数天数转换为日期值。
如何显示前 50 行？
在 MySQL 中， 使用以下代码查询显示前 50 行： SELECT*FROM TABLE LIMIT 0,50
MYSQL数据库锁的种类
在数据库系统中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。
1、行级锁
行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
2、表级锁
表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
3、页级锁
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁.表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
MySQL锁的特点分类
乐观锁
乐观锁:乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了
通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
举例：
下单操作包括3步骤：
1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods set status=2,version=version+1 where id=#{id} and version=#{version};
悲观锁
悲观锁:悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作,共享锁与排它锁都属于悲观锁的范畴
共享锁（Shared Lock），又称读锁。针对行锁
当有事务对数据加读锁后，其他事务只能对锁定的数据加读锁，不能加写锁（排他锁），所以其他事务只能读，不能写
加锁方式：
select * from T where id=1 lock in share mode;
释放方式：
commit、rollback;
排他锁（写锁）
排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。
与共享锁类型，在需要执行的语句后面加上for update就可以了
共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE
意向锁
意向锁是由数据引擎自己维护的，用户无法手动操作意向锁。
意向共享锁：IS锁，表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁：IX锁，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
表锁
– 查看哪些表被锁
show open TABLES
– 创建一个张表演示表锁，使用myisam存储引擎
CREATE table test_lock(
id int,
name VARCHAR(255)
)engine myisam;
给表加锁读 lock table xxx READ
lock table test_lock READ;
– 会阻塞自己和其他用户的update操作
insert into test_lock(id,name)VALUES(2,‘liyue’);
update test_lock set name=‘l’ where id=1;
SELECT * from test_lock;
–给表解锁
UNLOCK TABLES;
给表加独占写锁lock table xxx WRITE;
lock table test_lock WRITE;
– 会阻塞其他用户的update操作,对自己update后没有释放锁的情况下
– 只能在当前窗口查看不可打开一个新的窗口要不会视为一个新的session，由于锁被占用而无法查看
update test_lock set name=‘fi’ where id=1;
SELECT * from test_lock;
间隙锁（Next-Key锁）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
例：
假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：
mysql>
select * from emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的：
（1）防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；
（2）为了满足其恢复和复制的需要。
很显然，在使用范围条件检索并锁定记录时，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
MySQL常用存储引擎的锁机制
1、MyISAM和MEMORY:采用表级锁(table-level locking)
2、BDB:采用页面锁(page-level locking)或表级锁，默认为页面锁。
3、InnoDB:支持行级锁(row-level locking)和表级锁,默认为行级锁。在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁
Innodb中的行锁与表锁
Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！在实际应用中，要特别注意InnoDB行锁的这一特性，
不然的话，可能导致大量的锁冲突，从而影响并发性能。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。
行级锁与死锁
MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。
在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；
如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，
而且会锁定相邻的键值，即所谓的next-key locking。当两个事务同时执行，一个锁住了逐渐索引在等待其他相关索引，一个锁定了非主键索引，在等待主键索引。
这样就会发生死锁。发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。
如何避免死锁
有多种方法可以避免死锁，这里只介绍常见的三种，具体如下：
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
MySql中的存储引擎
MySql中的存储引擎有：1、MyISAM引擎；2、MyISAM Merge引擎；3、InnoDB引擎；4、memory引擎；5、archive引擎
- MyISAM引擎
这种引擎是mysql最早提供的，这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：
1）静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
2）动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
3）压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。
但是，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能 - MyISAM Merge引擎
这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。 - InnoDB引擎
InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。 - memory(heap)引擎
这种类型的数据表只存在于内存中。它使用散列索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。 - archive引擎
这种类型只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面
解释 MySQL 外连接、内连接与自连接的区别
- 自连接: 交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。
- 内连接 则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中， 即内连接只连接匹配的行。
- 外连接 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行， 这三种情况依次称之为左外连接， 右外连接， 和全外连接。
①左外连接， 也称左连接，左表为主表， 左表中的所有记录都会出现在结果集中， 对于那些在右表中并没有匹配的记录， 仍然要显示， 右边对应的那些字段值以NULL 来填充。
②右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，
③MySQL 目前还不支持全外连接
Myql 中的事务回滚机制概述
事务是用户定义的一个数据库操作序列， 这些操作要么全做要么全不做， 是一个不可分割的工作单位， 事务回滚是指将该事务已经完成的对数据库的更新操作撤销。
单个表事务回滚
多个表事务回滚
要同时修改数据库中两个不同表时， 如果它们不是一个事务的话， 当第一个表修改完， 可能第二个表修改过程中出现了异常而没能修改， 此时就只有第二个表依旧是未修改之前的状态， 而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候， 当第一个表修改完， 第二表修改出现异常而没能修改， 第一个表和第二个表都要回到未修改的状态， 这就是所谓的事务回滚
为表中得字段选择合适得数据类型
字段类型优先级: 整形>date,time>enum,char>varchar>blob,text
优先考虑数字类型， 其次是日期或者二进制类型， 最后是字符串类型， 同级别得数据类型， 应该优先选择占用空间小的数据类型
实践中如何优化 MySQL
最好是按照以下顺序优化：
1、SQL 语句及索引的优化
2、数据库表结构的优化
3、系统配置的优化
4、硬件的优化
优化数据库的方法
1、选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL， 例如’ 省份’、’ 性
别’ 最好适用 ENUM
2、使用连接(JOIN)来代替子查询
3、适用联合(UNION)来代替手动创建的临时表
4、事务处理
5、锁定表、优化事务处理
6、适用外键， 优化锁定表
7、建立索引
8、优化查询语句
2301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
