全面解析 52 条 SQL 语句性能优化策略,建议收藏!(下)

本文将全面揭秘 SQL 语句性能优化策略,直接上干货!

接上文

全面解析 52 条 SQL 语句性能优化策略,建议收藏(上)

41、MySQL备份过程:


  • 从二级复制服务器上进行备份;

  • 在进行备份期间停止复制,以避免在数据依赖和外键约束上出现不一致;

  • 彻底停止MySQL,从数据库文件进行备份;

  • 如果使用MySQL dump进行备份,请同时备份二进制日志文件 – 确保复制没有中断;

  • 不要信任LVM快照,这很可能产生数据不一致,将来会给你带来麻烦;

  • 为了更容易进行单表恢复,以表为单位导出数据——如果数据是与其他表隔离的。 

  • 当使用mysqldump时请使用–opt;

  • 在备份之前检查和优化表;

  • 为了更快的进行导入,在导入时临时禁用外键约束。;

  • 为了更快的进行导入,在导入时临时禁用唯一性检测;

  • 在每一次备份后计算数据库,表以及索引的尺寸,以便更够监控数据尺寸的增长;

  • 通过自动调度脚本监控复制实例的错误和延迟;

  • 定期执行备份。


42、查询缓冲并不自动处理空格,因此,在写SQL语句时,应尽量减少空格的使用,尤其是在SQL首和尾的空格(因为查询缓冲并不自动截取首尾空格)。


43、member用mid做标准进行分表方便查询么?一般的业务需求中基本上都是以username为查询依据,正常应当是username做hash取模来分表。


而分表的话MySQL的partition功能就是干这个的,对代码是透明的;在代码层面去实现貌似是不合理的。


44、我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT标志。


45、在所有的存储过程和触发器的开始处设置SET NOCOUNT ON,在结束时设置SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。


46、MySQL查询可以启用高速查询缓存。这是提高数据库性能的有效MySQL优化方法之一。当同一个查询被执行多次时,从缓存中提取数据和直接从数据库中返回数据快很多。


47、EXPLAIN SELECT查询用来跟踪查看效果:


使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的。


48、当只要一行数据时使用LIMIT 1 :


当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。


在这种情况下,加上LIMIT 1可以增加性能。这样一来,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。


49、选择表合适存储引擎: 


  • myisam:应用时以读和插入操作为主,只有少量的更新和删除,并且对事务的完整性,并发性要求不是很高的。 

  • InnoDB:事务处理,以及并发条件下要求数据的一致性。除了插入和查询外,包括很多的更新和删除。(InnoDB有效地降低删除和更新导致的锁定)。

    对于支持事务的InnoDB类型的表来说,影响速度的主要原因是AUTOCOMMIT默认设置是打开的,而且程序没有显式调用BEGIN 开始事务,导致每插入一条都自动提交,严重影响了速度。可以在执行SQL前调用begin,多条SQL形成一个事物(即使autocommit打开也可以),将大大提高性能。


50、优化表的数据类型,选择合适的数据类型: 


原则:更小通常更好,简单就好,所有字段都得有默认值,尽量避免null。 


例如:数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型。(mediumint就比int更合适) 


比如时间字段:datetime和timestamp,datetime占用8个字节,而timestamp占用4个字节,只用了一半,而timestamp表示的范围是1970—2037适合做更新时间 


MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。 


因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。


例如:在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间。甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了。


同样的,如果可以的话,我们应该使用MEDIUMINT而不是BIGIN来定义整型字段,应该尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。 


对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。


51、字符串数据类型:char,varchar,text选择区别。


52、任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。


来源:SimpleWu

attachments-2021-01-f3jRkIZe5ffe5ae54c2d0.jpeg

  • 发表于 2021-01-13 10:30
  • 阅读 ( 26 )

0 条评论

请先 登录 后评论
奈学教育
奈学教育

官方

150 篇文章

作家榜 »

  1. NX小编 1251 文章
  2. 58沈剑 322 文章
  3. 热爱技术的小仓鼠 169 文章
  4. 奈学教育 150 文章
  5. 李希沅 | 奈学教育 51 文章
  6. 江帅帅 | 奈学教育 32 文章
  7. 林淮川 | 奈学教育 12 文章
  8. 邱鹏超 3 文章