## 一、执行计划 explain这个命令来查看一个SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描等； 举例来说： ``` select * from card_info; explain select * from card_info; ``` 一个例子：  解释： | 列名 | 说明 | | --- | --- | | id | 选择标识符 | |select_type |表示查询的类型。| |table |输出结果集的表 | |partitions |匹配的分区 | |type |表示表的连接类型 | |possible_keys |表示查询时，可能使用的索引 | |key |表示实际使用的索引 | |key_len |索引字段的长度 | |ref |列与索引的比较 | |rows |扫描出的行数(估算的行数) | |filtered |按表条件过滤的行百分比 | |Extra |执行情况的描述和说明 | ### **id** 查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序 1. id相同，执行顺序从上往下； 2. id不同，id值越大，优先级越高，越先执行； ### **select_type** 表示查询中每个select子句的类型； 查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1. simple ——简单的select查询，查询中不包含子查询或者UNION 2. primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3. subquery/dependent subquery——在select或where列表中包含了子查询； 4. derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中； 5. union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived，故在union中第二个及之后的select。 6. union result:UNION 临时表检索结果的select。 ### **table** 输出的行所引用的表； ### **partitions** 如果查询基于分区表，将会显示访问的是哪个区； ### **type** 显示连接类型，显示查询使用了何种类型，按照从最佳到最坏类型排序： 1.system：表中仅有一行（=系统表）这是const联结类型的一个特例； 2.const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量； 3.eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配常见于唯一索引或者主键扫描，常用于连接查询。简单查询不会出现该类型； 4.ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，是使用普通索引或者唯一性索引的部分前缀，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体； 5.range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描； 6.index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多； 7.all：遍历全表以找到匹配的行； >[danger] > 1、性能按照type排序：system > const > eq_ref > ref > range > index > ALL； > 2、注意:一般保证查询至少达到range级别，最好能达到ref； ### **possible_keys** 指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）； ### **Key** 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。 要想强制mysql使用或者忽视possible_key列中的索引，在查询中使用force index、use index或者ignore index。 ### **key_len** 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）； >[danger] 不损失精确性的情况下，长度越短越好 ### **ref** 显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值； ### **rows** 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数； ### **filtered** 指返回结果的行占需要读到的行(rows列的值)的百分比； ### **Extra** 包含不适合在其他列中显示，但是十分重要的额外信息； 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”； 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by； 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作； 4、Using where :表明使用where过滤； 5、using join buffer:使用了连接缓存； 6、impossible where:where子句的值总是false，不能用来获取任何元组； 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化； 8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作； >[danger] 性能按照extra排序 > Using index>Using index condition>Using where>Using join buffer (Block Nested Loop)>Using filesort>Using temporary>Start temporary, End temporary>FirstMatch(tbl_name) ## 二、优化手段 1. SQL语句中IN包含的值不应过多，不能超过200个，200个以内查询优化器计算成本时比较精准，超过200个是估算的成本，另外建议能用between就不要用in，这样就可以使用range索引了。 2. SELECT语句务必指明字段名称：SELECT * 增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加 了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名； 3. 当只需要一条数据的时候，使用limit 1； 4. 排序时注意是否能用到索引； 5. 使用or时如果没有用到索引，可以改为union all 或者union； 6. 如果in不能用到索引，可以改成exists看是否能用到索引； 7. 不要用全表update或非索引条件update，会导致锁表，引起性能问题；例如，如下语句`update SysStaff set sts=:sts where expDate !='' and expDate<=:now`，必须确保expDate 字段为索引，方可使用，否则，每次执行会锁表； 8. 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL； 9. 创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减，因为如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性； 10. 如果取值范围有限，那么也不必建立索引，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引；