MySql学习（三）索引

2020-04-01 15:16 251人阅读评论(0)

索引是什么

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目
录，能加快数据库的查询速度。
索引的优势和劣势

优势

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。 – 检索
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。 --排序
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一
些。（可以参考后续讲的B+TREE的结构，帮助理解）
如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
如果where条件列符合索引的最左前缀原则的话，在存储引擎层会有一层index filter处理。(索引下推 ICP)
如果检索的字段都包含在索引当中可以减少回表查询从而提高效率（索引覆盖）

劣势

索引会占据磁盘空间。
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

索引原理分析

索引的存储结构

索引是在存储引擎中实现的，也就是说不同的存储引擎，索引会使用不同的数据结构
MyISAM和InnoDB存储引擎：只支持B+ TREE索引，也就是说默认使用B+TREE，不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引

B-TREE和B+TREE

B树是为了磁盘或其它存储设备而设计的一种多叉平衡查找树。
B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。
如果是三层树结构—支撑的数据可以达到20G，如果是四层树结构—支撑的数据可以达到几十T B和B+的区别
B树和B+树的最大区别在于非叶子节点是否存储数据的问题。
B树是非叶子节点和叶子节点都会存储数据。
B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向
的，也就是有顺序的

注：所以上文的留下的问题，因为B+树叶子节点是有顺序的且类似于链表的结构，所以会大大的缩短排序的时间，而且是只有包含在索引中的列的排序才能享受到这个待遇。

非聚集索引（MyISAM）

MyISAM索引(.myi)和数据(,myd)是分开存放的，MyISAM的B+树种叶子节点存放的是数据在数据文件（.myd）的指针位置

在物理结构篇中讲述了MyISAM中的存储结构和上述结构也有一个对照与论证

.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
.myd文件：主要用来存储表数据信息。
.myi文件：主要用来存储表数据文件中任何索引的数据树。

其中test表使用的是MyISAM引擎，结构如上图所示。

主键索引

辅助索引（非主键索引）

MyISAM的辅助索引与主键索引是相同的。存放的都是索引值与数据地址的键值对。

聚集索引（InnoDB）

主键索引

辅助索引

InnoDB索引数据是存放在一起的，InnoDB的B+树种叶子节点中主键索引存放的是完整的数据，而辅助索引中存放的是索引值与主键值。

在物理结构篇中讲述了InnoDB中的存储结构和上述结构也有一个对照与论证

1 .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息.
2. .ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。

其中user表使用的是InnoDB引擎，结构如上图所示。

一些名词解释

回表

结合InnoDB的索引数据结构，由于非主键查询，第一次仅能得到索引的值以及对应主键的值，需要回到主键的索引树种查询对应的行记录，这种行为就叫做回表。

索引覆盖

索引覆盖是针对回表来说的，检索所需的项目都包含在索引中，那么就不需要回表查询，这样的效率相对于回表查询要高。是否用到了索引覆盖可以根据查询计划中 extra的值来判断。
根据上面的InnoDB索引文件结构，索引中存放的是主键值，或者辅助索引的值，如果你查询只需要键值，那么直接就可以得到就不需要再根据索引中存放的主键再去检索主键索引了。
举个例子：

CREATE TABLE `tuser`  (
  `id` int(11) NOT NULL,
  `NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name_age`(`NAME`, `age`) USING BTREE,
  INDEX `idx_sex`(`sex`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

表结构如上述所示
执行查询，结果如下（不同数据库版本可能有所不同，我用的版本是5.7.24）

这里就是用到了索引覆盖，如果我把select项目改成*，或者不符合最做前缀原则那么，就需要回表进行查询了

重点： 索引覆盖是针对一个索引来说的，比如tuser表，单独使用主键索引，或者辅助索引用到了索引覆盖都是可以的，但是要根据主键值查询name则不行。

可以看到这两个查询都用到了所以覆盖，一个是索引idx_name_age的索引覆盖，一个是主键的索引覆盖。
但是我如果根据主键值查询name则无法进行索引覆盖。

再注意： 索引覆盖不要理解有误区，只要是能从当前索引树上取得所有的信息不需要再去查询主键索引树就是索引覆盖。就如下图所示，idx_name_age树中节点中存放的键值对为（name+age : id）所以检索id即使不符合idx_name_age的最左前缀原则，也是属于索引覆盖的范畴。

索引下推

在MySql5.6之前检索分为两个步骤

根据最左前缀原则在存储引擎层确定index key的检索范围，然后将回表读取这些数据
返回给SQL SERVER层再根据剩下的where条件进行过滤

而在MySql5.7之后，把第二步一部分操作下推到存储引擎层，即可以根据索引上的值过滤一些不符合条件的数据（称为 index filter），再返回到SQL SERVER层进行过滤（称为 table filter）过程如下

根据最左前缀原则在存储引擎层确定index key的检索范围
根据索引过滤掉一些数据，再回表查询读取
返回给SQL SERVER层再根据剩下的where条件进行过滤
这一改动就称之为索引下推

直观体验就是执行计划中 extra 提示Using index condition

脑筋急转弯（大雾）
如果把上面的*改成name,age进行索引覆盖还会出现索引下推么？

答案是：
不会出现，看原理我们得出索引下推是出现回表查询的时候才会用到的技术，那么索引覆盖不会回表，那么就不会出现索引下推。

索引下推的好处

直接在存储引擎层过滤，省去了这些记录回表查询以及返回到SQL SERVER曾的开销。

索引的选择

索引优先选择包含所有信息的列，如果有多个索引都包含全部信息，存储引擎自动选择键值最短的索引，这个跟存储引擎的工作机制有关，存储引擎一下子读取多条数据的键值进行比对，每次读取到内存中的大小是一定的，那么键值越短，每次读取的条数越多，IO越少，效率越高。

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `NAME` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`NAME`,`age`),
  KEY `idx_sex` (`sex`),
  KEY `idx_age_address` (`age`,`address`),
  KEY `idx_sex_address` (`sex`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果我要单一查找address,该信息在idx_age_address和idx_sex_address中都是存在的且都不需要回表查询，而idx_age_address键长为308，idx_sex_address为307所以会选择idx_sex_address

2. SQL优化器会自动帮你优化条件顺序，以最左前缀原则选择合适的索引

索引失效

讲索引失效之前，你要牢记索引的存储结构，就是

主键索引是主键值与整个行记录信息。
辅助索引存放的是辅助索引所包含的列的信息与主键值。

结合上边的例子，我们插入几条数据

INSERT INTO `tuser` VALUES (1, 'zhangsan', 20, '0', '致真 大厦');
INSERT INTO `tuser` VALUES (2, 'lisi', 23, '0', '致真 大厦');
INSERT INTO `tuser` VALUES (3, 'wangwu', 22, '0', '致真 大厦');
INSERT INTO `tuser` VALUES (4, 'zhuliu', 21, '0', '致真 大厦');

那么索引PRIMARY（主键索引）中存放的结构大体如下：

索引idx_name_age中存放的结构大体如下：

（一定都是排过序的）

不符合最左前缀原则

1. 想要利用索引，索引的第一项必须在检索条件中。
2. 如果断开只能利用到断开前的那一项。
3. SQL条件与你写的位置无关，SQL优化器会自动帮你优化条件顺序
口诀就是
带头索引不能死，中间索引不能断

其实根据键值很好理解比如，我想查询name=lisi的值，虽然索引idx_name_age键值是name和age拼接起来的，但是我依然可以根据键值是否以lisi开头来快速过滤出来。相反的我只检索age=20即使idx_name_age中里面有age字段但是在键值中间所以就无法使用该索引快速过滤只能全文检索了。
下面举几个例子（以name和age项组合索引为例）,多图预警