数据库 (2019.4.9)

最新推荐文章于 2024-10-28 10:50:40 发布

赵小雷

最新推荐文章于 2024-10-28 10:50:40 发布

阅读量459

点赞数 1

分类专栏：复习文章标签：数据库面试

本文链接：https://blog.youkuaiyun.com/hushuo0802/article/details/89087942

版权

复习专栏收录该内容

13 篇文章

订阅专栏

前言

本博客为个人复习时总结用，无商业目的，其大多数内容皆为博主整理所得，并非原创。侵删。

事务并发执行

不加锁，会引起的四个问题：（线程不加锁也会因此这四个问题）

丢失修改
事务T1，T2读取同一个数据，之后先后将修改的内容，写回数据库，会导致一个事务丢失修改。
例子：
数据a = 1；
T1， T2对a加1，先后将读取a的之后，又先后将2写进a，这样导致丢失修改。正确的a的值应该是3。
脏读
T1修改某个数据a，这时T2去读，之后T1撤销事务，a回到原来的值，这是T2读到的a的值就是一个错误的值，即脏数据。
读到了未提交
不可重复读
T1读取了一个数据之后，之后T2修改了这数据，T1在读这个数据，发现和之前读的不相同。
T1支付时查询5000，此时另一个人（T2）修改，T1支付时，金额变少了。
幻读
T1按照某个条件从数据库中查找出了某些数据，之后T2对表的记录进行插入和删除，T1在按相同的条件从数据库中，查找数据，发现记录条数多了或者少了，就像出现幻觉一样。
T1读取id为‘1’，如果不存在，就插入，此时T2插入id=‘1’，则T1失败。

数据库的索引

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树

Mysql的隔离级别

READ UNCOMMITTED（读取未提交）：最低级别的隔离，通常又称为dirty read，它允许一个事务读取还没commit的数据，这样可能会提高性能，但是dirty read可能不是我们想要的，这个很少用到，因为四种并发问题都阻止不了
READ COMMITTED：在一个事务中只允许已经commit的记录可见，如果session中select还在查询中，另一session此时insert一条记录，则新添加的数据不可见。
REPEATABLE READ：在一个事务开始后，其他session对数据库的修改在本事务中不可见，直到本事务commit或rollback。在一个事务中重复select的结果一样，除非本事务中update数据库。

可重复读和幻读
在可重复读中，该sql第一次读取到数据后，就将这些数据加锁（悲观锁），其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来实现。

SERIALIZABLE：最高级别的隔离，只允许事务串行执行。为了达到此目的，数据库会锁住每行已经读取的记录，其他session不能修改数据直到前一事务结束，事务commit或取消时才释放锁。

事务

原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

mysql 事务操作
ABCD 一个事务

  Connection conn = null;
  try{
    //1 获得连接
    conn = ...;
    //2 开启事务
    conn.setAutoCommit(false);
    A
    B
    C
    D
    //3 提交事务
    conn.commit();
  } catche(){
    //4 回滚事务
    conn.rollback();
  }

mysql 事务操作–Savepoint(保存点)需求：AB（必须），CD（可选）

  Connection conn = null;
  Savepoint savepoint = null;  //保存点，记录操作的当前位置，之后可以回滚到指定的位置。（可以回滚一部分）
  try{
    //1 获得连接
    conn = ...;
    //2 开启事务
    conn.setAutoCommit(false);
    A
    B
    savepoint = conn.setSavepoint();
    C
    D
    //3 提交事务
    conn.commit();
  } catche(){
    if(savepoint != null){   //CD异常
       // 回滚到CD之前
       conn.rollback(savepoint);
       // 提交AB
       conn.commit();
    } else{   //AB异常
       // 回滚AB
       conn.rollback();
    }
  }

语句

drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。
have
groupby

索引

一般来说，应该在这些列上创建索引：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

B+树

innodb中索引的组织形式是B+树，非叶子节点存key，叶子节点存key+data，叶子节点之间用指针联通
B树（B-tree）是一种树状数据结构，它能够存储数据、对其进行排序并允许以O(log n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。

根结点至少有两个子女。
每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m
每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m
所有的叶子结点都位于同一层。
每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

三范式

第一范式就是无重复的列。
第二范式就是非主属性非部分依赖于主关键字。
第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）

SQL语句优化

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。（!=与<>都是不等于）
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
用Where子句替换HAVING 子句因为HAVING 只会在检索出所有记录之后才对结果集进行过滤.
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，
如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20
in 和 not in 也要慎用，否则会导致全表扫描，

如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

下面的查询也将导致全表扫描：select id from t where name like '李%'若要提高效率，可以考虑全文检索。

避免在索引列上使用计算，也就是说，应尽量避免在 where 子句中对字段进行表达式操作和函数操作，这将导致引擎放弃使用索引而进行全表扫描。

select id from t where num/2=100
应改为:select id from t where num=100*2

select id from t where substring(name,1,3)='abc' ，name以abc开头的id
应改为:select id from t where name like 'abc%'

很多时候用 exists 代替 in 是一个好的选择：exists用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false。
```
select num from a where num in(select num from b)

用下面的语句替换：select num from a where exists (select 1 from b where num=a.num)
```
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
用>=替代>
高效: SELECT * FROM EMP WHERE DEPTNO >=4
低效: SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。
分表
冗余存储
子查询而不是外连接

参考博客：
https://blog.youkuaiyun.com/cll1224666878/article/details/79526932?utm_source=copy

事务与存储过程

事务是保存在项目里的，存储过程是保存在数据库里的。
事务中编程语言（java，C++等），存储过程中只有SQL语言。

mysql的聚簇索引是指innodb引擎的特性，mysiam并没有，如果需要该索引，只要将索引指定为主键（primary key）就可以了。

MyISAM与InnoDB区别

主要区别：

MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
MyISAM支持全文类型索引，而InnoDB不支持全文索引。
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。
InnoDB 中不保存表的具体行数，也就是说，执行select count() from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含 where条件时，两种表的操作是一样的。
MyISAM不支持外键
myisam索引：因为myisam的索引和数据是分开存储存储的，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
innodb索引：innodb的数据和索引放在一起，当找到索引也就找到了数据

应用场景：

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

聚簇索引和非聚簇索引（也叫二级索引）

聚簇索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引节点，并保留一个链接指向对应数据块。
索引和数据存储在一块（都存储在同一个B*tree 中）。
一般主键索引都是聚餐索引
二级索引存储的是记录的主键，而不是数据存储的地址。
以Mysql的InnoDB为例，主键是聚集索引，唯一索引、普通索引、前缀索引等都是二级索引（辅助索引）

触发器：

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。
触发器有一个非常好的特性就是:触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。

触发程序视为单一交易中的一部份，因此可以由原触发程序还原交易，如果在交易过程中侦测到严重的错误（如使用者中断连线），则会自动还原整个交易。
他的作用很明显了，可以保证数据的完整性，下面有一个实例来说明他的好处，以及如果使编写代码不那么复杂

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：

INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

请注意，trigger_event与以表操作方式激活触发程序的SQL语句并不很类似，这点很重要。例如，关于INSERT的BEFORE触发程序不仅能被INSERT语句激活，也能被LOAD DATA语句激活。

可能会造成混淆的例子之一是INSERT INTO … ON DUPLICATE UPDATE …语法：BEFORE INSERT触发程序对于每一行将激活，后跟AFTER INSERT触发程序，或BEFORE UPDATE和AFTER UPDATE触发程序，具体情况取决于行上是否有重复键。

对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。
trigger_stmt是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用BEGIN … END复合语句结构。这样，就能使用存储子程序中允许的相同语句

例：

delimiter ||      //mysql 默认结束符号是分号，当你在写触发器或者存储过程时有分号出现，会中止转而执行  
drop trigger if exists updatename||    //删除同名的触发器，  
create trigger updatename after update on user for each row   //建立触发器，  
begin  
//old,new都是代表当前操作的记录行，你把它当成表名，也行;  
if new.name!=old.name then   //当表中用户名称发生变化时,执行  
update comment set comment.name=new.name where comment.u_id=old.id;  
end if;  
end||  
delimiter ;

触发器的"自动性"
对程序员来说，触发器是看不到的，但是他的确做事情了，如果不用触发器的话，你更新了user表的name字段时，你还要写代码去更新其他表里面的冗余字段，我举例子，只是一张表，如果是几张表都有冗余字段呢，你的代码是不是要写很多呢，看上去是不是很不爽呢。
触发器的数据完整性
触发器有回滚性，举个例子，我发现我很喜欢举子，就是你要更新五张表的数据，不会出现更新了二个张表，而另外三张表没有更新。

jdbc 存储过程：

CallableStatement的所有超级接口为PreparedStatement、Statement、Wrapper。其中继承自PreparedStatement接口。CallableStatement主要是调用数据库中的存储过程。在使用CallableStatement时可以接收存储过程的返回值。CallableStatement对象为所有的DBMS提供了一种标准的形式去调用数据库中已存在的存储过程。对数据库中存储过程的调用是CallableStatement对象所含的内容。有两种形式：1：形式带结果参数；2：形式不带结果参数。结果参数是一种输出参数(存储过程中的输出OUT参数)，是存储过程的返回值。两种形式都有带有数量可变的输入、输出、输入和输出的参数。用问号做占位符。

形式带结果参数语法格式：{ ? = call 存储过程名[(?, ?, ?, …)]};
形式不带结果参数语法格式：{ call 存储过程名[(?, ?, ?, …)]};PS方括号里面的内容可有可无。

PreparedStatement是java.sql包下面的一个接口，用来执行SQL语句查询，通过调用connection.preparedStatement(sql)方法可以获得PreparedStatment对象。数据库系统会对sql语句进行预编译处理（如果JDBC驱动支持的话），预处理语句将被预先编译好，这条预编译的sql查询语句能在将来的查询中重用，这样一来，它比Statement对象生成的查询速度更快。

public class PreparedStmtExample {

    public static void main(String args[]) throws SQLException {
        Connection conn = DriverManager.getConnection("mysql:\\localhost:1520", "root", "root");
        PreparedStatement preStatement = 
        	conn.prepareStatement
        	("select distinct loan_type from loan where bank=?");preStatement.setString(1, "Citibank");
 
        ResultSet result = preStatement.executeQuery();
 
        while(result.next()){
            System.out.println("Loan Type: " + result.getString("loan_type"));
        }       
    }
}

这个例子中，如果还是用 PreparedStatement 做同样的查询，哪怕参数值不一样，比如：”Standard Chated” 或者”HSBC”作为参数值，数据库系统还是会去调用之前编译器编译好的执行语句（系统库系统初次会对查询语句做最大的性能优化）。默认会返回”TYPE_FORWARD_ONLY”类型的结果集（ ResultSet ）,当然你也可以使用preparedstatment()的重载方法返回不同类型的结果集。

优点：

PreparedStatement可以写动态参数化的查询。
PreparedStatement比 Statement 更快
PreparedStatement可以防止SQL注入式攻击

数据库悲观锁与乐观锁实现

悲观锁

悲观锁，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统的数据访问层中实现了加锁机制，也无法保证外部系统不会修改数据。

使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键或者索引，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

悲观锁并不是适用于任何场景，它也有它存在的一些不足，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受。所以与悲观锁相对的，我们有了乐观锁。

乐观锁

乐观锁（ Optimistic Locking ）相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以只会在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回用户错误的信息，让用户决定如何去做。实现乐观锁一般来说有以下2种方式：

使用版本号
使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
使用时间戳
乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。