笔试遇到联合索引，跪了....

大家好，我是小林。

前几天，在朋友圈发了一个关于联合索引的笔试题，我简单说了我的想法，然后看看朋友圈其他大佬的想法。

结果好家伙，出现了很多大佬一起来分析，每个人分析的思路都很不错，也是一个不错的学习素材，所以就今天整理了一下，给大家一起看看。

同学A的想法

建省份与idCare联合索引，省份最左边 limit 10之后，在用子查询过滤sex。

同学B的想法

其实还得校验下这个设计的，因为explain查询语句，有可能它最后没有采用索引，而采用了全表扫描，此时就得看查询计划的选择了。

同学C的想法

如果数据总量不是很大的情况下，sex加索引也是有效的，另外，题主没有说明表数据的组成情况如果表中只有百分之一的女性，那么sex放第一个我觉得会更快，假如条件是女性的情况下，面试官想听到的应该是思考的过程，而不是特定的结果.

同学D的想法

不考虑索引创建带来的存储开销，排序的单独建索引，性别可以建成bit索引。省份也可单独索引。相信数据库在投影上的查询优化。

同学E的想法

我觉得这里应该说明一下数据多少的场景，看表字段，性别、省份、身份证号，能推断出是存人的基本信息，当数据量很大的时候，省份、性别区分度很低，不说维护索引导致变更操作效率会低的情况，即使建了索引，由于区分度很低的情况可能也会导致全表扫描，创建的（省份、性别、身份证）索引也不会走，导致身份证也会无序需要重新排列。

所以我的结论是:

• 1.当数据量很大时，使用省份开头的复合索引也会全表扫时(全表扫描是)，可以就尝试创建idCard或是以idCard开始的复合索引。这样遍历当找到对应数据时无需进行排序。
• 2.当数据量比较小时，可以使用这里你回答的(shengfen、sex、idCard)索引，可以能全部走索引。
• 3.再次思考了下我思考的第一种方案，要全表扫，还是不行，太慢了，还是用你说得第二种方案更好，强制走索引，这样可以排除掉其他省份和性别的数据，然后可以优化
  • 3.1优化可以把这部分数据提前按省份(或者再加上性别)创建视图，或是新的表，在新的表或视图里创建idCard 的索引，这样查询的时候可以直接使用省份(或者和性别)直接在对应的视图里排序
  • 3.2还可以每次修改的时候，按省份性别按维护一个idCard 内含十条数据的最小堆(小顶堆)结构，这样直接查对应的堆结构即可。
• 4.最后写到这里的感悟，好像都是trade off取舍，要查的快，就要提前每次修改时维护新的有顺序的结构，不然就允许查的慢一点。
  • 4.1如果这种查的很多，可以选择每次维护下这个堆数据结构。
  • 4.2如果很少，可以就让其慢一点。
  • 4.3还可以折中，定时去维护这个结构。