单列索引和联合索引

绝地灬酷狼 2021-09-03 07:04 462阅读 0赞

## 一、简介 ##

利用索引中的附加列，可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。

联合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用，如果您知道姓和名，电话簿则更为有用，但如果您只知道名不知道姓，电话簿将没有用处。

所以说创建联合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，联合索引非常有用；仅对后面的任意列执行搜索时，联合索引则没有用处。

## 二、单列索引 ##

多个单列索引在多条件查询时优化器会优先选择最优索引策略，可能只用一个索引，也可能将多个索引全用上。但多个单列索引底层会创建多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引。

## 三、最左前缀原则 ##

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，如果第一个字段是范围查询需要单独建一个索引，在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。这样的话扩展性比较好，比如username经常需要作为查询条件，而age不常使用，则需要把username放在联合索引的第一位置，即最左边。

## 四、同时存在联合索引和单列索引（字段有重复），这个时候查询mysql会怎么用索引呢？ ##

这个涉及到MySQL本身的查询优化器策略，当一个表有多条索引可走时，mysql根据查询语句的成本来选择走哪条索引；

有人说where查询是按照从左到右的顺序，所以筛选力度大的条件尽量放在前面。网上百度过，确实有这种说法，但我亲自测试过，MySQL执行优化器会对其进行优化，当不考虑索引时，where条件顺序对效率没有影响，真正有影响的是是否用到了索引！

## 五、联合索引本质 ##

当创建\*\*（a, b, c）联合索引时，相当于创建了（a）单列索引，（a, b）联合索引以及（a, b, c）联合索引，想要索引生效的话，只能使用者三种组合；当然，我们上面测试过，a, c组合也可以，但实际上只用到了a的索引，c并没有用到。

## 六、索引失效 ##

1、like子查询，%放前面；

2、非空判断 is not null；or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效；

3、or语句（前后都有索引才行，SQL优化要避免写or语句）；

4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

## 七、其它知识点 ##

1、需要加索引的字段，要在where条件中

2、数据量少的字段不需要加索引，因为建索引有一定开销，如果数据量小则没有必要建索引，速度范围慢。

3、联合索引比每个列建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会越慢、另外建立多列索引时，顺序也是需要注意的，应该讲严格的索引放在前面，这样筛选的力度会更大，效率更高。

## 八、MySQL存储引擎简介 ##

1、InnoDB

支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交和回滚。

2、MyISAM

插入速度快，空间和内存使用比较低。如果表主要是用于插入新纪录和读取记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发要求比较低，也可以使用。

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

## 九、索引结构（方法、算法） ##

在mysql中常用两种索引结构(算法)BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。

1、Hash

Hash索引的底层实现是由Hash表来实现的，非常适合以 key-value 的形式查询，也就是单个key 查询，或者说是等值查询。

Hash 索引可以比较方便的提供等值查询的场景,由于是一次定位数据，不像BTree索引需 要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。但是对于范围查询的话，就需要进行全表扫描了。

但为什么我们使用BTree比使用Hash多呢？主要Hash本身由于其特殊性，也带来了很多限制和弊端：

1.  Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询。
2.  联合索引中，Hash索引不能利用部分索引键查询。 对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash索引无法被利用。
3.  Hash索引无法避免数据的排序操作 由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。
4.  Hash索引任何时候都不能避免表扫描 Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。
5.  Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高 对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

2、B+ Tree

B+Tree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，

例如：

select * from user where name like 'jack%'; select * from user where name like 'jac%k%';

如果一通配符开头，或者没有使用常量，则不会使用索引，

例如：

select * from user where name like '%jack'; select * from user where name like simply_name;

3、 B+/-Tree原理

在数据库中，数据量相对较大，多路查找树显然更加适合数据库的应用场景，接下来我们就介绍这两类多路查找树，毕竟作为程序员，心里没点B树怎么能行呢？

B树：B树就是B-树，他有着如下的特性：

1.  B树不同于二叉树，他们的一个节点可以存储多个关键字和多个子树指针，这就是B+树的特点；
2.  一个m阶的B树要求除了根节点以外，所有的非叶子子节点必须要有\[m/2,m\]个子树；
3.  根节点必须只能有两个子树，当然，如果只有根节点一个节点的情况存在；
4.  B树是一个查找二叉树，这点和二叉查找树很像，他都是越靠前的子树越小，并且，同一个节点内，关键字按照大小排序；
5.  B树的一个节点要求子树的个数等于关键字的个数+1；

B+树就是B树的plus版

1.  B+树将所有的查找结果放在叶子节点中，这也就意味着查找B+树，就必须到叶子节点才能返回结果；
2.  B+树每一个节点的关键字个数和子树指针个数相同；
3.  B+树的非叶子节点的每一个关键字对应一个指针，而关键字则是子树的最大，或者最小值；

上一篇：[Oracle数据库访问性能优化][Oracle]

下一篇：[Oracle行链接(Row chaining) 与行迁移(Row Migration)][Oracle_Row chaining_ _Row Migration]

参考：

[https://www.cnblogs.com/nov5026/p/11210078.html][https_www.cnblogs.com_nov5026_p_11210078.html]

[Oracle]: https://blog.csdn.net/guorui_java/article/details/105400738
[Oracle_Row chaining_ _Row Migration]: https://blog.csdn.net/guorui_java/article/details/106571908
[https_www.cnblogs.com_nov5026_p_11210078.html]: https://www.cnblogs.com/nov5026/p/11210078.html