【mysql】优化

limit优化

---

• 背景：通常情况下，limit 4000000, 25会导致慢查询很慢，多次查询会导致mysql集群瘫痪
• 分析：limit 4000000, 25 会导致mysql扫表从1 扫到4000025，取出4000001-4000025的25条数据，大大降低查询效率
• 优化措施：
  • 降级策略
    • 对查询跳页传值限制优化：对pageSize 较小，而pageNum过大的请求予以限制，自行做业务判断限制pageSize和pageNum，减少此类查询
  • 使用索引覆盖+子查询优化
    • 子查询获取偏移量的id，再向后查找

    		 /*子查询获取偏移100条的位置的id，在这个位置上往后取25*/
    		 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    		 from emp a left join dep b on a.depno = b.depno
    		 where a.id >= (select id from emp order by id limit 100,1)
    		 order by a.id limit 25;
    		
    		 /*子查询获取偏移4000000条的位置的id，在这个位置上往后取25*/
    		 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    		 from emp a left join dep b on a.depno = b.depno
    		 where a.id >= (select id from emp order by id limit 4000000)
    		 order by a.id limit 25;
    

  • 起始位置重定义
    • 记住上次分页的最后一条数据，从此往后查询

    		 /*记住了上次的分页的最后一条数据的id是100，这边就直接跳过100，从101开始扫描表*/
    		 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    		 from emp a left join dep b on a.depno = b.depno
    		 where a.id > 100 order by a.id limit 25;
    		
    		 /*记住了上次的分页的最后一条数据的id是4000000，这边就直接跳过4000000，从4000001开始扫描表*/
    		 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    		 from emp a left join dep b on a.depno = b.depno
    		 where a.id > 4000000
    		 order by a.id limit 25;
    

mysql主键生成策略

---

• 背景：主键自增长为数据表带来一些检索便利、易排序的优点，但需要处理删除后主键不连续问题
• 操作：
  • 重置AUTO_INCREMENT的值ALTER TABLE user_table AUTO_INCREMENT =1
  • 考虑新的主键生成策略
    • 分布式ID生成算法–SnowFlake
      • 参考博客，后续整理
    • 美团Leaf源码—号段模式
      • 参考博客，后续整理