南大通用GBase 8s索引优化策略之 单表（where）多字段过滤性能探究

原文链接：

https://www.gbase.cn/community/post/3882

更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

在数据库查询中，单表多字段过滤是一种常见的操作，但若无适当的索引支持，这些查询可能会变得缓慢。本文通过创建测试环境，模拟数据生成，并进行一系列测试SQL的执行，探讨了在不同过滤字段组合下，索引对查询性能的影响。

一、创建测试表

create table t_user(
c_id serial primary key,  --ID
c_name  varchar(20),  --姓名
c_age int,c_sex char(3),  --性别
c_cardno char(20),  --身份证
c_birthday char(10),  --出生日期
c_phone char(11),  --手机号
c_address1 varchar(255),  --联系地址1
c_address2 lvarchar(255)  --联系地址2
);

二、生成测试数据

sh gendata.sh 100000>t_user.unl

#!/bin/sh
#gendata.sh
for i in `seq $1`
do
xing="赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐"
num=$((RANDOM % ${#xing}))
ming="靖铭琛川承司斯宗骁聪在钩锦铎楚铮钦则女宝宝宜用字真心新悦西兮楚初千锐素锦静镜斯舒瑜童楠景茗聿启尧言嘉桉桐筒竹林乔栋家翊松楠景茗聿启尧言嘉桉桐筒竹林乔栋家翊松清澈泫浚润泽向凡文浦洲珩玄洋淮雨子云妍澜淇沐潆盈雨文冰雯溪子云汐潞淇妙涵卓昱南晨知宁年易晗炎焕哲煦旭明阳朗典灿夏珞煊晴彤诺宁恬钧灵昭琉晨曦南毓冉辰宸野安为亦围岚也以延允容恩衡宇硕已意也坤辰伊米安恩以容宛岚又衣亚悠允画"
num1=$((RANDOM % ${#ming}))
num2=$((RANDOM % ${#ming}))
age=$(( $RANDOM % (99)))
xingbies="男女"
xingbie=$((RANDOM % ${#xingbies}))
random_day=$((RANDOM % (36500)))
target_timestamp=$((random_day * 86400))
random_date=$(date -d @$target_timestamp "+%Y-%m-%d")
addr=`openssl rand -base64 100`
echo "0|"${xing:$num:1}${ming:$num1:1}${ming:$num2:1}"|"$age"|"${xingbies:$xingbie:1}"|"$(openssl rand -base64 10 |cksum |cut -c1-8)$(openssl rand -base64 10 |cksum |cut -c1-9)"|"$random_date"|139"$(openssl rand -base64 10 |cksum |cut -c1-8)"|"$addr"|"$addr"|"
done

或者使用python + faker生成测试数据

#!/usr/bin/env python3
import sys
import datetime
from faker  import Faker

# 获取参数
num=0
if len(sys.argv) == 2:
    num=str(sys.argv[1])

# 获取年份
curyear=datetime.datetime.now().year

#设置显示的数据为中文
fdata=Faker("zh_CN")
#将随机数据打印num次
for i in range(int(num)):
    # faker ssn, 获取生日、性别
    ssn=fdata.ssn()
    year=ssn[6:10]
    month=ssn[10:12]
    day=ssn[12:14]
    sex=int(ssn[16:17]) % 2
    birth=f'{year}-{month}-{day}'
    print("%d|%s|%d|%s|%s|%s|%s|%s|%s|" % (
         i+1,
         fdata.name_male() if sex==1 else fdata.name_female(),
         curyear - int(year),
         "男" if sex==1 else "女",
         ssn,
         birth,
         fdata.phone_number(),
         fdata.address(),
         fdata.address()))

三、导入数据

echo "load from t_user.unl insert into t_user;" |dbaccess testdb

四、测试SQL及结果

序号	SQL	表行数	过滤字段	索引字段	执行耗时
1	select * from t_user where c_name='天津南' and c_sex='男' and c_cardno='430524199008129900';	100000	c_name and c_sex and c_cardno	无	0.022 sec
2	select * from t_user where c_name='天津南' and c_sex='男' and c_cardno='430524199008129900';	100000	c_name and c_sex and c_cardno	c_name	0.003 sec
3	select * from t_user where c_name='天津南' and c_sex='男' and c_cardno='430524199008129900';	100000	c_name and c_sex and c_cardno	c_sex	0.041 sec
4	select * from t_user where c_name='天津南' and c_sex='男' and c_cardno='430524199008129900';	100000	c_name and c_sex and c_cardno	c_cardno	0.002 sec
5	select count(*) from t_user where c_name='天津南' or c_sex='男' or c_cardno='430524199008129900';	100000	c_name or c_sex or c_cardno	无	0.027 sec
6	select count(*) from t_user where c_name='天津南' or c_sex='男' or c_cardno='430524199008129900';	100000	c_name or c_sex or c_cardno	c_name	0.028 sec
7	select count(*) from t_user where c_name='天津南' or c_sex='男' or c_cardno='430524199008129900';	100000	c_name or c_sex or c_cardno	c_sex	0.027 sec
8	select count(*) from t_user where c_name='天津南' or c_sex='男' or c_cardno='430524199008129900';	100000	c_name or c_sex or c_cardno	c_cardno	0.028 sec
9	select count(*) from t_user where c_name='天津南' or c_sex='男' or c_cardno='430524199008129900';	100000	c_name or c_sex or c_cardno	c_cardno,c_name,c_sex	0.028 sec
10	select count(*) from t_user where c_name='天津南' or c_cardno='430524199008129900';	100000	c_name or c_cardno	无	0.027 sec
11	select count(*) from t_user where c_name='天津南' or c_cardno='430524199008129900';	100000	c_name or c_cardno	c_name	0.029 sec
12	select count(*) from t_user where c_name='天津南' or c_cardno='430524199008129900';	100000	c_name or c_cardno	c_cardno	0.027 sec
13	select count(*) from t_user where c_name='天津南' or c_cardno='430524199008129900';	100000	c_name or c_cardno	idx1(c_cardno,c_name)	0.042 sec
14	select count(*) from t_user where c_name='天津南' or c_cardno='430524199008129900';	100000	c_name or c_cardno	idx1(c_cardno),idx2(c_name)	0.005 sec

1、多个过滤字段是and拼接，在选择度高的字段上建索引。

2、多个过滤字段是or拼接，如拼接字段中无选择度低的字段，在每个字段分别创建一个索引。

通过对单表多字段过滤查询的测试和分析，我们深入理解了索引在优化数据库查询中的作用。正确的索引设计可以显著提高查询性能，减少系统资源消耗。希望本文的测试结果和优化策略能为数据库管理员和开发者在索引设计方面提供参考。感谢您的阅读，希望本文能为您提供数据库查询性能优化的实用视角。如果您有任何疑问或希望深入讨论索引优化策略，请在评论区留言。关注我们，获取更多数据库性能优化和索引策略的专业指导。

原文链接：

https://www.gbase.cn/community/post/3882

更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。