数据表通用字段 delete_time 导致的性能问题

[colorful3]

对于数据的软删除，Lin CMS是通过delete_time字段是否为null来判断的。因此所有的查询语句，都会拼接上一个 where 条件delete_time is null。这种做法会引发很诸多性能问题：
因为delete_time默认值为null，可以为 null ，会导致查询效率大打折扣，explain会走全表扫描：

推荐增加 is_deleted 字段来做删除标识，这种是比较常用的逻辑删除做法，查询性能上至少比现在的做法要好：

is_deleted int(1)  NOT NULL DEFAULT 0 COMMENT '0：正常 1：已删除'

参考链接：
性能优化案例分析之一：软删除是慢查询的罪魁祸首？
小心 MySQL Soft Delete
为什么 MySQL 不推荐默认值为 null ？

[fuhaodev]

一般查询会有前置条件再加上delete_time过滤，比如使用username查询结果集已经很小了，再加上delete_time对性能影响应该不大吧，使用is_deleted加索引也不合适，这个就和性别字段一样，只有两种情况，区分度非常低，加索引也相当于是全表扫描了

[fuhaodev]

使用delete_time字段主要是为了能知晓删除时间，个人建议可以做一个小优化，delete_time使用long类型的时间戳，正常为0，已删除为删除时的时间戳，查询时使用delete_time=0过滤，这样即兼顾了查询效率又可以知道具体的删除时间

[colorful3]

一般查询会有前置条件再加上delete_time过滤，比如使用username查询结果集已经很小了，再加上delete_time对性能影响应该不大吧，使用is_deleted加索引也不合适，这个就和性别字段一样，只有两种情况，区分度非常低，加索引也相当于是全表扫描了

确实is_deleted 只有两种情况，对于性能提升不大，性能是一方面，另一方面则关联到索引不生效的问题 #204
另外，你提的建议（将 delete_time改为使用long类型的时间戳）确实可行，我们会综合考虑下再做改动，感谢反馈！

[gadfly3173]

datetime类型的delete_time也可以将9999-12-31 23:59:59.999这样特定的时间作为默认值。
虽然mysql通过设置也可以设置0日期，但是这不是默认设置，所以选择一个特别大的时间会更合适。

另外现在项目中提供的schema.sql中是把delete_time字段作为唯一索引的最后一项来建立索引，但是mybatis-plus的QueryWrapper则是会把软删除字段放在第一个，其他字段用AND (xxxx)加在后面。不如就把delete_time直接作为唯一索引的第一项来提高查询效率。

在我的项目中字段改为最大值后实际QueryWrapper生成的语句：

SELECT id,username,nickname,avatar,email,create_time,update_time,delete_time FROM lin_user WHERE delete_time='9999-12-31 23:59:59.999' AND (username = ?)

SELECT id,user_id,identity_type,identifier,credential,create_time,update_time,delete_time FROM lin_user_identity WHERE delete_time='9999-12-31 23:59:59.999' AND (user_id = ? AND identity_type = ? AND identifier = ?)

SELECT COUNT(*) FROM lin_group WHERE delete_time = '9999-12-31 23:59:59.999'

[fuhaodev]

datetime类型的delete_time也可以将9999-12-31 23:59:59.999这样特定的时间作为默认值。
虽然mysql通过设置也可以设置0日期，但是这不是默认设置，所以选择一个特别大的时间会更合适。

另外现在项目中提供的schema.sql中是把delete_time字段作为唯一索引的最后一项来建立索引，但是mybatis-plus的QueryWrapper则是会把软删除字段放在第一个，其他字段用AND (xxxx)加在后面。不如就把delete_time直接作为唯一索引的第一项来提高查询效率。

在我的项目中字段改为最大值后实际QueryWrapper生成的语句：

SELECT id,username,nickname,avatar,email,create_time,update_time,delete_time FROM lin_user WHERE delete_time='9999-12-31 23:59:59.999' AND (username = ?)

SELECT id,user_id,identity_type,identifier,credential,create_time,update_time,delete_time FROM lin_user_identity WHERE delete_time='9999-12-31 23:59:59.999' AND (user_id = ? AND identity_type = ? AND identifier = ?)

SELECT COUNT(*) FROM lin_group WHERE delete_time = '9999-12-31 23:59:59.999'

正常情况下未删除数据占大多数，这种方式有大量的delete_time值都是9999-12-31 23:59:59.999，区分度非常低，作为第一索引不太合适

[fuhaodev]

datetime类型的delete_time也可以将9999-12-31 23:59:59.999这样特定的时间作为默认值。
虽然mysql通过设置也可以设置0日期，但是这不是默认设置，所以选择一个特别大的时间会更合适。
另外现在项目中提供的schema.sql中是把delete_time字段作为唯一索引的最后一项来建立索引，但是mybatis-plus的QueryWrapper则是会把软删除字段放在第一个，其他字段用AND (xxxx)加在后面。不如就把delete_time直接作为唯一索引的第一项来提高查询效率。
在我的项目中字段改为最大值后实际QueryWrapper生成的语句：

SELECT id,username,nickname,avatar,email,create_time,update_time,delete_time FROM lin_user WHERE delete_time='9999-12-31 23:59:59.999' AND (username = ?)

SELECT id,user_id,identity_type,identifier,credential,create_time,update_time,delete_time FROM lin_user_identity WHERE delete_time='9999-12-31 23:59:59.999' AND (user_id = ? AND identity_type = ? AND identifier = ?)

SELECT COUNT(*) FROM lin_group WHERE delete_time = '9999-12-31 23:59:59.999'

正常情况下未删除数据占大多数，这种方式有大量的delete_time值都是9999-12-31 23:59:59.999，区分度非常低，作为第一索引不太合适

Java开发手册有提到
【推荐】建组合索引的时候，区分度最高的在最左边。 正例：如果where a=? and b=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。 说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即建立组合索引idx_d_c。

[gadfly3173]

正常情况下未删除数据占大多数，这种方式有大量的delete_time值都是9999-12-31 23:59:59.999，区分度非常低，作为第一索引不太合适

Java开发手册有提到
【推荐】建组合索引的时候，区分度最高的在最左边。 正例：如果where a=? and b=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。 说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即建立组合索引idx_d_c。

https://github.com/baomidou/mybatis-plus/blob/2864ac656b1e24d52f6b6ef55a73f29c7ff4a108/mybatis-plus-core/src/main/java/com/baomidou/mybatisplus/core/injector/AbstractMethod.java#L204-L243

MP默认的SelectOne语句在拼接的最后一步时把逻辑删除字段前置了（Line 226），可能需要覆写SqlInjector等方法才能把逻辑删除字段后置

[fuhaodev]

正常情况下未删除数据占大多数，这种方式有大量的delete_time值都是9999-12-31 23:59:59.999，区分度非常低，作为第一索引不太合适

Java开发手册有提到
【推荐】建组合索引的时候，区分度最高的在最左边。 正例：如果where a=? and b=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。 说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即建立组合索引idx_d_c。

https://github.com/baomidou/mybatis-plus/blob/2864ac656b1e24d52f6b6ef55a73f29c7ff4a108/mybatis-plus-core/src/main/java/com/baomidou/mybatisplus/core/injector/AbstractMethod.java#L204-L243

MP默认的SelectOne语句在拼接的最后一步时把逻辑删除字段前置了（Line 226），可能需要覆写SqlInjector等方法才能把逻辑删除字段后置

sql书写的字段顺序对查询效率没有影响，比如索引是idx_b_a，查询条件是where a = ? and b = ?，查询也会用到索引idx_b_a，explain可以看出来

[fuhaodev]

正常情况下未删除数据占大多数，这种方式有大量的delete_time值都是9999-12-31 23:59:59.999，区分度非常低，作为第一索引不太合适

Java开发手册有提到
【推荐】建组合索引的时候，区分度最高的在最左边。 正例：如果where a=? and b=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。 说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即建立组合索引idx_d_c。

https://github.com/baomidou/mybatis-plus/blob/2864ac656b1e24d52f6b6ef55a73f29c7ff4a108/mybatis-plus-core/src/main/java/com/baomidou/mybatisplus/core/injector/AbstractMethod.java#L204-L243
MP默认的SelectOne语句在拼接的最后一步时把逻辑删除字段前置了（Line 226），可能需要覆写SqlInjector等方法才能把逻辑删除字段后置

sql书写的字段顺序对查询效率没有影响，比如索引是idx_b_a，查询条件是where a = ? and b = ?，查询也会用到索引idx_b_a，explain可以看出来

Orm一般都会打乱where条件的字段顺序，我们只需要关注索引的建立顺序，where条件的字段顺序其实没有影响

[gadfly3173]

sql书写的字段顺序对查询效率没有影响，比如索引是idx_b_a，查询条件是where a = ? and b = ?，查询也会用到索引idx_b_a，explain可以看出来

了解了 是我对最左前缀的理解出现了错误