278:union all 和 or 可以进行替换。
union:对两个结果集进行并集操作,去重,按照默认规则排序;
union all:对两个结果集并集操作,不去重,不排序;
or:满足两个条件的并集,不去重,不排序;
1.如果or字段是索引字段,那么使用unionall代替or操作,可以走索引;
2.如果能用union all ,尽量不要用union,相当于distinct又执行了order by;
279:union 和 or+distinct 可以进行替换。
280:要对一个子查询进行优化的很重要的方式就是通过索引来避免频繁的访问表,同时子查询中要避免访问不必要列,子查询尽量完全走索引。
281:5.6 版本里面针对使用 order by 排序列 limit 进行分页的场景,保证排序列上有索引,mysql 会自动使用索引。
282:执行计划关注的重要点包括哪些?
type | 本次查询表联接类型,从这里可以看到本次查询大概的效率 |
key | 最终选择的索引,如果没有索引的话,本次查询效率通常很差 |
Key_len | 本次查询用于结果过滤的索引实际长度 |
rows | 预计需要扫描的记录数,预计需要扫描的记录数越小越好 |
Extra | 额外附加信息,主要确认是否出现 Using filesort、Using temporary 这两种情况 |
分别介绍各列结果类型:
type | |
ALL | 执行full table scan,这事最差的一种方式 |
index | 执行full index scan,并且可以通过索引完成结果扫描并且直接从索引中取的想要的结果数据,也就是可以避免回表,比ALL略好,因为索引文件通常比全部数据要来的小 |
range | 利用索引进行范围查询,比index略好 |
index_subquery | 子查询中可以用到索引 |
unique_subquery | 子查询中可以用到唯一索引,效率比 index_subquery 更高些 |
index_merge | 可以利用index merge特性用到多个索引,提高查询效率 |
ref_or_null | 表连接类型是ref,但进行扫描的索引列中可能包含NULL值 |
fulltext | 全文检索 |
ref | 基于索引的等值查询,或者表间等值连接 |
eq_ref | 表连接时基于主键或非NULL的唯一索引完成扫描,比ref略好 |
const | 基于主键或唯一索引唯一值查询,最多返回一条结果,比eq_ref略好 |
system | 查询对象表只有一行数据,这是最好的情况 |
上面几种情况,从上到下一次是最差到最好。
Extra | |
Using filesort | 将用外部排序而不是按照索引顺序排列结果,数据较少时从内存排序,否则需要在磁盘完成排序,代价非常高,需要添加合适的索引 |
Using temporary | 需要创建一个临时表来存储结果,这通常发生在对没有索引的列进行GROUP BY时,或者ORDER BY里的列不都在索引里,需要添加合适的索引 |
Using index | 表示MySQL使用覆盖索引避免全表扫描,不需要再到表中进行二次查找数据,这是比较好的结果之一。注意不要和type中的index类型混淆 |
Using where | 通常是进行了全表/全索引扫描后再用WHERE子句完成结果过滤,需要添加合适的索引 |
Impossible WHERE | 对Where子句判断的结果总是false而不能选择任何数据,例如where 1=0,无需过多关注 |
Select tables optimized away | 使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询,例如MIN()\MAX(),这种也是比较好的结果之一 |
283:如何通过 id 来确认表的连接顺序。
id:id是一组数字,表示查询中执行select子句或操作表的顺序,如果id相同,则执行顺序从上至下,如果是子查询,id的序号会递增,id越大则优先级越高,越先会被执行。
284:如何通过 rows、key 来确认表连接是否走了索引,以及索引的效果。
key:查询真正使用到的索引,select_type为index_merge时,这里可能出现两个以上的索引,其他的select_type这里只会出现一个。有值则表示走了索引。
rows:这里是执行计划中估算的扫描行数,不是精确值。行数少表示索引效果好。
285:理解表连接中的非第一个表的 rows 的具体含义。
rows指的是查询的行数,非第一个表的rows指利用其它表查询出来的结果在对本表进行相关查询出的结果。
286:如何判断表连接顺序的入口表的选择是否合适。
通过执行计划查看入口表的执行情况,例如key、rows列的状态。
287:查看执行计划走的是相关还是无关子查询。
通过查看select的type列中的值,DEPENDENT意味着select依赖于外层查询中发现的数据。
288:执行计划查看细节分析,写出 SQL,查看执行计划。
1、如何来判断作为入口表选择是否合适?
2、如何判断访问这个表的索引的效率如何?
3、如何判断表连接所使用的索引效果如何?
4、如何判断是否相关子查询还是无关子查询?
5、如何判断表的连接顺序?
6、如何判断表连接的连接列?
7、如何判断子查询是否完全实现了索引化?
8、如何判断是否实现了索引合并?
9、如何判断是否实现索引覆盖?
10、如何判断是否走了全表扫描?
11、如何判断这个 SQL 访问的总行数?
12、如何判断一个索引访问是唯一索引访问还是范围索引访问,范围索引访问效果如
何?
13、mysql 中 is null、is not null 是否走索引?
14、mysql 中 key_column=expr OR key_column IS NULL;是否走索引?
15、如何判断是否有排序、如何判断是否是大排序?
16、如何判断 SQL 是否实现了分区消除?
289:互联网 MySQL 架构在高可用和负载均衡方面的基本要素?
MySQL 架构一定要结合前台业务来设计、优化,所以不管是哪种架构、根据业务要求组合成符合需求的即是最好的、不能泛泛而谈同时、也必须注意数据的安全(如ipsec,ssh,vpn传输)。
MySQL常见的架构都是进行业务切分、前端缓存、分库分表。若是过亿的查询量则先从业务上拆分、将 bbs、web、blog 分成几个组、然后再做成一主多从、读写分离的方式。而且、在设计表的时候、一般情况下、备库常充当起备份查询的作用,至于读写分离、在程序设计之初、读和写是通过不同的IP入口、这是思路一、或者定义类、或者用代理层,比如 MySQL-proxy大多数的场合、一般在应用层做读写分离、然后 MySQL 通过复制来实现、优点比较多,可控性非常好。