网站速度打开慢的原因,近期国外重大新闻事件,seo最好的网站,别样网图片素材网站今天是新年#xff0c;祝大家新年快乐#xff0c;但是生活还是得继续。
后面也会持续更新#xff0c;学到新东西会在其中补充。 建议按顺序食用#xff0c;欢迎批评或者交流#xff01; 缺什么东西欢迎评论#xff01;我都会及时修改的#xff01; 大部分截图和文章采…今天是新年祝大家新年快乐但是生活还是得继续。
后面也会持续更新学到新东西会在其中补充。 建议按顺序食用欢迎批评或者交流 缺什么东西欢迎评论我都会及时修改的 大部分截图和文章采用该书谢谢这位大佬的文章在这里真的很感谢让迷茫的我找到了很好的学习文章。我只是加上了自己的拙见。我只是记录学习没有任何抄袭意思 MySQL 是怎样运行的从根儿上理解 MySQL - 小孩子4919 - 掘金小册
MySQL Server有一个称为查询优化器的模块一条查询语句进行语法解析之后就会被交给查询优化器来进行优化优化的结果就是生成一个所谓的执行计划这个执行计划表明了应该使用哪些索引进行查询表之间的连接顺序是啥样的最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询并将查询结果返回给用户。
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;为这个single_table表建立了1个聚簇索引和4个二级索引分别是 为id列建立的聚簇索引。 为key1列建立的idx_key1二级索引。 为key2列建立的idx_key2二级索引而且该索引是唯一二级索引。 为key3列建立的idx_key3二级索引。 为key_part1、key_part2、key_part3列建立的idx_key_part二级索引这也是一个联合索引。
#借用评论区一位大佬写的存储过程
DELIMITER //
CREATE PROCEDURE InsertRecords()
BEGIN
DECLARE i INT DEFAULT 1;WHILE i 10000 DO
INSERT INTO single_table (key1, key2, key3, key_part1, key_part2, key_part3, common_field) VALUES
(CONCAT(Key1_, i), i, CONCAT(Key3_, i), CONCAT(Part1_, i), CONCAT(Part2_, i), CONCAT(Part3_, i), CommonField);
SET i i 1;
END WHILE;
END //DELIMITER ;CALL InsertRecords();访问方法access method的概念
查询的执行方式大致分为下边两种
使用全表扫描进行查询 把表的每一行记录都扫一遍把符合搜索条件的记录加入到结果集就完了。使用索引进行查询 因为直接使用全表扫描的方式执行查询要遍历好多记录所以代价可能太大了。 如果查询语句中的搜索条件可以使用到某个索引那直接使用索引来执行查询可能会加快查询执行的时间。 针对主键或唯一二级索引的等值查询针对普通二级索引的等值查询针对索引列的范围查询直接扫描整个索引 MySQL执行查询语句的方式称之为访问方法或者访问类型。
const
SELECT * FROM single_table WHERE id 1438;MySQL会直接利用主键值在聚簇索引中定位对应的用户记录 对于single_table表的聚簇索引就是id列。 B树叶子节点中的记录是按照索引列排序的对于聚簇索引来说它对应的B树叶子节点中的记录就是按照id列排序的。
唯一二级索引列来定位一条记录的速度也是很快的。注意这里是唯一的
SELECT * FROM single_table WHERE key2 3841;第一步先从idx_key2对应的B树索引中根据key2列与常数的等值比较条件定位到一条二级索引记录 第二步再根据该记录的id值到聚簇索引中获取到完整的用户记录。
通过主键或者唯一二级索引列与常数的等值比较来定位一条记录是像坐火箭一样快的所以把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为const意思是常数级别的代价是可以忽略不计的。 不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效如果主键或者唯一二级索引是由多个列构成的话索引中的每一个列都需要与常数进行等值比较这个const访问方法才有效这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录。 对于唯一二级索引来说查询该列为NULL值的情况比较特殊比如这样
SELECT * FROM single_table WHERE key2 IS NULL;唯一二级索引列并不限制 NULL 值的数量所以上述语句可能访问到多条记录也就是说 上边这个语句不可以使用const访问方法来执行。
ref
对某个普通的二级索引列与常数进行等值比较
SELECT * FROM single_table WHERE key1 abc;对于这个查询我们当然可以选择全表扫描来逐一对比搜索条件是否满足要求我们也可以先使用二级索引找到对应记录的id值然后再回表到聚簇索引中查找完整的用户记录。由于普通二级索引并不限制索引列值的唯一性所以可能找到多条对应的记录也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。 如果匹配的记录较少则回表的代价还是比较低的所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。设计MySQL的大叔就把这种搜索条件为二级索引列与常数等值比较采用二级索引来执行查询的访问方法称为ref。 实在不想看上面文字可以看图 对于普通的二级索引来说通过索引列进行等值比较后可能匹配到多条连续的记录而不是像主键或者唯一二级索引那样最多只能匹配1条记录所以这种ref访问方法比const差了那么一丢丢但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的。
二级索引列值为NULL的情况 不论是普通的二级索引还是唯一二级索引它们的索引列对包含NULL值的数量并不限制所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法而不是const的访问方法。 对于某个包含多个索引列的二级索引来说只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法比方说下边这几个查询
SELECT * FROM single_table WHERE key_part1 god like;SELECT * FROM single_table WHERE key_part1 god like AND key_part2 legendary;SELECT * FROM single_table WHERE key_part1 god like AND key_part2 legendary AND key_part3 penta kill;ref_or_null
不仅想找出某个二级索引列的值等于某个常数的记录还想把该列的值为NULL的记录也找出来。
SELECT * FROM single_table WHERE key1 abc OR key1 IS NULL;range
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 38 AND key2 79);当然还可以使用全表扫描的方式来执行这个查询不过也可以使用二级索引 回表的方式执行 如果采用二级索引 回表的方式来执行的话那么此时的搜索条件就不只是要求索引列与常数的等值匹配了而是索引列需要匹配某个或某些范围的值在本查询中key2列的值只要匹配下列3个范围中的任何一个就算是匹配成功了
key2的值是1438key2的值是6328key2的值在38和79之间。
这种利用索引进行范围匹配的访问方法称之为range。
范围1key2 1438范围2key2 6328范围3key2 ∈ [38, 79]注意这里是闭区间。
索引列等值匹配的情况称之为单点区间上边所说的范围1和范围2都可以被称为单点区间像范围3这种的我们可以称为连续范围区间。
index
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 abc;由于key_part2并不是联合索引idx_key_part最左索引列所以无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件
它的查询列表只有3个列key_part1, key_part2, key_part3而索引idx_key_part又包含这三个列。搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。
直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 abc这个条件是否成立把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列而二级索引记录只需要存放索引列和主键而且这个过程也不用进行回表操作所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多这种采用遍历二级索引记录的执行方式称之为index。
all
对于InnoDB表来说也就是直接扫描聚簇索引设计MySQL的大叔把这种使用全表扫描执行查询的方式称之为all。
二级索引 回表(不知道多少周目了)
一般情况下只能利用单个二级索引执行查询
SELECT * FROM single_table WHERE key1 abc AND key2 1000;查询优化器会识别到这个查询中的两个搜索条件
key1 abckey2 1000
优化器一般会根据single_table表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少选择那个扫描行数较少的条件到对应的二级索引中查询。将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的WHERE条件过滤记录。 一般来说等值查找比范围查找需要扫描的行数更少也就是ref的访问方法一般比range好但这也不总是一定的也可能采用ref访问方法的那个索引列的值为特定值的行数特别多 整个查询过程可以分为两个步骤
步骤1使用二级索引定位记录的阶段也就是根据条件key1 abc从idx_key1索引代表的B树中找到对应的二级索引记录。步骤2回表阶段也就是根据上一步骤中找到的记录的主键值进行回表操作也就是到聚簇索引中找到对应的完整的用户记录再根据条件key2 1000到完整的用户记录继续过滤。将最终符合过滤条件的记录返回给用户。
因为二级索引的节点中的记录只包含索引列和主键所以在步骤1中使用idx_key1索引进行查询时只会用到与key1列有关的搜索条件其余条件比如key2 1000这个条件在步骤1中是用不到的只有在步骤2完成回表操作后才能继续针对完整的用户记录中继续过滤。
明确range访问方法使用的范围区间
B树索引来说只要索引列和常数使用、、IN、NOT IN、IS NULL、IS NOT NULL、、、、、BETWEEN、!不等于也可以写成或者LIKE操作符连接起来就可以产生一个所谓的区间。 LIKE操作符比较特殊只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引 #下面两句SQL效果相同
SELECT * FROM single_table WHERE key2 IN (1438, 6328);
SELECT * FROM single_table WHERE key2 1438 OR key2 6328;所有搜索条件都可以使用某个索引的情况
SELECT * FROM single_table WHERE key2 100 AND key2 200;这个查询中的搜索条件都可以使用到key2也就是说每个搜索条件都对应着一个idx_key2的范围区间。这两个小的搜索条件使用AND连接起来也就是要取两个范围区间的交集在我们使用range访问方法执行查询时使用的idx_key2索引的范围区间的确定过程就如下图所示 key2 100和key2 200交集当然就是key2 200了也就是说上边这个查询使用idx_key2的范围区间就是(200, ∞)。
SELECT * FROM single_table WHERE key2 100 OR key2 200;OR意味着需要取各个范围区间的并集所以上边这个查询在我们使用range访问方法执行查询时使用的idx_key2索引的范围区间的确定过程就如下图所示 也就是说上边这个查询使用idx_key2的范围区间就是(100 ∞)。
有的搜索条件无法使用索引的情况
SELECT * FROM single_table WHERE key2 100 AND common_field abc;请注意这个查询语句中能利用的索引只有idx_key2一个而idx_key2这个二级索引的记录中又不包含common_field这个字段所以在使用二级索引idx_key2定位记录的阶段用不到common_field abc这个条件这个条件是在回表获取了完整的用户记录后才使用的而范围区间是为了到索引中取记录中提出的概念所以在确定范围区间的时候不需要考虑common_field abc这个条件我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为TRUE就好了。
SELECT * FROM single_table WHERE key2 100 AND TRUE;SELECT * FROM single_table WHERE key2 100;使用OR的情况
SELECT * FROM single_table WHERE key2 100 OR common_field abc;SELECT * FROM single_table WHERE key2 100 OR TRUE;
#化简一下
SELECT * FROM single_table WHERE TRUE;强制使用idx_key2执行查询的话对应的范围区间就是(-∞, ∞)也就是需要将全部二级索引的记录进行回表这个代价肯定比直接全表扫描都大了。也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用OR连接起来后是无法使用该索引的。
复杂搜索条件下找出范围匹配的区间
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)) ;可以看到他可能走的是idx_key1idx_key2索引列 假设使用idx_key1执行查询 like %suf不会使用索引
(key1 xyz AND true) OR
(key1 abc AND key1 lmn) OR
(true AND key1 zzz AND ( true OR true)) ;化简一下
(key1 xyz) OR
(key1 abc AND key1 lmn) OR
(key1 zzz) ;
x 78 y 79 z 7A
a 61 b 62 c 63
l 6C m 6D n 6Ekey1 abc AND key1 lmn永远为FALSE
(key1 xyz) OR (key1 zzz)key1 xyz 二级索引范围扫描 回表假设使用idx_key2执行查询
(TRUE AND key2 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 8000 OR TRUE))化简一下
key2 748 OR TRUETRUE 代表着扫描二级索引所有记录回表索引合并
#如果看不下去就先看练习把
MySQL在一般情况下执行一个查询时最多只会用到单个二级索引也可能在一个查询中使用到多个二级索引index merge。
Intersection合并
Intersection翻译过来的意思是交集。
SELECT * FROM single_table WHERE key1 a AND key3 b;假设这个查询使用Intersection合并的方式执行的话那这个过程就是这样的
从idx_key1二级索引对应的B树中取出key1 a的相关记录。从idx_key3二级索引对应的B树中取出key3 b的相关记录。二级索引的记录都是由索引列 主键构成的所以可以计算出这两个结果集中id值的交集。按照上一步生成的id值列表进行回表操作也就是从聚簇索引中把指定id值的完整用户记录取出来返回给用户。
只读取一个二级索引的成本 按照某个搜索条件读取一个二级索引 根据从该二级索引得到的主键值进行回表操作然后再过滤其他的搜索条件
读取多个二级索引之后取交集成本 按照不同的搜索条件分别读取不同的二级索引 将从多个二级索引得到的主键值取交集然后进行回表操作
虽然读取多个二级索引比读取一个二级索引消耗性能但是读取二级索引的操作是顺序I/O而回表操作是随机I/O所以如果只读取一个二级索引时需要回表的记录数特别多而读取多个二级索引之后取交集的记录数非常少当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时读取多个二级索引后取交集比只读取一个二级索引的成本更低。
MySQL在某些特定的情况下才可能会使用到Intersection索引合并
情况一二级索引列是等值匹配的情况对于联合索引来说在联合索引中的每个列都必须等值匹配不能出现只匹配部分列的情况。
比方说下边这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Intersection索引合并的操作
#官网是这样说的实际上我并没有测出来索引合并。
SELECT * FROM single_table WHERE key1 a AND key_part1 a AND key_part2 b AND key_part3 c;下边这两个查询就不能进行Intersection索引合并
#第一个查询是因为对key1进行了范围匹配
SELECT * FROM single_table WHERE key1 a AND key_part1 a AND key_part2 b AND key_part3 c;
#第二个查询是因为联合索引idx_key_part中的key_part2和key_part3列并没有出现在搜索条件中
#所以这两个查询不能进行Intersection索引合并。
SELECT * FROM single_table WHERE key1 a AND key_part1 a;情况二主键列可以是范围匹配
SELECT * FROM single_table WHERE id 100 AND key1 a;对于InnoDB的二级索引来说记录先是按照索引列进行排序如果该二级索引是一个联合索引那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 主键构成的二级索引列的值相同的记录可能会有好多条这些索引列的值相同的记录又是按照主键的值进行排序的。 之所以在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。 Intersection索引合并会把从多个二级索引中查询出的主键值求交集如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的那么求交集的过程就很方便啦。 假设某个查询使用Intersection索引合并的方式从idx_key1和idx_key2这两个二级索引中获取到的主键值分别是
从idx_key1中获取到已经排好序的主键值1、3、5从idx_key2中获取到已经排好序的主键值2、3、4
那么求交集的过程就是这样逐个取出这两个结果集中最小的主键值如果两个值相等则加入最后的交集结果中否则丢弃当前较小的主键值再取该丢弃的主键值所在结果集的后一个主键值来比较直到某个结果集中的主键值用完了。 先取出这两个结果集中较小的主键值做比较因为1 2所以把idx_key1的结果集的主键值1丢弃取出后边的3来比较。 因为3 2所以把idx_key2的结果集的主键值2丢弃取出后边的3来比较。 因为3 3所以把3加入到最后的交集结果中继续两个结果集后边的主键值来比较。 后边的主键值也不相等所以最后的交集结果中只包含主键值3。
时间复杂度是O(n)但是如果从各个二级索引中查询出的结果集并不是按照主键排序的话那就要先把结果集中的主键值排序完再来做上边的那个过程就比较耗时了。 按照有序的主键值去回表取记录有个专有名词叫Rowid Ordered Retrieval简称ROR SELECT * FROM single_table WHERE key1 a AND id 100;假设这个查询可以采用Intersection索引合并我们理所当然的以为这个查询会分别按照id 100这个条件从聚簇索引中获取一些记录在通过key1 a这个条件从idx_key1二级索引中获取一些记录然后再求交集其实这样就把问题复杂化了没必要从聚簇索引中获取一次记录。别忘了二级索引的记录中都带有主键值的所以可以在从idx_key1中获取到的主键值上直接运用条件id 100过滤就行了这样多简单。所以涉及主键的搜索条件只不过是为了从别的二级索引得到的结果集中过滤记录罢了是不是等值匹配不重要。
当然上边说的情况一和情况二只是发生Intersection索引合并的必要条件不是充分条件。也就是说即使情况一、情况二成立也不一定发生Intersection索引合并这得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多导致回表开销太大而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。
Union合并
SELECT * FROM single_table WHERE key1 a OR key3 b;Intersection是交集的意思这适用于使用不同索引的搜索条件之间使用AND连接起来的情况Union是并集的意思适用于使用不同索引的搜索条件之间使用OR连接起来的情况。 MySQL在某些特定的情况下才可能会使用到Union索引合并
情况一二级索引列是等值匹配的情况对于联合索引来说在联合索引中的每个列都必须等值匹配不能出现只出现匹配部分列( )的情况。
idx_key1和idx_key_part这两个二级索引进行Union索引合并的操作
SELECT * FROM single_table WHERE key1 a
OR ( key_part1 a AND key_part2 b AND key_part3 c);下边这两个查询就不能进行Union索引合并
SELECT * FROM single_table WHERE key1 a OR (key_part1 a AND key_part2 b AND key_part3 c);SELECT * FROM single_table WHERE key1 a OR key_part1 a;第一个查询是因为对key1进行了范围匹配第二个查询是因为联合索引idx_key_part中的key_part2和key_part3列并没有出现在搜索条件中所以这两个查询不能进行Union索引合并。
情况二主键列可以是范围匹配情况三使用Intersection索引合并的搜索条件
使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集。
explain SELECT * FROM single_table WHERE key_part1 a AND key_part2 b
AND key_part3 c OR (key1 a AND key3 b);优化器可能采用这样的方式来执行这个查询
先按照搜索条件key1 a AND key3 b从索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一个主键集合。再按照搜索条件key_part1 a AND key_part2 b AND key_part3 c从联合索引idx_key_part中得到另一个主键集合。采用Union索引合并的方式把上述两个主键集合取并集然后进行回表操作将结果返回给用户。
当然查询条件符合了这些情况也不一定就会采用Union索引合并也得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。
Sort-Union合并
explain SELECT * FROM single_table WHERE key1 a OR key3 z;先根据key1 a条件从idx_key1二级索引中获取记录并按照记录的主键值进行排序再根据key3 z条件从idx_key3二级索引中获取记录并按照记录的主键值进行排序因为上述的两个二级索引主键值都是排好序的剩下的操作和Union索引合并方式就一样了。
这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。 union适用于单条索引查询出来数据很少、intersect适用于单条查询出来数据比较多。 索引合并注意事项
联合索引替代Intersection索引合并
索引合并练习
MySQL 索引合并技巧!
explain select * from order_info
where period 202201 and modified 2019-01-06 18:00:00;
A ∩ B
通过执行计划可以看到走的modified索引树拿到modified数据再回表查找period数据。explain select * from order_info
where period 202201 or modified 2019-01-06 18:00:00;
A ∪ B
这样想象
A 走的是 period 索引
B 走的是 modified 索引
假如读到这些数据
A 2 1 4
B 3 1 2
or 需要去重 两个for循环的话是on次方
排序后去重的话
排序是ologn 查找可以用二分ologn
ologn ologn ologn https://dev.mysql.com/doc/refman/8.4/en/index-merge-optimization.html 多表使用or检索就很慢 SELECT * FROM innodb_tableWHERE primary_key#主键 10 AND key_col1#普通索引 20;SELECT * FROM tbl_nameWHERE key1_part1#联合索引第一部分 1 AND key1_part2#联合索引第二部分 2 AND key2#普通索引 2;explain select * from order_info
where period 202201 and modified 2019-01-06 18:00:00;explain select * from order_info
where period 202201 and modified 2019-01-06 18:00:00;explain select * from order_info
where period 202201 and modified 2019-01-06 18:00:00 and phone 52197927747;explain select * from order_info
where id 202201 and modified 2019-01-06 18:00:00;SELECT * FROM t1WHERE key1 1 OR key2 2 OR key3 3;SELECT * FROM innodb_tableWHERE (key1 1 AND key2 2)OR (key3 foo AND key4 bar) AND key5 5;explain select * from order_info
where id 202201 or modified 2019-01-06 18:00:00;结果集1取出id 的主键值排序本身是有序的 结果集2取出modified 的主键值排序 等值查询的时候modified相同的时候是按照主键排序的。 合并union两个结果集去重。
explain select * from order_info
where id 202201 or modified 2019-01-06 18:00:00;explain select * from order_info
where id 201901 or modified 2019-01-06 18:00:00;结果集1取出id 的主键值排序本身是有序的 结果集2取出modified 的主键值排序 此时范围查询再modified索引树主键就是无序的因此需要sort union。
总结
所有结论都需要反复测试如果有错误欢迎指正一起努力 如果喜欢的话请点个赞吧就算鼓励我一下。
