当我们面对大量的数据查询时,为了提高查询效率,我们在数据库中总要使用到索引。那么索引究竟是怎么样的东西呢?
索引本质
索引其实就是一种数据结构,他将数据库中的数据以一定的数据结构算法进行存储,能够帮助我们快速的检索数据库中的数据。
何时使用索引
1.当某些列的查询概率比较高或经常作为where条件的列。
2.在作为主键的列上。
3.在经常用在表连接的列上。
在这些时候,我们就要考虑使用此列作为一个索引
索引类型
1.在Mysql数据库的索引中,主要包括Hash索引和B+ Tree索引,我们常用的InnoDB引擎,默认的是B+树。
2.Hash索引和B+ Tree索引的区别:
1)Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询(大于、小于的范围查询)是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描。
2)哈希索引没办法利用索引完成排序(例如order by);
3)哈希索引不支持多列联合索引的最左匹配规则。(多列索引(联合索引)有最左前缀的原则:即最左优先。
如果我们建立了一个2列的联合索引(col1,col2),实际上已经建立了两个联合索引(col1)、(col1,col2);
如果有一个3列索引(col1,col2,col3),实际上已经建立了三个联合索引(col1)、(col1,col2)、(col1,col2,col3),查询时,会先查最左的第一个索引col1。)
4)如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题。
3.在B+ Tree索引下还分为了聚簇索引和非聚簇索引。在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引。聚簇索引查询会更快,因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值,查到主键的值以后,还需要再通过主键的值再回表进行一次查询。
4.深入探索问题——什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢?
答:当查询优化器未选择到该索引的执行计划。(查询优化器: 一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。 在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下: 1、根据搜索条件,找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价,找出成本最低的那一个)
索引的缺点
1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
索引是否生效
1.在数据库新建一个查询,使用 explain 关键字进行查询。
例如 explain select * from user WHERE company_id=410
此时在查询结果中就会发现
possible_keys列下面的值就是你创建的索引名称,如果为空,则说明你的索引未被使用到。