【MySQL是怎么运行的】二、索引

ops/2025/3/14 22:57:51/

引擎层有支持索引，如InnoDB和MyISAM，区别就是InnoDB支持事务、外键和行锁

索引物理结构

页：一页16KB，一页包含了多行记录
行：包含元数据和真实数据
元数据：

record_type（记录的类型，0-普通，1-目录项，2-最小，3-最大）
next_record(下一条记录)
真实数据：c1，c2，c3，其他信息

CREATE TABLE s1
(id           INT AUTO_INCREMENT,key1         VARCHAR(100),key2         INT,key3         VARCHAR(100),key_part1    VARCHAR(100),key_part2    VARCHAR(100),key_part3    VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),KEY idx_key1 (key1),UNIQUE KEY idx_key2 (key2),KEY idx_key3 (key3),KEY idx_key_part (key_part1, key_part2, key_part3)
) ENGINE = INNODBCHARSET = utf8;

上述的表一行占用的大小为：
**元数据：**行头信息5B+事务ID6B+回滚指针7B+7个边长字段7B=25B
**真实数据：**id和key2是int类型，固定4B，其他字段都是varchar类型1003+1=301B。总共4B+301B7=1814B
总计：1814B+25B=1839B≈1.8KB

索引设计原则

读多写少的字段，如mtime就不行
数据量较大（100w）、查询比较频繁（where，group by）的表建立索引，不建议db排序
尽量选择区分度高的列（如身份证号，性别不行，重复度大于10%也不行）作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
字符串类型的字段，字段的长度较长，考虑前缀索引
尽量使用联合索引，较少单例索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

索引失效场景

不符合最左匹配原则、模糊查询%加在前面
索引上有函数或类型不匹配
<>, or两侧有非索引字段

索引判断姿势

explain sql
type：针对单表的访问方法

结果值从最好到最坏依次是:
system > ⭐️const️ > eq_ref > ⭐️ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > ⭐️range（底线） > index > ALL
const️：唯一键等于，如select * from s1 where key2=?
ref: 二级索引等于，如select * from s1 where key3=?
range: 范围查询，如select * from s1 where key3 in (?,?)
rows
预估的需要读取的记录条数，走索引后的筛选出来的行数,值越小越好，代表走索引筛选的数量多，索引效率高
filtered
某个表经过搜索条件过滤后剩余记录条数的百分比，值越大越好，代表大多数结果都是通过索引查出来的，而不是服务层筛选的
Extra
一些额外的信息