SQL性能优化-索引

1.性能下降sql慢执行时间长等待时间长常见原因

1）索引失效
索引分为单索、复合索引。

四种创建索引方式

create index index_name on user (name);
create index index_name_2 on user(id,name,email);
2）查询语句较烂
3）关联查询太多join，sql设计不合理
4）服务器问题。

2. explain使用

explain关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。

2.1 ID 参数

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。三种情况：
【1】id 相同：执行顺序由上而下；

explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';

【2】id 不同：如果是子查询，id 序号会递增，id 越大优先级越高，越先被执行；

explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));

【3】id 相同不同同时存在：id 如果相同，可以认为是一组，由上往下执行；在所有组里 id 越大，优先级越高，越先执行；

explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 where s1.id = t2.id;

2.2select_type 数据读取操作类型

【1】simple
简单的 select 查询，查询中不包含子查询或者 UNION；
【2】primary
查询中若包含任何复杂的自查询，最外层查询为 PRIMARY；
【3】subquery
在 SELECT 或 WHERE 中包含子查询；
【4】derived
在 FROM 列表中包含的子查询被标记为 DERIVED（衍生）MySQL 会递归执行这些子查询，把结果放进临时表；
【5】union
若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION，若 UNION 包含在 FROM 子句的子查询，则外层SELECT 将被标记为 DERIVED；
【6】union result
从 UNION表中获取结果的 SELECT；

2.3 type 访问类型

从最好到最差：system>const>eq_ref>ref>range>index>ALL，一般达到 rang 级别，最好达到 ref 级别。

【1】system：表只有一行记录（系统表），平时不会出现；
【2】const：表示通过索引一次就能找到，const用于比较 primary和 unique索引。因为只匹配一行数据，所以很快；
【3】eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描。eg:CEO部门；

【4】ref：非唯一性索引扫描，返回匹配某个单独值的所有行；
【5】rang：只检索给定范围的行，使用一个索引来选择行。一般where语句中出现between、<、>、in等的查询。这种范围扫描索引比全表扫描要好，因为只需开始索引的某一点，而结束另一点，不用扫描全部索引；
【6】index：Full Index Scan，index与 ALL区别为 index类型只遍历索引树，索引文件通常比数据文件小。index从索引中读取，而All是从硬盘读取；
【7】ALL：从磁盘中读取

2.4 possible_keys 与 key

possible_keys：显示可能应用到这张表中的索引，一个或多个，查询字段上若存在索引则列出来，但不一定被查询实际使用。
key：实际使用的索引，如果该值为NULL，则没有使用索引；如果查询中使用了覆盖索引，则该索引仅出现在 key列表中。
【覆盖索引】：就是 select后面的字段都具备索引，提高了查询效率，前提顺序、个数都要一致；
【理解方式一】：就是 select的数据列从索引中就能够获取到，不必读取没有必要多余的数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建索引覆盖。
【理解方式二】：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个数据行，毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
【使用覆盖索引注意】：如果使用覆盖索引，一定注意 select列表中只取需要的列，不可使用select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

2.5 key_len

表示索引中使用的字节数，可通过该列查找出使用索引的长度。在不损坏精准性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际长度，即 key_len是根据表定义实际计算出来的，不是通过表内检出来的。

2.6 ref

显示索引的那一列被使用，如果可能的话，是一个常数。那些列或常量被用于查找索引上的值。

2.7 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录的行数。常用于优化时查看，使用该值与实际返回的行数进行比较，如果相差很大，则需要调优。

2.8 Extra

包含不适合在其他列中显示，但十分重要的信息。
【1】Using fileSort：说明 MySQL会对数据使用一个外部的索引排序，而不是按照表内索引进行读取。MySQL无法利用索引完成的排序操作称为“文件排序”。（出现表示不好）
【2】Using temporary：使用临时表保存中间结果，MySQL在查询结果排序时使用临时表。重用于排序 order by和分组查询 group by。
【3】Using index：表示相应的 select操作中使用了覆盖索引（convering index），避免访问了表的数据行，效率不错！

【4】using where,using index：查询的列被索引覆盖，并且 where筛选条件是索引列之一但是不是索引的前导列，Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据；

查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查询到符合条件的数据

【5】NULL（既没有Using index，也没有Using where Using index，也没有using where）：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)

【6】Using where：查询条件中使用了索引查找。查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra 中为 Using where。order_id 也是索引。

查询的列未被索引覆盖，where筛选条件非索引列，Extra中为Using where。意味着通过索引或者表扫描的方式进行 where条件的过滤，反过来说，也就是没有可用的索引查找，当然这里也要考虑索引扫描+回表与表扫描的代价。这里的 type都是 all，说明MySQL认为全表扫描是一种比较低的代价。

【7】Using index condition：查询的列不全在索引中，where条件中是一个前导列的范围

查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）

【8】Using join buffer：使用了连接缓存。
【9】impossible where：where子句总是false，不能用来获取任何元素。
【10】select tables optimized away：在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX操作。
【11】distinct：优化 distinct操作。在找到第一匹配的时候就停止找同样的动作。