存取方法

终於度过前面枯燥乏味的内容了...(但它们都很重要，也与今天的主题有关)
今天要来进入重点项目
我们想要进一步多了解Mysql，无不就是希望当语法跑太慢的时候，我们可以知道怎麽优化它！！

为了故事顺利发展，先把之前的例子再贴一次

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_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)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的uk_key2二级索引，且是唯一
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是个联合索引

优化查询语法是一个很大的范围，学会跑之前要先知道怎麽走！

所以我们要先知道
存取方法的概念
我们都有用过google map，在地图上设立了起点与终点後，就要决定使用何种交通工具来到达目的地。
而Mysql查询语法的过程跟这个也有点像，不管你过程使用什麽存取方法(交通工具)，只要能拿到需要的资料(到达目的地)就好。

来看看有那些存取方法
const
透过主键或唯一二级索引与常数的相等比较来搜寻一笔纪录，这个存取方法就是const(意思是常数等级的，代价可忽略不计)，这样的方法速度非常快。
看个例子：

select * from single_table where id = 1438;

透过主键与常数的比较，mysql会直接利用主键值在聚簇索引内定位对应的使用者纪录。

select * from single_table where key2 = 3841;

透过二级索引列与常数的比较，mysql会在uk_key2的B+树索引中定位到二级索引纪录，再根据id值去聚簇索引查询完整使用者纪录。

select * from single_table where key2 is NULL;

这个无法使用const存取方法(後续会提到使用什麽方法)，因为唯一二级索引并不限制NULL值的数量，所以上述叙述可能存取到多笔纪录。

ref
透过普通二级索引与常数的相等比较来搜寻一笔纪录，这个存取方法就是ref，这样方法的速度慢了const一些。
看个例子：

select * from single_table where key1 = 'abc';

透过普通二级索引与常数的比较，mysql会在idx_key1的B+树索引中定位到key1='abc'的所有二级索引纪录(可能很多笔，并不像const是唯一，所以会比较慢)，再根据id值去聚簇索引查询完整使用者纪录。

以下这样，只要左边连续的列都是与常数比较就算是ref

select * from single_table where key_part1 = 'abc';
select * from single_table where key_part1 = 'abc' and key_part2 = 'def';
select * from single_table where key_part1 = 'abc' and key_part2 = 'def' and key_part3 = 'ghi';

但如果有不是相等比较的条件就不算ref

select * from single_table where key_part1 = 'abc' and key_part2 > 'def';

ref_or_null
有时不仅想找出某个二级索引纪录等於常数的值，也需要NULL值，这个存取方法就是ref_or_null。
看个例子：

select * from single_table where key1 = 'abc' or key1 is NULL;

range
使用索引执行查询时，对应的扫描区间为许多个单点扫描或范围扫描区间，这个存取方法就是range。
看个例子：

select * from single_table where key2 in (1,123) or (key2 >= 100 and key2 <= 200);

index

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

由於key_part2不是联合索引中最左边的列，因此无法形成合适的扫描区间(如果不清楚请复习此篇)，所以也无法使用前面提到的那些查询方法。
但这个语法的查询列表资料(key_part1, key_part2, key_part3)都包含在索引idx_key_part内，搜索条件也一样。所以我们可以直接利用历遍idx_key_part二级索引资料就能得到所需的资料，由於二级索引纪录比聚簇索引纪录少多了，快上许多，这就是index。

另外order by主键也是一种index方法

select * from single_table order by id;

all
全表扫描