1、Common optimization solutions for databases
- To optimize the query, avoid full table scanning as far as possible.
- Consider establishing indexes on columns that are related to where and order by.
2、Causes the engine to discard the index and scan the entire table.
- Null values are evaluated in the where clause.
- Use in the where clause = = or < > operator.
- Use the or to connect the condition in the where clause, modify the plan: use union to join the query.
- inAnd not in should also be used with caution, modifying the scheme: for continuous values, using between instead of in, using exists instead of in is a good choice
- In the where clause, the expression is performed on the field.
- Function operations on fields in the where clause
- Like syntax with leading fuzzy queries.Backward guided fuzzy queries will be indexed.
The database locking mechanism is simply a rule designed by the database in order to ensure the consistency of the data and make all kinds of shared resources in order to be accessed concurrently.
MySQLEach storage engine uses three types of locking mechanisms: table level locking, row level locking, and page level locking.MySQLThe characteristics of these 3 locks can be summarized as follows:
- Table level locking: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict, the lowest degree of concurrency;
- Row-level locking: high overhead, slow locking; deadlock occurs; lock granularity is the smallest, the probability of lock collision is the lowest, and concurrency is the highest;
- Page level locking: Overhead and lock time are bounded between table locks and row locks; deadlocks occur; lock granularity is bounded between table locks and row locks, and concurrency is moderate.
- Suitable: From the point of view of lock, table-level lock is more suitable for query-based applications, only a small number of data updates according to index conditions, such as Web applications; row-level lock is more suitable for a large number of Concurrent update data according to index conditions, while there are concurrent query applications, such as some online transaction processing (OLTP) systems.Unification.
4、MySQLPessimistic lock and optimistic lock
Pessimistic locking and optimistic locking are two common resource concurrent locking design ideas, but also a very basic concept in concurrent programming.
Pessimistic locks are characterized by acquiring locks first, and then performing business operations, that is, pessimistic thinking that acquiring locks is very likely to fail, so it is necessary to ensure that the acquisition locks succeed before the business operations. Generally speaking, “one lock, two checks, three updates” means the use of pessimistic locks. Usually, the number of pessimistic locks on a database is required.The library itself provides support, that is, through the commonly used select… For update operation to implement pessimistic lock. When the database executes select for update, it gets the row lock of the data row in select, so other concurrently executed selecsT for update If you try to select the same row, exclusion occurs (you need to wait for the row lock to be released), thus achieving lock effect. Select for update gets the row lock that is automatically released at the end of the current transaction, so it must be used in the transaction.mysqlAnother problem is that all scanned rows in the select for UPDATE statement execution are locked, which can easily cause problems. Therefore, if the pessimistic lock is necessary in mysql, the index must be determined rather than the full table scan.
The characteristic of optimistic lock is to perform business operation first, and not to lock it up. That is, “optimistic” that the lock is likely to be successful, so after the business operation needs to actually update the last step of the data to take the lock again. Whether an optimistic lock is in a transaction is indifferent. Its underlying mechanism isNo concurrency is allowed when updates are on the same line within the database, that is, the database gets the write lock on the update line every time an update statement is executed, and it is not released until the line is successfully updated.Therefore, the current version number of the data that needs to be locked is obtained before the business operation, and then when the data is actually updated, the current version number is confirmed to be the same as the previous version number, and the version number is updated to confirm that no concurrent changes have occurred between them. If the update fails, the old version of the data has been modified.It does not exist, and at this point it is considered that the acquisition lock failed, requiring a rollback of the entire business operation and retrying the entire process as needed.
- Optimistic locking avoids database locking overhead in long transactions and greatly improves the overall performance of the system under a large number of concurrencies.
- Optimistic locks cost less than pessimistic locks in the absence of lock fetch failures, but rollback costs more in the event of a failure, so they are suitable for scenarios where the probability of lock fetch failures is small and can improve system concurrency performance.
- Optimistic locks can also be applied to more specific scenarios, such as where pessimistic locks do not work, such as failing to connect to the database during business operations.
- In a real production environment, pessimistic locking can be used to solve concurrency problems if the concurrency is small and dirty reads are not allowed; but if the concurrency of the system is very large, pessimistic locking can cause very large performance problems, so we have to choose an optimistic locking method.