Android @Relation和Indexes

Question

我的房间设置中有这个类，它将来自states表的数据与来自countries表的相应数据组合在一起：

public class StateWithCountryData {

    @Embedded
    public State state;

    @Relation(parentColumn = "country_id", entityColumn = "_id", entity = Country.class)
    public Country country;
}

正如您在我的@Relation代码中看到的那样，我的states.country_id列被连接到my countries._id列中。

我想知道的是@Relation代码是否会在states.country_id上创建一个自动索引，还是需要自己在State实体类中创建索引？

如果是后者，如何将所需的索引添加到状态实体类中？下面是：

@Entity(tableName = "states")
public class State {

    @PrimaryKey
    @ColumnInfo(name = "_id")
    private long stateId;

    @NonNull
    @ColumnInfo(name = "state_name")
    private String stateName;

    @ColumnInfo(name = "country_id")
    private long countryId;

    @ColumnInfo(name = "last_modified")
    private Date lastModified;

    public State(long stateId, @NonNull String stateName, long countryId, Date lastModified) {
        this.stateId = stateId;
        this.stateName = stateName;
        this.countryId = countryId;
        this.lastModified = lastModified;
    }

    public long getStateId() {
        return stateId;
    }

    public void setStateId(long stateId) {
        this.stateId = stateId;
    }

    @NonNull
    public String getStateName() {
        return stateName;
    }

    public void setStateName(@NonNull String stateName) {
        this.stateName = stateName;
    }

    public long getCountryId() {
        return countryId;
    }

    public void setCountryId(long countryId) {
        this.countryId = countryId;
    }

    public Date getLastModified() {
        return lastModified;
    }

    public void setLastModified(Date lastModified) {
        this.lastModified = lastModified;
    }
}

Answer 1

我想知道的是@Relation代码是否会在states.country_id上创建一个自动索引，还是需要自己在国家实体类中创建索引？

检查的方法是查看@数据库生成的代码，例如，您将得到以下内容:-

final SupportSQLiteOpenHelper.Callback _openCallback = new RoomOpenHelper(configuration, new RoomOpenHelper.Delegate(1) {
  @Override
  public void createAllTables(SupportSQLiteDatabase _db) {
    _db.execSQL("CREATE TABLE IF NOT EXISTS `Country` (`id` INTEGER NOT NULL, `name` TEXT, PRIMARY KEY(`id`))");
    _db.execSQL("CREATE TABLE IF NOT EXISTS `states` (`_id` INTEGER NOT NULL, `state_name` TEXT NOT NULL, `country_id` INTEGER NOT NULL, `last_modified` TEXT, PRIMARY KEY(`_id`))");
    _db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
    _db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '008d8fa1f719c970d7a1182e9e43f80b')");
  }
.....

正如您所看到的，答案是，no，。

如果是后者，如何将所需的索引添加到我的国家实体类中？

你可以使用:-

@Entity(tableName = "states"
    , indices = {@Index(name = "ixCountryId", value = "country_id")} //<<<<<<<<<< ADDED
    )

现在生成的代码是:-

@Override
  protected SupportSQLiteOpenHelper createOpenHelper(DatabaseConfiguration configuration) {
    final SupportSQLiteOpenHelper.Callback _openCallback = new RoomOpenHelper(configuration, new RoomOpenHelper.Delegate(1) {
      @Override
      public void createAllTables(SupportSQLiteDatabase _db) {
        _db.execSQL("CREATE TABLE IF NOT EXISTS `Country` (`id` INTEGER NOT NULL, `name` TEXT, PRIMARY KEY(`id`))");
        _db.execSQL("CREATE TABLE IF NOT EXISTS `states` (`_id` INTEGER NOT NULL, `state_name` TEXT NOT NULL, `country_id` INTEGER NOT NULL, `last_modified` TEXT, PRIMARY KEY(`_id`))");
        _db.execSQL("CREATE INDEX IF NOT EXISTS `ixCountryId` ON `states` (`country_id`)");
        _db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
        _db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '74ee12325d1720c24abff0e5ce479d81')");

即线

 _db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS `ixCountryId` ON `states` (`country_id`)");

已经加入了。

一个更简单的选项是在@ColumnInfo级别上编码，而不是在@实体级别上编码指示符。

@ColumnInfo(name = "country_id", index = true)
private long countryId;

这将自动生成索引名和结果，在上述情况下，在生成的代码中包含以下内容(而不是前面显示的行)：

_db.execSQL("CREATE INDEX IF NOT EXISTS `index_states_country_id` ON `states` (`country_id`)");

你也可以同时使用这两种(而不是你会做的事情，因为这是浪费/低效的)，这会导致:-

    _db.execSQL("CREATE INDEX IF NOT EXISTS `ix01` ON `states` (`country_id`)");
    _db.execSQL("CREATE INDEX IF NOT EXISTS `index_states_country_id` ON `states` (`country_id`)");

• @实体级的编码提供了更多的选项，例如复杂(多列)或唯一索引。

还是需要自己在国家实体类中创建索引？

不一定也可能不是，因为指数似乎会产生负面影响，但取决于国家和国家的数目，如果很低，那么影响可能微不足道。

考虑下面的测试代码，它创建了10000个国家和500000个随机分配给国家的州(每个国家大约有50个州)。

使用两个核心查询:-

SELECT * FROM states WHERE country_id > 500;
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;

在创建索引之前和之后，索引之前的时间是

SELECT * FROM states WHERE country_id > 500
> OK
> Time: 0.563s


SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500
> OK
> Time: 0.074s

在建立索引之后:-

SELECT * FROM states WHERE country_id > 500
> OK
> Time: 2.764s


SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500
> OK
> Time: 0.158s

所以索引要慢一些(第一个查询大约慢5倍，第二个查询慢9.5倍)。

在运行查询之前，代码还会执行解释查询计划。

索引创建前:-

查询1 :-

​

​

查询2 :-

​

​

索引创建后

查询1 :-

​

​

查询2 :-

​

​

使用的代码(使用Navicat) :-

DROP TABLE IF EXISTS Country;
DROP TABLE IF EXISTS states;
CREATE TABLE IF NOT EXISTS `Country` (`id` INTEGER NOT NULL, `name` TEXT, PRIMARY KEY(`id`));
CREATE TABLE IF NOT EXISTS `states` (`_id` INTEGER NOT NULL, `state_name` TEXT NOT NULL, `country_id` INTEGER NOT NULL, `last_modified` TEXT, PRIMARY KEY(`_id`));

/* Load data into the Country table */
WITH RECURSIVE cte1(counter,base) AS (
    SELECT 1, 'COUNTRY_A'
    UNION ALL SELECT counter+1,'COUNTRY_A' FROM cte1 LIMIT 10000
)
INSERT INTO Country (name) SELECT base||counter FROM cte1; 

/* Load fata into the states table */
WITH RECURSIVE cte1(counter,base,cid) AS (
  SELECT 1,'STATE_S', (abs(random()) % (SELECT count() FROM Country)) + 1
    UNION ALL SELECT 
        counter+1,
        'STATE_S',
      (abs(random()) % (SELECT count() FROM Country)) + 1   
    FROM cte1 LIMIT 500000
)
INSERT INTO states (state_name, country_id) SELECT base||counter, cid FROM cte1; 

EXPLAIN QUERY PLAN
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;
EXPLAIN QUERY PLAN
SELECT * FROM states WHERE country_id > 500;

SELECT * FROM states WHERE country_id > 500;
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;

/* Create the Index */
CREATE INDEX IF NOT EXISTS `ix01` ON `states` (`country_id`);

EXPLAIN QUERY PLAN
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;
EXPLAIN QUERY PLAN
SELECT * FROM states WHERE country_id > 500;

SELECT * FROM states WHERE country_id > 500;
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;

/* Show states per country */
SELECT Country.name,count() AS states_inCountry FROM States JOIN Country ON country_id = Country.id GROUP BY country_id;

/* Clean up */
DROP INDEX IF EXISTS ix01;
DROP TABLE IF EXISTS states;
DROP TABLE If EXISTS Country;

• 注意，这可能有点不公平的比较/测试，因为索引的创建可能会对后续的运行条件产生影响。理想情况下，查询应该彼此独立运行，并与创建索引分开。