Index use constraints, what SQL optimization

A, select the most applicable fields: When creating a table, we can set the width of the fields in the table to be as small as possible for better performance. Another way to improve efficiency is to set fields to NOTNULL, b whenever possible, use joins instead of sub-queries C, and use unions instead of manually created temporary tables D. Things like: A) Either every statement in the block succeeds, or all statements fail. In other words, the consistency and integrity of the data in the database can be maintained. Things start with the BEGIN keyword and end with the COMMIT keyword. If one of these SQL operations fails, the ROLLBACK command can restore the database to the state it was in before BEGIN. B) When multiple users use the same data source at the same time, it can use the method of locking the database to provide users with a secure access mode, so as to ensure that users' operations will not be interfered by other users. E, reduce table association, add redundant field F, and use foreign keys: locking tables can maintain data integrity, but it does not guarantee data relevance. That's when we can use foreign keys. G, use index H, optimized query statement I, cluster J, read-write split K, master-slave replication L, split table M, split library O, use stored procedure restrictions when appropriate: try to use full-time indexes, left-most prefix: queries start at the left-most front of the index and do not skip columns in the index; No operation on index column, all after the range is invalid; Unequal null values and OR, index influence should be paid attention to; Like Index invalidation starts with wildcard %. Index invalidation occurs when the string is not quotedCopy the code

Data synchronization issues (cache and database), cache optimization

1. Reduce back-end load: For high-consumption SQL: join result sets and group statistics results; These results are cached. 2. Speed up the request response 3. Merge the massive write into the batch write: for example, the counter accumulates the redis first and then writes DB 4 in batches. Expire 5. Proactive updates: Development control lifecycle (final consistency, short intervals) 6. Caching empty objects 7. Bloon filter interception 8. Efficiency of the command itself: for example, SQL optimization, command optimization 9. Network times: reduce communication times 10. Reduce access costs: long connection/connection pool,NIO, etc. 11.IO access merge purpose: To reduce the number of cache reconstruction, data consistency as far as possible, reduce potential risks. 1. Mutex setex,setnx: if set(nx and ex) is true, no other threads rebuild the cache, and the current thread executes the cache build logic. If setnx(nx and ex) is false, another thread is building the cache, and the current thread will rest for a specified amount of time (for example, 50 milliseconds, depending on the speed of building the cache) and then execute the function again until it gets the data. 2 never expires: Hot key is nothing but concurrent special big level to rebuild the cache time is longer, if the direct set expiration time, so time to time, huge amount of visit will be oppression to the database, so the hot key to val increased a logical expiration time fields, concurrent access, whether the logic field of time value is greater than the current time, A value greater than that indicates that the cache needs to be updated, so all threads are still allowed to access the old cache because the cache is not set to expire, but another thread is allowed to refactor the cache. After the refactoring is successful, that is, after the redis set operation is performed, all threads can access the new content in the refactoring cache. From the perspective of the cache, there is no expiration time, so there is no hot key expiration problem, that is, the "physical" does not expire. On a functional level, a logical expiration time is set for each value, and when the logical expiration time is exceeded, a separate thread is used to build the cache. If the cache fails to be deleted, do not update the database. If the cache fails to be deleted, do not update the database. If the cache fails to be deleted, do not update the database. If there is not a request in the queue, then do not put a new operation in the cache. Use a while (true) loop to query the cache for about 200MS, and then send it to the queue again. Then synchronously wait for the cache update to complete.Copy the code