分表之后查询性能下降明显

[aaa930811]

根据时间列分表之后，如果查询时间段的数据分散在2个及以上的表中的时候查询性能比单表查询慢很多，想问问是我查询方法不对吗？ ···

[MemoryDiagnoser, RankColumn] public class SqlSugarTestBenchmarker { private static string connstr = "Server=localhost;Port=3306;Database=SqlSugarTestDB;Uid=sa;Password=123456;Convert Zero Datetime=True;SslMode=none;AllowLoadLocalInfile=true;";

    DateTime starttime = Convert.ToDateTime("2023-07-15");
    DateTime endtime = Convert.ToDateTime("2023-08-15");

    public void GenerateData()
    {
        SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = connstr,
            DbType = DbType.MySql,
            IsAutoCloseConnection = true
        }, db =>
        {
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                Console.WriteLine(sql);//输出sql,查看执行sql 性能无影响
            };
        });
        var students = new List<Student>();
        var students2 = new List<StudentSplitTable>();
        for (int i = 0; i < 20000; i++)
        {
            int seed = i % 10;
            students.Add(new Student
            {
                Id = Guid.NewGuid(),
                Name = $"Test_{i}",
                Sex = "男",
                Phone = $"Phone_{i}",
                CreateTime = DateTime.Now.AddMonths(seed),
                IsDeleted = false
            });
            students2.Add(new StudentSplitTable
            {
                Id = Guid.NewGuid(),
                Name = $"Test_{i}",
                Sex = "男",
                Phone = $"Phone_{i}",
                CreateTime = DateTime.Now.AddMonths(seed),
                IsDeleted = false
            });
        }
        db.CodeFirst.InitTables<Student>();
        db.Fastest<Student>().BulkCopy(students);
        db.Fastest<StudentSplitTable>().SplitTable().BulkCopy(students2);
    }

    [Benchmark]
    public void StatisticBySingleTable()
    {
        int total = 0;
        SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = connstr,
            DbType = DbType.MySql,
            IsAutoCloseConnection = true
        }, db =>
        {
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                Console.WriteLine(sql);//输出sql,查看执行sql 性能无影响
            };
        });
        var list = db.Queryable<Student>().Where(x => x.CreateTime >= starttime && x.CreateTime < endtime).OrderByDescending(x => x.CreateTime).ToPageList(1, 1000);
    }

    [Benchmark]
    public void StatisticBySplitTable()
    {
        int total = 0;
        SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = connstr,
            DbType = DbType.MySql,
            IsAutoCloseConnection = true
        }, db =>
        {
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                Console.WriteLine(sql);//输出sql,查看执行sql 性能无影响
            };
        });
        var list = db.Queryable<StudentSplitTable>().SplitTable(starttime, endtime).OrderByDescending(x => x.CreateTime).ToPageList(1, 1000);
    }
}

public class Student
{
    [SugarColumn(IsPrimaryKey = true, IndexGroupNameList = new string[] { "Id" })]
    public Guid Id { get; set; }

    [SugarColumn(IndexGroupNameList = new string[] { "Name" })]
    public string Name { get; set; }

    public string Sex { get; set; }

    [SugarColumn(IndexGroupNameList = new string[] { "Phone" })]
    public string Phone { get; set; }

    [SugarColumn(IndexGroupNameList = new string[] { "CreateTime" })]
    public DateTime CreateTime { get; set; }

    public bool IsDeleted { get; set; }
}

[SplitTable(SplitType.Month)]//按月分表 （自带分表支持 年、季、月、周、日）
[SugarTable("StudentSplitTable_{year}{month}{day}")]//3个变量必须要有，这么设计为了兼容开始按年，后面改成按月、按日
public class StudentSplitTable
{
    [SugarColumn(IsPrimaryKey = true, IndexGroupNameList = new string[] { "Id" })]
    public Guid Id { get; set; }

    [SugarColumn(IndexGroupNameList = new string[] { "Name" })]
    public string Name { get; set; }

    public string Sex { get; set; }

    [SugarColumn(IndexGroupNameList = new string[] { "Phone" })]
    public string Phone { get; set; }

    [SplitField]
    [SugarColumn(IndexGroupNameList = new string[] { "CreateTime" })]
    public DateTime CreateTime { get; set; }

    public bool IsDeleted { get; set; }
}

···

[DotNetNext]

时间列加索引了吗

[DotNetNext]

跨月查询肯定是会慢些的

[DotNetNext]

你这个DEMO分页还是很快的

[DotNetNext]

比如你有1亿数据分了100个表，你查2个表肯定 查询一个表中有1亿数据的快

[DotNetNext]

你这种2万数据分表没什么意义

[aaa930811]

时间列加索引了吗

看上面的model，加过了的

[aaa930811]

你这种2万数据分表没什么意义

这只是一个测试，实际我的单表有3700W数据，性能下降更明显了

[aaa930811]

比如你有1亿数据分了100个表，你查2个表肯定 查询一个表中有1亿数据的快

但是我实际发现比查单表慢诶

[aaa930811]

比如你有1亿数据分了100个表，你查2个表肯定 查询一个表中有1亿数据的快

但是我实际发现比查单表慢诶

如果要查询的数据恰好只在其中的一个分表中那么确实是比单表查询快很多，但是实际情况几乎肯定会跨多个分表查询的

[DotNetNext]

那就要合适分表，比如按年

[DotNetNext]

还有mysql不要查询count

[DotNetNext]

where写到splitable前面

[aaa930811]

那就要合适分表，比如按年

那比如我要查询2022-12-01到2023-01-31的数据，那还是跨表了呀

[DotNetNext]

目前是没办法的，跨2个表性能还可以的。只能接受就行

[DotNetNext]

Mysql跨表性能是差些，不过只要不跨太多还是可以的。 在非跨库查询、更新、删除、插入性能都有质的提升