MySQL优化总结--SQL优化

问题：当面对一个有 SQL 性能问题的数据库时，从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题？

解答：

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

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

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

两种方式

通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

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

通过 EXPLAIN 或者 DESC 命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

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

索引问题

MyISAM 和 InnoDB 存储引擎都只支持 BTREE 索引；MEMORY/HEAP 存储引擎可以支持 HASH和 BTREE 索引。

MySQL 目前不支持函数索引，但是能对列的前面某一部分进索引。

使用到索引的情况

（1）对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用；

（2）对于使用 like 的查询，后面如果是常量并且只有％号不在第一个字符，索引才可能会被使用；如果 like 后面跟的是一个列            的名字，那么索引也不会被使用；

（3）如果对大的文本进行搜索，使用全文索引而不用使用 like ‘%…%’；

（4）如果列名是索引，使用 column_name is null 将使用索引；

存在索引但不使用索引

（1） 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引；

（2） 如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列，那么不会用到索引。heap 表只有在“=”的条件下才           会使用索引；

（3） 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

（4） 如果建有复合索引，但不是索引列的第一部分，在查询中这个索引也不会被 MySQL 采用；

（5） 如果 like 是以％开始；

（6） 如果列类型是字符串，那么一定得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL             也不会用到的。因为，MySQL 默认把输入的常量值进行转换以后才进行检索。

查看索引使用情况

如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值
的含义是在数据文件中读下一行的请求数。
show status like 'Handler_read%';

两个简单实用的优化方法

定期分析表和检查表
分析表的语法 ：ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
分析和存储表的关键字分布，分析期间，使用一个读取锁定对表进行锁定。这对于 MyISAM, BDB 和 InnoDB 表有作用。对于 MyISAM 表，本语句与使用 myisamchk -a 相当。

检查表的语法：CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED
| CHANGED}

检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。对于 MyISAM 表，关键字统计数据被更新。

定期优化表

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

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

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

常用 SQL 的优化

1、大批量插入数据
对于 MyISAM 存储引擎的表，可以通过以下方式快速的导入大量的数据。
ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS;
（1） InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
（2）在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯           一性校验，可以提高导入的效率。
（3）如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET                    AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
2、优化 INSERT 语句

（1）如果同时从同一客户插入很多行，尽量使用多个值表的 INSERT 语句，这种方式将大大缩减客户端与数据库之间的连接、           关闭等消耗，使得效率比分开执行的单个 INSERT 语句快(在一些情况中几倍)。
         insert into test values(1,2),(1,3),(1,4)…
（2）如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。
         DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分            别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；
（3）将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；
（4）如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 MyISAM 表使用；
（5）当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍.

3、优化 GROUP BY 语句

如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL禁止排序。

4、优化 ORDER BY 语句

在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且ORDER BY 的字段都是升序或者都是降序。
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

以下几种情况下则不使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC； --order by 的字段混合 ASC 和 DESC；
SELECT * FROM t1 WHERE key2=constant ORDER BY key1；        --用于查询行的关键字与 ORDER BY 中所使用的不相同；
SELECT * FROM t1 ORDER BY key1, key2；                                     --对不同的关键字使用 ORDER BY；

5、优化嵌套查询

使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起
来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

6、MySQL 如何优化 OR 条件

对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

7、使用 SQL 提示

在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

SELECT SQL_BUFFER_RESULTS * FROM...
这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后，所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助，因为可以尽快释放锁资源。
1．USE INDEX
在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，可以让 MySQL 不再考虑其他可用索引。
mysql> explain select * from sales2 use index (ind_sales2_id) where id = 3\G;
2．IGNORE INDEX
如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT。
mysql> explain select * from sales2 ignore index (ind_sales2_id) where id = 3\G;
3．FORCE INDEX
为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。例如，当不强制使用索引的时候，因为 id 的值都是大于 0 的，因此 MySQL 会默认进行全表扫描，而不使用索引，如下所示：
mysql> explain select * from sales2 where id > 0 \G;
当使用 FORCE INDEX 进行提示时，即便使用索引的效率不是最高，MySQL 还是选择使用了索引，这是 MySQL 留给用户的一个自行选择执行计划的权力。加入 FORCE INDEX 提示后再次执行上面的 SQL：
mysql> explain select * from sales2 force index (ind_sales2_id) where id > 0 \G;