05 | 浅谈mysql索引下

我们知道数据库索引的作用是用来提高查询效率，那索引应该怎么建立？有哪些建立技巧？，今天主要记述三种建立索引技巧！

一 . 覆盖索引

1. 什么是覆盖索引？
   如果一张表，id是主键索引，name为普通索引，当执行select id from t where name = 'neale’的时候，这个时候只需要查询id值，而id值已经在name树索引上了，因此可以直接返回查询结果而不需要进行回表。也就是说，在这个查询里面，name索引已经‘覆盖’ 了查询需求，这个时候我们称之为覆盖索引。
2. 覆盖索引的原理？
   查询一次索引树就能够得到结果，也就是说去除查询中的回表步骤
3. 覆盖索引的应用？
   a. 通过主键索引查询数据，主键索引即数据，数据即主键索引
   b. 经常需要查询某一列或者两列值的时候

二. 联合索引

1. 什么是联合索引？
   不同于单个索引，联合索引由多个字段组成，适用于最左前缀原则.
   最左前缀原则顾名思义最左优先，只要满足最左前缀就能够通过索引来加快检索，这个最左前缀可以是联合索引最左的N个字段，也可以是字符串索引的最左N个字符。
   假如建立一个(name,age,idcard)的联合索引 ，你可以使用name或者name,age或者name,age,idcard来使用索引
2. 联合索引的原理？
   通过使用多个字段建立索引来减少索引数量
3. 建立联合索引的时候如何安排索引捏字段的顺序？
   a. 如果通过调整顺序可以少维护一个索引，那么这个顺序往往是需要优先考虑采用的
   b. 其次应该考虑的就是空间问题了，如果不能通过调整顺序来减少索引的数量，则需要从空间上着手，尽量选择字段小的列作为单个索引
4. 索引下推
   mysql5.6之后引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录减少回表次数。
   
   假如查询语句为：
   select * from user where name like ‘张%’ and age = 10 and idmale = 1
   则5.6之前，上图中的1，2，3，4都需要进行回表，而5.6之后只有1,2回表

写在后面：

给表创建索引时，应该创建哪些索引，每个索引应该包含哪些字段，字段的顺序怎么排列，这个问题没有标准答案，需要根据具体的业务来做权衡。不过有些思路还是可供参考的：

1. 既然是一个权衡问题，没有办法保证所有的查询都高效，那就要优先保证高频的查询高效，较低频次的查询也尽可能的使用到尽可能长的最左前缀索引。可以借助pt-query-digest来采样统计业务查询语句的访问频度，可能需要迭代几次才能确定联合索引的最终字段及其排序。
2. 业务是在演进的，所以索引也是要随着业务演进的，并不是索引建好了就万事大吉了，业务发生变化时，我们需要重新审视当初建的索引是不是还依然高效，依然能满足业务需求。
3. 业内流传的有一些mysql 军规，其实这些并不是真正的军规，只是典型场景下的最佳实践。真正的军规其实就一条：高效的效满足业务需求。比如有个军规规定一个表上的索引数不超过5个，但如果我们现在有一些历史数据表、历史日志表，我们很明确的知道这些表上不会再有数据写入了，但我们的查询需求很多也很多样化，那我们在这些表上的索引数能不能超过5个？当然是没有任何问题的。当然关于这份军规还是要认真看一下的，但看的重点不是去记住它，而是要弄明白每一条军规它为什么这么规定，它这样规定是基于什么考虑，适用的场景和前提是什么，这些都弄明白了，你记不记得住这些军规都无所谓了，因为你已经把它溶化到了你的血液中，具体到自己的具体业务时游刃有余将是必然。

臆想面试：

1. mysql联合索引是怎么存储的？
   B-tree索引，数据存储在叶子节点，也就是说联合索引的数据列依次从左到右存储在索引树的叶子节点，
   数据按第一个字段排序存储，假如第一个字段有多个相同值，则再按照第二个字段进行排序存储，这也是为什么联合索引
   可以利用最左前缀原则。

2. 怎么让mysql的myisam引擎支持事务？
   猜测面试官想知道我们对数据库日志的了解？，提到事务，我们需要知道事务的ACID四大原则，而由于myisam没有redo log 所以AD实现不了，
   CI只能通过串行化，并且在数据库崩溃的时候也无法保证C，而仅仅只有串行化的I不要也罢

3. 为什么索引数量不能太多？
   一切都要从性能说起，少了影响检索性能，多了增加维护成本，降低性能！所以，貌似要找到传说中不多不少刚刚好的意思。
   回归正题，为啥不能太多？刚才说了维护成本高了，为啥维护成本高了？因为你每插入一条数据或者删除一条数据，所有的索引段都必须做出相应的添加和删除调整

4. 存在一张表T，有一个主键索引id和一个普通索引k
   如果你需要重建索引k，你可以这样写：
   alter table T drop index k;
   alter table T add index(k);
   如果你需要重建主键索引id，你可以这样写:
   alter table T drop primary key;
   alter table T add primary key(id);
   如何看待这两种写法？
   首先需要了解为啥要重新建立索引，因为索引可能因为删除或者分裂过程导致数据页存在空洞造成性能浪费，
   所以重建索引可以使所以更加紧凑更加高效
   所以上述两种写法，如果仅仅需要重建索引k是合理的，但如果需要重建主键索引，则重建索引k的步骤就多余了，
   因为不论是删除主键索引还是重建主键索引都相当于重新建表的过程

5. 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’ (‘ca’),
   key ‘cb’ (‘cb’)
   ) engine = innodb;
   小明进入一家新公司，发现有上面一个表，同事告诉他由于历史原因需要使用a, b做联合主键，这个他理解了，
   但他不理解的是为啥建立了c索引后，还要建ca, cb索引，然后同事告诉他，是因为业务中有这样两条语句：
   select * from t where c=n order by a limit 1;
   select * from t where c=n order by b limit 1;
   问题来了：ca, cb索引是否合理？
   表记录
   –a--|–b--|–c--
   1 2 3
   1 3 2
   1 4 3
   2 1 3
   2 2 2
   2 3 4
   主键 a，b的聚簇索引组织顺序相当于 order by a,b
   也就是先按a排序，再按b排序，c无序
   索引 ca 的组织是先按c排序，再按a排序，同时记录主键
   –c--|–a--|–主键b–
   2 1 1,3
   2 2 2,2
   3 1 1,2
   3 1 1,4
   3 2 2,1
   4 2 2,3
   索引 cb 的组织是先按c排序，再按b排序，同时记录主键
   –c--|–b--|–c--|–主键a–
   2 1 2,2
   2 3 1,3
   3 1 2,1
   3 2 1,2
   3 4 1,4
   4 3 2,3
   对于下面的语句
   select … from geek where c=N order by a
   走ca,cb索引都能定位到满足c=N主键
   而且主键的聚簇索引本身就是按order by a,b排序，无序重新排序。所以ca可以去掉
   select … from geek where c=N order by b
   这条sql如果只有 c单个字段的索引，定位记录可以走索引，但是order by b的顺序与主键顺序不一致，需要额外排序
   cb索引可以把排序优化调

上述多数内容借鉴学习有感于林晓斌老师mysql实战45讲！！！