持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第9天，点击查看活动详情
面试官：数据库的回表你了解过吗？
回表查询
一般我们自己建的索引不管是单列索引还是联合索引，其实一个索引就对应着一颗独立的索引B+树，索引B+树的节点仅仅包含了索引里的几个字段的值以及主键值。即使我们根据索引树按照条件找到了需要的数据，那也仅仅是索引里的几个字段的值和主键值，万一你搞了一个select *还需要很多其他的字段，那还得走一个回表操作，根据主键跑到主键的聚簇索引里去找，聚簇索引的叶子节点是数据页，找到数据页里才能把一行数据的所有字段值提取出来。
假设你是类似select * from table order by xx1,xx2,xx3的语句，可能你就是得从联合索引的索引树里按照顺序取出来所有数据，接着对每一条数据都走一个主键的聚簇索引的查找，其实性能也是不高的。有的时候MySQL的执行引擎甚至可能会认为，你要是类似select * from table order by xx1,xx2,xx3的语句，相当于是得把联合索引和聚簇索引，两个索引的所有数据都扫描一遍了，那还不如就不走联合索引了，直接全表扫描得了，这样还就扫描一个索引而已。
但是你如果要是select * from table order by xx1,xx2,xx3 limit 10这样的语句，那执行引擎就知道了，你先扫描联合索引的索引树拿到10条数据，接着对10条数据在聚簇索引里查找10次就可以了，那么就还是会走联合索引的。
面试官：那覆盖索引又是什么呢？
覆盖索引
其实覆盖索引不是一种索引，他就是一种基于索引查询的方式罢了。他的意思就是针对类似select xx1,xx2,xx3 from table order by xx1,xx2,xx3这样的 语句，这种情况下，你仅仅需要联合索引里的几个字段的值，那么其实就只要扫描联合索引的索引树就可以了，不需要回表去聚簇索引里找其他字段了。所以这个时候，需要的字段值直接在索引树里就能提取出来，不需要回表到聚簇索引，这种查询方式就是覆盖索引。
也正是这样，所以在写SQL语句的时候，一方面是要注意一下也许会用到联合索引，但是是否可能会导致大量的回表到聚簇索引，如果需要回表到聚簇索引的次数太多了，可能就直接做成全表扫描不走联合索引了；一方面是尽可能还是在SQL里指定仅仅需要的几个字段，不要搞一个select *把所有字段都拿出来，甚至最好是直接走覆盖索引的方式，不要去回表到聚簇索引。即使真的要回表到聚簇索引，那也尽可能用limit、where之类的语句限定一下回表到聚簇索引的次数，就从联合索引里筛选少数数据，然后再回表到聚簇索引里去，这样性能也会好一些。
面试官：设计索引的时候一般要考虑哪些因素呢？
设计索引需要考虑的因素
首先，我们在针对业务需求建立好一张表的结构之后，就知道这个表有哪些字段，每个字段是什么类型的，会包含哪些数据。
接着设计好表结构之后，接下来要做的，就是要设计表的索引，这个设计索引的时候，我们要考虑第一点，就是未来我们对表进行查询的时候，大概会如何来进行查询？此时我们完全可以在表结构设计完毕之后，先别急着设计索引，因为此时你根本不知道要怎么查询表。
接着我们就可以进入系统开发的环节，也就是说根据需求文档逐步的把Java业务代码给写好，在写代码的过程中，现在一般我们都是用MyBatis作为数据持久层的框架的，肯定会写很多的MyBatis的DAO和Mapper以及SQL吧？
那么当系统差不多开发完毕了，功能都跑通了，此时就可以来考虑如何建立索引了，因为系统里所有的MyBatis的SQL语句都已经写完了，完全知道对每一张表会发起些什么样的查询语句。
那么这个时候，第一个索引设计原则就来了，针对SQL语句里的where条件、order by条件以及group by条件去设计索引。也就是说，where条件里要根据哪些字段来筛选数据？order by要根据哪些字段来排序？group by要根据哪些字段来分组聚合？
此时就可以设计一个或者两三个联合索引，每一个联合索引都尽量去包含上where、order by、group by里的字段，接着就要仔细审查每个SQL语句，是不是每个where、order by、group by后面跟的字段顺序，都是某个联合索引的最左侧字段开始的部分字段？比如有一个联合索引是INDEX(a,b,c)，此时发现有三个SQL，包含了where a=? and b=?，order by a,b，group by a这些部分，那么此时where、order by、group by后续跟的字段都是联合索引的最左侧开始的部分字段，这就可以了，说明每个SQL语句都会用上你的索引了。
但是在设计索引的时候还得考虑其他的一些问题，首先一个就是字段基数问题，举个例子，有一个字段他一共在10万行数据里有10万个值对吧？结果呢？这个10万值，要不然就是0，要不然就是1，那么他的基数就是2，为什么？因为这个字段的值就俩选择，0和1。假设你要是针对上面说的这种字段建立索引的话，那就还不如全表扫描了，因为你的索引树里就仅仅包含0和1两种值，根本没法进行快速的二分查找，也根本就没有太大的意义了，所以这种时候，选用这种基数很低的字段放索引里意义就不大了。
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
其次的话，尽量是对那些字段的类型比较小的列来设计索引，比如说什么tinyint之类的，因为他的字段类型比较小，说明这个字段自己本身的值占用磁盘空间小，此时在搜索的时候性能也会比较好一点。不过当然了，这个所谓的字段类型小一点的列，也不是绝对的，很多时候就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间，那也得去设计这样的索引，比较关键的其实还是尽量别把基数太低的字段包含在索引里，因为意义不是太大。
那当然了，万一要是真的有那种varchar(255)的字段，可能里面的值太大了，都放索引树里太占据磁盘空间了，此时仔细考虑了一下，发现完全可以换一种策略，也就是仅仅针对这个varchar(255)字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里而已。此时建立出来的索引其实类似于KEY my_index(name(20),age,course)，就这样的一个形式，假设name是varchar(255)类型的，但是在索引树里你对name的值仅仅提取前20个字符而已。
此时在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对就可以了。但是假如要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引了！group by也是同理的。
假设现在设计好了一个索引，非常棒，接着在SQL里这么写：where function(a) = xx，给索引里的字段a套了一个函数，此时还能用上索引吗？明显是不行了。所以尽量不要让查询语句里的字段搞什么函数，或者是搞个计算。
现在设计索引的时候需要注意的点都已经讲完了，其实就是好好设计索引，让查询语句都能用上索引，同时注意一下字段基数、前缀索引和索引列套函数的问题，尽量让查询都能用索引，别因为一些原因用不上索引了。
接着我们来看看索引设计好之后，接着你系统跑起来，有数据插入也有查询的情况，其实查询基本都能走索引一般问题都不会太大的，但是插入就有点讲究了，之前也跟大家说过，其实插入数据的时候，肯定会更新索引树。插入数据肯定有主键吧，那有主键就得更新聚簇索引树，插入一条数据肯定会包含索引里各个字段的值吧，那联合索引的B+树是不是也要更新？对了，不停的增删改数据，就会不停的更新索引树。
所以因为你插入的数据值可能根本不是按照顺序来的，很可能会导致索引树里的某个页就会自动分裂，这个页分裂的过程就很耗费时间，因此一般设计索引别太多，建议两三个联合索引就应该覆盖掉你这个表的全部查询了。否则索引太多必然导致增删改数据的时候性能很差，因为要更新多个索引树。
另外很关键一点，建议主键一定是自增的，别用UUID之类的，因为主键自增，那么起码聚簇索引不会频繁的分裂，主键值都是有序的，就会自然的新增一个页而已，但是如果用的是UUID，那么也会导致聚簇索引频繁的页分裂。
