活动sql语句索引基本优化

前言

        最近接到了一个需求开发，然后开发完成以后打算对sql进行一些优化，于是等所有功能开发完成以后对mapper文件里面的sql,和service层的查询语句都摘出来，然后设计了一些索引，下面就来说说一些大概的优化思路，至于mysql索引这里就不说了，这里只是记录优化的大概思路

优化的过程

        首先先看下数据表的设计，这里只拿了其中两张来进行说明，其中核心字段都去掉了，主要是看查询的核心参数

数据表

CREATE TABLE `zxc_product` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `activity_id` bigint NOT NULL COMMENT '归属活动id',
	 `name` varchar(2000)  DEFAULT NULL COMMENT '商品英文名称',
	 `name_zh` varchar(2000)  DEFAULT NULL COMMENT '商品中文名称',
  `name_km` varchar(2000) DEFAULT NULL COMMENT '商品柬文名称',
	`store_no` varchar(2000) DEFAULT NULL COMMENT 'no',
  `zxc_store_id` bigint NOT NULL COMMENT '归属参与活动门店id',
  `product_id` bigint NOT NULL COMMENT '商品id',
	 `last_new` tinyint DEFAULT '1' COMMENT '最新的,用来去重的,0-不是,1-是,同一个productId只会有一个是',
  `join_state` tinyint DEFAULT '10' COMMENT '加入状态，10-已加入，11-已退出',
	`del_state` tinyint NOT NULL DEFAULT '10',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='zxc_product表';





CREATE TABLE `zxc_store` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `activity_id` bigint NOT NULL COMMENT '归属活动id',
  `store_id` bigint NOT NULL COMMENT '参与门店主键',
  `store_no` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '参与门店no',
  `store_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `store_name_zh` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `store_name_km` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_state` tinyint NOT NULL DEFAULT '10' COMMENT '删除标识符10:正常 11:禁用',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='zxc_store参与表';

存在的sql语句

 select zxc_store_id as activityStoreId, count(1) as productCount from zxc_product where zxc_store_id in(2)  and join_state = 10 and del_state = 10 group by zxc_store_id;

explain结果

  select * from zxc_product where del_state = 10  and (name like concat('%', 'zxc', '%') or name_zh like concat('%', 'zxc', '%') or name_km like concat('%', 'zxc', '%'))
and zxc_store_id = 1 and activity_id = 1 and join_state = 10;

explain结果

select * from zxc_product where del_state = 10 and id like concat('%', 10, '%') and store_no = '11' and zxc_store_id = 1 and activity_id = 2 and join_state = 10; 

explain结果

 select
        sp.id,
        sp.product_id,
        sp.store_no,
        sp.name,
        sp.name_zh,
        sp.name_km,
        s.store_name,
        s.store_name_zh,
        s.store_name_km
        from zxc_product sp left join zxc_store s on sp.zxc_store_id = s.id
        where sp.last_new = 1
        and (s.store_no like concat('%', 'zxc', '%')
            or s.store_name like concat('%', 'zxc', '%')
            or s.store_name_zh like concat('%', 'zxc', '%')
            or s.store_name_km like concat('%', 'zxc', '%'))
           and sp.activity_id = 1;

explain结果

select
        sp.id,
        sp.product_id,
        sp.name,
        sp.name_zh,
        sp.name_km
        from zxc_product sp
        where sp.last_new = 1
      and sp.zxc_store_id = 1； 

explain结果

以上是目前的所有sql,很明显全都是all查询，那么当数据量一大的时候，性能肯定会比较差，因此我们就需要添加索引来提升查询的性能

优化思路 

        sql索引优化一般是尽量不要用单值索引，因为索引也是要占空间的，如果索引过多插入和删除性能也会下降，所以我们一般都是使用复合索引，尽可能的包含所有的查询条件，一张表最多的复合索引也不能过多

        上面的几个sql查询中，其实存在很明显的一个字段会被用到，那就是zxc_store_id字段，所以我们是肯定需要创建一个以zxc_store_id为主的复合索引

        那么问题就来了，它后面还需要加什么比较合适，我们接着往下看，综合看5条sql，很多都会用到 join_state 和 del_state 虽然这两个值都是基数比较低的，但是很多地方都会用到，本来字段类型就是整形的，所以还是可以加入到里面去的

        第2条sql里面还有activity_id，这个字段基数就比较大了，所以我们还应该加一个这个字段，而这个字段需要放在join_state和del_state 前面，因为前面两个基数太低，过滤是很有限制的，store_no字段也是类似的

        最后就是last_new字段了，但是这个使用比较小，而且它的基数也比较低，所以这里我就不进行添加了，所以最后出来的一个索引，就是以五个字段组成的，如下

zxc_store_id，activity_id，store_no, del_state，join_state 按先后顺序创建索引

索引创建sql语句

ALTER  TABLE  `zxc_product`  ADD  INDEX idx_query (zxc_store_id,activity_id, store_no, del_state, join_state)

但是mysql报索引过长了，所以就只能改完4个字段了，如下

ALTER  TABLE  `zxc_product`  ADD  INDEX idx_query (zxc_store_id,activity_id, del_state, join_state)

加入这个索引以后，5个sql,有4个sql的type类型变为了ref，性能其实已经很高了，除了第4条还是all，如下

explain select
        sp.id,
        sp.product_id,
        sp.store_no,
        sp.name,
        sp.name_zh,
        sp.name_km,
        s.store_name,
        s.store_name_zh,
        s.store_name_km
        from zxc_product sp left join zxc_store s on sp.zxc_store_id = s.id
        where sp.last_new = 1
        and (s.store_no like concat('%', 'zxc', '%')
            or s.store_name like concat('%', 'zxc', '%')
            or s.store_name_zh like concat('%', 'zxc', '%')
            or s.store_name_km like concat('%', 'zxc', '%'))
           and sp.activity_id = 1;

explain结果

但是这也是没办法的，因为你确实是在找全部数据了，除非你再进一步过滤数据，不过总体来说，我们只需要进入一个复合索引，查询性能就会比完成没有加索引要快很多了

至于上面的store_no如果也要加入索引，可以再重新设计一个复合索引

总结

        其实这个例子并不复杂，而且相对来说还很简单，只是说我们平时对于索引的创建最好是在功能开发完成以后再进行维护，不然你可能也不知道要在哪些字段加索引，同时我们也要尽量使用复合索引