建筑工程网站监理答案,网页数据抓取,宣传片拍摄费用,广州新际网络科技有限公司1. 访问方法
MySQL把执行查询语句的方式称之为访问方法或者访问类型。 而访问方法大致分为两类
全表扫描索引 而进行细分的话可以分为以下几类 #xff08;为了方便说明#xff0c;先建一个表#xff09;
CREATE TABLE single_table (id INT NOT NULL AUTO_INCREMENT,key…1. 访问方法
MySQL把执行查询语句的方式称之为访问方法或者访问类型。 而访问方法大致分为两类
全表扫描索引 而进行细分的话可以分为以下几类 为了方便说明先建一个表
CREATE TABLE single_table (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),common_field VARCHAR(100),PRIMARY KEY (id),KEY idx_key1 (key1),UNIQUE KEY idx_key2 (key2),KEY idx_key3 (key3),KEY idx_key_part(key_part1, key_part2, key_part3)
) EngineInnoDB CHARSETutf8;1.1 const
SELECT * FROM single_table WHERE id 1438;SELECT * FROM single_table WHERE key2 3841;例如上面两个sql语句条件都是主键或者唯一二级索引列与常量进行比较。
第一个sql语句的执行直接就是在主键的聚簇索引中根据主键值进行匹配第二个sql语句先要在对应的唯一二级索引中根据查询的索引列进行一次匹配查到主键id后再回表查出所有的数据 因为主键和唯一二级索引他们的索引列都是唯一的因此查找的速度是非常快的所以这种访问方法叫const意思就是常数级别的查询速度.注意当索引列的查询条件为空时访问方法并不能算const因为可能有多条记录为空
1.2 ref
而ref的访问方式类似于唯一二级索引的访问方式都是先在二级索引上进行匹配找到主键id后再回表查询。但是因为普通二级索引列的值并不是唯一的因此可能查询到的是多条记录再进行多次回表。
SELECT * FROM single_table WHERE key1 abc;例如上面的sql语句因为key1是普通二级索引可能存在多个key1abc’的记录因此在查询时需要对多个记录进行回表
1.3 ref_or_null
这个与ref类似只是多了对于空值的匹配
SELECT * FROM single_table WHERE key1 abc OR key1 IS NULL;例如上面的sql语句是在key1这个二级索引上查询key1 abc’和key1为空的主键id然后再进行回表
1.4 range
而range访问方法意思就是根据范围进行查询前面的查询都是匹配单个常数值的而range是查询一个范围的。
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 38 AND key2 79);例如上面的sql语句只是在之前查询key2 IN (1438, 6328)的基础上将满足79key238的主键id也找出来同样进行回表
1.5 index
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 abc;key_part2 abc’这个条件并不满足最左匹配但是key_part1, key_part2, key_part3 属于一个联合索引idx_key_part因此这个查询是在idx_key_part索引的叶子节点中一个一个匹配key_part2 ‘abc’直接返回索引列即可并不需要回表。
index的意思就是直接在索引中匹配查询。
1.6 all
而all就是全表扫描
2.注意事项
2.1 一般情况下只能利用单个二级索引执行查询
SELECT * FROM single_table WHERE key1 abc AND key2 1000;查询优化器会根据single_table表的统计数据判断key1 ‘abc’ 和 key2 1000两个条件哪个索引查询的条数少就选择到哪个索引的B树上查。
例如在上面的例子中优化器选择的是key1 ‘abc’那么查询到满足key1 ‘abc’ 的主键后就会回表查询并且以 key2 1000为条件进行过滤
2.2 明确range访问方法使用的范围区间
2.2.1 所有搜索条件都可以使用某个索引的情况
例如
SELECT * FROM single_table WHERE key2 100 AND key2 200;因为条件都是来自同一个索引的范围查询因此只要将对应的范围进行集合的运算即可
2.2.2 有的搜索条件无法使用索引的情况
SELECT * FROM single_table WHERE key2 100 AND common_field abc;因为common_field abc’无法使用到索引因此范围搜索只需要关注key2 100这个范围即可回表的时候再根据common_field abc’过滤一次就可以保证选出来的记录满足 key2 100 AND common_field ‘abc’。
SELECT * FROM single_table WHERE key2 100 OR common_field abc;但是当条件换成or以后就无法使用key2的索引了因为查询出了key2 100的记录以后没有办法也在key2的索引中查出common_field abc’的记录
2.2.3 复杂搜索条件下找出范围匹配的区间
SELECT * FROM single_table WHERE (key1 xyz AND key2 748 ) OR(key1 abc AND key1 lmn) OR(key1 LIKE %suf AND key1 zzz AND (key2 8000 OR common_field abc)) ;主要使用到key1和key2 两个索引因此对他们进行分别的查找的区间
对于key1
先将不属于key1的条件替换为true
(key1 xyz AND TRUE ) OR
(key1 abc AND key1 lmn) OR
(TRUE AND key1 zzz AND (TRUE OR TRUE))替换掉永远为TRUE或FALSE的条件 因为符合key1 ‘abc’ AND key1 lmn’永远为FALSE所以上边的搜索条件可以被写成这样
(key1 xyz) OR (key1 zzz)合并范围
key1 xyz对于key2
先将不属于key1的条件替换为true
(TRUE AND key2 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 8000 OR TRUE))再次化简可以推出最后条件是true也就是说需要搜索全部的key2索引
2.3 索引合并
之前第一点注意事项说过 一般情况下只能利用单个二级索引执行查询而在特殊情况下也可能在一个查询中使用到多个二级索引。因此把这种使用到多个索引来完成一次查询的执行方法称之为index merge具体的索引合并算法有下边三种。
2.3.1 Intersection合并
Intersection交集意味着对多个二级索引的结果找交集
SELECT * FROM single_table WHERE key1 a AND key3 b;执行过程
在key1这个二级索引上找key1 ‘a’在key3这个二级索引中找key3 ‘b’因为二级索引查出来的记录格式是索引列加主键因此直接对他们查出来的主键作一个交集再回表即可
为什么不直接查到二级索引key1的记录然后在回表的过程中过滤掉不满足key3的条件呢
因为如果在key1上面查出来的记录条数很多的话那么就要进行很多次的回表而这种回表属于随机I/O非常耗时而选择两次的二级索引查找属于顺序I/O有更好的性能。
使用Intersection的情况
二级索引只能是等值匹配
SELECT * FROM single_table WHERE key1 a AND key_part1 a AND key_part2 b AND key_part3 c;
SELECT * FROM single_table WHERE key1 a AND key_part1 a AND key_part2 b AND key_part3 c;
SELECT * FROM single_table WHERE key1 a AND key_part1 a;第一条sql语句可以正常使用第二条使用了范围查询第三条没有使用联合索引的所有列
主键列可以是范围匹配
SELECT * FROM single_table WHERE id 100 AND key1 a;因为二级索引下索引列是已经排好序的了而如果出现了索引列全部相同的记录那么将按照主键值进行排序而使用了排好序的主键来求交集就非常简单了因为对于两个已经排好序的主键序列求他们交集的时间复杂度是on。
这也回答了第一种情况为什么只能是等值匹配因为只有等值匹配的情况下索引列记录相同的几条记录才能按照主键的顺序排好。
上面的两种情况只是使用Intersection的必要条件。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多导致回表开销太大而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。
2.3.2 Union合并
Union是并集的意思适用于使用不同索引的搜索条件之间使用OR连接起来的情况。 使用Union的情况 二级索引只能是等值匹配(与Intersection相同) 主键列可以是范围匹配(与Intersection相同) 使用Intersection索引合并的搜索条件 例如
SELECT * FROM single_table WHERE key_part1 a AND key_part2 b AND key_part3 c OR (key1 a AND key3 b);1.先按key1 ‘a’ AND key3 ‘b’使用 Intersection得到一个交集
2.再使用 key_part1 ‘a’ AND key_part2 ‘b’ AND key_part3 ‘c’得到一个集合
3.再对上面求到的两个集合求并集。然后进行回表
优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。
2.3.3 Sort-Union合并
Union的条件太苛刻了只有等值匹配才能使用到。
SELECT * FROM single_table WHERE key1 a OR key3 z而对于这样的范围查询就无法使用了而对于上面的sql就可能执行Sort-Union索引合并。
1.按照key1 a’查出所有记录并且排好序 2.按照key3 z’查出所有记录并且排好序 3.因为两个集合的主键都是排好序的了因此直接按Union那样做就行了
为什么没有Sort-Intersection 因为Union是在单独根据搜索条件从某个二级索引中获取的记录数比较少的情况下使用的而这种情况下记录数少排序的性能损耗小。而Intersection的情况恰恰相反它是针对查询出的记录数多如果排序对于性能的损耗就很大了
2.3.4 联合索引注意事项
联合索引替代Intersection索引合并
SELECT * FROM single_table WHERE key1 a AND key3 b;像这样的sql语句需要使用到两个单独的索引倒不如直接做成联合索引既可以少一颗b树还不能做集合运算。
ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);
