The incremental increase in business logic data and the incremental increase in the number of users in an organization creates a large number of problems of excessive database data. The default index tables are present in the database. A database has an index repository and a DATA database. The index library stores index tables that point to the data storage area. the Java-adapted MySQL database provides an index table mechanism for every data record table by default. The data index of the database table by default will look for the index table and then go to the data record table to find the data.
The database's leftmost matching principle defaults to matching query data through alphabetical sorting, and the leftmost matching principle is ordered queries. The amount of data is too large to produce a large number of slow query statements. The technical term back table query is the index of the data record table back to the data index table. A large number of slow query operations on the data record table will lose the database data query performance. the SQL is written as much as possible using indexed library queries. Indexed bank queries are small table query operations that consume less query data performance time. Index failure is a query performance problem caused by like fuzzy matching. like query is a full table scanning operation, and full table scanning will result in a long query time when the amount of data associated with the full table scan is too large.
An enhanced version of the binary tree index, B+Tree, implements tree-structured storage for indexes. Tree structure storage database indexes than linear array storage performance is better. Indexes are similar to pointers, joint indexes are indexed objects, which are combined by many indexes. Database index table management operations are very complex. The size of the index library index library can not be too large, too large also need to do distributed processing. the process of writing SQL involves subqueries can not be nested too much, subqueries will be in the running memory to the data output object to allocate memory space. Nested allocation of memory is not conducive to the release of memory space.
Table-to-table associations are established through foreign keys and primary keys of the primary table. Relational databases have too many constraints are less flexible. The difference between structured and unstructured databases is the normalized storage of data. Structured data storage is more memory-intensive, and a data table is tagged with a certain amount of memory space whether or not data exists in it. Unstructured databases are non-relational databases, non-relational databases use api to manipulate the data.Memochache and redis are non-relational databases, database data records exist, there will be a data structure to allocate the storage, there is no data fields will not be structured storage.
Try to use simple data in the data output data area of SQL. Aggregate function in SQL output data area will generate data output collection, similar to complex data object is not conducive to data retrieval. Aggregate function output data will generate index failure, need to re-index the output data.SQL's conditional filter is null will not scan the index table. Save memory space in the data index table. Conditional or keyword is used as little as possible and is also not associated with the data index table. The small table of the data table is similar to the index table, and the large table is similar to the database record table. The small table drives the big table, which can improve the data query performance.