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 made, the cache is accessed and the database is queried if it misses. 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 main 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 small.
3.
Use the index appropriately ,Even a multilevel index.
Query optimization Aspects:
2. Avoid null-valued fields in the WHERE clause, which will cause the engine to discard full table scans using the indexSuch as:
Select ID from t where num is null
You can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:
Select ID from t where num=0
3. Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.
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:
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, or it will cause 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, full-text indexing can be considered.
7. The index is not the more the better, the index can improve the efficiency of the corresponding select, but it also reduces the efficiency of insert and update, because the index may be rebuilt at insert or update, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.
8. Use the numeric field as much as possibleIf a field that contains only numeric information should not be designed as a character type, this can degrade query and connection performance and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.
9. Do not use SELECT * from t anywhere, replace "*" with a specific 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.
