explain:用于查看SQL的执行计划
1)select_type:查询类型
- SIMPLE:简单select
- PRIMARY:最外面的select
- UNION:union中的第二个或者后面的select语句
- DEPENDENT UNION:union中的第二个或者后面的select语句,取决于外面的查询
- UNION RESULT:union的结果
- SUBQUERY:子查询中的第一个select
- DEPENDENT SUBQUERY:子查询的第一个select,取决于外面的查询
- DERIVED:导出表的select from子句的子查询
2)table:输出的行所引用的表
3)partitions:针对分区表,显示查询将访问的分区
4)type:联接类型
性能从底到高:all<index<range<index_merge<ref_or_null<ref<eq_ref<system/const
- system:理想状态,表仅有一行(=系统表)。这是const联接类型的一个特例
- const:主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量。
- ref_eq:使用了主键和唯一索引进行查找
- ref:查找条件使用了索引而且不为主键和unique。
- range:范围的索引扫描
- index:按照索引顺序进行全表扫描,与all都是取得了全表的数据,index是先读索引然后在回表随机取数据,取同一个表的数据时候index比all要慢。
- all:“全表扫描”,暴力查找
- index_merge:合并索引, 使用多个单列索引搜索
5)possible_keys:mysql可能使用的索引
6)key:mysql实际决定使用的索引,NULL没有选择索引
7)key_len:mysql决定使用的键长度,在不损失精确性的情况下,长度越短越好
8)ref:显示使用哪个列或常数与key一起从表中选择行
9)rows:mysql认为执行查询时必须检查的行数。多行之间的数据相乘可以估算处理的行数
10)filtered:显示通过条件过滤出的行数的百分比估计值
11)extra:解决查询的详细信息
- no matching row in const table:没有匹配的行
- using index:使用索引查询,并且无需回表
- using temporary:为了解决查询,mysql需要创建一个临时表来容纳结果,说明查询就需要优化了
- using filesort:无法利用索引完成排序操作的时候,mysql query optimizer不得不选择相应的排序算法来实现。数据较少时从内存排序,否则从磁盘排序。
- using where:表示mysql服务器将存储引擎返回行以后再应用where过滤条件。
- using index condition:先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用where子句中的其他条件过滤这些数据行
- using index for group-by:当query中使用group by或者distinct子句时,如果分组字段也在索引中,数据访问和using index一样,所需数据只须要读取索引。
- using join buffer(block nested loop)/Using join buffer(batched key access):Block Nested-Loop Join算法:将外层循环的行/结果集存入join buffer,内层循环的每一行与整个buffer中的记录做比较,从而减少内层循环的次数。
mysql两种排序算法:
-
两次传输排序(旧版本使用)
读取行指针和需要排序的字段,对其进行排序,然后再根据排序结果读取所需要的数据行 -
单次传输排序(新版本使用)
先读取查询所需要的所有列,然后再根据给定列进行排序,最后直接返回排序结果。
优点:相比两次传输排序,只需要一次顺序I/O读取所有的数据,而无须任何的随机I/O。
缺点:如果需要返回的列非常多,非常大,会额外占用大量的空间的,这些列对排序操作本身来说没有任何作用。
mysql会分两种情况处理文件排序
1)Using filesort:order by子句中的所有列都来自关联的第一个表,那么mysql在关联处理第一个表的时候进行文件排序。
2)Using temporary;Using filesort:mysql都会先将关联的结果存放到一个临时表中,然后在所有的关联结束之后再进行文件排序。这种情况下,即便使用limit控制返回较少的数据,临时表和需要排序的数据量仍然会非常大。
-
mysql5.6之后,当只需要返回部分排序结果的时候,例如使用了limit子句,mysql不再对所有的结果进行排序,而是根据实情况,选择抛弃不满足条件的结果,然后再进行排序。
-
无论如何的排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序或者尽可能避免对大量数据进行排序。
ps:仅用于学习,来源各方参考,不用于商业用途,不经同意不得转载!