2.1 分页的优化
条目 | 语句 | 优化方法 | 条件 | 说明 |
---|---|---|---|---|
优化1 | select * from table limit #{offset} #{limit} | select * from table where id > #max_id# order by id limit n; | id上有索引,id是有序递增的 | 记录上一页最大的id号 |
优化2 | select * from table as a inner join (select id from table order by id limit m, n) | id上有索引 | 内部的子查询只扫描了id,用子查询确定了所需要的id;进而不用扫描整张表 | |
优化3 | 建立索引 | 建立索引,减少全表扫描 |
2.2 count(*) 的优化
//获取当前记录的总数
total = dishSpuService.getSpuCount(tenantId, menuId, DishSpu.TYPE_NORMAL_SPU);
sql语句:
|
查询分析:explain select count(*) from dish_spu where tenant_id=16796 and menu_id=98 and status=1; (当前的dish_spu表在(tenant_id, menu_id)上建立了复合索引);
可以看出需要到聚合索引扫描数据(这个可以被优化);
优化:
条目 | 语句 | 优化方法 | 条件 | 说明 |
---|---|---|---|---|
条目 | 语句 | 优化方法 | 条件 | 说明 |
优化1 | SELECT count(*) FROM dish_spu WHERE `tenant_id` = #{tenantId} AND menu_id = #{menuId} AND status =#{status} | 建立(tenant_id, menu_id,status)的复合索引; create index idx_t_m_s on `dish_spu`(tenant_id, menu_id, status) | 这样count操作只需要扫描索引,而不需要扫描数据节点 |
优化后:
2.3 对于当前翻页需要两次sql查询的案例,可以修改为一次sql调用; 即使用SQL_CALC_FOUND_ROWS
|
利用 SELECT FOUND_ROWS()可以获取所有行的总数。
这虽然是两个sql语句,但是确是查询一次数据库,效率提高了一半。其中SQL_CALC_FOUND_ROWS 告诉Mysql将sql所处理的行数记录下来,FOUND_ROWS() 则取到了这个纪录。
2.4 考虑产品逻辑的优化
如果当前符合条件的数据很多,并且翻页数很多,可以考虑仅在第一页的时候传count(*), 然后前端缓存该值,后续的翻页都不需要重新拉取;
如果在分页浏览期间,有新数据的插入,则会出现数据少量的不一致。
4. 避免使⽤NULL
缺点:NULL也需要额外的空间,会让你的程序变得复杂
很难进行查询优化
NULL列加索引,需要额外空间
复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。
所以我们在数据库设计时不要让字段的默认值为NULL
修改为
created_time not null DEFAULT NOW()
|
5. 避免使用大的update
案例: 批量修改spu排序表中的值。
|
缺点: 一条大update语句可能长时间锁表;
高并发的场景下可能导致服务崩溃;
当前端出现异常的调用,而后端没有判断,存在异常超大数据量的更新风险
说明:类似select * 语句属于快照读,不需要加锁。
插入/更新/删除操作,属于当前读,需要加锁。 update SQL被发给后,MYSQL server根据where条件读取第一条条件的记录,然后InnoDB返回第一条记录并加锁(current read),然后再发起一个update请求。
改进:建议在应用层加更新条目的限制,约定一个limit,超出这个值,返回异常
6. 当知道只会有一条结果, 或者只需要返回一条数据时, 加上limit 1可以提升性能
案例:在菜品和关系对应表中拉取制定菜品的对应的分类。
|
分析:如果不加limit 1,sql语句会在查找到符合条件的一条数据后,继续在聚簇索引或者辅助索引中遍历更多的数据。
7. 筛选出索引无效的sql语句
案例: 获取指定调拨单的所有调拨明细
|
如果不建立索引,则查询会很慢,特别是当每一行的数据比较多的情况。这时候建立索引是减少扫描表范围的有效手段
即在当前表建立(requisition_id,status)的索引。
但是上面的语句还是不能利用复合索引中的status索引,因为where 中有!=时,索引无效。
可优化为:
|