一、索引概念
什么是索引:
索引是一种数据结构能够帮助我们快速的检索数据库中的数据
索引具体使用那种数据结构:
常见的Mysql主要有两种:
Hash索引和B+Tree索引,InnDB引擎默认使用B+数索引
为什么使用B+树索引?和Hash索引比较有什么优缺点?
1.因为Hash索引底层是哈希表,哈希表是一种key-value存储的数据结构,所以多个数据在存储关系上是完全没有
任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,需要全表查询,所以哈希只适用于等值查询的场景
而B+数是一种多路平衡查询树,它的节点是天然有序的(左子节点小于父节点,父节点小于右子节点),所以不需要全表查询
总结:
哈希索引适合等值查询,但是无法进行范围查询
哈希索引没办法利用索引完成排序
哈希素银不支持多联合索引的最左匹配规则
如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞的问题
二、聚簇索引,覆盖索引
B+Tree的叶子节点都可以存那些东西?
InnoDB的B+Tree可能存储的是整行的数据,也可能是主键的值
聚簇索引和非聚簇索引的区别?
在innoDB里索引B+Tree的叶子点存储了整行数据的是主键索引,也成为聚簇索。
而索引B+Tree的叶子点存储了主键的值的是非聚簇索引的值是非主键值,也被称指非聚簇索引
覆盖索引:
指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取,也可以称之为实现了索引覆盖
当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免
了查到的索引后返回表操作,减少I/O提高效率
比如 表student中有一个普通索引 inx_key1_key2(key1,key2)。
当我们通过SQL语句:select key2 form student where key1=‘keytest’;的时候,就可以通过覆盖索引查询,无需回表
三、联合索引,最左前缀匹配
在创建索引时根据业务需求 where子句中适用最频繁的一列放在最左边,因为MySQL索引查询会遵循 最左前缀匹配的原则
,即最左优先,在检索数据时从联合索引的最左边开始匹配。所以我们
在创建一个联合索引的时候比如(key1,key2,key3)相当于创建了(key1) (key1,key2)
(key1,key2,key3)三个 索引,这就是最左侧匹配原则
四、索引下推,查询优化
MySQL5.6中对索引做的优化
Index Condition Pushdown Optimization(索引下推)
MySQL5.6引入了索引下推优化,默认开启,适用Set optimizer_switch = ‘indx_condition_pushdown=off’;可以将其关闭。
官方例子:
people表中(zipcode,lastname,firstname)构成一个索引
Select * from people where zipcode = ‘95054’ AND lastname LIKE ‘%etunia%’ AND address LIKE ‘%Main Street%’;
如果没有使用索引下推优化技术,则MySQL会通过zipcode =‘95054’ 从存储引擎中查询对应的数据,返回到Mysql服务端
,然后MySQL服务端基于lastname LIKE ‘%etrunia%’ 和 address LIKE ‘%Main Stree%’ 来判断数据是否符合条件
如果使用了索引下推优化技术,则MySQL会首先返回符合zipcode = ‘95054’ 的索引,然后根据lastname LIKE ‘%etrunia%’ 和address LIKE ‘%Main Stree%’
来判断索引是否符合条件,如果符合 则根据索引来定位对应的数据 如果不符合 则直接reject掉,有了索引下推优化技术,可以在有like条件查询的情况下,减少回表次数
查询优化器
一条SQL语句的查询,可以有不同的执行方案,至于最终那种方案,需要通过优化器进行选择,选择执行成本最低的方案
在条SQL单表查询语句真正执行之前。MySQL的查询优化器会找出执行该语句所有可能使用的方案,对此之后找出成本最低的方案
优化过程:
1.根据搜索条,找出所有可能使用的索引
2.计算全表扫描的代价
3.计算使用不同索引执行的查询的代价
4.对比各种执行方案的代价,找出成本最低的哪一个