ByteCoding

市民信息123456789CREATE TABLE `t` ( `id` INT(11) NOT NULL, `city` VARCHAR(16) NOT NULL, `name` VARCHAR(16) NOT NULL, `age` INT(11) NOT NULL, `addr` VARCHAR(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`)) ENGINE=InnoDB; 查询语句1SELECT city,name,age FROM t WHERE city='杭州' ORDER BY name LIMIT 1000; 存储过程12345678910111213DELIMITER ;;CREATE PROCEDURE idata()BEGIN DECLARE i INT; SET i=0; WHILE i<4000 DO INSERT INTO t VALUES (i,'杭州',concat('zhongmingmao&#x ...

count(*)实现 MyISAM：将表的总行数存放在磁盘上，针对无过滤条件的查询可以直接返回 如果有过滤条件的count(*)，MyISAM也不能很快返回 InnoDB：从存储引擎一行行地读出数据，然后累加计数 由于MVCC，在同一时刻，InnoDB应该返回多少行是不确定 样例假设表t有10000条记录 session A session B session C BEGIN; SELECT COUNT(*) FROM t;（返回10000） INSERT INTO t;（插入一行） BEGIN; INSERT INTO t（插入一行）; SELECT COUNT(*) FROM t;（返回10000） SELECT COUNT(*) FROM t;（返回10002） SELECT COUNT(*) FROM T;（返回10001） 最后时刻三个会话同时查询t的总行数，拿到的结果却是不同的 InnoDB默认事务隔离级别是RR，通过MVCC实现 每个事务都需要判断每一行记录是否对自己可见 优化 InnoDB是索引组织表 聚簇索引树：叶 ...

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 |+-----------------------+-------+ 文件空洞空洞：可以被复用但没有被使用的空间，经过大量增删改的表，都会存在空洞 删除 如果删掉R4， ...

场景建表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 | Nu ...

场景 维护一个市民系统，有一个字段为身份证号 业务代码能保证不会写入两个重复的身份证号（如果业务无法保证，可以依赖数据库的唯一索引来进行约束） 常用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为25 ...

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

索引模型哈希表实现上类似于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）字段为索引，假设页大 ...