load data local infile ‘ /root/sql1.log ‘ intotable `table_1` fields terminated by ‘ , ‘ lines terminated by ‘ \n ‘ ;

 

关闭唯一性校验：

在导入数据前执行SET UNIQUE_CHECKS =  0 ，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS =  1 ，恢复唯一性校验，可以提高导入的效率。

 

手动提交：

如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT =  0 ，关闭自动提交，导入结束后再执行SET AUTOCOMMIT =  1 ，打开自动提交，也可以提高导入的效率。

 

insert语句优化：

values多个：

 INSERT   INTO   MyTable
  ( Column1, Column2, Column3 )
  VALUES  
  (  ‘  John  ‘ ,  123 ,  ‘  Lloyds Office  ‘  ), 
  (  ‘  Jane  ‘ ,  124 ,  ‘  Lloyds Office  ‘  ), 
  (  ‘  Billy  ‘ ,  125 ,  ‘  London Office  ‘  ),
  (  ‘  Miranda  ‘ ,  126 ,  ‘  Bristol Office  ‘ );

 

一个事务提交：

开启一个事务，批量操作完了才提交事务，而不是，操作一次就提交一次，这样io太高，插入太慢。

 

插入字段尽量少，尽量使用默认值：

注意事项： max_allowed_packet 默认是1M，如何insert  values  sql 太大需要上调这个值

 

order by 优化：

2中排序方式：

MySQL支持两种方式的排序 filesort 和 index ，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

使用 using index的2中情况：

order by满足 两种情况 会使用Using index：

order by语句使用 索引最左前列 ； where子句与order by子句，条件列组合 满足索引最左前列 ；

优化：

尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则； 如果order by的条件不在索引列上，就会产生 Using filesort ； 在使用order by时，不要用select *，只查询所需的字段：

因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I / O操作

 

尝试提高sort_buffer_size；

尝试提高max_length_for_sort_data

基于using filesort的优化：

filesort有两种排序算法：双路排序和单路排序 双路排序：

在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。
 即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段 。

单路排序：

从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I / O变成了顺序I / O，但是会使用更多的空间，因为它 把每一行都保存在内存中 了。

 

单路排序的问题：

当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I / O，反而增加其I / O运算。

解决办法： 增加sort_buffer_size参数的设置。 增大max_length_for_sort_data参数的设置。

　　

 

mysql - SQL 优化小结

标签：rgb   span   sql   建议   table   where   根据   解决办法   支持   

查看更多关于mysql - SQL 优化小结的详细内容...