11.索引概念

索引：

1. 作用：提高查询效率
2. 索引不止存在内存中，还要写到磁盘上

常见的索引模型：

• 哈希表：适用于只有等值查询的场景（kv键值对）
• 有序数组：等值查询和范围查询场景（redis的有序集合）
• 搜索树：二叉树是搜索效率最高，大多数的数据库存储却并不使用二叉树 > [!TIP] 索引不止存在内存中，还要写到磁盘上。N 叉树（B+树）由于在读写上的性能优点，以及适配磁盘的访问模式

索引类型：

• 按「数据结构」分类：B+tree 索引、Hash 索引、Full-text 索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引、覆盖索引。
• 按「字段个数」分类：单列索引、联合索引。
• 补充说明：
  1. 主键索引（聚簇索引）
     1. 主键索引是一种特殊的唯一索引，单表只能有一个（primary key）
  2. 非主键索引（非聚簇索引）
     1. 唯一索引 索引的列的值必须唯一，可以有多个（unique key）
     2. 普通索引 非唯一索引（没有唯一性约束）
     3. 前缀索引 特殊的索引类型，只使用索引列的前缀来建立索引
     4. 覆盖索引 特殊的索引类型，不需要回表就可以
     5. 全文索引 特殊的索引类型，在文本列上创建，以支持自然语言查询
     6. 空间索引 支持空间数据类型的查询和分析等操作，点查询、矩形查询、距离查询，又叫做矢量索引（opensearch）
     7. 哈希索引 等值查询

innodb的索引：

索引组织表：

InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表，每一个索引在 InnoDB 里面对应一棵 B+ 树

mysql数据行格式：

见 行记录结构.md

innodb 页分裂、页合并：

1. B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护
2. 如果数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。(数据复制的过程比较耗时)
3. 页分裂操作还影响数据页的利用率：原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。
4. 页合并：当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

innodb 索引优化：

• 使用最左前缀原则：索引项按照索引定义的字段顺序进行排序
• 使用覆盖索引优化
• 使用索引下推：遍历时先按照索引中包含的字段做判断，直接过滤掉不满足条件的记录，从而减少回表次数。（与之对应的是，按照字段顺序逐个比对，无索引下推，会先回表）
• 主键索引最好是自增
• 防止索引失效

select * from tuser where name like ‘张%’ and age=10 and ismale=1;

change buffer:

• 作用：在更新操作中，当数据页不在内存中的时候，在不影响一致性的前提下，mysql会把当前操作放到change buffer，等下次数据页被读到内存时再更新。

• 配置change buffer 大小： innodb_change_buffer_max_size

• 补充：

  • change buffer是持久化、crash-safe的（通过redo log）
  • 只有普通索引会用到change buffer;唯一索引因为唯一性约束，不可避免需要读出数据页;
  • 适用性：
    • 对于写多读少的业务，change buffer 效果明显；
    • 对于写入后立即查询的业务，反而多了维护change buffer的成本。
  • redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），change buffer主要节省的则是随机读磁盘的IO消耗

待移除

9. 优化器如何选择索引：
   1. 宗旨：找到一个最优的执行方案，并用最小的代价去执行语句
   2. 判断指标：
      1. 扫描行数（基于统计信息来估算符合的记录数即索引区分度，索引上不同值的数量叫做基数，越多越好）
      2. 临时表
      3. 排序
      4. 其他综合因素
   3. 查看各索引基数的方法：show index from T
   4. 纠正错误统计信息：analyze table T
10. 优化器索引选择错误如何处理：
    1. select * from T force index
    2. 不改变sql语义，微调sql语句，引导优化器使用正确索引
    3. 新建更合适的索引或者删除误用的索引
11. explain用法：见单独标题