SQL优化的笔记

  一般使用SQL的时候你是不会去想到优化。但是面对一个有SQL性能问题的数据库时，我们应该如何入手进行系统的分析，使得能够尽快定位问题SQL，并且尽快解决问题。

1.使用show status 命令了解各种SQL的执行频率

引用

例如在Mysql的Cline上输入
show status like 'Com_%';

显示的是一些:Com_xxx.
Com_xxx 表示每个xx语句执行的次数。通常情况下我们比较关注如下一些操作：

引用

  Com_select:执行select操作的次数
  Com_insert:执行Insert操作的次数，对于批量插入的INSERT操作，只累加一次
  Com_update:执行update操作的次数
  Com_delete:执行Delete操作的次数

上面这些参数对于所有存储引擎的表操作都会进行累加。下面有些参数只针对InnoDB存储引擎的，累加的算法也有点不一样。

引用

  Innodb_rows_read:select查询返回的行数
  Innodb_rows_inserted:执行INSERT操作插入的行数
  Innodb_rows_updated:执行Update操作更新的行数
  Innodb_rows_deleted:执行Delete操作删除的行数

通过上面的一些参数，我们可以了解当前数据库的应用是以插入为主还是以查询为主。以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不管提交还是回滚都会进行累加。

对于事务型的应用，通过Com_commit和Com_rollback进行分析。如果回滚操作非常频繁那么要思考下是不是编写存在问题。
下面有几个参数用于了解数据库的基本情况

引用

  Connections:试图连接Mysql服务器的次数（执行的命令是：show status like 'Con_%';）
  Uptime: 服务器工作时间（执行的命令是：show status like 'Up_%';）
  Slow_queries:慢查询的次数（执行的命令是：show status like 'Slow_%';）

2. 定位执行效率较低的SQL语句

要想定义效率较低的SQL可以按照下面两种方式试试。

引用

  1. 通过慢查询日志定位那些执行效率较低的SQL语句，用 --log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
2. 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候进行查询慢查询日志并不能定位问题，可以使show processlist 命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进优化。

3. 使用EXPLAIN分析低效SQL的执行计划。
  在查询到效率低的SQL语句后，那我们可以使用explain或者DESC命令获取Myswl如何执行SELECT语句的信息，包括在Select语句执行过程中表如何连接和连接的顺序。
例如你想计数xxxx年公司的销售额，那么需要操作sales和comapny table，并对money字段进行sum操作。看看怎么使用explain：

引用

   explain select sum(moneys) from sales a company b where a.company_id = b.id and a.year=XXXX \G;(注意加上\G是为了更好的看)
显示如下：
*********************** 1. row***************************
          id: 1
select_type: SIMPLE
       table: a
        type: ALL
   possible_keys: NULL
         key:NULL
       key_len: NULL
       ref: NULL
       rows:1000
       Extra: Using where
*********************** 2. row***************************
          id: 2
select_type: SIMPLE
       table: b
        type: ref
   possible_keys: ind_company_id
         key:ind_comapany_id
       key_len: 5
       ref: sakila.a.company_id
       rows:1
       Extra: Using where;Using index

下面解释下每个列的含义：

引用

  select_type: 表示SELECT的类型，常见的取值为SIMPLE(简单表，不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION、SUBQUERY

table: 输出结果集的表

type: 表示表的连接类型，性能由好到差的类型类型为
(System(表中仅有一行，即常量表)，
const(单表中最多有一个匹配行)，
eq_ref(对于前面的每一行,在此表中只查询一条记录)，
ref(使用普通的索引)，
ref_or_null(和ref类似，但是条件中包含对于NULL查询),
index_merge(索引合并优化),
unique_subquery(in的后面是一个查询主键字段的子查询),
index_subquery(类似unique_subquery，主要是in的后面是查询非唯一索引字段的子查询),
range(单表中的范围查询),
index(对于当前的每一行，都通过查询索引来得到数据),
all(对于当前的每一行,都通过全表扫描来得到数据))

possible_keys: 表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描行的数量
Extra:执行情况的说明和描述

通过上面的思路进行分析和定位SQL语句有可能出现的低效率的情况。并且进行解决。下篇我讲叙述关于SQL优化之索引的问题。