DB相关知识

╰+哭是因爲堅強的太久メ 2022-02-04 10:59 188阅读 0赞

**1、数据库引擎：**  
    oracle中不存在引擎的概念，数据处理大致可以分成两大类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）  
        OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLTP系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作  
        OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。OLAP系统则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等  
    mysql数据库中，常用的引擎主要就是2个：innodb和myisam（isam、heap、berkley）  
        isam是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了  
        myisam是mysql的isam扩展格式和缺省的数据库引擎。除了提供isam里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM强调了快速读取操作，这可能就是为什么MYISAM受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据  
        innodb数据库引擎都是造就mysql灵活性的技术的直接产品，这项技术就是MYSQL+API。在使用mysql的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外来键。要比ISAM和MyISAM引擎慢很多。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了  
        MyISAM类型不支持事务处理等高级处理  
        MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快  
        DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除  
        Innodb引擎提供了对数据库ACID事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统，它本身实际上是基于Mysql后台的完整的系统，Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引，但是该引擎是不支持全文搜索的，同时启动也比较的慢，它是不会保存表的行数的，当进行Select count(\*) from table指令的时候，需要进行扫描全表，所以当需要使用数据库的事务时，该引擎就是首选，由于锁的粒度小，写操作是不会锁定全表的，所以在并发度较高的场景下使用会提升效率的  
        myisam引擎是MySql的默认引擎，但不提供事务的支持，也不支持行级锁和外键，因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低，不过和Innodb不同的是，MyISAM引擎是保存了表的行数，于是当进行Select count(\*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表，所以如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将MyIASM作为数据库引擎的首先  
        大容量的数据集时趋向于选择Innodb，因为它支持事务处理和故障的恢复，Innodb可以利用数据日志来进行数据的恢复，主键的查询在Innodb也是比较快的  
        大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候

**2、索引创建规则和使用、优化策略：**  
    mysql索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引  
    聚集索引，也叫聚簇索引：  
        alter table table\_name add primary key(colum\_name)  
        数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引  
        聚集索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询，因此在查询方面，聚集索引的速度往往会更占优势  
    非聚集索引：  
        索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引  
    B+树的优点：  
        非叶子节点不会带上指向记录的指针，这样一个块中可以容纳更多的索引项，一是可以降低树的高度，二是一个内部节点可以定位更多的叶子节点  
        叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动，具体的来讲，如何想扫描一次所有数据，对于b+树来说，可以从因为他们的叶子结点是连在一起的，所以可以横向的遍历过去  
    B树的优点：  
        对于在内部节点的数据，可直接得到，不必根据叶子节点来定位  
    hash索引：  
        只能用于memory表存储引擎，memory表存储引擎是内存表存储引擎，他会把表的数据全部放到内存中mysql重启之后，会全部清空  
        通过hash函数算出来的地址可能会产生冲突，此时mysql通过链表的方式来解决这种冲突数据，维护索引字段的值就比较麻烦，代价比较高  
        hash索引数据没有顺序的，因为hash函数算出来的值的排序不是顺序的，他会产生空间碎片  
        hash索引只支持等值比较(in , = , <=>)查询，不适合范围查找  
        无法使用最左前缀原则，例如：hash索引（col1,col2,col3），如果where条件中，只包含col1字段，是无法用上索引的，因为两次计算的hash值都不一样，所以用不上hash索引  
        col like ‘abc%’ 在hash索引中用不上索引  
          
**3、truncate、delete、drop的区别：**  
    1.truncate：删除内容、释放空间，但不删除定义  
    2.delete：删除内容、不释放空间、不删除定义  
    3.drop：删除内容、释放空间、删除定义  
    4.truncate是不可以rollback的，但是delete是可以rollback的  
    5.delete是删除的意思，使用delete删除的数据写入日志，可以通过日志恢复  
    6.truncate是截断的意思，使用truncate删除的数据是不操作日志的，不可以通过日志恢复，但可以通过一些其他工具恢复  
    7.truncate比delete速度快，且使用的系统和事务日志资源少，delete语句每次删除一行，并在事务日志中为所删除的每行记录一项，truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放  
    8.truncate是直接把高水线降下来，由于检索数据的时候是检索高水线以下的数据这样可以快速定位需要检索数据块的范围，降下来以后那些之前那些被占用的数据块会分批释放，空间得以重用，这个速度是非常快因为他不像delete操作实际上是把检索到的每个数据块都标识为删除，实际空间并没有释放还是可以恢复的，相比之下truncate的速度是比delete要快速的多  
    9.truncate使用注意事项：  
        truncate table在功能上与不带where子句的delete语句相同，二者均删除表中的全部行，但truncate table比delete速度快，且使用的系统和事务日志资源少  
        delete语句每次删除一行，并在事务日志中为所删除的每行记录一项，TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放  
        truncate table删除表中的所有行，但表结构及其列、约束、索引等保持不变，新行标识所用的计数值重置为该列的种子，如果想保留标识计数值，请改用delete，如果要删除表定义及其数据，请使用drop table语句  
        对于由foreign key约束引用的表，不能使用truncate table，而应使用不带where子句的delete语句，由于truncate table不记录在日志中，所以它不能激活触发器  
        truncate table不能用于参与了索引视图的表  
        对用truncate table删除数据的表上增加数据时，要使用update statistics来维护索引信息  
        如果有roolback语句，delete操作将被撤销，但truncate不会撤销

**4、创建索引应该注意哪些点：**  
    数据库索引创建规则：  
        表的主键、外键必须有索引  
        数据量超过300的表应该有索引  
        经常与其他表进行连接的表，在连接字段上应该建立索引  
        经常出现在Where子句中的字段，特别是大表的字段，应该建立索引  
        索引应该建在选择性高的字段上  
        索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引  
        频繁进行数据操作的表，不要建立太多的索引  
    一般选择在这样的列上创建索引：  
        在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的  
        在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询 时间  
        在经常使用在Where子句中的列上面创建索引，加快条件的判断速度  
        为经常出现在关键字order by、group by、distinct后面的字段，建立索引

**5、索引执行分析：**  
    explain详讲：  
        type：表示按照某种类型来查询，例如按照索引类型查找，按照范围查找  
            const 表示表中最多有一个匹配行  
            eq\_ref 对于每个来自于前面的表的记录，从该表中读取唯一一行  
            ref 对于每个来自于前面的表的记录，所有匹配的行从这张表中取出  
            ref\_or\_null 类似于ref，但是可以搜索包含null值的行  
            index\_merge 出现在使用一张表中的多个索引时，mysql会将这多个索引合并到一起  
            range 按指定的范围来检索  
            index 从索引树中查找  
            ALL 全表扫描  
        Extra：  
            using where 使用了where查询  
            using index 使用了覆盖索引，出现这个值是比较好的现象  
            using join buffer 表示使用了连接缓存  
            using filesort 使用了文件内排序，必须要优化，严重影响性能  
            using temporary 使用了中间表或者是临时表  
    以下情况会索引失效：  
        选择列尽量少用\*号  
        尽量遵循复合索引的字段顺序(最左前缀)  
        复合索引字段不要使用> <查找，会使索引后的字段失效  
        不要在索引列上做计算，自动类型转换操作，及函数计算  
        不要使用不等于 !=、<>，会使索引失效  
        尽量不要出现 is null、is not null，可能也会导致索引失效  
        like中字符串左边值不固定，会使索引失效  
        where中要尽量少用or

**6、in和exists：**  
    in执行顺序：先执行in中的子查询，作为我们最外层循环，主查询作为内层循环  
    exists：主查询作为最外层循环，子查询作为最内层循环  
    根据我们的时间复杂度，最外层循环小于内层循环的时候，使用的时间相对较少，结论：永远小表驱动大表是最优的选择方式  
      
**7、锁分类：**  
    按照对数据操作的类型分：读锁，写锁  
        读锁：也称为共享锁，针对同一资源，多个并发读操作可以并行执行，并且互不影响，不能写  
        写锁：也称排它锁，当前线程写数据的时候，会阻塞其它线程来读取或者写数据  
    按照数据操作的粒度：表锁，行锁，页锁：  
        表锁：就是锁住整个表，主要在myisam表存储引擎中出现  
        行锁：锁定单独的某个表中的某一行记录，主要用于innodb存储引擎

**8、事物特性及隔离级别：**  
    原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响  
    一致性（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态，拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性  
    隔离性（Isolation）：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离，即对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行  
    持久性（Durability）：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作，例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误  
    事物四种隔离级别：  
        Read uncommitted (读未提交)，会发生脏读、不可重复读、幻读，一个事务写数据时，只允许其他事务对这行数据进行读，通过“排他写锁”实现  
        Read committed (读已提交)，可避免脏读，会发生不可重复读、幻读（oracle默认隔离级别），读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行  
        Repeatable read (可重复读)：可避免脏读、不可重复读，会发生幻读（mysql默认隔离级别），读取数据的事务将会禁止写事务但允许读事务，写事务则禁止任何其他事务，通过“行级共享读锁”和“行级排他写锁”实现  
        Serializable (串行化)：可避免脏读、不可重复读、幻读，禁止任何事务，一个一个进行，通过“表级共享读锁”和“表级排他写锁”实现  
    脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据，当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致  
    不可重复读：是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了  
    幻读：第一个事务对一定范围的数据进行读取，第二个事务在这个范围增加一条数据，这时候第一个事务就会出现两次对数据读取记录数不一致  
    脏读和不可重复读区别：脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据  
    幻读和不可重复读区别：都是读取了另一条已经提交的事务，所不同的是不可重复读读取的是同一个数据项（针对修改），而幻读针读取的是一批数据整体（针对插入）