SQLite tutorial (5): Index and data analysis/cleanup, sqlite Data Analysis

SQLite tutorial (5): Index and data analysis/cleanup, sqlite Data Analysis

I. Create an index:

In SQLite, the SQL syntax for index creation is basically the same as that of most other relational databases, because here is just an example usage:
 Copy codeThe Code is as follows:
Sqlite> create table testtable (first_col integer, second_col integer );
-- Create the simplest index, which is based on a field in a table.
Sqlite> create index testtable_idx ON testtable (first_col );
-- Create a joint index. The index is based on multiple fields in a table and can specify the sorting rules (ascending or descending) for each field ).
Sqlite> create index testtable_idx2 ON testtable (first_col ASC, second_col DESC );
-- Create a unique index. This index rule is the same as that of the data table. That is, NULL is different from any value, including NULL.
Sqlite> create unique index testtable_idx3 ON testtable (second_col DESC );
Sqlite>. indices testtable
Testtable_idx
Testtable_idx2
Testtable_idx3
 
From the output of the. indices command, we can see that all three indexes have been created successfully.

Ii. delete an index:

The deletion of indexes is very similar to the deletion of views. Therefore, the following is an example:Copy codeThe Code is as follows:
Sqlite> drop index testtable_idx;
-- IF you delete an index that does not exist, the Operation will fail. IF you do not want the error to be thrown, you can use the "if exists" clause.
Sqlite> drop index testtable_idx;
Error: no such index: testtable_idx
Sqlite> drop index if exists testtable_idx;

Iii. Re-indexing:

Re-indexing is used to delete an existing index and re-indexing the index based on its original rules. Note that if the database name is not provided after the REINDEX statement, all the indexes in all the Attached databases connected to the current database will be rebuilt. If the Database Name and table name are specified, all indexes in the table will be rebuilt. If only the index name is specified, the specified index of the current database will be rebuilt.
 Copy codeThe Code is as follows:
-- Indexes in all databases connected to attached are rebuilt.
Sqlite> REINDEX;
-- Rebuild all indexes of the testtable table in the current primary database.
Sqlite> REINDEX testtable;
-- Rebuild the index named testtable_idx2 in the current primary database.
Sqlite> REINDEX testtable_idx2;

Iv. Data Analysis:

Similar to PostgreSQL, the ANALYZE command in SQLite is also used to ANALYZE data in data tables and indexes and store the statistical results in the internal system table of SQLite, in this way, the query optimizer can select the optimal query execution path based on the analyzed statistical data to improve the overall query efficiency. See the following example:
 Copy codeThe Code is as follows:
-- If no parameters are specified after the ANALYZE command, ANALYZE the tables and indexes in all the Attached databases in the current connection.
Sqlite> ANALYZE;
-- If the database is specified as the ANALYZE parameter, all tables and indexes in the database will be analyzed and statistical data will be generated.
Sqlite> ANALYZE main;
-- If you specify a table or index in the database as the ANALYZE parameter, the table and all its associated indexes will be analyzed.
Sqlite> ANALYZE main. testtable;
Sqlite> ANALYZE main. testtable_idx2;

5. data cleanup:

Compared with the VACUUM commands in PostgreSQL, their functions and implementation methods are very similar. The difference is that PostgreSQL provides a finer granularity, while SQLite can only apply this command to the database, it cannot be accurate to the specified data table or index in the database. However, this is exactly what PostgreSQL can do.
When one or more data tables in a database have a large number of insert, update, and delete operations, a large amount of disk space will be occupied by deleted data, SQLite did not return the VACUUM commands to the operating system until they were executed. Because the data storage in such data tables is very scattered, it is impossible to obtain better batch IO reading results during query, thus affecting the query efficiency.
In SQLite, only the master database in the current connection can be cleared, but other Attached databases cannot be cleared. The vacuum command adopts the same policy as PostgreSQL when cleaning up data, that is, to create a new database file of the same size as the current database file, then, import the data in the database file to the new file. The deleted data blocks are not imported. After the import is completed, shrink the size of the new database file to an appropriate size. This command is very simple to execute, such:
 Copy codeThe Code is as follows:
Sqlite> VACUUM;