面试--数据库（一）

https://www.cnblogs.com/huanongying/p/7021555.html这是最好的帖子没有之一。

https://blog.csdn.net/xiaomingdetianxia/article/details/72475924点击打开链接

https://www.cnblogs.com/LipeiNet/p/6099849.html

1事物

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，由一条或者多条sql语句组成，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。ACID

原子性（Atomicity）：指整个数据库事务是不可分割的工作单位。只有事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个sql语句执行失败，那已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态。 
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束，也就是说在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏 
隔离性（Isolation）：隔离性也叫做并发控制、可串行化或者锁。事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见，这通常使用锁来实现多个事务并发执行时，一个事务的执行不应影响其他事务的执行。 
持久性（Durability）：表示事务一旦提交了，其结果就是永久性的，也就是数据就已经写入到数据库了，如果发生了宕机等事故，数据库也能将数据恢复。

2隔离级别https://blog.csdn.net/qq_33290787/article/details/51924963点击打开链接

 未提交读：事物种的修改即使没有提交，对其它事物也是可见的。一个事务可以读取另一个未提交事务的数据

提交读：一个事务只能读取已经提交的事务所做的修改。就是一个事务要等另一个事务提交后才能读取数据

可重复读：保证在同一个事务中多次读取同样数据的结果是一样的。就是在开始读取数据（事务开启）时，不再允许修改操作

可串行读：强制事务串行执行。

mysql默认的方式是提交读

3范式

https://blog.csdn.net/douunderstand/article/details/70159540点击打开链接

1NF：属性不可分。

2NF:在第一范式的基础上，要求非主属性都要和码有完全依赖关系 。所谓完全依赖是指不能存在仅依赖码一部分的属性，必须是依赖全部属性。（区别于部分依赖） 或者说：.首先满足第一范式，并且表中非主键列不存在对主键的部分依赖。

3NF：第三范式：任何非主属性不依赖于其它非主属性。 3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。

4存储引擎：   InnoDB和MyISAM    Innodb的特性，缓存。

比较：点击打开链接

• 事务：InnoDB 是事务型的，可以使用 Commit提交 和 Rollback回滚 语句。

• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

• 外键：InnoDB 支持外键。MyISAM不支持外键。

• 备份：InnoDB 支持在线热备份。

• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

• 其它特性：MyISAM 支持压缩表和空间数据索引。

• CRUD操作：如果执行大量的SELECT，MyISAM是更好的选择。 如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

InnoDB 是 MySQL 默认的事务型存储引擎，只有在需要 InnoDB 不支持的特性时，才考虑使用其它存储引擎。采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ），并且通过间隙锁（next-key locking）策略防止幻影读。表是基于聚簇索引建立的，它对主键的查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够自动在内存中创建哈希索引以加速读操作的自适应哈希索引、能够加速插入操作的插入缓冲区等。通过一些机制和工具支持真正的在线热备份。

MyISAM 提供了大量的特性，包括压缩表、空间数据索引等。不支持事务。不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取查询的同时，也可以往表中插入新的记录，这被称为并发插入。

5索引

https://blog.csdn.net/douunderstand/article/details/70198755点击打开链接

一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。

整个表变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置

物都是有两面的， 索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的， 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销。

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据

 数据库中的索引类似于一本书的目录，在这本书中使用目录可以快速找到我们想要的信息，而不需要读完整本书。在数据库中，数据库程序使用索引可以查询表中的数据，而不必扫描整个表。数据库中的索引是表中值以及各值存储位置的列表。

索引能够轻易将查询性能提升几个数量级。索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

在数据库系统中建立索引主要有以下作用： 

　（1）快速过滤数据，加快我们要找到的内容； 
　（2）保证数据记录的唯一性； 
　（3）实现表与表之间的参照完整性； 
　（4）在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

优点 
　1.大大加快数据的检索速度; 
　2.创建唯一性索引，保证数据库表中每一行数据的唯一性; 
　3.加速表和表之间的连接; 
　4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。 
缺点 
　1.索引需要占物理空间。 
　2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

索引分类https://blog.csdn.net/douunderstand/article/details/70215061聚集索引

http://blog.codinglabs.org/articles/theory-of-mysql-index.html点击打开链接

sELECT * FROM test_tab WHERE name = 一个外部输入的数据，刚开始，数据不多的时候，执行效果还不错。
随着数据量的增加，这个查询，执行起来，越来越慢了。
然后在 name 上面 建立了索引：CREATE INDEX idx_test4_name ON test_tab (name );
这样， 可以加快前面那个查询的速度。但是，某天，你执行了下面这个SQL， 发现速度又慢了
SELECT * FROM test_tab WHERE age = 25
为啥呢？ 因为 age 字段上面，没有索引，索引只在 name 上面有
换句话说， 也就是 WHERE 里面的条件， 会自动判断，有没有 可用的索引，如果有， 该不该用。
多列索引，就是一个索引，包含了2个字段。
例如：CREATE INDEX idx_test_name_age ON test_tab (name, age);那么SELECT * FROM  test_tab WHERE name LIKE '张%'AND age = 25
这样的查询，将能够使用上面的索引。多列索引，还有一个可用的情况就是， 某些情况下，可能查询，只访问索引就足够了， 不需要再访问表了。

例如：SELECTAVG ( avg ) AS 平均年龄FROM test_tab WHERE name LIKE '张%'

这个时候， name 与 age 都包含在索引里面。 查询不需要去检索表中的数据

 

 

（1）唯一索引 

唯一索引是不允许其中任何两行具有相同索引值的索引。 
例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。创建索引方法

CREATE UNIQUE INDEX 索引名称 ON 表名（列名）;

（2）主键索引 
在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。

（3）普通型索引 
这是最基本的索引类型，而且它没有唯一性之类的限制。创建方法：

CREATE INDEX 索引名称 ON 表名（列名）;

（4）前缀索引 
有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

CREATE INDEX 索引名称 ON 表名（列名（n））;

B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。 
HASH 索引：只有Memory引擎支持，使用场景简单。 
R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。 

 

Full-text (全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本提供对全文索引的支持。

 

 

 

B-Tree

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度。h为一个正整数，称为B-Tree的高度。

每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。

所有叶节点具有相同的深度，等于树高h。

key和指针互相间隔，节点两端是指针。

一个节点中的key从左到右非递减排列。

所有节点组成树结构。

每个指针要么为null，要么指向另外一个节点。

如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)v(key1)，其中v(key1)v(key1)为node的第一个key的值。

如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)v(keym)，其中v(keym)v(keym)为node的最后一个key的值。

如果某个指针在节点node的左右相邻key分别是keyikeyi和keyi+1keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)v(keyi+1)且大于v(keyi)v(keyi)。

图2是一个d=2的B-Tree示意图。

图2

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

 

B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

每个节点的指针上限为2d而不是2d+1。

内节点不存储data，只存储key；叶子节点不存储指针。

图3是一个简单的B+Tree示意。

图3

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。

一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关，将在下面讨论。

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

图4

如图4所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

图8

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

图9

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

图10

图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

图11

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

 

 

锁：并发情况下处理。
数据库链接池 c3p0，dru
引擎indb可以建索引 支持事物，my不可以
innodb缓存池  核心机制（三个）：两次写。。。
写一些查询 sql语句
sql优化