数据库学习笔记-EXPLAIN

EXPLAIN

explain可以对select语句进行分析，并输出select执行的详细信息，供开发人员有针对性的进行优化。

• select_Type
  表示查询的类型，常用的值如下：
  simple：表示查询的语句不包含子查询或union
  primary：表示此查询时最外层的查询
  union：表示此查询时union的第二个或后续查询
  dependent union：union中的第二个或后续的查询语句，使用了外面的查询结果
  union result：union的结果
  subquery：select子查询语句
  dependent subquery：select子查询语句依赖外层查询的结果
  最常见的查询类型时simple，表示我们的查询没有子查询也没有union查询
• type
  表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询时全表扫描还是基于索引的部分扫描。常用属性从上到下效率依次增强
  ALL：表示全表扫描，性能最差
  INDEX：表示基于索引的全表扫描，先扫描索引再扫描全表数据
  RANGE：表示使用索引范围查询。使用>、>=、<、<=、in等
  REF：表示使用非唯一索引进行单值查询
  EQ_REF：一般情况下出现在多表join查询，表示前面表的一个记录，都只能匹配后面表的一行结果
  CONST：表示使用主键或唯一索引做等值查询，常量查询。
  NULL：表示不用访问表，速度最快
• possible_keys
  表示查询时能够使用到的索引。并不一定会真正使用，显示的是索引名称
• key
  表示查询时真正使用到的索引，显示的是索引名称
• rows
  mysql查询优化器会根据统计信息，估算sql要查询到结果需要扫描多少行记录。原则上rows越少效率越高，可以直观的了解到sql效率高低
• key_len
  表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
• extra
  extra表示很多额外的信息，各种操作会在extra提示相关信息，常见几种：
  using where：表示查询需要通过索引回表查询数据
  using index：表示查询需要通过索引，索引可以满足所需数据
  using filsort：表示查询出来的结果需要额外排序，数据量小在内存，大的在磁盘，因此有using filesort建议优化
  using tempprorary：查询使用到了临时表，一般出现用于去重、分组等操作

回表操作

extra中显示为using where
InnoDB索引有聚簇索引和辅助索引（非聚簇索引）。聚簇索引的叶子阶段存储行记录，innoDB必须要有，且只有一个。辅助索引的叶子阶段存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫描两边索引树：先通过辅助索引定位主键值，然后通过聚簇索引定位行记录，这就叫回表查询，它的性能比扫一遍索引树低

覆盖索引

explain中extra字段的值为using index
只需要在一颗索引树上就能获取sql所需的所有列数据，无需回表，速度块，这就叫索引覆盖

最左匹配原则

符合索引使用时遵循最左匹配原则，查询中使用到最左边的列，那么查询就会使用到索引，如果索引从第二列开始查找，则索引失效

like查询

mysql在使用like查询时，当查询条件的前面有“%”的时候，索引会失效
select * from user where fname like "%a%"; -- 索引失效
select * from user where fname like "%a"; -- 索引失效
select * from user where fname like "a%"; -- 索引有效

null查询

mysql虽然可以在含有null的列上使用索引，但null和其他数据相比还是有区别的，不建议列上允许为null。最好设置not null，并给一个默认值
对mysql来说，null是一个特殊值，它不能够使用=><等运算符，对null做算数运算的结果都为null，count的时候不会包括null行，null比空字符串需要更多的存储空间等

索引与排序

mysql查询支持filesort和index两种方式的排序，filesort是先把结果查询出来，在缓存或磁盘中排序，效率低。使用index是指利用索引自动实现排序，不需要另作排序操作，效率比较高