索引应该注意什么

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		建立索引		建立索引，减少全表扫描

      

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

优化后：

                     

 

 

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>