MySQL索引详解

一、InnoDB的索引模型

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

有一个主键列为ID的表（表1），表中有字段k，并且在k上有索引。这个表的建表语句如下：

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示意图（图1.1）如下：

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

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引。非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引

基于主键索引和普通索引的查询有什么区别？

• 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树
• 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表

基于非主键索引的查询需要多扫描一棵索引树。因此，在应用中应该尽量使用主键查询

二、索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护

以上面的图1.1为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新纪录

如果新插入的ID值为400，需要逻辑上挪动后面的数据，空出位置。如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。页分裂不仅会影响性能，还会影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%

当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程可以认为是分裂过程的逆过程

自增主键的插入数据模式，正好符合递增插入的场景。每次插入一条新纪录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有些业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高

除了考虑性能外，还可以从存储空间的角度来看。假设表中有一个唯一字段，比如字符串类型的身份证号，如果用身份证号做主键，由于每个非主键索引的叶子节点上都是主键的值，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型则是8个字节

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

从性能和存储空间方面考虑，自增主键往往是更合理的选择

在只有一个索引，该索引必须是唯一索引的场景下适合用业务字段直接做主键

三、使用普通索引时SQL语句的执行流程

表T（表2）的初始化语句如下：

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');

如果执行select * from T where k between 3 and 5，这条SQL语句的执行流程如下：

1.在k索引树上找到k=3的记录，取得ID=300

2.再到ID索引树查到ID=300对应的R3

3.在k索引树取下一个值k=5，取得ID=500

4.再回到ID索引树查找ID=500对应的R4

5.在k索引树取下一个值k=6，不满足条件，循环结束

回到主键索引树搜索的过程称为回表

四、覆盖索引

对于表2来说，如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经覆盖了我们的查询需求，称为索引覆盖

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

在引擎内部使用覆盖索引在索引k上其实读了三个记录，但是对于MySQL的Server层来说，它就是找引擎拿到了两条记录，因此MySQL认为扫描行数是2

在一个市民信息表（表3）上，是否有必要将身份证号和名字建立联合索引？

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；

身份证号是市民的唯一标识。如果有根据身份证号查询市民信息的需求，只要在身份证号字段上建立索引就够了

如果有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间

五、最左前缀原则

B+树这种索引结构，可以利用索引的最左前缀来定位记录

以表3的(name,age)这个联合索引为例来分析

索引项是按照索引定义里面出现的字段顺序排序的

如果要查的是所有名字是"张三"的人，可以快速定位到ID4，然后向后遍历得到所有需要的结果

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

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

在建立联合索引的时候，如何安排索引内的字段顺序？

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

在市民信息表（表3）上创建 (身份证号，姓名)这个联合索引，并用这个索引支持"根据身份证号查询地址"的需求

如果既有联合查询，又有基于a、b各自的查询，需要同时维护(a,b)、(b)这两个索引。考虑的原则就是空间了，name字段是比age字段大的，那么可以创建一个(name,age)的联合索引和一个(age)的单字段索引

六、索引下推

以市民信息表（表3）的联合索引(身份证号，姓名)为例，如果要检索出表中名字第一个字是"张"，而且年龄是10岁的所有男孩，SQL语句如下：

select * from tuser where name like '张 %' and age=10 and ismale=1;

根据索引前缀规则，所以这个语句在搜索索引树的时候，只能用"张"，找到第一个满足条件的记录ID3，然后判断其他条件是否满足

在MySQL5.6之前，只能从ID3开始一个回表。到主键索引上找到数据行，再对比字段值

MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

无索引下推执行流程图：

索引下推执行流程图：

上面两个图中，每一个虚线箭头表示回表一次

无索引下推执行流程图中，这个过程InnoDB并不会去看age的值，只是按顺序把name第一个字是“张”的记录一条条取出来回表。因此，需要回表4次

索引下推执行流程图中，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过

七、普通索引和唯一索引，应该怎么选择？

一个市民系统中，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的SQL语句：

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

如果要在id_card字段上建立索引，首先由于身份证号字段比较大，不建议把身份证号当作主键，要么给id_card字段创建唯一索引，要么创建一个普通索引。从性能的角度考虑，选择唯一索引还是普通索引呢？

1、查询过程

假设执行查询的语句是select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是上图中右下角的这个数据页， 然后可以认为数据页内部通过二分法来定位记录

• 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索

InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB

因为引擎是按页读写的，所以当找到k=5的记录的时候，它所在的数据页就都在内存里了。对于普通索引来说，要多做的那一次查找和判断下一条记录的操作，就只需要一次指针寻址和一次计算。如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一条记录，必须读取下一个数据页，这个操作会稍微复杂一些

总体来说，使用普通索引还是唯一索引对查询语句影响不大

2、更新过程

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性

change buffer是可以持久化的数据，在内存中有拷贝，也会被写入到磁盘上

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭的过程中，也会执行merge操作

merge的执行流程：

1.从磁盘读入数据页到内存（老版本的数据页）

2.从change buffer里找出这个数据页的change buffer记录（可能有多个），依次应用，得到新版数据页

3.写redo log。这个redo log包含了数据的变更和change buffer的变更

到这里merge过程就结束了。这时候，数据页和内存中change buffer对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了

如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率

什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了

唯一索引的更新不能使用change buffer，只有普通索引可以使用

如果要在这张表中插入一个新纪录(4,400)的话，InnoDB的处理流程是怎么样的？

1.如果这个记录要更新的目标页在内存中。这时，InnoDB的处理流程如下：

• 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束
• 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束

普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，差别不大

2.如果这个记录要更新的目标也不在内存中。这时，InnoDB的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束
• 对于普通索引来说，则是将更新就在change buffer，语句执行就结束了

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的

3、change buffer的使用场景

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存起来，所以在一个数据页做merge之前，change buffer记录的变更越多，收益就越大

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用小高最好

假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用

4、索引选择和实践

普通索引和唯一索引在查询能力上没差别的，主要考虑的是对更新性能的影响。建议尽量选择普通索引

如果所有的更新后面都马上伴随着对这个记录的查询，那么应该关闭change buffer。而在其他情况下，change buffer都能提升更新性能

5、change buffer和redo log

在表上执行这个插入语句：

insert into t(id,k) values(id1,k1),(id2,k2);

假设当前k索引树的状态，查找到位置后，k1所在的数据页在内存中，k2所在的数据页不再内存中

带change buffer的更新状态图：

这条更新语句中涉及了四个部分：内存、redo log、系统表空间（ibdata1）、数据表空间（t.ibd）

这条更新语句做了如下的操作：

1.Page1在内存中，直接更新内存

2.Page2没有在内存中，就在内存的change buffer区域记录下我要往Page2插入一行这个信息

3.将上述两个动作记入redo log中

做完上面这些，事务就可以完成了。执行这条更新语句的成本很低，就是写了两处内存，然后更新了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的

图中的两个虚线箭头是后台操作，不影响更新的响应时间

那在这之后的读请求，要怎么处理呢？

比如，现在要执行select * from t where k in (k1,k2)。如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间和redo log无关了

1.读Page1的时候，直接从内存返回

2.读Page2的时候，需要把Page2从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果

直到需要读Page2的时候，这个数据页才会被读入内存

redo log主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的是随机读磁盘的IO消耗

八、字符串字段创建索引的场景

可以使用的方式有：

1.直接创建完整索引，这样可能比较占用空间

2.创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引

3.倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题

4.创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，和第三种方式一样，都不支持范围扫描

九、为什么这些SQL语句逻辑相同，性能却差异巨大？

1、条件字段函数操作

一个交易系统中的交易记录表tradelog包含交易流水号（tradeid）、交易员id（operator）、交易时间（t_modified）等字段，这个表的建表语句如下：

CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设，现在已经记录了从2016年初到2018年底的所有数据，要统计发生在所有年份中7月份的交易记录总数。SQL语句如下：

select count(*) from tradelog where month(t_modified)=7;

t_modified字段上有索引，但却执行了特别久才返回结果

如果SQL语句条件用的是where t_modified='2018-7-1’的话，引擎就会按照上面绿色箭头的路线，快速定位到需要的结果。而B+树快速定位能力来源于同一层兄弟节点的有序性

如果计算month()函数的话，在树的第一层就不知道该怎么办了

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能，选择全索引扫描

优化器并不是放弃使用这个索引。在这个例子中，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引t_modified，优化器的对比索引大小后发现，索引t_modified更小，遍历这个索引比遍历主键索引来得更快。因此最终选择索引t_modified

即使是对于不改变有序性的函数，也不会考虑使用索引。比如，对于select * from tradelog where id+1=10000这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行。所以，需要写成where id=10000-1

2、隐式类型转换

select * from tradelog where tradeid=110717;

交易编号tradeid这个字段上，本来就有索引，但是这条语句需要走全表扫描。tradeid的字段类型是varchar(32)，而输入的参数却是整型，所以需要做类型转换

在MySQL中，字符串和数字作比较的话，是将字符串转换成数字

对于优化器来说，上面这条语句相当于：

select * from tradelog where  CAST(tradid AS signed int) = 110717;

对索引字段做函数操作，优化器会放弃走树搜索功能

如果id的类型是int，下面这条SQL语句不会导致全表扫描

select * from tradelog where id="83126";

3、隐式字符编码转换

假设系统里还有另外一个表trade_detail，用于记录交易的操作细节，建表语句如下：

CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /* 操作步骤 */
  `step_info` varchar(32) DEFAULT NULL, /* 步骤信息 */
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这时候要查询id=2的交易的所有操作步骤信息，SQL语句如下：

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; 

1.第一行显示优化器会先在交易记录表tradelog上查到id=2的行，这个步骤用上了主键索引，rows=1表示只扫描一行

2.第二行key=NULL，表示没有用上交易详情表trade_detail上的tradeid索引，进行了全表扫描

在这个执行计划中，是从tradelog表中取tradeid这个字段，再去trade_detail表里查询匹配字段。因此，我们把tradelog称为驱动表，把trade_detail称为被驱动表，把tradeid称为关联字段

1.根据id在tradelog表里找到L2这一行

2.从L2中取出tradeid字段的值

3.根据tradeid值到trade_detail表中查找条件匹配的行。explain的结果里面第二行的key=NULL表示的就是，整个过程是通过遍历主键索引的方式，一个一个地判断tradeid的值是否匹配

第3步不符合预期，因为表trade_detail里tradeid字段上是有索引的，却没有使用索引快速定位到等值的行。这是因为这两个表的字符集不同，一个是utf8，一个是utf8mb4，表连接过程中要求在被驱动表的索引字段上加函数操作，所以做表连接查询的时候用不上关联字段的索引

优化语句的方法：

1）把trade_detail表上的tradeid字段的字符集也改成utf8mb4，这样就没有字符集转换的问题了

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

2）如果业务上暂时不能做这个DDL的话，只能采用修改SQL语句的方法了

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

主动把l.tradeid转成utf8避免了被驱动表上的字符编码转换

十、order by是怎么工作的？

在市民表中，要查询城市是杭州的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄

表定义如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

SQL语句如下：

select city,name,age from t where city='杭州' order by name limit 1000;

1、全字段排序

city字段上创建索引之后，用explain命令查看这个语句的执行情况：

Extra这个字段中的Using filesort表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer

city索引示意图：

从上图中可以看到，满足city='杭州’条件的行，是从ID_X到ID_(X+N)的这些记录：

这个语句执行流程如下：

1.初始化sort_buffer，确定放入name、city、age这三个字段

2.从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X

3.到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer

4.从索引city取下一个记录的主键id

5.重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y

6.对sort_buffer中的数据按照字段name做快速排序

7.按照排序结果取前1000行返回给客户端

全字段排序流程图：

按name排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。sort_buffer_size就是MySQL为排序开辟的内存的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序

2、rowid排序

全字段排序算法有个问题，如果查询要返回的字段很多的话，那么sort_buffer里面放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差

SET max_length_for_sort_data = 16;

max_length_for_sort_data是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法

新的算法放入sort_buffer的字段，只有要排序的列和主键id

整个执行流程如下：

1.初始化sort_buffer，确定放入两个字段，即name和id

2.从索引city找到第一个满足city='杭州’条件的主键id，也就是ID_X

3.到主键id索引取出整行，取name、id这两个字段，存入sort_buffer

4.从索引city去下一个记录的主键id

5.重复3、4直到不满足city='杭州’条件为止，也就是ID_Y

6.对sort_buffer中的数据按照字段name进行排序

7.遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回个客户端

rowid排序流程图：

对比全字段排序流程图，rowid排序多访问一次表t的主键索引

3、全字段排序 vs rowid排序

如果MySQL担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据

如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放在sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据

MySQL的设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问

对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择

4、使用联合索引

在这个市民表上创建一个city和name的联合索引，对应的SQL语句如下：

alter table t add index city_user(city, name);

在这个索引里面，依然可以用树搜索的方式定位到第一个满足city='杭州’的记录，并且额外确保了，接下来按顺序取下一条记录的遍历过程中，只要city的值是杭州，name的值就一定是有序的

整个查询过程的流程就变成了：

1.从索引(city,name)找到第一个满足city='杭州’条件的主键id

2.到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回

3.从索引(city,name)取下一个记录主键id

4.重复2、3，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束

这个查询过程不需要临时表，也不需要排序

从图中可以看到，Extra字段中没有Using filesort了，也就是不需要排序了。而且由于(city,name)这个联合索引本身有序，所以这个查询也不用把4000行全都读一遍。在这个例子只需要扫描1000次

5、使用覆盖索引

覆盖索引是指索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据

创建一个city、name和age的联合索引，对应的SQL语句如下：

alter table t add index city_user_age(city, name, age);

1.从索引(city, name, age)找到第一个满足city='杭州’的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回

2.从索引(city, name, age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回

3.重复执行步骤2，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束

Extra字段里面多了Using index，表示的就是使用了覆盖索引，性能上会快很多

思考题：

1、对于下面这个InnoDB表T来说

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

如果要重建索引k，两个SQL语句如下：

alter table T drop index k;
alter table T add index(k);

如果要重建主键索引，SQL语句如下：

alter table T drop primary key;
alter table T add primary key(id);

对于上面这两个重建索引的做法，有什么不合适的？

参考答案：

重建索引k的做法是合理的，可以达到节省空间的目的。但是，重建主键索引的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句可以用这个语句代替：

alter table T engine=InnoDB;

2、有一个表的表结构定义类似这样的：

CREATE TABLE `T` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

为什么要创建ca、cb这两个索引？

解释：因为业务里面有这样的两种语句：

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

解释是否正确？

参考答案：

主键ab的聚簇索引组织顺序相当于order by a,b，也就是先按a排序，再按b排序，c无序

a|b|c|d

索引ca的组织是先按照c排序，再按a排序，同时记录主键（这里只有b），这个跟索引c的数据模型是一样的

c|a|主键部分b

索引cb的组织是先按照c排序，再按b排序，同时记录主键（这里只有a）

c|b|主键部分a

所以ca可以去掉，cb需要保留

3、如果某次写入change buffer机制，之后主机异常重启，是否会丢失change buffer和数据？

参考答案：

不会丢失，虽然只更新内存，但是在事务提交的时候，change buffer的操作也记录到redo log中了，所以崩溃恢复的时候，change buffer也能找回来

4、市民表建表语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`),
  KEY `city_name` (`city`,`name`)  
) ENGINE=InnoDB;

表里面已经有了city_name(city,name)这个联合索引，如果要查询杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前100条记录，SQL语句如下：

select * from t where city in ('杭州','苏州') order by name limit 100;

这个语句执行的时候会有排序过程吗？

参考答案：

这个过程中虽然有city_name(city,name)这个联合索引，但对于单个city内部，name是递增的。但是由于这条SQL语句是要同时查询杭州和苏州这两个城市，因此所有满足条件的name就不是递增的了。这条SQL语句需要排序

可以先执行select * from t where city='杭州' order by name limit 100;，这个语句是不需要排序的，客户端用一个长度为100的内存数组A保存结果，再执行select * from t where city='苏州' order by name limit 100;，用同样的方法保存到内存数组B，现在A和B是两个有序数组，采用归并排序的思想，得到name最小的前100值，就是最终需要的结果了