[Indexer] [break change] Split Object states and perf Indexer

[baichuan3]

Summary

This pr will break the indexer db, need cleanup the roochdb data before restart the rooch server. relative of #2402

Mainly on solving the query and paginate performance issues after Sqlite single table has more than 100 million records, by spliting the Objectstates table, optimize the index fields, optimize the paginate performance, etc.

1. Split object_states into object_states, utxos and inscriptions, and fix indexer rebuild and tx revert tools
2. Optimize object_states page query based on object_type, use index query
3. Rebuild index, optimize all order by queries, ensure that indexes can be used, avoid USE TEMP B-TREE FOR ORDER BY
4. For object_states query, use "select *" with caution, and specify the columns to be used
5. Only keep object_states, utxos, inscriptions index fields
6. Only keep events index field
7. Only keep transactions index field
8. Compatible with object_ids query #2454
9. Simplify inscription_id query method, [break rpc]
10. Query_events interface supports decode parsing
11. Query_events and query_transactions query ObjectID unified support query by ObjectID array
12. Dynamic fields are not indexed object
13. Further optimize cache and pool size
14. Enable Sqlite multithreading mode PRAGMA SQLITE_THREADSAFE = 2 track by #2486

• Closes #2454

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

Name	Status	Preview	Comments	Updated (UTC)
rooch-portal	✅ Ready (Inspect)	Visit Preview	💬 Add feedback	Aug 26, 2024 9:49am

1 Skipped Deployment

| Name | Status | Preview | Comments | Updated (UTC) | | :--- | :----- | :------ | :------- | :------ | | **rooch** | ⬜️ Ignored ([Inspect](https://vercel.com/rooch/rooch/Evdk28BCmgBBVy4nWqDBoiVYqwjH)) | [Visit Preview](https://rooch-git-indexerperf-rooch.vercel.app) | | Aug 26, 2024 9:49am |

[github-actions[bot]]

Dependency Review

✅ No vulnerabilities or license issues or OpenSSF Scorecard issues found.

OpenSSF Scorecard

Package	Version	Score	Details

Scanned Manifest Files

[baichuan3]

Before optmize：

Indexer write benchmark:
Benchmarking write_object_states/indexer_write/10000
Benchmarking write_object_states/indexer_write/10000
Benchmarking write_object_states/indexer_write/1000000: Collecting 100 samples in estimated 5.4298 s (90
Benchmarking write_object_states/indexer_write/10000
write_object_states/indexer_write/1000000
                        time:   [4.9820 ms 5.2295 ms 5.4758 ms]
                        change: [-4.2140% +1.8781% +9.0716%] (p = 0.59 > 0.05)
                        No change in performance detected.
Indexer read benchmark
Benchmarking read_object_states/indexer_read/1000000: Collecting 100 samples in estimated 49.284 s (100
Benchmarking read_object_states/indexer_read/1000000
read_object_states/indexer_read/1000000
                        time:   [485.53 ms 486.66 ms 487.86 ms]
                        change: [-50.861% -50.709% -50.567%] (p = 0.00 < 0.05)
                        Performance has improved.
Found 5 outliers among 100 measurements (5.00%)
  5 (5.00%) high mild

Before optimize:

cargo bench --bench bench_indexer
    Finished `bench` profile [optimized] target(s) in 1.03s
     Running benches/bench_indexer.rs (/Users/Baichuan/coinspace/move-eco/rooch-network/rooch/target/release/deps/bench_indexer-b7088a53bb875e1e)
Gnuplot not found, using plotters backend
Benchmarking write_object_states/indexer_write/10000
Benchmarking write_object_states/indexer_write/10000
Benchmarking write_object_states/indexer_write/1000000: Collecting 100 samples in estimated 5.0022 s (80
Benchmarking write_object_states/indexer_write/10000
write_object_states/indexer_write/1000000
                        time:   [5.4034 ms 5.6397 ms 5.8834 ms]

Benchmarking read_object_states/indexer_read/1000000
Benchmarking read_object_states/indexer_read/1000000: Collecting 100 samples in estimated 5.7024 s (35k
Benchmarking read_object_states/indexer_read/1000000
read_object_states/indexer_read/1000000
                        time:   [162.41 µs 166.17 µs 171.66 µs]
Found 8 outliers among 100 measurements (8.00%)
  3 (3.00%) high mild
  5 (5.00%) high severe