优化索引的方法索引优化

目录\n1. 精准筛选创建索引的列\n 2. 联合索引中离散大字段前置\n3. 避免在频繁更新字段上建索引\n4. 巧用前缀索引\n5.\n6. 善用覆盖索引\n7. 主键索引以自增为宜\n8. 莫在索引列使用函数\n 9. 减少使用!=/notin//or 等操作\n10.\n11. 索引列设为 NOT NULL 约束\n12.\n1. 精准筛选创建索引的列\n什么样的字段适合构建索引呢？\n字段具备唯一性限制；\n用于查询条件的字段，若查询条件涉及多个字段，可创建联合索引。\n由于数据库能借助索引迅速筛选出符合要求的数据，无需遍历全表。当查询条件涵盖多个字段时，联合索引可进一步提升查询效率。联合索引依照字段在索引里的定义顺序匹配，若查询条件的字段顺序与联合索引顺序不一致，可能无法充分发挥索引优势。\n例如，存在一个订单表，常依据订单状态与客户 ID 来查询订单，那么构建一个(订单状态, 客户 ID)的联合索引会大有裨益。当查询条件是“订单状态 = '已发货' AND 客户 ID = 123”时，数据库能够高效运用联合索引。\n当一个字段频繁用于 GROUP BY 操作或 ORDER BY 操作时，为其创建索引可避免查询时额外的排序操作。因为 B+树结构的索引本身依索引字段的值有序存储。\n例如，若有一个销售表，经常需要按照产品类别进行分组统计销售额，或者按照销售日期进行排序查询，那么在产品类别和销售日期字段上创建索引，能够极大提高此类查询的性能。\n 2. 联合索引中离散大字段前置\n离散大的字段即变量各个取值之间差异程度较大的列，可通过 count(distinct(列名))/count() 来计算。\n例如，假设有一个用户表，包含“性别”和“用户 ID”这两个字段。若经常基于这两个字段进行联合查询，将“用户 ID”置于联合索引的前面更为合适。\n因为在查询时，数据库首先会依据离散程度高的字段进行筛选，能更有效地缩小结果集范围。如此可提升查询效率，减少不必要的磁盘 I/O 和数据处理。并且 MySQL 有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。那这个索引就是无效的。\n所以对于离散程度高的字段，其在索引中的区分度更佳，能够更快地定位到符合条件的数据。\n3. 避免在频繁更新字段上建索引\n索引字段频繁修改，由于要维护 B+Tree 的有序性，那么就需要频繁地重建索引，这个过程会影响数据库性能。\n4. 巧用前缀索引\n前缀索引是一种通过选取字段中字符串的前几个字符来创建索引的方式。使用前缀索引的主要缘由是为了缩减索引字段所占用的存储空间。由于索引通常需要在内存和磁盘中进行存储与处理，较小的索引字段大小意味着在一个索引页中能够容纳更多的索引值，从而提高索引的查询速度。\n例如，对于一个包含很长字符串描述的字段，如文章的内容摘要，如果直接对整个字符串创建索引，会占用大量的空间。但如果只选取摘要字符串的前若干个字符作为前缀建立索引，就能在保证一定查询效率的同时，显著减小索引的存储空间。\n然而，前缀索引也存在一些局限。\n首先，排序操作通常无法使用前缀索引。通常需要对整个字段的值进行排序，而前缀索引只包含了字段的部分字符，无法准确反映整个字段的排序顺序。\n其次，前缀索引也不能用作覆盖索引。覆盖索引是指索引中包含了查询所需的所有列数据，从而无需回表查询实际的数据行。由于前缀索引只包含了字段的部分内容，不满足覆盖索引的要求。\n5.\n当查询经常基于多个列的组合条件时，创建联合索引是一个有效的优化策略。联合索引可以提高多个列之间的查询效率。另外，使用联合索引时，存在最左匹配原则，即在查询条件中使用了联合索引的第一个字段，索引才会被使用，如果不是按照索引的最左列开始查找，则无法使用索引。\n所以应该把区分度最高的字段放在最左边：这是因为在查询时，数据库会从左到右依次使用索引字段进行匹配和筛选。区分度高的字段放在左边能够更有效地缩小查询范围。\n除此之外，联合索引的最左匹配原则，在遇到范围查询并建立了索引，但在查询时将该字段与一个数字进行比较，数据库就会进行隐式转换，将字符串转换为数字来进行比较。此时，可能就无法利用该字段的索引，从而降低查询效率。 \nselect * from user where id = 210401\n具体来说，以 MySQL 为例，其隐式转换的规则如下：\n两个参数至少有一个是 NULL 时，比较的结果也是 NULL，特殊的情况是使用 = 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换。\n两个参数都是字符串，会按照字符串来比较，不做类型转换。\n两个参数都是整数，按照整数来比较，不做类型转换。\n十六进制的值和非数字做比较时，会被当做二进制串。\n有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 TIMESTAMP。\n有一个参数是 DECIMAL 类型，如果另外一个参数是 DECIMAL 或者整数，会将整数转换为 DECIMAL 后进行比较，如果另外一个参数是浮点数，则会把 DECIMAL 转换为浮点数进行比较。\n所有其他情况下，两个参数都会被转换为浮点数再进行比较。\n为了避免索引隐式转换带来的性能问题，在编写 SQL 查询时，应注意查询条件中数据类型的匹配，尽量进行显式的类型转换，确保与字段的数据类型一致。这样可以充分利用索引，提高查询的性能。同时，在设计数据库表结构时，也应合理规划字段的数据类型，以减少不必要的类型转换。\n11. 索引列设为 NOT NULL 约束\n索引列存在 NULL 会使优化器在做索引选择时更加复杂，难以优化。因为可为 NULL 的列会导致索引、索引统计和值比较变得更为复杂。例如，在进行索引统计时，count 会省略值为 NULL 的行；当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节，在某些存储引擎中甚至可能导致固定大小的索引变成可变大小的索引。\n然而，需要注意的是，将 NULL 列改为 NOT NULL 带来的性能提升可能比较小，除非确定这会导致问题，否则不必优先进行此优化操作。但在设计表结构时，仍建议尽量将索引列设置为 NOT NULL，并为可能为空的列设置合理的默认值，以提高数据库的性能和可维护性。常见的默认值设置方式如：整形可使用 0 作为默认值，字符串可默认使用空字符串等。\n12.\n使用 like xx%这种右模糊匹配可以使用索引，但是像 like %xx、like %xx%这种左模糊和左右模糊匹配的方式会导致索引失效，进行全表查询。