数据库索引(主要介绍mysql)
首先什么是索引
- 索引(Index)是帮助MySQL高效获取数据的数据结构
- 是对数据库查询进行优化的,既然是数据结构,那通常是那种结构?不同又在哪里?
索引的数据结构
-
通常是Hash索引 和B+Tree索引 ,mysql是InnoDB引擎,默认是B+tree索引(区别是什么?)
-
因为Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描,
-
哈希索引适合等值查询,但是无法进行范围查询
-
哈希索引没办法利用索引完成排序
-
哈希索引不支持多列联合索引的最左匹配规则
-
如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞问题
知道B+ Tree的叶子节点都可以存哪些东西吗?
- 有两张图有助于我们理解
- 叶子节点上可以使整行数据,也可以是一行数据的id(主键)
- 在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引(主键索引效率高,因为不用回表)
联合索引、最左前缀匹配
- 创建索引的时候都会考虑哪些因素呢?一般对于查询概率比较高,经常作为where条件的字段设置索引
- 创建联合索引的时候,需要做联合索引多个字段之间顺序你们是如何选择的呢?在创建多列索引时,我们根据业务需求,where子句中使用最频繁的一列放在最左边,因为MySQL索引查询会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候,如(key1,key2,key3),相当于创建了(key1)、(key1,key2)和(key1,key2,key3)三个索引,这就是 最左匹配原则
MySQL 5.6中,对索引做了哪些优化吗?
- Index Condition Pushdown(索引下推)
MySQL 5.6引入了索引下推优化,默认开启,使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下:
people表中(zipcode,lastname,firstname)构成一个索引
SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;
如果没有使用索引下推技术,则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。
如果使用了索引下推技术,则MYSQL首先会返回符合zipcode='95054’的索引,然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。有了索引下推优化,可以在有like条件查询的情况下,减少回表次数。
查询优化器?
一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。
在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。
这个成本最低的方案就是所谓的执行计划。优化过程大致如下:
1、根据搜索条件,找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价,找出成本最低的那一个