order by 多个字段使用联合索引 order by字段是否建立索引

转载

数据挖掘者 2024-03-19 00:05:19

哪些情况适合创建索引

字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的
频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立 组合索引 。
UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。
多表 JOIN 连接操作时，创建索引注意事项

首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。

连接字段上创建索引是指在被驱动表上创建索引，因为是先从驱动表查出数据后，拿着数据根据ON的条件去被驱动表中查数据（这里面你可以理解为驱动表查出一条数据就去被驱动表中检索，但是实际上为了减少随机IO的发生，可能会触发MRR优化，就是先从驱动表查出多条数据，存在缓存中，这多条数据先按主键进行排序，再一下子到被驱动表中检索）

连接分为内连接和外连接，外连接的话，比较好判断被驱动表，左连接的左边就是驱动表，右边就是被驱动表，右连接与之相反，但是对于内连接我们是没发判断，驱动表和被驱动表的，因为在SQL执行前会mysql的查询优化器会分析两个表分别作驱动表和被驱动表时，谁的执行最好，最终是由查询优化器决定的，但是通常遵循”小表驱动大表“原则
使用列的类型小的创建索引

这个还是比较好理解的，因为索引即数据，数据即索引，一个页的大小是16kb，使用的索引列类型越小，一个页中能够存储的数据就越多，而页是内存和磁盘交互的基本单位，那么IO从磁盘读到内存的数据就越多
使用字符串前缀创建索引

拓展：Alibaba《Java开发手册》
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本
区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

引申另一个问题：索引列前缀对排序的影响
如果对前缀索引的索引列进行排序的话，会导致索引失效，这个也可以理解，前缀索引只是对截取的字段排序。

区分度高(散列性高)的列适合作为索引
使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。
在多个字段都要创建索引的情况下，联合索引优于单值索引

通常是联合索比单列索引强的，但是这个不能说的太绝对。比如对下面这个sql
select c from table where a=’1‘ and b =‘2’
如果我们对a和b分别建立了索引index_a ,索引 index_b,和联合索引index_ab
那么查询优化器在生成SQL执行计划之前，会分别分析三种索引＋全表扫描，这四种情况，谁的执行成本更低，最终选择最好的一个，但是这个是由查询优化器决定的
另外，如果你没有对a、b建立联合索引，那么mysql也会尝试给你建立联合索引，并分析它的执行成本，这是mysql自身对索引的一种优化
限制索引的数目