Mysql知识点总结

1、B+Tree索引机制 

索引类似书的目录，可以提高数据检索效率，降低数据库的IO成本，但占用磁盘空间，降低更新表的效率；

索引的分类：

主键索引：索引列的值必须唯一，不允许为空值；

普通索引：基本的索引类型，索引列允许重复值、空值；

唯一索引：索引列的值必须唯一，但允许为空值；

全文索引：只能在char,varchar,text类型字段上创建，场景在数据长度较大时使用，避免普通索引like模糊查询效率低；

前缀索引：只能在char,varchar,text类型字段上创建，可以指定索引列的长度；

组合索引：多个列组合代替单个列，遵循最左匹配原则；

索引的数据结构：

二叉树：每个节点最多有2个分支，左右子树数据顺序左小右大，可能发生树不分叉的情况；

平衡二叉树：左右子树高度差最大为1，但树的高度决定IO的次数，且范围查找需从根节点多次遍历，效率低；

B树：节点中包含键值和数据，节点可以有多个分叉，父节点的元素不会出现在子节点中，叶子节点深度相同，叶子节点之间没有相连，同样范围查找效率低，数据增大时占用空间增大，树的高度增加，磁盘IO次数随之增加；

B+树（多路平衡二叉树）：非叶子节点不存储数据，叶子节点之间形成双向有序链表，支持范围查询，数据量增大占用空间增大不明显，树高度不变；

聚簇索引：InnoDB中每个表都会有一个聚簇索引，当主键索引存在时，聚簇索引为主键索引，当主键索引不存在时，寻找一个不为null的唯一索引列作为聚簇索引，以上两个都没有时，会使用一个6个字节长度的隐式字段ROWID建立聚簇索引，当数据新增行时该字段自动递增；

辅助索引：InnoDB中除聚簇索引外的都为辅助索引，辅助索引的叶子节点存储的是该行的主键值，在检索中使用此主键值在聚簇索引中搜索行记录（回表）；

覆盖索引的优化策略可以减少回表，增加检索效率；

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

MySQL的InnoDB存储引擎是通过事务来保证数据一致性的；

数据库事务通常包含一序列对数据库数据的读写操作，主要要两个目的：

a、通过这一序列的读写操作在异常状态恢复到正常状态同时保持数据一致性；

b、在多个连接并发访问数据库时提供一个隔离的机制，让彼此的操作不会相互干扰；

事务的特点（ACID）：

原子性：事务作为整体被执行，包含的多个操作要么全部执行，要么都不执行；

一致性：事务应确保数据库状态从一个一致状态转变为另一个一致状态（数据满足完整性约束）；

隔离性：多个事务并发执行，一个事务的执行不受其他事务影响，同时也不影响其他事务的执行；

持久性：已被提交的事务对数据库数据的操作修改应该永久保存在数据库中；

事实上，原子性、持久性、隔离性都是为了保证一致性；

原子性的实现：Mysql事务的原子性是通过undo log来实现的，将对数据的修改都写入日志undo log，如果事务执行了部分操作后，数据库异常，则可以通过该日志对已执行的操作进行撤销回滚；undo log是逻辑日志，记录和事务操作相反的SQL语句，以追加写的方式记录，不会覆盖之前的日志；

持久性的实现：Mysql事务的持久性是通过redo log来实现的，当数据发生修改时，InnoDB会先将记录写道redo log，并更新内存，此时更新就算完成，后续会在合适的时机将记录刷到磁盘；redo log是物理日志，记录的是某个数据页的具体修改，而不是SQL语句的形式，它有固定大小，以循环写的方式记录，空间用完后会覆盖之前的日志；

隔离性的实现：如果没有隔离性将会发生：

a、第一类丢失更新，一个事务在撤销的时候覆盖了另一个事务已提交的更新数据；

b、脏读，一个事务读到了另一个事务未提交的更新数据；

c、幻读，一个事务读到了另一个事务已提交的新增数据；

d、不可重复读，一个事务读到了另一个事务已提交的更新数据；

e、第二类丢失更新，一个事务在提交时覆盖了另一个事务已提交的更新数据；

为解决上述五类问题，MySQL提供了四种事务隔离级别：

Read Uncommited(读未提交)：一个事务可以读到另一个事务未提交的数据，安全性低，并发性能高；

Read Commited(读已提交)：一个事务可以读到另一个事务已提交的数据，安全性较高，并发性能较高；

Repeatable Read(可重复读)：MySQL的默认隔离级别，同一个事务中相同的查询会得到同样的数据行，安全性较高，并发性能较高；

Serializable(串行化)：事务以一种串行的方式执行，安全性非常高，并发性能很低；

隔离级别	是否出现第一类丢失更新	是否出现脏读	是否出现幻读	是否出现不可重复读	是否出现第二类丢失更新
Read Uncommited	否	是	是	是	是
Read Commited	否	否	是	是	是
Repeatable Read（默认）	否	否	是	否	否
Serializable	否	否	否	否	否

Repeatable Read下select幻读通过MVCC机制解决、select...lock in share mode/select...for update/insert/delete的幻读通过间隙锁来解决；

3、MVCC机制

MVCC：多版本并发控制 (Multi-Version Concurrency Control)，只有在InnoDB引擎下存在；MVCC机制：避免同一个数据在不同事务之间的竞争，提高系统的并发性能；

• 允许多个版本同时存在，并发执行；
• 不依赖锁机制，性能高；
• 只在读已提交和可重复读的事务隔离级别下工作；

ReadView：数据库中某一个时刻所有未提交事务的快照；ReadView有几个重要的参数：

• m_ids：表示生成ReadView时，当前系统正在活跃的读写事务的事务Id列表；
• min_trx_id：表示生成ReadView时，当前系统中活跃的读写事务的最小事务Id；
• max_trx_id：表示生成ReadView时，当前时间戳InnoDB将在下一次分配的事务id；
• creator_trx_id：当前事务id；

当创建ReadView时，可以知道这个时间点上未提交事务的所有信息；

隐藏列：

• DATA_TRX_ID，6bytes，最近更新该行的事务ID；
• DATA_ROLL_PTR，7bytes，指向该行回滚段（rollback segment）的指针，通过它找到旧版本的数据（在undo Log 链中），就相当于一个指针，可以通过它来找到该记录修改前的信息；
• DB_ROW_ID，6bytes，隐藏主键，如果表没有主键，InnoDB会自动生成一个隐藏主键(单调自增ID)；

事务链：每次对记录进行修改时，都会记录一条undo log信息，每一条undo log信息都会有一个roll_pointer（DATA_ROLL_PTR）属性(INSERT操作没有这个属性，因为之前没有更早的版本)，可以将这些undo日志都连起来，串成一个链表；

MVCC原理：

• 如果被访问版本的trx_id属性值小于ReadView的最小事务Id，表示该版本的事务在生成 ReadView 前已经提交，所以该版本可以被当前事务访问；
• 如果被访问版本的trx_id属性值大于ReadView的最大事务Id，表示该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问；
• 如果被访问版本的trx_id属性值在m_ids列表最小事务Id和最大事务Id之间，那就需要判断一下 trx_id 属性值是不是包含在 m_ids 列表中，如果包含的话，说明创建 ReadView 时生成该版本的事务还是活跃的，所以该版本不可以访问；如果不包含的话，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问；

RC隔离级别的MVCC：RC隔离级别的事务在每次查询开始时都会生成一个独立的 ReadView；

RR隔离级别的MVCC：RR隔离级别的事务在第一次读取数据时生成ReadView，之后的查询都不会再生成，所以一个事务的查询结果每次都是一样的；

4、Innodb引擎之锁

锁机制：提高并发访问效率，同时确保每个用户能以一致的方式读取和修改数据；锁是数据库系统区别于文件系统的一个非常重要的特性，它用于管理对共享资源的并发访问，保证各个用户访问数据一致和完整；

共享锁（shared locks）和排他锁（exclusive locks）：

InnoDB实现了标准的行级锁，其中有两种类型的锁，共享锁（shared locks）和排他锁（exclusive locks）；

共享锁允许持有该锁的事务读取一行；

排它锁允许持有该锁的事务更新或删除行；

如果事务T1持有行 r 上的共享锁(S)，那么来自不同事务T2的请求将按照以下方式处理： 

• T2对S锁的请求可以立即被授予，结果就是，T1和T2在行r上都持有S锁；
• T2对X锁的请求不能立即被授予；

如果事务T1持有行 r 上的排它锁(X)，那么来自不同事务T2的请求不能立即被授予 r 上任何一种类型的；相反，事务T2必须等待事务T1释放其在行 r 上的锁；

Intention Locks（意向锁）：

• 意向共享锁(IS)表示事务打算在表中的单个行上设置共享锁；
• 意向排他锁(IX)表示事务打算在表中的单个行上设置排它锁；

在事务获得表中某一行的共享锁之前，它必须首先获得表上的IS锁或更强锁；
在事务获得表中某一行的排它锁之前，它必须首先获得表上的IX锁；

Record Locks（记录锁）：

记录锁是索引记录上的锁；例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;  防止任何其他事务插入、更新或删除t.c1值为10的行；

记录锁总是锁定索引记录，即使一个表没有定义索引也是如此。如果表没有索引，InnoDB创建一个隐藏的聚集索引，并将该索引用于记录锁；

Gap Locks（间隙锁）：

间隙锁是在索引记录之间的间隙上的锁，或者是在第一个索引记录之前或最后一个索引记录之后的间隙上的锁；例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;  防止其他事务将值15插入到t.c1列中，无论该列中是否已经有这样的值，因为范围内所有现有值之间的间隙都被锁定了；

Next-key Locks：索引记录上的记录锁和索引记录之前的间隙锁的组合；

Insert Intention Locks（插入意向锁）：

插入意向锁是一种间隙锁，是由INSERT操作在行插入之前设置的。这个锁表示，如果多个事务插入到同一个索引间隙中，如果它们没有插入到这个间隙中的同一位置，那么它们就不需要等待对方；假设有值为4和7的索引记录，尝试插入值分别为5和6的独立事务，在获得插入行的排他锁之前，每个事务都用插入意向锁锁住4和7之间的间隙，但不会阻塞彼此，因为行是不冲突的；

AUTO-INC锁：

AUTO-INC锁是一种特殊的表级锁，由插入到带有AUTO_INCREMENT列的表中的事务获得；在最简单的情况下，如果一个事务正在向表中插入值，那么任何其他事务都必须等待自己对该表的插入，以便由第一个事务插入的行接收连续的主键值；

5、Explain执行计划

只需要在SQL语句前加上explain关键字就可以查看执行计划，执行计划包括以下信息：id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra，总共12个字段信息；

id：SELECT识别符，SQL执行的顺序的标识，SQL从大到小的执行；

• id相同时，执行顺序由上至下；
• id不同时，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；

select_type：表示select查询的类型，用于区分各种复杂的查询，例如普通查询，联合查询，子查询等；

• simple：表示最简单的查询操作，也就是查询SQL语句中没有子查询、union等操作；
• subquery：当select或where中包含有子查询，该子查询被标记为SUBQUERY；
• derived：在SQL语句中包含在from子句中的子查询；
• union：表示在union中的第二个和随后的select语句；
• union result：代表从union的临时表中读取数据；
• materialized：表示物化子查询，子查询来自视图；

table：表示输出结果集的表的表名，并不一定是真实存在的表，也有可能是别名，临时表等等；

partitions：表示SQL语句查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表则会显示分区表命中的分区情况；

type：表示查询使用了哪种类型，在 SQL优化中是一个非常重要的指标，依次从优到差分别是：system > const > eq_ref > ref > range > index > ALL；

• system 和 const ：单表中最多有一条匹配行，查询效率最高，所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理；通常出现在根据主键或者唯一索引进行的查询，system是const的特例，表里只有一条元组匹配时（系统表）为system；
• eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录，所以这种类型常出现在多表的join查询；
• ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，可能会找到多个符合条件的行；
• range：使用索引选择行，仅检索给定范围内的行；一般来说是针对一个有索引的字段，给定范围检索数据，通常出现在where语句中使用 bettween...and、<、>、<=、in 等条件查询 ；
• index：扫描全表索引，通常比ALL要快一些；
• ALL：全表扫描，MySQL遍历全表来找到匹配行，性能最差；

possible_keys：表示在查询中可能使用到的索引来查找，列出的索引并不一定是最终查询数据所用到的索引；

key：跟possible_keys有所区别，key表示查询中实际使用到的索引，若没有使用到索引则显示为NULL；

key_len：表示查询用到的索引key的长度(字节数)；如果单列索引，那么就会把整个索引长度计算进去，如果是联合索引，不是所有的列都用到，那么就只计算实际用到的列，可以根据key_len来判断联合索引是否生效；

ref：显示了哪些列或常量被用于查找索引列上的值，常见的值有：const，func，null，字段名；

rows：mysql估算要找到我们所需的记录，需要读取的行数；可以通过这个数据很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好；

filtered：指返回结果的行占需要读到的行(rows列的值)的百分比，一般来说越大越好；

Extra：表示额外的信息。此字段能够给出让我们深入理解执行计划进一步的细节信息；

• Using index：说明在select查询中使用了覆盖索引，覆盖索引的好处是一条SQL通过索引就可以返回我们需要的数据；
• Using where：查询时没使用到索引，然后通过where条件过滤获取到所需的数据；
• Using temporary：表示在查询时，MySQL需要创建一个临时表来保存结果，临时表一般会比较影响性能，应该尽量避免；
• Using filesort：索引除了查询中能起作用外，排序也是能起到作用的，所以当SQL中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL不得不选择相应的排序算法来实现，这时就会出现Using filesort，应该尽量避免使用Using filesort；

explain的信息中，一般我们要关心的是type，看是什么级别，如果是在互联网公司一般需要在range以上的级别，接着关心的是Extra，有没有出现filesort或者using template，一旦出现就要想办法避免，接着再看key使用的是什么索引，还有看filtered筛选比是多少；

6、主从复制的原理

①当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中；

②salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程；

③当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点；

④I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log；

⑤SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作；

7、读写分离

master负责写入数据，slave负责读取数据：

1、可采用AOP的方式：

a、通过方法名判断，方法名中有get、select、query开头的则连接slave，其他的则连接master数据库；

b、通过自定义注解，方法上加@slave则连接slave，加@master则连接master，不加默认连接master；

2、开源的分布式数据库中间件解决方案，例如：读写分离就可以使用ShardingSphere-JDBC实现；

8、分表分库思想

垂直拆分：

分库：按业务划分模块，每个模块拥有自己的数据库；

优点：业务逻辑清晰，易于维护；

缺点：表直接不能直接关联查询，依然存在表数据大的问题，事物处理复杂；

生产实践：用户模块、交易模块、账务模块等；

分表：将大表（字段多）拆分为主表和附表，热字段放在主表，冷字段放在附表；

优点：冷热字段分开，提升查询、更新性能；

缺点：依然存在表数据大的问题，事物处理复杂；

生产实践：将交易表分为trade表和tradeSub表；

水平拆分：

分库：通过某个或某几个字段将数据分散到多个数据库中；

生产实践：无；

分表：通过某个或某几个字段将数据分散到多个表中；

优点：表数据量减少，性能提升；

缺点：范围查询问题；

生产实践：交易表每天一个表，日终将日表写入历史表；