sqlserve添加索引

转载

编程小天匠 2024-10-29 12:18:19

文章标签 sqlserve添加索引 sql mysql 数据库字段 文章分类 数据仓库大数据

前言

最近接到了一个需求开发，然后开发完成以后打算对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结果

sqlserve添加索引_sql

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结果

sqlserve添加索引_数据库_02

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结果

sqlserve添加索引_数据库_03

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结果

sqlserve添加索引_sql_04

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结果

sqlserve添加索引_sql_05

以上是目前的所有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结果

sqlserve添加索引_sql_06