mysql单表访问方法一二讲

#单表
create tabel tab(
	id int not null auto_increment,
    key1 varchar(100),
    key2 int,
    key3 varchar(100),
    key_part1 varchar(100),
    key_part2 varchar(100),
    key_part3 varchar(100),
    commend_field varchar(100),
    primary key (id),
    key idx_key1 (key1),
    unique key uk_key2 (key2),
    key idx_key3 (key3),
    key idx_key_part(key_part1, key_part2, key_part3)
);

# const：主键索引（PRIMARY KEY）以及唯一二级索引（UNIQUE KEY）的等值查询，常数级，唯一二级索引（UNIQUE KEY）需要一次回表操作；
select * from tab where id = 100;
select * from tab where key2 = 100; #二者都是仅仅匹配到一条数据 二级索引需要回表查询一次
#ref：
#（1）非唯一的二级索引等值查询，由于可能匹配多个数据，这些数据是连续的，因此执行代价取决于扫描的记录行数，需要多次回表；
select * from tab where key1 = 'abc'; #扫描的是连续的单点区间[abc,abc]
#（2）注意：在二级索引列允许存储null，且使用二级索引（无论是UNIQUE KEY还是KEY）进行 key is null 的匹配时， 由于二级索引列并不会对null的数量进行限制，因此其最佳的访问只能是ref；
select * from tab where key2 is null; #相当于单点扫描区间[null，null]
#（3）对于二级索引包含多个列的情况（也就是联合索引），只要最左边连续的列是与常数进行等值比较，就可以采用ref访问方法。
select * from tab where key1_part = 'abc';
select * from tab where key1_part = 'abc' and key2_part = 'adc';
select * from tab where key1_part = 'abc' and key2_part = 'adc' and key3_part = 'abc';
#ref_or_null：同时进行二级索引列的等值查询和is null查询
select * from tab where key1 = '123' or key1 is null;
#range:利用索引进行数据查询时，对应的扫描区间是若干个单点扫描区间或者范围扫描区间
select * from tab where key2 in (123, 345) or (key2 >= 23 and key2 <= 45); #两个单点扫描区间[123,123] [345,345] 一个范围扫描区间 [23,45] 
#index：扫描所有的二级索引记录的访问方法
#匹配条件中仅包含key_part2 并不是idx_key_part最左边的列 无法使用联合索引，
#但是 查询的所有列正好是idx_key_part索引包含的列 因此可以通过扫描idx_key_part索引所有的记录 
#针对每一条二级索引记录 来进行条件判断  key2_part = 'abc'， 在二级索引上的扫描区间属于（-∞，+∞） 
#这个过程并不需要回表
select key_part1, key_part2, key_part3 from tab where key2_part = 'abc';


#二级索引的回表操作简单介绍
select * from tab where key1 = '123' and key2 > 100;
#两个搜索条件 key1 = '123'  key2 > 100
#mysql会判断两个条件哪一个的扫描行数会更低， key1 = '123' 的单点扫描区间[123,123]，ref访问方法
# key2 > 100的（100，+∞）的范围扫描区间 range访问方法 一般情况下ref的性能要比range更优 但并不是绝对的 如果相同数值的结果很多的话 性能就会下降
#这里假设使用idx_key1作为优化索引 查询到步骤如下所示
#（1）首先通过索引idx_key1扫描到区间[123,123]的第一条二级索引记录，并通过这条记录拿到主键
#（2）通过拿到的主键进行回表操作，取出这条记录
#（3）检测这条记录是不是满足 key2 > 100 如果满足 发送给mysql 不满足 继续寻找下一条 key1 = '123' 的二级索引记录
#（4）执行上述操作，直到扫描区间[123,123]的最后一条数据