美文网首页我爱编程
(转载)联合索引优化多条件查询

(转载)联合索引优化多条件查询

作者: Wide_Star | 来源:发表于2018-05-24 16:31 被阅读0次

转载自:联合索引优化多条件查询


联合索引是由多个字段组成的组合索引。若经常需要使用多个字段的多条件查询(WHERE col1 = … AND col2 = … AND col3 = …),可以考虑使用联合索引。


现在数据表myIndex中i_testID是主键列,其他列无任何索引:

这里写图片描述

多条件查找名字为xiaoming,城市为beijing,年龄为21的人:

这里写图片描述

返回了一行数据,从执行计划中看到,查询没有使用任何索引,进行了全表扫描,磁盘IO大。


为vc_Name建立索引:

这里写图片描述

进行同样的查询并查看执行计划:

这里写图片描述

返回了相同的结果,分析执行计划:SQL通过刚刚建立的index_of_name索引,不再进行全表扫描,而是先在索引中查找满足节点值为xiaoming的节点(有5个),再指向数据库中相应的行,返回一个初步结果集后再由另外两个条件进行一步步筛选得到最终结果。大大减少了磁盘IO,查询效率也高于前者。


虽然在 vc_Name 上建立了索引,查询时MYSQL不用扫描整张表,效率有所提高,但离我们的要求还有一定的距离。同样的,在 vc_City 和 i_Age 分别建立的MySQL单列索引的效率相似。为了进一步榨取 MySQL 的效率,就要考虑建立组合索引。


为多条件涉及的列建立联合索引:

这里写图片描述

值得注意的是,建立索引的时候应该根据需要规定索引长度,例如一个人的名字长度应该不会超过10个字符,通过vc_Name(10)规定索引长度后一定长度可以减少索引所占内存。

现在表中的结构:

这里写图片描述

进行相同的查询并查看执行计划:

这里写图片描述

返回相同的结果,从执行计划可以看到:本次查询使用了联合索引name_city_age,在遍历索引时就确定了只有一个节点满足条件,直接指向数据库表进行查询(rows:1)。有更少的磁盘IO,所用时间更少!


使用联合索引应该注意:

  1. MySQL使用联合索引只能使用左侧的部分,例如INDEX(a,b,c),当条件为a或a,b或a,b,c时都可以使用索引,但是当条件为b,c时将不会使用索引。这好比一本先根据姓,再根据名进行排序的电话簿,当查找的时候有姓的条件,效率会比没有任何条件高;如果在姓的基础上还有名的条件,效率会更高;但若只有名的条件,电话簿将不起作用。
  2. 离散度更高的索引应该放在联合索引的前面,因为离散度高索引的可选择性高。考虑一种极端的情况,数据表中有100条记录,若INDEX(a,b)中a只有两种情况,而b有100种情况。这样对于查询唯一记录a = …,b = …时,先遍历全部索引看满足a条件的有50个索引节点,接下来还要再一个个遍历这50个索引节点。如果是INDEX(b,a),先遍历全部索引发现满足b条件的索引节点只有一个,再遍历这个节点发现也满足a条件。虽然最后都能找到那个唯一的索引节点,但是第二种索引顺序对引擎遍历索引效率有很大的提高(用电话薄的思想去思考问题)。
  3. 查看列的离散程度:
这里写图片描述

customer_id列的离散程度更高,建联合索引时应该INDEX(customer_id,staff_id);

相关文章

  • (转载)联合索引优化多条件查询

    转载自:联合索引优化多条件查询 联合索引是由多个字段组成的组合索引。若经常需要使用多个字段的多条件查询(WHERE...

  • 索引的技术点

    联合索引:多字段组合索引,加速组合条件查询 联合索引要满足从左到右的查询需求,例如(a,b,c)的联合索引,能够加...

  • 联合索引

    如果给表,a,b字段建立了联合索引 在查询时 查询条件 a,b时会走索引 查询条件只有a时,如果a为联合索引第一个...

  • MySQL,必须掌握的6个知识点

    目录 一、索引B+ Tree 原理 MySQL 索引 索引优化 索引的优点 索引的使用条件 二、查询性能优化使用 ...

  • mysql 索引优化特性之索引下推

    索引下推优化默认开启,索引下推 和联合索引有密切的关系,它让组合索引里的范围条件变得有意义 查询开启状态 我们可以...

  • 性能调优之SQL优化

    性能调优之SQL优化如何加快查询速度?1、升级硬件2、根据查询条件,建立索引,优化索引、优化访问方式,限制结果集的...

  • MySQL优化:单索引的选择-index_merge

    1.多个单索引 where子句后有多个查询条件,MySQL 5.1以前即使分别为每个条件建立索引,查询优化器也只会...

  • 性能调优之SQL优化

    如何加快查询速度?1、升级硬件2、根据查询条件,建立索引,优化索引、优化访问方式,限制结果集的数据量。3、扩大服务...

  • SQL优化(收集)

    如何加快查询速度? 升级硬件 根据查询条件,建立索引,优化索引,优化访问方式,限制结果集的数据量。 扩大服务器的内...

  • SQL不走索引的可能原因

    1、联合索引,查询条件没有联合索引的第一列。 2、在包含有null值的table列上建立索引,当时使用select...

网友评论

    本文标题:(转载)联合索引优化多条件查询

    本文链接:https://www.haomeiwen.com/subject/icmsjftx.html