一文读完常用MySQL基础知识

MySQL

关系型数据库三范式

第一范式:数据库的每一列都是不可分割的原子列
第二范式:表中的每个字段都要和主键相关,不能仅和主键的一部分相关
第三范式:表中字段不能引用其他表的非主键字段

1.关键字

explain

分析sql执行语句,sql调优的重要依据。
查询结果字段:

id:

将查询内的sql进行分级的字段,id越大的sql查询越先进行

select_type:

查询类型

simple: 简单SELECT，不使用UNION或子查询等

primary: 子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为primary

union: union中的第二个或后面的select语句

dependent union: union中的第二或后面的select语句,取决于外面的查询

union result: UNION的结果，union语句中第二个select开始后面所有select

SUBQUERY: 子查询中的第一个SELECT，结果不依赖于外部查询

DEPENDENT SUBQUERY: 子查询中的第一个SELECT，依赖于外部查询

DERIVED: 派生表的SELECT, FROM子句的子查询

UNCACHEABLE SUBQUERY: 一个子查询的结果不能被缓存，必须重新评估外链接的第一行

table

显示这一步所访问数据库中表名称,如果有别名则显示为别名

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

possible_keys:

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

Key:

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len:

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

ref:

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows:

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

Using where: 不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using index: 索引覆盖了

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句

2.MySQL架构的组件

1.连接器:

主要负责和客户端建立连接,获取权限,维持和管理连接

2.查询缓存:

优先在缓存中进行查询,如果查到了则直接返回,如果缓存查询不到,则去数据库中查询

MySQL默认关闭了查询缓存,即官方不建议开启,因为触发缓存的场景比较苛刻。
1.SQL必须完全一致才能命中缓存,换一个where条件的等值查询则需要重新缓存。
2.更新表数据后还需要清除相关的所有缓存，效率低下
因此在8.0版本中已经将缓存功能移除

3.解析器/分析器

分析器的主要工作是要对执行的SQL语句进行词法解析，语法解析，最终得到抽象语法树，然后使用预处理器对抽象语法树进行语义检查，检查语法树中的表和对应字段是否存在

4.优化器

主要将SQL经过词法解析，语法解析得到的语法树，通过数据字典和统计信息内容，再经过一系列运算，最终得出一个执行计划，包括选择使用哪个索引

在分析是否走索引查询时，使用过动态数据采样分析出来的。
只要是统计分析出来的，那就可能存在分析错误的情况，所以在SQL执行不走索引时，也要考虑这方面的因素

5.执行器

根据一系列的执行计划去调用存储引擎提供的API接口去操作数据，完成SQL的执行。

3. innodb存储引擎的执行过程

（1）首先MySQL执行器根据执行计划调用存储引擎的API查询数据
（2）存储引擎先从缓存池Buffer pool中查询数据，如果没有就会去磁盘中查询，如果查询到了就将其放到缓存池
（3）在数据加载到Buffer pool的同时，会将这条数据的原始记录保存到undo日志中
（4）innodb会在Buffer pool中执行更新操作
（5）更新后的数据会记录在redo log buffer中
（6）提交事物在提交的同时会做以下三件事
（7）（第一件事）将redo log buffer中的数据刷新到redo log文件中
（8）（第二件事）将本次操作记录写到bin log中
（9）（第三件事）将bin log文件名和更新内容在bin log中的位置记录到redo log中，同时在redo log最后添加commit标记
（10）使用一个后台线程，它会在某个时机将我们Buffer pool中的更新后的数据刷到MySQL数据库中，这样就将内存和数据库中的数据保持统一了

4.常见的存储引擎

存储引擎是对物理层数据执行实际操作的组件，为server服务层提供各种各样操作数据的API，常用的存储引擎有InnoDB，MyISAM，Memory。

InnoDB和MyISAM的区别

（1）事务：MyISAM不支持事务，InnoDB支持事务。
（2）锁级别：MyISAM只支持表级锁，InnoDB支持行级锁和表级锁，默认使用行级锁，但是行级锁只有通过索引查询数据才会使用，否则使用表锁。
行级锁在每次获取和释放锁的操作比表锁消耗更多的资源。使用行锁可能会出现死锁的状况，但是表级锁不存在死锁。
（3）主键和外键：MyISAM支持没有任何索引和主键，不支持外键
              InnoDB主键不能为空且支持主键自增长，如果没有设置主键或者非空唯一索引，就会自动生成一个6字节的主键，支持外键完整性约束
（4）索引结构：MyISAM和InnoDB都是使用B+树索引，
不同的是MyISAM的主键索引和辅助索引的Data域都是保存行数据记录的地址，
而InnoDB的主键索引的Data域保存的是整行的数据内容，而辅助索引的Data域保存的则是主索引的值
tips：因为InnoDB在非主键索引内保存的是主键索引的值，而不是具体数据，所以辅助索引需要检索两遍索引列。
由于辅助索引存储的Data域是主键的值，因此不建议将主键设置过长，如果设置过长会导致辅助索引也变得非常大
（5）全文索引：MyISAM支持全文索引，InnoDB在5.6之前不支持全文索引，5.6版本之后开始支持全文索引
（6）表的具体行数：MyISAM会保存表的总行数，InnoDB不会保存表的总行数
（7）存储结构：MyISAM会在磁盘上存储成三个文件1.frm文件存储表定义2.myd文件存储数据3.myi文件存储索引
InnoDB把数据和索引存放在表空间里面，所有的表都保存在同一个数据文件中，InnoDB表的大小只受限于操作系统文件的大小，一般大小为2G
（8）存储空间：MyISAM可被压缩，存储空间较小。支持三种不同的存储格式：静态表，动态表，压缩表
InnoDB：需要更多的内存和存储，他会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
（9）适用场景：如果需要支持事务，崩溃恢复ACID事务的能力，选择InnoDB。
如果表主要用来查询数据，读操作远高于写，且不需要数据库事务支持的，选择MyISAM
备注：在8.0中MyISAM已经废弃

5.事务的ACID与实现原理

数据库的事务是并发控制的基本单位，是指逻辑上的一组操作，要么全部执行，要么全部不执行。

事务的ACID

1.原子性：事物是一个不可分割的工作单元，事物里面的操作要么都成功，要么都失败，如果事务执行失败，则需要进行回滚。
2.隔离性：事务所操作的数据在提交之前，对其他事务的可见程度。
3.持久性：一旦事务提交，它对数据库的数据的改变就是永久的。
4.一致性：事务不能被破坏数据的完整性和业务的一致性。

ACID的实现原理

1.原子性

原子性是通过MySQL的回滚日志undo log来实现的，当事务对数据库操作进行修改时，InnoDB会生成对应的undo log，如果事务执行失败或者调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

2.隔离性

事务的隔离级别：
为了保证在并发环境下读取数据的完整性和一致性，数据库提供了四种事务隔离级别，隔离级别越高，越能保证数据的完整性和一致性，但是对高并发性能的影响也会越大，执行效率越低

1.读未提交：允许在事务执行过程中，读取其他事务尚未提交的事务
2.读已提交：允许事务在执行过程中，读取其他事务已经提交的数据
3.可重复读（默认级别）：在同一事务内，在任意时刻的查询结果都是一致的
4.读序列化：所有事务逐个依次执行，每次读都需要获取表级共享锁，读写会相互阻塞

3.事务的并发问题：

如果不考虑事务的隔离性，在事务并发的环境下，可能会存在存在如下问题

1.更新丢失：多个事务互相不知道对方的存在，都去更新的一条数据相互覆盖，最终导致数据混乱。
2.脏读：数据A修改了某条数据，并且未提交，这时候事务B也使用了这个数据（读到了修改后的数据），后来A事务撤销回滚了事务，并把修改后的数据复原了，这时候B的数据就与数据库内的数据不一致，即B读到数据就是脏数据。
3.不可重复度：A事务多次读取了同一个数据，事务B在这期间更新了数据并提交了，
则A的多次读取到的数据会不一致
4.幻读：在A事务中，先后两次读取相同的数据（范围查询），但是由于B新增或者删除了某些数据，则A事务前后两次读取的数据会不一致

不同的事务隔离级别，在并发环境会存在不同的并发问题：

是否可防止	脏读	不可重复读	幻读
读未提交	否	否	否
读已提交	是	否	否
可重复读	是	是	否
序列化读	是	是	是

4.事务隔离级别实现原理

InnoDB的事物隔离级别是由MVCC机制和锁机制实现的：
MVCC（多版本并发控制）:
备注：MVCC只在RR（可重复读）和RC（读已提交）级别生效，MVCC是为了解决读-写冲突的无锁并发控制而产生的
依赖于数据库的三个隐藏字段，undo log，read view来实现
1.三个隐藏字段

DB_TRX_ID：最近修改的事务id，创建这条记录或者最后一次修改这条记录的事务id
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向还是那个一个旧版本
DB_ROW_ID：隐藏的主键，如果表中没有主键，innoDB会自动生成这个主键

2.undo log
InnoDB引擎特有的回滚日志，会记录一条数据的更新状况，形成一条数据链，当事务更新数据的时候会生成一条新数据行，数据行的DB_ROLL_PTR指向旧的数据行
3.read view
查询视图，用来维护事务的隔离级别，在一个事务开始快照读的时候创建的，是多版本并发控制实现的核心，用来管理一个事务能看到历史数据版本的视图，主要结构分为四部分m_ids，min_trx_id，max_trx_id，creator_trx_id。

备注：快照读和当前读

• 快照读：像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

• 当前读：像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

• 总结：简单理解为当前读就是加锁的读（不论共享锁即S锁，还是独占锁即X锁），快照读就是不加锁的读

m_ids：记录在生成当前read view的时候活跃的事务id
min_trx_id：记录当前活跃事务中最小的事务id，也就是m_ids中最小的id
max_trx_id：记录在创建当前read view时即将要分配给下一个事务的事务id
creator_trx_id：记录创建当前read view时所在的事务id

read view控制数据版本可视化原理：
read view对于其他事务修改的数据是否对当前事务可视有三个判断逻辑

1. 首先判断数据的DB_TRX_ID是否大于当前的事务id即min_trx_id，如果小于则当前数据对事务是可视的，如果大于则进入第二条判断
2. 判断当前数据的DB_TRX_ID是否大于等于max_trx_id，如果大于，则证明这一条数据是当前数据是生成read view之后由其他事务生成的，那么其他事务生成的肯定是对当前事务不可见的。如果小于则进入下一个判断
3. 判断DB_TRX_ID是否在m_ids中，如果存在，则证明当前数据还在活跃当中，没有commit。如果不存在，则证明数据在当前read view生成前就已经提交了，所以数据对当前数据是可以查看的。

问题：RR是如何在RC的基础上解决了不可重复度的问题？或者说在InnoDB中RR和RC隔离级别的快照读有什么不同？

答案：RR是在事务开始后只在第一次快照读生成read view，但是RC会在每一次快照读的时候都会生成read view，因此RC可以在事务期间读到其他事务提交的数据，而RR由于在事务期间只有一个read view，所以在此read view之后提交的数据对当前事务都是不可见的。

锁机制：
MySQL的锁机制工作原理就是在修改数据前，需要先获得数据相应的锁，获得锁的事务才可以修改数据，当其他事务想要修改数据需要等待当前事务提交或回滚后锁得到释放才能进行操作。

• 排它锁解决了脏读
• 共享锁解决不可重复度
• 临间锁解决了幻读

备注：虽然锁机制能够解决事务隔离性的问题，但是会造成大量的事务堵塞，性能下降。某些时候还会造成死锁，为了解决死锁，还需要引入死锁检测机制，造成性能的进一步下降，因此需要更高效的实现事物隔离级别的方法，也就是为什么会有MVCC机制

5.持久性

持久性依靠redo log日志来实现，在执行SQL时会保存已经执行的SQL语句到redo log文件，但是为了提高效率，会先将日志写入到redo log buffer缓冲区中。
具体执行流程：当有数据需要写入数据库时，执行过程会先写入redo log buffer，redo log buffer中修改的数据会定期刷新到redo log中，这一个过程被称为刷盘（即redo log buffer写日志到磁盘的redo log file中）。

redo log buffer的使用可以大大提高读写数据的效率，但是也带来了新的问题：如果MySQL宕机，而此时redo log buffer中修改的数据在内存还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。为了保证持久性，在当事务提交时，会调用fsync接口对redo log进行刷盘，刷新频率由innodb_flush_log_at_trx_commit变量来控制：

• 0: 表示不输入磁盘
• 1: 表示每次提交事务的时候，就把缓冲区中的数据刷新到磁盘中
• 2: 提交事务的时候，把缓冲池中的数据写入磁盘文件（redo log file）对应的os cache缓存里去，而不是直接进入磁盘文件。可能1秒后才会把os cache里的数据写入到磁盘文件里去。

6.一致性

一致性是指事务不能破坏数据的完整性和业务的一致性：

数据的完整性：实体完整性，列完整性（如字段的类型，大小，长度要符合要求），各种约束等
业务的一致性：例如银行转账前后，总金额应该一致

那如何保证一致性？
原子性，隔离性，持久性三者都实现即可保证数据的一致性

6.数据库中的锁机制

当数据库中多个事务并发操作同一数据的时候，若对并发不做控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
锁机制的工作原理就是：
事务在修改数据库之前，要先获得对应的锁，获得锁后的事务才能修改数据；在事务操作数据期间，这部分数据是锁定的，如果其他事务需要修改数据，需要等待当前事务提交事务或回滚后释放锁。

按照不同的分类方式，锁的类型可以分为以下几种：

按照锁粒度划分：行级锁，表级锁，页级锁（间隔锁）；
按锁的类型划分：共享锁（S锁），排它锁（X锁）；
按锁的使用策略划分：乐观锁，悲观锁；

6.1 表级锁，行级锁，页级锁

• 表级锁：最大粒度的锁级别，发生冲突的概率最大，并发度最低，但是开销小。
• 行级锁：最小粒度的锁级别，发生冲突的概率最小，并发度最高，但是开销大，加锁慢，会发生死锁。
• 间隔锁：粒度介于表级锁和行级锁之间，性能也介于行级锁和表级锁之间。

不同引擎对锁机制的支持：

• InnoDB：支持行级锁和表级锁，默认情况下在查询使用索引时，使用行级锁，否则使用表级锁
• MyISAM,MEMORY：引擎采用的是表级锁
• BDB：使用间隔锁，但是也支持表级锁

6.2 InnoDB的行锁

InnoDB的行锁有两种类型：

• 共享锁（S锁，读锁）：多个事务可以对同一数据行共享一把锁，但是只能读，不能修改；
• 排它锁（X锁，写锁）：一个事务获取排它锁后，可以对锁定范围内的数据执行写操作，在锁定期间，其他事务不能再获取这部分数据行的锁（包括共享锁和排它锁），只允许获取到排它锁的事务进行更新数据。

对于update，insert，delete操作，InnoDB会自动给涉及的数据行加排它锁；普通select不会加锁，select …… for update加X锁，select …… for share加S锁。

6.3 InnoDB的表锁与意向锁

因为InnoDB引擎允许行锁和表锁共存，实现多粒度的锁机制，但是表锁和行锁虽然锁定范围不同，但是会相互冲突。
例如在加表级排它锁时，需要检查表中是否有其他表级排它锁或者行级排它锁，这样就需要对全表进行扫描，这种方法显然是低效的。因此MySQL引入了意向锁，来检测表锁和行锁的冲突。

意向锁也就是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁），当事务要在记录上加锁时，则先要对表上加上对应的意向锁。之后事务如果想进行表锁，只要先判断是否有意向锁存在，如果存在，则可以快速返回该表的锁不可加，否则可以加对应的锁，这样就无须遍历整个表，可以提高效率。

6.4 InnoDB行锁的实现与临间锁

InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引检索数据才能加上行锁，否则加上的是表锁。

InnoDB为了解决幻读的问题，引入了临间锁（next-key lock）。根据索引划分区间，划分成一个个左闭右开的区间。当进行范围查询的时候，若索引命中并且能检索到数据，则锁住锁在记录和它的下一个区间。其实，临间锁=记录锁+间隔锁

• 间隔锁：当是范围查询而不是精准查询时，并请求排它锁或者共享锁，InnoDB会给符合条件的已有记录索引加锁；对于键值在查询范围内但并不存在的记录（就是对应索引项没数据，比如：数据库的A列有1，3，4，5，则2位置就是指的这种不存在的记录），叫做间隙
• 记录锁：使用唯一索引，且记录存在的精准查询，使用记录锁。

6.5 利用锁机制解决的并发问题

• X锁解决脏读：在事务未提交前，别的事务不可以对本事务涉及的记录上锁，所以对其他事务也是不可见的
• S锁解决不可重复读：加了S锁以后，记录不能再被加X锁，即不能被其他事务进行修改了，所以在事务期间都是可重复读
• 临间锁解决幻读：临间锁锁住了记录和它的下一个间隙，其他事务不能往间隙里插入其他数据，所以解决了幻读问题

7 MySQL索引的实现原理

索引本质上就是一种通过减少查询需要遍历行数，加快查询速度的数据结构，避免数据库进行全表扫描（一个表最多16个索引）

7.1 索引的优缺点

优点：

• 减少查询需要检索的行数，加快查询速度，避免进行全表扫描。
• 如果索引的数据结构是B+树，在使用分组和排序时，可以显著减少查询中分组和排序的时间
• 通过创建唯一索引，可以保证表中数据的唯一性。
  缺点：
• 当对表中数据进行增删改时，索引也需要对应的维护，且维护成本随着数据量增大而增大
• 索引建立需要消耗额外的空间，如果建立聚簇索引则需要更大的空间（索引和数据需要存储在一起）

7.2 索引的建立原则

建立原则：

• where 语句中经常出现的列建立索引
• 按范围存取的列或者出现在group by或者order by中的列，因为索引已经建立，这样可以利用索引加快查询排序时间
• 经常用于连接的列上，这些列主要是一些外键，可以加快连接速度
• 作为主键的列上，强制该列的唯一性和组织表中数据的排列结构

不建议创建索引的列：

• 区分度不高的列，例如性别
• 在查询中很少用到的列
• 当维护索引的成本比查询带来的成本提高还要高时，因为会降低表的插入速度
• 定义为text，image和bit数据类型的列不应该创建索引

7.3 索引的分类

1.普通索引，唯一索引，主键索引，全文索引，组合索引

• 普通索引：最基本的索引，没有任何限制
• 唯一索引：索引值必须唯一，允许有空值，并且可以有多个空值。如果是组合索引，则列值的组合必须唯一。
• 主键索引：一种特殊的唯一索引，不允许有空值
• 全文索引：全文索引只支持char，varchar或者txt类型，用于代替效率较低的like模糊匹配操作，而且可以通过多字段组合全文索引一次性匹配多个字段。
• 组合索引：主要是为了提高mysql效率，创建时使用最频繁的字段列放在左端，按使用频率一次递减

2.聚簇索引与非聚簇索引

按照索引和数据的存放位置，索引可以分类为聚簇索引和非聚簇索引

• 聚簇索引：表中数据存储物理顺序与索引值的顺序一致，一个基本表只能有一个聚簇索引，更新聚簇索引列的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于常常更新的列不宜建立聚簇索引
• 非聚簇索引：表中数据的物理顺序于索引值的顺序不一致的索引组织，一个表可以创建多个聚簇索引。

7.4 索引的数据结构

常见的索引数据结构有：B+树，Hash索引

Hash索引：
Hash索引在InnoDB中需要开启，并且索引不能人为干预，hash索引把数据用hash值的方式组织起来，因此查询效率非常高，可以一次定位

hash索引优缺点：
优点：查询效率非常高，可以一次定位
缺点：

• hash索引只支持等值查询，不能进行范围查找和排序，因此数据经过hash算法后，其大小关系就可能发生改变。
• 当hash索引创建组合索引后，不能利用起它的部分进行查找，因为hash组合索引是将多个字段组合起来再计算hash值，所以对单独数据列进行hash计算是无法定位数据的
• 当发生hash碰撞时，hash索引不可避免的要扫描表数据。因此仅仅比较hash值是不够的，需要比较实际的值来判断是否符合要求。

b+树索引：b+树索引是MySQL使用最频繁的索引类型，是InnoDB和MyISAM存储引擎默认的索引类型。B+树在查找时需要从根节点到叶子节点进行多次IO操作，但是查询速度比不上hash索引，但是适合排序分组和范围查找等场景。

b+树的优点：

• 页内节点不存储数据，每次IO可以读取更多的行，大大减少磁盘I/O读取次数
• B+树所有数据都存在在叶子节点上，并且叶子节点之间有相互访问指针，可以加快范围查询的速度

7.5 为什么使用B+树作为索引

索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘中。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，磁盘I/O存取的消耗要高几个数量级，所以一个数据结构作为索引的优劣重要的指标就是在查找过程中I/O操作的渐进复杂度。换句话说，索引的数据结构要尽量减少查找过程中磁盘I/O的存取次数。

（1）局部性原理与程序预读

由于磁盘本身存取就比主存慢很多，再加上机械运动耗费，为了提高查询效率，就要减少磁盘I/O。为了达到这个目的，磁盘往往不会是严格的按需读取，而是每次都会多预读一些数据，即使只读一个字节，也会将这个字节顺序往后读取一定的数据放入内存。这样做的原理是根据局部性原理：当一个数据被用到时，其附近的数据通常也会被马上使用到。程序运行期间所用到的数据通常比较集中。

由于磁盘顺序读取效率很高（不需要寻道时间，只需要很短的旋转时间），因此对于具有局限性的程序来说，预读可以提高I/O效率。预读长度一般为页的整数倍。当程序要读的数据不存在主存中时，会触发缺页异常，此时系统会向磁盘发出缺页异常，此时系统会像磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页的数据。

（2）B+树索引的性能分析：

B+树相比于二叉树能够用更浅的深度来存储更多的索引数据，加快数据的检索速度（每个节点最多遍历logdN层就可以找到），并且数据库利用节点预读策略，将每个节点的数据都设置在一个页中，这样可以实现每个节点只进行一次I/O读取就可以获取全部数据，加快了查询速度。

而二叉平衡树或者红黑树因为子节点个数的限制，要找到叶子节点的数据需要遍历很多层，并且逻辑上相近的数据可能会存储在相隔很远的物理位置，所以无法利用局部性预读策略，因此IO效率明显比B树差很多

B+树是对B树的增强，B+树的查询耗时比较稳定，且由于叶子节点是互相连接的，对于范围遍历来说比B树更快。

（3）B+树和B树索引的对比

• B+树有利于对数据库的扫描：B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题，而B树只扫描叶子节点就可以解决对全部关键字信息的扫描，所以对于范围查询，排序等操作，B+树有着更高的性能。
• B+树磁盘IO代价更低：B+树的内部节点的data域并没有存储数据，因此其内部节点所占空间比B树更少。如果把所有同一关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也就越多。一次性读入内存中所需查找的关键字也就越多，相对来说IO读写次数也就降低了。
• B+树的查询效率比B树更加稳定：因为B+树中间节点并不存储数据，只在子节点存储数据

8 索引优化，查询，表结构优化

8.1索引失效的场景：

• like语句前导模糊查询，例如“%zhangsan”
• 范围条件右边的列不能使用索引，所以对于组合索引，范围查找要放到最后
• 使用部分函数的时候会使索引失效，例如：YEAR,SUBSTRING等（explain SELECT * FROM user where substring(name,1,3)='abc'）
• where中使用变量也会导致索引失效
• 强制类型转换会导致索引失效

8.2索引使用原则

• 组合索引的最左匹配原则，创建索引的时候区分度最高查询频率最高的列放到左边
• 尽量利用索引覆盖来进行查询操作，避免回表，减少select *的使用
• 利用索引下推来减少回表次数
• join字段尽量建立索引，表关联最好不要超过三个，需要join的字段数据类型要一致
• 单表索引尽量控制在5个以内
• explain中type最少要达到range级别，要求是ref级别，最好是consts级别（index有序的全表扫描，all顺序的全表扫描）
• 具有唯一特性的字段数据，即使是多个字段的组合也必须创建唯一索引，防止脏数据的产生
• 更新频繁，区分度不高的字段不要创建索引

索引下推：比如创建了一个name,age,position的组合索引
查询一个name like 'zhangsan%' and age = 18 and position = 'xxxx'
如果不使用索引下推：sql只能在存储引擎利用索引查找到所有name是zhangsan开头的数据，然后拿着这些数据一条一条回表查询，将结果返回给执行器，再由执行器判断这些数据的age和position是否符合条件
如果使用索引下推：存储引擎会在找到name是zhangsan开头的同时，也会过滤掉age和postion不匹配的数据（因为都在索引中），再将这些数据通过主键索引回表查询，将结果一并返回给执行器

8.3查询的优化

• 只查询必要的字段，避免select *的出现
• 只返回必要的行，如果有必要limit限制要加上，因为数据库会在查询到指定数量的行后停止查询
• 分页场景使用延迟关联或者子查询，例如
  select a.* from 表1 a,(select id from 表1 where 条件 limit 100000,20) b where a.id=b.id；
  这样做的好处是可以利用主键进行快速分页并且用主键关联不需要回表即可获取到数据
• 分解大连接查询，对于三个以上的表进行关联，可以进行分段关联再通过应用程序进行关联，使查询变小提高效率
• 避免使用select的内联子查询，因为外表要查多少行内联子查询就要执行多少次
• 尽量使用join代替子查询
• 多张大表join先分别过滤后再join
• 尽量避免使用or来关联多个条件，可能会导致索引失效
• 对于连续的值使用between而不是in
• 在列上使用表别名，避免数据库对列所属关系的解析

8.4表结构的优化

数据类型的选择

数字型：

能用整数用整数，小数不仅是存储更耗费空间，而且还会有精度问题，固定精度的消暑建议乘以固定倍数转换为整数

字符类型：

长度固定选用char，长度不固定按需求选择长度合适的varchar，万不得已不要使用text
txet存短数据没必要，长数据可以存入文件
char存储数据长度固定，不够长度也会按固定长度存储
varchar会按照实际长度存储

时间类型：

尽量使用TIMESTAMP类型，因为其存储空间只占DATETIME类型的一半。
对于只用精确到天的数据，建议使用DATE类型，因为它的存储空间只需要占3个字节，比TIMESTAMP还要少。

表结构设计

• 适度冗余字段，避免过度的关联查询，可以通过空间换时间来提高查询效率
• 尽量使用not null，因为null会影响索引效率。

9 MySQL的主从复制

9.1主从复制原理

Slave从Master获取binlog二机制日志文件，然后将日志文件解析成相应的SQL语句在Slave端重新执行一遍主服务器的操作，通过这种方式来保证数据的一致性。由于主从复制的过程是异步的，因此Slave和Master之间的数据有可能存在延迟的现象，只能保证数据的最终一致性。在Master和Slave之间实现主要是由三个线程来实现的：

1. Slave SQL Thread线程：创建用于读取relay log中继日志并执行日志中包含的更新，位于Slave端
2. Slave I/O Thread线程：用于读取Master服务器的Binlog Dump线程发送的内容并保存到Slave服务器端的relay log中继日志中，位于Slave端
3. Binlog dump Thread线程：将bin-log二进制日志中的内容发送到Slave服务器，位于Master端
   注意：如果一台Master配备两台Slave，那么主服务器上就会有两个Binlog dump线程，而每台Slave上各有两个线程

9.2 主从复制流程

1. master服务器上执行SQL语句后，记录到日志文件binlog中
2. slave端IO线程连接到master端，并请求从指定binlog中指定pos开始复制之后的内容
3. master端在接收到slave发出的复制请求后，会通知负责复制的IO线程，根据slave指定的读取位置，然后将内容返回给slave客户端，并且返回数据中包含本次读取到的binlog文件名和binlog位置
4. slave的IO线程接受master的IO信息，将接收到的信息依次写入relay log文件的最末端，并将读取到的binlog名称和读取位置记录到master-info文件中（此文件位于slave端），以便在下一次同步的时候可以告知master读取位置
5. slave端的SQL线程在监测到relay log中文件有增加的时候，会马上解析relay log中的内容翻译成执行SQL语句，再按照顺序依次执行这些SQL，从而达到master和slave端数据的一致

9.3 主从复制的优缺点

优点：

1.数据更安全，因为做了数据冗余，不会因为单台服务器宕机导致数据丢失
2.提升性能，一主多从，不同用户从不同数据库读取，提升性能，也可以实现主库从库的读写分离
3.扩展性更优，当流量增大时可以方便地增加从服务器，不影响系统的使用

缺点：

1.主从数据会由于异步同步的问题造成主从数据不一致
2.如果需要对从库数据进行修改，并且从库正在执行主库的bin-log时，就会出现错误而停止同步，这个操作很危险，因此对从库数据修改要格外小心

9.4 MySQL支持的复制类型极其优缺点

binlog日志文件有两种格式，一种是statement（基于语句的复制），另一种是row（基于行的复制）。默认格式为statement，如果想要改变格式需要在服务开启的时候指定-binlog-format选项，具体命令如下

mysqld_safe –user=msyql –binlog-format=格式 &

1.基于语句的复制（statement）：在主服务器上执行SQL语句，在从服务器上执行同样的SQL语句。效率比较高。

优点：

• 因为记录SQL语句，所以占用空间更少。binlog日志包含了描述数据库操作的事件，但这些事件包含的情况仅仅是对数据库改变的操作，例如insert，update，create，delete等操作。相反对于select，desc等操作并不会记录。
• binlog记录了所有改变数据库的语句，可以作为数据库审核依据。
  缺点：
• 不安全，不是所有改变了数据的语句都会被记录下来。对于非确定性的行为不会被记录。例如：delete或者update语句，如果使用了limit但是并没有order by，这就是数据非确定性语句，就不会被记录。
• 对于没有索引条件的update，insert语句，会锁定更多的数据，降低了数据库的性能。

2.基于行的复制（row）：把改变的内容复制过去，而不是把命令在服务器上执行一遍，从MySQL 5.0开始支持。

优点：

• 所有的改变都会被复制，这是最安全的复制方式。
• 对于update，insert语句等锁定的行更少。
  缺点：
• 不能通过binlog日志查看都执行了什么语句，也无法知道在从服务器上接收到了什么数据，我们只能看到什么数据改变。
• 因为记录的是数据，所以说binlog日志文件的存储空间会比statement占用更大
• 对于数据量大的操作花费的时间更长。

3.混合类型的复制：MySQL默认采用的就是混合类型的复制，即一旦发现语句无法精确复制了，就会采用基于行的复制。

10 读写分离：

10.1 读写分离实现原理

读写分离解决的数据库的写操作影响了查询效率，适用于读远大于写的场景。读写分离的原理实现基础是主从复制，主库利用主从复制将自身数据改变同步到从数据库集群中，然后主库负责写操作，从数据库负责读操作。并且可以根据需求，增多从库，缓解查询压力

10.2 读写分离提高性能的原因

• 从服务器增加显而易见可以分摊负荷
• 主从只负责各自的读写，极大程度的缓解X锁和S锁的竞争
• 从服务器可以设置MyISAM引擎，提升查询性能以节约开销
• 增加数据的冗余，即使有服务器宕机也不会导致数据的全部丢失

10.3 MySQL 读写分离的实现方式

基于程序代码内部实现

优点：可以在代码中实现，不需要引入额外中间件。不会引起程序变复杂
缺点：增加代码复杂度，且只能开发人员运维，运维人员无法参与维护

基于中间代理层

引入代理层，在数据库集群和应用服务器之间，代理数据库接受到查询请求后，将请求转发到读数据库，在接受到修改请求后将请求转发到写数据库

优点：不会增加代码复杂度，开发人员无感知，实现了主从数据库和应用代码的解耦
缺点：会增加系统的复杂度，运维成本增加

11 分库分表：垂直分表，垂直分库，水平分表，水平分库

11.1 垂直拆分

1.垂直分表：
将一个表按字段拆分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。

优点：

• 避免IO竞争减少锁表的概率。
• 可以更好地提升热门数据的查询效率

缺点:

• 大字段占用空间更大，单页内存储的行数会变少，会使得IO操作更多
• 数据量更大，需要更长的读取时间

2.垂直分库：
按照不同的业务模块，将表拆分到不同数据库中，适合业务之间的耦合非常低，业务逻辑清晰的系统。

优点：

• 降低业务之间的耦合，方便对不同的业务进行分级管理
• 可以提升IO，数据库连接数，解决单机硬件存储资源的限制

3.垂直拆分的缺点：

• 主键出现冗余，需要管理冗余列，根据主键更新需要更新多个表
• 事务的处理变得复杂
• 仍然存在但表数据量过大的问题

11.2 水平拆分

1.水平分表：
在同一个数据库内，把同一个表的数据按照一定规则拆分到多个表中

优点：

• 解决了单表数据量过大的问题
• 避免了IO竞争并减少锁表的概率

2.水平分库：
把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。

优点：

• 解决单库数据量大的瓶颈问题
• IO冲突减少，某一个库出现问题不影响其他数据库，提高了系统的稳定性

3.水平拆分的缺点：

• 分布式事务的一致性难以解决
• 跨节点join性能变差，逻辑会变得复杂
• 数据扩展难度大，不易维护

11.3 分库分表存在的问题解决

1.事务问题

方案一，使用分布式事务：

• 优点：由数据库管理，简单有效
• 缺点：性能代价高，特别是shard越来越多的时候，并且对水平分表支持性不好

方案二，应用程序于数据库共同实现：
原理就是将一个跨多节点的事务拆分成一个一个单接点的小事务，有代码控制他们的原子性。

• 优点：性能上有优势
• 缺点：需要在应用程序上做灵活控制。增加代码复杂度

2.跨节点join，count，order by，group by

这类问题都要依赖数据库和程序代码共同处理，同时向各个数据库发起查询，将结果在内存中进行合并。
join可以先查询出主表中的数据，然后找出关联列的值，再进行二次查找
count直接查询各个表中符合条件的数据即可
order by，group by也是查询出符合条件的数据，然后内存中进行二次排序

3.跨节点分页查询

跨节点分页查询可以和业务妥协，变为下一页的方式。也可以牺牲性能换取准确的查询结果

全局视野法：
如果查询第X页数据，可以向各个节点发送查询页数数据，将order by time offest X * pageSize limit pageSize，改写成order by time offest 0 limit (X+Y) * pageSize，再将N个表中的数据在内存集中计算第X页
方案局限性很明显，页码越靠后，整个查询将会越缓慢

禁止跳页法：
只允许下一页式的请求，返回第一页数据后，发起第二页请求时带上第一页请求数据的最大创建时间，然后向各个表查询大于此时间的一页数据，再将这个数据在内存中整合后截取第二页数据返回

允许精度丢失法：
在业务可以允许不准确地请求第二页数据时，可以向每个表中请求同等的数据，例如请求第二页的数据，每页50条，一共有五台分表的MySQL数据库，可以向每台请求5条，然后返回

二次查询法：
先改写查询，如果要查100条数据，则根据分库个数将100条查询平均非配到每个库里，拿到查询数据后，找到其中创建时间最小的一条，然后按照这一条当作最小时间，对每个库再次发出between请求，最小时间是这个查询时间，最大时间是上一次各个库返回的数据的最大时间，然后计算第二次查询比第一次各个库都多了几条，算在偏移量里，然后往后推当前页需要的数据返回，比如第二次查询比第一次多了五条，则证明当前最小数据是上一页的倒数第五条，需要往后推五条后返回即可。

4.分库分表后ID键如何处理

• uuid本地生成，全局唯一不重复，缺点是占用空间太大，不适合作为索引。
• 数据库自增，用一个专门的表来维护全局自增的id
• redis生成id
• 其他生成唯一id算法

12 分区

分区就是将MySQL存储的数据分开，存储在不同的数据文件中，在查询的时候只需要查询对区的数据，而不用查询全部数据。常见的分区类型有：Range分区，List分区，Hash分区，Key分区

• Range分区：按连续区间范围进行分区
• List分区：按照给定的集合中的值进行分区
• Hash分区：基于用户给定的表达式返回值进行分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的，产生非负整数值的任何表达式
• Key分区：类似于按照Hash分区，区别在于Key分区只支持一列或多列，且Key分区的哈希函数是由MySQL服务器提供

12.1 分区表优点与限制

优点：

• 相比于单个磁盘或文件，分区表可以存储更多数据，可以跨磁盘存储文件
• 对于使用了分区键的查询可以加快查询速度
• 对于count，sum等操作可以并行计算
• 对于已经失去了保存意义的数据可以很快地删除

缺点：

• 分区表达式必须是整数，5.5以后某些场景可以用字符串或日期进行分区
• 如果分区表中有主键或者唯一索引，那么分区键必须是主键或者唯一索引
• 分区表中无法使用外键
• 不支持临时表进行分区，不支持全文索引

分区表适用于OLAP系统，对于OLTP系统提升效果不大，谨慎使用

本人承接系统二次建设开发，有兴趣的同学可以添加微信admjgj122254