Mysql索引

1.索引概念

什么是索引:
  索引是一种数据结构能够帮助我们快速的检索数据库中的数据

索引具体使用那种数据结构:
  常见的Mysql主要有两种:
    Hash索引和B+Tree索引,InnDB引擎默认使用B+数索引

为什么使用B+树索引?和Hash索引比较有什么优缺点?
    1.因为Hash索引底层是哈希表,哈希表是一种key-value存储的数据结构,所以多个数据在存储关系上是完全没有
      任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,需要全表查询,所以哈希只适用于等值查询的场景
      而B+数是一种多路平衡查询树,它的节点是天然有序的(左子节点小于父节点,父节点小于右子节点),所以不需要全表查询
总结:
  哈希索引适合等值查询,但是无法进行范围查询
  哈希索引没办法利用索引完成排序
  哈希素银不支持多联合索引的最左匹配规则
  如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞的问题

2.聚簇索引,覆盖索引

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';的时候,就可以通过覆盖索引查询,无需回表

3.联合索引,最左前缀匹配

在创建索引时根据业务需求 where子句中适用最频繁的一列放在最左边,因为MySQL索引查询会遵循
最左前缀匹配的原则 ,即最左优先,在检索数据时从联合索引的最左边开始匹配。所以我们
在创建一个联合索引的时候比如(key1,key2,key3)相当于创建了(key1) (key1,key2) (key1,key2,key3)三个
索引,这就是最左侧匹配原则

4.索引下推,查询优化

  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.对比各种执行方案的代价,找出成本最低的哪一个

 

上一篇:Redis入门


下一篇:Redis 并发竞争key问题如何解决