数据库索引学习

浅浅的花香味﹌ 2022-03-01 14:18 233阅读 0赞

# 数据库索引学习 #

导语：索引对于数据库性能非常的关键,索引的主要作用就是如何快速的找到我们所需要的数据，当表中数据不多时，索引作用不大，因为此时表的数据可以完全缓存到内存之中,全表扫描的速度也很快，但如果成为了一个大表，索引的作用就很重要了，一般有两种主要错误用法：**1. 完全不用(除了主键外)， 2. 每一列都建立了索引**

## MySQL支持的索引类型 ##

1.  Btree索引
    
    B 树索引，大名鼎鼎的 B 树索引，使用的是 B+ 树的结构存储数据(就是平衡树的一种变种，所以每个叶子到根节点的距离都是相同的).
    
    适合范围： 更适合个进行范围查找.
    
    使用情况（不一定是最优）：全值匹配的查询； 匹配最左前缀的查询；匹配列全追查询(Like ‘123%’)；匹配范围值的查询；精确匹配左前列并范围匹配另外一列；只访问索引的查询
    
    一些限制：
    
    1.  如果不是按照索引最左列开始查找，则无法使用索引
    2.  使用索引时不能跳过索引中的列
    3.  Not in 和 <> 操作无法使用索引
    4.  如果查询中有某个列的范围查询，则其右边所有列都无法使用索引
2.  Hash索引
    
     Hash索引时基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引，聊了Hash表的人就很容易理解Hash索引。
    
     特点：1. 索引中只有 hash 码和值 以及指向对应行的指针，所以需要使用这个指针再次去查找对应的行数，所以Hash索引需要**二次查找**.(如果不存指针，而直接存入数据，将会对索引甚至数据库性能产生影响)
    
    1.  无法对查询结构进行排序
    2.  hash索引不支持部分索引查找也不支持范围查找。
    3.  不适合选择性差的列，如：性别，年龄（选择性很差，可选项很少）。
    
    ## 为什么要使用索引 ##
    
     *  索引大大减少了存储引擎需要扫描的数据量
     *  索引可以帮助我么进行排序以避免使用临时表
     *  索引可以把随机I/O变成顺序I/O
    
    ## 索引是不是越多越好？ ##
    
     并不是，索引会占用空间，也会增大写入的成本。因为我们需要修改数据时，同时要修改索引。
    
    此外，索引过多时，查询优化器会很纠结，从而影响了索引的最大优势：加快查询。
    
    ## 索引优化策略 ##
    
    1.  索引列上不能使用表达式或函数
    
        select ... from product 
        WHERE to_days(out_date)-todays(current_date) <= 30
    
    明显使用了函数to\_days(),在索引列上，我们需要将其进行改进:消除索引
    
        select ... from product 
        WHERE out_date <= date_add(current_date, interval 30 day)
    
    1.  前缀索引和索引列的选择。
    
        CREATE INDEX index_name ON table(col_name(n));
    
    索引的选择性时不重复的索引值和表的记录数的比值;
    
    当前缀索引过短，会造成选择性差(可选择性小)，当其过长时会造成索引肿大，不利于加快查询速度。

![在这里插入图片描述][20190322185929293.png]

1.  联合索引的相关问题
    
    Q: 如何选择索引列的顺序？
    
    A: 经常会被使用到的列优先，当然也要考虑选择性问题。此外还有宽度小的列优先，因为会减小索引的大小。
    
    Q: 选择性差到底会带来什么？
    
    A : 选择性差，就是可以供选择的选项不多，比如性别，这时MySQL索引优化器就觉得这么差的索引，还不如直接全表扫描，于是索引就没有起到任何正面作用。
2.  覆盖索引
    
    如果一个索引包含(或覆盖)所有需要查询的字段的值，称为’覆盖索引’。
    
    优点：
    
     1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。  
     2.因为索引是按照列值顺序存储的,所以对于IO密集的范围查找比随机从磁盘读取每一行数据的IO少很多。  
     3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
    
     4.InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。(InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)
    
     5.可以避免对MyISAM表进行系统调用
    
    缺点：
    
     1.mysql查询优化器会在执行查询前判断是否有一个索引能进行覆盖，假设索引覆盖了where条件中的字段，但不是整个查询涉及的字段，mysql5.5和之前的版本也会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。
    
     2.mysql不能在索引中执行LIke操作。mysql能在索引中做最左前缀匹配的like比较，但是如果是通配符开头的like查询，存储引擎就无法做比较匹配。这种情况下mysql只能提取数据行的值而不是索引值来做比较
3.  联合索引
    
    联合索引是指对表上的多个列进行索引。
    
    以下为创建联合索引idx\_a\_b的示例：
    
    ![这里写图片描述][SouthEast]

## 使用索引来优化查询 ##

1.  使用索引扫描来优化排序
    
    熟悉平衡树的人应该知道树可以实现自排序。所以当我们需要将查询结果排序时，就有两种选择：费时费力但是没有限制的排序操作，或者按照索引顺序扫描数据(按照树提供的顺序去遍历数据，树保证有序)，当然要求索引的排序规则与order by 的排序规则完全相同。
2.  使用B树索引模拟Hash索引
3.  使用索引可以优化锁(InnoDB下的MySQL)

*  索引可以减少遍历的行数，减少行锁的数量
 *  索引可以加快处理速度，同时也加快了锁的释放。

## 索引的维护和优化 ##

删除重复和冗余的索引

1.  重复索引

primary key(id), #主键索引
    unique key(id),#唯一索引
    index(id) #单列索引

以上三个都是索引，重复了。

1.  冗余索引
    
        Index(a),index(a,b)#先建立a索引，再建立a,b的联合索引，冗余。

[20190322185929293.png]: /images/20220301/c060748af8424657814355e487b23270.png
[SouthEast]: /images/20220301/80c507eb8875459aa6657c2de0c793d9.png