你了解MySQL？

开局一张图,内容全靠编

实例&&数据库傻傻分不清

• 数据库

  就是一堆数据文件的集合，相当于一个大文件夹其中包含了各种格式的数据文件，不同的数据格式代表使用了不同的存储引擎，如我们经常使用的innoDB在磁盘上的存储格式就是.ibd文件，还有frm MYD MYI格式的，代表不同的存储引擎。如果当前的数据表出现问题，可以从这个数据文件执行恢复数据。需要特别注意的是存储引擎的粒度是数据表而不是数据库，所以在建表的时候是可以指定存储引擎的。

• 实例

  MySQL实例其实就是一个守护进程mysqld再加上一块共享内存，这个实例就参操作那些数据文件子这块内存和硬盘上来回复制，

切记有人以后再问及数据库实例和数据库时候就可以清晰的解释了，实例就是一个进程，单机而言一个数据库对应一个实例，集群下就是一个数据库对应了多个实例。

### MySQL数据库结构

上图是摘自MySQL技术内幕一书中的配图，我相信大多数的开发人员不是很清楚MySQL到底是怎么的结构，大多数使用时候只是在项目中添加连接的jar包，配置数据源，然后crud，其内部构造不是业务的关注点，故而了解的点在开发人员是较为有限，不是很深入，但是作为软件开发人员最好对数据库有一个较为深入的理解，虽然我们不是DB开发程序员，但是熟悉数据库的构造，存储引擎，有助于我们更好的建立数据表，数据恢复等等，题外话就此打住，切入正题，上图中的最上层为多语言的支持，为各个语言提供api接口，用于交互数据库实例进行业务数据的操作，紧接着为连接池，最左边是暂且理解为管理中心，接着右边依次是sql接口实现，查询分析器 ，优化器，缓存 ，下面一层就是MySQL的插件存储引擎，用户可以在建立数据表的时候指定任何一个存储引擎，最下面就是磁盘的文件，所以整个MySQL的结构分层有序，而且灵活性十足，你要足够牛X，你可以自己实现存储引擎的接口，满足你的特殊需求。并不是我们单纯的启动电脑 启动mysql 好了，启动应用服务器，run就完事了，整个应用和每一步都是上下层有序的进行着，只不过这个对于非数据库研发来说是个黑盒。
浅谈常用的MySQL几大存储引擎

• InnoDB

  作为MySQL支持事务的唯一一个存储引擎，在5.5.8 release版本的MySQL 中为默认存储引擎 该存储引擎存放的数据表为idb格式独立存储，通过乐观锁来实现并发访问，支持标准SQL的隔离级别默认可重复读，innoDB拥有类似Oracle的rowid，在无指定主键的情况下，默认使用rowid，在存储上是按照主键顺序存储的。

• MyISAM

  MyISAM呢不支持事务，不支持锁，但是支持全文检索，在5.5.8版本之前是MySQL的默认存储，MyISAM呢的缓存仅仅是缓存了索引文件这一点是和InnoDB不一样的地方，而且MyISAM的方式数据文件和索引文件是分开存放的，MYD存放数据，而MYI存放索引，5.0版本之前MyISAM默认存储的表的大小只有4G，如果要存放的数据超过该值，需要修改配置文件中的max_rows和avg_row_length，5.0之后呢默认支持256TB。

• NDB

  NDB呢是Oracle收购回来的，这特点就是内存型，所有的数据塞入内存中，所以其查询速度是异常的快，可以通过水平拓展增加数据节点来线性提高数据库性能，但是其JOIN操作是由MySQL自己完成的，所以复杂的join操作需要很大的网络开销。

至于Memory Archive Feddeated Maria 不是常见使用的类型，就不提了，有兴趣可以自行谷歌。

记得面试的上有问过 当数据量大于1000万之后，MySQL的性能如何保证，回答的答案也是众说纷纭，数据行数的增加性能肯定会有所下降这道理很简单，篮子里的鸡蛋多了要保证不碎肯定得小心翼翼的拿放，不说业务上的如果处理这大数据量，MySQL官方手册曾说，只要你选择合理的数据存储引擎和配置，MySQL可以满足你的要求。

我们怎么平时的增删改查是怎么和MySQL交互

首先从应用JDBC触发，连接阶段是携带上我们的用户名 地址 密码 要链接的数据库的名字等这些信息，通过MySQL实例开放的端口进行通信，首先请求到达mysql实例，去建立tcp/ip连接，建立连接之后，要先去查询权限是否满足，如用户名 密码 以及请求的ip地址，这一系列校验通过之后，就可以从连接池中获取一个连接,之后的数据操作工作呢，就有这个连接池中的实例去通过访问sql接口来进行文件的读写。

我们来看这个查询SQL执行，在mysql Server端到底执行了那些内部操作。

select * from T where ID=10  

首先要执行就需要先去建立连接，不管是mysql Client还是jdbc去连接的时候均会和mysql Server的连接器交互，在交互的过程中，回去校验用户的权限，然后建立一个数据库链接，从连接池中拿到一个实例，这是一个长连接，所以有很多数据库连接的话，数据库会很占内存，极坏情况下会重启，拿到数据库实例之后会利用mysql的分析器去吧这条sql语句去解析，在分析的过程中会去判断查询的column是否存在，如果不存在就会报Unknown Column 之类的错误，最主要的是会把sql中的表名映射为 数据库中的表，列映射为数据库的Column 映射完毕之后会对这条sql进行sql语法校验，要是这时候你的sql关键字写错了 就会提示你 ...have an error in your SQL syntx,出现之后你只需关注use near处的内容即可，然后经过语法校验完毕之后会走到优化器，在优化的过程中回看你的查询条件是否使用了索引，如果有多个索引时决定使用哪一个，还有就是join的时候要去决定连接的顺序，这一系列的操作执行完毕之后就到了执行器阶段了，在执行器阶段还是需要做一步权限验证，此时会校验你这个用户是否对该表有权限，如果有就回去交互存储引擎去拿去数据，读取数据的过程，会逐行搜索，当遇到就加入结果集中，没有则会走到最后一行，然后将拿到的结果集返回。

我们来看这个更新SQL执行，在mysql Server端到底执行了那些内部操作。

update T set name ='李茂' where ID=10  

和之前分析的查询语句执行一样，同样也会走连接器，分析器，优化器，执行器这一套，但是不同的是更新操作呢涉及到binlog，而innodb数据库引擎时候还会设计到redolog。redo log呢是innodb数据引擎特有的，mysql5.5版本之前都是myisam引擎，这个存储引擎呢，就是所谓的无事物，大表存储，mysql收购回来innodb引擎之后呢，才增加了事物，什么事redolog呢，就类似与cpu和主存之间的高速缓存。所有的更新操作在更新了redo页之后就返回结果集，应为频繁的交互磁盘带来的IO代价特别的高，而IO密集型的web引用来说，这部分的代价就灰常高，所有innodb存储引擎就搞了个redo页，默认是4个redo页，更新只去更新内存，有一条守护进程去做刷磁盘的工作。应为redo页的存在，异常重启时候，不会丢失之前提交写操作，binlog呢则是归档日志，就是说你所有的操作均会在这里体现，一般用于主从复制时候，从库备份主库的数据，还有就是，这俩个日志的区别是，binlog类似redis的aof模式，追加写，但是没有aof的合并命令的操作，基本是逻辑备份，你执行了什么sql就会在里面push什么记录，而redolog则不是，内存中指定了这些区域，是循环写的，当redo页满了之后，会通知进程去刷磁盘，然后刷完之后更新操作会覆盖之前。而redolog最著名的就是二踢脚，(二阶段提交)，更新内存之后就回去此时redo也先提交一次进入prepare，然后向binlog写入日志，最后在提交一次redo页进入commit，这样保证了redolog和binlog的数据一致。和这俩个log相关的两个数据库参数为innodb_flush_log_at_trx_commit,sync_binlog,这俩个设置为1，代表每一次更新均会持久化。建议在线上开启。数据无价。。。

事物隔离级别

事物的隔离 分为如下几种 READ_UNCOMMITTED READ_COMMITED REPEATABLE_READ SERLALIZABLE

针对上图的查询情况下，如果是Oracle的读提交模式下。那么V1==1 V2 V3的值都是2，而如果是读未提交的情况下呢，那么V1 V2 V3得到的值都是2，可重复读时候V1 V2都是1 V3的值是2，串行化则是，对同一行来说，写会加写锁，读会加读锁，也就是说 1改为2的b事物需要在a事物完事之后才可以，所以对应的值为v1=1 v2=1 v3=2。
提到事物呢我们就要说下这个隔离级别的实现，其依赖于回滚段的设计，也就是undo页，一个数据记录在数据库存在了多条备份，也就数MVCC多版本控制，依托于这个就可以实现4中不通的隔离级别。当然如果持有的事物特别长的时候，这样回滚段的占用就会非常感，这些就可能拖垮库。所以我=我们开发的时候要避免使用长事物，在建立连接的时候有时候会增加一个参数autoCommit=0这样的时候就会在开始建立连接就打开了事物，如果该连接一直没有释放得话，就是一个长事物。我们要避免这个参数的使用，
可以使用如下语句去查询事物较长的

-- 大于60秒的长事物
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

索引

索引是数据检索的一个重要的使用策略，是为了提高数据查询的速率，索引的设计模型一般统筹有序数组索引 hash表 二叉搜索树这几种模式，hash的模式适配于等值搜索，有序数据适用于静态数据的搜索，树的查询时间复杂度O(LogN)。

• hash表

  hash表其结构(key–value)。我们更具要查找的数据的hash值去定位key，然后在得到key之后去获取value，hashMap就是基于该模式的的数组加链表的情况来实现，拉链的方式来取解决hash冲突，采取hash表的模式检索不适用于范围搜索场景。

• 有序数组

  有序数组呢则是基于在等值查询和范围查询的场景中性能还是可以满足的，参考数组的随机访问即可以找到，但是结合数组的插入，如果插入的同事还要保证数组的有序，那么一定会有数据的移位，所以有序数组的检索场景在于静态数据的查询。

• 二叉搜索树

二叉搜索树的特点就是左子树都比根节点小，右子树都比根节点大。所以查询性能就是O(LogN)，为了保证这个性质，那么其必须是要做到插入或者删除的时候，要保证这个树的平衡。实际查询中我们可以使用的N叉树了。

我简述了上述的三种索引模型，innodb存储引擎下支持三种索引，全文索引，B+tree，哈希索引。

innodb的哈希索引比较特殊的是自适应模式无法人工干预在数据表使用hash索引。使用与等值搜索，如select * from table where name='xxxx';并且访问该数据页的模式必须一致持续访问超过100次，还有要保证该页被访问的N次=页中的记录的1/16.此时innodb存储引擎会建立自适应hash索引，可以通过SHOW ENGING INNODB STATUS来查看当前的AHI的使用情况。

B+tree索引呢就是使用的二叉搜索树索引模型来实现的。B+tree的数据结构已经不可以认为是一颗树了，因为b+tree的叶子节点之间用指针关联着，使用B+tree来做查询时比Btree(平衡多路查找树)减少了磁盘访问的次数，而且其节点的的大小也比Btree的占用空间小。

要理解B+tree数据结构，首先要知道二分查询，也叫做折半查找，针对有序数组进行的跳跃式的查找,通过与中间值的比较逐步划分查找区间。

public static int midFind(int[] array,int n,int start,int end){
        if (start>end) return -1;
        int mid=start+((end-start)>>1);
        if (n==array[mid]){
            return mid;
        }else if (array[mid]>n){
            return midFind(array,n,start,mid-1);
        }else {
            return midFind(array, n, mid+1, end);
        }
}
     6    
    /  \
   /    \
  3      7
 /  \      \  
2   5      8

[2,3,4,5,6,7,8]

构造一有序的二叉查找树，左子树都比根节点小，右子树都比根节点大，那么对于上面这个颗树来说，每次从根出发，找5为例，先找左子树，再找左子树的右子树，找了3次，以此类推找2,5,8的趟数均是3，如果是有序数组的顺序查找，那么找到8就得多进行3次的访问。二叉查找的速度平均也要比顺序访问快的多。二叉查找树呢只要符合右节点始终大于根，就可以任意构建，但是其性能也就不一样了，所以为了补充二叉查找树的稳定性，引入了平衡的概念，任意节点的俩个子树的高度最大差为1，为了保证插入和删除节点之后树的平衡，引入了左旋，右旋的操作。B+tree呢则是由精简的Btree+叶子节点顺序访问来构造。除了构造了一颗平衡查找N叉树，其所有的节点都是按照Key的大小顺序存放的。而且叶子节点之间通过指针关联着。

B+Tree的插入删除

当节点中搜索节点的个数达到5的时候进行页分裂。每页存放4个节点,叶子节点的个数必须是有序的，插入删除也需要保证这个顺序

                {[25]               |[50]                       |[75]    |[]}
            /            \               \                         \
          /                \               \                         \
   {[5]|[10]|[15]|[20]}—>{[25]|[30]|[]|[]}—>{[50]|[55]|[60]|[]}—> {[75]|[80]|[89]|[99]}

插入

当叶子节点没有满或者索引节点没有满的时候，按照顺序直接插入对应节点即可，如果说叶子节点满了达到了5个了而索引节点未满，就要分裂该叶子节点成左右节点，把中间节点提到索引节点中，小于中间节点的放在左叶子节点中，大于中间节点的放在右节点中。如果说索引节点满了叶子节点也满了，那么叶子节点分裂成左右节点，把中间节点提到索引节点中，小于中间节点的放在左叶子节点中，大于中间节点的放在右节点中，然后索引节点也一样。每次的平衡的代价就是节点的拆分，这就意味着大量的磁盘交互，也会发生旋转来吧记录移动到左右兄弟节点。

删除

B+tree的删除方式页的填充因子有关，填充因子在innodb中相关的参数为innodb_fill_factor默认100.表示使用时页是会剩余极小的空间，会分配1/16的用于未来的增长，只针对于索引节点和叶子节点，text和blob的溢出行是无效的。当叶子节点大于填充因子时，直接删除，如果该节点还是索引节点，那么用该节点的右节点来替换；如果叶子节点小于填充因子中间节点大于填充因子，那么就合并这个叶子节点和他的兄弟节点，同时会更新索引节点，如果说叶子节点和索引节点都小于填充节点时，合并这个叶子节点和他的兄弟节点，同时更新索引节点，合并索引节点和他的兄弟节点。

聚集索引和普通索引

数据库的索引分为了聚集索引和普通索引，聚集索引和普通索引的关键在于起叶子节点的存放内容，聚集索引存放的是整行的数据，而聚集索引存放的则是主键的值。

聚集索引就是根据主键构造了一张B+tree，叶子节点的存放的是整行记录，由于聚集索引构造按照主键的顺序来构造，所以检索的速度非常快，而且sql执行在优化器阶段更加侧重于聚集索引，由于是有顺序的，所以针对主键的范围查询和排序是很快的。应为对于主键的order By 操作实际上在查询收集结果集就处理了，并不会做filesort的操作。

普通索引则是在索引节点上就是被索引的key的值，而叶子节点呢则是主键的值，当使用普通索引的来查询数据的过程呢，就是需要从普通索引的B+tree上搜索到主键的信息，然后在做一次“回表”操作，再从主键查询得到整行的数据。

索引语法

ALTER TABLE ADD INDEX|KEY indexName (name,age);
ALTER TABLE DROP INDEX|KEY indexName;

CREATE INDEX indexName ON tableName(name,age);
DROP INDEX indexName ON tableName

Innodb的主键索引的创建和删除代价是极大的，会先创建一个临时表，然后通过ALTER TABLE 填充表结构，把原表的数据导入临时表，删除原表，然后重命名临时表。但是普通的索引则由于FIC机制的存在，会给原表加锁，在原表的基础上建立，完毕之后释放锁，删除普通索引的情况则是直接表示内部视图中辅助索引的空间标记为不可用即可。

查询某个表的索引使用情况

SHOW INDEX FROM TABLENAME;

着重关注结果中的Cardinality表示索引中唯一值的预估值，如果该值非常小，可能该索引的列就是不是高频变换的，增加索引的效果不明显，这个Cardinality的值会被查询优化器使用，可以使用 ANALYZE TABLE tableName去更新该值。当Cardinality等于NULL的时候，可能创建了索引却从未使用过。在做sql执行计划分析的时候，如果说俩次的分析结果不一致，一次使用表扫描 一次使用了索引，那么此时可以更新一下Cardinality的值，让优化器更高效的工作。

虽然普通索引的创建有FIC机制不用去创建临时表，但是加的锁会阻塞DML操作。至于为什么，我会在表锁MDL(Meta Data Lock)时候细致分析。所以再MySQL5.6之后增加了一个Online DDL，就是允许在执行普通索引创建的时候允许 insert update delete 操作，还有添加外键，column的重命名等。

ALTER TABLE ADD INDEX|KEY indexName (name,age) ALGORITHM = {DEFAULT|INPLACE|COPY} LOCK = {NONE|SHARE|EXCLUSIVE|DEFAULT};

ALGORITHM用于指定索引创建删除的算法，INPLACE则是原地操作不创建临时表，COPY则是回去创建，LOCK部分
NONE表示不使用锁，这是并发*度最高，但是有并发访问问题，SHARE则是会加一个写锁，阻塞写，对于读事物并不会阻塞，EXCLUSIVE则是，读写均阻塞，但是不会创建临时表，DEFAULT是默认判断当前是否可以使用这三种模式，NONE—>SHARE—>EXCLUSIVE

online DDL的实现原理则是在把DML的操作日志放到缓存中，先去更新DDL然后在重新把DML的操作从缓存拿出来，然后在对表数据进行操作。

回到Cardinality这个值上来看，我们知道索引的建立并不是越多越好，应为更新操作会对索引的进行维护，当然建立索引的字段应该是高选择性的，长范围的，不能是性别这种低频字段。这个值代表了数据库不重复记录的数。是个预估值。那么他和总行数的比值越接近1，证明该索引建立的越合理。

联合索引

联合索引这一块就有一个最左适配原则，比如我们在(a,b)俩列上建立 那么查询select * from t where a='xx' h和 select * from t where b='yy'和select * from t where a='xx' and b='yy'均会使用到索引， 如果是 select * from t where b='xx' and x='yy' 则不会使用到索引应为顺序不一致，，还有就是使用联合索引的时候，如果被查询的字段在联合索引中，则不会去再进行一次回表操作，所以我们要避免使用*去查询，而是查询指定字段的形式去。
还有就是在MySQL5.6之后，支持了索引下推，就是说在命中a索引的时候会在联合索引内部进行一次过滤。减少了回表查询次数。

PS:
在范围查询的时候 又是可能会导致索引失效(基于上面的联合索引)
select * from t wehre a>1 and b<1000

如果说SQL优化器不能自动指定索引的话，可以使用FORCE index来强制使用索引，只针对普通索引。

针对索引的MRR优化，使用set @@optimizer_switch='mrr=on,mrr_cost_based=off 来开启。在explain分析是可以看到 use Condition MRR 使用了MRR之后性能提升较为明显，而且针对联合索引的情况来说
select * from t where a>1 and a<1000 and b='xx', 会被优化为等值查询（1,‘xx’）…(1000,‘xx’）.

锁

全局锁

表锁

行锁