核心内容摘要
探寻“另类人与物动交ⅩXXXB”：一场颠覆想象的感官盛宴
MySQL索引入门
什么是索引
索引的底层实现与存储特性
什么时候适合添加索引
索引的创建、删除
索引失效的六大场景
索引的完整分类
什么是索引索引是 MySQL 数据库为提升数据检索效率而设计的有序数据结构其核心作用是将无序的原始数据转化为可快速定位的有序结构类比日常使用的字典 —— 通过拼音 / 部首索引能直接定位到目标汉字而非逐页翻阅。
从底层逻辑来看索引本身是一张独立的 “映射表”存储了索引字段的值、对应的主键以及指向实体表记录的物理存储地址。
由于实际业务中数据表的体量通常极大索引文件本身也会占用大量存储空间无法全部加载到内存中因此索引默认以文件形式存储在磁盘上且其排序逻辑与TreeSet红黑树一致保证了查找、插入、删除操作的高效性。
索引的核心优势提升查询效率合理设计的索引能将全表扫描转化为索引树的精准查找使 MySQL 的查询性能实现质的飞跃降低数据库 IO 开销索引通过有序结构减少了磁盘 IO 的次数避免了对全表数据的逐一读取大幅降低硬件资源消耗索引的潜在代价更新性能损耗对表执行INSERT、UPDATE、DELETE操作时MySQL 不仅要修改实体数据还需同步更新索引文件数据量越大、索引越多更新耗时越明显。
磁盘空间占用每个索引都会生成独立的索引文件无节制创建索引会快速消耗磁盘存储空间。
维护成本增加过多的索引会增加数据库的维护负担如索引碎片清理、优化器选择最优索引的计算成本等。
索引的底层实现与存储特性在 MySQL 中索引是独立于数据表的对象不同存储引擎对索引的存储方式和实现逻辑不一样MyISAM 引擎索引与数据分离存储索引文件.MYI单独存放索引信息数据文件.MYD存储实际数据索引中记录的是数据行的物理存储地址。
InnoDB 引擎采用 “聚簇索引” 设计索引与数据融合存储在表空间tablespace中 —— 主键索引的叶子节点直接存储整行数据辅助索引的叶子节点存储主键值通过主键再回表查询完整数据。
MEMORY 引擎索引直接存储在内存中基于哈希表或 B 树实现适用于临时表、高频读写的小表但数据重启后会丢失。
什么时候适合添加索引索引并非越多越好以下场景适合添加索引数据量阈值达标当表中数据量达到一定规模通常建议万级以上索引的收益会远大于维护成本小表使用索引反而可能因索引查找 回表的额外开销降低效率查询条件高频字段字段频繁出现在WHERE子句、JOIN关联条件、ORDER BY/GROUP BY子句中DML 操作低频字段字段极少被修改若字段需高频更新则需谨慎创建索引PS索引的添加必须适可而止建议单表索引数量不超过 5 个核心业务字段优先非核心查询字段不建索引
索引的创建、删除
索引的创建-- 为temp表的ENAME字段创建单列索引mysqlcreateindextemp_ename_indexontemp(ENAME);Query OK,0rowsaffected(
15sec)Records:0Duplicates:0Warnings:0-- 查看表结构确认索引创建成功Key列显示MUL表示存在非唯一索引mysqldesctemp;--------------------------------------------------|Field|Type|Null|Key|Default|Extra|--------------------------------------------------|EMPNO|int(
|NO||NULL|||ENAME|varchar(
|YES|MUL|NULL|||JOB|varchar(
|YES||NULL|||MGR|int(
|YES||NULL|||HIREDATE|date|YES||NULL|||SAL|double(7,
|YES||NULL|||COMM|double(7,
|YES||NULL|||DEPTNO|int(
|YES||NULL||--------------------------------------------------8rowsinset(
00sec)-- 为temp表的ENAME和JOB字段创建复合索引mysqlcreateindextemp_ename_job_indexontemp(ename,job);Query OK,0rowsaffected(
14sec)Records:0Duplicates:0Warnings:
索引的删除-- 删除temp表的temp_ename_index索引mysqldropindextemp_ename_indexontemp;Query OK,0rowsaffected(
07sec)Records:0Duplicates:0Warnings:
索引有效性验证EXPLAIN关键字EXPLAIN可判断索引是否被命中type列显示查询类型ALL表示全表扫描索引未生效ref/range/eq_ref等表示索引生效key列显示实际使用的索引名称NULL表示未使用索引rows列显示 MySQL 预估需要扫描的行数数值越小说明查询越高效。
示例无索引时查询ename KING的执行计划mysqlexplainselect*fromtempwhereenameKING;-----------------------------------------------------------------------------------------------------------|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|-----------------------------------------------------------------------------------------------------------|1|SIMPLE|temp|NULL|ALL|NULL|NULL|NULL|NULL|14|
1
00|Usingwhere|-----------------------------------------------------------------------------------------------------------1rowinset,1warning(
00sec)解读typeALL表示全表扫描keyNULL表示未使用索引需扫描 14 行数据效率较低。
索引失效的六大场景
场景 1模糊查询以 % 开头当LIKE查询的匹配符%出现在字符串开头时索引无法被匹配触发全表扫描-- 索引失效%在开头select*fromempwhereenamelike%T;-- 索引生效%在结尾select*fromempwhereenamelikeT%;优化方案避免前缀模糊查询若业务必须使用可考虑全文索引FULLTEXT或数据预处理如存储字段的反向字符串
场景 2OR连接的条件字段未全部建索引OR查询的逻辑是 “只要满足任一条件即可”若OR两侧的字段有一个未建索引MySQL 会放弃索引选择全表扫描-- 假设仅ename有索引job无索引此查询索引失效select*fromempwhereenameKINGorjobMANAGER;优化方案尽量用UNION替代OR若必须用OR则确保所有条件字段都建索引。
场景 3复合索引未遵循 “最左前缀原则”复合索引的生效顺序是从左到右查询时若跳过左侧的索引字段直接使用右侧字段索引会完全失效-- 复合索引temp_ename_job_index (ename, job)-- 索引生效使用左侧字段enameselect*fromtempwhereenameKING;-- 索引失效跳过ename直接使用jobselect*fromtempwherejobMANAGER;-- 索引生效遵循最左前缀同时使用enamejobselect*fromtempwhereenameKINGandjobMANAGER;优化方案创建复合索引时将查询频率最高的字段放在左侧
场景 4索引列参与数学 / 算术运算若在WHERE子句中对索引字段进行加减乘除等运算MySQL 无法直接使用索引需先计算再匹配导致索引失效-- 索引失效sal字段参与运算select*fromempwheresal10005000;-- 索引生效将运算移到右侧等价逻辑select*fromempwheresal
;优化方案避免在索引列上执行运算将运算逻辑转移到查询条件的右侧
场景 5索引列使用函数处理对索引字段使用内置函数如SUBSTR、DATE_FORMAT、CONCAT等会破坏索引的有序性导致索引失效-- 索引失效ename使用SUBSTR函数select*fromempwhereSUBSTR(ename,1,
K;-- 索引失效hiredate使用DATE_FORMAT函数select*fromempwhereDATE_FORMAT(hiredate,%Y)1981;优化方案避免在索引列上使用函数若业务需要可通过新增冗余字段如存储ename的首字母、hiredate的年份建立索引
场景 6隐式类型转换导致索引失效当索引字段的类型与查询条件的值类型不一致时MySQL 会进行隐式类型转换进而触发索引失效-- 假设empno是int类型以下查询索引失效字符串转数字select*fromempwhereempno7369;-- 索引生效类型匹配select*fromempwhereempno7369;优化方案确保查询条件的值类型与索引字段类型完全一致避免隐式转换
索引的完整分类
按索引字段数量划分单列索引仅对单个字段创建的索引复合索引对多个字段组合创建的索引
按索引特性划分主键索引主键字段自动生成的索引具有唯一性且非空一张表只能有一个主键索引唯一性索引基于UNIQUE约束的字段创建的索引保证索引字段的值唯一普通索引无唯一性约束的索引仅用于提升查询效率全文索引针对文本内容的索引如文章、评论支持模糊匹配和关键词检索仅适用于CHAR、VARCHAR、TEXT类型
按存储结构划分B 树索引MySQL 默认的索引结构所有数据存储在叶子节点非叶子节点仅存索引值支持范围查询、排序适用于绝大多数场景哈希索引基于哈希表实现查找速度极快O (
但不支持范围查询、排序MEMORY 引擎默认使用哈希索引。
空间索引针对地理空间数据如GEOMETRY、POINT类型的索引适用于位置相关的查询使用场景较窄。