mysql如何写出高效执行的sql

一、如何写出高性能SQL语句

1、慢SQL语句的集中常见诱因

1）无索引、索引失效

• 如果在一张几千万数据的表中以一个没有索引的列作为查询条件，大部分情况下查询会非常耗时，这种查询毫无疑问是一个慢 SQL 查询。所以对于大数据量的查询，我们需要建立适合的索引来优化查询。
• 虽然我们很多时候建立了索引，但在一些特定的场景下，索引还有可能会失效，所以索引失效也是导致慢查询的主要原因之一。

2）锁等待

常用的存储引擎有 InnoDB 和 MyISAM，前者支持行锁和表锁，后者只支持表锁。InnoDB 存储引擎支持的行锁更适合高并发场景。但在使用 InnoDB 存储引擎时，我们要特别注意行锁升级为表锁的可能。

• 在批量更新操作时，行锁就很可能会升级为表锁。

  a)MySQL 认为如果对一张表使用大量行锁，会导致事务执行效率下降，从而可能造成其它事务长时间锁等待和更多的锁冲突问题发生，致使性能严重下降，所以 MySQL 会将行锁升级为表锁。

  b)行锁是基于索引加的锁，如果我们在更新操作时，条件索引失效，那么行锁也会升级为表锁。
  当“值重复率”低时，甚至接近主键或者唯一索引的效果，“普通索引”依然是行锁；当“值重复率”高时，MySQL 不会把这个“普通索引”当做索引，即造成了一个没有索引的 SQL，此时引发表锁

• 行锁相对表锁来说，虽然粒度更细，并发能力提升了，但也带来了新的问题，那就是死锁。因此，在使用行锁时，我们要注意避免死锁。

3）不恰当的SQL语句

使用不恰当的 SQL 语句也是慢 SQL 最常见的诱因之一。例如，习惯使用<select *>、<select count(*)> SQL 语句，在大数据表中使用 <limit m,n>分页查询，以及对非索引字段进行排序等等

2、优化SQL语句的步骤

1）通过EXPLAIN分析SQL执行计划

通常，我们在执行一条 SQL 语句时，要想知道这个 SQL 先后查询了哪些表，是否使用了索引，这些数据从哪里获取到，获取到数据遍历了多少行数据等等，我们可以通过 EXPLAIN 命令来查看这些执行信息。这些执行信息被统称为执行计划。

• id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。
• select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
• table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。
• partitions：访问的分区表信息。
• type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
  a)system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。
  b)eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
  c)ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。
  d)range：索引范围扫描，比如，<，>，between 等操作。
  e)index：索引全表扫描，此时遍历整个索引树。
  f)ALL：表示全表扫描，需要遍历全表来找到对应的行。
• possible_keys：可能使用到的索引。
• key：实际使用到的索引。
• key_len：当前使用的索引的长度。
• ref：关联 id 等信息。
• rows：查找到记录所扫描的行数。
• filtered：查找到所需记录占总扫描记录数的比例。
• Extra：额外的信息。

2）通过Show Profile分析SQL执行性能

通过 EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况，如果我们想要深入到 MySQL 内核中，从执行线程的状态和时间来分析的话，这个时候我们就可以选择 Profile。 Profile 除了可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时间。

3、常用的SQL优化

1）优化分页查询

通常我们是使用 <limit m,n>+ 合适的 order by 来实现分页查询，这种实现方式在没有任何索引条件支持的情况下，需要做大量的文件排序操作（file sort），性能将会非常得糟糕。如果有对应的索引，通常刚开始的分页查询效率会比较理想，但越往后，分页查询的性能就越差。

这是因为在使用 LIMIT 的时候，