关键字：缓存，日志才是本体，B+Tree

或许海豚才是本体

所谓的数据库，就是通过存储引擎，把数据按一定格式（压缩，加密）存在文件里。默认的存储引擎是InnoDB

结构：MySql简介

树型，按页储存，脉冲刷盘

MySql是B+Tree的形式储存数据；B+Tree就是将所有信息都保存在叶子节点的B-Tree（后面细说）。

使用的是聚簇索引，也就是索引和数据在一起，找到索引就找到数据（物理磁盘上的在一起）。

按页（Page）保存，每页16k，这16k在磁盘上是连续的；以脉冲的形式从内存向磁盘刷盘（就是一阵一阵的，减少I/O操作，减少对外设的操作。磁盘虽然在主机里，但它也是外设；只有CPU和内存是自己人）。

缓存：主要工作区域

主要工作都是在缓存中做的，所有的增删改查都是在缓存中完成，然后定期刷盘到物理磁盘保持（所以突然断电数据会丢失，所有的软件都是这样，只有ctrl+s了才是存盘）。

Buffer Pool：缓冲池，简称BP（查询缓存）

BP以Page页为单位，默认大小16K，在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率。

脏页是什么？

dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致。

其他两个状态：free page ： 空闲page，未被使用。clean page：被使用page，数据没有被修改过

只有脏页需要刷盘。刷盘：将修改的数据更新到磁盘

热数据维护LRU算法

缓存中的数据越来越多，就需要删除不常用的数据；这个概念在别的数据库，比如Redis中也有（Redis就是大佬嫌MySql不够快才去创新的产物）。

具体方法就是末尾淘汰，BP缓存结构是一个链表，被访问过的数据移到头部，一直没被访问就掉到末尾了。不用担心淘汰了脏页，导致数据没有更新到。脏页还同时有另一个链表保存flush list，专门用来刷盘。

Change Buffer：写缓冲区，简称CB

很诡异啊，写操作怎么缓存？这其实是一个搭便车的操作。

如果读缓存中没有它要操作的数据，那SQL操作会先缓存到写缓冲区（减少一次I/O），等下次查询时先去磁盘查询到相关数据，再去更新读缓存的数据，然后等刷盘。

如果一直没读相关数据怎么办？

不用太担心，默认情况下，每个一秒（脏页达到75%，不满足条件的话最晚10秒）就会执行刷盘操作，所以最多就丢失一秒（是的就是一秒，不是十秒，因为还有日志可以恢复）的数据。本质是减少I/O，但是每秒都有一次I/O的机会。

Adaptive Hash Index：自适应哈希索引

神奇的东西，而且还是自动的。

InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

Log Buffffer：日志缓冲区

就是日志的缓存，和BP一样，定期写入磁盘。每隔一秒执行刷盘（日志是更新的最勤快的，所以说日志才是本体；什么都没了，只要还有有日志就不用跑路）。

线程：读写分工，甚至还有一个专门负责日志

在InnoDB中使用了大量的AIO（Async IO）来做读写处理。写要排队，读可以一起。

所以高并发场景可以先把要更新的数据都读到缓存中，修改后再慢慢写入，保证数据准确性；比如打卡的时候先读取用户信息+当前系统时间，然后排队插入，而不是插入的时候写当前时间，这样就不会有人明明踩点打卡但是迟到了。

日志：最重要的东西

日志即数据

日志不止记录操作，还可以还原数据的；甚至MySql对日志的写入比刷盘还频繁。

Undo Log：撤销日志

看名字就知道是用来回滚事务的，通过记录反向SQL语句来回滚。

Redo Log：重做日志

是用来恢复的？也是和事务有关，在发生故障时恢复事务。比如事务没执行完就断电了，这时候用Redo Log恢复数据，继续执行事务。

Binlog日志： Binary log（二进制日志）

主从复制，恢复表数据都用它。以二进制的方式保存了所有更新操作（增，删，改）。

主从复制就是跟踪主数据库Binlog的位置，有变化就更新。

慢查询日志

默认是10秒；查看超过这个数的SQL语句（不光是查询语句）。

索引：就是一个迷你表

索引也是B+Tree，只不过值是表id（主键）。

联合索引（很好用，一个顶多个）：

一个A-B-C三个字段的索引等于

A

A-B

A-B-C

但是不等于（不能错位，不能乱序）

B-A

A-C

C-B-A

文本索引：需要分词；就是个玩具，有其他替代工具。

EXPLAIN 命令，分析查询

EXPLAIN SELECT * from city WHERE id < 1000;

type：效率由低到高

ALL：表示全表扫描，性能最差。

index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。

range：表示使用索引范围查询。使用>、>=、<、<=、in等等。（好sql语句的标准）

ref：表示使用非唯一索引进行单值查询。

eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。

const：表示使用主键或唯一索引做等值查询，常量查询。

NULL：表示不用访问表，速度最快

key：

表示查询时真正使用到的索引，显示的是索引名称

rows：

MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。

Extra：

Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

Using where表示查询需要通过索引回表查询数据。

Using index表示查询需要通过索引，索引就可以满足所需数据。

Using fifilesort表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using fifilesort建议优化。

Using temprorary查询使用到了临时表，一般出现于去重、分组等操作。

事物：不是所有存储引擎都支持事务，有的嫌事务影响效率

通过undo log中的反向sql

undo log内容//todo

事务也是通过日志回滚的，MySql精妙的地方都在日志里。

BTree：数据库的常用结构

B-Tree，B+Tree，红黑树

二叉查找树演化而来，为了查找设计出来的树。

红黑树适合少量数据；为了平衡需要旋转变色，大大增加了增加删除节点操作的复杂度。

BTree的一个节点容量刚好是磁盘一页的大小（转一圈读的是磁道，硬盘中有多个磁盘，形成柱面，柱面转一圈是页面，也就是读一个磁道的时候把其他磁盘这个磁道一起读了，就是页面），很科学因为磁盘读取的时候就会把整页（这个页和MySql的页不一样，但是可能设计理念上有相通的地方）都读取到内存，就算你只要这页里的一个字。所以不浪费每一次操作。

B+Tree是链表+树的模式

B+Tree的所有数据都存在叶子节点，所以查询任何数据的速度都很平均；叶子节点还是一个链表结构，加快了遍历速度。所以B+Tree相对于他的其他兄弟，更适合储存大量数据。

MySql动态建表

mysql可以动态建表来解决按日期建表的问题。

优化

避免回表查询，可以将经常查询的几个字段创建联合索引。大大加快查询速度。

大数据分页，比如10万后的分页查询，可以先通过主键定位到第10万个数据，再向后查询，避免遍历前10万条数据。

select ＊ from XXX where id>100000 limit 10;(前提是id有序，如果是雪花算分怎么用呢？)

select * from user where id>= (select id from user limit 10000,1) limit 100;

模糊查询，左边不加%，还是可以使用索引。

尽可能扩大内存中的数据量，加大内存，最优的状态是数据都在内存里；（这就有了redis）

为什么redis可以撑住高并发，但是MySql不行，只查询内存里的也不行吗？

redis缓存读写性能碾轧MySql。

加大innodb_buffer_pool_size（innoDB引擎下缓存池尺寸），看自己机子情况，给别的工具留点内存。

数据预热，启动时先将一些数据移到缓存；编写预热脚本，运行脚本。

降低磁盘写入次数，增大redolog，减少刷盘，innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size；会不会导致丢失更多数据？

不会，写日志频率还是一样的，可以用日志恢复。

设计优化：

统计表，专门统计数据，而不是查询的时候再去计算。

冗余字段，减少关联查找，最好是不会修改的字段，比如公司名称，用户名称；最好再做一个补偿功能，比如每周半夜同步这些数据。

拆表，拆一个副表出来，保存一些不经常用的信息。

主键，建议雪花，不重复而且有序。

其他字段，大小够用就行，不要太大，默认不要为null（null比空更长，设置0或空）。数字比字符串快。

加索引，alter table XXX add index index名称（字段名）（虽然说加索引会影响表更新效率，但是数据库90%操作都是查询）

in包含值不要过多，最好是排序的。

不要用＊，多余字段产生回表查询。不如说是只查询索引字段。

MySql一条数据的结构到底是什么样的？既然要数据类型，说明会预先申请空间，那一条数据应该在连续空间中，所以查到任何字段，其他字段就在旁边不会影响效率，而且根据读盘读一页的原则，可能数据都已经取出在内存里了；为什么＊会影响效率，就是顺手取的事。

索引是另一张表，不用＊就是怕触发回表，如果字段里有不在索引都字段，那用不用＊都无所谓了。

当查一条数据当时候使用limit 1

排序字段要加索引

不用or而是拼两条sql，这样至少有一个可以用到索引。

分段查询（个人经常用），分开查，然后用应用程序遍历组合；一般超过3张表关联就使用分段查询。

关联当时候，小表驱动大表；减少IO总量，小表数据少，连接次数就少；inner join会自动选小表做驱动表，比left join好。