MySQL进阶：7.SQL优化

忘是亡心i 2023-02-24 08:25 22阅读 0赞

【专栏目录】  
[MySQL进阶：1.MySQL基础环境搭建][MySQL_1.MySQL]  
[MySQL进阶：2.索引][MySQL_2.]  
[MySQL进阶：3.视图][MySQL_3.]  
[MySQL进阶：4.为什么不推荐使用存储过程和触发器][MySQL_4.]  
[MySQL进阶：5.存储引擎][MySQL_5.]  
[MySQL进阶：6.定位SQL性能问题][MySQL_6._SQL]  
[MySQL进阶：7.SQL优化][MySQL_7.SQL]

`以下内容均以InnoDB类型的表为例`

# 1.海量数据导入优化 #

# 导入指定文件的数据到指定表
    # 文件中的数据格式："1,wy,123123\n2,xy,123123\n3,zy,123123"
    mysql> load data local infile '/xx/xx/xx' into table 'xxx' fields terminated by ',' lines terminated by '\n';

<table> 
 <thead> 
  <tr> 
   <th>优化方法</th> 
   <th>原因</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>建议按照主键顺序导入</td> 
   <td>因为存在主键，所以必定要维护至少一个索引，而InnoDB类型的表创建的索引是B+数结构，所以按照顺序方式导入会更快。因此这里建议将数据按照主键顺序排序后再导入。</td> 
  </tr> 
  <tr> 
   <td>关闭唯一性校验</td> 
   <td>如果数据表中某个列是唯一属性的，那么每导入一条数据时，都会对该列进行唯一性校验，即判断是否重复，如果重复，则不导入。所以在导入数据时可以先执行"SET UNIQUE_CHECKS=0;"关闭唯一性校验，导入完成后再"SET UNIQUE_CHECKS=1;"开启。注意：这么做的前提是你一定要保证你导入的数据中，该列的所有值都是唯一的。</td> 
  </tr> 
  <tr> 
   <td>设置手动提交事务模式</td> 
   <td>如果不设置手动提交事务，那么默认每条插入语句都是一个事务，每次都要提交事务。设置手动提交事务的话，可以在循环前开启事务，循环结束后再提交事务，只需要提交一次事务。所以可以通过"SET AUTOCOMMIT=0"关闭自动提交，导入数据后再"SET AUTOCOMMIT=1"开启。最后还是要提醒一点，手动提交事务模式确实能加快效率，但是不能说手动提交事务就比自动提交事务要好。比如，使用手动提交模式时，如果有一个数据插入异常导致失败，那么前面插入的数据就都失败了。而自动提交模式就能保证”失败的数据“之前的所有数据都已经插入完成，不会丢失</td> 
  </tr> 
 </tbody> 
</table>

# 2.insert优化 #

*  如果需要同时对一张表插入很多行数据时，应该尽量使用多个值的一条insert语句，减少客户端与数据库之间的连接、关闭操作；

insert into UserEO values(1,'wy','123123');
    insert into UserEO values(2,'xy','123123');
    insert into UserEO values(3,'zy','123123');

优化

insert into UserEO values(1,'wy','123123'),(2,'xy','123123'),(3,'zy','123123');

*  设置手动提交事务，原因参考海量数据导入优化

start transaction;
    insert into UserEO values(1,'wy','123123');
    insert into UserEO values(2,'xy','123123');
    insert into UserEO values(3,'zy','123123');
    commit;

*  数据有序插入，原因参考海量数据导入优化
 *

insert into UserEO values(2,'xy','123123');
    insert into UserEO values(1,'wy','123123');
    insert into UserEO values(3,'zy','123123');

优化

insert into UserEO values(1,'wy','123123');
    insert into UserEO values(2,'xy','123123');
    insert into UserEO values(3,'zy','123123');

# 3.order by优化 #

`order by的优化主要在于减少FileSort的出现`

# 假设UserEO表中存在复合索引询(name,passwd)
    # 按照姓名和密码排序查询name和passwd，默认是升序asc
    # 因为name和passwd属于索引的一部分，所以当前查询是使用索引的
    mysql> select name, passwd from UserEO order by name, passwd

`使用order by时，如果使用索引，建议同时满足以下条件，否则会出现FileSort：`

*  order by的字段必须得都是索引字段

# 这种情况就会出现FileSort
    mysql> select name, passwd from UserEO order by id, passwd

*  order by的字段必须得都是升序或者都是降序

# 都是升序
    mysql> select name, passwd from UserEO order by name, passwd
    # 都是降序
    mysql> select name, passwd from UserEO order by name desc, passwd desc

*  order by的字段必须要和索引的顺序一样

# 索引顺序为(name,passwd)，则下面四种情况都符合索引顺序
    mysql> select name, passwd from UserEO order by name, passwd
    mysql> select name, passwd from UserEO order by name desc, passwd desc
    mysql> select passwd, name from UserEO order by name, passwd
    mysql> select passwd, name from UserEO order by name desc, passwd desc

[MySQL_1.MySQL]: https://blog.csdn.net/w_y_x_y/article/details/107048525
[MySQL_2.]: https://blog.csdn.net/w_y_x_y/article/details/107048532
[MySQL_3.]: https://blog.csdn.net/w_y_x_y/article/details/107188936
[MySQL_4.]: https://blog.csdn.net/w_y_x_y/article/details/107188957
[MySQL_5.]: https://blog.csdn.net/w_y_x_y/article/details/107188967
[MySQL_6._SQL]: https://blog.csdn.net/w_y_x_y/article/details/107188979
[MySQL_7.SQL]: https://blog.csdn.net/w_y_x_y/article/details/107214956