简介相信大家都知道索引可以加快数据查询速度，但是有时候如果索引设计不当，也可能会导致索引失败并扫描全表数据，最终会导致系统性能下降。因此，我们需要在索引设计阶段充分考虑各种可能出现的情况，尽量避免因索引设计缺陷导致后期数据查询性能问题。本文总结了7条实用的Mysql索引设计原则，相信大家在设计索引时可以参考。索引设计原则数据库表设计好后，不要急于马上设计表的索引，因为此时，你其实并不知道以后可能存在于这张表上的查询条件是什么。因此，我们需要根据实际的产品需求来开发业务代码。在这个过程中，我们不可避免地会涉及到数据库的持久化操作，也就是我们常说的CRUD。我们写完对应的Mapper接口和SQL之后，基本就确定了哪些字段是条件字段，哪些字段是排序字段，哪些字段是分组字段。这些字段确定之后，我们就可以进行数据库表的索引设计了。关于如何设计索引，这里有7条非常实用的索引设计原则，相信大家在实际项目中都能用得上。原则一：根据SQL语句中where条件、orderby条件、groupby条件对应的字段设计索引。当我们的SQL语句中出现where条件、orderby条件和groupby条件的时候，就意味着我们需要用sql语句来过滤数据（where条件），根据哪些字段排序（orderby条件）以及根据哪些字段进行排序。字段被分组和聚合（按条件分组）。所以我们设计的索引需要尽可能的覆盖这些字段，以便在查询数据的时候通过这些字段来使用索引。假设我们有这么一张表衣服，可以用来查询衣服，那么在设计索引的时候，我们需要根据实际查询的需求，在对应的字段上创建索引。那么对于衣服表，一般会在c_brand（品牌）、c_type（类型）、c_size（尺码）等字段上创建索引，因为它们是最常用的过滤条件。另外，可以考虑在price字段上排序。这也是一个很常见的过滤器。原则二：在基数比较大的字段上创建索引，把基数大的字段放在最左边。什么是基数比较大的字段？其实就是一个值比较多的字段，或者说字段值区分度比较高的字段。我们可以用一个简单的公式来判断一个领域的歧视程度。区分度等于count(distinctSpecificcolumns)/count(*)，表示字段不重复的比例。也就是说，如果该字段包含大量变化的数据，则更适合做索引，因为这样才能发挥索引B+树的潜力。你为什么这么说？假设有这样一张员工表，其中包含性别字段i_gender，它的值只有0：男，1：女。我们都知道Mysql的索引结构是通过B+树实现的，而B+树背后的核心本质思想其实就是二分查找。二分查找需要对大基数的数据进行排序，即辨别度高。但是，如果字段中只有0和1，则基数比较小，无法发挥索引树检索的效率。mysql认为这种索引树不如全表查询快。另外需要特别注意的是，对于差异化程度高的字段，应该放在联合索引的左侧，因为这样可以更快的过滤掉更多的无效数据，从而提高索引的使用效率。仍以员工信息为例，员工表中研究生院字段的歧视程度远高于种族字段。在设计指标时，我们需要在设计联合指标时将研究生院领域模拟到种族领域。在左侧，这可以更快地过滤掉无效数据。原则三：如果SQL中出现JOIN操作，则JOIN的字段必须有索引，并且字段的类型和字符集必须一致。数据库JOIN是数据记录遍历的常用SQL操作。假设平台有用户表和订单表。如果此时想要获取用户的订单信息，可以使用JOIN操作来完成操作。但是在使用JOIN的过程中，如果参与JOIN的表过多，对应的结果可能是笛卡尔积。Mysql优化器真的很难选择哪个是最好的执行计划，就像找对象一样。如果只有一个可供选择，就没有什么可担心的。如果有10个可供选择，那就很头疼了。不知道该选哪个，所以要避免表太多的JOIN。还有一个很重要的一点就是要在JOIN字段上建立索引，否则会扫描全表。同时JOIN字段的类型和字符集必须保持一致，避免JOIN过程中可能隐式类型转换导致无法使用索引的后果。原则4：如果SQL中出现JOIN操作，则JOIN的字段必须有索引，并且字段的类型和字符集必须一致。数据库JOIN是数据记录遍历的常用SQL操作。假设平台有用户表和订单表。如果此时想要获取用户的订单信息，可以使用JOIN操作来完成操作。但是在使用JOIN的过程中，如果参与JOIN的表过多，对应的结果可能是笛卡尔积。Mysql优化器真的很难选择哪个是最好的执行计划，就像找对象一样。如果只有一个可供选择，就没有什么可担心的。如果有10个可供选择，那就很头疼了。不知道该选哪个，所以要避免表太多的JOIN。还有一个很重要的一点就是要在JOIN字段上建立索引，否则会扫描全表。还有一点很重要，JOIN使用的字段的类型和字符集需要保持一致，否则可能会发生隐式类型转换，无法访问索引。原则5：尽量在字段类型值比较小的字段上建立索引。索引本身也会占用磁盘空间，所以如果能在字段类型比较小的字段上建立索引，那么对应的索引占用的空间会更少，相应的数据检索效率也会更高。但这不是绝对的。如果有区分度较高的字段，但是字段类型比较多，那么我们还是会在区分度较高的字段上建立索引，但是可以做一些折衷。比如我们可以把该字段的前10个字符作为一个索引，这样我们就可以对一个区分度高的字段建立索引，又不会占用太多的磁盘空间。原则6：建立的索引越多越好。有些同学在设计索引的时候希望把所有的字段都加索引。他们总是认为索引越多，性能越好。事实上，在真实场景中并非如此。我们都知道，索引就像一本书的目录，就像一棵树的目录在书里占纸一样，索引也要占用磁盘空间存储，索引太多会浪费资源。另外，索引过多会降低性能，因为在数据插入的过程中，如果创建过多的索引，会更新多棵索引树。在这个过程中，如果数据没有按顺序插入，那么仍然会出现导致数据页分裂的问题。因此，我们尽量用两个或三个联合索引来覆盖所有的查询场景。原则七：使用字符串前缀创建索引有些字段类型的长度比较长，所以字段分区比较大，所以这些字段更适合做索引。但是也因为字段的长度，创建的索引会占用比较大的磁盘空间。其实只要字段区分度足够高，就没有必要对整个字段进行索引。我们可以截取字段中指定个数的字符作为检索条件的索引。需要截取多少个字符，取决于截取的字符串能否保持比较高的辨别度。做一个决定。SELECTCOUNT(DISTINCTLEFT(order_code,10))/COUNT(*)FROMorder总结本文主要总结了设计索引时需要考虑的一些设计原则。其实索引设计无外乎两点，一是希望使用两个或三个联合索引覆盖数据检索的各个场景，避免检索时没有索引导致数据检索效率低下的问题。此外，希望在实际的SQL操作中尽可能避免索引失败。索引但实际上不起作用。掌握了这两个原则后，相信你可以轻松设计索引。
