MySQL数据库的理解

0、索引

0.1、什么是索引？

特殊文件：包含对数据库中所有记录的引用指针

InnoDB的索引：表空间的一个组成部分

0.2 索引的优点：

• 加快的对数据的检索速度
• 过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点：

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间

索引类型：

主键索引、唯一索引、普通索引、全文索引；

0.3 索引的数据结构

（1）B+树

• N个子树的节点，包含N个关键字，保存的是数据的索引
• 叶子节点包含所有的元素，且叶子节点按关键字的大小顺序用指针链接
• 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
• B+ 树中，数据对象的插入和删除仅在叶节点上进行

（2）哈希索引

数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过

Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），

将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；

如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

0.4 B树和B+树的区别

（1）B+树，查询的IO次数更少，查询效率更高   【磁盘IO次数由树的高度决定，B+树比B树更加矮胖，更低】

（2）B+树查询时的性能更稳定，每一次查询到的结果都在叶节点，但B树匹配到值得最好的情况在根节点，最坏情况在叶节点

（3）B+树，范围查询的很快，B树只能依次中序遍历，效率没B+树高。

（4）B+树中间节点只存键，B树中间节点可以存值和键。

（5）B树只适合随机检索，而B+树同时支持随机检索和顺序检索

1、事务

事务是一组逻辑的执行命令，要么都被执行，要么都不被执行；

数据库并发操作的最小单位，不可分割的数据库操作序列；

1.1 事务的四大特性

原子性：事务是最小的执行单位，要么全部被执行，要么全部不被执行

一致性：事务对数据操作前后是一致的，数据保持一致

持久性：事务对数据的修改是持久的

隔离性：并发访问数据库时，一个用户事务是不能被其他事务所干扰的

1.2 并发操作带来的问题：

脏读：A事务访问数据库并修改数据，此时修改的数据还没有提交，B事务来访问数据，B读到的就是脏数据

丢失修改：A事务访问数据库，读取数据a，并修改a，此时，B事务也读取a数据，并修改它，A修改的内容会丢失；

不可重复读：在A事务中多次读取同一个数据，在A事务还没有结束的时候，B事务也来访问数据库，并修改了这条数据，所以，A事务两次读取到的数据可能不相同；一个事务内两次读取到的数据不一样

幻读：A事务读取了几行数据，此时另一个并发B事务插入了一些数据，在之后的查询中，A事务会发现多了一些原本不存在的记录；

不可重复读：读取一条数据，发现某些列的值被修改；

幻读：多次读取一条数据，发现被增加或减少；

1.3 事务的隔离级别

读取未提交：最低隔离级别，允许读取尚未提交的数据变更，问题：幻读、不可重复读、脏读

读取已提交：允许读取并发事务已经提交的数据，防止：脏读，问题：幻读、不可重复读

可重复读：对同一段数据多次读取结果都一样，防止：脏读、不可重复读，问题：不可重复读

可串行化：最高级别隔离，事务之间完全不会被干扰，防止：幻读、不可重复读、脏读

InnoDB：默认可重复读的隔离级别，在分布式事务情况中会使用，可串行化隔离级别；

2、锁机制

什么是锁机制：

当数据库有并发事务的时候，可能会产生数据的不一致，

这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

2.1 锁和隔离级别的关系

读取未提交：无锁，读取数据不需要加共享锁

读取已提交：读操作需要加共享锁，但是在语句执行完以后释放共享锁

可重复读：读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁

可串行：限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

2.2 死锁

什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法：

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

2.3 数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的，

并发控制的任务是确保在多个事务同时存取数据库中同一数据时，不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

• 在查询完数据的时候就把事务锁起来，直到提交事务。
• 实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

• 在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
• 实现方式：乐一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

3、数据库优化

3.1 优化的思路：

• 将字段很多的表分解成多个表

  对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

  因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

  增加中间表

  对于需要经常联合查询的表，可以建立中间表以提高查询效率。

  通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

  增加冗余字段

  设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

  表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。