Mysql索引默认InnoDB引擎Mysql索引的排序规则左前原则字符串排序核心规则1.英文字符例子2. 中文排序例子3.collate的规则按照储存结构聚簇索引主键索引/聚集索引二级索引非聚簇索引/辅助索引按照索引数量单列索引复合索引联合索引实战示例复合索引与最左前缀原则按照索引特性普通索引唯一索引主键索引唯一非空全文索引空间索引很少用这里不作介绍MySQL 覆盖索引 回表查询InnoDB概括1. 回表查询需要回表2. 覆盖索引不回表Using indexMysql索引的排序规则左前原则一句话联合索引 (col1,col2,col3)B 树叶子节点是先按 col1 排序col1 相同再 col2col2 相同再 col3。查询时要从索引最左边开始连续匹配索引才能生效一旦遇到范围条件 between like 前缀模糊后面的索引列失效。字符串排序字符串也遵循左前原则用第一个字符串索引的第一个字符从前往后依次排序字符大小以ASCII码表为基础。核心规则核心MySQL 里字符串排序name varchar按字符的编码字符集对应的编码值比较不是简单字典序字符集决定排序规则 (collate)1.英文字符例子我们例子里的 nameli、wang、zhangutf8mb4 常用排序 utf8mb4_general_cici case insensitive 不区分大小写L 和 l 当成一样cs case sensitive 区分大小写字母顺序li wang zhang比较逻辑从左到右逐个字符对比第一个不一样的字符就分出大小l (108) w (119) → l 更小li 排在最前面w z → wang 在 zhang 前面举个字符串对比细节zhang 和 zhao第 1 位 z z第 2 位 h h第 3 位 a a第 4 位 n vs o → n 编码更小✅ 所以 zhang zhao2. 中文排序例子中文名字重点面试容易忽略如果 name 是中文不是按拼音默认排序utf8mb4_general_ci按汉字 utf 编码值排序不是拼音utf8mb4_unicode_ci更标准 unicode 排序utf8mb4_0900_as_ci新版本推荐如果想要按拼音排序要专门的 collate例如 utf8mb4_pinyin_ci比如张三、李四直接用 utf8mb4_general_ci不一定李四在前要看编码值不是拼音 LiZhang。3.collate的规则回到联合索引 (name,age) 的排序逻辑先比较name按字符集 collate 规则比较字符串name 相等 → 再比较 age数字直接比大小按照储存结构聚簇索引主键索引/聚集索引InnoDB 特有主键就是聚簇索引叶子节点保存完整的一行数据一张表只能有 1 个聚簇索引如果没有主键InnoDB 会找唯一非空索引都没有就自动生成隐藏 rowid 作为聚簇索引即 MySQL 自动生成的排序 ID 序号二级索引非聚簇索引/辅助索引除聚簇索引之外都是二级索引叶子节点索引列 主键值不存完整行一张表可以多个二级索引在MysqlInnoDB引擎中使用改良版的B树存储数据而创造一个二级索引本质上就是创建一颗新的B树以创造的索引或者复合索引列为主键而叶子节点储存的数据叶为原表的主键ID和以原表为模版创造的索引或者复合索引按照索引数量单列索引最基础索引允许重复、允许 NULL只用来加速查询CREATEINDEXidx_addressONuser(address);复合索引联合索引多个字段组成一个索引遵循最左前缀原则CREATEINDEXidx_name_ageONuser(name,age);实战示例复合索引与最左前缀原则假设有一张用户订单表经常需要按user_id和order_time组合查询-- 创建包含两个字段的复合索引CREATEINDEXidx_user_timeONorders(user_id,order_time);使用EXPLAIN查看索引命中情况-- 命中复合索引最左前缀生效EXPLAINSELECT*FROMordersWHEREuser_id1001ANDorder_time2026-01-01;-- 命中复合索引只用到最左列 user_idEXPLAINSELECT*FROMordersWHEREuser_id1001;-- 无法命中复合索引跳过了最左列 user_idEXPLAINSELECT*FROMordersWHEREorder_time2026-01-01;最左前缀原则的生效条件复合索引(user_id, order_time)按「最左列优先」组织 B 树。只有当查询条件中包含最左列user_id时索引才会被命中若查询只用到order_time而跳过user_id则无法走该复合索引只能全表扫描或另建索引。按照索引特性普通索引最基础索引允许重复、允许 NULL只用来加速查询CREATEINDEXidx_addressONuser(address);唯一索引索引列的值不能重复可以有 NULL多个 NULL 是允许的CREATEUNIQUEINDEXidx_phoneONuser(phone);主键索引唯一非空特殊的唯一索引非空 唯一同时作为聚簇索引。全文索引用于长文本模糊搜索替代 like ‘%关键词%’like 前缀 % 不走普通索引CREATEFULLTEXTINDEXidx_contentONarticle(content);//模版SELECT*FROMarticleWHEREMATCH(content)AGAINST(mysql);例子空间索引很少用这里不作介绍MySQL 覆盖索引 回表查询InnoDB概括前提InnoDB 是聚簇索引主键索引叶子节点存完整行数据二级索引叶子节点只存【索引列 主键】。回表通过二级索引查到主键再拿着主键去主键索引里读取完整行数据。覆盖索引查询需要的所有字段都在二级索引里面不需要回表explain 里 Extra 显示 Using index。CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENT,-- 主键聚簇索引nameVARCHAR(32),ageINT,addressVARCHAR(100));-- 创建二级联合索引idx_name_age (name, age)CREATEINDEXidx_name_ageONuser(name,age);索引结构主键索引叶子节点 id - 整行(id,name,age,address)二级索引 idx_name_age叶子节点 (name,age) - id1. 回表查询需要回表示例 SQLsqlSELECTname,age,addressFROMuserWHEREname张三;执行过程在二级索引 idx_name_age 根据 name‘张三’ 找到记录拿到 name、age、id但是 SQL 需要查询 address二级索引没有 address拿着拿到的 id去主键索引查找整行数据取出 address 这个第二步操作就叫回表explain 结果重点plaintexttype: refkey: idx_name_ageExtra: NULL 没有Using index发生回表一句话二级索引只存索引列 主键查不在索引里的字段就要回主键索引再查一次。2. 覆盖索引不回表Using index查询的所有列全部包含在索引中不需要回表示例 SQLsqlSELECTname,ageFROMuserWHEREname张三;执行过程在二级索引 idx_name_age 找到 name‘张三’查询需要的字段 name、age 就在二级索引叶子节点直接返回不用回表explain 结果重点plaintexttype: refkey: idx_name_ageExtra: Using index – 代表触发覆盖索引✅ 另外二级索引叶子自带主键 id所以下面这条也属于覆盖索引sqlSELECTid,name,ageFROMuserWHEREname张三;索引是(name,age)叶子节点存 (name,age,id)三个字段全都有不需要回表。❌ 下面这条不是覆盖索引会回表sqlSELECTname,age,addressFROMuserWHEREname张三