Some experiences gained from the database optimization experience compiled by our predecessors

Occasionally, we will see articles on Database optimization compiled by our predecessors, some of which are easy to understand. The following article requires us to contact B tree, the hash structure can make sense in theory. Of course, sometimes our most reliable approach is practice. However, if we can understand it theoretically, is it better ..

First, we know that the database index can greatly improve the search speed. In this process, it depends not only on whether you have correctly applied the index, but also on the index creation policy. Some fields cannot be indexed or the index is useless. When will index creation be counterproductive?

Assume that we have indexed the field "gender", with thousands of users. At this time, the system indexes a large number of keys correspond to a large number of identical values. Currently, the database mainly uses the B tree and hash structure to organize data, A one-way linked list is formed between the leaf nodes under each intermediate node of the tree. The node in this single-chain table contains the gender information of each user. By scanning the linked list, you can find the node. Similarly, in a hash table, a mechanism (hash function) is used to associate a storage address. however, in the hypothetical scenario, there are generally only two values for the gender field. At best, an "unknown" field is added, which is only three values. Tens of thousands of hash functions are virtually identical. As a result, a large number of hash return value (Address) conflicts are formed, and the hash values are linked in a linked list, the header node may still be hashed out. In this way, you have to find the specific gender of a user on this ultra-long connection table.

As we can see from the above, if we create an index blindly, it will not only waste space, increase the workload, but also not improve the query speed ..

Here, I would like to ask you: is there or how many SQLite databases are used in small websites? This light-weight database, according to the Forum, has at least better performance than access and supports transactions. What problems will be encountered when using SQLite for websites with low costs and a small amount of data?