MySQL高级

存储结构

【一个字段设置为主键后，数据库会自动为其建立主键索引】

• InooDB中，每个表都有主键，和主键索引；表在内存中的存储是按主键索引存储的，又因索引是B+树，B+树的每个非叶子节点的值就是键值和指针，叶子节点存储所有的索引和对应的数据行，所以InooDB中，表数据和主键索引存在一起-----索引组织表；
• 即数据存在主键索引里，InnoDB的数据文件本身就是索引文件，就是一颗B+树；

索引

索引概述

索引的优劣势

索引的结构

BTree结构

• B+树是在B树基础上改造的，他的数据都在叶子节点，同时叶子节点还加了指针。
  

• 每个节点的key是升序排列

一个例子

B+Tree结构

• 虽然B树的查找效率比B+高，但同B树相比，B+树的每一个节点少一个“指向数据的指针”，所以同样的内存大小【内存大小有限，若内存大小无限，则选用B树】，存储的B+树比B树多三分之一，存储的节点多，就能减少内外存之间的IO操作，提高效率；

MySQL中的B+Tree结构

• 下面是主键索引图示，叶子节点包含所有的key（即，主键），每一个key对应一整行数据；
  

为什么MySql索引使用B+树

参考

• 一般的树，一个节点可以有多个孩子，但一个节点只能存储一个元素，在元素非常多的时候，就使得要么树的度（节点拥有子树的个数的最大值）非常大，要么树的高度非常大，甚至两者都必须足够大才行。又因为索引是放在外存中的，查询这么庞大的树结构，使得IO操作（内存存取外存的次数）非常多，降低了性能；
• 然后是B树（多路查找树），其一个节点可以存储多个元素，这样可以降低树的规模，从未降低了IO操作次数，提高了查找速度，改善了性能；【我们不用红黑树，因为红黑树子节点只有两个，做到了树的平衡，但树的规模还是没B树限制的好】
• 但如果查询多条数据的话，B树因为每个节点只存储对应字段的值，所以需要做局部的中序遍历来获取多条数据，所以可能要跨层访问，这就意味着要在硬盘的页面之间进行多次访问【节点存储在不同的页面中】，降低了性能。而B+树由于所有数据都在叶子结点，不用跨层，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出来了。

hash比B+查找时间更短，为什么索引不用hash？

• 这和业务场景有关，如果只查找一个值的话，hash是一个很好的选择，但数据库经常会查多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了
  【同样查询n条数据，hash需查n次，即O(n) ；
  B+是O(log n)：查询一次是O(log n)，但查询一次后，由于B+树所有数据都在叶子结点，不用跨层，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出来了，不在花费时间】。

• 而且数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次装入内存，B+树的设计可以允许数据分批加载，同时树的高度较低，提高查找效率。

索引分类（只针对InooDB的B+树索引）

聚簇索引&非聚簇索引

B+树中所有的索引都属于这两类

• 比如上表，a是主键，聚簇索引如下：
• 非叶子结点：索引（创建索引的列的值）+指针（指向的处于两个相邻索引值之间的索引页或数据页）；
  非叶子结点存储在索引页（索引页：索引+指针）；
• 叶子节点：完整的数据行（即，索引+后面对应的行数据）
  叶子节点存储在数据页（索引+后面对应的行数据）；

• 叶子节点之间加双向链表的原因：【在叶子节点处维护出整个表的感觉，当不能使用主键索引（又没辅助索引）时，使用它一页一页的遍历查找】
  1、若使用主键a条件查询：select * from 表 where a = 1；按a = 1 条件，可以根据索引，从索引的根节点快速定位到在哪一页（即，对应的叶子节点）；
  2、若不使用主键a条件查询，而是使用b条件：select * from 表 where b = xxx；由于是对a建立的索引，所以b不能做索引查询，这时索引数据无效，但由于InooDB中数据的存储方式是索引组织表，即数据存在索引里，我还得从这个索引树里找，虽然我不能从通过索引的中间部分（即非叶子节点）查找，但叶子节点存储这所有的行记录，我查叶子节点即可；
  这时索引用不上，就只能从前向后的遍历叶子节点找（即，查全表），一个叶子节点找不到，需要去下一个节点找，就需要指针，指向下一个节点；

• 从上面可看出，当使用不是主键的字段做条件时，就是遍历表，这样很低；怎么办呢？就需要辅助索引，通过辅助索引找到对应的主键索引，进行查找；
  

• 对C建普通索引
  

例子：

• 对于聚簇索引，要找id=[1 , 6]之间的数，由于数据行和索引排序一致，找到id=1,6,然后去中间的数据即可，不用一行一行的找；而非聚簇索引，若想找一个范围内的数据，则需一个一个的查找。

具体索引分类

1.索引一般分类：普通索引、唯一索引、全文索引、空间索引

各种资料索引分类很杂，这里按创建索引时，用的命令分：create index / unique_index/full_index/space_index

2.存储方式：B-Tree、Hash

3.依赖列数：单列索引、组合索引

4.数据分布：聚簇索引、二级索引(辅助索引/非聚簇索引)

• 聚簇索引：索引末端存的是数据行，即找到索引，就得到了数据，不用回表，效率高；

• 二级索引：索引末端存的是一个id，即通过索引找到的是id，通过id再去聚簇索引里找真实的数据，所以它需要走两次索引，索引的索引，第二次访问索引即回表；

5.回表情况：覆盖索引（不需回表）

当一个索引包含（覆盖）了需要查询的字段的值时，称其为覆盖索引；

B+树实现的索引，每一个节点存的是索引字段的值，即，索引树里包含的字段值，可能就覆盖了需要查询的字段的值；

比如：item表 给字段 title建索引，price不建索引

1、select title from item where title = " " ;

2、select title , price from item where title = " " and price = " ";

1、此时title 是覆盖索引，查询的是title，因为title的字段值已经存储在索引里，title索引包含需要查询的字段的值，所以用索引查找时，就得到了相应的字段值，不用回表；

2、此时title 不是覆盖索引，查询的是 title , price，虽然title的字段值已经存储在索引里，用索引查找时，得到了title 相应的字段值；但还要查询price，title索引里没有price值，即title索引没有包含需要查询的字段的值，在查出满足条件的title 后，还需回表查询price，所以此时title 不是覆盖索引；

• 问题一：

聚簇索引包含了所有的数据，所以它也是一个覆盖索引。

不对【因为不一定，可能是也可能不是，看聚簇索引能不能包含（覆盖）需要查询的字段的值】

只有select、where中出现的列，被索引覆盖的情况才是覆盖索引，此时Extra会显示Using index；

聚簇索引 和 覆盖索引 不需回表的区别：

聚簇索引是在索引末级得到的数据，不用回表。

覆盖索引是在索引中间得到的数据，不用回表。

• 问题二：主键索引和唯一索引的区别

主键索引：某一个属性组能唯一标识一条记录；索引列的值必须唯一，且不能为null，
**唯一索引：**索引列的值必须唯一，能为null；
主键索引只能有一个，唯一索引可能有多个
一个字段设置为主键后，数据库会自动为其建立主键索引

索引语法

创建索引

查看索引

删除索引

alter(修改)命令

索引的设计原则

索引到底什么时候建----根据性能分析创建索引（Explain）

Explain是什么

Explain能干嘛

Explain怎么用

Explain + SQL语句

Explain结果各字段解释

查询优化

批量创建数据【略】

单表索引优化及常见索引失效

1、全值匹配

WHERE后面有多少个字段，就给多少个字段创建一个复合索引，这样索引不浪费，都能用上。

2、最佳左前缀法则

从创建索引的多个字段中，最左边的字段开始按顺序命中

• 给多个字段创建复合索引，每个字段的索引是由层级关系的，层级顺序，按创建索引的顺序，前面字段的索引层级高，先被查询，eg：

• 各层级之间的索引是一级一级相连的，只有前一级的索引存在，后面的索引才存在。如下图

• 例子

1、3个字段的索引都生效：
虽然sql中where后的字段顺序和创建索引时的顺序不一样，但3个字段的索引仍生效，是因为MySQL体系结构中的优化器的作用，它在不改变查询结果的条件下，调整sql的顺序，生成执行计划，即，这里顺序虽然变了，但优化器会调整他的顺序，再执行；

2、少了deptid字段，只有age字段的索引生效
少了deptid，后面的name索引自然也就失效了

3、少了age字段，所有字段的索引都失效
age是最左边的，少了age字段，后面的deptid和name自然也就失效了。

原因

多个索引，下层索引是在上级索引排好序的基础上排序的，左面的层级高，比如上图：
先对X排序，然后对相同的X1对应的M，进行排序；接着对相同的M3对应的S，进行排序；即下层索引是在上级索引排好序的基础上排序的。若上层用范围查询（或上层索引丢失或模糊查询），则下层就变得无序（eg：X使用范围查询，查询到X1和X2，而X1和X2对应的M是无序的（X1和X2单独对应的M是有序的））无法使用索引，索引失效；

3、不在索引列上做任何操作

4、索引中，范围查询字段右边的字段的索引失效

• 上面sql语句中，deptId字段使用范围查询，则在第1个索引中，NAME字段在deptId右边，该字段索引失效；第2个索引中deptId字段在最后，没有字段在它后面，所以所有字段生效
• 即，经常范围查询的字段（如：电商中的价格、日期等）要在创建索引的时候放在后面。

5、查询条件为不等于，索引条件失效

6、查询条件为is not null ，索引条件失效

7、like以通配符开头，索引条件失效

因为name字段【英文存的数据】的索引树，是根据首字母按照a–z的顺序排的，如果like以通配符开头，即首字母都不确定，索引自然也用不上。

8、where条件，等号后面的值不能做类型转换，否则索引失效

和3对应，3是不能在索引列（即等号前面的字段）上发生手动或自动的类别转换，这里是等号后面的值不能和索引列的类型不一致，否则mysql会自动进行类别转换，导致索引失效，进而使用不上索引，查询速度变慢，所以Java写程序时，bean中的属性类型，一定要和数据库中的字段类型一致。

一般性建议（查询优化）

关联、子查询优化

• 驱动表建索引，还得全表扫描，是因为 比如上面查询，条件是 class.card = book.card，这里并不知道class.card是多少，需要全表扫描驱动表的class.card，扫描到一个就去被驱动表里找。

建议

子查询优化

排序分组优化【P339–340，略】

查询截取分析

Q:随着用户增加，数据越来越多，数据库查询变慢了，怎么优化；
A：用explain分析，建索引；
Q：那难道系统里所有的sql都用explain分析一下；
A：对查询次数最多的sql统计出来（比如，电商中，访问商品首页就很频繁），优化它，这样解决20%d的sql优化了80%的用户体验；

• 怎么统计出查询次数最多的sql，用下面两个工具
  找运维人员，在生产环境下开启慢查询日志，找出查询最慢的部分sql，然后用explain分析，根据分析结果优化，比如 sql语句优化、建索引、索引优化等。

慢查询日志

查询出来的结果太多，靠人去查看太低效，用日志分析工具mysqldumpslow

SHOWPROCESSLIST(展示进程列表)【略】

概念

视图

视图的概述

• 视图是为了简化我们的查询操作。

创建或修改视图

查看及删除视图

• 查看
  
• 删除
  

存储过程和存储函数【略】

存储过程和存储函数概述

触发器【略】

介绍

MySQL的体系结构

概述

sql的执行流程

• 客户端发送一个连接后，就要到mysql的连接池中获取一个连接，来执行对应的请求。获取连接时，还需进行一些：认证、连接最大数、缓存等；
• 然后访问缓存和缓冲【缓存：读数据；缓冲：写数据】，若查询语句在缓存中命中，则不需要在接着执行了，直接将缓存里的对应数据返回出去；
• 缓存中命中不了，再到SQL接口，再到解析器；
• 再到优化器，它在不改变查询结果的条件下，调整sql的顺序，生成执行计划；
• 存储器按照执行计划，对表进行操作，得到数据后，将结果返回客户端程序，同时向缓存里存一份。

存储引擎

存储引擎概述

各存储引擎

InnoDB和MyISAM区别

• 行锁会发生死锁，表锁不会，因为表锁 锁的范围太大，不可能出现死锁。
  

锁

锁概述

锁的分类

mysql锁

MyISAM表锁

InnoDB行锁

行锁介绍

InnoDB行锁的模式

无索引行锁升级为表锁

间隙锁的危害

总结

事务【该事务下的知识，都是针对MySQL InnoDB引擎下的】

事务的ACID特性，只是规定，标准；

很多数据库为了性能的考虑，不是百分之百满足ACID，尤其是隔离性；比如很多数据库在默认的隔离级别下，是不能满足100%的隔离性的；

MySQL在5.1版本后，InooDB默认的隔离级别就100%满足ACID了，并且性能还很好；

原子性

我们能保证一致性和持久性，就能保证原子性。

一致性

一致性的意思就是全都成功或全都失败。

• 一致性使用undo log实现的，当事务执行失败，回滚的时候用到它。
• redo log是专门的文件存储，undo log和数据存储在一起；
• redo log存放的是二进制数据，二进制数据恢复硬盘数据很快；
• undo log存放的是sql，读取很方便，便于事务回滚，和MVCC的并发读操作。

隔离性

事务的隔离性是通过锁实现的，锁的程度不同导致不同的隔离级别；

lock

锁是解决并发产生的死锁等问题的，一般来说，锁会降低并发性；那么锁的越重，会不会事务的并发性越差，效率越差呢？----------不会，因为MVCC机制

粒度

类型

• IX与S和X不兼容，即IX与行锁不兼容，但它和IS可以兼容，即事务可以给一张表上IS和IX，但若先上的是IX，它想写，然后在加X，实行写操作；后上的IS，即我想读，但不能上S，因为S和IX不兼容，你想读可以，但读的操作不能进行，即别人正在写，你不能读；或者再加一个IX，想写，可以，但在想加X不行，因为IX与X不兼容，即别人正在写，你不能写。

• X与任何锁不兼容，所以X是个很重的锁，使事务完全丧失并发性；

机制

• 在加锁的前提下，通过什么能提高并发性呢。
  

无锁（MVCC）

加了锁并发度降低，MVCC是用来提高并发的。

无锁表示：增删改的时候，没在语句中显示加锁，是系统自动加锁。
其它事务想要并发的读，采用的是MVCC，MVCC是对没加锁的历史版本数据进行读操作；

每次事务提交新数据时都会产生一个历史版本数据（该历史版本数据放在undo log日志里，因为undo log日志是保证事务一致性的，所以undo log日志一定会有，这样就不会有什么损失），事务锁的是新数据行，但其他事务若想读，读的是历史版本，这样就在加锁的情况下保证了并发性。

有锁

增删改的时候，自动加锁，查的时候不自动加锁，若查的时候需要加锁，就手动加锁。

锁的算法

• mysql内置了3中算法，来实现锁的机制

事务并发产生的问题

死锁

超时回滚的缺点：

• 完全被动，超时回滚时间是10s，就得等10s;
• 有可能误杀事务，比如超时回滚时间是10s，有可能有的事务就是执行的时间长，超过10s，我只是没执行完，被误杀了。

死锁检测
画出事务间的关系，若两个事务间互相联系，产生回路（一个事务访问另一个事务的数据），则发生死锁；检测出死锁就将其中一个事务回滚，从而解除死锁；

锁的升级

隔离级别（加锁解决事务并发产生的问题）

每个数据库并不是100%解决了隔离性，它是给你几个隔离级别选择，因为隔离级别会影响并发性，一般隔离级别越高，并发性越低。但mysql InnoDBl两者能兼顾。

持久性

持久性的意思就是全都成功。

持久性使用redo log实现的

redo log机制

redo log 与 bin log区别

主从复制

概述

复制的基本原理

为了缓解数据库的压力，进行读写分离

• 读写分离架构是建立在主从模式下的，主从模式是基于两个日志log实现的。
  

1、主服务器在执行DML语句时，以事务为单位，在事务提交时，将数据变更记录进二进制日志文件bin log中；

2、从服务器从主服务器读取binlog来进行数据同步，另外从服务器负责“读写分离”的读，要承受别人读的压力；所以如果是一个线程处理这两个读操作，压力会很大，所以从服务器里有两个核心线程：

一个是IO线程，主要是读取binlog，写入到从机的中继日志relay log，将数据同步到从机；

另一个是SQL线程，当外部线程要读数据时，会执行SQL线程，SQL线程会从relay log中得到想要的结果；

3、从服务器还负责生成快照，假设主服务器因为操作错误或被攻击，数据出现大规模错误，可以借助从机的快照恢复。

• 快照：快照就是在这一瞬间，硬盘里数据的形态，相当于做了一份备份

复制的最大问题

延时：由原理可得，主从复制需要IO操作，所以会有延时，毫秒级。

复制的基本原则

binlog的格式

- statement（陈述）
binlog里存的是sql语句，当sql语句中有函数时，可能会造成主从复制不一致的问题，因为比如有时间函数，从主机复制到从机需要时间，所以时间函数在主机和从机上执行的结果不一致；

- ROW（行）
记录执行完sql每一行的改变，这样可以避免函数在主从机执行，可能会造成主从复制不一致的问题；
但它有效率问题，比如我执行的是没有筛选条件的整表操作update xxx set id vaule(2)；，即每一行的id都改为2，每一行都发生变化，这样需将整张表都写入bin log，和statement只需记一句sql相比，效率太低；

- mixed（混合）
statement和ROW的混合，它判断写操作有没有函数，有函数用ROW，没函数用statement；
但如果出现系统变量，可能会造成主从复制不一致的问题；

Mycat

Mycat介绍

Mycat是什么

Mycat是干什么的

• 读写分离

只有mycat是不能实现“”读写分离“”的，先搭建mysql的主从复制，才能实现mycat的读写分离。

先搭建mysql的主从复制

再mycat的读写分离

• 主从搭起来，要进行读写分离，用Java操作的话【虽然，主从已经搭建起来，主机负责写，从机负责读，但这是我们事先知道的，我们如果不进行一些操作的话，数据操作是不知道应该访问哪个服务器的，需要用一些方法告诉它，eg：java程序操作、mycat自动分配】，需要配置两个数据源，并且执行数据库操作还得做判断，如果执行写操作，就发给主机；如果是select读操作，就发给从机。很麻烦，所以要在中间加个中间件mycat来解决，Java程序就配置mycat一个数据源，后面的主从数据库由mycat管理，这里将mycat看做是一个逻辑上的数据库。【和Nginx很像】

Mycat原理

搭建读写分离的配置文件

通过修改配置文件，实现读写分离；

• schema.xml中的balance参数：
  

Q: 有没有搭过读写分离？
A: 有，balance设置为3，搭建的是一主一从。

mycat分库（垂直拆分）

• 表

• 订单表：记录一条条订单信息；
• 订单详情表：订单表的每一条订单信息对应一个订单详情；eg：用户查看历史订单，会看看一条条记录，点开一条，会显示订单详情

一个数据库快到5千万条数据，达到了单库存储的瓶颈，需要垂直拆分，进行分库（分到不同主机上的库），把表拆分到不同的库。但要把哪些表拆到其它库呢？

将和其他表没有关联的表拆分到其它库（因为同一个服务器上的不同库之间可以用join操作，但不同服务器上的库之间不可以用join操作），拆分的时候不是直接把需要拆出去的表复制到目标服务器的库，再删除原来服务器的拆分出去的表，而是先在两个服务器上准备两个空库，再通过mycat分库（具体为，在mycat上执行原库的建表语句，mycat会根据配置好的配置，将对应的语句在对应的服务器上执行）。

• 客户表和其他表没关联字段，所以将客户表拆分出去

mycat分表（水平拆分）

一个表快到5百万条数据，达到了单表储的瓶颈，需要水平拆分，进行分表（分到不同主机上的库）。

• 订单表和订单详情表数据都超过了500w，所以需要分表；

• 先分订单表，若按订单日期分，则每个服务器上都是同一个时间段的订单信息；但大多数用户查询的是最近一段时间的历史订单，导致符合该时间段的服务器经常被查询，而其它时间段的服务器很少被查询，显然不合理；如果不用customer_id分，一个用户的订单历史信息可能会存在多个服务器上，如果一个用户查询历史信息，可能会使多个服务器为一个人服务，浪费资源。所以我们可以按customer_id分表，比如要分到2个服务器上，则让customer_id对2取余，结果是0或1 的分开；这样由于每个用户的订单有限，在一台服务器上完全放得下，所以不同的用户查历史订单，不同的服务器服务，分摊了服务器的压力；

• 再分订单详情表，由于订单详情表和订单表是有关联字段的，所以根据关联字段分----------该表叫ER表：根据关联表分，关联表怎么分，就怎么分，具体是根据关联字段分；

• 订单状态字典表，该表订单表和订单详情表都需要，所以冗余的为每个数据库复制一份。该表叫----全局表；

• 最终的分表逻辑