• InnoDB 默认使用 B+树存储索引，一般是 3-4 层
    • 为什么用 B+树而不用红黑树？
      • 首先B树的节点会存储数据指针，这样会增加单个节点大小，导致树的层级变深，增加 IO 次数，淘汰
      • B+树只有叶子节点才会存储数据指针，缩小了节点大小，树的层级得到控制，采用
      • 红黑树首先实现难，且每个节点只有两个节点，在大数据量情况下树的层级将会特别深，IO 次数太多，代价高昂，淘汰
  • MySql 的数据存储在磁盘
  • 查询数据慢的原因一般都是卡在 IO 读取上
  • 提高 IO 读取的效率（满足需求的情况下）：
    • 减少 IO 读取的次数
    • 减少 IO 读取的数据量
  • 局部性原理：
    • 空间局部性：数据和程序都有聚集成群的倾向
    • 时间局部性：同时之前访问过的数据很大概率会被再次访问
  • 磁盘预读：内存在读取磁盘时，一般有一个最小的逻辑单元，称之为页（datapage），页的大小视操作系统而定，一般是 4K 或者 8K，而读取磁盘时都是取页的整数倍来读取，就算需要的数据只有一页带一点，也是要读两页出来然后再过滤多余的数据。
    • InnoDB 存储引擎每次读取 16K

  • 索引存储在磁盘中，每次查询会先将索引加载到内存然后查询

    1，索引采用的数据结构

    1，OLAP（联机分析处理）

    对海量历史数据进行分析，做出重要的决策
    主要是数据仓库形式（比如 Hive）
    OLAP 的索引结构：

  • key：实际数据行中存储的值

  • 文件的物理地址

  • offset：偏移量

    2，OLTP（联机事务处理）

    要求在很短的时间内返回对应的操作结果
    主要是关系型数据库形式（MySql，Oracle，MSSql）
    MySql 的索引结构：B+树

    2，可供选择的数据结构

    1，哈希表

    image.png

  • 哈希冲突，导致数据的散列分布不均匀，大量数据存储在链表里，导致查询耗时

  • 不支持范围查询，因为散列表是无序的，范围查询需要全部挨个遍历
  • 对于内存空间要求十分高
  • 等值查询速度非常快

MySql 中的 Hash（哈希）索引

  • memory 存储引擎使用 Hash 索引

  • InnoDB 支持自适应 Hash（MySql 自身决定使用 Hash 还是 B+树来存储索引）

    2，树

    3，MySql 的索引结构（B+树）

    image.png

  • 树的深度越小，IO 的读取次数越少，查询越快

    • 一般是 3-4 层深度的 B+树
    • 尽量选取占用空间小的字段作索引，这样每一个树节点能存更多的索引，树的层级也会更小，可支持的数据量也就越多
    • B+树 底层节点是一个双向链表，方便正反向范围查找

      1，InnoDB 的索引结构

      image.png

      2，Myisam 的索引结构

      image.png

      4，索引的分类

      1，聚簇（聚集）索引

      2，非聚簇（聚集）索引

      是否是聚簇索引取决于数据跟索引是否放在一起。
      InnoDB 只能有一个聚簇索引，向 InnoDB 插入数据时必须要包含一个索引的 key 值，这个索引的 key 值可以是主键，如果该表没有主键，那么 InnoDB 会自动生成一个 6 字节的 rowid（用户不可见）当作索引
      为什么只能有一个聚簇索引？

  • 因为聚簇索引的索引和数据是放在一起的，如果有多个聚簇索引，那么数据就必须要很多份，会产生数据冗余

  • 只能有一个聚簇索引，但是可以有很多非聚簇索引。

    1，单值（常规）索引

    指定单列为索引，一个表可以有多个单值索引

    2，唯一索引

    以不能重复的字段做的索引叫唯一索引，一般是主键

    3，复合索引

    多个列构成的索引

    4，全文索引

    全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列
    MySql 5.6.24 上 InnoDB 引擎也加入了全文索引

    5，与索引有关的问题

    假如有一张表 user（id，name，age），其中 id 是主键（聚集索引），name 是非聚集索引是，（name，age）是联合非聚焦索引

    1，回表

    如果使用非聚焦索引查询，因为非聚焦索引节点本身不存储数据，叶子节点只存储主键，索引再查询出主键时，还要根据主键去聚集索引里面查找到真正的数据，这样就走了两颗索引 B+树，这种情况就叫做回表

    1. SELECT * FROM user WHERE name = '**'

    2，索引覆盖

    和回表一样的步骤，但是如果要查询的数据不是全量数据，而是聚集索引和使用的非聚集索引的字段，那么就不必去聚集索引的 B+树里面查找真正的数据，这就是索引覆盖

    1. SELECT id name FROM user WHERE name = '**'

    3，最左匹配原则

    组合索引使用时必须从左开始匹配，例中的组合索引就是先匹配 name，再匹配 age
    当使用的过滤字段不符合组合索引的顺序或者不满足组合索引的最左字段时组合索引无法生效，但是 MySql 的优化器会在一定程度上调整过滤字段的顺序

    1. --下面这个符合最左匹配，组合索引生效
    2. SELECT * FROM user WHERE name = '**' AND age = *
    3. --下面这个符合最左匹配，组合索引生效
    4. SELECT * FROM user WHERE name = '**'
    5. --下面这个不符合最左匹配，组合索引=失效，但是有可能会被 MySql 优化调整为先 name 再 age
    6. SELECT * FROM user WHERE age = * AND name = '**'

    最左匹配可以这么理解：
    假如说有省市县三级做非聚集索引

  • 当使用具体的省市县过滤时，可以根据索引逐一过滤，最后确定是哪个县

  • 但是当使用市县过滤时，我们无法根据市来判断属于哪个省，只能全量查找，所以组合索引失效

  • 使用县市省时也一样，不过假如我们知道这个顺序，就可以认为的调整它的顺序为省市县，这样组合索引仍可生效

    4，索引下推（ICP Index Condition Pushdown）

    一个数据库命令的执行要经过三层

  • 客户端

  • 数据库服务器
  • 物理层存储引擎

假如要执行下面的 SQL

  1. SELECT * FROM user WHERE name = * AND age = *

1，没有索引下推

没有索引下推时，会先根据 name 从存储引擎中找出符合这个 name 过滤条件的数据并将其存储到内存中，然后在数据库服务器层（内存中）在进行 age 条件的过滤

  • 需要读取大量不必要的数据到内存中，增加回表次数

    2，有索引下推

    有了索引下推之后，会直接根据 name 和 age 双重条件从存储引擎中找出符合这个双重过滤条件的数据并将其存储到内存，并由数据库服务器层返回给客户端

  • 从物理层进行筛选，拷贝到内存的数据就会变少，减少回表次数

image.png

6，索引要不要设置自增？

在能满足业务的情况下，能使用自增主键就使用自增主键，原因如下：

  • 如果主键是自增的，那么在聚集索引的叶子节点里面存储的数据就是连续的，在插入新数据的时候只会在链表后面不停的新加入数据，不会影响到链表前面的数据，大大提升插入效率

    7，索引相关命令

    1，创建索引

    1，单值索引

    1. create index 索引名 on 表名(列)
    2. alter table 表名 add index 索引名(列)

    2，唯一索引

    1. create unique index 索引名 on 表名(列)
    2. alter table 表名 add unique index 索引名(列)

    3，复合索引

    1. create index 索引名 on 表名(列1,列2...)
    2. alter table 表名 add index 索引名(列1,列2...)

    2，删除索引

    1. drop index 索引名 on 表名

    3，查询索引

    1. show index from 表名;
    2. show index from 表名 \G

    4，重建索引

    1，DROP + CREATE

    删除字段上原有的索引，然后再给其新建索引,如果是主键索引的话，无论删除还是创建主键索引都会重建表，所以删除语句是徒劳的。 ```sql

    重新索引k

    alter table T drop index k; alter table T add index(k);

重建主键索引

alter table T drop primary key; —徒劳的删除语句 alter table T add primary key(id);

主键索引应该这样重建

alter table T engine=InnoDB;

  1. <a name="MMrii"></a>
  2. ### 2，REPAIR TABLE（MyISAM，ARCHIVE，CSV）
  3. ```sql
  4. REPAIR TABLE T

修复后表的索引和数据文件时间会更新
image.png
image.png
image.png

3，OPTIMIZE TABLE（MyISAM，BDB，InnoDB）

执行时会锁表，且不支持 InnoDB 独享表空间的表
关于 InnoDB 表空间是否独享的设置：

  1. SHOW VARIABLES LIKE 'innodb_file_per_table'
  2. SET GLOBAL innodb_file_per_table = 1;
  3. --改为 1 后，表示每个表一个 .ibd 文件
  • 对于 MyISAM 执行如下操作：
    • 1，如果表已经删除或分解了行，则修复表
    • 2，如果卫队索引页进行分类，则分类索引页
    • 3，如果表的统计数据没有更新（且无法通过索引分类实现修复），则进行更新
  • 对于 BDB：OPTIMIZE TABLE 被 ANALYZE TABLE 操作代替
  • 对于 InnoDB：OPTIMIZE TABLE 被 ALTER TABLE 操作代替，将会重建表