面试篇-MySql

事务的四大特性

ACID 原子性 一致性 隔离性 持久性
原子性：要么全部成功，要么全部失败。
一致性：前后的状态是一致的，就是从一个稳定的状态到了另一个稳定的状态。
隔离性：不同的事务之间是互不影响的。
持久性：事务提交后，会持久化到数据库。

事务隔离级别

读未提交，读已提交，可重复读，串行化
MySql的默认事务隔离级别是可重复读。
读未提交：当前事务可以读取到其它事务未提交的内容。存在问题脏读。
读已提交：只能读取到事务已提交后的数据。存在问题不可重复读。
可重复读：就是解决了不可重复读的问题。保证事务中，每一次读取的数据都是一致的。存在问题幻读。
串行化：所有事务串行执行。性能很低，需要阻塞。
例子：银行转账
事务一：用户A要转账1000给用户B。A账户要减1000，B账户要增加1000。
事务二：用户C要转账1000给用户A。C账户要减1000，A账户要增加1000。

脏读：之前读取到的未提交数据回滚了，导致读取到了错误的数据。
不可重复读：读取的中间，有其它事务修改数据，导致前后读取的数据不一致。
幻读：在一个事务中查询某个数据不存在，在此之后另一个事务新增了该数据，导致在当前事务插入失败。出现明明查询出来没有，但是插入的时候又提示重复的错觉。

如何实现可重复读？
MVCC，多版本并发控制。通过版本号来保证事务读取到的数据都是一致的。

数据库引擎

MySql的体系结构

MySql的体系结构是分层的，分为连接层，服务层，引擎层，存储层。
连接层：包括认证授权、连接数量等。
服务层：包括SQL接口、查询优化、缓存等。
引擎层：提供了可插拔的存储引擎，如InnoDB、MyISAM、Memory等。Mysql5.0之后默认使用InnoDB引擎。不同引擎的索引实现不一样。

常见存储引擎

InnoDB特点：支持事务、行级锁、外键。
InnoDB的逻辑存储结构：表空间、段、区、页、行。一个区大小是1M，一个页大小是16K。
MyISAM：不支持事务、外键、行级锁。支持表锁。访问速度快，是MySql早期默认的存储引擎。
Memory：数据存储在内存中，访问速度快。默认用的是hash索引 。

索引

索引结构

索引结构有B+树索引（大多数引擎都支持）、hash索引（只有Memory引擎支持）、全文索引、空间（R-tree）索引。

经典B+树叶子节点是单向链表，MySql 对B+树做了优化，形成了双向链表，利于排序。
Hash索引是基于哈希表实现，只在Memory中支持。InnoDB存在自适应hash，在特定条件下会将B+树索引转换为hash索引。

为什么选择B+树，而不是二叉树，红黑树，B树？

• 二叉树不是平衡的，可能导致性能很差。
• 红黑树是自平衡，但是每个节点只有两个节点。
• B树一个节点除了存储key还要存储数据，可能会导致树的高度变高。
• B+树除了叶子结点，其余节点只存索引，叶子节点才存数据，并且各个叶子节点会有双向指针，形成一个双向链表。

B+树的高度计算（可存储数据量）：
InnoDB一个节点对应一页，一页是16KB，即16x1024个字节。
InnoDB中一个指针占6个字节，主键以bigint为例，占8个字节。
一个节点约能存1171个指针。假设一行数据为1K，一页能存16行数据。
则：
高度为2的B+树能存18000+条数据。
高度为3的B+树能存2100W+。
高度超过3一般会分库分表。

索引类别

索引的类别可分为主键索引，唯一索引，普通索引，全文索引。
主键索引只能有一个，唯一索引、普通索引和全文索引都可以有多个。

从另一个维度可分为聚集索引和二级索引，二级索引也叫辅助索引。

• 聚集索引默认是主键索引，如果不存在主键索引默认取第一个唯一索引，如果不存在唯一索引，会为每行默认生成rowid，基于rowid生成聚集索引。聚集索引叶子节点存的是整行数据。
• 二级索引叶子节点只存了主键，如果需要其它字段信息，需要回表查询。
• 全文索引是基于文本建立的索引，基于倒排索引的机制。

最左前缀法则

联合索引如果中间字段不匹配，会造成索引的部分失效，中间以后的用不到索引。

索引失效

索引列进行运算，如substring
字符串不加引号会失效，因为会进行默认转换。
模糊匹配，头部模糊匹配会失效，尾部模糊不会失效。
or连接的条件，前后都有索引才会生效。
Mysql评估使用索引会比全表扫描还慢，就不会使用索引。（数据分布影响，符合条件的数据超过总数的一半则不使用索引）

SQL性能分析

查看SQL执行频率：show global status like ‘Com_______’ 一个下划线对应一个字符
查看慢SQL查询日志（默认10s，默认没有开启）
show profiles 查询每一条SQL的执行时间。
explain 查看SQL语句的执行计划，可以查看可能的索引，用到的实际索引等等信息。

SQL提示

可以手动通过指定索引，来优化数据库。包括use index、ignore index、 force index。

覆盖索引

应尽量使用覆盖索引，查询的字段在索引列都可以找到，避免回表查询。

前缀索引

可以对字符串的前缀建立索引。节省索引空间，提升索引效率。尽量保证索引的选择性比较高，即前缀字符串的不重复值占记录总数的比例。

SQL优化

SQL优化其实主要就是对索引优化。

Insert优化

• 多条insert合成一条进行insert，但是不能过长一般不超过1000。
• 多条insert 手动管理事务，减少提交次数。
• 尽量按主键进行顺序插入。顺序插入性能比乱序高，取决于mysql数据的组织结构，索引组织表。且所有数据形成了一个双向链表，顺序插入比乱序高。

主键优化

• 主键设计原则：主键不能设置太长，索引中存放的都是主键，会增加磁盘的空间和IO性能。
• 主键尽量设置成自增，插入时选择顺序插入。
• 避免对主键修改，因为会重新建立索引。
• 主键不要采用UUID或者其它自然主键，比如身份证号，一个是由于太长，另一个是不连续的会造成页分裂的现象。

页分裂：在对数据进行插入时，由于页空间有限，如果就两个页中间没有空间可以插入，前一个页会分出一半的数据，分裂成一个新
页，并对链表的关系重新建立关联。
页合并：每次在删除元素时，会看跟前一个页或后一个页能否进行合并。

查询优化

• 查询时建立联合索引。
• 对oder by和group by后面的字段建立索引。
• 尽量建立覆盖索引，避免回表查询。
• 对于多字段排序,如果排序的方式不一致，可以根据实际情况建立对应索引，比如按第一个字段升序，第二个字段降序。

Update优化

update 更新字段要加索引，否则行锁会变成表锁，影响并发性能。因为行锁是加在索引上的。

Limit优化

limit分页查询对于后面的数据分页查询效率低下，尽量采用加覆盖索引和子查询的方式

Count优化

• count(*) = count(1) > count(主键) > count(字段)
• 前两个不用取值，后两个要先把字段值取出来，count(字段)没有加非空约束的还要进行约束判断。count(*) Mysql内部专门对其进行了优化。

锁

包括全局锁，表级锁和行锁。

全局锁

全局锁只允许读，主要是在做数据库备份或迁移时使用。

表级锁

表级锁可分为表锁、元数据锁和意向锁。
表锁分为共享和互斥两种。
元数据锁（MDL）：元数据指的是表结构。为了防止DML（增删改查）和DDL（修改表结构）的冲突。当有增删改查时加的是元数据读锁，修改表结构时加的是元数据写锁（排他锁）。
意向锁：防止加表锁时要一行一行判断是否有加行锁，因此在加行锁时，会加一个意向锁。加表锁时根据是否存在意向锁进行判断。
意向锁可分为意向共享锁和意向排他锁，意向共享锁和表锁兼容。

行级锁

行锁也是分为共享锁和排他锁。
间隙锁和临键锁，可以防止幻读。
间隙锁：是把两个叶子节点数据之间的空隙锁住，不允许往空隙中插入数据，比如查询某个不存在数据。
临键锁：是锁住当前叶子节点和它前面的空隙，即行锁与间隙锁的结合。（不算是一个独立的概念，就是行锁和间隙锁的结合）
行锁锁住的是索引。

索引上的等值查询（唯一索引）查询不存在的记录，会对前后两条记录的中间加上间隙锁。
索引上的等值查询（普通索引）会对查询记录的前后加上间隙锁。
索引上的范围查询（唯一索引）会对范围内加上临键锁和间隙锁。
总结：会对查询数据范围内加上间隙锁或临键锁，防止其他事务插入间隙，避免幻读。间隙锁可以共存，一个事务加的间隙锁不会另一个事务也加间隙锁。

InnoDB引擎

InnoDB逻辑存储结构

InnoDB的逻辑存储结构包括：表空间、段，区，页，行。
一个区一般是1M，一页是16KB。一个区中有64个连续的页。
每个表默认都会有个表空间，一个表空间中可分为索引段，数据段，回滚段（undo log）
索引段：B+树的非叶子节点。
数据段：B+树的叶子节点。

InnoDB的内存结构

包括缓冲池、更改缓冲区，日志缓冲区，自适应hash索引。一个服务器80%的内存都会分配给缓冲区，来提升效率。
缓冲池（buffer pool）：主内存的一个区域，增删改查基本在缓冲池进行，定期刷新到磁盘，减少磁盘IO。
缓冲池以页为单位，页分为空闲页，使用页（数据未被修改），脏页（还未刷新至磁盘）
更改缓冲区（change buffer）（mysql 8.0才引入）：主要是针对非唯一的二级索引列。非唯一的二级索引基本会是非顺序插入，为减少磁盘IO，专门独立出来change buffer来进行增删改操作。而后合并至buffer pool中，定期再刷新至磁盘。
自适应hash索引：Innodb会自动监控索引页的查询，如果发现hash索引可以提升效率则会建立hash索引，不需要人为干预。默认开启。
日志缓冲区：用来保存要存入磁盘的log日志数据，包括redo log 和undo log。也会定期刷新至磁盘。

存在后台线程定期把缓冲池中的数据刷新到磁盘。

InnoDB的磁盘结构

事务原理

redo log

redo log叫重做日志，实现了事务的持久性。记录了事务提交时对数据页的物理修改。用于在把脏页（缓冲池与磁盘不一致的数据页）刷新到磁盘时，出现错误用来做数据恢复。
redo log涉及重做日志缓冲，和重做日志文件，当缓冲池中的数据被修改后，就会生成对应的重做日志，放在重做日志缓冲然后再刷新到磁盘。相比直接把缓冲池的修改直接刷新到磁盘相比，重做日志是采用追加的方式，顺序磁盘IO性能高于随机磁盘IO。

undo log

undo log保证了事务的原子性，叫做回滚日志，记录了事务修改前的数据，比如一条update语句记录的是反向的update语句。
事务提交后不一定会马上销毁undo log日志，需要看MVCC是否需要用到。

MVCC

是mysql实现读已提交和可重复读的原理。
MVCC多版本并发控制涉及三个因素：隐藏字段，undo log版本链，readview。

隐藏字段

mysql的表有两个隐藏字段，分别是当前最近修改的事务id，以及上一次修改的地址。

undolog版本链

undolog版本链实际就是根据undolog可以知道每次修改的历史记录。

readview

readview是读视图，是快照读获取数据的依据，它记录了活跃事务的id，以及当前事务的id。
对于读已提交而言，在事务开始后，每次其它数据更新的版本记录都会生成一次readview。
可重复读只有事务开始的时候会去生成readview，之后直接复用。

事务中分为当前读和快照读，当前读表示每次都能读到最新的数据，快照读对于读已提交来说，每次查询都会生成一个快照读，可重复读是只有第一个读才会生成快照读。

运维

分库分表

垂直拆分

垂直分库：不同表放在不同的库中。
垂直分表：将不同字段拆到不同表中。

水平拆分

水平分库：将同一个库的数据分到不同的库中，每个库的表结构一样。
水平分表：将同一张表的数据分到不同的表中，每个表的结构一样。

读写分离

将数据库读写操作分开。

工具

MyCat 作为中间件，可以处理分库分表以及读写分离。