ByteCoding

InnoDB的物理存储 InnoDB表的组成：表结构（frm）+数据（ibd） MySQL 8.0开始，允许将表结构定义（占用空间很小）放在系统数据表中 控制参数innodb_file_per_table ON：每个InnoDB表数据存储在一个以**.ibd为后缀的文件中，推荐** 更容易管理，DROP TABLE会直接删除这个文件 OFF：InnoDB表数据存储在共享表空间 DROP TABLE，空间也是不会回收的 123456mysql> SHOW VARIABLES LIKE '%innodb_file_per_table%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_file_per_table | ON |+-----------------------+-------+ 文件空洞空洞：可以被复用但没有被使用的空间，经过大量增删改的表，都会存在空洞 删除 如果删掉...

场景建表12345CREATE TABLE SUser( id BIGINT UNSIGNED PRIMARY KEY, name VARCHAR(64), email VARCHAR(64)) ENGINE=InnoDB; 查询1SELECT id,name,email FROM SUser WHERE email='[email protected]'; 创建索引12ALTER TABLE SUser ADD INDEX index1(email);ALTER TABLE SUser ADD INDEX index2(email(6)); index1 索引长度：整个字符串 从index1索引树找到第一个满足索引值为zhangssxyz@xxx.com的记录，取得主键为ID2 到聚簇索引上查找值为ID2的行，判断email的值是否正确（Server层行为），将该行记录加入结...

脏页 + 干净页 脏页：内存数据页与磁盘数据页内容不一致 干净页：内存数据页与磁盘数据页内容一致 flush：将内存中的脏页写入磁盘 flush – 刷脏页；purge – 清undolog；merge – 应用change buffer flush过程 触发flushredolog写满 当InnoDB的redolog写满，系统会停止所有的更新操作，推进checkpoint 把checkpoint从CP推进到CP’，需要将两点之间的日志（绿色），所对应的所有脏页都flush到磁盘上 然后write pos到CP’之间（红色+绿色）可以再写入redolog 性能影响InnoDB应该尽量避免，此时所有更新都会被堵住，更新数（写性能）跌为0 内存不足 当需要新的内存页，而内存不够用时，就需要淘汰一些内存数据页（LRU） 如果淘汰的是脏页，就需要先将脏页flush到磁盘 该过程不会动redolog，因为redolog在重放的时候 如果一个数据页已经flush过，会识别出来并跳过 性能影响 这种情况是常态，InnoDB使用buffer pool管理内存 buffer pool中内存页有3种状...

优化器 优化器的重要职责：_选择索引_ 目的是寻找最优的执行方案 大多数时候，优化器都能找到正确的索引 在数据库里面，决定执行代价的因素 扫描行数 – 本文关注点 是否使用临时表 是否排序 MySQL在真正开始执行语句之前，并不能精确地知道满足条件的记录有多少 只能根据统计信息（索引的区分度）来估算记录数 基数越大（不同的值越多），索引的区分度越好 统计信息中索引的基数是不准确的 12345678mysql> SHOW INDEX FROM t;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |...

场景 维护一个市民系统，有一个字段为身份证号 业务代码能保证不会写入两个重复的身份证号（如果业务无法保证，可以依赖数据库的唯一索引来进行约束） 常用SQL查询语句：SELECT name FROM CUser WHERE id_card = 'XXX' 建立索引 身份证号比较大，不建议设置为主键 从性能角度出发，选择普通索引还是唯一索引？ 假设字段k上的值都不重复 查询过程 查询语句：SELECT id FROM T WHERE k=5 查询过程 通过B+树从树根开始，按层搜索到叶子节点，即上图中右下角的数据页 在数据页内部通过二分法来定位具体的记录 针对普通索引 查找满足条件的第一个记录(5,500)，然后查找下一个记录，直到找到第一个不满足k=5的记录 针对唯一索引 由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续查找 性能差异 性能差异：微乎其微 InnoDB的数据是按照数据页为单位进行读写的，默认为16KB 当需要读取一条记录时，并不是将这个记录本身从磁盘读出来，而是以数据页为单位进行读取的 当找到k=5的记录时，它所在的数据页都...

视图 虚拟表 – 本文不关心 在调用的时候执行查询语句并生成执行结果 SQL语句：CREATE VIEW InnoDB在实现MVCC时用到的一致性读视图（consistent read view） 用于支持RC和RR隔离级别的实现 没有对应的物理结构 主要作用：在事务执行期间，事务能看到怎样的数据 快照 在RR隔离级别下，事务在启动的时候保存了一个快照，快照是基于整库的 在InnoDB，每个事务都有一个唯一的事务ID（transaction id） 在事务开始的时候向InnoDB的事务系统申请的，按申请的顺序严格递增 每行数据都有多个版本，每次事务更新数据的时候，都会生成一个新的数据版本 事务会把自己的transaction id赋值给这个数据版本的事务ID，记为row trx_id 每个数据版本都有对应的row trx_id 同时也要逻辑保留旧的数据版本，通过新的数据版本和undolog可以计算出旧的数据版本 多版本 虚线框是同一行记录的4个版本 当前最新版本为V4，k=22，是被transaction id为25的事务所更新的，因此它的row trx_id为...

全局锁 全局锁：对整个数据库实例加锁 加全局读锁：FLUSH TABLES WITH READ LOCK，阻塞其他线程的下列语句 数据更新语句（增删改） 数据定义语句（建表、修改表结构） 更新类事务的提交语句 主动解锁：UNLOCK TABLES 典型使用场景：全库逻辑备份 把整库每个表都SELECT出来，然后存成文本 缺点 如果在主库上执行逻辑备份，备份期间不能执行更新操作，导致业务停摆 如果在备库上执行逻辑备份，备份期间从库不能执行由主库同步过来的binlog，导致主从延时 备份加全局锁的必要性 保证全局视图是逻辑一致的 mysqldump --single-transaction 导数据之前启动一个事务，确保拿到_一致性视图_ 由于MVCC的支持，在这个过程中是可以正常更新数据的 需要存储引擎支持_RR的事务隔离级别_ MyISAM不支持事务，如果备份过程中有更新，总是能取到最新的数据，破坏了备份的一致性 因此MyISAM只能依赖于FLUSH TABLES WITH READ LOCK，不能使用--single-transaction 针对全库逻辑备份的场景，--si...

索引模型哈希表实现上类似于java.util.HashMap，哈希表适合只有等值查询的场景 有序数组有序数组只适用于静态存储引擎（针对不会再修改的数据） 查找 等值查询：可以采用二分法，时间复杂度为O(log(N)) 范围查询：查找[ID_card_X,ID_card_Y] 首先通过二分法找到第一个大于等于ID_card_X的记录 然后向右遍历，直到找到第一个大于ID_card_Y的记录 更新在中间插入或删除一个纪录就得挪动后面的所有的记录 搜索树平衡二叉树查询的时间复杂度：O(log(N))，更新的时间复杂度：O(log(N))（维持树的平衡） N叉树 大多数的数据库存储并没有采用二叉树，原因：索引不仅仅存在于内存中，还要写到磁盘上 对于有100W节点的平衡二叉树，树高为20，即一次查询可能需要访问20个数据块 假设HDD，随机读取一个数据块需要10ms左右的寻址时间 即一次查询可能需要200ms – 慢成狗 为了让一个查询尽量少的读取磁盘，就必须让查询过程访问尽量少的数据块，因此采用N叉树 N的大小取决于数据页的大小和索引大小 在InnoDB中，以INT（4 Bytes）字段为索引，假...

概念 事务：保证一组数据库操作，要么全部成功，要么全部失败 在MySQL中，事务支持是在存储引擎层实现的 MyISAM不支持事务 InnoDB支持事务 隔离性与隔离级别 事务特性：ACID（Atomicity、Consistency、Isolation、Durability） 如果多个事务并发执行时，就可能会出现脏读、不可重复读、幻读（phantom read）等问题 解决方案：隔离级别 隔离级别越高，效率就会越低 SQL标准的事务隔离级别 READ-UNCOMMITTED 一个事务还未提交时，它所做的变更能被别的事务看到 READ-COMMITTED 一个事务提交之后，它所做的变更才会被其他事务看到 REPEATABLE-READ 一个事务在执行过程中所看到的数据，总是跟这个事务在启动时看到的数据是一致的 同样，在RR隔离级别下，未提交的变更对其他事务也是不可见的 SERIALIZABLE 对同一行记录，写会加写锁，读会加读锁，锁级别是行锁 当出现读写锁冲突时，后访问的事务必须等前一个事务执行完成，才能继续执行 默认隔离级别 Oracle：READ-COMMITTED...

更新语句123mysql> CREATE TABLE T (id INT PRIMARY KEY, c INT);mysql> UPDATE T SET c=c+1 WHERE id=2; 执行过程 通过连接器，客户端与MySQL建立连接 update语句会把T表上的所有查询缓存清空 分析器会通过词法分析和语法分析识别这是一条更新语句 优化器会决定使用id这个索引（聚簇索引） 执行器负责具体执行，找到匹配的一行，然后更新 更新过程中还会涉及redolog（重做日志）和binlog（归档日志）的操作 redolog – InnoDB 如果每次更新操作都需要直接写入磁盘（在磁盘中找到相关的记录并更新），整个过程的IO成本和查找成本都很高 针对这种情况，MySQL采用的是WAL技术（Write-Ahead Logging）：_先写日志，再写磁盘_ 当有一条记录需要更新的时候，InnoDB会先把记录写到redolog（redolog buffer），并更新内存（buffer pool） InnoDB会在适当的时候（例如系统空闲），将这个操作记录到磁盘里面（刷脏页） InnoDB的r...