基础优化展示二：索引使用分析

 

 

 

 

主要内容：

        cardinality

        索引介绍

        联合索引

        覆盖索引

        

 

 

 

1、Cardinality

 

1.1、什么是cardinality

        并不是所有在查询条件中出现的列都需要添加索引。对于什么时候添加B+索引，一般的经验是，在访问表中很少一部分行时，使用B+树索引才有意义。对于性别字段，地区字段，类型字段，他们可取值的范围很小，称为低选择性，例如：

        select * from student  where sex='M';

        按性别查询时，可取值的范围一般只有M和F，因此上述SQL语句得到的结果可能是50%的数据（假设男女比例1：1），这时添加B+ 树索引时完全没有必要的。相反如果某个字段的取值范围很广，几乎没有重复，即高可选择性的，那么此时使用B+树索引时最适合的。例如姓名字段，基本上不会出现重复。

 

1.2、获取cardinality

        可以通过show index语句中的Cardinality列来观察。Cardinality值非常关键，表示索引中唯一一只记录数量的预估值。这里需要注意的是，Cardinality是一个预估值，而不是一个准确值。用户也不可能得到一个准确值。

        在实际应用中，Cardinality/n_rows_in_table应尽可能接近1，如果非常小，那么需要考虑是否还要建立这个索引

 

如下实例：

     表node_file_viruse有两个索引，主键id索引和create_time索引。Cardinality分别是482782和78850。

     这里由于表设计的特性，id的个数与记录个数一样，所以可以认为总的n_rows_in_table=482782，退出分别为1和0.163

     注意：

            1、实际添不添加索引，应该看项目需求。这里虽然是0.163依然添加了索引。

            2、下图仅仅列出了一部分列

 

　　

 

 

1.3、InnoDB引擎如何统计Cardinality

        在生产环境中，索引的更新操作可能非常频繁。如果在每次索引引发生更新操作时就对其进行Cardinality的统计，那么将会给数据库带来非常大的负担。另外如果一张表的数据量非常大，那么统计一次Cardinality信息所需要的时间可能非常长，这在生产环境中是不可能接受的。因此，数据库对于Cardinality的统计都是通过采样的方法实现的。

        

        在InnoDB引擎中，Cardinality统计信息更新发生在两个操作中：INSERT和UPDATE。根据前面的叙述，不可能在每次发生INSERT和UPDATE时都去更新Cardinality的信息，这回增加数据库系统的负担，同时对大表进行统计时，在时间上也不允许。因此InnoDB存储引擎对于更新Cardinality信息的策略为：

        1、表中1/6数据已经发生变化。

        2、stat_modified_counter >  2 000 000 000

        

 

      在InnoDB引擎中，Cardinality信息的统计和更新操作是通过采样方法实现的。InnoDB引擎只对8个叶节点进行采样。采样过程为：

        1、取得B+树索引中叶子节点的数量，即为A

        2、随机取B+树索引中的8个叶节点。统计每个页不同记录的个数，即为P1， P2， 。。。 P8

        3、根据采样信息给出Cardinality的预估值： Cardinality = (P1 + P2 + ....) * A /8 

    通过以上说明直到， 在InnoDB引擎中，Cardinality信息是通过8个叶节点预估到的，不是一个精确值。再者，每次对于Cardinality值都是通过随机读取8个叶子节点得到的，说明每次得到的Cardinality值可能是不同的。

  

 

 

2、索引介绍

这段转载自：https://www.cnblogs.com/kkbill/p/11354685.html

 

            索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点，索引就是为了提高数据查询的效率。索引可以包含一个或多个列的值，如果索引包含多个列的值，则列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。

 

2.1、索引类型

用于提高读写效率的数据结构有很多，这里先介绍常见的3种，分别是：

    哈希表

    有序数组

    搜索树（重点）（B+树）

 

2.2、 哈希索引

        哈希表是一种以键-值（key-value）的方式存储数据的结构，我们只要输入待查找的值（即key），就可以找到其对应的值（即Value）。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置，即idx = Hash(key)。如果出现哈希冲突，就采用拉链法解决。

 

        因为哈希表中存放的数据不是有序的，因此不适合做区间查询，适用于只有等值查询的场景。

 

2.3、 有序数组

        有序数组在等值查询和范围查询场景中的性能都非常优秀。用二分法就可以快速找到（时间复杂度为O(logN)）。但是如果要往中间插入一条数据，则必须挪动后面的所有记录，成本较高。因此，有序数组只适用于静态存储引擎，即数据表一旦建立后不再会修改。

 

2.4、B+索引

        首先，得先好好理解什么是B+树！看单独介绍B树、B+树的文章，基于篇幅不在此赘述。简单的说，是因为使用B+树存储数据可以让一个查询尽量少的读磁盘，从而减少查询时磁盘I/O的时间。

 

        在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

 

假设，有这样一张表：该表主键为ID，且还有一个字段为k，并在k上有索引。

CREATE TABLE T(

    id int primary key,

    k int not null,

    index (k)

)engine=InnoDB;

 

表中有5条记录，分别为R1~R5，(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。则在InnoDB中的索引组织结构是这样的：

　　

 

根据叶子结点的内容，索引类型分为主键索引和非主键索引。

    主键索引的叶子结点存的是整条记录，主键索引也被称为聚簇索引（clustered index）。

    非主键索引的叶子结点存的是主键的值，非主键索引也被称为二级索引（secondary index）/普通索引/辅助索引。

    上图，左边的是主键索引，右边的是非主键索引

 

那么，基于主键索引和非主键索引的查询有什么区别？

    如果语句是 select * from T where ID=500，即主键查询，则只需要搜索ID这棵树。

    如果语句是 select * from T where k=5，即非主键索引查询，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。从非主键索引回到主键索引的过程称为回表。

 

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。而从存储空间的角度讲，因为非主键索引树的叶结点存放的是主键的值，那么，应该考虑让主键的字段尽量短，这样非主键索引的叶子结点就越小，非主键索引占用的空间也就越小。一般情况下，建议创建一个自增主键，这样非主键索引占用的空间最小

 

上面的说法也并非绝对的，实际也要看需求如何。如需要统计表中数据个数时，可以使用非主键索引，如SELECT count(*) from T where k> 10  and k< 100，这时由于非主键索引上记录的数据少（B+树的叶子节点上存储的是主键值，而非一条完整记录），在相同内存时，可以加载的记录更多，当然要使用非主键索引。再比如查询的数据内内容，在该非主键索引上已经存在，如：SELECT k from T where k> 10  and k< 100 ；SELECT k， id  from T where k> 10  and k< 100；等。

 

 

 

 

3、联合索引

 

联合索引是指对表上的多个列进行索引。

 

3.1、联合索引的一般性使用说明

 

创建一个实例表：

CREATE TABLE T(

    a INT,

    b INT, 

    PRIMARY  KEY (a),

    KEY idx_a_b  (a, b)

)engine=InnoDB;

 

分析联合索引的使用。从本质上来说，联合索引还是一颗B+树，不同的是联合索引的键值数量不是1，而是大于等于2。以下使用两个整数型列组成的联合索引，假定两个键值的名称分别为a,b，如下图所示：

　　

    从图中可知，和单个键值的B+树没什么不同，键值都是有序排列的，通过叶子节点可以逻辑上顺序的读取所有数据，这里是：（1，1），（1，2），（2，1），（2，4），（2，4），（3，1），（3，2）。数据按照(a,b)的顺序进行了存放。

 

    因此，对于查询SELECT * FROM TABLE WHERE a=xxx and b=xxx，显然是可以使用（a , b）这个联合索引的。对于单个的a列查询SELECT * FROM TABLE  WHERE a =xxx也是可以使用(a,b)这个联合索引的。但是对于SELECT * FROM TABLE  WHERE b =xxx，不可以使用这颗B+树索引。可以看到叶子节点上的b值为1，2，1，4，1，2显然不是顺序的。因此对于b列的查询不能使用(a,b)的索引。

 

 

3.2、联合索引的排序性与使用

 

    联合索引的另一个好处是可以对第二个键值进行排序。

 

    假设有下面这样一张表，有这样一个需求，我们需要查询某个用户的购物情况，并按照时间进行排序，取出某用户近几次的购物情况。    

CREATE TABLE buylog(

    userid int not null,

    buy_date DATE

)ENGINE=InnoDB;

 

// 插入数据

insert into buylog values(1, '2019-08-13');

insert into buylog values(2, '2019-08-14');

insert into buylog values(3, '2019-08-15');

insert into buylog values(1, '2019-08-11');

insert into buylog values(3, '2019-08-10');

insert into buylog values(1, '2019-08-12');

 

// 添加索引

alter table buylog add index(userid);

alter table buylog add index(userid, buy_date);

// （或用key关键字也一样的）

alter table buylog add key(userid);

alter table buylog add key(userid, buy_date);

 

 

如果只对于userid进行查询，如：

        select * from buylog where userid=2;

通过explain查看该语句的执行情况，如下，（explain的用法，简单了解）

　　

 

可以看到，possible_keys在这里有两个索引可供使用，分别是userid索引和(userid,buy_date)联合索引。优化器最终选择的索引（即key）是userid，因为该索引的叶子节点只包含单个键值，所以理论上一页能存放的记录会更多（意味着可以减少查询的次数）。

接着假定要查询userid为1的最近两次的购买记录，如：

        select * from buylog where userid=1 order by buy_date desc limit 2;

同样的，我们看一下它的执行过程是怎样的，如下：

　　

 

可以看到，这一次查询优化器选择的索引是userid_2（也就是(userid, buy_date)联合索引）。为什么呢？因为在这个联合索引中，记录已经分别根据userid和buy_date排好序了，利用这个索引则可以直接取出相应的数据而无需再对buy_date额外做一次排序操作了。如果强制使用userid索引，则它的执行计划如下：

　　

 

从Extra字段可以看出，该语句的执行需要使用fliesort，也就是需要一次额外的排序操作才能完成查询。显然，这个排序就是对buy_date字段的排序，因为这里仅使用了userid索引，该索引未对buy_date进行排序。

 

 

总结：

 

联合索引(a, b)是根据a, b进行排序（先根据a排序，如果a相同则根据b排序）。因此，下列语句可以直接使用联合索引得到结果（事实上，也就是用到了最左前缀原则）：

select ... from xxx where a=xxx;

select ... from xxx where a=xxx order by b;

 

而下列语句则不能使用联合查询：

select ... from xxx where b=xxx;

 

对于联合索引(a, b, c)，下列语句同样可以直接通过联合索引得到结果：

select ... from xxx where a=xxx order by b;

select ... from xxx where a=xxx and b=xxx order by c;

 

而下列语句则不行，需要执行一次filesort排序操作。

select ... from xxx where a=xxx order by c;

 

 

3.3、联合索引的最左前缀原则

 

        所谓最左前缀原则，是指联合索引的使用原则。

 

        对于有很多字段的一张表，查询的方式是多样的，难道要为了每一种可能的查询都定义索引吗？这样岂不是很浪费空间，毕竟建索引也是需要一些空间的。事实上，B+ 树这种索引结构，可以利用索引的“最左前缀”原则来定位记录，避免重复定义索引。

 

以下面的例子进行说明什么是“最左前缀原则”。

　　

 

 

假设建立了一个联合索引(name,age)，可以看到，索引项是按照索引定义里面出现的字段顺序排序的，先根据名字排序，名字相同的就根据年龄排序。

 

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

 

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

因此，基于最左前缀原则，我们在定义联合索引的时候，考虑如何安排索引内的字段顺序就至关重要了！评估的标准就是索引的复用能力，比如，当已经有了(a,b)字段的索引，一般就不需要再单独在a上建立索引了。

 

 

 

4、覆盖索引的使用

 

        覆盖索引，也就是上面提到的非主键索引。使用覆盖索引的好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引（主键索引），因此可以大大减少IO操作。如果查询要查询的信息都在覆盖索引上，则可以最大的加速查询效率，否则就需要根据覆盖索引查询得到的主键值，继续从主键索引上查询完整的记录。

 

        对于InnoDB存储引擎的辅助索引而言，由于其中包含了主键信息，因此其叶子节点存放的数据为（priamy key1, primary key2。。。 key1, kye2。。。），其中primary keyn表示主键索引的列值，key表示非主键索引的列值

        针对这种存储特性，一般可以使用以下查询语句：

        SELECT  key2 FROM  table where  key1=xxx

        SELECT  key1, primary key 1 from table WHERE key1=xxx;

        SELECT  count(*)  FROM  table;

 

以下是一个覆盖索引的使用举例：

        覆盖索引使用explain后的关键特性，已经给标注出来

　　

        

这里另外说明以下：

        一般而言，表中的id是主键索引，上面举例中的id也确实是主键，只是后来又在该表中建立了一个覆盖索引。具体请参考下面

　　

说明：

        主键默认有主键索引的。

        在主键上单独建立唯一索引也是可以的，具体根据实际需要使用，一般不推荐在主键上单独再建立索引。

        上面的语句中，由于覆盖索效率高，所以优化器自动选择使用覆盖索引实现。（由于覆盖索引的存储的数据少，可以减少IO使用次数）