不论是面试还是实际开发(后端),SQL优化一直是绕不开的一个话题,本文会提到 52 条 SQL 语句性能优化策略,有些优化策略需要你有一定的SQL实践才能体会其中的道理,当然你也可以根据这些优化策略去实践一下,这样更能加深理解和记忆。
文章较长,建议先收藏或转发,方便下次继续阅读
查询的结果用于“插、删、改”的不能加 nolock; 查询的表属于频繁发生页分裂的,慎用 nolock ; 使用临时表一样可以保存“数据前影”,起到类似 Oracle 的 undo 表空间的功能,能采用临时表提高并发性能的,不要用 nolock。
select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' (A = B, B = '号码')
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' (A = B, B = '号码', A = '号码')
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' (B = '号码', A = '号码')
索引的创建要与应用结合考虑,建议大的 OLTP 表不要超过 6 个索引; 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过 index index_name 来强制指定索引; 避免对大表查询时进行 table scan,必要时考虑新建索引; 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用; 要注意索引的维护,周期性重建索引,重新编译存储过程。
SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒
SELECT * FROM record WHERE amount/30 < 1000 --11秒
SELECT * FROM record WHERE convert(char(10), date, 112) = '19991201' --10秒
SELECT * FROM record WHERE card_no like '5378%' -- < 1秒
SELECT * FROM record WHERE amount < 1000*30 -- < 1秒
SELECT * FROM record WHERE date = '1999/12/01' -- < 1秒
SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB = 'PRESIDENT'
OR JOB = 'MANAGER'
SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB = 'PRESIDENT'
OR JOB = 'MANAGER'
GROUP BY JOB
触发一个触发器,执行一个触发器事件本身就是一个耗费资源的过程; 如果能够使用约束实现的,尽量不要使用触发器; 不要为不同的触发事件(Insert、Update 和 Delete)使用相同的触发器; 不要在触发器中使用事务型代码。
表的主键、外键必须有索引; 数据量超过 300 的表应该有索引; 经常与其他表进行连接的表,在连接字段上应该建立索引; 经常出现在 WHERE 子句中的字段,特别是大表的字段,应该建立索引; 索引应该建在选择性高的字段上; 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引; 复合索引的建立需要进行仔细分析,尽量考虑用单字段索引代替; 正确选择复合索引中的主列字段,一般是选择性较好的字段; 复合索引的几个字段是否经常同时以 AND 方式出现在 WHERE 子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引; 如果复合索引中包含的字段经常单独出现在 WHERE 子句中,则分解为多个单字段索引; 如果复合索引所包含的字段超过 3 个,那么仔细考虑其必要性,考虑减少复合的字段; 如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引; 频繁进行数据操作的表,不要建立太多的索引; 删除无用的索引,避免对执行计划造成负面影响; 表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。 尽量不要对数据库中某个含有大量重复的值的字段建立索引。
从二级复制服务器上进行备份; 在进行备份期间停止复制,以避免在数据依赖和外键约束上出现不一致; 彻底停止 MySQL,从数据库文件进行备份; 如果使用 MySQL dump 进行备份,请同时备份二进制日志文件 – 确保复制没有中断; 不要信任 LVM 快照,这很可能产生数据不一致,将来会给你带来麻烦; 为了更容易进行单表恢复,以表为单位导出数据——如果数据是与其他表隔离的。 当使用 mysqldump 时请使用 –opt; 在备份之前检查和优化表; 为了更快的进行导入,在导入时临时禁用外键约束。; 为了更快的进行导入,在导入时临时禁用唯一性检测; 在每一次备份后计算数据库,表以及索引的尺寸,以便更够监控数据尺寸的增长; 通过自动调度脚本监控复制实例的错误和延迟; 定期执行备份。
myisam:应用时以读和插入操作为主,只有少量的更新和删除,并且对事务的完整性,并发性要求不是很高的。 InnoDB:事务处理,以及并发条件下要求数据的一致性。除了插入和查询外,包括很多的更新和删除。(InnoDB 有效地降低删除和更新导致的锁定)。 对于支持事务的 InnoDB类 型的表来说,影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的,而且程序没有显式调用 BEGIN 开始事务,导致每插入一条都自动提交,严重影响了速度。可以在执行 SQL 前调用 begin,多条 SQL 形成一个事物(即使 autocommit 打开也可以),将大大提高性能。