优化索引的方法

目录

1.选择合适的列创建索引

 2.在创建联合索引时，将取值离散大的字段放在前面

3.经常更新的字段不用创建索引

4.使用前缀索引

5.利用联合索引最左匹配原则 

6.使用覆盖索引

7.主键索引最好是自增的

8.不要在索引列上面使用函数

 9.尽量不使用 != /not in/<>/or 等操作

10.注意隐式转换会导致全表扫描 

11.索引列要为NOT NULL 约束

12.使用like语句会导致索引失效 

---

1.选择合适的列创建索引

什么样的字段适合做索引？

• 字段有唯一性限制的；
• 经常用于 WHERE 查询条件的字段，如果查询条件不是一个字段，可以建立联合索引。

因为数据库可以利用索引快速筛选出符合条件的数据，而不必遍历整个表。当查询条件涉及多个字段时，建立联合索引可以进一步提高查询效率。联合索引按照字段在索引中的定义顺序进行匹配，如果查询条件的字段顺序与联合索引的顺序不一致，可能无法充分利用索引的优势。

例如，如果有一个订单表，经常根据订单状态和客户 ID 来查询订单，那么创建一个 (订单状态, 客户 ID) 的联合索引会很有帮助。当查询条件是“订单状态 = '已发货' AND 客户 ID = 123”时，数据库可以高效地使用联合索引。

• 经常用于 GROUP BY 和 ORDER BY 的字段

当一个字段经常用于 GROUP BY 操作（分组）或 ORDER BY 操作（排序）时，为其创建索引可以避免在查询时进行额外的排序操作。因为 B+树结构的索引本身就是按照索引字段的值有序存储的。

例如，如果有一个销售表，经常需要按照产品类别进行分组统计销售额，或者按照销售日期进行排序查询，那么在产品类别和销售日期字段上创建索引，可以大大提高这类查询的性能。

 2.在创建联合索引时，将取值离散大的字段放在前面

离散大的字段即变量各个取值之间差异程度较大的列，区分度可以使用 count(distinct(列名))/count(*) 来计算。

例如，假设有一个用户表，包含“性别”（取值通常为“男”和“女”，离散程度低）和“用户 ID”（每个用户具有唯一的 ID，离散程度高）这两个字段。如果经常基于这两个字段进行联合查询，将“用户 ID”放在联合索引的前面更为合适。

因为在查询时，数据库首先会根据离散程度高的字段进行筛选，能够更有效地缩小结果集范围。这样可以提高查询效率，减少不必要的磁盘 I/O 和数据处理。而且MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。那这个索引就是无效的。

所以对于离散程度高的字段，其在索引中的区分度更好，能够更快地定位到符合条件的数据。

3.经常更新的字段不用创建索引

索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

4.使用前缀索引

前缀索引是一种通过选取字段中字符串的前几个字符来创建索引的方式。使用前缀索引的主要原因是为了减少索引字段所占用的存储空间。由于索引通常需要在内存和磁盘中进行存储和处理，较小的索引字段大小意味着在一个索引页中能够容纳更多的索引值，从而提高索引的查询速度。

例如，对于一个包含很长字符串描述的字段，如文章的内容摘要，如果直接对整个字符串创建索引，会占用大量的空间。但如果只选取摘要字符串的前若干个字符作为前缀建立索引，就能在保证一定查询效率的同时，显著减小索引的存储空间。

然而，前缀索引也存在一些限制。

首先，order by 操作通常无法使用前缀索引。这是因为 order by 通常需要对整个字段的值进行排序，而前缀索引只包含了字段的部分字符，无法准确反映整个字段的排序顺序。

其次，前缀索引也不能用作覆盖索引。覆盖索引是指索引中包含了查询所需的所有列数据，从而无需回表查询实际的数据行。由于前缀索引只包含了字段的部分内容，不满足覆盖索引的要求。

5.利用联合索引最左匹配原则 

当查询经常基于多个列的组合条件时，创建联合索引是一个有效的优化策略。联合索引可以提高多个列之间的查询效率。另外，使用联合索引时，存在最左匹配原则，即在查询条件中使用了联合索引的第一个字段，索引才会被使用，如果不是按照索引的最左列开始查找，则无法使用索引。

所以应该把区分度最高的字段放在最左边：这是因为在查询时，数据库会从左到右依次使用索引字段进行匹配和筛选。区分度高的字段放在左边能够更有效地缩小查询范围。

除此之外，联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。但是对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

所以当同时出现这两种范围查询时，应该把后者放在左边，这样才能让索引更有效的发挥作用。

6.使用覆盖索引

覆盖索引，即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

例如，假设只需要查询商品的名称和价格，为了避免回表，可以创建“商品 ID、名称、价格”的联合索引。因为这个联合索引包含了查询所需的全部信息，所以数据库在执行查询时，可以直接从这个联合索引中获取数据，而无需根据索引找到对应的主键值，再通过主键值去聚簇索引中查找完整的行记录。

使用覆盖索引的显著优势在于减少了大量的 I/O 操作。由于不需要回表获取整行记录的所有信息，节省了磁盘 I/O 开销，提高了查询的效率。特别是在处理大量数据和频繁查询的场景中，这种优化效果更为明显。

7.主键索引最好是自增的

InnoDB 中，主键索引默认是聚簇索引，数据存放在 B+Tree 的叶子节点上，同一叶子节点内的数据按主键顺序存放。

当使用自增主键时，每次插入新数据，它会按顺序追加到当前索引节点的位置。因为是顺序添加，不需要移动已有的数据，当当前页面写满，会自然开辟新页面。这种追加式的插入操作效率极高，减少了数据调整和页面管理的复杂性。

若使用非自增主键，由于主键值随机，新数据可能插入到现有数据页的中间位置。这就需要移动其他数据来为新数据腾出空间，甚至可能导致页分裂，即从一个页面复制数据到另一个页面。页分裂不仅操作复杂，耗费资源，还可能产生大量内存碎片，使索引结构变得松散不紧凑，进而影响查询效率。

例如，假设有一个订单表，主键为订单号。如果订单号是自增的，新订单数据会依次添加到索引的末尾，简单高效。但如果订单号是随机生成的，新订单可能会插入到已有订单中间，导致数据调整和页面变动，增加系统开销。

8.不要在索引列上面使用函数

当在索引列上进行计算、使用函数或进行其他操作时，数据库通常无法直接利用已创建的索引来加速查询，从而导致索引失效，数据库不得不采用全表扫描的方式来获取数据。

例如，如果在一个包含 age 列（已建立索引）的表中，执行以下查询：

SELECT * FROM users WHERE FLOOR(age) = 25; 

 由于对 age 列使用了 FLOOR 函数进行操作，数据库无法直接使用 age 列的索引，而必须扫描整个表来找到满足条件的记录。

再比如，如果对索引列进行计算： 

SELECT * FROM products WHERE price * 2 > 100; 

同样会导致索引失效，因为数据库不能基于计算后的结果直接在索引中进行查找。

为了避免这种情况，应尽量在查询条件中直接使用索引列的原始值，以充分利用索引提高查询性能。

例如，正确的写法应该是：

SELECT * FROM users WHERE age = 25; 
SELECT * FROM products WHERE price > 50; 

 9.尽量不使用 != /not in/<>/or 等操作

!= 和 <> 操作符：

• 当使用这些不等于操作符时，数据库可能无法有效地利用索引进行查询优化。特别是在数据量大且索引不匹配的情况下，可能导致全表扫描，降低查询性能。

not in 操作符：

• 如果 not in 列表中的值数量较多或者值的分布不均匀，可能会导致查询效率低下。数据库可能难以基于索引来处理这样的条件。

or 操作符：

• 当在一个查询条件中使用 or 连接多个条件，并且这些条件涉及的列没有合适的索引时，数据库可能无法有效地优化查询，从而可能选择全表扫描。

然而，在某些特定的、数据量较小或者索引设置合理的情况下，使用这些操作符可能不会对性能产生明显的影响。但在一般情况下，为了确保良好的查询性能，应尽量避免使用它们，或者尝试通过其他方式重写查询条件来达到相同的目的。 

10.注意隐式转换会导致全表扫描 

当查询条件中操作符两侧的数据类型不匹配时，数据库会自动进行隐式转换以使操作数兼容。然而，这种隐式转换可能会破坏索引值的有序性，导致优化器放弃使用索引，而选择全表扫描。

例如，对于某个字段，其实际数据类型为字符串类型（如varchar）并建立了索引，但在查询时将该字段与一个数字进行比较（没有进行显式的类型转换），数据库就会进行隐式转换，将字符串转换为数字来进行比较。此时，可能就无法利用该字段的索引，从而降低查询效率。 

select * from user where id = 210401

具体来说，以 MySQL 为例（不同的数据库管理系统可能有略微不同的行为），其隐式转换的规则如下：

• 两个参数至少有一个是NULL时，比较的结果也是NULL，特殊的情况是使用<=>对两个NULL做比较时会返回 1，这两种情况都不需要做类型转换。
• 两个参数都是字符串，会按照字符串来比较，不做类型转换。
• 两个参数都是整数，按照整数来比较，不做类型转换。
• 十六进制的值和非数字做比较时，会被当做二进制串。
• 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为TIMESTAMP。
• 有一个参数是DECIMAL类型，如果另外一个参数是DECIMAL或者整数，会将整数转换为DECIMAL后进行比较，如果另外一个参数是浮点数，则会把DECIMAL转换为浮点数进行比较。
• 所有其他情况下，两个参数都会被转换为浮点数再进行比较。

为了避免索引隐式转换带来的性能问题，在编写 SQL 查询时，应注意查询条件中数据类型的匹配，尽量进行显式的类型转换，确保与字段的数据类型一致。这样可以充分利用索引，提高查询的性能。同时，在设计数据库表结构时，也应合理规划字段的数据类型，以减少不必要的类型转换。

11.索引列要为NOT NULL 约束

索引列存在 NULL 会使优化器在做索引选择时更加复杂，难以优化。因为可为 NULL 的列会导致索引、索引统计和值比较变得更为复杂。例如，在进行索引统计时，count 会省略值为 NULL 的行；当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节，在某些存储引擎中甚至可能导致固定大小的索引变成可变大小的索引。

然而，需要注意的是，将 NULL 列改为 NOT NULL 带来的性能提升可能比较小，除非确定这会导致问题，否则不必优先进行此优化操作。但在设计表结构时，仍建议尽量将索引列设置为 NOT NULL，并为可能为空的列设置合理的默认值，以提高数据库的性能和可维护性。常见的默认值设置方式如：整形可使用 0 作为默认值，字符串可默认使用空字符串等。

12.使用like语句会导致索引失效 

使用like xx%这种右模糊匹配可以使用索引，但是像like %xx、like %xx%这种左模糊和左右模糊匹配的方式会导致索引失效，进行全表查询。