胶州网站建设规划,昆山城乡建设局网站,自己注册公司一年多少费用,自己做的网站怎么绑定域名Explain简介Explain关键字是Mysql中sql优化的常用「关键字」#xff0c;通常都会使用Explain来「查看sql的执行计划#xff0c;而不用执行sql」#xff0c;从而快速的找出sql的问题所在。在讲解Explain之前首先创建需要的「用户表user、角色表role、以及用户角色关系表role_… Explain简介Explain关键字是Mysql中sql优化的常用「关键字」通常都会使用Explain来「查看sql的执行计划而不用执行sql」从而快速的找出sql的问题所在。在讲解Explain之前首先创建需要的「用户表user、角色表role、以及用户角色关系表role_user」作为测试用的表// 用户表DROP TABLE IF EXISTS user;CREATE TABLE user ( id int(11) NOT NULL, name varchar(25) DEFAULT NULL, age int(11) NOT NULL DEFAULT 0, update_time datetime DEFAULT NULL, PRIMARY KEY (id)) ENGINEInnoDB DEFAULT CHARSETutf8;INSERT INTO user (id, name, age,update_time) VALUES (1,张三,23,2020-12-22 15:27:18), (2,李四,24,2020-06-21 15:27:18), (3,王五,25,2020-07-20 15:27:18);DROP TABLE IF EXISTS role;CREATE TABLE role ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) DEFAULT NULL, PRIMARY KEY (id), KEY index_name (name)) ENGINEInnoDB DEFAULT CHARSETutf8;INSERT INTO role (id, name) VALUES (1,产品经理),(2,技术经理),(3,项目总监);DROP TABLE IF EXISTS role_user;CREATE TABLE role_user ( id int(11) NOT NULL, role_id int(11) NOT NULL, user_id int(11) NOT NULL, PRIMARY KEY (id), KEY index_role_user_id (role_id,user_id)) ENGINEInnoDB DEFAULT CHARSETutf8;INSERT INTO role_user (id, role_id, user_id) VALUES (1,2,1),(2,1,2),(3,3,3); 我们首先执行一条sqlexplain select * from user where id 2;执行后可以看到执行的结果如下可以看到这里有12个字段那个且都有对应的值这就是explain的执行计划能看懂这个执行计划你离精通sql优化就不远了下面就来详细的介绍这12个字段分别表示什么意思。id字段id表示执行select查询语句的序号它是sql执行的顺序的标识sql按照id从大到小执行id相同的为一组从上到下执行。什么意思呢例如执行这条sqlexplain select * from user where id in (select user_id from role_user);----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 1 | SIMPLE | user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | NULL || 1 | SIMPLE | role_user | NULL | index | NULL | index_role_user_id | 8 | NULL | 3 | 33.33 | Using where; Using index; FirstMatch(user); Using join buffer (Block Nested Loop) |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------显示出的两者的id都相同便表示sql的执行从上往下执行第一条记录对应的是user表然后第二条记录对应的是role_user表这种是id相同的情况。若是id不同例如执行下面的sqlexplain select (select 1 from user limit 1) from role;------------------------------------------------------------------------------------------------------------------| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |------------------------------------------------------------------------------------------------------------------| 1 | PRIMARY | role | NULL | index | NULL | index_name | 33 | NULL | 3 | 100.00 | Using index || 2 | SUBQUERY | user | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using index |------------------------------------------------------------------------------------------------------------------就会看到有两条记录并且两条记录的id会不一样id越大的就越先执行可以看到id2的执行的是user表也就是子查询部分最后执行最外层的部分。「结论」 这个就是id标识sql的执行顺序一般在复杂查询中会有多条记录简单查询只有一条记录复杂查询中id相同的为一组执行的顺序是从上往下而id越大的越先执行Mysql 8中会存在对子查询进行优化所以有时候即使是复杂查询也只有一条记录。select_type字段select_type表示查询的类型也就是对应的是简单查询还是复杂查询若是复杂查询又包含「简单的子查询、from子句的子查询、union查询」。下面就分别来看看select_type中的所有查询类型。simplesimple表示简单查询不含有任何的复杂查询。PRIMARY复杂查询中「最外层的select语句的查询类型就是PRIMARY」例如执行下面的sqlexplain select * from role where id (select id from role_user where role_id (select id from user where id 2));最外层的select也就是select * from role where id 会被标记为PRIMARY类型。SUBQUERY在「select或者where中包含的子查询」会被表示为SUBQUERY类型例如上一句执行的sql中就有两次的子查询为SUBQUERY。DERIVED「DERIVED表示的是派生表或者衍生表的意思在from包含的子查询中会被表示为DERIVED类型」Mysql会递归执行这些子查询并且把结果放在临时表中。执行sqlexplain select * from (select name from user union select name from role) a where a.name 张三;在Mysql 5.7以上的版本中对其做了优化新增了derived_merge(派生合并)可以加快查询效率。UNION在出现「UNION查询语句中第二个select的查询语句就会被表示为UNION」UNION RESULT「UNION查询语句的结果被标记为UNION RESULT」如上面执行的sqlexplain select * from (select name from user union select name from role) a where a.name 张三;第四行记录中从table字段中可以看出第四行的记录来源于第二行和第三行因此一个UNION查询语句的结果就会被标记为UNION RESULT其它上面的七个select_type都是比较常见的还有一些不常见的作为了解就好DEPENDENT UNION也表示UNION查询语句中第二个或者后面的语句但是取决于外面的查询。DEPENDENT SUBQUERY子查询中的第一个select语句也是依赖于外部的查询。UNCACHEABLE SUBQUERY子查询的结果不能被缓存必须重新评估外连接的第一行。table字段这个很容易看出「table字段表示的是查询的是哪个表」一个是已经存在的表比如上面的user、role都是我们自己创建的表也可以表示衍生表。比如UNION RESULT的table字段表示为也就是查询的是第二行和第三行的结果记录。type字段「type字段表示的sql关联的类型或者说是访问的类型」。从这个字段中我们可以确定这条sql查找数据库表的时候查找记录的大概范围是怎么样的直接就能体现sql的效率问题。type字段的类型也是有比较多主要常见掌握的有以下几个system、const 、eq_ref 、ref 、range 、index 、ALL。它的性能体现是从高到低即system const eq_ref ref range index ALL下面就来详细的说一说这属性。systemsystem是const的特例「表示表中只有一行记录」这个几乎不会出现也作为了解。constconst表示通过索引一次就查找到了数据一般const出现在「唯一索引或者主键索引中使用等值查询」因为表中只有一条数据匹配所以查找的速度很快。例子explain select * from user where id 2;eq_refeq_ref表示使用唯一索引或者主键索引扫描作为表链接匹配条件对于每一个索引键表中只有一条记录与之匹配。例如explain select * from user left join role_user on user.id role_user.user_id left join role on role_user.role_idrole.id;refref性能比eq_ref差也表示表的链接匹配条件也就是使用哪些表字段作为查询索引列上的值ref与eq_ref的区别就是eq_ref使用的是唯一索引或者主键索引。ref扫描后的结果可能会找到多条符合条件的行数据本质上是一种索引访问返回匹配的行。例如explain select * from user where name 张三;range「range使用索引来检索给定范围的行数据一般是在where后面使用between、、in等查询语句就会出现range」explain select * from user where id 2;indexindex表示会遍历索引树index回避ALL速度快一些但是出现index说明需要检查自己的索引是否使用正确explain select id from user;ALL「ALL与index的区别就是ALL是从硬盘中读取而index是从索引文件中读取」ALL全表扫描意味着Mysql会从表的头到尾进行扫描这时候表示通常需要增加索引来进行优化了或者说是查询中并没有使用索引作为条件进行查询explain select * from user;possible_keys字段possible_keys表示这一列查询语句可能使用到的索引仅仅只是可能列出来的索引并不一定真正的使用到。当没有使用索引为NULL时说明需要增加索引来优化查询了若是表的数据比较少的话数据库觉得全表扫描更快也可能为NULL。key字段key字段与possible_keys的区别就是表示的真正使用到的索引即possible_keys中包含key的值。若是想Mysql使用或者忽视possible_keys中的索引可以使用FORCE INDEX、USE INDEX或者IGNORE INDEX。key_len字段表示sql查询语句中索引使用到的字节数这个字节数并不是实际的长度而是通过计算查询中使用到的索引中的长度得出来的显示的是索引字段最大的可能长度。一般来说在不损失精度的前提下key_len是越小越好比如上面的测试表的id为int类型int类型由4个字节组成explain select * from user where id 2;key_len对于不同的类型有自己的计算规则具体的计算规则如下所示数据类型所占字节数字符串char(n)n字节长度varchar(n)2字节存储字符串长度如果是utf-8则长度 3n 2数值类型tinyint1字节smallint2字节int4字节 bigint8字节时间类型date3字节timestamp4字节datetime8字节若是索引为字符串类型的时候实际存储的字符串非常长已经超出了字符串类型的存储最大长度(768字节)mysql就会使用类似左前缀索引来处理。ref字段ref表示列与索引的比较表连接的匹配条件表示哪些列或者常量被用于查询索引列上的值。rows字段rows表示估算的要扫描的行数一般Mysql会根据统计表信息和索引的选用情况估算出 查找记录所要扫描的行数注意这个并不是实际结果集的行数。partitions、filtered字段partitions表示所匹配的分区filtered表示的是查询表行所占表的百分比。Extra字段该字段显示的是sql查询的额外信息主要有以下几种情况Using index表示查询的列被索引覆盖这个是查询性能比较高的体现即所要查询的信息搜在索引里面可以得到不用回表索引被正确的使用explain select id from user where id 2;假如同时出现了using where表示索引用于执行索引键值的查找若是没有出现using where则表示索引用于读取数据而非执行查询的动作。Using where该属性与Using index相反查询的列并没有被索引覆盖where条件后面使用的是非索引的前导列它仅仅是使用了where条件而已explain select user.* from user,role,role_user where user.id role_user.user_id and role.idrole_user.role_id;Using temporary「Using temporary表示使用了临时表存储中间的结果一般在对结果排序的时候会使用临时表」例如排序order by 和分组查询group by。例子explain select * from (select name from user union select name from role) a where a.name 张三;Using filesortUsing filesort表示文件排序说明Mysql对数据使用了外部的索引进行排序并没有使用表中的索引进行排序explain select * from user order by name;Using join bufferUsing join buffer表示使用连接缓存explain select user.* from user,role,role_user where user.id role_user.user_id and role.idrole_user.role_id;它强调在获取连接条件时并没有使用索引而是使用连接缓冲区来存储中间结果若是出现该值一般说明需要添加索引来进行优化了。Impossible whereImpossible where会出现在where后的条件一直为false的情况下这种可以忽视比较少出现explain select * from user where name hah and name sfsd;Select tables optimized away表示select语句没有遍历表或者索引就返回数据了比如explain select min(id) from user;在Extra字段中还有其它的属性但是几乎都没见过的不出现所以哪些就讲解有兴趣的可以自己去了解这里只列出这些常见的。说了那么多理论总是要实践一下的下面以user测试表为例进行测试实践。实践(1)通过查询 explain select * from user where name 张三;name字段并没有创建索引。我们可以通过创建一个联合索引index_name_age_time来解决alter table user add index index_name_age_time (name,age,update_time) ;当再次查询的时候就会使用上了索引(2)使用联合索引要遵循「最左前缀法则」关于最左前缀法则原则的使用之前我写过一篇详细介绍的文章可以参考[]。(3)在使用索引进行查询的时候不要做任何的函数操作不然会导致索引失效例子EXPLAIN SELECT * FROM user WHERE name 张三;但是你在使用的时候使用了left()函数如EXPLAIN SELECT * FROM employees WHERE left(name,2) 张三;会导致索引失效。(4)在数据库的查询中不要使用(或者)等判条件和is null,is not null、like关键词中以%开头来判断不然也会使索引失效大专半年Java面试JVM基础扎实当场发offer2020-07-22 Spring高阶必问为什么使用三级缓存解决循环依赖2020-07-27 Java中异常处理的9个最佳实践2020-07-18点个在看,赞?支持我吧
