索引应该注意什么

最新推荐文章于 2025-03-23 01:38:45 发布

原创最新推荐文章于 2025-03-23 01:38:45 发布 · 257 阅读

0 ·

CC 4.0 BY-SA版权

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) as b on a.id= b.id	id上有索引	内部的子查询只扫描了id，用子查询确定了所需要的id；进而不用扫描整张表
优化3		建立索引		建立索引，减少全表扫描

条目

语句

优化方法

条件

说明

优化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)

as b on a.id= b.id

id上有索引

内部的子查询只扫描了id，用子查询确定了所需要的id；进而不用扫描整张表

优化3

建立索引

建立索引，减少全表扫描

2.2 count(*) 的优化

//获取当前记录的总数
total = dishSpuService.getSpuCount(tenantId, menuId, DishSpu.TYPE_NORMAL_SPU）；

sql语句：

SELECT count(*) FROM dish_spu WHERE `tenant_id` = #{tenantId} AND menu_id = #{menuId} AND status =#{status}

查询分析：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操作只需要扫描索引，而不需要扫描数据节点

条目

语句

优化方法

条件

说明

条目

语句

优化方法

条件

说明

优化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 id="getCatesByQuery" parameterType="map" resultType="com.sankuai.sjst.erp.invoicing.domain.Cate">

// 增加 SQL_CALC_FOUND_ROWS

SELECT SQL_CALC_FOUND_ROWS `id`, `tenant_id`, `code`, `name`, `parent_id`, `created_time`, `modify_time`

FROM `cate`

WHERE `id` > #{lastMaxId} and`tenant_id` = #{tenantId} AND `status` != 128

ORDER BY `id` DESC LIMIT #{limit};

</select>

利用 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()

//在（tenant_id, modify_time）上建立的索引，但如果有一条记录的modify_time 缺省为NULL，则它的索引无效

<select id="getSupplier" resultMap="BaseResultMap" parameterType="java.util.Map" >

select

<include refid="Base_Column_List" />

from supplier

where tenant_id = #{tenantId}， `modify_time` > #{modify_time}

</select>

5. 避免使用大的update

案例：批量修改spu排序表中的值。

<update id="deleteBySpuIdAndCateIdList">

UPDATE `dish_spu_rank`

SET `status` = 128

WHERE `tenant_id` = #{tenantId} AND `dish_spu_id` = #{dishSpuId} AND `dish_cate_id` IN

<foreach index="index" item="item" collection="dishCateIds" open="(" separator="," close=")">

#{item}

</foreach>

</update>

缺点：一条大update语句可能长时间锁表；

高并发的场景下可能导致服务崩溃；

当前端出现异常的调用，而后端没有判断，存在异常超大数据量的更新风险

说明：类似select * 语句属于快照读，不需要加锁。

插入/更新/删除操作，属于当前读，需要加锁。 update SQL被发给后，MYSQL server根据where条件读取第一条条件的记录，然后InnoDB返回第一条记录并加锁（current read），然后再发起一个update请求。

改进：建议在应用层加更新条目的限制，约定一个limit，超出这个值，返回异常

6. 当知道只会有一条结果, 或者只需要返回一条数据时, 加上limit 1可以提升性能

案例：在菜品和关系对应表中拉取制定菜品的对应的分类。

<select id="getDishCateRelById" resultType="com.sankuai.sjst.erp.menu.domain.DishCateRel">

SELECT *

FROM `dish_cate_rel`

WHERE `id` = #{id} AND `tenant_id` = #{tenantId};

</select>

分析：如果不加limit 1，sql语句会在查找到符合条件的一条数据后，继续在聚簇索引或者辅助索引中遍历更多的数据。

7. 筛选出索引无效的sql语句

案例: 获取指定调拨单的所有调拨明细

<select id="getRequisitionSnapshots" resultMap="RequisitionSnapshot">

SELECT

`id`, `requisition_id`,`material_id`,`material_code`,`material_name`,`quantity`,

`price`, `stock_quantity`,`base_unit_name`,`desc`,`status`,`tenant_id`

FROM

`requisition_snapshot`

WHERE

`requisition_id` = #{requisitionId}

AND

`status` != 128

</select>

如果不建立索引，则查询会很慢，特别是当每一行的数据比较多的情况。这时候建立索引是减少扫描表范围的有效手段

即在当前表建立（requisition_id,status）的索引。

但是上面的语句还是不能利用复合索引中的status索引，因为where 中有!=时，索引无效。

可优化为：

<select id="getRequisitionSnapshots" resultMap="RequisitionSnapshot">

SELECT

`id`, `requisition_id`,`material_id`,`material_code`,`material_name`,`quantity`,

`price`, `stock_quantity`,`base_unit_name`,`desc`,`status`,`tenant_id`

FROM

`requisition_snapshot`

WHERE

`requisition_id` = #{requisitionId}

AND

（`status` > 128 or `status` < 128)

</select>