在前一篇文章浅谈mysql索引(上)中，主要谈论了聚簇索引和二级索引的关系，并且讨论了关于自增主键在业务场景中的使用：自增主键相对于使用业务字段作为主键的好处。
这篇文章我们继续接着谈一谈索引的相关问题。在前一篇文章中已经介绍过了二级索引在查找到数据之后会进行一次“回表”操作。我们来看看下面这张表：
mysql> 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')
上述sql语句执行完成之后，会创建如下2颗B+树。
当我们执行如下的sql语句时
select ID from T where k between 3 and 5复制
按照之前说的理论，此时k是二级索引，需要进行回表操作，到主键索引树中得到完整的数据。但是实际上，这条sql语句并不需要回表，因为它需要的数据是ID，也就是主键索引，那么这样就省略了回表操作的这一个步骤。上述这种索引方式就是覆盖索引。考虑如下场景：每个人都会有一个身份证号码，根据身份证号码就能够很容易得到个人的身份信息，假设现在有这样一个热点查询：根据一个人的身份证号码来查找他的姓名。这种情况下，如果在（身份证号码，姓名）上面建立联合索引，将会减少一次回表的操作，即减少一次I/O操作，会使系统的性能提升。（联合索引：在B+树上首先使用第一个字段进行排序，如果第一个字段相同，那么则会使用第二个字段排序）。
索引是在存储引擎中实现的，我们都知道在innodb中索引具有最左前缀原则，这个原则产生的原因，我们应该从数据结构的角度进行考虑：B+树在排序时是如何进行排序的？这是个数据结构中的问题。首先需要理解一点：在mysql中，如果查询不走索引，那么它就会走全表扫描，如果一张表很大的话，走全表扫描很明显会产生巨大的消耗，这是最下策。那么，我们应该保证每条sql语句都尽量走索引，无论它是热点查询，还是不频繁的查询。但是给所有不频繁的查询均建立索引很显然是不可取的，此时，基于B+树的性质，索引天然就有最左前缀原则（这个最左前缀可以是联合索引的前N个字段，也可以是字符串索引的最左N个字符）。所以，我们在建立联合索引的时候，应该尽量考虑索引的“复用能力”，也就是说，一条索引应该尽可能的覆盖多的sql语句。因此，建立联合索引时的第一原则就是如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。如果现在有一个联合索引(a,b)，同时现在又有各自基于a或b的查询，那么此时，基于b的查询将无法使用(a,b)的联合索引，必须要为b单独建立一个索引。在这种情况下，建立联合索引的原则应该是尽可能的节约空间。比如现在有name和age两个字段，现在有各自基于name或者age的sql查询，同时也有基于name和age的联合查询，此时我们需要考虑的应该是从空间的角度，即建立(name,age)和age这两个索引。
我们对“最左前缀原则”已经有了一定的了解，下面我们来看看这条sql语句，前提：已经构建了(name,age)的联合索引
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
根据最左前缀原则，因为在name上使用了模糊匹配，所以此时索引无法对age生效了。在mysql5.6之前，当遇到这种情况时，会取当前匹配到“张%”的值，回到主键索引中得到完整的数据。但是在mysql5.6之后，引入了一个“索引下推”的概念，与覆盖索引的思想有某种类似，它也是对当前联合索引中多余的信息进行利用，比如(name,age)这个联合索引，当name匹配的是"张%"时，实际上这个联合索引对应的B+树中的age信息还没有使用呢，这不是白白浪费资源。索引下推指的就是当name模糊匹配之后，不会马上会主键索引中找对应的记录并查看age和ismale是否匹配，而是会从当前的聚合索引树中找到age字段，先查看age是否匹配。如果此时age已经不匹配了，很明显没有必要还回表一次到主键索引中去找具体的记录。
介绍了上面几种常见的情况，下面我们进一步考虑普通索引和唯一索引应该如何区分？唯一索引实际上就是在该字段上的值必须不能重复。比如身份证号码肯定不重复，那么就可以在身份证号码上面创建唯一索引。创建唯一索引的语法为：
CREATE UNIQUE INDEX uni_user_info_pass ON user_info(pass);复制
在某列或者某几列上创建唯一索引之后，在新插入数据时必须不能重复。普通索引一和唯一索引在sql的查询上，实际上性能差距不多。主要差距出现在更新操作上。首先需要了解innodb的更新过程WAL机制(write ahead log)：当需要更新某一行记录时，比如将age从9变成10：
会在内存中记录undo log（用于记录数据更新前的状态，后续也需要写入磁盘）
在内存数据页上修改记录值（需要更新的数据页如果不在内存中，则需要从磁盘中读数据）
写redo log（物理日志，环形，容量不能增长，写入的内容是哪些内存数据页发生了修改），将redo log顺序写入磁盘（肯定也是先写内存缓冲区再写入磁盘的，毕竟操作系统用户态下是没有办法直接写磁盘的）
写binlog（以追加的方式写入磁盘）
commit/flush（commit之后，将内存中的数据刷入盘中）
在上述过程中，我们注意到第2步，如果需要更新的数据页不在内存中，按照常规需要去磁盘中读取数据，但是在innodb中，会将更新操作缓存到change buffer中，这样就不需要从磁盘中读取原始数据页了。这个change buffer中的数据会在适当的时候被merge到磁盘的原始数据页上进行持久化。merge的时机包括：查询该数据页的时候，系统有后台线程会定期merge，在数据库正常关闭的过程中，也会执行merge操作。显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。那么什么时候能够使用change buffer呢？对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。change buffer的作用实际上就是：对于普通索引而言，在更新操作时，能够减少访盘次数。将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。很明显，当change buffer中待刷盘的数据越多时merge，mysql的性能会越好，所以普通索引的change buffer适用于写多读少的场景，比如日志或者账单类系统。而对于写少读多的情况，即便使用了change buffer，由于在查询时会自动触发merge，因此效果依然不好。change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
