SQL优化的一些策略（整理篇）

定位SQL

当我们数据量大的时候，我们的SQL执行效率往往不是那么可观，虽然结果也执行的出来

SQL也是正确的，但是有的时候我们不得不更改原来的SQL以提升性能。

就像排序算法一样，逻辑简单的冒泡也能实现功能，但是数据量大的时候执行效率就非常

的差，而我们的快速排序可以出色的解决我们的需求。

show status

• show status命令可以查看我们数据库的一些参数，比如如图所示，前四个Com是近期各类查询语句的执行次数，对于批量查询这个计数只会增加1，是对于任何的存储引擎都显示。
• 后面四个是InnoDB存储引擎的增删改查的SQL执行情况，记录的是影响的行数。
• 我们可以通过这些数据来分析，最近的SQL是哪些语句执行的比较多。举个例子，如果一个读多写少的数据表，我们更多的考虑是读的操作的SQL是否需要优化。
  

慢查询日志

• 除了上述的方式，我们可以开启慢查询日志，默认情况下是关闭的。
• 还有我们的慢查询日志一般默认在mysql根目录下的data文件夹下，里面记录了执行比较慢的SQL的信息。
• 如图，我们使用慢查询日志定位到了SQL的信息，比如执行时间是17秒，SQL是一个单表的查询语句，因上锁等待的时间是0秒等等，通过信息有利于我们进行问题的排查
  
  
• 除了查看慢查询日志的方式以外，MySQL提供了一种查询方法自动帮我们查询执行较慢的SQL。
• 使用：mysqldumpslow -t n 日志文件地址 可以自动帮我寻找指定日志文件中，执行速度最慢的n条SQL
• 如果某些SQL查询的慢，但是大部分情况下是好的那么可以先不考虑优化。
  如果某些SQL出现的频率非常的高，那么这些SQL就是我们要排查的需要优化的SQL。
  

SQL优化

• 对于经常被where条件关联的字段可以添加索引，如图所示，输了主键外，我还为name字段添加了索引，我们对主键，nam和email三个字段分别进行一个等值查询，可以看到没有添加索引的字段执行效率非常的低下。
• 如果一个字段频繁被查询到，可以考虑添加索引。
• 注意！对于 or 子句，如果要利用索引，则or 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。
  
• 对于已经添加过索引的字段，如果指向效率非常低下，我们可以使用explain指令查看SQL的执行计划：explain [SQL语句] /G
• 如图，同样是like语句查询姓名，如果把%通配符写在后面不影响索引的使用，写在前面则索引失效。explain语句可以分析SQL的执行计划，以此来帮助我们排查问题。
  
  

优化 insert 语句

对于 Innodb 类型的表，我们有以下几种方式可以提高insert插入数据的效率的效率：

• a. 因为 Innodb 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 Innodb 表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。
• b. 在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行 SETUNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
• c. 如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
• 如果你同时从同一客户插入很多行，使用多个值表的 INSERT 语句。这比使用分开INSERT 语句快(在一些情况中几倍)。Insert into test values(1,2),(1,3),(1,4)…
• 使用 create temporary table 语法，它是基于 session 的表，表的数据保存在内存里面，当 session 断掉后，表自然消除。对于大表的统计分析，如果统计的数据量不大，利用 insert … select 将数据移到临时表中比直接在大表上做统计要效率更高。

优化 group by 语句

• 默认情况下，MySQL 排序所有 GROUP BY col1，col2，…。查询的方法如同在查询中指定 ORDER BY col1，col2，…。如果一条查询语句中包括 GROUP BY 但你想要避免排序结果的消耗，你可以指定 ORDER BY NULL禁止排序。例如：
  INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

优化 order by语句

• 在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。where 条件和 order by 使用相同的索引，并且 order by 的顺序和索引顺序相同 ，并且 order by 的字段都是升序或者都是降序。
• 例如：下列 sql 可以使用索引。
  • SELECT * FROM t1 ORDER BY key_part1,key_part2,… ;
  • 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 DESC;
• 但是以下情况不使用索引：
  • 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；

数据库的优化策略

1. 使用持久的连接数据库以避免连接开销。
2. 经常检查所有查询确实使用了必要的索引。
3. 避免在频繁更新的表上执行复杂的 SELECT 查询，以避免与锁定表有关的由于读、写冲突发生的问题。
4. 对于没有删除的行操作的 MyISAM 表，插入操作和查询操作可以并行进行，因为没有删除操作的表查询期间不会阻塞插入操作．对于确实需要执行删除操作的表，尽量在空闲时间进行批量删除操作，避免阻塞其他操作。
5. 充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这减 少 MySQL 需要做的语法分析从而提高插入速度。
6. 对经常访问的可以重构的数据使用内存表，可以显著提高访问的效率。
7. 通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。
8. 为了防止备份期间对应用的影响，可以在复制服务器上执行备份操作。
9. 表的字段尽量不使用自增长变量，在高并发情况下该字段的自增可能对效率有比较大的影响，推荐通过应用来实现字段的自增长。

适当添加冗余字段

• 表需要使用何种数据类型，是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样即浪费存储也浪费内存。
• 我们可以使用 PROCEDURE ANALYSE()对当前已有应用的表类型的判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。语法：
  • SELECT * FROM tbl_name PROCEDURE ANALYSE();
  • SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);
• 输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDURE ANALYSE()不要为那些包含的值多于 16 个或者 256 字节的 ENUM 类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM 定义通常很难阅读。在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。

通过拆分，提高表的访问效率

这里我们所说的拆分，主要是针对 Myisam 类型的表，拆分的方法可以分成两种情况：

• 纵向拆分：
  纵向拆分是只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的，这样可以有效的提高表的查询和更新的效率。
• 横向拆分：
  横向拆分是指按照应用的情况，有目的的将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效的避免 Myisam 表的读取和更新导致的锁问题。