合理使用索引:减少全表扫描 索引其实是一种数据结构,能够帮助我们快速的检索数据库中的数据,MySQL主要有两种结构:Hash索引和B+ Tree索引,我们使用的是InnoDB引擎,默认的是B+树。B+ 树是一种多路平衡查询树,所以它的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描;可以显著增加查询、删除的速度,以及多表join的速度! 索引增加SQL性能的前提是,SQL语句走索引了,SQL索引需要符合最左前缀的原则,并且如果使用表达式、in等写法都会造成SQL索引失效。 同时,SQL索引会一定程度地增加增、删、改的负担,在对源数据增删改的时候,对索引树也会进行增删改。索引树在单表数据量很庞大的时候,如百万行的时候,会变得十分缓慢,磁盘页会分裂,即使走索引也会拖累查询速度,同时还要考虑索引树所额外占的磁盘和内存空间。 建议学习书籍《MySQL技术内幕:InnoDB存储引擎》和《MySQL性能之巅》,任意吃透其中的一本书,从底层出发,SQL性能优化尽可掌握十分充足。
返回更少的数据1. 禁用select * , 需要几个字段写上几个具体的字段,作用是减少MySQL服务器查询分析器解析成本。2.在代码中写分页查询逻辑时,大的分页,必须使用'limit'限制返回的记录条数,防止过多返回的数据量造成更大的内存垃圾。3. 利用延迟关联或者子查询优化超多分页场景。MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。4. 设置合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度;比如设置人类的age字段,可以选择“unsigned tinyint”。更多细节建议学习书籍《码出高效》。
减少CPU或者内存的资源消耗1. 超过三个表或者更多的大数据量的表禁止 join,因为join是进行笛卡尔积进行运算的,会产生巨量的中间结果集,占用大量内存,可以拆分开到service层处理。2. 需要 join 的字段,数据类型必须绝对一致;多表关联查询时, 保证被关联的字段需要有索引,join后的on条件必须走索引以确保性能。3. 使用子查询优化超多分页场景,比如先快速定位子查询中需要获取的 id 段,然后再关联。更多细节建议学习书籍《某里巴巴Java开发手册》。
SQL语句的规范1. 不得使用外键与级联,一切外键概念必须在应用层解决。级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。2. 禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。3. in 操作能避免则避免,若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内。4. 单表无特殊要求,尽量选用自增主键,自增主键效率更高,产生的记录页分裂的情况更低。5. 学习使用 explain 命令,explain是sql语句分析器,帮助识别sql的查询类型,是否走了索引,以及sql或者索引的类型等等。 更多细节建议学习书籍《高性能MySQL》。
SQL的锁机制和死锁问题1.并发修改同一记录时,避免更新丢失,需要加锁;使用乐观锁替代悲观锁,不建议使用悲观锁,乐观锁使用 version 作为更新依据。悲观锁容易升级,造成的时间开销很大。2. 对多个资源、数据库表、对象同时加锁时,需要保持一致的加锁顺序,否则可能会造成死锁,严重影响性能。 说明:线程一需要对表 A、B、C 依次全部加锁后才可以进行更新操作,那么线程二的加锁顺序也必须是 A、B、C,否则可能出现死锁。3. 禁用级联更新,级联更新是强阻塞,会造成锁表的情况,严重增加数据库负担,存在造成数据库死锁的风险。更多细节建议学习书籍《某里巴巴Java开发手册》。
一步一个脚印,慢慢积累,慢慢学起,注意要多实践,自己多动手泡泡SQL,来感受这些优化措施的优势所在。
喜欢本文的话,欢迎点赞、收藏、留言,希望可以对正在读本文的你有所帮助。☆(*^▽^*)