【进阶篇】SQL 优化

  在刚开始学SQL得时候，使用的都是自己本地的数据库，那时候的重点是写出符合逻辑的SQL语句，对当时的数据来说，怎么写都是瞬间出结果，但当工作时，数据库体量瞬间变成了百万级别，而且需要好多个这样表，这时候才发现，写一个查询语句要等几个小时或者更久才能出结果，这明显无法满足工作的需要；而且现在面试，问的多的是当数据很多时，你如何提高查询效率，而不是这个简单的需求怎么写SQL，这里写一些书中以及遇到过的情况来说明SQL优化。

一、优化的一般步骤

  那当拿到一个数据库时，如何优化呢？优化重点是什么呢？这就不得不提优化SQL的流程和步骤了，先找出对运行速度影响最大的数条语句，然后再逐条优化，从而实现对整个SQL程序的优化。

1、通过 show status 命令了解各种 SQL 的执行频率

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

show status like 'Com_%';

+--------------------------+-------+
|Variable_name | Value |
+--------------------------+-------+
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_event | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
……

  Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

• Com_select：执行 select 操作的次数，一次查询只累加 1。
• 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 服务器的次数。
• Uptime：服务器工作时间。
• Slow_queries：慢查询的次数。

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

  可以通过以下两种方式定位执行效率较低的 SQL 语句。

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

  
  这里对慢日志文件得操作做一个说明：

1.查看慢日志是否开启以及日志文件所在位置 --------ON为开启,OFF为关闭

show variables like ‘slow_query%’;

2.开启慢日志查询

set global slow_query_log=‘ON’;

3.设置慢日志时间

show variables like ‘long_query_time’; //显示当前慢日志的触发时间
set global long_query_time=5; //设置慢日志触发时间

注意：设置完成后需要重新连接或新建会话

3、通过 EXPLAIN 分析低效 SQL 的执行计划

  通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序，比如想计算 2006 年所有公司的销售额，需要关联 sales 表和 company 表，并且对 moneys 字段做求和（sum）操作，相应 SQL 的执行计划如下：

explain select sum(moneys) from sales a,company b where a.company_id = b.id and a.year = 2006;

*************************** 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: 1
 select_type: SIMPLE
 table: b
 type: ref
possible_keys: ind_company_id
 key: ind_company_id
 key_len: 5
 ref: sakila.a.company_id
 rows: 1
 Extra: Using where; Using index
2 rows in set (0.00 sec)

  每个列的简单解释如下：

• select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等。
• table：输出结果集的表。
• type：表示表的连接类型，性能由好到差的连接类型为 system（表中仅有一行，即常量表）、const（单表中最多有一个匹配行，例如 primary key 或者 unique index）、eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index）、ref（与eq_ref类似，区别在于不是使用primary key 或者 unique index，而是使用普通的索引）、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：执行情况的说明和描述。

4、确定问题并采取相应的优化措施

  经过以上步骤，基本就可以确认问题出现的原因。此时用户可以根据情况采取相应的措施，进行优化提高执行的效率。
  在上面的例子中，已经可以确认是对 a 表的全表扫描导致效率的不理想，那么对 a 表的year 字段创建索引，具体如下：

create index ind_sales2_year on sales2(year);

Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

创建索引后，再看一下这条语句的执行计划，具体如下：

explain select sum(moneys) from sales2 a,company2 b where a.company_id = b.id and a.year = 2006;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: a
 type: ref
possible_keys: ind_sales2_year
 key: ind_sales2_year
 key_len: 2
 ref: const
 rows: 1
 Extra: Using where
*************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: b
 type: ref
possible_keys: ind_company2_id
 key: ind_company2_id
 key_len: 5
 ref: sakila.a.company_id
 rows: 1
 Extra: Using where; Using index
2 rows in set (0.00 sec)

  可以发现建立索引后对 a 表需要扫描的行数明显减少（从 1000 行减少到 1 行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显。

二、索引问题

  索引是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的 SQL 性能问题。

1、索引的存储分类

  MyISAM 存储引擎的表的数据和索引是自动分开存储的，各自是独立的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。关于这点可以看前面的介绍。存储引擎
  MySQL 中索引的存储类型目前只有两种（BTREE 和 HASH），具体和表的存储引擎相关：MyISAM 和 InnoDB 存储引擎都只支持 BTREE 索引；MEMORY/HEAP 存储引擎可以支持 HASH和 BTREE 索引。
  MySQL 目前不支持函数索引，但是能对列的前面某一部分进索引，例如 name 字段，可以只取 name 的前 4 个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。下面是创建前缀索引的一个例子：

create index ind_company2_name on company2(name(4));

Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

2、MySQL 如何使用索引

  索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作性能的最佳途径。但是不一定建立索引就会被使用，下面举例讲解索引的使用。

1.正常单索引

  根据列名建立单索引，正常情况下列名出现在where之后都会使用，单个别情况除外（见第四点）。需要说明得是如果列名是索引，使用 column_name is null 将使用索引。

2.多列索引

  对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用，这就是索引的前缀特性。但是不是按最左侧条件查询表，那么索引就不会被用到。

--创建多列索引
create index ind_sales2_companyid_moneys on sales2(company_id,moneys);

-- 使用最左侧条件，索引被使用
select * from sales2 where company_id = 2006;

-- 使用非最左侧条件，索引不被使用
select * from sales2 where moneys = 1;

3.like 查询中得索引

  Where 条件中，like 9%， like %9%， like%9，三种方式理论都用不到索引。实际后两种方式对于索引是无效的。但第一种9%是不确定的，可能使用，可能不使用；如果 like 后面跟的是一个列的名字，那么索引也不会被使用。

4.有索引单不使用得情况

• 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。
• 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到
• Where条件中IN可以使用索引， NOT IN 无法使用索引
• 查询条件中使用函数，索引将会失效，这和列的离散性有关，一旦使用到函数，函数具有不确定性。

3、查看索引使用情况

  关于索引的使用情况，我们可以根据下面语句查看：

show status like ‘Handler_read%’;

  如果索引正在工作，Handler_read_key 的值将很高，这个值代表了索引使用的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
  Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

三、两个简单的优化方法

  对于大多数开发人员来说，可能只希望掌握一些简单实用的优化方法，对于更多更复杂的优化，更倾向于交给专业 DBA 来做。下面介绍两个简单的优化方法。

1、定期分析表和检查表

1.分析表语句

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …
 
例如：
analyze table sales;
±-------------±------±---------±---------+
| Table | Op | Msg_type | Msg_text |
±-------------±------±---------±---------+
| sakila.sales | check | status | OK |
±-------------±------±---------±---------+
1 row in set (0.00 sec)

2.检查表语句（可以检查视图）

CHECK TABLE tbl_name [, tbl_name] … [option] … option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
 
例如：
check table sales; / check table sales_view3;
±-------------------±------±---------±---------+
| Table | Op | Msg_type | Msg_text |
±-------------------±------±---------±---------+
| sakila.sales_view3 | check | status | OK |
±-------------------±------±---------±---------+
1 row in set (0.00 sec)
 

 
  分析表，analyze 分析表语句用于分析和存储表的关键字分布，分析的结果可以使系统得到更准确的统计信息，使得 SQL 能够生成正确的执行计划。这对于 MyISAM、BOB 和 InnoDB 表有作用。

  ​ 检查表的作用是检查一个或者多个表是否有错误。 CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。

2、定期优化表

优化表的语法如下：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …
 

例如：
optimize table sales;

  如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）进行了很多更改，则应使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。
  
  

  注意： ANALYZE（分析）、CHECK（检查）、OPTIMIZE（优化） 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。
  

四、常见的SQL优化

  我们已经知道了 MySQL 中怎么样通过索引来优化查询。但日常开发中，除了使用查询外，我们还会使用一些其他的常用 SQL，比如 INSERT、GROUP BY 等。对于这些 SQL 语句，我们该怎么样进行优化呢？我们慢慢学习。

1、大批量插入数据

1.对于 MyISAM 存储引擎

ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS;

  DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

例如：

alter table film_test2 disable keys;

load data infile '/home/mysql/film_test.txt' into table film_test2;

alter table film_test2 enable keys;

2. 对于 InnoDB 存储引擎

1. 因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
2. 在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
3. 在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

2、优化 INSERT 语句

  当进行数据 INSERT 的时候，可以考虑采用以下几种优化方式。

• 多值insert由于多条单值insert
• 不同客户插入很多行，使用 INSERT DELAYED 语句得到更高的速度。DELAYED 的含义是让 INSERT 语句马上执行，但数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多
• 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；
• MyISAM 表批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，
• 当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

3、 优化 GROUP BY 语句

  默认情况下，MySQL 对所有 GROUP BY col1，col2…的字段进行排序。这相当于使用了ORDER BY col1，col2…语句。因此，则可以显示的指定 ORDER BY NULL禁止排序。

例如：

explain select id,sum(moneys) from sales2 group by id;
 
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: sales2
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1000
 Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

explain select id,sum(moneys) from sales2 group by id order by null\G;

*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: sales2
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1000
 Extra: Using temporary
1 row in set (0.00 sec)

  可以明显的看出第一个SQL语句需要进行“filesort”，而第二个SQL由于ORDER BY NULL不需要进行“filesort”，而 filesort 往往非常耗费时间。

4、优化 ORDER BY 语句

  在order by 语句中，是可以正常使用索引的，但也有一些情况不适用索引，所以要尽量避免这种情况。比如下面例子：

--order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

--用于查询行的关键字与 ORDER BY 中所使用的不相同
SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

--对不同的关键字使用 ORDER BY：
SELECT * FROM t1 ORDER BY key1, key2；

5、优化嵌套查询

  MySQL 4.1 开始支持 SQL 的子查询。即使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代，高效的原因是MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

6、优化 OR 条件

  前面已经讲过，对于含有 OR 的查询子句，当前面索引而后面没索引时，是不会使用索引的，所以如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

7、使用 SQL 提示

  SQL 提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。下面是一些在 MySQL 中常用的 SQL 提示。

1. USE INDEX

  在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。

explain select * from sales2 use index (ind_sales2_id) where id = 3;

*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: sales2
 type: ref
possible_keys: ind_sales2_id
 key: ind_sales2_id
 key_len: 5
 ref: const
 rows: 1
 Extra: Using where
1 row in set (0.00 sec).

2.IGNORE INDEX

  如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作 为 HINT。

explain select * from sales2 ignore index (ind_sales2_id) where id = 3;

*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: sales2
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1000
 Extra: Using where
1 row in set (0.00 sec).

3.FORCE INDEX

  为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。尽管可能不使用索引效率更高，但强制就会执行。当然实际中这样做的并不多。

explain select * from sales2 force index (ind_sales2_id) where id > 0 ;

*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: sales2
 type: range
possible_keys: ind_sales2_id
 key: ind_sales2_id 
 key_len: 5
 ref: NULL
 rows: 1000
 Extra: Using where
1 row in set (0.00 sec).