2. 索引

mysql

索引

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引、非聚簇索引。
按「索引特性」分类：主键索引、唯一索引、辅助索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

一、数据结构

二、物理存储

聚簇索引：索引和数据存储在一起。
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。

innodb中，基于聚簇索引（主键索引）之创建的索引称之为辅助索引。辅助索引访问数据基本都需要二次查找（回表），非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

三、索引特性

3.1 主键索引

主键索引就是建立在主键字段上的索引，通常建议一张表最多只有一个主键索引，索引列的值不允许有空值。

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

3.2 唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

3.3 辅助索引

辅助索引就是建立在非主键字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

3.4 前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

四、字段个数

4.1 联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。

CREATE INDEX index_product_no_name ON product(product_no, name);

最左前缀

五、索引区分度

索引区分度=distinct(col)/ count(*) 当索引区分度很小，不适合建立索引或不适合排在联合索引列的靠前的位置

六、优化索引的方法

前缀索引优化；
覆盖索引优化；
索引最好设置为 NOT NULL；
防止索引失效；

6.1 前缀索引优化

使用前缀索引，优化字符串查询，目的是减小索引大小，以及加速索引查询速度。不过，前缀索引有一定的局限性，例如：

order by 就无法使用前缀索引；
无法把前缀索引用作覆盖索引；

最好是使用数值类型替代字符串常量类型

6.2 覆盖索引优化

覆盖索引是指 Select的所有字段，在辅助索引中都存在，MySQL就不会再去回表到主键索引操作了。

6.3 索引最好设置为 NOT NULL

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表，如下图的紫色部分：

6.4 避免索引失效

以下操作会造成索引失效：

使用！=、<>、is null、is not null
使用 like 以通配符开头
字符串不加单引号
对索引列进行运算。需要建立函数索引
使用 or
违背最左匹配原则（复合索引，缺少最左列）

七、回表

众所周知使用innodb的表可以存在多个二级索引，但只能有一个主键索引。这主键索引满足下列要求：

当存在主键，索引值就是主键
当主键不存在，但存在唯一索引，那么索引值就是该唯一索引
当既不存在主键，也不存在唯一索引，那么就自动生成rowid所谓索引值
主键索引索引值存储在b+树的叶子节点上，同时他还带着对应的行数据。
而二级索引索引值带的数据则是主键索引的索引值。

假设你的主键索引值是自增的id，你还设了一个二级索引，并通过sql语句使用该二级索引查询，此时MySQL会在该索引上查询得到主键id，然后再通过主键id去查询，这就叫做回表。

八、索引下推

回表有什么坏处呢？回表会增加查询的步骤、降低查询效率，试想你创建了一个（name,age）的组合索引。当你通过name不等值匹配和age等值匹配查询时，这时索引中的age会不起作用

组合索引满足最左匹配，但是遇到非等值判断时匹配停止。 name like '陈%' 不是等值匹配，所以 age = 20 这里就用不上 (name,age) 组合索引了。如果没有索引下推，组合索引只能用到 name，age 的判定就需要回表才能做了。5.6之后有了索引下推，age = 20 可以直接在组合索引里判定

当使用索引下推后就不存在这种情况了。MySQL是默认开启索引下推的。手动开关代码如下：

set optimizer_switch='index_condition_pushdown=off'
set optimizer_switch='index_condition_pushdown=on'

使用索引下推后会优先在索引上进行查询过滤，使用explain查询会发现：

当Extra的值为Using index condition; 说明你查询的字段全部在索引上完成了过滤操作，回表时会根据主键直接得到对应的行数据。当Extra的值为Using where时，可能你的查询字段没有设置索引，这时会直接在主键索引上查询，效率低下。当Extra的值为Using index condition; Using where时，说明你查询的一部分部分字段有索引，另一部分没有索引，当有索引的字段完成查询时回表，并不会立马返回数据，而是还要再全表查询那些没有索引的字段。

九、自增 ID 作为主键的好处

主键索引有序，不用担心因为主键随机的特性，导致B+树频繁的上溢而产生的性能问题
不用担心主键重复问题

十、逻辑删除的好处

不用担心因为数据物理删除，导致索引变动引发的B+树上溢下溢而产生的性能问题
数据恢复方便

如果真有必要清理已经逻辑删除的数据，可以选择在业务低谷期清理数据，尽可能减低索引变动对业务体验影响

十一、explain

使用explain可以查看sql具体执行计划

执行计划会返回很多信息，主要看key（命中的索引）、possible_keys（可以命中的索引）、extra、type（找到所需行的方式）

详解：TODO

2. 索引

# 一、数据结构

# 二、物理存储

# 三、索引特性

# 3.1 主键索引

# 3.2 唯一索引

# 3.3 辅助索引

# 3.4 前缀索引

# 四、字段个数

# 4.1 联合索引

# 五、索引区分度

# 六、优化索引的方法

# 6.1 前缀索引优化

# 6.2 覆盖索引优化

# 6.3 索引最好设置为 NOT NULL

# 6.4 避免索引失效

# 七、回表

# 八、索引下推

# 九、自增 ID 作为主键的好处

# 十、逻辑删除的好处

# 十一、explain