MySQL架构体系（四层） 单进程多线程 前端、 连接池组件、管理服务与工具组件、SQL接口、查询分析器、优化器、缓冲 插件式存储引擎 物理文件 常用存储引擎、优缺点 InnoDB、事务处理、全文索引、行锁、外键、MVCC高并发、一致性非锁定读、默认可重复读、通过next-key-locking解决幻读问题（脏读、幻读、不可重复读）、插入缓冲、二次写、自适应哈希索引、预读 MyISAM、不支持事务、表锁、非聚集索引、存储文件单独放，不完整的外键 Memory（hash索引、堆存储、表锁并发差、不支持text、blob，定长存变长字段、临时表有可能转MyISAM）、 NDB（内存、集群、数据库层面的join）、 Archive（归档、压缩）、 Meria（支持缓存数据和索引文件、行锁、MVCC、支持事务和非事务安全的选项、更好的BLOB字段处理） 还有Federated、CSV、Merge InnoDB存储引擎（多线程）是基于磁盘的数据库系统 架构：后台线程、内存池、文件 后台线程： Master Thread：主要负责将缓冲池中的数据异步刷新到磁盘中，保证数据的一致性，包括脏页的刷新，INSERT BUFFER、UNDO页的回收 IO Thread：使用异步IO处理写IO的请求；一个 insert buffer线程、一个log线程、4个read thread、4个write线程 Purge Thread（清除线程）：回收已经使用并分配的undo页，可以设置数量 Page Cleaner Thread （页面清洁器线程）：脏页的刷新 内存： 缓冲池： 弥补磁盘速度慢提高性能，将数据页fix到内存中，通过checkpoint机制将数据刷回磁盘。 数据页：索引页、数据页、自适应哈希索引、insert buffer、锁信息、数据字典信息、额外的内存池、重做日志缓冲 允许多个缓冲池实例，增加并发处理能力。 缓冲池命中率不应低于95% LRU List、 Free List 和 Flush List： 默认页大小：16KB LRU加入了midpoint，5/8处，尾端37%，前面为new，后面为old LRU中被修改的页称为脏页，即缓冲池中的页和磁盘上的页的数据产生了不一致，通过checkpoint机制将数据刷回磁盘 重做日志缓冲： 重做日志信息先放到这个缓冲区，然后按一定频率刷新到重做日志文件，不需要很大，8M默认 以下三种情况会刷新到重做日志文件： 1、Master Thread（Page Cleaner Thread） 每秒将重做日志刷新到重做日志 2、每个事务提交时 3、重做日志缓冲池小于50% 额外的内存池：缓冲帧、缓冲控制对象 Write Ahead Log：防止数据丢失，先写重做日志再修改页；当宕机时可以通过重做日志恢复数据。 Checkpoint技术： 目的： 1、缩短数据库的恢复时间（Checkpoint之前的点，已经刷新到磁盘中） 2、缓冲池不够用时，将脏页刷新到磁盘（LRU溢出的页） 3、重做日志不可用时、刷新脏页到磁盘（然后重做日志就可以被覆盖重用了） InnoDB关键特性： 插入缓冲、（索引是辅助索引、索引不是唯一的）提高性能 两次写：保证数据的可靠性，对于脏页的刷新，先写到内存中的doublewrite buffer中（2M），再分两次写到物理磁盘上的doublewrite页，然后马上同步脏页到磁盘； 自适应哈希索引：时间复杂度为O(1)，通过缓冲池的B+树页构造而来，不需要对整张表创建哈希索引，是一种数据库的自优化。 异步IO：可以进行IO Merge 刷新临近页：当刷新一个脏页时，会检测所在区的所有页，如果是脏页，那么一起刷新，同时可以通过AIO将多个IO合并为一个IO操作。 文件： 参数文件 日志文件： 错误日志、 二进制日志（恢复、复制、审计） 写同步：sync_binlog=1 事务回滚但是写了日志：innodb_support_xa=1 同时主库运行rand、uuid、触发器等操作会导致数据不一致 慢查询日志、 查询日志 Pid文件 表定义文件frm idb文件 InnoDB引擎的重做日志：只记录自身的事务日志 表： innoDB为索引组织表，表都根据主键顺序阻止存放的，必有一个主键，如建表时未指定主键，则判断表中是否有非空的唯一索引，如果有该索引为主键（聚集索引），否则自动生成一个6字节的rowid作为主键，除了rowid还有6字节的事务ID，7字节的回滚指针ID 表空间： 段、段有innoDB自己控制 区、区固定大小1MB， 页、页的大小可以改变4K、8K、16K 行格式：compact 约束： 主键、唯一索引、外键、触发器 视图 分区： MySQL支持水平分区，并不支持垂直分区；局部索引分区，即分区中既存放了数据又存放索引。 如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。 如果建表时没有指定主键和唯一索引，可以指定任何一个列作为分区列。 分区类型： RANGE分区：行数据基于一个给定连续区间的值被放入分区。比如（1，10），（11,20），（21,30），一般年月日、可以设置maxvalue；语句： partition p0 values less than (TO_DAYS(‘2010-04-01‘)) LIST分区：与range分区类似，不过是离散的值。如（1,3,5,7,9），（2,4,6,8,10）；语句：使用 in 而不是less than HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数（根据分区数进行取模散列）。优化类型：LINEAR HASH KEY分区：根据MySQL数据库提供的哈希函数来进行分区（不用用户指定表达式，直接指定列名、分区数即可） COLUMNS分区：可以直接使用非整数类型的数据进行分区。比如日期类型不需要YEAR()转成整数就可以直接使用 RANGE COLUMN分区：可以使用多个列进行分区 子分区：每个分区的子分区数量必须相同，必须定义所有的子分区，子分区的名字必须唯一 NULL值的处理 MySQL的分区总是认为NULL值小于任何一个非NULL值，与order by 一样。 RANGE：总是将NULL值放在最左边的分区，如果删除含有NULL值的分区P1，那么只是删除分区定义的记录，含有NULL值的记录仍在。 LIST：必须明确指定NULL在哪个分区 HASH 和 KEY ：任何分区函数都会将含有NULL值的记录返回为0 InnoDB 和 MyISAM 插入多个值，其中一个值不在分区的定义内时，处理方式不同，InnoDB会事务回滚。 分区和性能： 对于OLAP应用，分区可以很好地提高查询性能。 对于OLTP应用，通常通过索引获取少量数据，因此只需2-3次IO，B+树就可以完成，如果贸然分区会增加IO次数。 如果只对ID列分区，那么即使uname列加了索引（非唯一），通过uname进行查询时，将会查询所有分区，十个分区就是20-30次IO，会慢很多。 表和分区交换数据 索引与算法： InnoDB索引介绍 二分查找法 二叉查找树和平衡二叉树（多用于内存中） B-树 和 B+树 （多用于磁盘），结构、旋转操作、拆分合并 B+ 树索引：只能找到数据所在的页，然后将页拿到内存中进行二分查找得到具体的行。适合进行范围查询和排序 聚集索引（叶子节点存放整行的数据） 辅助索引（叶子节点存放指向行数据的书签），正常来讲IO要比聚集索引多一倍，但是可以覆盖索引避免回表。 全文索引 哈希索引（自适应，无法人为的干预） B+树索引的管理： ALTER TABLE 以及 CREATE/DROP INDEX 两种创建和删除索引的方式 Cadinality值：该列的高选择性，越接近1，选择性越高，过低时优化器不使用该索引。但不是实时更新。ANALYZE TABLE可以更新该值（1/6数据变化也会更新）。 在线添加索引的演变历史： 1、建新表、修改表结构、导入数据，删除原表，将临时表重命名为原来的表名。在过程中，表完全不可用 2、FIC，在原表上加S锁。可以读，并且只限定辅助索引，如果是主键索引，同样需要重建表 3、OSC，Facebook用PHP实现的在线执行DDL方式，有较大的局限性。 4、Online DDL：可以在线修改辅助索引、并可以同时对数据进行增删改，可以修改自增值、外键、列的重命名 Online DDL原理： 通过重做日志达到数据的最终一致性。 B+树 比较矮胖、扇出高、IO少、故适用于磁盘，当获取表中少部分数据时，建立B+树索引才有意义。 联合索引： 对索引中第二个列已经排序，对（a,b）排序，不对（a,c）排序，有时可以避免一次排序操作 最左前缀原则：a,b,c索引，可以使用a索引以及a,b索引和a,b,c索引，一般无法跳过a使用（如果是统计操作，并且是覆盖索引的，会使用第二个索引列） 覆盖索引 索引中即包含所要查询的列，不需要查询聚集索引中的记录。IO次数少 不使用索引 *号、范围查找、JOIN操作，一般会进行全表扫描（聚集索引） 优先级： 覆盖索引 辅助索引，如果数据量大（20%），或者查询整行信息，会选择聚集索引 强制使用索引 FORCE INDEX：强制使用 USE INDEX（建议优化器使用该索引，但优化器不一定使用该索引） 什么情况下使用： 索引过多时，可能优化器选择索引的时间都要大于SQL执行的时间 优化器可能错误的使用某个索引，导致查询过慢 MRR优化：将随机访问转化为较顺序的访问（性能提升比较高） ICP优化：将先取再根据where过滤，优化为取出时就根据where过滤。 必须是该索引能覆盖到的范围。 哈希算法： 除法散列 拉链法（解决冲突） 自适应哈希索引： 只能用来进行等值查找。 全文索引： 使用倒排索引实现，需要辅助表存储相关信息 https://www.cnblogs.com/lz0925/p/12022889.html 锁： 用处：对共享资源的并发访问，提供数据的一致性和完整性 latch：一般称为闩锁，用来保证并发线程操作临界资源的正确性，并且没有死锁检测机制。有读写锁、互斥量（内存锁，一般不需要开发去管理和优化） lock：面对事物，锁定数据库中的对象（表、页、行）。有行锁、表锁、意向锁 行级锁： S锁 共享锁只和共享锁（意向共享锁）兼容 X锁 排它锁和任何锁都不兼容，包括X锁本身 意向锁： 意向锁和意向锁兼容，IS和S锁兼容，颗粒度：表级锁 三张表：INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS RC 的一致性非锁定读，总是读取最新的快照 RR 的一致性非锁定读，读取事务开始时的数据版本 （这里的区别也是RR实现可重复读的根本） 一致性锁定读就是手动加锁 for update、 lock in share mode（使用这两个语句时，务必在一个事务中） 外键与锁： 查询父表的时候使用 select ... lock in share mode，主动加S锁，如果父表已经加了X锁，子表的操作会被阻塞，否则会使用一致性非锁定读，产生数据不一致。 innodb会自动为外键添加索引 锁的算法 行锁的三种算法： Record Lock: 单个行记录上的锁。 会锁住索引记录，没有没建立索引，会使用隐式主键来进行锁定 Gap Lock：间隙锁，锁定一个范围，但不包含记录本身 Next-Key-Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身（解决幻读问题，只有在RR级别下才生效） 如果是唯一索引或主键，会降级为Record Lock，而不是锁住范围。 如果是辅助索引，且值为 1,3，6，10， 进行where b=3 的查询时，会用Next-key-lock锁定(1,3], 然后用Gap Lock锁定(3, 6)， 将3的左右范围都锁定 如果是where > 6 那么会锁住(6, +∞)这个范围，而不是10 脏读：指一个线程中的事务读取到了另外一个线程中未commit或者回滚的数据。 脏读官方定义：即一个事务中读取到另外一个事务中未提交的数据。 不可重复读：指一个线程中的事务读取到了另外一个线程中提交的update的数据，导致两次读取到的数据内容不一致。 不可重复读与脏读的区别是：脏读读到的是未提交的数据，而不可重复读读到的却是已经提交的数据，但是其违反了数据库事务一致性的要求。 幻读：指一个线程中的事务读取到了另外一个线程中提交的insert的数据，两次读取到的数据总量不一样。 幻读官方定义：指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行 丢失更新：虽然数据库能阻止丢失更新的问题，但是逻辑意义上的更新无法避免。所以需要在事务中加X锁，串行化，第二个事务等待第一个事务完成后再运行。 阻塞和死锁： 死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。 解决死锁： 1、超时：通过innodb_lock_wait_timeout参数设置超时的时间，超时则其中一个事务回滚 2、wait-for-graph（等待图）进行死锁检测：判断是否有回路，若存在死锁，选择回滚undo量最小的事务。 死锁的概率： 死锁应该非常少发生，若经常发生，则系统是不可用的。 死锁的次数要少于等待，因为至少需要两次等待才会产生一次死锁。 大多数的死锁，innoDB可以检测到，不需人为进行干预。 锁升级：将当前锁的粒度降低。行锁升级页锁，页锁升级表锁 innodb中不存在锁升级的问题，实际不论单条记录还是多个记录都是对页进行加锁，开销一样。 事务： ACID