SQLite INDEX OPTIMIZATION

Reposted on this blog (thanks to this friend ):

Http://hi.baidu.com/3g13/blog/item/f12dc5131313aa1e5baf5301.html

 

I have always liked text-based databases, which are simple, convenient, and easy to save. Recently, the MySQL database of a website was converted into a 2.5 GB SQLite database. The tragedy happened, and it was very slow. The website often took over 30 seconds to run, so it was always carried by the cache. The 10.1 holiday was just free, and it was decided to completely solve this slow problem.

The first is the slow homepage. After carefully analyzing the functions called by the homepage, it is found that they are stuck on a collect table and open the Collect table. There are 0.12 million pieces of data, which is conservatively estimated, at least 1.5 GB of space should be occupied, because the content data is basically in this table. No wonder it's slow. Even if it's a physical machine's high-speed hard disk, it cannot hold such data.

Check the indexes in the Collect table and find that there are only two integer indexes, vtype and Class. Extract the SQL statement of the function:

Select ID, title from collect where vtype = 6 and class = 0 Limit 20

The execution takes more than 40 seconds !!! Tragedy ~~~~

It takes nearly one minute to create a composite index for the class and vtype fields.

Then the query speed immediately drops from 40 seconds to less than 100 ms, and 2nd queries, the speed is within 20 ms, 3rd Times, 4th times and 2nd times is an order of magnitude.

PS: Why is it 1st slow? It is reasonable to say that a text-type database such as SQLite should not be cached. Compared with a service-type database such as MySQL and sqlserver, it should not be fast if no cache is available? I personally think it should be caused by the operating system's Io cache. In the past, when the operating system copied the file, it was deleted after the copy, and 2nd copies were performed. The speed was a lot, probably because of this.

After the above optimization, the homepage can be opened in more than 300 ms, and the page cache is set to 86400 S (that is, updated once a day). The first response is over 300 ms, in the future, the cache is output within 10 ms, and the optimization is successful!

 

The 2nd pages to be optimized are list pages and 0.12 million data entries are displayed on pages. The code is simple:

Select ID, title from collect where vtype> = 5 and class = 0 Limit 20

The SQL statement is very fast, but the page is very slow .. The cause cannot be found.

Later I found that it was the reason for the paging class. The paging class requires the total count, which has one:

Select count (*) from collect where vtype> = 5 and class = 0

My God, the tragedy happened. After two minutes, a 38049 integer was flushed out after two minutes... Why? Why is it so slow when a composite index is made clearly?

So I tried again: Select count (*) from collect, select count (*) from collect where vtype> 5, select count (*) from collect where class = 0

These statements are all very fast. How long is the result obtained in milliseconds? Why is the above statement so slow ??? It is proved that the above where condition is not indexed at all. Baidu and Google rarely describe SQLite compound indexes. There is only one multi-Table query article that tells you how to create an index. It seems that I only tried it myself.

First, I wonder if> = is a problem? Change the statement:

Select count (*) from collect where class = 0 and vtype> 4 (still very slow)

Select count (*) from collect where class = 0 and vtype = 5 (very fast, 20 ms after query)

Select count (*) from collect where class = 0 and vtype in (5, 6) (very slow)

Select count (*) from collect where class = 0 and (vtype = 5 or vtype = 6) (very slow)

Select count (*) from collect where (class = 0 and vtype = 5) or (class = 0 and vtype = 6) (soon, 20 ms query is complete)

Select count (*) from collect where (vtype = 5 and class = 0) or (class = 0 and vtype = 6) (very fast)

Conclusion: we can see from the above that vtype cannot have more than two possible and class operations, it will be faster, and the order of class and vtyp has no effect on the query results; no experiment has been conducted on the index.

 

Since the explain Statement of SQLite is not as easy as MySQL, it cannot know the details of the index. Therefore, we can only speculate and test here to draw some conclusions.

First, there is one problem: Select ID from collect where class = 0 and vtype> 4 Limit 20 can be found soon, proving that the compound index is adopted. However, select count (*) from collect where class = 0 and vtype> 4 is slow. You said that he didn't take the index. At least the index is definitely followed by the WHERE clause. But if you say that he has gone through the index, count (*) is indeed traversing, that is, there is no total number of records in the index.

To verify Why count is so slow and limit is fast, I did an experiment.

Select ID from collect where class = 0 and vtype> 4 Limit 20 (8 ms)

Select ID from collect where class = 0 and vtype> 4 limit 200 (16 ms)

Select ID from collect where class = 0 and vtype> 4 limit 2000 (268 ms)

Select ID from collect where class = 0 and vtype> 4)

20 items are very fast. The more the number is, the slower it is, but the index is indeed taken. Before the index is taken, the same statement is too slow to be scared, which is usually rolled out in a few minutes.

See the following statement:

Select ID from collect where class = 0 and vtype = 5 limit 20 (0 ms)

Select ID from collect where class = 0 and vtype = 5 limit 200 (2 ms)

Select ID from collect where class = 0 and vtype = 5 limit 2000 (8 ms)

Select ID from collect where class = 0 and vtype = 5 (243 ms, 38 thousand records)

The above shows that it is very fast, basically 10 times the above. There is basically no big difference from the number of output records.

Comparison of the above two statements shows that for composite indexes,

1)>, = will indeed go through the index, making simple limit queries faster;

2) for big data output,> very time-consuming, especially for count, when the data volume is large, the comparison symbol (<,>) will be stuck directly.

3) = number is the fastest. Whether it is count or big data output, the speed is acceptable. In normal programming, use the = operator as much as possible to process composite indexes.

But for a single index, there is no impact, No matter>, = the speed is very fast. The count function is not affected either.

 

 

 

 

1. Download sqlite3 from the website, decompress it, and put it in D: \ SQLite. There are three files: sqlite3.def, sqlite3.dll, and sqlite3.exe.

1). sqlite3.def: used to compile and generate the corresponding lib File

2). sqlite3.dll: Provides the dynamic link library file required by SQLite.

32.16.sqlit3e.exe: Execution file used to perform database operations

2. Example:

1 ). Open a cmd program, enter the SQLite directory, and execute:

D: \ SQLite> sqlite3.exe my. DB --> Create a database file my. DB

Sqlite3>. Help -->

Sqlite3>. Width [column1_max_width] [column2_max_width] ......

Sqlite3>. Output [filename] ---> output the result to the special file

Sqlite3> .. tables, you can view all tables in the database;
Sqlite3>. Indices, which can list all indexes of a specified table;
Sqlite3>. Schema: The create statement used to create tables and indexes. You can also specify the table name later;
Sqlite3>. databases, to view all the currently opened databases, usually a main TEM

Sqlite3>. Dump; the exported SQL statement can be executed in Oracle;

Sqlite3>. Explain: queries the execution plan finally parsed by an SQL statement, which is useful for SQL statement optimization;

Sqlite3>. Timeout: the default timeout value is 0. When a table or index is queried and the table or index is locked, the system returns immediately without waiting;

3. limitations:

1) SQLite is only applicableConcurrent read operations on a single file are not suitable for concurrent write transactions.The limitations of transactions restrict their applications and are usually used to process small transactions.

2) foreign key constraints are not supported;
3) incomplete support for triggers;
4) The support for alter table is incomplete;
5) embedded transactions are not supported;
6) Right and full outer join are not supported.
7) Write update operations on views are not supported;
8) Grant and revoke are not supported;

9) stored procedures are not supported.

10) filelock: SQLite controls the concurrency of transactions through low-level primitives of local filelock. If some data remains in the network partition, The filelock fails.