SQL要怎么写才会调用到复合索引和分区

本文讨论复合索引以及分区的简单使用，也即是SQL语句要如何写才会调用到复合索引以及分区。在此只作为新手交流。

 

Titles表的设计：

 

CREATE TABLE `titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 PARTITION BY RANGE (to_days(from_date))(
 PARTITION p01 VALUES LESS THAN (725371) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (725736) ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN (726101) ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN (726467) ENGINE = InnoDB,
 PARTITION p05 VALUES LESS THAN (726832) ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN (727197) ENGINE = InnoDB,
 PARTITION p07 VALUES LESS THAN (727562) ENGINE = InnoDB,
 PARTITION p08 VALUES LESS THAN (727928) ENGINE = InnoDB,
 PARTITION p09 VALUES LESS THAN (728293) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (728658) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (729023) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (729389) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN (729754) ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN (730119) ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN (730484) ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN (730850) ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (731215) ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN (731580) ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN (1096092) ENGINE = InnoDB)

 

从该表的定义上可以看到，有两个索引：（`emp_no`,`title`,`from_date`）主键索引（复合索引）和`emp_no`索引（单列索引）。还按照to_days(from_date)做了range分区。

 

先来了解下该表所拥有的数据量（44万）：

 

一、复合索引的使用

 

这里就不讨论什么是索引，以及索引有什么好处之类的，就只说明SQL语句要怎么写才会调用到复合索引。

 

1.       什么情况下会调用到主键索引（复合索引）

 

从表设计中可以看到主键索引是一个复合索引，由（`emp_no`,`title`,`from_date`）三列构成，那当我们写SQL的时候，where条件后面加上哪一列才会使用到主键索引（复合索引）呢？我们一个一个来试一下：

 

1）  where条件使用了emp_no列

 

 

 

 

从执行计划中可以看到，只要where条件中使用了emp_no列，那不管后面还有没有其他条件，都会使用到主键索引。

 

 

 

1）  where条件中没有使用emp_no列，但使用了复合主键中的title和from_date列

 

 

 

 
 

从执行计划中可以看到，虽然复合索引是由（`emp_no`,`title`,`from_date`）三列构成的，但由于没有使用emp_no列作为查询条件，所以MySQL优化器在做执行计划的时候，没有使用到任何索引。

 

为什么会出现这种情况？为什么一定要用到emp_no列才会使用到我们定义的复合索引？ title和from_date不也是索引的一部分吗？原因是我们在定义复合索引的时候，把emp_no放在了前面，可以看下前面的定义：PRIMARY KEY (`emp_no`,`title`,`from_date`)。复合索引定义的顺序也有关系吗？要说明这个问题，先来看下复合索引是怎么组织的，以及优化器是如何查找的。

 

 

 

当我们建立了复合索引(`emp_no`,`title`,`from_date`)后，实际上MySQL执行引擎会生成一张索引表，结构类似上图所示，(`emp_no`,`title`,`from_date`)作为一行的标识，后面紧跟实际数据的地址。

 

你可以看到emp_no都在索引表中每行的最左边，当执行引擎查找数据的时候，它会从最左边开始查找数据，就是从emp_no列开始查找数据，如：

 

 

如果它发现where条件中包含了emp_no列，它就会查找索引表，否则则不使用索引。这又有两种情况：

-1）where条件只包含emp_no列

 

执行引擎会按emp_no去搜索索引表，查出所有符合条件的索引列，然后通过后面的数据地址（Oracle其实就是rowid），直接到数据表（titles）中去获取符合条件的数据。

 

-2）where条件中除了emp_no，还包含了复合索引中的其他列

 

执行引擎就会按照(emp_no + 其他索引列)去查找索引表，查出符合条件的索引列，然后通过后面的数据地址（Oracle其实就是rowid），直接到数据表（titles）中去获取符合条件的数据。

 

由此可见我们在创建复合索引的时候，第一列的选择非常重要，其关系到执行引擎会不会使用该复合索引。

 

那我们为什么不直接就使用单列索引呢，如这里的emp_no，因为无论如何，我们要使用索引，都必须要使用到emp_no才行，那干脆就不要title和from_date了。

 

那是因为单独使用title和from_date时虽然不会使用到索引，但是通过tmp_no + title + from_date组合后，缩小了查找的范围。如emp_no = ‘100001’，如果我们只通过100001来查找数据，那可能会有很多数据，但是如果通过emp_no = ‘100001’和title=’Engineer’来查找，由于多了一个条件，这样数据量就变小很多。道理就是这样。

 

 

现在回到最初表的设计上，当时我们不是也创建了一个单列索引（KEY `emp_no` (`emp_no`)）吗？怎么之前的测试没有使用到？都是走的复合索引。那是因为在这里复合索引就是主键索引，主键索引是唯一性的，对数据的查找更精确，所以优化器优先选择了主键索引。所以这就说明了一个问题，在建索引时要考虑周全，不要建立无用的索引（当然emp_no单列索引在这里另有用处，但这里暂不讨论），无用的索引不仅用不到，而且还会增加ADD、UPDATE和DELETE的负担。

 

 

二，分区的使用

 

   这里也只讨论SQL语句要怎么写，才会使用到分区。

 

从最初的titles表设计中可以了解到，titles表按照to_days(from_date)做了range分区。那怎样才能让执行引擎按分区查找数据呢？

 

  

当只使用emp_no来搜索数据时，使用到了所有的分区，也就是查找了所有分区（当然在这里emp_no是先走了索引，然后再查分区。但如果是其他列，那么这里就会是全表扫描了）。而当使用到了from_date列来作为搜索条件时，才会到具体的分区中去查找数据，这里只搜索到了p15分区。

 

所以只有当where条件中包含了分区列（这里是from_date）时，执行引擎才会只搜索包含了所需数据的分区，而不去做耗时耗能的全表扫描。