适用环境MySQL 8.0存储引擎以 InnoDB 为主。索引的最终目标不是“数量多”而是让常用查询以更少的页面访问得到更少的候选行。1. 索引索引是存储引擎维护的、有序的数据结构。它保存索引键及定位记录所需的信息使 MySQL 不必从第一行扫描到最后一行索引类似书的目录但数据库索引还会随着数据变化而维护select可能因索引减少扫描、排序和回表insert、delete、更新索引列时需要同步修改索引索引占用磁盘与缓冲池空间优化器按成本选择执行计划有索引不等于一定使用因此索引是“以空间和写入成本换取读取效率”适合高频查询而不是给所有列机械建索引2. InnoDB 主要使用 B 树[!NOTE]InnoDB是MySQL的一种存储引擎负责真正管理表的数据、索引、事务等底层操作2.1 Hash 为什么不是默认选择Hash 根据哈希值定位桶平均等值查找很快但键值没有顺序难以支持、between、前缀排序和连续范围扫描。InnoDB 的普通索引只支持 B-treeMEMORY引擎才允许显式选择 Hash。InnoDB 内部的自适应哈希索引是一种自动优化机制不能等同于用户创建的索引。2.2 二叉树为什么不适合磁盘索引普通二叉搜索树可能退化成链表AVL、红黑树虽能控制高度但每个节点只有两个分支面对海量数据时树仍然较高索引节点通常按“页”存储向下一层可能意味着访问另一个页面所以数据库更希望一层容纳更多分支减少树高和页面访问次数2.3 B 树2.3.1 B树的优势B 树是多路平衡查找树核心特点如下非叶子节点主要保存分隔键和子页面指针同一页可容纳较多分支树通常很矮真正的索引记录集中在叶子层从根到任意叶子的路径长度相同叶子页按键值顺序组织并通过链表相连找到范围起点后可顺序扫描插入、删除会触发页分裂、合并或重平衡仍能保持有序和平衡根页分隔键内部页 A内部页 B叶子页120叶子页2140叶子页4160等值查询沿根页逐层缩小范围范围查询先定位第一个叶子记录再沿叶子页有序扫描。MySQL 文档常统称其为 B-tree讨论 InnoDB 实现原理时通常称 B 树。2.3.2 B树与B树的区别区别B树B树数据存在哪里所有节点只有叶子节点非叶子节点保存数据只保存索引叶子节点没有特殊关系通过链表连接范围查询一般非常方便数据库使用较少大量使用3. MySQL 中的页3.1 InnoDB 的数据页页是 InnoDB 管理索引和数据的基本单位索引页默认大小为 16KBshowvariableslikeinnodb_page_size;每一个页中即使没有数据也会使用16KB的存储空间一次查询需要的页若已在 Buffer Pool 中可直接从内存读取不在时才需要从磁盘加载。按页读取利用了局部性刚访问的数据及其附近数据近期再次使用的概率较高。一个索引页可概括为区域作用File Header / Trailer页号、校验及前后页等信息Page Header记录数、目录槽数、空闲空间等页内状态Infimum / Supremum表示页内最小、最大边界的系统记录User Records按索引键组织的用户记录Page Directory保存分组末端记录的槽辅助页内二分定位数据页的基本结构如下图页内记录通过链表维持逻辑顺序查询时先对 Page Directory 的槽做二分定位再在很小的分组内查找。B 树解决“定位哪一页”页目录解决“在这一页的哪里”。叶子页之间的双向链表则服务于跨页范围扫描。类似“三层 B 树可存两千多万行”只是基于固定页大小、索引项和行大小的估算。真实容量会受主键长度、行格式、变长列、页填充率和分裂影响实际查询也不等于固定发生三次磁盘 I/O因为根页和热点页通常已被缓存。3.2 页文件头和页文件尾页文件头与页文件尾中包含的信息如下图上图中我们只关注上一页页号和下一页页号就类似链表中的头节点、尾节点链接形成一个双向链表3.3 页主体页主体是保存真实数据的主要区域在每当创建一个新页时都会自动分配两个行分别是页内最小行Infimun、页内最大行Supremun这两行实际不存储任何真实信息而是做为数据行链表的头和尾。此外每一个数据行都有一个记录下一行的地址偏移量的区域next_record将页内所有数据行组成了一个单向链表其结构图如下当有一个新页插入数据时将Infimun连接第一个数据行,最后一行真实数据行连接Supremun该单向链表就如下图结构所示3.4 页目录为了避免沿着链表顺序逐个比对查找每个页数百行数据InnoDB通过二分查找法来解决查找效率问题所利用的方式就是在每一页中加入一个叫页目录Page Directory的结构将页内包括头行、尾行在内的所有行进行分组约定头行单独为一组其他组最多8条数据同时把每个组最后一行在页中的地址按主键从小到大的顺序记录在页目录中页目录中的每一个位置称为一个槽每个槽都对应了一个分组一旦分组中的数据行超过分组的上限8个时就会分裂出一个新的分组所以后续在查询某行时通过二分查找先找到对应的槽随后在槽内最多8个数据行中进行遍历即可3.5 数据页头数据页头记录了当前页保存数据相关的信息如下图4. 聚集索引、二级索引与回表4.1 聚集索引每张 InnoDB 表都有一个聚集索引其叶子记录保存完整行数据数据按聚集键组织有primary key使用主键作为聚集索引无主键选择第一个所有列都为not null的unique唯一索引作为聚集索引两者都没有创建名为GEN_CLUST_INDEX的隐藏聚集索引使用 6 字节行 ID也就是偷偷生成DB_ROW_ID按照这个GEN_CLUST_INDEX隐藏ID建立聚集索引所以“主键索引就是聚集索引”只适用于通常的 InnoDB 主键场景并非概念上的绝对同义关系。主键应尽量短、稳定因为主键值还会存入每个二级索引。递增主键通常有利于顺序插入但仍应先满足业务身份和分布式设计要求4.2 二级索引与回表聚集索引之外的索引称为二级索引。其叶子记录主要保存“二级索引列 对应主键值”。例如往篇中提及的在student_design2(name)上建索引后按姓名查询的过程是搜索姓名索引得到匹配记录的主键id再用id搜索聚集索引取得完整学生行。第二步叫回表。如果查询所需列已全部包含在同一个二级索引中可直接返回这叫覆盖索引。例如索引(class_id, name)可覆盖selectclass_id,namefromstudent_design2whereclass_id1;覆盖索引减少回表但不要为了覆盖select *而建立过宽索引宽索引会降低单页容量并增加写入成本。5. 索引的分类分类角度类型含义业务与约束主键、唯一、普通主键标识行唯一索引限制重复普通索引只加速访问索引列数单列、复合一个索引由一列或多列按顺序组成InnoDB 存储聚集、二级叶子保存完整行或保存索引键与主键专用用途全文、空间全文检索使用倒排结构空间索引使用 R-tree约束负责数据规则索引负责访问路径两者不是同一种对象。primary key、unique会用唯一索引实现外键要求相关列存在可用索引从表缺少时 MySQL 会自动创建但这不代表外键就是索引。6. 索引操作6.1 手动创建索引6.1.1 主键索引主键列的值必须唯一且非空一张表只能定义一个主键但主键可以由多列组成。方式一建表时使用列级语法适合单列主键写法最简洁。createtablet_user_pk1(idbigintunsignedprimarykeyauto_increment,usernamevarchar(30)notnull);方式二建表时使用表级语法主键定义与字段定义分开结构更清晰并且可以定义复合主键。createtablet_user_pk2(idbigintunsignedauto_increment,usernamevarchar(30)notnull,primarykey(id));方式三给已有表添加主键先添加主键再设置自增是因为auto_increment列必须受到索引支持。createtablet_user_pk3(idbigintunsignednotnull,usernamevarchar(30)notnull);altertablet_user_pk3addprimarykey(id);altertablet_user_pk3modifyidbigintunsignednotnullauto_increment;添加前必须检查已有的id是否包含NULL或重复值否则主键创建会失败。CREATE INDEX不能创建主键已有表必须使用ALTER TABLE ... ADD PRIMARY KEY6.1.2 唯一索引唯一索引用于电话号码、学号、邮箱等不可重复的业务字段。若字段允许NULLMySQL 唯一索引通常允许多个NULL要求必须填写且唯一时应同时定义NOT NULL。方式一建表时使用列级语法createtablet_user_uk1(idbigintunsignedprimarykeyauto_increment,usernamevarchar(30)notnullunique);方式二建表时命名唯一约束显式命名便于根据业务含义识别和删除工程中更推荐这种写法。createtablet_user_uk2(idbigintunsignedprimarykeyauto_increment,usernamevarchar(30)notnull,constraintuk_user_usernameunique(username));方式三给已有表添加唯一约束altertablet_user_uk3addconstraintuk_user_usernameunique(username);方式四使用独立的索引语句createuniqueindexuk_user_usernameont_user_uk4(username);UNIQUE强调“不允许重复”的数据规则UNIQUE INDEX强调实现该规则的索引结构在 MySQL 中两者都会建立唯一索引6.1.3 普通索引普通索引只提供查询访问路径不限制字段值重复。索引名建议使用idx_表或业务_字段便于维护。方式一建表时创建createtablet_user_idx1(idbigintunsignedprimarykeyauto_increment,phonevarchar(20),indexidx_user_phone(phone));方式二使用ALTER TABLE给已有表添加altertablet_user_idx2addindexidx_user_phone(phone);方式三使用CREATE INDEX独立创建createindexidx_user_phoneont_user_idx3(phone);方式二和方式三最终都给已有表增加索引。ALTER TABLE适合把多项表结构调整写在一起CREATE INDEX的语义则更直观。MySQL 8.0.13 及以上还支持函数索引表达式需要额外一层括号createindexidx_user_birth_yearont_user_idx3((year(birthday)));函数索引保存的是表达式结果适合不能改写成原列范围查询、且确实频繁使用相同表达式的场景6.2.4 复合索引复合索引包含两个或更多索引列创建语法与单列索引相同只需按查询需要排列多个字段。具体利用规则见第 7 节“复合索引与最左前缀”。方式一建表时创建createtablet_user_multi1(idbigintunsignedprimarykeyauto_increment,class_idbigintunsignednotnull,usernamevarchar(30)notnull,indexidx_user_class_name(class_id,username));方式二使用ALTER TABLE添加altertablet_user_multi2addindexidx_user_class_name(class_id,username);方式三使用CREATE INDEX创建createindexidx_user_class_nameont_user_multi3(class_id,username);复合索引也可以具有主键或唯一属性-- 复合主键同一学生与同一课程只能形成一条记录primarykey(student_id,course_id)-- 复合唯一索引同一班级中学号不能重复uniqueindexuk_student_class_sno(class_id,sno)6.2 查看索引6.2.1 查看完整索引信息下面三种写法等价实际使用一种即可showindexfromt_user_multi1;showindexesfromt_user_multi1;showkeysfromt_user_multi1;MySQL 命令行客户端可用\G将宽表结果纵向显示showkeysfromt_user_multi1\G\G是客户端结束符不是标准 SQL 语法。在 DBeaver 中通常直接执行带分号的SHOW INDEX通过结果网格查看即可。字段含义Key_name索引名称主键固定为PRIMARYNon_unique0表示不允许重复1表示允许重复Seq_in_index当前列在复合索引中的顺序从1开始Column_name索引列名Cardinality不同值数量的估算不是精确统计值Index_type索引结构InnoDB 普通索引通常显示BTREEVisible该索引是否对优化器可见Expression函数索引使用的表达式普通列索引通常为NULL6.2.2 查看表结构摘要desct_user_multi1;DESC的Key列只提供摘要PRI表示主键UNI表示唯一索引MUL表示该列可出现重复值的索引。它不能完整展示索引名和复合索引列顺序因此不能代替SHOW INDEX。6.2.3 查看真实建表定义showcreatetablet_user_multi1;修改或删除索引前先查看建表语句可以确认字段完整属性、约束名和索引名避免删除错对象。6.2.4 查看是否使用索引使用explain select查询可以知道该查询是否使用了索引以及使用了哪个索引例如select_type查询类型常见有simple、primary、subquery、derived、uniontypeMySQL 访问这张表时采用什么方式。排序有以下【好- system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL -差】All全表扫描index扫描全部索引树range扫描部分索引索引范围扫描对索引的扫描开始于某一点返回匹配值域的行常见于between、、等的查询ref使用非唯一索引或非唯一索引前缀进行的查找不是主键或不是唯一索引eq_ref和const的区别eq_ref唯一索引性扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描const、system单表中最多有一个匹配行查询起来非常迅速例如根据主键或唯一索引查询。system是const类型的特例当查询的表只有一行的情况下使用system。NULL不用访问表或者索引直接就能得到结果possible_keysMySQL 判断这条 SQL可能使用的索引但不代表最终真的用了key这个才表示 MySQL 最终实际选择使用的索引Extra执行情况的说明和描述常见有以下Extra含义Using index使用覆盖索引不需要回表读取完整行Using where还需要通过 WHERE 条件进行过滤Using index condition使用索引条件下推ICPUsing temporary使用临时表Using filesort需要额外排序Using index for group-byGROUP BY / DISTINCT 等利用索引优化Impossible WHEREWHERE 条件不可能成立Select tables optimized away优化器可以直接得到结果不需要按常规方式访问表NULL没有额外信息6.3 修改索引MySQL 可以重命名索引altertablet_user_multi1renameindexidx_user_class_nametoidx_user_class_username;但不能直接修改索引包含的字段或字段顺序。此时应先删除旧索引再创建新索引把两个动作写在同一条ALTER TABLE中可以清楚表达一次结构调整altertablet_user_multi1dropindexidx_user_class_username,addindexidx_user_name_class(username,class_id);MySQL 8.0 还可修改非主键索引的可见性用于验证优化器不使用该索引时的执行计划altertablet_user_multi1alterindexidx_user_name_class invisible;altertablet_user_multi1alterindexidx_user_name_class visible;不可见索引仍会在写入时维护所以它不能减少索引的空间和写入成本。6.4 删除索引删除前先执行SHOW CREATE TABLE或SHOW INDEX原因是主键、唯一约束、外键和普通索引的删除语法及依赖关系不同。6.4.1 删除主键索引altertablet_user_pk1dropprimarykey;主键名固定为PRIMARY不能使用普通的DROP INDEX 索引名代替。若主键列带有auto_increment并且主键是支持该自增列的唯一索引直接删除会报错。应先核对字段完整定义再移除自增属性-- 第一步确认 unsigned、not null、default、comment 等原有属性showcreatetablet_user_pk1;-- 第二步只移除 auto_increment其他需要保留的属性必须重新写全altertablet_user_pk1modifyidbigintunsignednotnull;-- 第三步自增依赖解除后再删除主键altertablet_user_pk1dropprimarykey;MODIFY不会自动保留未写出的字段属性因此不能不看原定义就机械执行MODIFY id BIGINT。生产表还应先检查该主键是否被其他表的外键引用。6.4.2 删除普通、唯一或复合索引以下两种写法等价dropindexidx_user_phoneont_user_idx1;altertablet_user_idx1dropindexidx_user_phone;删除唯一索引会同时取消它所实现的唯一性规则。删除复合索引会删除整个索引不能只从索引中直接移除某一列若需改变列组成应删除后重建。外键约束与支持外键的索引是两个对象。如果某索引仍被外键检查需要MySQL 会拒绝直接删除应先确认依赖再决定是保留索引、建立替代索引还是删除对应外键约束。参考MySQL 8.0InnoDB 聚集索引与二级索引MySQL 8.0InnoDB 索引物理结构MySQL 8.0复合索引MySQL 8.0B-tree 与 Hash 索引MySQL 8.0CREATE INDEXMySQL 8.0EXPLAIN以上是我关于MySQL的笔记分享感谢你读到这里这也是我学习路上的一个小小记录。