单表访问方法

访问方法

MySQL把执行查询语句的方式称为访问方法

const

通过索引定位一条记录

select * from single_table where id = 1438;

通过唯一二级索引列来定位一条记录

select * from single_table where key3 = 1438;

查询步骤：

• 在uk_key3对应的B+树索引中，根据key3列与常熟的等值比较条件定位到一条二级索引记录
• 然后根据该记录的id值到聚簇索引中获取到完整的用户记录。

ref

使用普通的二级索引与常熟进行等值比较

select * form single_table where key1 = 'abc';

对于这样的查询，可以进行全表扫描也可以通过二级索引idx_key1来执行，由于查询的是* ，所以每当使用二级索引查询出一条数据时，都需要进行回表处理，所以ref比const消耗的成本高一点。

• 对存在null值的列时，最多只能用到ref的查询语句，而不能使用const查询语句。
• 对于复合索引来说，只要最左边连续的列是余常熟进行等值比较，就可以使用ref进项查询。

ref_or_null

我们不仅想找出某个二级索引列的值等于某个常数的记录，而且还想把该列中的值为NULL的记录也找出来

select * from single_table where key1='a' or key1 is null;

range

对索引列进行区间扫描

select * from single_table where key3 in (1,4,5) or key3 in(9,10);

index

扫描全部二级索引的访问方法并且不需要进行回表操作。key_part2并不是联合索引idx_key_part的索引列中最右边的列，所以无法形成适合的范围区间来减少需要扫描的记录数量，从而无法使用ref和range访问方法来执行这个语句，但是这个查询语句复合：

• 查询列只有idx_key_part中的列
• 只有key_part2列，这个列也包含在索引idx_key_part中
  由于二级索引记录要比聚簇索引记录小的多，所以整个过程不需要回表操作。

select key_part1, key_part2, key_part3 from single_table where key_part2 = 'abc';

all

全表扫描

索引合并

使用多个二级索引完成一次查询

select * from single_table where idx_key1='a' and idx_key2='b';

从每个索引中获取到的所及索引记录按照主键值排序（为了更加快速获取交集），在从多个索引中获取交集，并执行回表操作。

Union索引合并

从不同索引中扫描到的记录的id值取并集，为这些id值执行回表操作。

select * from single_table where key1 = 'a' OR key2 = 'b';

上面的查询，在idx_key1的[‘a’,‘a’]扫描区间中的二级索引记录都是按照主键值排序的，在idx_key3的[‘b’,‘b’]扫描区间中的二级索引记录也都是按照主键值排序的。

• 从两个有序集合中执行去重操作
• id有序，在执行回表操作的时候就不会进行随机I/O。
  以下SQL不能使用UNION索引

select * from single_table where key1 = 'a' or key3 = 'b';

以下sql也可以使用Union索引

select * from single_table where key1 = 'a' or id > 9000

select * from single_table where (key_part1 = 'a' and key_part2='b' and key_part3='c') OR (key1 = 'b' and key3 = 'b');

Sort-Union索引合并

先将各个索引中扫描的记录的主键进行排序，在按照执行Union索引合并的方式执行查询。

select * from single_table where key1 < 'a' OR key3 > 'z';

将从各个索引中扫描到的记录的主键值进行排序，再按照执行Union索引合并进行查询。

测试数据

CREATE TABLE `single_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(10) DEFAULT NULL,
  `key2` varchar(10) DEFAULT NULL,
  `key3` int(10) DEFAULT NOT NULL,
  `com_key1` varchar(10) DEFAULT NULL,
  `com_key2` varchar(10) DEFAULT NULL,
  `com_key3` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key1` (`key1`) USING BTREE,
  KEY `idx_key2` (`key2`) USING BTREE,
  UNIQUE KEY uk_key3 (`key3`) USING BTREE,
  KEY `idx_com` (`com_key1`,`com_key2`, `com_key3`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

CREATE FUNCTION `rand_str_func`(`n` int) RETURNS varchar(255) 
BEGIN
	#Routine body goes here...
  declare str varchar(255) default 'abcdefghijklmnopqrstuvwxyz';
  declare num int default 0;
  declare return_str varchar(255) default '';
  while num<n DO
     set return_str= concat(return_str,substr(str,floor(RAND()*26)+1,1));
     set num=num+1;
  end while;
  return return_str;
END

CREATE  PROCEDURE `rand_insert`(in n int)
BEGIN
	#Routine body goes here...
   DECLARE num int default 0;
   a:REPEAT 
    insert into single_table values(0,rand_str_func(1),rand_str_func(1),num，rand_str_func(1),rand_str_func(1),rand_str_func(1));
    set num=num+1;
   until num>=n end repeat a;
END