MySQL
最后更新于
MySQL是一款大规模的开源关系型数据库，在很多大厂上面都用作数据的持久层，稳定性是具有保障的，默认端口是3306
MySQL关于存储引擎的一些知识点：
show engines;显示现有的存储引擎
可以看到只有InnoBD支持事务和XA分布式事务，SavePoints用于对事务的Back，按照我的理解应该是属于事务的一部分的。
查看表使用哪个存储引擎：SHOW TABLE STATUS LIKE '%table_name%';
InnoDB和MyISAM的区别：
简而概括就是MyISAM不支持事务和行锁，并且在崩溃之后是无法恢复的，这非常致命，但是支持一些额外特性如全文索引。
是否支持行锁：MyISAM只支持表锁，InnoDB支持表锁和行锁，默认使用行锁
是否支持事务和安全恢复：MyISAM强调性能，不支持事务和安全恢复，InnoDB都支持
是否支持外键：InnoDB支持，MyISAM不支持
是否支持MVCC：仅InnoDB支持，面对高并发事务，MVCC比直接加锁更高效，仅仅在READ COMMITTED和REPEATABLE READ两个隔离级别下工作良好
一般情况下选择InnoDB没错，InnoDB适合大多数场景
MySQL索引使用的数据结构主要有BTree索引和哈希索引，索引的实现是由存储引擎来控制的，只不过大多数存储引擎的实现都一样，所以就直接说是MySQL的了
对绝大多数查询为单记录查询的场景，可以使用哈希索引，其余的场景都使用BTree即可。
主要是BTree实现上MyISAM实现和InnoDB实现有所不同，MyISAM实现的是直接将叶子结点存放对象的地址，因此取出这一条记录时候还得再经过一次寻址操作
InnoDB实现则是直接将数据存储在主键构成的B+树的叶子结点上，对于非主键索引，则在叶子结点上存储的是主键的ID，根据非主键进行索引还会进行一次回表查询
这里的K其实是普通索引还是唯一索引都没那么重要了，因为唯一索引仅仅只是在普通索引的基础上添加了唯一的特性，在插入时候如果碰到相同值会拒绝插入，往往是利用这个特性来避免重复。就查询效率而言，两者的效率差距微乎其微，但是就插入效率而言，明显普通索引更快，因此在可以保证数据唯一性的前提下并且不需要做数据校验，优先使用普通索引
建议主键选择是与业务无关的自增ID作为主键
因为这样可以保证插入的时候记录会顺序添加到当前索引节点的后续位置，只有当一页写满了才会去开辟后续空间，空间利用率高，且每次插入时候基本不会涉及到移动已有数据
如果使用的不是自增主键如身份证或者是学号：1、索引比较长，会占用大量无效空间 2、因为每次插入都具有随机性的，所以会涉及到频繁的数据移动和频繁的分页操作，会产生大量的碎片，碎片比较大的表查询效率会比较低
查询缓存
在8.0之后移除了查询缓存，查询缓存会缓存查询结果，如果命中则直接返回，不会缓存汇总函数如SUM等的值，不建议开启。
什么是事务
事务是一组操作要么都执行，要么都不执行
事务的四大特性：ACID
Atomicity：原子性，事务的操作是原子性的
Consistency：一致性，事务执行后，数据库从一个状态变成另一个状态
Isolation：隔离性，各个并发事务之间的数据库是独立的
Durability：持久性，数据库的数据改变是持久的
并发执行带来的几个问题：
脏读：一个事务访问到了另一个事务未提交的数据
不可重复读：同一个事务读取一条记录两次，发现读取的内容不一致
幻读：事务在执行过程中发现多出了一些原本不存在的记录，即记录条数变了
不可重复读和幻读都是读取到了其他事务已经提交的数据，之间的区别主要是不可重复读读到的是update操作，而幻读读取到的是delete和insert操作。
幻读的产生主要是由于MVCC（多版本并发控制）的影响，SELECT会查询当前版本号的快照，INSERT和UPDATE会更新最新版本号的快照以避免对已经提交的修改进行了覆盖，违背了一致性原则。
模拟情况：事务A执行SELECT操作查看是否存在id为1的数据，假设此时不存在，此时事务B开启，执行了INSERT id为1的操作，并且执行了提交。此时事务A尝试插入id为1的数据，发现失败，此时事务A就发生了幻读，因为明明查出来id为1的数据不存在，但是插入不了
MVCC：
每次事务开启时候都会开启一个版本号，并且维持一个当前版本号的快照，当然不是直接dump下整个数据，那样太大了，而是通过记录undo Log（回滚日志）的方式，只需要将当前数据执行undo log就可以得到前面的某个特定版本了，读操作会直接读事务处于的版本，而写操作入INSERT和UPDATE就会直接操作最新版本了，避免了其他事务的提交被覆盖。
更细致的来说，读会读取ReadView，而ReadView在读已提交和可重复读两种隔离级别下的生成时间不同（当然都是通过undo log来生成的），读已提交在当前事务每进行一次读操作之前都会生成ReadView，而可重复读仅仅是在事务的第一次读时候生成ReadView
读已提交和可重复读都使用到了MVCC技术，只是生成ReadView的策略不同
MySQL的事务隔离级别：
脏读 | 不可重复读 | 幻读 | |
读未提交 | √ | √ | √ |
读已提交 | × | √ | √ |
可重复读（默认隔离级别） | × | × | √ |
串行化 | × | × | × |
InnoDB使用的可重复读是MVCC + Next-key Lock锁算法，是不会出现幻读的，同时效率上几乎无任何损失。
InnoDB是支持表锁和行锁的，MyISAM仅仅支持表锁，表锁是MySQL中粒度最大的锁，实现简单，不会出现死锁，粒度最大，触发冲突几率最高，并发度最低。行锁是MySQL中粒度最小的锁，粒度小，并发度高，加锁成本高，加锁慢，会出现死锁。
InnoDB使用到的锁的算法有：Record Lock（单行上的锁）、Gap Lock（间隙锁，锁定一个返回不包含记录本身）、Next-key Lock（结合Record和Gap锁实现，锁定一个范围包含自身，InnoDB使用他来解决幻读问题）。
数据库的四大特性ACID实现：
原子性
定义：事务提交要么成功要么失败
实现：事务要么提交成功，要么提交失败，如果提交失败之后，事务就会进行回滚，回滚的依据就是undo log日志（InnoDB独有），undo log会记录数据修改，如果当前事务执行失败了，此时就会根据undo log来完成数据的回滚
一致性
定义：事务执行前后都必须保持系统处于一致的状态
实现：前面的原子性、持久性隔离性都是为了实现数据一致性的
持久性
定义：事务一旦提交，对数据库的改变是永久性的
实现：为了避免每次写入都要进行磁盘IO，效率会比较低，所以系统的实现是使用Buffer Pool来解决的，Buffer Pool完成定时刷盘，调用fsync接口完成数落盘操作，但是如果两次落盘之间MySQL宕机了就会导致数据丢失，因此解决办法是使用redo log日志来完成的，写入时候会以append方式将修改的内容追加到redo log当中去，然后再到Buffer Pool当中写入，在事务提交时候调用fsync接口完成redo log的数据刷盘，这样宕机之后从redo log当中完成数据恢复，相当于将数据落盘由原来的Buffer Pool转移到了redo log，之所以能有这个转移，是因为以下两个方面：
Buffer Pool当中存储的是数据，在完成落盘操作时候可能完成对数据和索引的同时修改，因此执行的是随机IO，redo log当中存储的是变更，属于只追加文件方式的落盘，是顺序IO
Buffer Pool是以数据页（16K）为单位的，每次修改都会直接同步整个数据页刷入到磁盘当中，而redo log每次只需要在事务提交时候跟着一起将数据刷入到磁盘上
隔离性
提供了几种隔离级别来保证隔离性，
写操作对另一个写操作的影响：锁机制保证
写操作对另一个读操作的影响：MVCC机制
锁机制：只有通过索引检索数据才会使用行锁，否则就是用表锁了
隔离性：根据undo log的版本号机制来完成对数据的还原操作
度未提交——没有MVCC
读已提交——每次进行SELECT时候都会根据最新版本数据创建ReadView
可重复读——第一次执行SELECT时候创建ReadView，之后都不会创建。通过next-key lock机制保证
串行化
大表优化
1、查询时候务必增加范围，如根据订单名字查历史订单限制时间在一个月之内
2、读写分离：增加MySQL的吞吐量
3、垂直拆分
使用垂直拆分可以减少单个表的频繁IO，甚至可以将拆分的表放在不同的库中来增加每个表的吞吐量，劣势也很明显，会出现列的冗余，以及让事务处理更加复杂
4、水平拆分
一般不建议使用，除非是非常大的体量表，到达了千万级的体量，不得不拆分的时候才会使用
主要是通过第三方库封装的Spring JDBC如当当的Shading-JDBC，阿里的TDDL来实现对分区之后的事务管理等操作。
分库分表之后主键id如何处理：
1、利用redis生成 2、美团的Leaf分布式ID生成器来完成
可能会问到MySQL语句的执行过程
这里要区分开来是查询语句还是更新语句，更新语句要在查询语句的基础上进行日志的记录。
查询语句的执行过程：通过连接器建立与客户端的连接，并检查该用户是否有权限执行该语句——如果是MySQL8.0之前，要在查询缓存中查找是否存在以当前sql语句为key的值，如果存在就直接返回——如果不存在，就进入分析器，进行词法和语法的分析——再进入优化器，在这一步会决定该查询语句走什么索引，是由MySQL自己决定的，不一定最优——执行器，最后检查权限，调用存储引擎的API执行语句，返回结果。
如果是更新语句，同样要先进行查询，找出哪几条记录需要修改，得到要修改的记录之后，进行修改，将结果记录到Server层的binlog和存储引擎层的redo log中（InnoDB独有），需要使用两阶段提交来保证binlog和redo log的数据一致性。存储引擎先将数据更新到内存当中（实际更新的数据），同时记录到redo log当中去，此时redo log处于prepared状态，此时告知执行器让其记录到binlog中去，执行器将binlog直接记录进磁盘当中，执行完之后调用存储引擎层面的事务提交接口，此时的redo log从prepared状态变为commit状态，事务成功提交。
每次事务的提交redo log和binlog都会立即写入到磁盘当中，防止丢失
使用MySQL几点建议：
控制单表数据量在500万以内。
少使用分区表，宁愿选择水平拆分也别使用分区表，跨分区的效率可能非常低
对数据表进行垂直拆分，将冷热数据分离，实现热数据常驻内存，避免冷数据常驻内存
建议自增字段改成无符号的，容量增加一倍
避免使用TEXT和BLOB列，无法创建内存临时表
索引列的顺序选择：列区分度最高的在最左侧，字段长度小的在最左侧，使用频繁的列放在最左侧
对查询执行比较慢的情况的分析：
1、偶尔慢：可能是刚好当前数据没有加载到内存当中来，走的磁盘，会慢一点、可能此时别的事务使用到了表锁锁定了表，获取不到锁从而阻塞了
2、一直很慢：
没用到索引：当前查询字段没有索引，只能走全表扫描了、可能没走上索引，如查询c+1<100的记录就不会走c索引，但是查询c<100-1就会走c索引。
选错索引：优化器经过分析觉得走全表扫描比较快，通过采样的方式获取当前索引的区分度，从而决定是否要走 这个索引，可能出现选错索引的情况。
补充：可能数据表非常大，到达了千万级别的，这时候建议水平拆分来提升速度
可能当前表的列数太多，或者存在TEXT和BLOB列导致无法加载到内存中来，导致查询时候走的硬盘，需要表的垂直拆分，保证当前热数据在内存临时表中。
阿里开发规范：
【强制】禁止使用左模糊或者全模糊，如果需要就走搜索引擎解决
因为哪怕建立了索引，索引是B+Tree的，是左前缀匹配原则，左边值没确定就无法走索引了
【强制】不得使用外键和外联，一切外键概念必须在应用层中解决
【参考】@Transactional事务不要滥用，会影响数据库的QPS
使用索引主要是为了加快速度的检索速度，如果没有索引，就只能直接走全表扫描，使用自增索引作为主键的时候还可以增加数据的规整性，降低表的碎片化程度，这样在查询的时候效率会更高，使用唯一索引还可以利用索引的唯一性来进行一些业务逻辑的处理。
为什么不在每个字段上都建立索引
虽然索引可以加快查询的速度，但是在插入数据，删除数据和更改数据的时候都要对索引进行维护，对数据修改影响比较大。此外，索引会占用额外空间，非聚镞索引还好，叶子结点存放的是主键id号，但是聚镞索引叶子节点存放的是整行的数据，会占用大量内存空间。
聚镞索引具有唯一性，默认是选择主键作为聚镞索引，如果没有主键，那么InnoDB会选择一个唯一的非空索引代替，如果这也不存在，那么InnoDB会隐式定义一个主键来作为聚镞索引
建索引的几个规则
1、在经常作为搜索条件的列上建立索引
2、在需要排序的列上建索引，因为建立索引自然就进行了排序了（默认是B+Tree实现）
3、避免对where子句中的判断字段施加函数，即使是x+1也不行，会导致不会走索引
4、建议在not null列上建立索引，因为null索引可能需要更多的存储空间，并且null值无法参与某些运算
索引的实现和InnoDB和MyISAM的区别，前面写了，MyISAM叶子结点存放的是该行的地址
覆盖索引：如果当前选定的索引包含了需要查询的值，就可以不用再根据主键进行回表查询了，例如建立了索引(username,age)，那么在执行如下语句的时候：
就不需要进行回表查询了。
索引的数据结构：哈希和B+（在数据结构里面已经详细写过了）
之所以哈希不好，不仅是因为哈希冲突，更因为哈希表无法支持范围查询，当然B+树也不支持左模糊查询和全模糊查询，因为B+树是根据左边来优先进行排序的。
索引的几种常见类型：
主键索引：一张表只能有一个主键，自增，最好是无符号的。如果没有，InnoDB则检测是否有唯一索引，如果有则选用作为默认的主键，如果没有则创建一个隐式的自增主键
指定的主键会作为聚簇索引
二级索引：叶子结点为主键的索引。
主要有：唯一索引，普通索引，前缀索引，全文索引
唯一索引：要求不重复，但允许数据为null，可以确认很多个唯一索引，往往使用唯一索引是为了确保唯一性，而不是查询效率，查询效率与普通索引微乎其微，但是插入和删除效率差距非常大
普通索引：可以建立多个普通索引，允许为null，唯一作用就是加快查询
前缀索引：指定字符串前几个字符创建索引，因此往往比普通索引更小
全文索引：5.6之前MyISAM独有，后来InnoDB也引入了，检索大文本数据中的关键字信息
聚镞索引：索引结构和数据存放于一起的一种索引，主键索引就是聚镞索引
优点：查询快，B+本身就是平衡的，叶子节点也是有序的，数据都在叶子节点上，可以保证访问数据的稳定性，此外还可以充分利用缓存，读取相邻的数据
缺点：非常依赖插入数据的有序性，如果是无序的，插入数据慢不说，可能还会造成大量的碎片，导致查询速度降低，特别是以String或者是UUID作主键的数据、更新代价大，如果是非聚镞索引列还好，如果是聚镞索引列的更新，不仅会更新当前数据，还会造成索引结构的改变，当前数据的移动
非聚镞索引：索引结构和数据分开存放的索引，如二级索引和MyISAM的索引
二级索引存放的是主键值，而MyISAM索引存放的则是数据的地址，需要进行寻址操作获取数据
优点：更新代价比聚镞索引来的小
缺点：依赖插入数据的顺序性，可能会二次回表
不一定一定回表，如果索引覆盖所有需要查询的字段的值，也就是覆盖索引，那么就不需要回表了
联合索引创建原则：最左前缀原则，尽量将创建最频繁的字段作为最左的第一个字段，查询的时候也尽量以这个字段为第一条件
创建最左，查询最左
建立索引的一些条件：
不为null的字段推荐建索引，因为如果存在null不仅会占用更多的存储空间，且null无法参与运算
应该尽量考虑建联合索引而不是单列索引，因为单列索引需要建立和维护很多B+树，而联合索引多个字段在一个索引节点上，可以省去很大的一部分空间
在使用字符串时候考虑使用前缀索引代替普通索引
关于存储日期的一点建议
DateTime和Timestamp之间的抉择
通常会首选Timestamp
主要原因：DateTime不会保存时区，Timestamp会保存时区
占用空间：Timestamp占用4字节，Datetime占用8字节，但是这难免使得Timestamp表示时间范围较小，只能存储1970到2037年最后一天，而DateTime可以存取1000-9999最后一天
时间戳的存储，争论在使用整数存取到1970年的时间还是Timestamp，没有结论，高性能MySQL作者推荐使用timeStamp，可读性强。
唯一索引和普通索引的性能区别比较
查询性能差距微乎其微
但是对数据进行修改的时候，唯一索引要判断唯一性，需要将数据读入到内存当中，然后进行修改，无法使用change buffer，而普通索引仅仅只需要将修改记录存进change buffer当中即可。然后使用两阶段提交记录进redo log中做持久化。
补充：
最左前缀原则：其实就相当于是MySQL联合索引的一种属性，或者说是MySQL索引的属性，无法支持左模糊查询或者全模糊查询，因此需要左值是确定的，最左匹配原则也是同理，如创建了（col1，col2，col3）的联合索引，只有明确了col1的值的时候才能走索引，否则就全表扫描了，如（col1），（col1，col2），（col1，col2，col3）就会走索引。
因此才推荐我们将最常使用的列放在最左边来保证可以走上这个索引
MySQL的主从复制是基于binlog的，因为可能不会使用InnoDB，所以需要在Server层实现主从复制功能。
主要有三大范式
第一范式：属性需要满足原子性，不可再分解
如我们表中存在一个出生年月的字段，因为出生年月可以被拆分为年信息和月信息，因此是不满足第一范式的，如果所有字段都是不可拆分的，那么就是满足第一范式的。
第二范式：表中必须有主键，并且没有包含在主键中的列必须完全依赖于主键，而不只是依赖主键的一部分
第二范式也就是我创建表时候喜欢用的，将表拆分到最细粒度，表之间的查询使用SQL语句自己实现
如存在一张表：学号、课程号、姓名、学分
这里由于学分依赖于课程号，姓名是依赖于学号的，可以再进行一次拆分，因此不符合第二范式
第三范式：表中数据的冗余性，要求字段没有冗余，需要将数据进行拆分
如表中存放：学号、姓名、学院名称、学院电话
存在学院名称和学院电话的冗余，如果一旦需要修改学院名称和学院电话，那么需要修改大量数据
