Mysql 索引

HyggeBest
58 阅读4分钟

索引的作用就像一本书的目录，方便用户快速查找到目标数据。

按数据结构来分类

  • B+Tree索引
    • 属于Mysql Innodb存储引擎的的默认索引类型。
  • Hash索引
    • 基于哈希表数据结构的索引类型。
  • Full-text索引
    • 用于文本数据进行全文检索。

以innodb引擎作为主
innodb以B+Tree索引(有序、有序、有序)

  • 主键索引
    • 主键索引就是建立在主键字段上的索引，一般在创建表的时候就会执行，并且设置自增AUTO-INC.
    • 一张表只能有一个主键索引，并且唯一。(不允许为空)
CREATE TABLE test(
   `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
   PRIMARY KEY (`id`),
)
  • 唯一索引
    • 这个和主键索引就很像，一张表可以有多个唯一索引，索引列必须唯一，允许为空。
CREATE UNIQUE INDEX unique_b ON test(b);
  • 普通索引
    • 普通索引就是建立在普通字段上的索引，他只是加速查询，并不要求唯一。
CREATE INDEX index_a ON test(a)
  • 前缀索引
    • 字符类型字段的前几个字符建立索引，而不是整个字段上建立索引。一般建立在char、varchar、binary、arbinary
CREATE INDEX index_d ON test(d(lenght))
  • 联合索引
    • 也就是多个字段组合成为一个索引。
CREATE INDEX index_test_a_b_c ON test(a,b,c)

最左匹配原则

这个就是联合索引的创建方式。
这里需要注意，如果使用联合索引需要遵循最左匹配原则，也就是产需那条件的列，必须从联合索引的最左列开始，否则索引无法被有效利用。
联合索引的最左匹配原则，在遇到范围查询的时候(>、<)的时候，就会停止匹配，也就是说范围查询的字段可以用到联合索引，但是在范围查询字段后面的字段无法使用到联合索引。对于>=、<=、between、like前缀匹配的范围查询，并不会停止匹配。

索引下推

mysql在查询时，将一些查询条件从查询阶段推送到索引扫描阶段，这样就可以直接在索引扫描的时候过滤掉不符合条件的数据，而不是从数据表中在去取一次数据之后再进行判断。
适用于联合索引(复合索引)，范围查询。优势就非常明显。

回表

在使用索引进行查询数据时，如果查询字段不在索引中，mysql会通过索引查询到符合条件的位置记录(通常为主键),然后通过位置记录取查询完整的数据。这就是二外的开销。

索引优化

前缀索引优化
  • 使用前缀索引目的是为了减小索引的大小，可以增加一个索引页中存储的索引值，有效提升索引的查询速度。一些较大的字符串字段作为索引的时候，可以使用前缀索引减少索引项的大小。但无法作用于order by、和索引覆盖。
覆盖索引优化

我们在查询的时候，尽量查询的字段都在索引上，避免进行回表操作，造成不必要的开销。如果我们在创建索引的时候，讲需要查询的字段都建立好索引，这个时候数据都会存放在叶子结点上，这个时候就可以完全使用到索引，避免回表，

如果是主键索引，最好使用自增。要知道B+tree结构下的索引都是有序的，如果主键为自增的情况，在进行插入的时候就不需要进行找到位置移动数据等操作，只需要进行追加操作。

索引列最好这事为NOT NULL。因为索引列存在null会导致优化器在选择的时候更加复杂，更加难以优化，因为可以为NULL的列会使索引、索引统计和值比较都更为复杂。如索引统计时会省略为NULL的行。要知道在row格式中，NULL会占用1字节的空间，就会出现占用空间的问题。

索引失效

我们在使用索引的时候，必须防止索引失效的问题，不然索引的存在将毫无意义。
索引失效的情况：

  1. 使用模糊匹配的时候 like %xxx%和like '%x'
  2. 对索引使用函数，比如lenght、表达式计算等
  3. 类型转换，也就是如果age为int，我们进行的是age = "18"的情况下也会导致索引失效。
  4. 不遵循最左匹配原则
  5. where条件中使用OR作为条件
avatar
文章
阅读
粉丝