在设计索引时需要考虑的几个关键因素：哪些字段适合建立索引，如何创建索引，以及创建索引后如何合理使用它们。
1. 设计索引的时候，我们一般要考虑哪些因素
首先，设计索引的前提是我们已经完成了表结构的设计。此时，我们已经知道了表中的字段、字段类型和包含的数据。接下来，我们的任务是为这些表设计索引。
1.1. 1️⃣字段顺序符合索引的最左前缀规则
设计索引时，需要考虑的第一点是：我们未来如何查询这个表？虽然在表设计阶段，很多人可能还不知道具体会如何查询表，但这并不意味着我们不能开始设计索引。
在实际的系统开发过程中，我们根据需求文档逐步编写业务代码。通常我们会使用MyBatis作为数据持久层框架，在编写代码时，我们会创建DAO、Mapper以及SQL语句。等到系统功能开发完成，SQL语句基本确定时，我们就可以开始考虑索引的设计。
此时，设计索引的第一个原则是：根据SQL语句中的 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
那么这些SQL语句中的字段顺序就符合联合索引的最左侧字段顺序，表示这些SQL语句可以有效利用该联合索引。
总结一下，设计索引时的第一条原则就是确保你的SQL语句中的where、order by和group by条件字段顺序符合索引的最左前缀规则，这样才能最大程度地提升查询性能。
1.2. 2️⃣优先使用基数较大的字段建立索引
1.3. 3️⃣字段类型小的字段建立索引
1.4. 4️⃣长字符串字段采用前缀索引
之前我们提到，设计索引时，最好确保SQL语句中的where、order by和group by字段顺序能够匹配联合索引的最左侧字段，这样才能确保索引得到充分利用。
但在设计索引时，还需要考虑一些额外的细节，首先是字段的基数问题。举个例子，假设有一个字段在10万行数据中有10万个值，但这些值只是0和1，那么它的基数就只有2。对于这种基数非常低的字段，建立索引的意义并不大，因为索引树中的值很少，根本无法发挥B+树快速二分查找的优势，最终查询效率甚至可能不如全表扫描。
因此，在设计索引时，应该尽量选择基数较大的字段，也就是值种类较多的字段，这样才能最大限度地利用B+树进行高效查找。
其次，在选择字段时，我们应优先考虑类型较小的字段来建立索引。比如说tinyint类型的字段，因其占用的磁盘空间较小，查询时的性能也较好。
当然，并不是所有情况下都能遵循这一原则。如果你需要为 varchar(255) 类型的字段建立索引，虽然它占用的空间较大，但也可能是业务需求的关键字段，此时仍然需要为其设计索引。需要注意的，还是要避免将基数较低的字段放入索引中，因为它们带来的性能提升有限。
另外，如果你确实有一个较长的varchar(255)字段，担心其占用过多磁盘空间，另一种策略是仅为该字段的前几个字符建立索引。例如，针对varchar(255)类型的name字段，你可以只对前20个字符进行索引，形式像是KEY my_index(name(20), age, course)。这样，索引树中只包含name字段前20个字符的数据。
这样做的好处是，如果你在where条件中使用name进行查询时，系统会通过索引树先找到前20个字符匹配的数据，然后再回到聚簇索引中进行完整的比对。这样可以提高查询效率。然而，如果你的查询涉及order by name或group by name，由于索引树中的name值只包含前20个字符，排序和分组操作就无法利用索引了。
总之，重点是：
对于基数很低的字段，尽量不要将其放入索引，因为没有实际的性能提升；
对于较长的字符串类型字段，可以通过创建前缀索引，索引字段的部分内容来节省磁盘空间，同时仍能在where查询中使用索引，但需要注意order by和group by操作无法利用索引。
这些都是在设计索引时需要注意的细节，应用到实际开发中，会提升你的代码执行效率。
1.5. 5️⃣尽量避免在查询语句中对字段进行函数操作或者计算
1.6. 6️⃣不要设计过多的索引
1.7. 7️⃣主键最好使用自增的方式
之前我们讲解了如何根据查询语句来设计索引，确保查询能够高效地使用索引，同时也讨论了字段基数和前缀索引的设计，强调了在设计索引时不要包含基数低的字段，并且对长字符串字段采用前缀索引来提高where查询的效率。
还有什么其他的索引设计原则呢?有的~~~
首先，我们来探讨一个常见的问题：假设你设计了一个索引，然后在SQL语句中这样写：where function(a) = xx，你可能会认为索引还能生效吗？显然，答案是不能的。原因很简单，当你在查询条件中对字段a使用了函数或者计算，索引就无法被有效利用了。因此，在设计索引时，要尽量避免在查询语句中对字段进行函数操作或者计算。
那么，索引设计的原则其实已经很清楚了：设计时要确保查询语句能够使用上索引，避免低基数字段，合理设计前缀索引，并避免在查询条件中使用函数或计算。这样可以保证你的查询在大多数情况下都能走索引，从而提高查询性能。
接着，我们来说一下索引设计好之后的数据操作。通常情况下，当你进行查询操作时，索引的使用是没有问题的。但插入数据时就需要特别注意了。因为每当插入数据时，相关的索引树都会被更新。
例如，在插入数据时，如果有主键，那么聚簇索引树就需要更新；如果插入的数据包含索引列的值，那么相关的联合索引树也需要更新。随着数据的增删改，索引树会不断更新和维护。插入数据时，由于数据值可能不是按照顺序插入的，可能会导致某个索引页发生分裂，而页分裂的过程是非常耗费时间的。
因此，在设计索引时，建议不要设计过多的索引。一般来说，最多设计两到三个联合索引，这样就可以覆盖大部分查询操作。如果索引设计得过多，那么每次插入数据时需要更新的索引就会变得非常多，从而降低插入性能。
还有一个重要的建议是，主键最好使用自增的方式，而不是使用UUID。使用自增主键时，聚簇索引的页不会频繁分裂，因为主键值是有序的，每次插入数据时，新的数据就会追加到索引页的末尾。而如果使用UUID作为主键，由于其值是随机的，就会导致聚簇索引频繁地发生页分裂，从而影响性能。
1.8. 索引设计通用原则总结
总结一下，本周我们讨论了以下几个关键的索引设计原则：
设计索引时，确保查询语句能够高效地使用索引；
避免低基数字段的索引设计；
优先在字段类型小的字段建立索引；
对长字符串类型字段可以设计前缀索引；
避免在查询条件中使用函数或计算，确保索引能生效；
索引设计时要控制索引的数量，不要设计过多的索引；
主键最好使用自增，避免使用UUID，以减少聚簇索引的频繁分裂。
在实际工作中能够灵活应用这些原则，能设计出高效的索引结构。
评论区