数据库

最新推荐文章于 2021-03-22 15:13:28 发布

pmt123456

最新推荐文章于 2021-03-22 15:13:28 发布

阅读量644

点赞数

关系型数据库

数据库之理解超键、候选键、主键、主属性、外键

范式

MySQL查询语句练习题(面试时可能会遇到哦！)

SQL语句中的Having子句与where子句

SQL的四种连接-左外连接、右外连接、内连接、全连接

mysql linux命令

获取前几行

1、查询第一行记录： 
select * from table limit 1 
2、查询第n行到第m行记录 
select * from table1 limit n-1,m-n; 
SELECT * FROM table LIMIT 5,10；返回第6行到第15行的记录 
select * from employee limit 3,1; // 返回第4行 
3、查询前n行记录 
select * from table1 limit 0,n; 
或 
select * from table1 limit n; 
4、查询后n行记录 
select * from table1 order by id desc dlimit n；//倒序排序，取前n行 id为自增形式 
5、查询一条记录($id)的下一条记录 
select * from table1 where id>$id order by id asc dlimit 1 
6、查询一条记录($id)的上一条记录 
select * from table1 where id<$id order by id desc dlimit 1

1、超键、候选键、主键、外键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键。

超键、候选键、主键区别

2、什么是事务？什么是锁？

事务：事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。通过事务，SQL能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过 ACID 测试，即原子性，一致性，隔离性和持久性。

1.开启事务

START TRANSACTION 或 BEGIN

2.提交事务（关闭事务）

COMMIT

3.放弃事务（关闭事务）

ROLLBACK

4.折返点

SAVEPOINT adqoo_1
ROLLBACK TO SAVEPOINT adqoo_1

发生在折返点 adqoo_1 之前的事务被提交，之后的被忽略
5.事务的终止
设置“自动提交”模式
SET AUTOCOMMIT = 0
每条SQL都是同一个事务的不同命令，之间由 COMMIT 或 ROLLBACK隔开
掉线后，没有 COMMIT 的事务都被放弃

mysql事物处理实例

MYSQL的事务处理主要有两种方法
1.用begin,rollback,commit来实现
    begin开始一个事务
    rollback事务回滚
    commit 事务确认
2.直接用set来改变mysql的自动提交模式
    mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过
    set autocommit = 0 禁止自动提交
    set autocommit = 1 开启自动提交

事务的隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

未提交读：所有事物都可以看到未提交事物的执行结果，可能产生脏读

已提交读：一个事物在开始时，只能看见已提交事物所做的改变

可重复读：确保同一事务的多个实例在并发读取数据时，会看到同样的数据行

可串行化：通过强制事务排序，使之不能相互冲突，从而解决幻读问题。

脏读：一个事务读取到了另外一个事务没有提交的数据；
比如：事务T1更新了一行记录的内容，但是并没有提交所做的修改。事务T2读取到了T1更新后的行，然后T1执行回滚操作，取消了刚才所做的修改。现在T2所读取的行就无效了；
不可重复读：在同一事务中，两次读取同一数据，得到内容不同；
比如：事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化；
幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同；
比如：事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然出现的一样。

SET SQL_SAFE_UPDATES=0;  
update books set free =1, new=1;  
commit;  #提交
SET AUTOCOMMIT=1; #开启自动提交 
begin;   #事务
select free, new from books limit 1;  
update books set free = 0;  
select free, new from books limit 1;  
SAVEPOINT book1;  #point
update books set new = 0;  
select free, new from books limit 1;  
rollback to book1;  #回滚
select free, new from books limit 1;  
rollback;  #回滚
select free, new from books limit 1;

数据库事务的四个特性及含义

①原子性(Atomicity)：事务中的所有元素作为一个整体提交或回滚，事务的个元素是不可分的，事务是一个完整操作。
②一致性(Consistemcy)：也就是说一致性指事务将数据库从一种状态转变为另一种一致的状态，在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；
③隔离性(Isolation)：对数据进行修改的多个事务是彼此隔离的。一个书屋只有在完成之后才对其他事物可见。
④持久性(Durability)：事务完成之后，它对于系统的影响是永久的，该修改即使出现系统故障也将一直保留，真实的修改了数据库

3、什么是锁？

锁：在所以的 DBMS 中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

除了事务来处理以上不可重复读、幻读、脏读等问题。还有锁也能解决上述问题。在讲解锁之前，需要对常见的几个锁的名词有一个感性的认识。

1.排它锁：
由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁，典型是mysql事务中。也就是既不能对表查询，也不能对表进行修改。

SELECT ... FOR UPDATE;

在查询语句后面增加 FOR UPDATE ，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

select actor_id,first_name,last_name from actor where actor_id = 178 for update;

2.共享锁：

由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写

SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加 LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode;#共享锁

读锁是共享锁，支持并发读，写操作被锁。
写锁是独占锁，上锁期间其他线程不能读表或写表。

4、什么是视图？

视图是从一个或几个基本表（视图中）导出的虚拟表，数据库中至存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

特点：a. 虚表是从一个或几个基本表（或视图）导出的表；b.只存放视图的定义，不会出现数据冗余；c.基本表中的数据变化，视图中查询出的数据也随之变化

视图的优点
1 - 简化用户操作

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使用户不必为以后的操作每次都指定全部的条件。
2- 对重构数据库提供了一定程度上的逻辑独立性

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。
3- 安全性

通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上。

视图的缺点
1 - 性能：

SQL Server必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，SQL Server也把它变成一个复杂的结合体，需要花费一定的时间。
2- 修改限制：

当用户试图修改视图的某些行时，SQL Server必须把它转化为对基本表的某些行的修改。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的。

数据库视图详解

5、触发器的作用？

存储过程

是一组T-SQL语句的集合，被集合在一起以完成一个特定的任务，经编译后放在数据库服务器端。能够向用户返回数据、向数据表中写入或修改数据，还可以执行系统函数和管理操作，用户在编程中只需要给出存储过程的名称和必需的参数，就可以方便的调用。

优点：

1、存储过程只在创造时进行编译，以后每次执行存储过程都不需要重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。

2、当对数据库进行复杂操作时，可见此复杂操作用存储过此封装起来，以后直接调用。

3、存储过程可以重复使用，可减少数据库开发人员的工作量

4、安全性高，可设定只有某用户才具有对指定存储过程的使用权。

Mysql中的存储过程

和自定义函数的区别：

1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。

3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。

4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

触发器

触发器是一种特殊的存储过程，不由用户直接调用，是由事件来触发某个操作，这些事件包括insert语句，update语句和delete语句，当数据库系统执行这些事件时，就会激活触发器执行相应的操作，这样做可以保证某些操作之间的一致性。例如，当学生表中增加来一个学生信息时，学生总数就必须同时改变，可以在这里创建一个触发器，每次增加一个学生的记录，就执行一次计算学生总数的操作，这样就可以保证在每次增加学生的记录后，学生总数是与记录数是一致的，触发器触发的执行语句可能只有一个，也可能由多个。

6、维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

尽可能使用约束，如 check, 主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

尽管触发器有很多优点，但是在实际的项目开发中，特别是OOP思想的深入，触发器的弊端也逐渐突显，主要：

1、过多的触发器使得数据逻辑变得复杂

2、数据操作比较隐含，不易进行调整修改

3、触发器的功能逐渐在代码逻辑或事务中替代实现，更符合OO思想。

建议：
使用触发器需慎重。

7、索引的作用？和它的优点缺点是什么？

数据库索引，是对数据库表中一列或多列的值进行排序的数据结构，可以提高查询的速度，通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。索引的实现通常使用B树及其变种B+树。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

删除索引的语法

DROP INDEX [indexName] ON mytable;

优点：

可以提高检索数据的速度；

对有依赖关系的子表和父表之间的联合查询时，可以提高查询速度

使用分组和排序子句进行数据查询时，同样可以显著节省分组和排序的时间

缺点：

创建和维护索引需要耗费时间，耗费的数量随着数据量的增加而增加；

索引需要占据物理空间；

一般来说，应该在这些列上创建索引：

（1）在经常需要搜索的列上，可以加快搜索的速度；

（2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

（3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

（4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

（5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

（6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

7.数据库范式

函数依赖

范式：满足特定要求的模式

不同级别的范式要求各不相同

范式可以作为衡量一个关系模式好坏的标准

若关系模式R满足xNF，记R∈xNF

规范化：将低一级范式的关系模式通过模式分解转换为高一级范式的关系模式集合的过程

规范化：
1、第一范式（1NF）
如果一个关系模式R的所有属性都是不可分的基本数据项，（即对于关系模式R的任一实例，其元祖的每一个属性值都只含有一个值），则R∈1NF。

1NF是关系的基本要求，否则不能称其为关系数据库。

2 第二范式（2NF）
若关系模式R∈1NF，且每一非主属性完全函数依赖于R的码，则R∈2NF

3 第三范式（3NF）
关系模式R（U，F）中，当且仅当R属于1NF，且R中的每一个非主属性都不传递依赖于主码时，R∈3NF

4 BC范式

在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合第三范式。

7.mysql体系结构

（1） Connectors指的是不同语言中与SQL的交互
（2）Management Serveices & Utilities：系统管理和控制工具，例如备份恢复、Mysql复制、集群等
（3）Connection Pool（ 连接池 ）：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求
（4）SQL Interface:（SQL接口）：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
（5）Parser（解析器）：SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本，主要功能：
a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
（6）Optimizer（查询优化器）：SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果

（7） Cache和Buffer（高速缓存区）：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
通过LRU算法将数据的冷端溢出，未来得及时刷新到磁盘的数据页，叫脏页。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

（8）Engine ：存储引擎。存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。

Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）
现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB
默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。

InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。

Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

8、mysql存储引擎

一、存储引擎：
1、存储引擎其实就是如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。
2、MySQL中的数据用各种不同的技术存储在文件（或内存）中，这些技术中的每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

mysql的存储引擎可以分为官方存储引擎和第三方存储引擎

注：插件式存储引擎：插件式表存储引擎是底层物理结构的实现，负责为数据库执行实际额数据I/O操作，它是基于表而不是数据库的，可以根据实际应用需求为每个表设定不同的选择。

插件式存储引擎的核心是文件访问层的一个抽象接口，任何人都可以利用这个API接口去简历新的文件访问机制。

二、MySQL 中查看引擎

1、show  engines;   #查看mysql所支持的存储引擎，以及从中得到mysql默认的存储引擎。
2、show  variables  like '% storage_engine';    #   查看mysql 默认的存储引擎

3、show  create   table  tablename ;   //    查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！

4、show  table   status  from  database  where  name="tablename"#准确查看某个数据库中的某一表所使用的存储引擎

9、Myisam和InnoDB的区别

InnoDB支持外键，MyISAM不支持；
InnoDB支持事务处理，MyISAM不支持；
InnoDB是行锁（默认隔离级别为可重复读，在这个隔离级别上使用了间隙锁防止“幻读”），MyISAM是表锁；
MyISAM是默认的存储引擎，强调性能
inoodb支持MVCC机制获取高并发性能
MyISAM支持全文索引和空间索引

MyISAM和InnoDB的区别

MyISAM和innodb索引的区别

点击打开链接

1、MyISAM：非聚簇索引，叶子节点的data域保存数据记录的地址，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

2、Innodb：primary index：data域保存了完整的数据记录，

secondary index：data域存储相应记录主键的值而不是地址。但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得

1、MyISAM自身只缓存说索引，没有数据；

2、InnoDB的缓存机制与MyISAM不相同，该缓存池不仅用来缓存InnoDB的索引快，而且也用来缓存InnoDB的数据块

1、MyISAM通常在多次写入之后就会把索引的改变刷写到磁盘上

2、InnoDB比MyISAM复杂，它使用日志来减少提交事务的开销，不是每次在事务提交时就把缓冲池刷写的磁盘上，而是记录了事务日志。

（1）将数据写入到InnoDB buffer pool，并对相关记录加独占锁

（2）将UNDO信息写入到undo表空间的回滚当中

（3）更改缓存页中的数据，并将更新记录写入redo buffer中

（4）提交时，根据innoDB_redo_log_file_at trx_commit的设置，用不同的方式将redo buffer中的更新刷新到innoDB redo log file中，然后释放独占锁；

（5）最后，后台IO携程根据需要将缓存中更新过的数据刷新到磁盘文件中

1、MyISAM的表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁

2、InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，有可能发生死锁

10、分析mysql执行慢

mysql 方法或者存储过程执行慢的调试方法

1、设置慢查询日志

2、通过mysqldumpslow命令分析慢查询日志

3、通过show profile来查看sql的执行情况

4、通过explain查看执行计划
EXPLAIN SELECT 。。。

11、存在索引但不能使用索引的情况

（1）以%开头的like查询不能够引用B-Tree索引

select * from actor where last_name like '%NI%';

（2）数据类型出现隐式转换的时候不会使用索引

eg actor表中last_name是字符型，但SQL语句条件中1是一个数值类型

select * from actor where last_name=1;//不使用索引
select * from actor where last_name='1';//使用索引

（3）复合索引下，假如查询条件不包含索引列最左边部分，即不满足最左原则

（4）如果MySQL估计使用索引比全表扫描更慢，则不会使用索引。

（5）用or分隔开的条件，如果or前的条件的列有索引，而后面的列中没有索引，则涉及的索引都不会被用到。

12、优化order by语句

分析：MySQL有两种排序方式（1）通过有序索引顺序扫描直接返回有序数据，不需额外排序；（2）通过对返回数据进行排序，即Filesort排序

优化目标：尽量减少额外的排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。否则肯定需要额外的排序操作；

优化：1、创建适当的索引；

2、filesort有一次扫描算法和两次扫描算法：一次扫描算法较快但内存开销大，二次扫描算法反之；

适当增大sort_buffer_size

尽量只是用必要的字段，select具体的字段名称，而不是select*,可以减少排序区的使用，提高SQL性能

13、多版本并发控制MVCC

MVCC是通过及时保存在某些时刻的数据快照，而得以实现的，这意味着同一事务的多个实例，在同时运行时，无论每个实例运行多久，他们看到的数据视图是一致的；二同一时间，对于同一张表，不同事务看到的数据却是不同的。

InnoDB通过为每个数据行增加两个隐含值的方式来实现MVCC。这两个隐含值记录了行的创建时间，以及过期时间。每一行都存储了事件发生时的系统版本号，用来替代事件发生时的实际时间。每一次，开始一个新事务时，版本号都会自动递增。每个事务都会保存它在开始时的“当前系统版本”的记录，而每个查询都会根据事务的版本号，检查每行数据的版本号。

MVCC只工作在repeatable read和read commited两个隔离级别

eg-当事务隔离级别为repeatable read时，MVCC在实际操作中的应用方式

select：innodb检查每行数据，确保他们符合两个标准：

1.innodb值查找版本早于当前事务版本的数据行（当前数据行的版本小于等于事务的版本），确保了当前事务读取的行都是在事务开始前已经存在的。

2.数据的删除版本必须是未定义的，或是大于事务版本的，这保证了事务读取的行，在事务开始时是未被删除的

只有通过上述两项测试的数据行，才会被当做查询结果返回

insert：innodb为每个新增加航记录当前系统版本号

delete：innodb为每个删除行记录当前系统版本号，作为行删除标识

update：innodb会为每个需要更新的行，建立一个新的行拷贝，并且为新的行拷贝，记录当前的系统版本号。同时，也为更新前的旧行，记录系统的版本号，作为旧行的删除版本标识。

14、当GROUP BY 与ORDER BY 一起使用时，ORDER BY 子句中的列必须包含在聚合函数或 GROUP BY 子句中。

15、锁

（1）MyISAM表锁

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作（update、delete、insert）前，会自动给涉及的表加写锁，并不需要用户干预。

显示加锁（一般是为了在一定程度模拟事务操作）

lock tables tablename write;
lock tables tableaname read;
unlock tables;

#用lock tables给表显示加锁时，必须同时取得所有涉及表的锁，且MySQL不支持所升级，因此在lock tables后，只能访问显示加锁的这些表，不能访问为加锁的表；同时，如果加的是读锁，只能执行查询操作，而不能执行更新操作。
#在自动加锁的情况下也是如此，MyISAM总是一次获得SQL语句锁需要的全部锁——MyISAM不会死锁的原因。
#MyISAM是写锁优先的，大量的更新操作会造成查询操作阻塞，因此适合与有大量更新和查询操作。

（2）InnoDB

共享锁

select * from tablename where ... lock in share mode;

排它锁

select * from tablename where  ... for update

InnoDB是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来加锁。如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁

record lock：对索引项加锁

gap lock：对索引项之间的间隙加锁（幻读）

next_key lock：前两项的组合

pmt123456

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库

索引：http://blog.csdn.net/liujiahan629629/article/details/7803929
复制链接

扫一扫