Mysql索引（一篇就够le）

　　我想很多人对mysql的认知可能就是CRUD（代表创建(Create)、更新(Update)、读取(Retrieve)和删除(Delete)操作），也不敢说自己会用和熟悉mysql，当然我就是其中一个，虽然知道mysql有很多东西，但是一直都没有深入的了解和掌握，最近想着好好的把Mysql原理学习下，这篇就是开胃菜吧，以后的慢慢道来。本篇文章内容主是基于mysql的InnoDB存储引擎。

一、Mysql索引介绍

　　索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
　　MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。
　　创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)，而不是在select的字段中，实际上，索引也是一张“表”，该表保存了主键与索引字段，并指向实体表的记录，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。说白了索引就是用来提高速度的，但是就需要维护索引造成资源的浪费，所以合理的创建索引是必要的。

1.1、索引的类别

先去官网文档看看支持的索引类型，索引的实现方式如下图所示：https://dev.mysql.com/doc/refman/8.0/en/create-index.html（官网）

  由于本文是基于mysql的InnoDB存储引擎，索引我们主要看第一个表格，其他的表格可以自行的观看，都不难，从表格我们可以看出来，InnoDB存储引擎索引只支持BTREE类型的索引，索引的类别有Primary Key，Unique，Key，FULLTEXT和SPATIAL。当然也有其他的分法，按照索引列的数量分为单列索引和组合索引。

  1. Primary Key（聚集索引）：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。
  2. 单列索引：单列索引即一个索引只包含单个列
  3. 组合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合
  4. Unique（唯一索引）：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
  5. Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
  6. FULLTEXT（全文索引）：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
  7. SPATIAL（空间索引）：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL

这里在说一下组合索引的遵循最左前缀原则：

order by使用索引最左前缀
- order by a
- order by a,b
- order by a,b,c
- order by a desc, b desc, c desc 

如果where使用索引的最左前缀定义为常量，则order by能使用索引
- where a=const order by b,c
- where a=const and b=const order by c
- where a=const and b > const order by b,c

不能使用索引进行排序
- order by a , b desc ,c desc  --排序不一致
- where d=const order by b,c   --a丢失
- where a=const order by c     --b丢失
- where a=const order by b,d   --d不是索引的一部分
- where a in(...) order by b,c --a属于范围查询

创建一个简单的表：

CREATE TABLE my_test (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  `user_num` int(11) unique,
  PRIMARY KEY (`id`),
  index(username)
);

show index from my_test;

  明明在建表的时候只创建了一个索引，查询出来的有三个，其实主键，唯一约束列，外键这些都自动会生成索引，至于外键大家可以去尝试下。

上表格中各个列的说明：

table #表名称
non_unique  #如果索引不能包括重复词，为0，如果可以，则为1
key_name  #索引的名称
seq_in_index #索引中的列序号
column_name  #列名称
collation  #列以什么方式存储在索引中，在mysql中，有值'A'（升序）或者NULL（无分类）
cardinality  #索引在唯一值的数据的估值，通过运行analyze table xxx_table;或者 myisamchk -a 可以更新，技术根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没必要是精确的，基数越大，当进行联合所饮食，mysql使用该索引的机会越大。myisam中，该值是准确的，INNODB中该值数据是估算的，存在偏差
sub_part  #如果列只是部分的编入索引 则为被编入索引的字符的数目，如果整列被编入索引，则为NULL
packed  #指示关键词如何被压缩，如果没有被压缩，则为NULL
NULL   #如果列含有NULL，则含有YES，如果没有，则该列为NO
index_type  #用过的索引方法（BTREE,FULLTEXT,HASH,RTREE）
comment  #备注
index_comment  #为索引创建时提供了一个注释属性的索引的任何评论

1.2、索引的创建原则

  1. 索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改的同时，索引也会进行调整和更新
  2. 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
  3. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
  4. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。
  5. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
  6. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
  7. 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
  8. 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，有一个CHAR(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
  9. 利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
  10. 对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果

二、索引的管理和使用

2.1、制造实验数据

这里的实验数据采用的是一个github的一个开源项目，具体的操作流程查看：https://github.com/wuda0112/mysql-tester

数据制造完成后会有四个数据库：

   数据可以根据参数进行生成，很简单，根据步骤来就好了。

2.2、explain使用说明

使用实验数据我们进行explain的查询：explain SELECT store_id , count(1) from foundation_item.item  group by store_id limit 10;

   EXPLAIN语句的基本语法如下：

explain select select_option

select_options是SELECT语句的查询选项，包括FROMWHERE子句等

id: SELECT识别符。这是SELECT的查询序列号,表示查询中执行select子句或操作表的顺序,id相同，执行顺序从上到下,id不同，id值越大执行优先级越高
select_type：表示SELECT语句的类型。它可以是以下几种取值：
    SIMPLE:表示简单查询，其中不包括连接查询和子查询；
    PRIMARY:表示主查询，或者是最外层的查询语句，最外层查询为PRIMARY，也就是最后加载的就是PRIMARY；
    UNION:表示连接查询的第2个或后面的查询语句， 不依赖于外部查询的结果集
    DEPENDENT UNION:连接查询中的第2个或后面的SELECT语句，依赖于外面的查询；
    UNION RESULT:连接查询的结果；
    SUBQUERY:子查询中的第1个SELECT语句；不依赖于外部查询的结果集
    DEPENDENT SUBQUERY:子查询中的第1个SELECT，依赖于外面的查询；
    DERIVED:导出表的SELECT（FROM子句的子查询）,MySQL会递归执行这些子查询，把结果放在临时表里。
    DEPENDENT DERIVED:派生表依赖于另一个表
    MATERIALIZED:物化子查询
    UNCACHEABLE SUBQUERY:子查询，其结果无法缓存，必须针对外部查询的每一行重新进行评估
    UNCACHEABLE UNION:UNION中的第二个或随后的 select 查询，属于不可缓存的子查询
table:表示查询的表
partitions:查询将从中匹配记录的分区。该值适用NULL于未分区的表
type:表示表的连接类型
    system:该表是仅有一行的系统表。这是const连接类型的一个特例
    const: 数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的查询优化中作为常量对待。const表查询速度很快，因为只读取一次,const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。
    eq_ref:对于每个来自前面的表的行组合，从该表中读取一行,可以用于使用=运算符进行比较的索引列 。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式
    ref:对于来自前面的表的任意行组合，将从该表中读取所有匹配的行，ref可以用于使用“＝”或“＜＝＞”操作符的带索引的列。
    fulltext:使用FULLTEXT 索引执行联接
    ref_or_null:这种连接类型类似于ref，但是除了MySQL还会额外搜索包含NULL值的行。此联接类型优化最常用于解析子查询
    index_merge:此联接类型指示使用索引合并优化。在这种情况下，key输出行中的列包含使用的索引列表，并key_len包含使用的索引 的最长键部分的列表
    unique_subquery:类型替换 以下形式的eq_ref某些 IN子查询,unique_subquery 只是一个索引查找函数，它完全替代了子查询以提高效率。
    index_subquery:连接类型类似于 unique_subquery。它代替IN子查询,但只适合子查询中的非唯一索引
    range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。当使用＝、＜＞、＞、＞＝、＜、＜＝、IS NULL、＜＝＞、BETWEEN或者IN操作符用常量比较关键字列时，类型为range
    index:该index联接类型是一样的 ALL，只是索引树被扫描。这发生两种方式：1、如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra列显示为 Using index，2、使用对索引的读取执行全表扫描，以按索引顺序查找数据行。 Uses index没有出现在 Extra列中。
    ALL:对于前面的表的任意行组合进行完整的表扫描    
possible_keys:指出MySQL能使用哪个索引在该表中找到行。若该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看它是否引用某些列或适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高查询的性能。
kye:表示查询实际使用的索引，如果没有选择索引，该列的值是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
key_len：表示MySQL选择的索引字段按字节计算的长度，若键是NULL，则长度为NULL。注意，通过key_len值可以确定MySQL将实际使用一个多列索引中的几个字段
ref：表示使用哪个列或常数与索引一起来查询记录。
rows：显示MySQL在表中进行查询时必须检查的行数。
Extra：表示MySQL在处理查询时的详细信息

更详细说明见官网：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

2.3、创建索引

创建索引的语法（如下都是默认的innodb存储引擎）：https://dev.mysql.com/doc/refman/8.0/en/create-index.html

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}
algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

可以在创建的表的时候建立索引，也可以对创建好的表建立索引。

2.3.1、基于创建表时建立索引

CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。

1、创建普通索引

CREATE TABLE test.`user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  PRIMARY KEY (`id`),
  index idx1(username)
);

show index from test.`user2`;

   2、创建唯一索引

CREATE TABLE test.`user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  `score_num` int(11) NOT NULL UNIQUE,
  PRIMARY KEY (`id`),
  unique index idx1(username)
);

show index from test.`user2`;

   前面两个索引都是通过主键和唯一约束自动创建的

3、创建组合索引

注意：最左前缀原则

CREATE TABLE test.`user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  `score_num` int(11) NOT NULL UNIQUE,
  PRIMARY KEY (`id`),
  index idx1(id,score_num,username)
);

   4、创建全文索引

FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引

CREATE TABLE test.`user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  `score_num` int(11) NOT NULL UNIQUE,
  PRIMARY KEY (`id`),
  fulltext index idx1(username)
);

5、创建空间索引

create table test.test(
    position geometry not null,
    spatial index idx1(position)
);

show index from test.test;

  6、创建前缀索引

CREATE TABLE test.t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

  前缀索引的目的主要是减少字符串作为索引占用的空间，提高查询速度

2.3.2、基于已创建好的表创建索引

在已经存在的表中创建索引，可以使用ALTER TABLE语句或者CREATE INDEX语句创建索引。

1、使用ALTER TABLE语句创建索引

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

2、使用CREATE INDEX创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON  table_name (col_name[length],...) [ASC|DESC]

2.4、删除索引

1、使用ALTER TABLE删除索引

ALTER TABLE table_name DROP INDEX index_name
CREATE TABLE test.t1 (
  id int AUTO_INCREMENT primary key,
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

 alter table test.t1 drop index col1;

alter table test.t1 drop index PRIMARY;  #会报错：添加AUTO_INCREMENT约束字段的唯一索引不能被删除

  2、使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

2.5、聚集索引和二级索引

1、聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

2、二级索引（辅助索引）

对于辅助索引（Secondary Index），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

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

2.6、覆盖索引

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

CREATE TABLE `item` (
  `item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `store_id` bigint(20) unsigned NOT NULL COMMENT '所属店铺ID',
  `type` tinyint(3) unsigned NOT NULL COMMENT '商品类型 . 不同类型的商品, 保存到各自不同的表中. 参考 https://learnwoo.com/woocommerce-different-product-types/',
  `state` tinyint(3) unsigned NOT NULL COMMENT '状态',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `create_user_id` bigint(20) unsigned NOT NULL,
  `last_modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_modify_user_id` bigint(20) unsigned NOT NULL,
  `is_deleted` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`),
  KEY `fk_store_id` (`store_id`)
) ENGINE=InnoDB AUTO_INCREMENT=332604631475558863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='代表所有的物品，之前有把用户ID放进来，表示该物品所属的用户，但是考虑到如果有子账号的情况，物品难道属于这个子账号所属的用户吗？而且记录了创建人用户ID，考虑这两个因素，因此不设置用户ID列'

查看：explain select store_id,create_time from item where store_id > 332604504321036698 ;

  这里使用了全表扫描，没有走索引，然后我们把查询语句改为：explain select store_id from item where store_id > 332604504321036698 ;

 这样就变成了范围查询，走索引，因为索引中包含了需要查询的全部值，所以不需要再查询聚集索引，减少磁盘IO，这样就可以提高速度。 

2.7、Multi-Range Read优化

Multi-Range Read优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。Multi-RangeRead优化可适用于range，ref，eq_ref类型的查询。

Multi-Range Read的好处：

  1. MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
  2. 减少缓冲池中页被替换的次数。
  3. 批量处理对键值的查询操作

MRR的工作方式如下：

  1. 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
  2. 将缓存中的键值根据RowID进行排序。
  3. 根据RowID的排序顺序来访问实际的数据文件。

实验：采用前面2.1节导入的数据：

explain select *  from foundation_item.item where store_id > 332604504249736122 and store_id  < 332604504249736201;

 MySQL5.6版本开始支持Multi-Range Read（MRR）优化，通过参数 optimizer_switch 的标记来控制是否使用MRR，当设置mrr=on时，表示启用MRR优化。mrr_cost_based 表示是否通过 cost base的方式来启用MRR.如果选择mrr=on,mrr_cost_based=off,则表示总是开启MRR优化。

例如设置：set optimizer_switch='mrr=on,mrr_cost_based=on';然后我们继续查看：

explain select *  from foundation_item.item where store_id > 332604504249736122 and store_id  < 332604504249736201;

  如上：MRR优化关闭后没有启动了

2.8、Index Condition Pushdown（ICP）优化

MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能，优化支持range、ref、eq_ref、ref_or_null类型的查询，选择Index Condition Pushdown优化时，可在执行计划的列Extra看到Using index condition提示。

实验：数据来源于2.1生成的数据：

首先我们创建一个组合索引：create index idx_email_id_address_state on foundation_commons.email (state,address,email_id);

explain SELECT * from foundation_commons.email where state =0 and address like '%D%' and email_id >='332604504249734136' ;

  以上实验只是为了显示这样的现象

1、当sql需要全表访问时，ICP的优化策略可用于range, ref, eq_ref, ref_or_null类型的访问数据方法 。
2. 支持InnoDB和MyISAM表。
3. ICP只能用于二级索引，不能用于主索引。
4. 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。

三、索引实现的原理

innodb存储的索引是基于B+树实现的，从1.1节中的表格可以看出，不支持hash的实现方式。首先来了解下B+树的特点；

B+树的特征：

  1. 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
  2. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
  3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

B+树的优势：

  1. 单一节点存储更多的元素，使得查询的IO次数更少。
  2. 所有查询都要查找到叶子节点，查询性能稳定。
  3. 所有叶子节点形成有序链表，便于范围查询。

在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。先来看一个B+树，其高度为2，每页可存放4条记录，扇出（fan out）为5，如下图所示：

  索引的设计思考：

  • 索引是一种存储方式，最相关的硬件就是磁盘，索引磁盘的性能会直接影响到数据库的查询效率
  • 磁盘的性能和读写的顺序有关，普通磁盘顺序读写比随机读写快很多，所以尽量避免随机读写。
  • 数据都是以行为单位一行一行的存储的，每一行都包括了所有的列，多行可以连续存储。
  • 每一行数据中，一般都有一个键，其他的列可以称为值，可以理解为键值对。innodb必须有唯一非空的主键，就是默认的键。
  • 在键值对中，键值可以排序，还可以组合键值。

索引的设计：

  • 磁盘空间会划分为许多个大小相等的块或者页，一个页中可以存储多行数据，这样就可以符合磁盘的顺序读写，这样一次IO就可以读取很多数据到内存，可以减少磁盘IO。
  • 在一个页内，所有的数据可能会经常变动，并且大小也是相对固定的，所以内部通过链表或者数组管理。
  • 每个键值可以排序，所以在一个块内的所有数据也可以是有序的，这样通过二分法查找可以很快的在一个页内找到指定键对应的数据
  • 一个页设计好之后，可以把页作为B+树的节点，通过页来承载数据，通过B+数来组织不同页之间的关系
  • B+树的特点是在内节点存储键来提高搜索的性能，所以很自然的，内节点用来存储数据行的键，叶子节点存储所有数据行，可以很好的提升性能

接下来在结合2.5节的聚集索引和二级索引来说：

表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。如下图所示：

 

 

 

 

　　上图所示的是一个深度为2的B+树，也是我们所称的索引，这里假设页有随机唯一的编号，根页号为20。这里只有一个内节点（根节点），其他的都是叶子节点，也是数据节点，对于内节点来说，存有key和pageno的指针信息，对于叶子节点来说，只存有完整的数据。对于聚集索引，data部分存有除主键外的其他列的组合，如果是二级索引，则这里存放就是这行记录对应主键的组合，用于回表。

　　最左边的MIN为了很好的组织树形结构的指针，和其他的内节点一样，主要用来标记它是最小记录Min，还有就是一个pageno指针指向下层最左边的Min记录，其他节点的Min记录用于判断搜索是否到了边界。每个页都有页头页尾用来管理和标记页面的状态，页面中的数据是如何存储，有没有空闲的空间，以什么样的顺序存储等。

　　上图中所有的叶子节点从左到右都是从小到大的顺序以双向链表的方式存储的，所以当我们需要遍历全部的数据，只需要通过B+树找到最小的位置，然后通过遍历链表则可以查询到所有的数据，还有就是10,16,25这三条记录在内节点和叶子节点均存在，这既是B+数的特点，叶子节点会存有所有的key和值。而内节点只存储了key，不存储其他的数据，只有用来索引。叶子节点除了第一条记录会有上一层重复的存储，其他数据不会有这样的现象，所以浪费的空间也不大，由于每一个页的大小是固定的（16k），在内节点上只存储key，不存储其他数据，一个页就可以存储更多的key，这样检索也能减少磁盘的IO，由于页存储Key增多，这样就可以使得B+树的深度减少，这样也可以减少磁盘的IO，提高查询性能。

例如一个三层的B+数，每一个页能存1000个key，所以第二层就有1000*（1+1000）个key，第三层就可以有1000*1001*1001=1002001000（十亿级别），一个简单的三层B+数据就可以存十亿级别的数据，很强大。

　　上面说到的“回表”其实就是在使用二级索引进行搜索时，因为二级索引只保存了部分列的数据，如果需要获取键值不包括的列的数据时，需要通过二级索引的指针（书签：用于指向聚集索引的指针）来找到聚集索引的全部数据，然后返回需要查询的列的值。如果使用二级索引不能找到需要的值（需要回表），称为非覆盖索引，否则为2.6节介绍的覆盖索引。非覆盖索引需要回表，增加IO，所以性能会差一些。所以可以根据业务需求创建组合索引来避免回表。但是也要权衡索引带来的利是否大于弊。所以在统计行总数的时候可以通过二级索引来统计，这样速度会快一些。大概图形如下：

 

 　　这里附带的说一些不能走索引的情况，但是不多说，因为优化这个东西太多，后期准备写一两篇优化的文章，所以这里只是提一下，走索引的强大；虽然可能创建了很多索引，很多情况都不走索引，比如：like '%query_name%' ，where端使用or条件连接，where端使用函数等，在group by和order by使用的时候要注意组合索引的最左前缀原则。

基于索引的优化可以看：mysql优化篇（基于索引）

 

参考：

《InnoDB存储引擎（第2版）》

《mysql运维内参》

《mysql8入门到精通》

https://blog.csdn.net/qq_26222859/article/details/80631121
https://zhuanlan.zhihu.com/p/29118331

 

posted @ 2020-10-16 22:59  一寸HUI  阅读(52550)  评论(5)  编辑  收藏  举报