谈谈sql的优化

1. sql语句优化的过程

第一步：通过show status命令来了解各种sql的执行频率
MySQL 客户端连接成功后，通过 show [session|global]status 命令可以提供服务器状态信息，也可以在操作系统上使用 mysqladmin extended-status 命令获得这些消息。show[session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。下面的命令显示了当前 session 中所有统计参数的值：

    Com_select：执行 select 操作的次数，一次查询只累加 1。
    Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
    Com_update：执行 UPDATE 操作的次数。
    Com_delete：执行 DELETE 操作的次数。

如果是表是InnoDB 存储引擎的，累加的算法也略有不同 。我们使用以下命令：
SHOW STATUS LIKE “Innodb_rows_%”;

Innodb_rows_read：select 查询返回的行数。
Innodb_rows_inserted：执行 INSERT 操作插入的行数。
Innodb_rows_updated：执行 UPDATE 操作更新的行数。

这些操作会明确的知道该数据库是一插入更新还是查询为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。 对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。此外，以下几个参数便于用户了解数据库的基本情况。
Connections：试图连接 MySQL 服务器的次数。
Uptime：服务器工作时间。
Slow_queries：慢查询的次数。
第二步：根据慢日志定位慢查询sql
SHOW VARIABLES LIKE ‘%query%’ 查询慢日志相关信息

slow_query_log 默认是off关闭的，使用时，需要改为on 打开　　　　　　
slow_query_log_file 记录的是慢日志的记录文件
long_query_time 默认是10S，每次执行的sql达到这个时长，就会被记录
SHOW STATUS LIKE ‘%slow_queries%’ 查看慢查询状态

Slow_queries 记录的是慢查询数量 当有一条sql执行一次比较慢时，这个vlue就是1 （记录的是本次会话的慢sql条数）
　注意：
如何打开慢查询 ： SET GLOBAL slow_query_log = ON;
将默认时间改为1S： SET GLOBAL long_query_time = 1;（设置完需要重新连接数据库，PS：仅在这里改的话，当再次重启数据库服务时，所有设置又会自动恢复成默认值，永久改变需去my.ini中改）
第三步：使用explain
在sql语句前面加上explain，获取explain执行计划，explain字段简介
select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、 PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等。
table：输出结果集的表。
type：

1. system: 表中只有一条数据. 这个类型是特殊的 const 类型.
2. const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
3. eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.
4. ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
5. range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL,<=>, BETWEEN, IN() 操作中. 当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len字段是此次查询中使用到的索引的最长的那个.
6. index: 表示全索引扫描(full index scan), 和 ALL 类型类似,只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据，index 类型通常出现在:所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.。
7. ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.
   type性能比较：ALL < index < range ~ index_merge < ref < eq_ref < const < system

第四步：确定问题并采取相应的优化措施
可以先通过show status命令来令来了解各种sql的执行频率，通过慢查询日志定位那些执行效率较低的 SQL 语句以对其进行优化，比如可以看看其连表查询时是否是小标驱动大表，如果是在表设计的时候根据三范式和具体的设计要求建立合适的表结构，选择合适的数据库存储引擎，如果需要大量SELECT查询并且对事物要求不高，推荐选择MyISAM，如果要求事物、行级锁、恢复数据等推荐选择InnoDB根据业务需求，在更新少辨识度高查询较多等等xxx的列上根据合适的需求建立合适的索引，在日常书写sql的时候，多用explain去分析执行计划, 尽量避免出现让索引失效的情况，比如使用索引查询时不遵循最左前缀，查询时%写在like的后面，查询语句中出现不等、空值、or之类的，var值不写单引号，尽量使用覆盖索引，少用select*的使用。