mysql负向查询索引_谈谈MySQL 索引

1、索引是什么

索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为：索引的目的在于提高查询效率。

2、原理

索引的数据结构是B+树，原理图如下

关于B+树的详细介绍，可以参见文章下面的参考。

精简描述：B+树是为了磁盘或其他直接存储辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在叶子节点上，各叶子节点直接由指针进行连接。

B+树中的B不是代表二叉(binary)，而是代表平衡(balance)，因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。

索引的内部也是一张表，索引组织表，索引的构建需要使用到磁盘空间跟内存空间，并不是索引越多越好，太多反而导致性能下降，建议一张表最多不要超过8个索引。

3、索引分类

下面的分类维度没有标准，但一般索引我们会比较关注聚集索引跟非聚集索引。

3.1 聚集索引

聚集索引(clustered index)是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。

主键是一个聚集索引，如果在InnoDB没有定义主键，那么默认生成一个rowId作为聚集索引。

3.2 非聚集索引

非聚集索引(Secondary Index，也称辅助索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

3.3 覆盖索引

从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

使用覆盖索引的好处是辅助索引不包括整行记录中的所有信息，可以减少IO操作。

3.4 唯一索引

不允许具有索引值相同的行，从而禁止重复的索引或键值。

3.5 联合索引

联合索引是指对表上的多个列进行索引。从本质上来说，联合索引也是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

索引匹配的原则是最左匹配原则，MySQL会一直向右匹配直到遇到范围查询(>、

如建立了索引(a,b,c)，那么这些条件语句是可以走到这个索引的，“a=1 and b = 2 and c > 3”、“a=1 and b = 2”，但“b > 1 and d = 10”就没有走到这个索引了，第一个命中的必须是“a”。

4、索引的使用

4.1 选择性：

count(distinct colname)/count(colname) ->1，尽量将选择性高的列放在索引的最前面，但是也存在例外情况。

典型的例子如性别，一般是不建索引的。

4.2 避免Null：

额外存储，特殊处理；

使索引统计和值更加复杂；

SQL执行结果不符合预期

4.3 索引上不要做运算

select id,value from tab where id+10=15 (x)

4.4 多使用联合索引：

将多个字段建立一个组合索引

一个索引可以被多个query利用

减少索引数量，消除重复索引

4.5 避免负向查询

避免使用NOT、!=、<>、!、NOT EXISTS、NOT IN、NOT LIKE等

5、索引优化

5.1 explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

一般重点看type、key、rows。

type：表示表的连接类型。常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右，性能从差到好)

key：表示实际使用的索引

rows：扫描出的行数(估算的行数)，一般这个值越小越好

5.2 Cardinality值

索引是否具有高选择性，一般就可以看Cardinality值。

可以通过“SHOW INDEX FROM table”结果中的列Cardinality来观察，这是一个预估的值，而不是一个准确的值，是通过采样得到的数据。

Cardinality/总条数越接近1，越有必要建索引。

5.3 MySQL查询优化器

MySQL 查询优化器的主要功能是完成SELECT语句的执行，在保证SELECT语句正确执行之外，还有一个重要的功能，就是使用关系代数、启发式规则、代价估值模型等不同种类的技术，提高SELECT语句的执行效率。

这部分是MySQL实现的，可以让我们的SQL写得更“随意”。

参考资料