Mysql底层索引原理（二）索引的类型和使用

目录

索引分类

索引类型

正确使用索引

1.什么是回表查询

2.什么是索引覆盖

3.索引合并

4.索引添加原则

a).最左前缀匹配原则， 非常重要的原则：

b). = 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

c).尽量选择区分度高的列作为索引

d). 索引列不能参与计算，保持列"干净"。

5.索引无法命中的情况

a). like '%xx'

b). 使用函数

c). or

d).类型不一致

e). 普通索引的不等于不会走索引

f). >、<

g).排序条件为索引，则select字段必须也是索引字段，否则无法命中

---

索引分类

首先，介绍下索引的分类

1.普通索引 index ： 加速查找

2.主键索引 primary key： 加速查找 + 约束(不为空且唯一)

3.唯一索引 unique： 加速查找 + 约束(唯一)

4.联合索引

        -primary key(id, name) ：

        -unique(id, name) ：

        -index(id, name) ：

5.全文索引 fulltext ：用于搜索很长一篇文章的时候，效果最好

6.空间索引 spatial ：了解就好，几乎不用

索引类型

索引的两大类型 hash 与 btree

hash类型的索引：查询单条快，范围查询慢；

btree类型的索引：b+树，层数越多，数据量指数增加(我们目前就是使用btree，因为innodb默认支持)

注意：不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行锁，支持 B-tree、Full-text 等索引，不支持 Hash 索引。

MyISAM 不支持事务，支持表锁，支持 B-tree、Full-text 等索引，不支持 Hash 索引。

Memory 不支持事务，支持表锁，支持 B-tree、Full-text 等索引，不支持 Hash 索引。

NDB 支持事务，支持行锁，支持 Hash 索引，不支持 B-tree、Full-text 等索引。

Archive 不支持事务，支持表锁，支持 B-tree、Full-text 等索引，不支持 Hash 索引。

正确使用索引

在正确使用索引之前，我们先了解下几个常用的词汇

1.什么是回表查询

我们知道innodb采用的是 B+树 聚集索引，主键和数据绑定在一个索引树，主键索引 B+树 的叶子节点存储了数据信息，而普通索引叶子节点存储的是主键值。因此我们可以得知通过普通索引查询时无法直接定位到所有数据，通常情况下，需要扫描两次索引树。

例如：

select * from user where name='test';

这个 sql 就是根据 name 普通索引 查询到对应的 数据主键id 然后再通过 主键id 去遍历主键索引树来获取到整条数据。

这就是 回表查询，先定位主键值，再通过主键值定位行记录，性能比直接查询索引树定位行数据更慢。

2.什么是索引覆盖

只需要在一棵索引树上就可以获取sql所需所有的列数据，不需要回表，较之回表速度要更快。

使用explain输出结果extra字段为Using index时，则表示触发了索引覆盖。

那么如何实现索引覆盖呢？

方法：将要查询的字段建到组合索引中。

3.索引合并
 

合并索引：把多个单列索引合并使用

组合索引能做的事情用合并索引也能做

  乍一看好像索引合并更好了，可以命中更多情况，但实际上如果是 name="a" and email = "b"

那么组合索引的效率还是高于索引合并的，但如果是单条件查询，那么还是索引合并合理。

4.索引添加原则

a).最左前缀匹配原则， 非常重要的原则：

        create index ix_name_email on s1(name,email,)

        - 最左前缀匹配：必须按照从左到右的顺序匹配
        select * from t1 where name = 'n'; #可以
        select * from t1 where name = 'n' and email='e';  #可以
        select * from t1 where emai l= 'e';  #不可以

        mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，        

        比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引， d是用不到索引的，  如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

b). = 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

        或许有人觉得这里可以乱序，a却要求最左原则有些混乱。

        这里之所以可以乱序是因为是SQL执行优化器优化了语句，实际执行的时候是把语句改成了从而适应myqsl索引最左匹配原则，而执行优化器的这些操作，每一个MYSQL版本优化的力度都是不同的，也许你换一个旧一点的版本他就不帮你优化了呢。

c).尽量选择区分度高的列作为索引

        区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。

d). 索引列不能参与计算，保持列"干净"。

        例如：from_unixtime(create_time) = ’2014-05-29’
        就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.索引无法命中的情况

a). like '%xx'

    select * from t1 where email like '%cn';

b). 使用函数

    select * from t1 where reverse(email) = 'eeee';

c). or

    select * from t1 where id = 1 or name = 'snnn';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from t1 where id = 1 or name = 'nnn';
            select * from t1 where id = 1 or name = 'nnn' and email = 'eee'

d).类型不一致

    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from t1 where email = 666;

e). 普通索引的不等于不会走索引

    select * from t1 where email != 'b'
    
    特别的：如果是主键，则还是会走索引
        select * from t1 where id != 123

f). >、<

    select * from t1 where email > 'b' 
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123

g).排序条件为索引，则select字段必须也是索引字段，否则无法命中

    select name from t1 order by email desc;
    当根据索引排序时候，select查询的字段如果不是索引，则不走索引
    select email from t1 order by email desc;
    特别的：如果对主键排序，则还是走索引：
    select * from t1 order by id desc;