MySQL

MySQL 0 参考资料 黑马程序员 MySQL数据库入门到精通 图解MySQL介绍 MySQL | CS-Notes 面试笔记 MySQL面试题 MySQL常见面试题总结 SQL语句 通用语法 分号结尾，可多行书写 不区分大小写 注释采用–、#、/**/ 分类 DDL(Data Defination Language): 数据定义语言，用来定义数据库对象（数据库，表，字段）。 DML(Data Manipulation Language): 数据操作语言，用来对数据库表中数据进行增删改 DQL(Data Query Language): 数据查询语言，用来查询数据库中表的记录。 DCL(Data Control Language): 数据控制语言，用来创建数据库用户、控制数据库访问权限。 1 存储引擎 1.1 MySQL体系结构 连接层：负责连接处理、授权认证，以及相关的安全服务。 服务层：提供SQL借口，完成缓存查询，SQL的分析和优化，部分内置函数的执行，跨存储引擎功能的实现（过程、函数等）。 引擎层：负责数据的存储和提取，服务层通过API和存储引擎通话，不同存储引擎有不同功能。 存储层：将数据存储在文件系统上，完成于存储引擎的交互。 1.2 存储引擎特点 InnoDB：支持事务、支持行级锁、支持外键。 MyISAM：不支持事务，支持表级锁，访问速度快。 Memory：数据存放在内存，访问速度快，支持Hash索引。 2 索引 索引是帮助MySql高效获取数据的数据结构。 2.1 索引结构 索引是在存储引擎层实现的，不同存储引擎有不同的结构 索引结构 描述 B+Tree索引 大部分存储引擎都支持 Hash索引 底层使用Hash表实现，针对精确匹配有效，不支持范围查询 R-Tree索引（空间索引） MyISAM引擎拥有的特殊索引，用于地理空间数据类型 Full-Text索引 倒排索引，类似于Lucene、ES InnoDB和MyISAM都支持B+Tree索引和全文索引。 2.1.1 B+Tree索引 为什么不用二叉搜索树？ 顺序插入会退化成链表，查询效率降低，并且层数较深。 为什么不用红黑树？ 大数据量的情况下，层级较深。...

MySQL补充 1 InnoDB中数据如何存放 InnoDB引擎中，一张表的数据是存放在“tableName.idb”表空间文件中的。 表空间由段（segment）、区（extent）、页（page）、行（row）组成。 行：记录按行存放 页：记录的读取写入是以页为单位，页是InnoDB存储引擎磁盘管理的最小单元。页的类型有数据页、undo log页、溢出页等。16KB。 区：表中数据量大的时候，为某个索引分配空间不再按照页为单位划分，而是以区为单位进行分配。1M。 段：表空间有索引段（B+Tree非叶子节点）、数据段（B+Tree叶子节点）、回滚段（MVCC）。 Compact行格式中，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。 记录的额外信息： 变长字段长度列表：存储变长字段的数据占用的大小，当数据表没有变长字段的时候，表里的行格式就不会有「变长字段长度列表」。 NULL值列表：表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。（1标识该列为NULL，0标识不为NULL）。当数据表的字段都定义成 NOT NULL 的时，表里的行格式就不会有 NULL 值列表。 记录头信息： delete_mask ：标识此条数据是否被删除。 next_record：下一条记录的位置。 record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录。 记录的真实数据： 记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer。 row_id：如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。 trx_id：事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。 roll_pointer：这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。 2 两阶段提交 事务提交后，redolog和binlog都要持久化到磁盘，但是这两个日志是独立的逻辑，可能会出现半成功的情况，也就是两个日志逻辑不一致： 如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。 导致从库的值是旧值，主库的是新值。 如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。 导致从库的值是新值，主库的是旧值。 MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。...

MySQL面试 1 基础 MySQL执行一条Select语句期间发生了什么？ 通过连接器建立连接，管理链接、校验用户身份。 查询缓存：命中直接返回。 解析SQL，解析器对SQL查询语句进行词法语法分析。 执行SQL： 预处理：检查表或者字段是否存在； 优化：选择查询成本最小的执行计划； 执行：根据查询计划执行SQL语句，从存储引擎读取数据，返回给客户端。 MySQL 的 NULL 值是怎么存放的？ MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。 NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。 MySQL 怎么知道 varchar(n) 实际占用数据的大小？ MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。 varchar(n) 中 n 最大取值为多少？ 一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。 行溢出后，MySQL 是怎么处理的？ 如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。 Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。 Compressed 和 Dynamic 这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。 2 索引 索引的分类 按数据结构分类：B+tree索引、Hash索引、Full-text索引 按物理存储分类：聚簇索引、非聚簇索引 按字段特性分类：主键索引、唯一索引、普通索引、前缀索引...