6、mysql的SQL优化

1、 大批量插入数据

  

  1) 主键顺序插入

    因为InnoDB类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。如果InnoDB表没有主键,那么系统会自动默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这点,来提高导入数据的效率。

  2) 关闭唯一性校验

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

  3) 手动提交事务

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

 

 

2 优化insert语句

  当进行数据的insert操作的时候,可以考虑采用以下几种优化方案。

  • 如果需要同时对一张表插入很多行数据时,应该尽量使用多个值表的insert语句,这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

  • 6、mysql的SQL优化

     

     

3 优化order by语句

 

  1). 第一种是通过对返回数据进行排序,也就是通常说的 filesort 排序,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

  2). 第二种通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高。

  

   了解了MySQL的排序方式,优化目标就清晰了:尽量减少额外的排序,通过索引直接返回有序数据。where 条件和Order by 使用相同的索引,并且Order By 的顺序和索引顺序相同, 并且Order by 的字段都是升序,或者都是降序。否则肯定需要额外的操作,这样就会出现FileSort。

 

  通过创建合适的索引,能够减少 Filesort 的出现,但是在某些情况下,条件限制不能让Filesort消失,那就需要加快 Filesort的排序操作。对于Filesort , MySQL 有两种排序算法:

  1) 两次扫描算法 :MySQL4.1 之前,使用该方式排序。首先根据条件取出排序字段和行指针信息,然后在排序区 sort buffer 中排序,如果sort buffer不够,则在临时表 temporary table 中存储排序结果。完成排序之后,再根据行指针回表读取记录,该操作可能会导致大量随机I/O操作。

  2)一次扫描算法:一次性取出满足条件的所有字段,然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大,但是排序效率比两次扫描算法要高。

 

  MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小, 来判定是否那种排序算法,如果max_length_for_sort_data 更大,那么使用第二种优化之后的算法;否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量,来增大排序区的大小,提高排序的效率。

 

.7 优化分页查询

  一般分页查询时,通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ,此时需要MySQL排序前2000010 记录,仅仅返回2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大 。

  6、mysql的SQL优化

 

 

  优化思路一

    在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。

6、mysql的SQL优化

 

 

优化思路二

  该方案适用于主键自增的表,可以把Limit 查询转换成某个位置的查询 。

6、mysql的SQL优化

 

 

5.8 使用SQL提示

SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

5.8.1 USE INDEX

在查询语句中表名的后面,添加 use index 来提供希望MySQL去参考的索引列表,就可以让MySQL不再考虑其他可用的索引。

5.8.2 IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引,则可以使用 ignore index 作为 hint 。

 explain select * from tb_seller ignore index(idx_seller_name) where name = 小米科技;
5.8.3 FORCE INDEX

为强制MySQL使用一个特定的索引,可在查询中使用 force index 作为hint 。

6、mysql的SQL优化

 

 

 

6、mysql的SQL优化

上一篇:MySQL索引(一)


下一篇:MongoDB批量更新|按条件更新SQL|批量删除某个字段