企业官网建设流程全解析

沧州网站设计报价,福建搜索引擎优化,小说类型网站怎么做,设计网络培训使用 MySQL 时#xff0c;我们经常会听到“回表”、“索引下推”这样的概念#xff0c;今天就来聊一聊什么是回表#xff0c;什么是索引下推。 1.回表 1.1 概念 我们看下面这个 SQL#xff1a; CREATE TABLEtest_temp ( idINT(11) NOTNULLDEFAULT0, aVARCHAR(20) DEFAU…使用 MySQL 时我们经常会听到“回表”、“索引下推”这样的概念今天就来聊一聊什么是回表什么是索引下推。1.回表1.1 概念我们看下面这个 SQLCREATE TABLEtest_temp ( idINT(11) NOTNULLDEFAULT0, aVARCHAR(20) DEFAULTNULL, bVARCHAR(10) DEFAULTNULL, PRIMARY KEY (id), KEY(b) ) ENGINEINNODBDEFAULTCHARSETutf8我们创建一个 test_temp 表主键是 id给字段 b 加了一个索引。插入 4 条数据SQL 如下INSERT INTO test_temp(100, 10, 50); INSERT INTO test_temp(200, 20, 40); INSERT INTO test_temp(300, 30, 30); INSERT INTO test_temp(400, 40, 10);test_temp 表会构建 2 个索引一个是主键索引一个是字段 b 的普通索引。一般主键索引被称为 聚集索引普通索引被称为 非聚集索引。我们执行下面查询 SQLselect * from test_temp where b in(10, 20, 30 ,40);这个 SQL 语句的查询过程如下图1.从索引 b 上查询 10查到主键 id 的值是 400再用 400 这个 id 去主键索引上取出 row42.从索引 b 上查询 20没有查到记录继续下一条3.从索引 b 上查询 30查到主键 id 的值是 300再用 300 这个 id 去主键索引上取出 row34.从索引 b 上查询 40查到主键 id 的值是 200再用 200 这个 id 去主键索引上取出 row25.给客户端返回结果集。上面 1、3、5 回到主键索引搜索数据的过程就叫回表。上面查询回表 3 次。1.2 缺点回表有什么问题吗回表次数多了可能会严重影响查询效率。1.导致磁盘 I/O 增加每次回表读取数据行这些数据分散在磁盘各个地方导致大量的磁盘 I/O。2.导致缓存失效回表的数据如果不在缓存行中就需要从磁盘加载新的数据可能会覆盖已有的缓存影响其他查询。1.3 措施那有什么方法可以避免回表吗下面两个方法可以避免1.覆盖索引上面的查询中如果 SQL 改成select b, id from test_temp where b in(10, 20, 30 ,40);这样就不用回表查询了。如果需要查询 b、a 两个字段可以创建 b、a 的覆盖索引这样就可以从 b、a 这个覆盖索引上查询出结果。2.只查询必要字段修改查询范围不用的字段不查询。如果查询的字段不多可以把查询语句改成只查联合索引包含的字段。如果查询频率高又没有覆盖索引可以加一个包含查询字段的联合索引。2.索引下推首先回顾一下 MySQL 的逻辑架构Server 层是 MySQL 的核心服务层这一次包括查询解析、分析、优化、缓存、以及所有内置函数例如日期、时间、数学和加密函数所有跨存储引擎的功能都在这一层实现包括存储过程、触发器、视图等。存储引擎层负责 MySQL 中数据的存储和提取。首先我们创建一张表CREATE TABLEtest_temp ( idINT(11) NOTNULLDEFAULT0, aVARCHAR(20) DEFAULTNULL, bVARCHAR(10) DEFAULTNULL, cVARCHAR(10) DEFAULTNULL, dVARCHAR(10) DEFAULTNULL, PRIMARY KEY (id), KEYa_b(a,b) ) ENGINEINNODBDEFAULTCHARSETutf8插入一批数据INSERT INTO test_temp VALUES(100, 10, 20, 2, 1); INSERT INTO test_temp VALUES(200, 10, 40, 4, 2); INSERT INTO test_temp VALUES(300, 10, 30, 3, 3); INSERT INTO test_temp VALUES(400, 40, 10, 1, 4);这时我们看一下下面这条 SQL 的执行计划EXPLAIN SELECT * FROM test_temp WHERE a 10 AND b 50;我们看一下执行计划上图中的 Using index condition 就是使用了索引下推。如果不使用索引下推比如只对 a 这个字段加了索引那就会对 a 这个字段筛选出来的 id依次做回表查询查到结果后再对 b 字段进行过滤。而使用了索引下推SQL 执行过程如下1.Server 层向存储引擎查询数据2.存储引擎根据 a_b 联合索引首先找到所有 a 10 的数据根据联合索引中已经存在的 b 字段对数据做过滤找出符合条件 b 50 的数据3.存储引擎根据 a_b 联合索引找到所有符合条件的数据后回表查询给 Server 层返回结果集。可以看到索引下推最大的优势就是在存储引擎层利用联合索引的优势对查询条件进行了过滤这样可以减少回表查询次数从而大大减少 I/O 次数提升查询性能。索引下推是在 MySQL 5.6 版本中才引入的MySQL 5.6 以前版本没有这个功能。当然使用索引下推也有一定限制1.索引下推主要适用于 eq_ref、range、ref、ref_or_null 这几个场景2.InnoDB 和 MyISAM 存储引擎都支持索引下推MySQL 分区表也支持3.对 InnoDB 存储引擎来说索引下推只适用于二级索引主键索引聚集索引不支持因为主键索引存储了数据不存在回表这一说;4.语句中子查询的条件不支持索引下推5.使用了存储函数的 SQL存储函数中的条件不支持索引下推因为存储引擎无法调用存储函数。我们再看下面这个查询语句把条件 b 改成条件 cEXPLAIN SELECT * FROM test_temp WHERE a 10 AND c 50;这个语句其实并不能使用联合索引第二个字段在存储引擎层做过滤还是需要对每一条索引 a_b 上查询到的 id 做回表查询但是执行计划里面却有索引下推这也是需要注意的一点。总结本文介绍了 MySQL 的回表和索引下推这两个概念在 MySQL 中非常重要希望对你的学习和面试有所帮助。