Mysql:索引与B+树

在日常MySQL建表中我们有三种定义主键的方式。不同的主键定义方式在InnoDB底层B树聚簇索引的创建时机、磁盘开销、底层逻辑完全不同。方式一字段后直接定义主键内联主键CREATE TABLE user( id INT PRIMARY KEY, name VARCHAR(20) );1. 建表语句执行的同时InnoDB直接以当前主键字段构建聚簇索引B树。2. 数据在磁盘物理页中有序连续存储。3. 后续新增数据直接按照B树规则插入不会产生任何表重构。4. 性能最优是开发中最推荐的方式。总结建表 聚簇B树 一次性生成零额外开销。方式二先建表后续再追加主键CREATE TABLE user( id INT, name VARCHAR(20) ); ALTER TABLE user ADD PRIMARY KEY(id);1. InnoDB表必须要有聚簇索引。建表无显式主键时InnoDB按照优先级自动生成临时的聚簇B树。无主键时优先级自定义主键 唯一索引 隐藏6字节 row_id2. 执行 alter 新增主键时销毁原本的临时B树全表数据重新磁盘排序 基于新的自定义主键重新完整构建一颗全新的聚簇B树3. 整个过程会产生大量IO开销表数据量越大性能损耗越严重。4.删除主键也会触发全表重构。总结数据量大时会产生大量磁盘IO、锁表、性能雪崩生产环境严禁后期添加主键。方式三定义主键 额外普通索引/联合索引CREATE TABLE user( id INT PRIMARY KEY, name VARCHAR(20), INDEX idx_name(name) );InnoDB会同时存在两颗及以上B树1. 聚簇索引B树叶子节点存储完整的整行数据以主键为排序规则。2. 二级索引普通索引B树叶子节点只存储索引列的值 对应的主键值不存储整行数据。3. 回表机制当通过二级索引查询数据时先在二级B树找到对应主键 → 再拿着主键去聚簇B树中查询完整数据这个过程叫做回表查询。复合索引可以不回表已知 a 找 b 在a,b二级B树能直接找到 b索引创建原则1.比较频繁作为查询条件的字段应该创建索引2.唯一性太差的字段不适合单独创建索引即使频繁作为查询条件3.更新非常频繁的字段不适合作创建索引4.不会出现在where子句中的字段不该创建索引