mysql - SQL 优化小结

优化批量插入数据：

1. 数据源，根据主键做好排序，再进行数据导入：

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

    

2. 关闭唯一性校验：

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

    

3. 手动提交：

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

    

insert语句优化：

1. values多个：

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

    

2. 一个事务提交：

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

    

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

   注意事项： 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：

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

优化：

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

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

    

4. 尝试提高sort_buffer_size；

5. 尝试提高max_length_for_sort_data

基于using filesort的优化：

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

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

   2. 单路排序：

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

       

2. 单路排序的问题：

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

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

or 优化：

1. 如果可以的话，给or条件中的每个单字段，增加单列索引；
2. 若不方便加索引，使用 union 并集操作，实现or：

   # 优化前：
   select * from table_1 where id=2 or name='123';
   
   # 优化后：
   select * from table_1 where id=2 union select * from table_1 where name='123';

    

group by 优化：