关于mysql:MySQL索引二索引优化方案你都了解吗

在上一篇文章中，咱们介绍了 MySQL 中常见的索引类型以及每种索引的各自特点，那么这篇文章带你来与我一起看一下汇集索引与二级索引的关系，最初在附上常见的索引优化计划。首先咱们还是看一下汇集索引和二级索引的区别

https://mp.weixin.qq.com/s?__…

首先，每个 Innodb 引擎的表都有一个汇集索引，用于存储行数据，通常状况下，汇集索引也叫做主键索引。

如果一个表定义了主键，Innodb就应用它作为汇集索引。因而咱们要尽可能的为表增加一个主键，如果切实没有一个列是非空且惟一的能够作为主键列，倡议增加一个主动递增的列作为主键列
如果表没有主键，Innodb会抉择第一个非空且惟一的列作为汇集索引
如果表既没有主键，也没有非空且惟一的索引，则 Innodb 生成一个暗藏的汇集索引，GEN_CLUST_INDEX 蕴含 rowid 值的列，数据行依据 rowid 排序，rowid是一个 6 字节的字段，随着数据插入而枯燥递增，也就是说，数据行依据 rowid 排序也就是依据插入程序排序的

在下面，咱们晓得了主键索引也就是汇集索引，而且咱们的日常工作中，查问如果依据主键查问都是很快的，那么汇集索引是如何晋升查问效率的呢？

通过汇集索引拜访一条数据是很快的，这是因为所有的行数据和索引保留在同一个页上。如果表数据特地大，相较于数据和索引保留在不同的页上的存储构造相比，Innodb大大节俭了磁盘 IO 操作

当初咱们晓得了汇集索引之所以查问的快是因为要查问的行数据和索引都保留在同一个页上，也就缩小了去磁盘查找数据的过程，那么二级索引呢，二级索引是如何与汇集索引关联的呢？

汇集索引之外的其它索引全副被称为二级索引。在 Innodb 中，二级索引中的每条记录都蕴含主键列以及自身二级索引指定的索引列，在汇集索引中，Innodb应用此主键值查问该行的数据

如果主键较长的话，那么咱们二级索引保留主键列时就会占用更多的空间，所以主键尽可能的短是无利的

读到这，咱们应该曾经晓得了，汇集索引中所有记录与索引都保留在同一个页中，所以这也是汇集索引查问快的起因。二级索引没有保留以后记录的数据，只保留了主键列，所以在应用二级索引的时候会波及到两步操作，即依据二级索引先定位主键列，而后依据主键列在汇集索引中查问数据返回。当初咱们也晓得了，通过二级索引查找会波及到多一次交互的问题，那么这个点也是咱们后文将要探讨的一个点，也就是所谓的回表。目前咱们罕用的索引优化形式有笼罩索引、最左前缀、索引下推，当初咱们一起来具体看下索引的优化形式是怎么工作的

首先咱们还是新建一张表 t，在k 列建设索引，建表语句如下

create table t(
  id int primary key,
  k int not null default 0,
  s varchar(16) not null default '',
  index k(k)
)engine=Innodb;
# 退出测试数据
insert into t values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

在上文中，咱们曾经晓得了汇集索引与二级索引的区别，所以在执行完下面的插入语句之后，数据的存储构造为两棵索引树，一棵主键索引树蕴含数据，一棵二级索引 k 的索引树

此处借鉴一下极客工夫丁奇老师的索引结构图，此处放个不同数据结构存储图，这个网站不错，能够模仿数据结构的存储过程，给大家举荐一下，这块网站模仿的和丁奇老师的还是不一样，这块起因还不理解，对这块还不是很相熟，有理解的能够评论区说一下，互相学习一下。本文还是以丁奇老师解说的图为主

https://www.cs.usfca.edu/

首先咱们执行一条查问语句

select * from t where k between 3 and 5;

执行过程是这样的，首先到 k 索引树取到值为 3 的到主键索引树获取主键等于 300 的 R3 记录返回，而后取 k 索引树下一值 5 的主键 500 到主键索引树取 500 的记录 R5，而后取 k 索引树下一个值 6 的主键 600 到主键树查问，发现 6 不符合条件，不再去主键树查问，返回后果

通过下面的剖析，那么什么是笼罩索引呢，很简略，咱们看上面两条 sql 语句

1、explain select * from t where k between 3 and 5;
2、explain select id from t where k between 3 and 5;

两条语句的执行剖析后果如下

两条 sql 惟一的区别就是第一条 sql 会获取所有的字段，而第二条 sql 只获取 id 这个字段，而 k 索引树上曾经保留了 id 的值，所以此时也就不必回表查问了，这种形式就是笼罩索引。因为笼罩索引能够大大减少搜寻树的次数，所以应用笼罩索引是罕用的优化伎俩

上面咱们在看另一个应用笼罩索引的例子首先还是创立一张用户表，建表语句如下

CREATE TABLE `tuser` (`id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
# 退出数据
insert into tuser values(1,'123456789012345678','test1','18',1),(2,'123456879012345677','test2','19',1),(3,'123456987012345676','test3','20',1),(4,'123456986012345675','user1','21',0),(5,'123456985012345674','user2','22',1),(6,'123456787012345673','user3','23',1),(7,'123456788012345672','admin1','24',0);

当初咱们在表上建设了两个索引，身份证上一个索引，姓名和年龄一个索引。如果咱们依据身份证获取用户信息，那么一个身份证索引就能够满足了，然而要是有一个高频的申请，依据身份证获取用户姓名，那么咱们能够再创立一个身份证和姓名的联结索引，这样这些所有的依据身份证获取用户名的申请都能够在身份证姓名的联结索引上应用笼罩索引，那么这个索引也就是有意义的。

explain select * from tuser where id_card = '123456789012345678';

上面是依据身份证索引查问信息的执行后果

借助 name，age 的联结索引咱们来剖析一下最左前缀和索引下推

在下面的测试数据中，如果咱们有以下查问语句

1、explain select * from tuser where name like 'test%';
2、explain select * from tuser where name = 'test1';

第一条语句会在查问时获取 name,age 索引树上 test 结尾的数据，是能够用到 name_age 的联结索引的(possible_keys), 然而在这次查问中优化器没有抉择应用索引（key 是 null）

第二条语句也是能够走 name,age 索引树的，而且抉择走 name_age 的联结索引（possible_keys 和 key 都是 name_age）

通过下面两条 sql 语句，咱们能够晓得，不只是索引的全副定义，只有满足索引的最左前缀也是能够减速拜访的。这个最左前缀能够是字符串索引的前 n 个字节，也能够是联结索引的最左 n 个字段。

开启敞开索引下推性能, 默认状况下是开启的

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

那么什么是索引下推呢，还是上 SQL 语句

SET optimizer_switch = 'index_condition_pushdown=off';
explain select * from tuser where name like 'test%' and age = '18';
SET optimizer_switch = 'index_condition_pushdown=on';
explain select * from tuser where age = '18' and name like 'test%';

那么怎么看是否应用了索引下推呢，援用下官网的一句话，Extra列显示 Using index condition 即应用了索引下推

EXPLAIN output shows Using index condition in the Extra column when Index Condition Pushdown is used. It does not show Using index because that does not apply when full table rows must be read.

所以，到底什么是索引下推呢，来看我剖析

敞开索引下推优化时

首先或者所有 name 是 test 结尾的数据，而后回表，判断 age 等于 18 的数据，而后返回数据
开启索引下推优化时

获取 name 是 test 结尾的数据，并判断 age 等于 18 的数据，而后残余的数据拿到主键索引树回表查问返回

两者的区别就是回表的次数显著变少，在索引下推优化敞开时，会拿所有的主键去主键树获取数据，而开启之后，提前做判断，缩小回表次数，这就是索引下推，也是工作中罕用到的优化形式

非空，惟一，主键

create table test(a int primary key,b varchar(5));
insert into test values(1,'a'),(2,'b'),(3,'c'),(4,'c'),(5,'d');
select _rowid from test;

原文链接

https://mp.weixin.qq.com/s?__…

https://dev.mysql.com/doc/ref…

有不同索引类型之间的形容

https://dev.mysql.com/doc/ref…

b 树索引与 hash 索引的比照

https://dev.mysql.com/doc/ref…

index

https://dev.mysql.com/doc/ref…

geek

https://time.geekbang.org/col…

关于mysql:MySQL索引二索引优化方案你都了解吗

汇集索引和二级索引区别

汇集索引如何晋升查问效率

二级索引如何与汇集索引关联

罕用的索引优化形式

笼罩索引

最左前缀

索引下推

_rowid 查看示例

参考链接