索引基础知识
优点
- 大大减少了服务器需要扫描的数据量,提高了检索速度
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
- 通过使用索引,可以在查询的过程中,使用查询优化器,提高系统的性能
索引的使用场景
- 快速查找匹配WHERE子句的行
- 如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查找行
- 当有表连接的时候,从其他表检索行数据
- 查找特定索引列的min或max值
- 排序和分组
- 在某些情况下,可以优化查询以检索值而无需查询数据行
索引的分类
主键索引
唯一索引
普通索引
前缀索引
全文索引
特点
类似于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 |
哈希索引
特点
- 基于哈希表的实现,只有精确匹配索引所有列的查询才有效
- 在mysql中,只有memory的存储引擎显式支持哈希索引
- 哈希索引自身只需存储对应的hash值,所以索引的结构十分紧凑,这让哈希索引查找的速度非常快
限制
- 哈希索引只包含哈希值和行指针,而不存储字段值,索引不能使用索引中的值来避免读取行
- 哈希索引数据并不是按照索引值顺序存储的,所以无法进行排序
- 哈希索引不支持部分列匹配查找,哈希索引是使用索引列的全部内容来计算哈希值
- 哈希索引支持等值比较查询,也不支持任何范围查询
- 访问哈希索引的数据非常快,除非有很多哈希冲突,当出现哈希冲突的时候,存储引擎必须遍历链表中的所有行指针,逐行进行比较,直到找到所有符合条件的行
- 哈希冲突比较多的话,维护的代价也会很高
案例
当需要存储大量的URL,并且根据URL进行搜索查找,如果使用B+树,存储的内容就会很大
select id from url where url=“”
也可以利用将url使用CRC32做哈希,可以使用以下查询方式:
select id fom url where url=“” and url_crc=CRC32(“”)
此查询性能较高原因是使用体积很小的索引来完成查找
聚簇索引与非聚簇索引
聚簇索引
不是单独的索引类型,而是一种数据存储方式,指的是数据行跟相邻的键值紧凑的存储在一起
优点
- 可以把相关数据保存在一起
- 数据访问更快,因为索引和数据保存在同一个树中
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点
- 聚簇数据最大限度地提高了IO密集型应用的性能,如果数据全部在内存,那么聚簇索引就没有什么优势
- 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式
- 更新聚簇索引列的代价很高,因为会强制将每个被更新的行移动到新的位置
- 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题
非聚簇索引
数据文件跟索引文件分开存放
技术名词
回表:
介绍
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
索引的匹配方式
全文匹配
匹配最左前缀
匹配列前缀
匹配范围值
精确匹配某一列并范围匹配另一列
只访问索引的查询