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

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

索引物理结构

页：一页16KB，一页包含了多行记录

行：包含元数据和真实数据

元数据：

1. record_type（记录的类型，0-普通，1-目录项，2-最小，3-最大）

2. 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 = INNODB
  CHARSET = utf8;

上述的表一行占用的大小为：

**元数据：**行头信息5B+事务ID6B+回滚指针7B+7个边长字段7B=25B

**真实数据：**id和key2是int类型，固定4B，其他字段都是varchar类型100 3+1=301B。总共4B+301B 7=1814B

总计：1814B+25B=1839B≈1.8KB

索引设计原则

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

索引失效场景

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

索引判断姿势

explain sql

type：针对单表的访问方法

1. 结果值从最好到最坏依次是:

   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 (?,?)

2. rows

   预估的需要读取的记录条数，走索引后的筛选出来的行数,值越小越好，代表走索引筛选的数量多，索引效率高

3. filtered

   某个表经过搜索条件过滤后剩余记录条数的百分比，值越大越好，代表大多数结果都是通过索引查出来的，而不是服务层筛选的

4. Extra

   一些额外的信息

• Using where: 当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时
• Using index: 使用了覆盖索引
• Using index condition：使用了索引下推
• Using union：索引合并，使用多个索引来取交集、并集后进行回表的优化操作
• Using filesort：文件排序，出现这个说明sql中有排序字段，要避免

索引如何优化

1. 没索引就建索引
2. 有索引但失效了就改sql，让索引生效。或强制InnoDB走预期索引

慢sql

慢sql包含读和写，一般超过100ms的执行时间我们都认为是慢sql。

慢查一般从索引方向治理

慢写一般从锁方向治理