MySql进阶(索引、视图、事务）

数据库把数据放哪儿的：

一般在C:\ProgramData\MySQL\MySQL Server 5.7\Data路径下的文件夹对应着数据库中的数据库，例如图中的test文件夹对应test数据库

 

基于InnoDB的数据库文件夹中，每个表对应两个文件：

• .frm文件：表结构

• .ibd文件：索引文件+数据文件共同存储

基于MyISAM的数据库文件中,每个表对应三个文件

• .frm:表结构

• .MYD:数据

• .MYI:索引

提高检索的数据结构：

二叉树、红黑树、Btree、Hash

三大范式：

什么是范式：

范式是设计数据库表结构时主要遵守的规则，一般情况下，遵守前三个范式，设计出的表结构就是合理；违反了前三个范式任意之一，表结构一定不合理。

第一范式-1NF

所有的字段必须是原子性的

是指在关系模型中（就是表中），对于添加这个操作的一个规范要求，所有的域（字段）应该是原子性的，不可再分，不能是集合，数组，记录等非原子数据。例如：地址，是能再分成省、市、区的，应该将地址拆分。

• 不合理的设计，因为收获地址能够再分

  订单编号	收货地址
  101002	重庆市xx区xx街道

第二范式-2NF

非码属性必须完全依赖于候选码。

候选码：在表中可以用于唯一的表示一条记录，作为主键的候选存在

非码属性：非候选码

第二范式要求数据库表中的每一个记录必须可以被唯一区分，选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

第三范式-3NF

任何非主属性不依赖于其他非主属性（消除传递依赖）

例如一个表里有个部门id和部门名称，部门id这个字段不是主键，但是部门名称依赖于他，则不满足第三范式

视图 - view

视图用于保存查询的结果集，视图中并不保存数据，器数据均来源于真实表中。视图实质上是用于封装我们的查询语句。视图是一张虚拟表。

eg：

列表页面要查询你2个表中的部分数据，此时会使用两表联查，将结果展示给用户。假设在其他位置也要显示刚才的查询结果，诺将刚才的sql语句重写多次，则不符合编程规范，此时就应该将以上的查询语句进行提取，如果需要再次查询，直接调用该sql语句即可。

此时使用视图来保存结果集，其实实质为使用视图来封装刚才的查询语句，后期如果其他地方要再次查询该结果，直接调用视图即可，即可查询到相应的数据。

• 视图的创建

• 查询所有员工数据，包括部门名称
  select e.*,d.name from dept d join emp1 e on d.id = e.dept_id
  ​
  创建视图：
  create view v_empl as select ...
  create view v_empl(col1,col2....) as select ...
  使用视图：相当于是查询一个虚拟表
  select * from v_cmpl;

注意点：

1. 视图是虚拟表，并不保存数据，器数据来源于真实表中

2. 真实表中的数据发生改变，视图的结果也会发生改变

3. 视图是用于保存结果的，并不用于执行写操作

   • 逻辑角度上，不要对视图进行增删改操作。

   • 物理角度上对视图执行写操作，一定程度上是可以成功。

     • 改操作：无论是多表还是单表都能成功。

     • 增操作：单表成功，多表不成功。

     • 删操作：单表成功，多表不成功。

事务

事务的使用情景：多表的更改

定义：

事务是数据库管理系统执行过程中的一个逻辑单位，由一个优先的数据库操作序列构成。

哪些存储引擎支持事务：

InnoDB、NDB（用来做集群的）

事务的四大特性（ACID）：

• 原子性：实际上刚刚所说的逻辑单位，原子代表不可能再分了，在这个逻辑中，MySQL的各种操作，要么都成功，要么都失败。

• 一致性：一个是数据库自身完整性的约束，它不能被破坏，数据执行前后数据应该都是合法的状态，例如：主键唯一且非空，数据的类型和字段长度不能违反定义。二是业务中数据的一致性，例如银行A账户减少1000，而B账户增加500，虽然满足原子性，都成功了，但是不满足一致性。

• 隔离性：数据库里面是时时刻刻有很多的事务在对数据库的表进行操作，所以对隔离性的定义是：事务对于表的操作应该是互相透明，不干扰的。

• 持久性：事务提交成功了，产生的结果应该是永久性的。

数据库什么时候会出现事务？

1. 自动开启的事务：对于数据库的增删改，会默认自动开启事务，哪怕只是一个update语句。

2. 手动开启事务：如果默认关闭了事务，则需要手动提交事务：

   • 使用begin或者START TRANSACTION开启事务。

   • 结束事务的方式，一种是回滚，一种是提交

隔离级别:

首先这里有个表作为测试的表（account）

1. read-uncommitted（读未提交）：会造成脏读，脏读会读到其他事务未提交的数据，这个数据最后可能不存在。

   例子：事务A

   #将隔离性设置成<读未提交>
   set tx_isolation='read-uncommitted';
   #开启一个事务
   begin;
   #给zhangsan的账户加500块
   UPDATE account set balance=balance + 500 WHERE id = 1;
   #回滚事务
   ROLLBACK;

   事务B

   #将隔离性设置成<读未提交>
   set tx_isolation='read-uncommitted';
   #开启一个事务
   BEGIN;
   #查询zhangsan的信息
   SELECT * FROM account WHERE id = 1;
   #提交事务
   COMMIT;

   如果事务B在执行到查询的时候，事务A刚刚将张三的账户增加了500，但是还没有提交，则事务B也会读到该数据：

2. 

   但是等到事务A回滚了这次事务，则实际上事务B读到的数据并不存在。

3. 

   张三的账户金钱实际上没变，但是事务B却读到了还没提交的数据，这就是脏读。

4. read-committed（读已提交）：不会产生脏读的问题，但是会产生不可重复读问题。就是在一次事务中重复的查询一个数据，可能会产生数据结果不一致的问题。

   例子：事务A

   set tx_isolation='read-COMMITTED';
   ​
   begin;
   #给张三的余额增加500
   UPDATE account set balance=balance + 500 WHERE id = 1;
   ​
   COMMIT;

   事务B

   set tx_isolation='read-COMMITTED';
   ​
   BEGIN;
   ​
   #第一次读张三的账户
   SELECT * FROM account WHERE id = 1;
   #第二次读张三的账户
   SELECT * FROM account WHERE id = 1;
   ​
   COMMIT;

   如果在事务B在运行过程中，第一次读张三的数据，那么余额值为0。第一次读完之后，事务A提交了一次，那么第二次读的时候余额值就变成500了。问题就是：在用一个事务当中，重复读出来的数据不相等，这就导致不知道该选择哪一个，这就是不可重复读的问题。

   不可重复读解决方案：加锁

5. repeatable-read（可重复读）：没有脏读和不可重复读问题，上面的例子，如果是可重复读的隔离级别：在第一次读完之后，事务A提交了一次，则第二次读的时候，账余额还是显示的0。就是只要我读了最新数据之后，不管后面的数据怎么改变，还是第一次读的数据。

   可以这样理解：第一次做任何查询记录的时候，数据库里面有一个快照，当后面任何时间去读，都会读那个快照的。

   问题：如果用快照中的数据去操作表，很有可能产生脏写的问题，假如快照中张三的余额是500，另外一个事务已经将真实数据变为800，这时候用快照中的数据为基础给张三加200（500+200），最后张三的余额显示为700。但是实际上张三的余额应该是1000才对。

6. serializable（串行）：上面问题全部解决，但是效率低。当事务A在对张三的余额进行改变的时候（未提交），事务B去查询，事务B会被阻塞，直到读完。

锁

锁的分类：

- 按锁的粒度分:表锁和行锁
- 按锁的类型分:共享锁和排他锁(独占锁)
    给表/行添加上共享锁后,允许在此基础上再次添加共享锁,不允许在此基础上添加排他锁。（读写锁）
    共享锁通常作用于表
    排他锁通常作用于行，也可以作用于表
    应用：事务中对某条数据进行增删改操作，会给数据加排它锁

手动添加共享锁、排他锁

• 读锁：select ... for share;

  读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改。

• 写锁（排它锁）：select ... for update，这样查询也能上写锁

  写锁是排他的，会阻塞其他的写锁和读锁，update、delete、insert都会加写锁

MVCC机制(多版本并发控制机制)

首先了解一下，CopyOnWrite机制：

就是读写分离，读的是一个数据，而写的是它的副本，等到写操作做完之后，再让副本把数据替换了，以后就读之前的副本。这样就做到了读写分离，MVCC机制差不多。

实际上在MySQL底层，一行数据还有几个隐藏字段，一个是事务id，一个是rowid，一个是回滚指针，回滚指针指向的是undolog。如果操作是insert，实际上undolog保存的是delete。

数据库中的悲观锁乐观锁：

悲观锁和乐观锁是两种思想

悲观锁是基于数据库中的排它锁实现的，乐观锁没有使用数据库中的锁。

悲观锁:

在多线程并发执行时,某个线程总是悲观的认为,在自己执行期间,总有其他线程与之并发执行,此时可能会产生线程安全问题,为了保证线程安全,当前线程直接给操作数据加排它锁,从而保证线程安全.这种方式在保证线程安全的同时并发执行效率低.

- 悲观锁的应用: Java中的Synchronized ,数据库中的排他锁。

乐观锁:

在多线程并发执行时,某线程总是乐观的认为,在其执行期间,没有人与之并发执行,所以不会给对象加锁,但是实际上确实可能存在线程与之并发,就有可能产生线程安全问题,此时为了保证线程安全,采用版本号机制;这种方式可以实现在保证线程安全的同时,提高并发执行效率

索引

什么是索引：

索引是作用于列上,为该列的数据形成目录,从而提高该列数据的查询效率.索引通常作用于数据量大的表中.

用途：提高查询效率。索引和实际数据都是存储在磁盘的，只不过在进行数据读取的时候会优先把索引加载到内存中。

1）索引存储什么格式的数据？

key-value的格式

2）知道是k-v格式的数据，应该去选择一个合理的数据结构。

• Hash表

• 树

3）B树和B+树

BTree对所有元素进行了排序,是一颗度可以自定义的树,每个节点位置保存的是一个数据块,而不是每个元素.定义好度n后,则每个数据块中最多可以保存n-1个元素,当数据块中的元素数量达到n,此时这个数据块会进行分裂提取,将最中间元素进行提取,提取到上一级数据块中,中间元素两侧分裂成2个数据块,依然存在于这一层中.
    
好处:数据量大的数据形成BTree,查询找查询次数会降低,从而提高查询效率.
    

B+Tree是基于BTree扩展而来的,都实现了元素的排序,和BTree一样都会进行分裂提取,度设置好后,每个数据快中保存的最大元素数量也确定,为n-1,当数据块中的元素数量达到n,此时进行分裂提取
​
B+Tree和BTree不同:
  1. 提取后的元素是否存在于原层级
    B+Tree
      若从叶子节点中提取元素,提取到上一级后,该元素依然存在于叶子节点中
      若从非叶子节点中提取元素,提取到升一级后,该元素不再存在于原来层级
  2. B+Tree中叶子节点的数据块之间有链表进行维护(有序的),好处是可以提高区间范围内数据查询的效率.

为什么最后选择B+树？

随着数据量的增加，索引文件的大小也会增加，如果索引在逐渐变大的过程中，没办法直接加载到内存中怎么办？假设内存只有8G，但是索引文件有16G，这样就没办法一次性读到内存中。

考虑一个解决思路：分块读取——分而治之的思想。

操作系统：

局部性原理：

时间局部性：之前被访问过的数据很有可能被再次访问

空间局部性：数据和程序都有聚集成群的倾向

磁盘预读：

内存跟磁盘在进行交互的时候，有一个最小的逻辑单位，这个单位称之为页，或者datapage，一般是4k或者8k，由操作系统决定，我们在进行数据读取的时候，一般会读取页的整数倍，innoDB存储引擎在进行数据加载的时候，读取的是16kb的数据。

注意innoDB存储引擎支持自适应hash，人为不可干预

• hash表：

  • 需要比较好的hash算法，如果算法不好的话，会导致hash碰撞，hash冲突，导致数据散列不均匀。

  • 因为是无序的，所以当需要进行范围查找的时候需要挨个遍历，效率比较低。

• 树：二叉树->BST->AVL二叉平衡树->红黑树；

• 

  • 劣势：当需要给这些树插入很多数据的时候，这些树会变得非常高。查询的时候，每经过一层就会增加一次IO，IO次数过多，会影响查询效率。

• B树：由于上面的原因，所以引入了B树，B树有一个重要概念，就是度（阶），B树有多少度，就代表B树可以拥有多少个子节点。每个结点就能拥有N-1个数据。

• 

  每一个结点应该存储三种类型的数据，包括key，value，下一个的指针；

  由于B树要存入value，所以大大减少了存储的数量，假设一个节点16k，数据库表中的一条数据为1k，在理想情况下，3层满的B树，最多存4k多条记录。

  但是B+树，把数据存在最下面的一层，上面的结点只存key和指针，假定key是int类型，占用4个字节，加上一个指针的大小大约6个字节，一共是10个字节；则一个满的B+树能存储四千万左右条记录。

• 问题：MySQL的B+树一般是多少层：

  • 一般情况下，三到四层的B+树，足矣千万级别的数据存储。

• 问题：建立索引选择什么数据类型比较好？int？varchar？

  • 建立索引的时候，应该考虑：让key尽可能少的占用存储空间

  • 由于存入B+树结点的数据是（指针和key），指针大小不能改变了，但是key的数据类型key改变，int占用4个字节；而varchar占用的大小取决于创表的时候指定的大小｛例如：name varchar 20）｝，如果指定的大小，小于4个字节，也是可以用 varchar建立索引的。

聚簇索引和非聚簇索引

数据跟索引存储在一起的叫聚簇索引，没有存储在一起的叫非聚簇索引。

innodb：

此时，数据必定是跟某一个索引绑定在一起的，绑定数据的索引叫做聚簇索引

回表

例子:

所以回表的效率低，要避免回表

索引覆盖

最左匹配

第一个SQL语句，先查的是name再age，所以会使用索引

第二个SQL语句，只查的是name，name在组合索引的前面，所以也会使用到索引

第三个SQL语句，只查询age，但是age在组合索引的后面，则不会使用到索引

第四个SQL语句，先查age，再查name，age在name的前面，但是MySQL会帮我们优化，帮我们调换位置，所以最后还是先查的name，这就叫最左匹配

索引的操作：

创建索引:create index index_name  on table(col)
查询索引:show index  from table_name
删除索引:drop index index_name on table_name

索引失效场景

1. 对添加了索引的字段进行运算,此时会放弃索引,执行全表扫描
    eg: select ..from ...where age+4>12
2. 对添加了索引的字段使用函数,此时会放弃索引,执行全表扫描
    eg: select ....sum(xx) from ...
3. 对添加了索引的字段使用左模糊查询,此时会放弃索引,执行全表扫描
    eg: select....from ..where xx like '%_xx'
4. 对添加了索引的字段使用not in,此时会放弃索引,执行全表扫描
    eg: select...from...where age not in(11,12,13)