本文讨论复合索引以及分区的简单使用,也即是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