MySQL知识点总结

数据存储引擎

存储引擎是MYSQL的核心技术，不同的存储引擎使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力。常见的引擎分为三种：InnoDB存储引擎（MYSQL默认的事务性引擎）、MyISAM存储引擎、Memory存储引擎。
三种存储引擎的功能对比如下表所示：

总结三种引擎的使用选择如下：
1、InnoDB：适合要提供提交、回滚和崩溃后的安全恢复的事务安全能力，并要求实现并发控制；
2、MyISAM：适合于只读的数据，或者表比较小、可以忍受修复操作数据库；
3、Memory：适用于快速查找数据，用于数据分析中产生的中间数据。‘’

数据库三大范式

1、第一范式：数据库表的每一列都是不可分割的基本数据项，即同一列不能有多个值；
2、第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
如：设计订单信息表时，商品名称、商品价格等商品信息与表的主键不相关，而只与商品编号相关，因此可将表设计为订单信息表和商品信息表。
3、第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
如果一张表中出现另一张表的非主键，可以将这两张表用外键关联，而不是将另一张表的非主键直接写在当前表中。
设计数据库表的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。

参考博客：数据库三大范式。

数据库索引类型及原理

索引是一种对数据库表中一列或多列的值进行排序的一种数据结构，指向表中特定的数据内容，从而提高查询效率。

一、为什么要用索引

假设有一张存储了10万个数据（每条数据包含姓名、年龄、身份证号等信息）的表，若没有索引，要想查找姓名为"张三”的身份信息，需要从上到下依次对表中的所有数据进行扫描，找到所有名为张三的数据，这也叫全表查询。

可以看出，全表查询的效率非常低，需要逐条对比，因此就需要通过对每条数据建立索引，从而直接通过索引快速查询到数据信息，大大提高了查询效率。

二、数据库索引的类型及原理

1、B+树索引： 适合范围查询、顺序查询，不适合插入、删除数据，是InnoDB、MyISAM的索引方式。
B树与B+树都是用于大量数据查询的一种数据结构，二者有以下特点：
B树：（1）由二叉搜索转变为N叉搜索，树的高度大大降低，查询次数少；
（2）叶子节点、非叶子节点都可以存储多个数据，每次可以读取一页数据，IO次数更少；
（3）通过中序遍历，可以访问树上所有节点，但需要多次往返各个节点之间，效率有待提升；
由B树的特点可知，对于范围查询而言，B树需要通过中序遍历来进行查找，不够完美，B+树在此基础上进行了改进。B+树的特点为：
（1）依旧为N叉树，但是非叶子只保存索引不存储数据，所有数据存储在同一层的叶子节点上，查询性能更稳定；
（2）非叶子节点可保存更多索引，相同的数据，B+树的高度更低，查询的IO次数更少；
（3）所有叶子节点形成一个有序链表，不需要通过中序遍历进行顺序查询，更适合范围查询。
对比可知，B+树最大的优点就是适合范围查询，这在实际应用中是非常广泛的，因此InnoDB选用的就是B+Tree。B+树的缺点就是插入、删除操作非常复杂，一般只用在数据库的查询操作中。
2、哈希索引： 适合单一数据的查找、删除、插入，不适合范围查找，是Memory的索引方式。
哈希表的查询、删除、插入的平均时间复杂度都是O(1)，适合每次只查询一条信息。但是对于需要排序查询（对查询的数据进行排序输出）、范围查询（如：大于或小于某值的范围查询），采用哈希索引的时间复杂度会从O(1)退化为O(n)，相当于全表查询，效率极低。总结哈希索引的特点如下：
（1）只支持等值比较查询，不支持范围查询；
（2）访问数据的速度非常快，但当哈希冲突较多时，查询效率会大大降低；
（3）哈希索引数据不是按顺序存储的，即无序的，无法用于排序查询。
因此，哈希索引只适用于特定的场合，不要轻易使用。

参考链接：https://blog.csdn.net/bjweimengshu/article/details/105108585。

三、索引分类

1、聚簇索引：叶子节点存储整行数据

按照每张表的主键构造一颗B+树，叶子节点存放整张表的行数据。每张表只能有一个聚簇索引，如果没有主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。

优点：①索引和数据保存在同一颗B+树中，数据访问更快；
②聚簇索引对于主键的范围查找速度非常快。

2、非聚簇索引（辅助索引）：叶子节点存储主键值

在聚簇索引之上创建的索引称之为非聚簇索引，其叶子节点存储的数据为主键值，访问数据通常需要二次查找。

假设一张表中有id（主键），order_id（唯一键）两个字段。若使用“where id=14"的条件进行查询，就会走聚簇索引，直接可以查询出对应行数据；若使用“where order_id= 1100202"的条件进行查询，就会走非聚簇索引，会先根据order_id查询到所在行的主键id，再根据主键id走聚簇索引就查到行数据。如下图所示：

参考博客：聚簇索引与非聚簇索引。

回表与覆盖索引

所谓回表查询，就是先通过非聚簇索引定位到主键，再通过聚簇索引定位到数据行。

而索引覆盖则是一种避免回表查询的优化策略，即：一个索引包含了所有需要查询的字段值，查询时直接返回索引的数据，而不需要回表查询。
举例：假设在学生表的年龄上建立了索引，那么当进行select age from student_table where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

覆盖索引的优点：
1、覆盖索引只需要读取索引，极大减小了数据访问量；
2、避免了回表查询，提高了查询效率。

四、索引失效的情况

具体索引失效使用的案例可参考：一张图搞懂MySQL的索引失效。

五、联合索引最左前缀匹配原则

最左前缀匹配：最左优先，以最左边为起点的任何连续的索引都可以匹配上。遇到范围查询(>、<、between、like)就会停止匹配。

假设有联合索引（a,b,c）：
①where条件为（a,b,c）、（b,a,c）、（c,a,b）等，会走联合索引；
②where条件为（a）、（a,b）、（a,b,c），会走联合索引；
③where条件为（b）、（c）、（b,c），不会走索引，会全表扫描；
④where条件为（a,c）时，会走索引，但只使用a的索引。

参考博客：Mysql最左匹配原则。

为什么要遵循最左匹配原则

假设有如下B+树，联合索引为（a,b)：

由图可知，最左边的a 都是有序的，分别是 ： 1、1、2、2、3、3， 但是右边的b 不一定有序： 1、2、1、4、3、2。但是在a相同的情况下 b是有序的， 如： a=1时 b =1，2 ； a=2时， b= 1，4； a=3时 ，b=1，2。

因此，在筛选数据的时候， 若直接筛选b ，整个就是无序的，需要做全表扫描，此时索引失效；若先筛选a再筛选b ，就可以利用B+树的有序性来加快查找速度。综上，在使用联合索引时需要遵循最左匹配原则。

六、创建索引的原则

1、遵循最左前缀匹配原则
2、为频繁作为查询条件的字段创建索引（如：订单id)
3、更新频繁的字段不适合创建索引（如：订单状态）
4、不能有效区分数据或重复值比较多的字段不适合创建索引（如：性别）
5、尽量拓展索引，不要新建索引。
6、定义有外键的字段一定要创建索引。
（外键：一个表中存放的另一个表的主键。）

锁

当多个用户对数据库并发操作时，会存在数据读取不一致的问题，造成数据混乱。数据库中锁的作用就是保证数据的一致性，与线程同步含义相同。数据库中的锁分为两大类：悲观锁和乐观锁。

悲观锁（Pessimistic Lock） ：适用于多写的应用类型

总是假设最坏的情况，每次有事务去拿数据时都会觉得别人会修改，所以每次使用时都会给该数据上锁，而其他事务就会阻塞，直到这个事务释放锁把数据转让给下一个用户。

悲观锁按使用性质可划分为以下几类：
1、共享锁（Share Lock）：也叫读锁（S锁），允许多个事务对同一数据共享一把锁，都能访问到数据，但 只能读不能修改。
2、排他锁（Exclusive Lock）：也叫写锁（X锁），一个事务获取了某数据的排他锁，其它事务就不能获取其它锁，只有获取排他锁的事务能对数据进行读取和修改。（独占式锁）
3、更新锁：简称U锁，在数据修改操作的初始化阶段锁定可能要被修改的资源，从而避免共享锁竞争排他锁造成的死锁现象。

悲观锁按作用范围可划分为：
1、行锁：锁的作用范围是行级别。对于UPDATE、INSERT、DELETE语句，会自动加排他锁。InnoDB默认采用行锁。 数据库能够确对哪些行进行操作的情况下使用行锁（如使用主键时），如果不知道就使用表锁（不使用主键时）。
行锁的优势：锁的粒度小，发生锁冲突的概率低，并发处理的能力高。
行锁的劣势：开销大，加锁慢。

2、表锁：锁的作用范围是整张表。
表锁的优势：开销小，加锁快。
表锁的劣势：锁的粒度大，发生锁冲突的概率高，并发处理的能力低。

乐观锁（Optimistic Lock）：适用于多读的应用类型

与悲观锁相反，总是假设最好的情况，每次有事务去拿数据的时候都认为别人不会修改，所以不会给该数据上锁。但在更新的时候会判断在此期间有没有事务更新了该数据。
乐观锁两种常见的实现方式：
1、版本号机制：一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。
2、CAS算法（compare and swap）：一种无锁算法，即在不使用锁的情况下实现多线程之间的变量同步。CAS采用自旋的模式，会浪费CPU资源。原理：通过原子操作来更新数据的值的，比较读取的当前值V与当前线程先前取出的值A是否一样，若一样表示该值在此期间未被其它线程修改，则更新该值，否则重新从数据表读取数据赋给A，再进行V和A的比较，直到更新成功。（具体参考CAS机制）。

参考链接：https://blog.csdn.net/weixin_39651041/article/details/79985715。

数据库事务隔离级别

事务就是访问数据库进行的一组数据操作，所有操作必须成功，否则就会回滚所有操作导致失败。

一、事务的四大特性（ACID）

1、原子性（Atomicity）： 事务开始后的所有操作要么全部完成，要么全部不完成，不能只完成一部分。事务执行过程中发生错误，会回滚已有操作并恢复到事务开始前的状态。
2、一致性（Consistency）： 事务开始前和结束后，数据库的完整性没有被破坏。比如：A向B转账1000元，A的账户中会减少1000元，而B的账户中会增加1000元。
3、隔离性（Isolation）： 多个事务并发执行时，同一时间只允许一个事务请求同一数据，不同的事务之前不会互相干扰。如：A在从一张银行卡取款的过程中，其他人不能向这张银行卡转账。
4、持久性（Durability）： 事务完成之后，事务对数据库的所有更改应该保存在数据库中，不能回滚。

二、事务并发的三大问题

1、脏读： 一个事务读取到了另一个事务未提交的数据。比如：事务A读取了事务B更新但尚未提交的数据，B提交失败发生回滚操作，那么A读取的数据是脏数据。
2、不可重复读： 一个事务多次读取同一数据，另一事务在其读取过程中对该数据进行了修改（update操作）并提交，导致这个事务前后读取的数据结果不一致。
3、幻读： 一个事务多次读取同一数据，另一事务在其读取过程中对该数据进行了插入或删除（insert操作）并提交，导致这个事务前后读取的数据结果不一致。

三、事务隔离级别及实现原理

事务隔离是通过加锁来实现的，锁的竞争会带来性能的损失。事务隔离级别分为以下四种：
1、读未提交（READ UNCOMMITTED）： 不加锁，性能最好，但是无法解决脏读、不可重复读、幻读问题；
实现：不加锁，可看作无隔离。
2、读提交（READ COMMITTED）： 一个事务只能读取其它事务已经提交的数据，但不能解决不可重复读、幻读问题；
实现：事务每次操作数据时都会重新生成一次快照，来记录当前数据的版本，在快照时间之前提交的数据版本则可以被读到。（MVCC）
3、可重复读（REPEATABLE READ）： 一个事务在开始后直到提交前的任意时刻读取的数据都是一样的，不会读到其它事务对已有数据的修改，但可以读取其它事务插入的新数据，即无法解决幻读问题。（mysql中默认的隔离级别，MVCC）
实现：事务开始时生成一个当前事务全局性的快照，后面每次读取的数据都是该次快照的数据版本。
4、串行化（SERIALIZABLE）： 隔离效果就好，可以解决脏读、不可重复读、幻读问题，但需要加锁，性能较差。
实现：一个事务读的时候会加共享锁，其他事务可以并发读，但不能写；该事务写的时候加排它锁，其他事务不能写也不能读。

参考链接：https://blog.csdn.net/weixin_40172337/article/details/114218800。

MVCC（多版本并发控制）实现机制

MVCC是一种多版本并发控制机制，通过保存数据在某个时间点的快照来实现的。不同的存储引擎的MVCC实现是不同的，典型的有乐观（Optimistic）并发控制和悲观（pessimistic）并发控制。

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间（创建版本号），一个保存行的删除时间（删除版本号）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务版本号，用来和查询到的每行记录的版本号进行比较。保存这两个额外的系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作简单，性能好。不足之处就是每行记录都需要额外的存储空间，需要更多的行检查和维护工作。
在READ COMMITTED隔离级别下，一个事务多次输入SELECT查找语句时，InnoDB每次查询时都会生成一个快照，记录当前已提交的数据版本，在这之前的数据版本都可以被读取到。
在REPEATABLE READ隔离级别下，一个事务多次输入SELECT查找语句时，InnoDB只在事务开始时生成一个当前事务全局性的快照，每次查找都是读取早于当前事务版本的数据行。

MySQL数据库性能优化的方法

①硬件优化。提升服务器的硬件配置，如CPU、内存大小等。
②数据库调优，如增加索引。
③引入缓存，减小数据库压力。
④读写分离。增加从库，抗住更多的读请求。
⑤分库分表。单表数据超千万时，考虑分库分表。

MySQL主从复制，读写分离

一、为什么要主从复制

1、高可用性： 若主库发生故障，可快速切换到其中一个从库，从而保证系统业务的可用性。
2、负载均衡： 主库用于写数据，各个从库用于读数据，实现读写分离，将流量分布到各个库上，从而实现负载均衡。
3、可扩展性好： 当业务量很大的时候，为了抗住更多的读请求，可以增加从库，从而分担流量。

二、主从复制的原理

MySQL主从复制是一个异步的复制过程，主库发送更新事件到从库，从库读取更新记录，并执行更新记录，使得从库的内容与主库保持一致。
主从复制的流程为：
①当主库进行insert、update、delete操作时，会按顺序写入到binlog（二进制日志）中；
②从库启动I/O线程，跟主库建立客户端连接；
③主库启动binlog dump线程，读取主库上binlog的内容发送给从库的I/O线程；
④从库的I/O线程接收到 binlog 内容后，将内容写入到本地的 relay log（中继日志）；
⑤从库启动SQL线程，读取relay log的内容，并完成对从库数据的更新。

上图为一个从库的流程，实际中，有N个从库，主库就会对应有N个binlog dump线程，而每个从库都会有自己的I/O线程和SQL线程。

参考文章：MySQL主从复制原理。

MySQL分库分表

一、为什么要分库分表

MySQL单表最多能存储5000w数据，但是单表数据表达1000w以后，即使添加从库、优化索引，查询的性能依旧很差。这时候就需要通过分库分表，从而有效减小单台数据库的压力。

二、数据表的两种拆分方式

1、垂直拆分

数据表列的拆分，把一张列比较多的表拆分为多张表，如：把主键和常用的列放一张表，把主键和不常用的列放另一张表。如下图所示：

垂直拆分还有一种理解，即从业务的角度进行拆分，如：一个数据中既存在用户表，又存在订单表，那么就可以把用户表存在用户库，订单表存在订单库中。如下图所示：

优点：使每条数据变小，一个数据块block可以存储更多数据，查询时可减小I/O次数；
缺点：①主键出现冗余，需要管理冗余列，查询所有数据时需要关联查询JOIN操作；
②依旧会出现单表数据量过大的情况。
应用场景：数据表中某些列常用，而某些列不常用的情况。

2、水平拆分

数据表行的拆分，数据数量超过千万级别时，数据表的查询效率就会很慢，就可以把一张表的数据按行拆分成多个表来存放。如下图所示：

优点：不存在单表大数据造成的性能瓶颈；
缺点：逻辑复杂，通常查询时需要多个表名；
应用场景：单表数据量达百万级别甚至千万级别。

上图所示为库内分表，仅仅单纯的解决了单一表数据过大的问题，而没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决，即分库分表。

三、数据表水平拆分的两种方案

水平分表最主要的就是路由算法，即把路由的key按照指定的算法进行路由存放。常用的水平分表方案有两种：range范围路由、hash路由。

1、range范围路由：按照数据范围进行拆分数据

range方案比较简单，就是把一定范围内的订单，存放到一个表中；如上图id=12放到0表中，id=1300万的放到1表中。设计这个方案时就是前期把表的范围设计好。通过id进行路由存放。

优点：数据扩容方便，不需要数据迁移；
缺点：有热点问题，由于id的值一般递增的，某段时间的数据会集中在某一张表中，就会导致该表压力过大，而其它表没有压力。
（热点问题是指某段时间对数据的操作集中在一个表中，而其他表的操作很少。）

2、hash路由：指定路由key对分表总数进行取模

在设计系统之前，假设未来几年的订单量为4000万。每张表我们可以容纳1000万，也我们可以设计4张表进行存储。

hash路由的具体方法为：对指定的路由key（如：id）对分表总数进行取模，上图中，id=12的订单，对4进行取模，也就是会得到0，那此订单会放到0表中。id=13的订单，取模得到为1，就会放到1表中。为什么对4取模，是因为分表总数是4。

优点：数据可以均匀的放到每张表中，对数据进行操作时，就不会有热点问题。
缺点：若数据量继续增大，需要增加分表数，数据的迁移和扩容，很会麻烦。

四、分库分表方案

上述的两种水平分表方案中，hash可以解决数据均匀问题，range可以解决数据迁移问题，因此可以将两者结合在一起，实现分库分表的方案。

实现思路为：先用range路由方案让数据落地到一个范围内，这样需要扩容时以前的数据不需要迁移；再在这个范围内，使用hash路由方案让数据均匀分配在几个表中，这样就解决了数据热点问题，保证每个表压力一样；最后把这些表分配到几台数据库机器上，实现分库。以上就实现了分库分表。

具体实现，我们一起看一个例子：

假设数据量为4000万，定义一个Group01组，组内有三个DB库，DB_0中有4张表，DB_1中有3张表，DB_2中有3张表。每张表内存储的路由key（id）的范围如上图所示。这里假设DB_0的服务器性能更好，所以存储4张表，从而可以存储更多的数据。

存储路由key的具体流程为：

扩容的时候，只需要新增加一个group02组，而不需要迁移之前的数据。

实际设计的时候，我们只需要维护group、db、table的对应关系，就可以将数据存储在对应的表中。如下图所示，图中table表字段有些小错误，仅做示例。

实际开发的时候，这三张表可以保存在缓存，而不是MySQL中。