MySQL数据库专题

最新推荐文章于 2024-07-26 09:40:31 发布

小叶是我宝贝呀

最新推荐文章于 2024-07-26 09:40:31 发布

阅读量108

点赞数

文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/qq_38437416/article/details/128352752

版权

1. MySQL的基础

1.1 何为三范式

所谓范式，指的就是在设计表结构时参考的理论依据，我们常用的就是一、二、三、范式，且每一个范式都是在上一个范式的基础上进行加强。但是我们在进行表结构设计时，一定还要遵守一个原则：需求 > 性能 > 范式；

以下三个范式概念分别为：

第一范式：表中的每一列都是不可再分割原子数据；比如说一个列中出现：1318888888_上海，这样的数据；

第二范式：在第一范式的基础上，不允许表中存在与当前表描述不相关的字段；

第三范式：在第二范式的基础上，每一个列的数据必须与主键直接关联，而不能简介关联；

1.2 MySQL有哪些引擎

MySQL的引擎主要就是数据库底层的软件组织，是插拔式的设计，不同的引擎主要的区别在于存储机制的不同、索引方式的不同、锁表机制不同；MySQL关系型数据库中支持的主流引擎有：InnoDB、MyISAM、Memory等；其中根据不同的场景我们可以选择不同的引擎，比如说我们需要存储具有事务机制等数据我们需要使用InnoDB引擎，我们需要存储文档型数据我们需要使用MyISAM引擎、如果我们不需要落盘仅需要做热点数据我们可以选择Memory引擎；

InnoDB引擎：

优点：支持事务、支持行锁、具有缓冲池用于缓存数据和索引；
缺点：因为支持了事务，所以性能没有MyISAM高；
适用场景：需要事务支持的业务场景；

MyISAM引擎：

优点：查询性能极高，并且自己维护了整个表中的数据总和；
缺点：不支持事务，索引的效率没有InnoDB的高；
适用场景：适用于不需要事务支持的业务场景,尤其适用于高速存储和检索的场景;在MySQL5.5版本一下的默认数据库;

Memory引擎：

优点：基于内存crud，效率极高；
缺点：不能操作数据量过大的业务场景，并且数据安全性很低；
适用场景：适用于对安全性要求不高且数据量不大的场景；

2.4 InnoDB和MyISAM对比

事务：InnoDB支持，MyISAM不支持；
性能：MyISAM的读写性能更高；
锁粒度：InnoDB支持到行锁级别、MyISAM只支持到表锁级别；
索引：两者都是用的B+树，但是InnoDB是采用了聚簇索引，MyISAM采用的则是非聚簇索引，效率不如InnoDB；
行数保存：MyISAM内部自己维护了行数总数，InnoDB不保存需要自行计算；

2.索引

2.1 索引概述

索引能够提升查询效率的原因：
- 索引就像是一本书的目录，进行了有序排列；
- 通过有序排列，我们在进行范围查找的时候就不需要进行全表扫描了；
不同引擎默认的索引数据结构：
- InnoDB引擎默认索引：B+树索引；
- Memory引擎默认索引：Hash索引；
- MyISAM引擎默认索引：Fulltext索引；
不同的索引原理

B+树索引原理：B+树底层数据结构是有序数组链表+平衡二叉树，每一个节点(也就是每一个页)采用16kb来存储数据，并且非叶子结点只存索引key值且每个非叶子结点中的索引值都是有序的，叶子结点存储了索引对应的value具体的值，并且叶子结点中的数据通过链表指针关联起来，可以在范围查找的时候直接通过叶子结点将每个页中的数据读取出来，这样就会减少IO操作；

Hash索引原理：就是采用了Hash进行计算，通过不同的key计算出来应该存储的位置，将value值放入，当离散度很高的时候才哦那个Hash索引可以大大提高效率；

2.2 B+树和B树索引的比较

B+树只会在叶子结点上存储数据，B树会在每个节点上存储数据，并且叶子结点存放null；
B+树叶子结点存储了这条链路上所有索引值对应的数据，而B树没有，这样就会在范围查询的时候减少对磁盘的IO操作；
B+树的叶子结点通过指针进行关联，而B树没有；

问题：为什么索引采用B+树而不采用B树：

因为B+树非叶子结点只存储索引key值，不存储具体的数据，叶子结点才存储具体的数据，这样的设计会让索引树查询效率更加稳定；
同时B+树的叶子结点存储了整条索引值链路对应的value值，只需要将叶子结点上所有的数据从磁盘读取出来即可，而不需要多次进行IO操作，从而提高了查询效率；

2.3 B+树索引和哈希索引的比较

B+树的范围查询比哈希索引要快，但哈希索引在进行某个具体的值查询的效率要比B+树索引快；
在哈希碰撞较多的情况的下，哈希索引的效率也不快，只有在数据离散度高的情况下才能提高查询效率；

2.4 聚簇索引和非聚簇索引

聚簇索引
- 聚簇索引一张表中只会有且仅有一个，通常使用primary key主键作为索引；
- 聚簇索引不仅仅存储索引的key值，同时在叶子结点会存储该key值对应数据的value值；
- 聚簇索引决定了文件的真实存储的位置，通常数据文件和聚簇索引都是在同一个文件当中；
非聚簇索引
- 非聚簇索引一张表中可能会有多个，通常是由具体业务字段来确定索引列；
- 非聚簇索引只会排序索引值，并且在最后的叶子结点存储相关的数据，并且附带上rowId的值(也就是聚簇索引值)，方便后续进行回表查询所有的值；
- 由此可见，非聚簇索引并不能决定真实数据存储的位置，只能对索引列进行排序；所以非聚簇索引和真实数据文件不在同一个文件中；
总结来看：聚簇索引其实就是根据主键索引进行排序之后的真实的数据文件，而非聚簇索引就是一个辅助索引文件，通过聚簇索引快速定位到具体的rowId的值，来进行回表查询聚簇索引上的真实数据；

注意：并不是所有的非聚簇索引都需要进行回表查询，因为可能会在非聚簇索引上发生索引覆盖的情况，就是非聚簇索引的叶子结点上存储的value值刚好满足sql语句中需要查询的数据，就无需进行回表查询了；

2.5 联合索引

联合索引指的是多个字段构建一个索引值，这里会存在最左匹配原则带来的索引失效的问题；

2.6 如何建立索引

数据量很少的表不建议建立索引；
选择离散度高、非空的字段作为索引的key值，同时索引列字段大小应该合理尽量小；
经常增删的表在建立索引的时候主键尽量选择自增；

3. MySQL的常见优化手段

3.1 SQL语句优化的方案

针对于建立索引优化：
- 建立索引时，选择非null值，且离散度高的字段；
- 每张表的索引个数要控制在合理范围，最好不要超过6个，因为过多的话Insert和update速度慢；
针对于联合索引优化：
- where后面的字段索引值应该尽可能全，同时要满足最左匹配原则；
- select的时候，尽可能使得索引覆盖；
- 建立联合索引时，各个索引字段的顺序按照实际情况来设置，比如有范围查询的字段放到后边；
针对索引失效的优化：
- like ‘%查询%’，左边的%会导致索引失效，需要避免；
- where过滤时，索引列的条件不能有任何函数；
- where筛选条件时，字符串不加’'会导致索引失效(因为底层的优化器会对索引字段做函数操作转换为数字类型)；
针对SQL语句的优化：
- or替换成UNION 效率会更高；
- distinct替换成grow by；
- !=、not null、is not null、not in都要慎用；

3.2 MySQL优化方案

主从复制，读写分离；
分库分表方案；
避免大事务；
合理的创建表结构，字段长度要合理、合理利用引擎；
增加MySQL数据库的连接数；
在MySQL上面增加一层缓存抵挡流量；
硬件升级；

3.3 Explain的使用

4.分库分表

5.事务特性与隔离级别

5.1 事物的四大特性

事物的四大特性：ACID、Automic原子性、Consistency一致性、Isolation隔离性、Durability持久性；

原子性：指的是事务的所有操作要不然一起全部成功，要不然一起全部失败；

一致性：指的是事务操作后的数据在逻辑上具有一致性，比如说A向B账户扣钱，事务完成之后，B冲了多少钱，A就一定扣了多少钱；

隔离性：事务具有隔离级别，不同的事务之间互不影响；有四个隔离级别：读未提交、读已提交、可重复读、串行化；

持久性：在事务提交之后，数据将会一直存在不会丢失；

5.2 事务的隔离级别

不同的事务隔离级别会造成不同问题，比如说脏读、幻读、不可重复读；
- 脏读：在事务A进行中，读取到了未提交事务B的数据；
- 不可重复读：在事务A进行中，第一次读取数据假如值为1，此时B事务针对于此条数据修改为2，事务A再次查询此条数据会变为2，造成了幻读；
- 幻读：在事务A进行中，第一次读取数据未读取到，此时事务B针对于这条数据进行插入，此时事务A再次进行查找就能查询到了，造成了幻读；
隔离级别：
- 读未提交：会造成脏读、不可重复读、幻读；
- 读已提交：可以解决脏读，但是解决不了不可重复读、幻读；
- 可重复读：可以解决脏读和不可重复读，但是解决不了幻读；
- 串行化：可以解决幻读；

6.MVCC

什么是MVCC，其实就是实现事务回滚的底层原理，我们叫做：多版本并发控制 ；视频讲解的最清楚网址:https://www.bilibili.com/video/BV1YJ411J7vb/?spm_id_from=333.337.search-card.all.click&vd_source=5b7c815173703310af2176c0bad5c050

怎么控制，有三个主要的组件：每个数据行后面隐藏的三个字段、undolog日志、readview视图；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LpbKVkr2-1671254621265)(/Users/yinbo/Library/Application Support/typora-user-images/image-20221216204938553.png)]

多版本并发控制的流程是：

每一次update语句的时候，undolog日志都会记录对应的事务id，以及上一个记录的地址；

同时在进行select的时候，会生成一个readView视图出来，这个视图是一个未提交事务的一个区间；此时将整个的undolog拉取出来，从上往下进行读取，那最新出现的一条不在未提交事务区间的日志即为当前select查询到的日志；

如果在一个回话当中，事务的隔离级别为可重复读，readView会在第一次查询的时候生成，此后每次的查询不会生成；

如果在一个回话当中，事务的隔离级别为读已提交，readView会在每一次的查询的时候都重新生成；

6.1 表中隐藏三个字段

表隐藏的三个字段分别为：row_id、trx_id、roll_pointer；

其中row_id表示当前数据行id；

trx_id表示当前事务号id，是递增的；

roll_pointer表示当前事务回滚指向上一个事务的id号；

6.2 undolog日志

undolog日志，其实就是针对于某条日志的修改链路的记录值；记录了每条数据的事务trx_id、上一条数据的指针对应位置；

undolog的主要作用是生成一个版本链！方便在进行查找时，生成的read_view视图查找整个的更新链路，来确定当前select应该返回的数据；

6.3 ReadView视图

在一个查询会话中，会生成一个ReadView的查询视图，其实就是一个快照的视图，就是记录当前未提交事务的区间，以及提交完事务的最小的trx_id，生成一个区间值[未提交事务最小trx_id，未提交事务最大trx_id]，这个区间是在当前select的时候，生成的一个视图；如果在进行select查找的时候，不在此未提交区间的事务id则为一提交的；