索引基础知识

索引基础知识

优点

1. 大大减少了服务器需要扫描的数据量，提高了检索速度
2. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
3. 通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能

索引的使用场景

1. 快速查找匹配WHERE子句的行
2. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
3. 当有表连接的时候，从其他表检索行数据
4. 查找特定索引列的min或max值
5. 排序和分组
6. 在某些情况下，可以优化查询以检索值而无需查询数据行

索引的分类

主键索引

唯一索引

普通索引

前缀索引

全文索引

特点

类似于es的反向索引

局限

只能在 InnoDB 或 MyISAM 的表上使用 ,只能用在CHAR VARCHAR或者TEXT

组合索引

特点

当包含多个列作为索引，最左原则，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要

案例

某张表 a,b,c字段是一个组合索引

语句	索引是否发挥作用
where a= 3	是，只使用了a
where a= 3 and b= 5	是，只使用了a b
where a= 3 and b= 5 and c = 4	是，只使用了a b c
where b= 3 or c = 4	否
where a= 3 and c = 4	是，只使用了a
where a= 3 and b >10 and c = 7	是，只使用了a b
where a= 3 and b like ‘%xx%’ and c = 7	是，只使用了a

哈希索引

特点

1. 基于哈希表的实现，只有精确匹配索引所有列的查询才有效
2. 在mysql中，只有memory的存储引擎显式支持哈希索引
3. 哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快

限制

1. 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
2. 哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
3. 哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
4. 哈希索引支持等值比较查询，也不支持任何范围查询
5. 访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
6. 哈希冲突比较多的话，维护的代价也会很高

案例

当需要存储大量的URL，并且根据URL进行搜索查找，如果使用B+树，存储的内容就会很大

select id from url where url=“”

也可以利用将url使用CRC32做哈希，可以使用以下查询方式：

select id fom url where url=“” and url_crc=CRC32(“”)

此查询性能较高原因是使用体积很小的索引来完成查找

聚簇索引与非聚簇索引

聚簇索引

不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起

优点

1. 可以把相关数据保存在一起
2. 数据访问更快，因为索引和数据保存在同一个树中
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

1. 聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
3. 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题

非聚簇索引

数据文件跟索引文件分开存放

技术名词

回表：

介绍

Mysql回表指的是在InnoDB存储引擎下，二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键B+tree的叶子节点里面，二级索引的B+树叶子节点都是存放的(索引列,主键)

覆盖索引

介绍

如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引，memory引擎不支持覆盖索引，由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用

优势

只读取索引，极大减少数据访问量

因为索引是按照值顺序存储的，所以IO密集型范围查询会比随机从磁盘读取数据要快的多

最左匹配：

介绍

最左匹配原则都是针对联合索引来说的 。 对于联合索引，MySQL会一直向右匹配直到遇到范围查询(＞、＜、between、like)就停止匹配，比如a=1 and b=2 and c＞3 and d=4如果建立(a,b,c,d)顺序的索引，d是用不到索引的 。

索引下推：

介绍

index condition pushdown ,简称ICP，MySQL 5.6推出用于优化查询，默认开启。在不使用ICP的，在使用非主键索引（普通索引又叫二级索引）进行查询时，存储引擎通过索引检索数据，然后返回给MySQL服务器，服务器判断是否符合条件。在使用ICP的情况下，如果存在某些被索引的列的判断条件，MySQL会将这一部分判断条件传递给存储引擎。

案例

假设有一张表（user）,其中有id，name,age,建立联合索引（name,age）

需求：查询姓陈的，年龄大于18的所有用户

select * from user where name like "陈%" and age > 18

根据最左匹配原则，这里使用了联合索引，在5.6之前的版本，会忽略掉age字段，在（name,age）这个索引树上找到符合 name like "陈%"的主键 id，进行N次的回表查询，这个过程回表N次；

5.6之后，由于ICP的存在，innoDB存储引擎没有忽略age这个字段，会在（name,age）这个树上准确匹配出符合age的id集合，回表一次

执行 explain sql 可以看到 Extre字段显示 Using index Condition

优势

ICP 在非主键上的优化，可以有效减少回表次数，大大提升查询效率

索引的数据结构

哈希表

B+tree

索引的匹配方式

全文匹配

匹配最左前缀

匹配列前缀

匹配范围值

精确匹配某一列并范围匹配另一列

只访问索引的查询