深入Mysql 索引实现及优化

曾经终败给现在 2022-04-04 13:14 226阅读 0赞

### 索引 ###

Mysql索引文件存储在磁盘上，衡量一个索引实现的数据结构优劣的标准，就是减少索引搜索产生的磁盘I/O次数。

### 实现 ###

> `B+树`是一种 `树型数据结构`，通常用于`数据库`和`操作系统的文件系统`中。B+ 树的特点是能够保持数据`稳定有序`，其插入与修改拥有较稳定的`对数`时间复杂度。B+ 树元素`自底向上`插入，这与二叉树恰好相反。

B+树的特性

1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；  
这一点很好理解，所有结点自上而下搜索插入位置，自底向上插入。

2.不可能在非叶子结点命中；  
3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；

### InnoDB 中的各种索引 ###

InnoDB B+树索引内容按索引类型不同，内容也不同

#### 主键索引 ####

![主键索引][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MDE4Mjgz_size_16_color_FFFFFF_t_70]

可以看到 InnoDB的索引及数据文件，主键索引的B+树叶子结点，就是主键对应的数据和数据地址。

#### 一般索引 ####

比如用户表中的用户姓名 Name 的单列 `普通索引`，索引树的非叶子结点存储的是`主键`的信息，叶子结点存储的是索引字段 Name 数据，和数据地址，所以用一般索引查询，定位了叶子结点之后，如果Sql查询的结果集，包含了除Name的其他字段，那么还需要再根据查询到的主键，去`主键索引`再次查询，获取对应列的其他信息，这个过程，称为`回表`。

#### 覆盖索引 ####

如一般索引中提到的 `回表` 操作 ，比如：

# 创建普通索引
    ALTER TABLE User ADD INDEX age (age)
    # sql
    select  * from  User where age between  3 and 5

这样一条在用户表中查询age 在 3到 5 之间的 sql，假设用户表有 `age 索引`，我们知道的，在索引的叶子结点上，有连续的、密集的、稠密的、有序的链式索引数据，非常适合这种`范围查询`，但是不可避免的，再查询一次索引之后，还需要回表的操作，利用如下的sql语句，可以使用`覆盖索引来`有效的`避免回表`的操作，提升查询效率，缩减查询的时间，比如有一个高频查询 按照`身份证` 来查询 `名称`的查询，就可以建立 `(name,card)` 的联合索引来在这个查询中形成覆盖索引，避免回表：

#创建联合索引
    create index card_c_name on User（name,card)
    #查询
    select  name from User where card = "320122198373838383"

#### 前缀索引 ####

那么，如果此时还是那个 `User` 表，又有一个低频查询，要求是 `按照身份证查询地址`，是否又要建一个 `联合索引` 来满足这个查询呢？

# 新建索引
    create index card_c_addr on User（addr,name)
    # sql
    select  name from User where card = "320122198373838383"

实际上，在 `覆盖索引` 中，建立的 `(name,card)` 也可以配合这种需要根据 `name` 来查询被作为索引使用，这就是 B+树的索引的 `最左前缀匹配`，形如 `(a,b,c)` 的联合索引，可以满足查询 `a`，查询 `a，b`，和 `a，b，c` 的查询，但是如果是查询条件只有 `b` 的话，就无法使用了，所以还需要额外维护一个b的索引，这需要在创建索引的时候，对索引内部的顺序有一个考量。

如果在实际情况中，即需要 `(a,b)` 又需要 `a` 和 `b` 的单独查询索引，那么和时候我们要考量的，就是 a 和 b 哪一个的 `单独空间占用` 较少了，来决定联合索引的顺序。

#### 唯一索引 ####

顾名思义，唯一索引就是从sql-Server层限制索引列的数据唯一性，如果能够确保从业务上保证数据唯一性，即不会重复插入，那么不建议使用唯一索引，使用唯一索引会在插入时带来额外的开销，影响插入/更新语句的执行速度。

# 创建唯一索引
    ALTER TABLE User ADD UNIQUE (column_list)

### 索引误选 ###

有的时候，即使我们根据业务需求等因素，综合考量之后创建了合适的索引，但是sql 引擎仍然会 `"倔强"` 的不使用我们为他准备好的索引，这是为什么呢？

举个例子：  
这里有一张表t，有10w行数据，a,b 列，分别是1到10w。分别创建单独索引 a,b 。

# 查询 表t a 1到1000；b为 5w到10w 的
    explain select * from t    where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

这里即使不看执行计划，我们也能猜想到，优化器应该使用 `a 索引`来进行加速查询，因为，a 的范围更小，而实际上，我们可以看到执行计划显示，优化器选择 `b 索引，查询行数是50128条，查询时间 40ms` 。

如何解决这种 `索引误选` 的情况呢 ？

*  `1 force index` ：缺点就是需要提前指定索引列 ，我们实际开发中，可能更多的是在慢查询日志中进行分析，而不会在开发阶段就特意去指定sql使用的索引，比如上面这个sql语句，难道我们会预先想到优化器会 `弃a择b` ？

# force index 
    explain select * from t force index(a)   where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
    # excute result
    rows 999      1.2ms

可以看到，强行指定了使用的索引之后，优化器使用了a索引，执行结果符合我们的预期，效率也提高了40倍

*  `2 干预sql语义`  
    在默认的sql中，因为排序使用的是b ，所以优化器认为使用b索引，可以减少排序的查询次数。那么我们修改排序的字段为 b,a 试试：

# sql
    explain select * from t   where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
    # execute result
    rows 999 , index a ;

可以看到，这里因为 `limit 1` ，所以无论是按照a还是b排序，查出的结果都不会变 。按照b,a 排序，优化器 就去除了，b索引排序带来的优势，自然选择了a 。但是改了sql的业务逻辑 ，sql的语义，这样并不好。

*  `3 根据 数据特征 诱导索引选择`

# explain 
    explain select * from (select * from t    where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100  ) alias limit 1  ;

发现还是选择b ，rows 5w 。

# explain 
    explain select * from (select * from t    where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1000  ) alias limit 1  ;

扩大到1000 ，rows 999 选择了 a 根据数据特征诱导了优化器，通过把 limit 提升，让优化器意识到 使用b为索引排序，再去找a的代价很大。

*  `4删除索引b`  
    最直接的办法，如果业务确实不需要b索引，有时候说不定可以尝试一下。

### 给字符串建立索引 ###

对于一个字符串字段来说，建立字符串对应的完整索引，比较占用空间，可以通过一些办法来优化字符串索引：

*  `前缀索引`  
    通过建立前缀索引，如：`email(6)`，可以节省创建字符串索引带来的空间占用，但是，只要使用了前缀索引，在查到结果集之后就需要 回表 ，就是查询一次 `主键索引` ，主索引上的这个字段，是否完整的等于查询值，也就是说必须回表，无法使用 `(id,email)` 这张覆盖索引。

> 如何查看区分度  
> select  
> count(distinct nickname) as L, \# 100  
> count(distinct left(nickname,1) ) as L1, \# 10  
> count(distinct left(nickname,2) ) as L2, \# 30  
> count(distinct left(nickname,3) ) as L3, \# 50  
> count(distinct left(nickname,4) ) as L4 from User2 \# 80  
> 区分度分别为 10% ～ 80%，区分度越高，查询效果越好，联合忍受的容错率，来进行合适的前缀索引建立。

*  `建立hash 或 reverse`  
    有些时候，建立前缀索引，可能需要很长很长才能建立到一个合适的索引，比如身份证这种字段，前6位的区分度很低，比如南京建邺区就都是`320105`，上海静安区就是 `310106` 。可以采用 新建一列身份证 hash 或者 用 `mysql 的 reverse 函数` 。
    
    如果采用的是 `建立Hash字段`，那么在存储身份证时，就要创建对应的 `card-hash-value` ，然后在查询时候使用，这种查询需要注意的是，需要查询 `card-hash-value` 和 `原字段` 。
    
    如果采用的是 `mysql 的 reverse 函数` ，那么就可以直接查询身份证的后面字段，这样区分度较高。
    
    值得一提的是，这两个办法都不支持 `范围扫描` 。

合理的索引能够帮助我们加速查询的效率。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MDE4Mjgz_size_16_color_FFFFFF_t_70]: /images/20220404/de3ded36e4f54696acdf3cd2458247c1.png