MySQL索引原理详解
select执行流程
简单介绍索引
-
官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度
-
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）
-
我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引
索引的优势和劣势
-
可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录
-
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
-
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些
-
如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多
-
-
索引会占据磁盘空间
-
索引虽然会提高查询效率，但是会降低更新表的效率
-
MySQL会级联的去维护索引数据
-
索引的使用
索引的类型&创建
主键索引
-
索引列中的值必须是唯一的，切不能为空
-
alter table table_name add primary key (column_name);
普通索引
-
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值
-
alter table table_name add index index_name (column_name)
唯一索引
-
索引列中的值必须是唯一的，但是允许为空值
-
create unique index index_name on table(column_name) ;
全文索引
-
只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引
-
字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引
-
全文索引一般很少使用，数据量比较少或者并发度低的时候可以用
-
数据量大或者并发度高的时候一般是用专业的工具lucene,es，solr
-
alter table table_name add fulltext index
idx_content(column_name); -
可以使用MATCH() ... AGAINST语法执行全文搜索
-
select* from test where match (column_name) against ('巴拉巴拉');
-
空间索引
-
MySQL在5.7之后的版本支持了空间索引，
-
支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则
-
不怎么用，想学习可以d参考官网
-
前缀索引
-
在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定
-
alter table table_name add index index_name (column1(length));
组合索引
-
使用2个以上的字段创建的索引
-
组合索引的使用，需要遵循最左前缀原则（最左匹配原则，后面详细说明）
-
一般情况下，建议使用组合索引代替单列索引（主键索引除外，具体原因后面详细说明）
-
alter table_name add index index_name (column1,column2);
索引查看& 删除
-
drop index index_name on table
-
show index from table_name \G
索引的数据结构
索引的要求
-
索引的数据结构，至少需要支持两种最常用的查询需求
-
等值查询：根据某个值查找数据，比如
-
select * from user where name='张三';
-
-
范围查询：根据某个范围区间查找数据，比如
-
select * from user where age>=18 and age<=25;
-
-
-
同时需要考虑时间和空间因素。在执行时间方面，我们希望通过索引，查询数据的时间尽可能小
-
在存储空间方面，我们希望索引不要消耗太多的内存空间和磁盘空间
索引数据结构的选用
-
常用的数据结构：Hash表、二叉树、平衡二叉查找树（红黑树是一个近似平衡二叉树）、B树、B+树
-
下面我们挨着挨着来说明这些数据结构
Hash表
-
Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据
-
我们使用Hash表存储表数据，Key可以存储索引列，Value可以存储行记录或者行磁盘地址
-
Hash表在等值查询时效率很高，时间复杂度为O(1)
-
不支持范围快速查找，范围查找时还是只能通过扫描全表方式
二叉查找树
-
二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大
-
二叉树的检索复杂度和树高相关
-
将age列构建二叉树，检索age=76的数据，只需要三次IO就可以查询到结果
-
但是也不是二叉树就绝对会带来查询效率的提升
-
二叉树在极端情况下会退化为一个单向列表，效率急速下降
-
平衡二叉查找树
-
上面我们看到了二叉查找树 极端情况的出现导致效率拉低
-
根本原因在于，该树不平衡，太极端了
-
-
于是便有了这个平衡二叉查找树
-
平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点
-
最主要的特征是树的左右两个子树的层级最多相差1
-
在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况
-
使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。查询id=6，只需要两次IO
-
但是也有问题暴露出来
-
查询数据的时间复杂度和树的搞得相关
-
平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高
-
B树（改造二叉树）
MySQL的数据是储存在磁盘文件当中的
查询处理数据时，需要先从磁盘中将数据加载到内存当中
访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度
-
假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）
-
MySQL的InnoDB存储引擎一次IO会读取的一页16K的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低
-
为了最大化利用一次IO空间，一个朴素的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了
-
这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：
-
B树的节点中存储着多个元素，每个内节点有多个分叉
-
节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据
-
父节点当中的元素不会出现在子节点中
-
所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接
-
对于一个主键索引，主键值bigint=8字节，data为记录的磁盘地址为4个字节，一个元素占用空间12字节。一个磁盘块大小为16k。磁盘块中的分叉数=元素树+1，假设可以存储x个元素，12x+(x+1)4=16k,约等于1000，也就是说一页中可以最多可以存储1000个元素。 (1) ：二层B树结构可以存储的数量10001000=1百万，三层树结构可以存储的数量100010001000=1百亿。 (2) ：B树的高度一般都是在2-4这个高度，树的高度直接决定IO读写的次数以及查询时间复杂度O(log2n)）
我们来模拟一下查找查找主键为29的数据
-
第一次磁盘IO：将磁盘块1加载到内存当中，然后遍历比较 17 < 29 <35,锁定磁盘块1中 P2 这个指针
-
第二次磁盘IO：将磁盘块3加载到内存中，然后遍历比较 26 < 29 < 30，锁定磁盘块3中 P2 这个指针
-
第三次磁盘IO：将磁盘块8加载到内存中，然后遍历比较 28 < 29 = 29，找到了29所在位置，取出data
-
如果date中储存的是表行数据，则直接返回
-
如果data中储存的是磁盘地址，则根据该地址去磁盘中获取数据，查询终止
-
相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率
但是B树也是有一定缺陷的：
-
B树不支持范围查询的快速查找，如果我们想要查找15和26之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高
-
如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大
B+树（改造B树）
在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题
B树：非叶子节点和叶子节点都会存储数据
B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶 子节点形成了一个双向有序链表
-
B+树的最底层叶子节点包含所有索引项
-
B+树查找数据，由于数据都存放在叶子节点，所以每次查找都需要检索到叶子节点，才能查询到数据
-
B树查找数据时，如果在内节点中查找到数据，可以立即返回，比如查找值等于17的数据，在根节点中直接就可以找到，不需要再向下查找，具备中路返回的特点
我们来模拟一下查找查找主键为29的数据
-
第一次磁盘IO：将磁盘块1加载到内存当中，然后遍历比较 28 < 29 ,锁定磁盘块1中 P2 这个指针
-
第二次磁盘IO：将磁盘块3加载到内存中，然后遍历比较 29 < 36，锁定磁盘块3中 P1 这个指针
-
第三次磁盘IO：将磁盘块7加载到内存中，然后遍历比较 28 < 29 = 29，找到了29所在位置，取出data
-
如果date中储存的是表行数据，则直接返回
-
如果data中储存的是磁盘地址，则根据该地址去磁盘中获取数据，查询终止
-
我们再来模拟一下范围查询 15 -- 29
-
第一次磁盘IO：将磁盘块1加载到内存当中，然后遍历比较 15 < 28 ,锁定磁盘块1中 P1 这个指针
-
第二次磁盘IO：将磁盘块2加载到内存中，然后遍历比较10 < 15 < 17，锁定磁盘块2中 P2 这个指针
-
第三次磁盘IO：将磁盘块5加载到内存中，然后遍历比较 15 = 15，找到了25所在位置
-
叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表
-
-
第四次磁盘IO：将磁盘块6加载到内存中，然后遍历比较 15 < 17 < 26 < 29
-
叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表
-
-
第五次磁盘IO：将磁盘块7加载到内存中，然后遍历比较 15 < 28 < 29 = 29，将所有结果缓存到结果集
可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构
MyISAM使用B+树构建索引
MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址
我们先创建一个表：id为主键，age为普通索引
CREATE TABLE `user_myisam` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `username` VARCHAR ( 20 ) DEFAULT NULL, `age` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ) USING BTREE, KEY `idx_age` ( `age` ) USING BTREE ) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
导入一部分初始数据，用作演示
主键索引的B+树
-
表user_myisam的索引存储在索引文件：/var/lib/mysql/数据库名/user_myisam.MYI中，
-
数据文件存储在数据文件/var/lib/mysql/数据库名/user_myisam.MYD中
等值查询数据：select * from user_myisam where id=30;
-
第一次磁盘IO：先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路
-
第二次磁盘IO：将左子树节点加载到内存中，比较20<30<49，向下检索
-
第三次磁盘IO：检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项
-
第四次磁盘IO：从索引项中获取磁盘地址，然后到数据文件user_myisam.MYD中获取对应整行记录
-
将记录返给客户端
-
磁盘IO次数： 3 + 1
范围查询数据 ：select * from user_myisam where id between 30 and 49;
-
第一次磁盘IO：先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路
-
第二次磁盘IO：将左子树节点加载到内存中，比较20<30<49，向下检索
-
第三次磁盘IO：检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项
-
第四次磁盘IO：从索引项中获取磁盘地址，然后到数据文件user_myisam.MYD中获取对应整行记录 缓存到结果集中
-
叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表
-
-
第五次磁盘IO： 查询语句时范围查找，需要向后遍历底层叶子链表，找到49的索引项
-
第六次磁盘IO：从索引项中获取磁盘地址，然后到数据文件user_myisam.MYD中获取对应整行记录 缓存到结果集中
-
将记录返给客户端
-
磁盘IO次数： 2 + 2 + 2
辅助索引的B+树
在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复
查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据
InnoDB使用B+树构建索引
InnoDB索引简介
每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：
在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引
如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引
如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增
除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录
我们还是先创建一个表用来做测试，id为主键，age为普通索引
CREATE TABLE `user_innodb` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `username` VARCHAR ( 20 ) DEFAULT NULL, `age` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ) USING BTREE, KEY `idx_age` ( `age` ) USING BTREE ) ENGINE = INNODB;
导入一部分初始数据，用作演示
-
InnoDB的数据和索引存储在一个文件：/var/lib/mysql/数据库名/user_innodb.ibd中
-
InnoDB的数据组织方式，是聚簇索引
主键索引的B+树
-
主键索引的叶子节点会存储数据行，辅助索引只会存储主键值
-
InnoDB要求表必须有一个主键索引(MyISAM 可以没有)
等值查询数据：select * from user_innodb where id=30;
-
第一次磁盘IO：先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路
-
第二次磁盘IO：将左子树节点加载到内存中，比较20<30<49，向下检索。
-
第三次磁盘IO：检索到叶节点，将节点加载到内存中遍历，比较 20 < 30 = 30 直接可以获取整行数据
-
磁盘IO次数：3次
范围查询数据 ：select * from user_innodb where id between 30 and 50;
-
第一次磁盘IO：先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路
-
第二次磁盘IO：将左子树节点加载到内存中，比较20<30<49，向下检索。
-
第三次磁盘IO：检索到叶节点，将节点加载到内存中遍历，比较 20 < 30 = 30 ，将数据缓存到结果集中
-
第四次磁盘IO：向后遍历底层叶子链表 ，49 < 50 = 50,将这两个数据缓存到结果集中
-
磁盘IO次数：4次
可以看到，因为在主键索引中直接存储了行数据，所以InnoDB在使用主键查询时可以快速获取行数据。
-
当表很大时，与在索引树中存储磁盘地址的方式相比
-
因为不用再去磁盘中获取数据，所以聚簇索引通常可以节省磁盘IO操作
辅助索引的B+树
除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址
-
底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序
-
使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主键索引中检索获得数据
等值查询数据：select * from user_innodb where age=7;
-
第一次磁盘IO：先在主键树中从根节点开始检索，将根节点加载到内存，比较7 < 8，走左路
-
第二次磁盘IO：将左子树节点加载到内存中，比较6 < 7，走右路。
-
第三次磁盘IO：将节点加载到内存中遍历，，比较 6 < 7 = 7,检索到叶节点，获取主键id = 49
-
第3 + 3次磁盘IO：去主键B+树中根据ID查询id为49的数据
-
其他情况磁盘IO：如果在这张表中，age为7的数据不止一条，会中继的不断将结果缓存到结果集中一起返回
-
磁盘IO次数：不可计算
根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询
范围查询数据：select * from t_user_innodb where age between 5 and 8;
-
辅助索引的范围查询流程和等值查询基本一致，先使用辅助索引到叶子节点检索到第一个符合条件的索引项
-
然后向后遍历，直到遇到第一个不符合条件的索引项，终止。
-
检索过程中需要将符合筛选条件的id值，依次到主键索引检索将检索的数据放入结果集中。
-
最后将查询结果返回客户端
组合索引储存结构
下面我们了解一下组合索引的B+树是如何构建，查找的时候又是如何查找的
首先我们还是创建一个测试表：id为主键，idx_abc(a,b,c) 为组合索引
CREATE TABLE `multiple_index` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `a` INT ( 11 ) DEFAULT NULL, `b` INT ( 11 ) DEFAULT NULL, `c` VARCHAR ( 10 ) DEFAULT NULL, `d` VARCHAR ( 10 ) DEFAULT NULL, PRIMARY KEY ( `id` ) USING BTREE, KEY `idx_abc` ( `a`, `b`, `c` ) ) ENGINE = INNODB;
我们还是老样子，导入一点测试数据进去
上图构建的B+树索引结构如图所示
-
索引树中节点中的索引项按照（a，b，c）的顺序从小到大排列
-
先按照a列排序，a列相同时按照b列排序，b列相同按照c列排序
-
在最底层的叶子节点中，如果两个索引项的a，b，c三列都相同，索引项按照主键id排序
组合索引的查找方式
select * from multiple_index where a=55 and b=555 and c=5c;
-
第一次磁盘IO：先在索引树中从根节点开始检索，将根节点加载到内存，先比较a列 55 < 88
-
第二次磁盘IO：将左子树节点加载到内存中，先比较a列，44 < 55，走右路
-
第三次磁盘IO：达到叶节点，将节点加载到内存中从前往后遍历比较
-
55 = 55 符合要求
-
555 = 555 符合要求
-
5c = 5c 符合要求
-
取出data的值，即 id = 5，
-
-
第N次磁盘IO：回表去主键B+树中检索id=5的数据放入结果集中
-
其他情况磁盘IO：如果在这张表中，abc符合要求的数据不止一条，会中继的不断将结果主键id缓存到起来，再依次去主键B+树种查询数据，并依次将结果缓存到结果集中，最后统一返回
-
磁盘IO次数：不可计算
组合索引的最左前缀匹配原则
最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的
在上面的查找方式中我们可以发现，首先会根据：where a=55 and b=555 and c=5c;
-
a 位于最左侧的第一个条件，也是拿去匹配索引的第一个属性，在B+树中优先比较最左侧的属性值
-
上面我们也已经说到了：先按照a列排序，a列相同时按照b列排序，b列相同按照c列排序
-
叶子节点按照第一列a列从左到右递增排列
-
-
所以当我们使用 where a=55 and b=555 and c=5c去查询数据的时候，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起
-
所以，就有一个规矩出现：
-
想要命中组合索引，提高响应性能，第一个条件就必须要有，比如a=55
-
如果你的a没有赋值，where b = 555 and c=5c;
-
在组合B+树中因为a没有值，无法在组合索引B+树种定位数据，也无法回表去主键B+树种快速定位数据
-
-
最后只能走全表扫描
-
-
其实我们在创建组合索引的时候
-
InnoDB给我们一共创建三个索引： a 、a,b、a,b,c
-
或许有人会问了，为什么没有：a,c
-
因为a,过了之后就改匹配b了，此时你b没值，根本就轮不到c去匹配，所以无法命中组合索引
-
-
还有一点就是：
-
组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直从左向右匹配直至遇到范围查询(>、<、between、like)就停止匹配
-
另外书写SQL条件的顺序，不一定是执行时候的where条件顺序，优化器会帮助我们优化成索引可以识别的形式
-
比如下面这个（唯一保证的是绝对正向优化，不会负向优化）
-
#无法命中组合索引 select * from multiple_index where b=555 and a=55 and c=5c; #优化器正向优化:优化器会按照索引的顺序优化 select * from t_multiple_index where a=55 and b=555 and c=5c;
-
一颗索引树等价与三颗索引树，从另一方面了说，组合索引也为我们节省了磁盘空间。所以在业务中 尽量选用组合索引，能使用组合索引就不要使用单列索引
-
组合索引创建原则
-
频繁出现在where条件中的列，建议创建组合索引。
-
频繁出现在order by和group by语句中的列，建议按照顺序去创建组合索引
-
order by a,b 需要组合索引列顺序（a,b）。如果索引的顺序是（b,a），是用不到索引的
-
-
常出现在select语句中的列，也建议创建组合索引
对于第1种情况和第3种情况，组合索引创建的顺序对其来说是等价的，这种情况下组合索引中的顺序，是很重要的。由于组合索引会使用到最左前缀原则，使用频繁的列在创建索引时排在前面
select * from t where a=1 and b>2 order by c
-
大家觉得这个组合索引该如何创建
-
这种情况实际上比较的是b的区分度和c的区分度，如果b的区分度比较差，建议使用ac。如果c的区分度比较差，建议使用a,b
-
可以考虑建立 （a,c）联合索引：这样 a等值查询c就是已经排好序的了
-
覆盖索引
-
前面我们提到，根据在辅助索引树查询数据时，首先通过辅助索引找到主键值，然后需要再根据主键值到主键索引中找到主键对应的数据。这个过程称为回表
使用辅助索引查询比基于主键索引的查询多检索了一棵索引树，那是不是所有使用辅助索引的查询都需要回表查询呢？
-
在上面的组合索引中，我们为（a,b,c）创建了组合索引，在B+索引树种叶子节点一共包含了四个数据，
-
分别是【a,b,c,id】
-
-
然后我们查询某条数据，根据组合索引定位主键id，然后回表主键B+索引树查询该条数据
-
其实也不是所有的查询都需要回标查询
-
select a from multiple_index where a=11 and b=111; select a,b from multiple_index where a=22 and b=222; select a,b,c from multiple_index where a=33 and b=333; select a,b,c,id from multiple_index where a=44 and b=444;
-
比如上面这些sql，我们想要查询的数据，可以直接在辅助索引树上全部获取 ，此时则不需要在回表操作
-
这种现象就是覆盖索引
-
使用explain工具查看执行计划，可以看到 extra 中“Using index”，代表使用了覆盖索引
-
索引条件下推ICP
是MySQL5.6对使用索引从表中检索行的一种优化。可以通过参数optimizer_switch控制ICP的开始和关闭
ICP的目的是为了减少回表次数，可用于 InnoDB 和 MyISAM 表，对于InnoDB表ICP仅用于辅助索引
我们还是以上面的组合索引为例，来讲解ICP的作用
select * from t_multiple_index where a=11 and b>=111 and c='1c' and d='1d';
-
在使用组合索引在检索数据时是使用最左前缀原则来定位记录，那不满足最左前缀的索引列，MySQL会怎么处理？
-
我们去看看执行计划是怎么说的 !
-
之前我们说过：
-
组合索引的最左前缀匹配原则：mysql会一直从左向右匹配直至遇到范围查询(>、<、between、like)就停止匹配
-
所以上面的sql只使用了 （a,b）这个索引来检索数据
-
MySQL首先会在组合索引中定位到第一个满足 a=11 and b>=111 的索引项
-
然后我们看上图的执行计划 ：
-
extra列中的“Using index condition” 执行器表示使用了索引条件下推ICP
-
-
MySQL5.6之前不使用ICP的操作
-
执行器使用索引(a,b,c)，筛选条件a=11 and b>=111
-
根据最左前缀原则联合索引检索定位到索引项（11,111,1c,id=1）
-
在我们的测试表中只有一条数据，实际中会查询出很多符合（a=11 and b>=111）的数据项
-
然后这些数据项全部回表查询，获得多条行记录
-
-
然后使用id=1回表查询，获得id=1的行记录。返回给MySQL服务层
-
MySQL服务层使用剩余条件c='1c' and d='1d'过滤，不符合要求，直接丢弃
-
返回结果集
-
在这个过程中，由于（a=11 and b>=111）可能会定位到多条数据
-
定位到的所有数据全部都会回表查询，然后得到多条行数据
-
再然后MySQL服务层使用剩余条件c='1c' and d='1d'过滤刚刚得到的多行数据，不符合要求，直接丢弃
-
整个过程，无用回表操作多次，磁盘IO倍增，效率急剧下降
-
MySQL5.6之后使用ICP的操作
-
可以在辅助索引B+树索引遍历过程中，对where中包含剩余条件c='1c' and d='1d'先做判断，只有满足条件的才会回表查询读取行数据。这么做可以减少回表查询，从而减少磁盘IO次数
-
执行器使用索引(a,b,c)，筛选条件a=11 and b>=111
-
根据最左前缀原则联合索引检索定位到索引项（11,111,1c,id=1）
-
在我们的测试表中只有一条数据，实际中会查询出很多符合（a=11 and b>=111）的数据项
-
【不使用ICP】然后这些数据项全部回表查询，获得多条行记录
-
【使用ICP】使用ICP下推条件c='1c' 判断 ,不满足条件，直接丢弃 ,满足条件再回表
-
因为还有d没有匹配，此时可能还是有冗余数据存在，但是会少很多
-
-
回表查询，得到所有符合a=11 and b>=111 and c='1c'的数据行
-
然后MySQL服务层使用剩余条件d='1d'过滤，不符合要求，直接丢弃
-
-
最后将结果集返回
看了上面你可能会有疑问了？
-
为什么不在辅助索引树中就把d='1d'一起给过滤了，而是只单单过滤了c='1c'
-
给你一分钟好好想想，我再给你一点提示
-
-
上面的图中，有d的数据吗？如果没有，在哪里去拿来比？？？
所以，接下来，把本子拿出来记好，这是要考的
-
不使用ICP，不满足最左前缀的索引条件的比较是在存储引擎层进行的，非索引条件的比较是在Server层进行的
-
使用ICP，所有的索引条件的比较是在存储引擎层进行的，非索引条件的比较是在Server层进行的
-
ICP可以有效减少回表查询次数、减少了磁盘IO次数、服务层与存储引擎的交互次数。
索引创建原则
哪些情况需要创建索引
-
频繁出现在where 条件判断，order排序，group by分组字段
-
select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）
-
多表join关联查询，on字段两边的字段都要创建索引
索引创建建议
-
表记录很少不需创建索引 （索引是要有存储的开销） .
-
一个表的索引个数不能过多
-
空间开销、数据维护同时需要维护索引树，性能被拉低
-
-
频繁更新的字段不建议作为索引
-
频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高
-
-
区分度低的字段，不要建索引
-
比如你的性别这种字段建来干啥，加上回表查询消耗，还不如全表扫描呢
-
-
在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段
-
主键索引树一个页节点是16K，主键字段越长，一个页可存储的数据量就会越少
-
辅助索引树上叶子节点存储的数据是主键值，主键值越长，一个页可存储的数据量就会越少
-
都会导致查询时磁盘IO次数会增多，查询效率会降低
-
-
不建议用无序的值作为索引。例如身份证、UUID （踩坑了吧）
-
更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间
-
-
尽量创建组合索引，而不是单列索引
-
1个组合索引等同于多个索引效果，节省空间
-
可以使用覆盖索引
-
组合索引应该把把频繁的列，区分度高的值放在前面
-
频繁使用代表索引的利用率高，区分度高代表筛选粒度大，可以尽量缩小筛选范围
-
-
索引失效分析
先创建演示表: id为主键，(name,age,address)为组合索引，school 没有索引
CREATE TABLE `test` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `name` VARCHAR ( 10 ) DEFAULT NULL, `age` INT ( 11 ) DEFAULT NULL, `address` VARCHAR ( 10 ) DEFAULT NULL, `school` VARCHAR ( 10 ) DEFAULT NULL, PRIMARY KEY ( `id` ) USING BTREE, KEY `idx_name_age_address` ( `name`, `age`, `address` ) ) ENGINE = INNODB;
-
再来一点测试数据
全值匹配我最爱
就是索引全值匹配 比如test表的辅助索引有【(name),(name,age),(name,age,address)】
最佳左前缀法则
带头索引不能死，中间索引不能断
不要在索引上做计算
不要进行这些操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全索引扫描或全表扫描
范围条件右边的列失效
不能继续使用索引中范围条件（bettween、<、>、in等）右边的列
尽量使用覆盖索引
索引字段上不要使用不等
索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
索引字段上不要判断null
索引字段上使用 is not null 判断时，会导致索引失效而转向全表扫描
使用 is null 是可以使用索引的
索引字段使用like不以通配符开头
索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描
解决like '%字符串%' 时，索引失效问题的方法 ：使用覆盖索引可以解决
like与范围条件（bettween、<、>、in等）不同的是：不会导致右边的索引失效
索引字段字符串要加单引号
索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
索引字段不要使用or
索引字段使用 or 时，会导致索引失效而转向全表扫描
