macro:1.
Cache。 Caching is done on top of the persistence layer or persistence layer. The data queried from the database is first placed in the cache, and the next time the query is queried, the cache is first visited. The database is queried if a miss is missed.
2.
table partitioning and splitting.Whether it is a split on business logic or a partition with no business meaning. 3.
increase disk speed.This includes the processing of RAID and other disk file fragments. The basic idea is to increase the concurrency of the disk (multiple physical disks hold the same file).
Micro:table Design Aspects:1.
field redundancy .Reduce cross-Library queries and large table join operations.
2.Large segment stripping of database tables.Ensure that the amount of data in a single record is very small.
3.
Use the index appropriately ,Even a multilevel index.
Query optimization Aspects:
2. Avoid null-value inference of the field in the WHERE clause, otherwise it will cause the engine to abandon using the index for full table scanningSuch as:
Select ID from t where num is null
The ability to set the default value of 0 on NUM ensures that the NUM column in the table does not have a null value. Then this query:
Select ID from t where num=0
3. You should try to avoid using the! = or <> operator in the WHERE clause. Otherwise, the engine discards the use of the index for a full table scan.
4. Try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon using the index for full table scanSuch as:
The select ID from t where num=10 or num=20 can be queried like this:
Select ID from t where num=10 union ALL select IDs from T where num=20
5.in and not in should also be used with caution, otherwise it will result in a full table scan. Such as:
Select ID from t where num in
For consecutive values, you can use between instead of in:
Select ID from t where num between 1 and 3
6. The following query will also cause a full table scan:
Select ID from t where name is like '%abc% ' to improve efficiency, you can consider full-text indexing.
7. The index is not the more the better, the index can improve the efficiency of the corresponding select, but also reduces the efficiency of insert and update at the same time, because it is possible to rebuild the index at INSERT or update. Therefore, how to build an index requires careful consideration, depending on the details of the situation. The number of indexes on a table should not be more than 6. If too many, consider whether some of the indexes that are not commonly used are necessary.
8. Use the numeric field as much as possibleA field that contains only numeric information should not be designed as a character type, which reduces the performance of queries and connections and adds storage overhead. This is due to the fact that the engine handles queries and connections one at a time in each character in the string, whereas for a digital type it only needs to be done more than once.
9. Do not use SELECT * from t anywhere, replace "*" with a detailed field list, and do not return any fields that are not available.
10. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.
How to optimize database query
