Considerations and key points for optimizing mysql execution efficiency

Considerations and key points for optimizing mysql execution efficiency

1. The principle of SQL optimization is to minimize the number of blocks to be read by an operation, that is, to reach the maximum data throughput in the shortest time. You can adjust poor SQL statements from the following points :? Check for poor SQL statements and check whether there is any optimizable content in the statement? Check the subquery to check whether SQL subqueries can be re-written in simple connection mode? Check to optimize index usage? Consider the database optimizer 2. Avoid the SELECT * FROM table statement, and specify the fields to be identified. 3. In an SQL statement, if a where condition filters more database records, the more accurate the location is, the more advanced the where condition should be. 4. Try to overwrite the index when querying. That is, create a composite index for the SELECT field. In this way, only index scanning is performed during the query, and data blocks are not read. 5. We recommend that you do not use the select count (*) and select top 1 statements when determining whether there are any matching records. 6. Use the internal Limitation Principle to break down and classify query conditions when spelling SQL statements, and try to limit them at the innermost layer of SQL statements to reduce the amount of data processed. 7. Avoid using expressions in the order by clause. 8. If you want to read data from an associated table, the number of associated tables should not exceed 7. 9. Be careful when using IN and OR. Pay attention to the data volume In the IN set. We recommend that you set no more than 200 data entries. 10. <> Replace with <,>,> Replace with> =, and <Replace with <=, so that indexes can be effectively used. 11. When querying, minimize the amount of extra data read, including extra columns and extra rows. 12. pay attention to compound indexes. For example, when creating a composite index, the column sequence is F1, F2, F3, in the where or order by clause, these fields must appear in the same order as the fields when the index is created, and must contain the first column. It can only be F1, F1, F2, F1, F2, F3. Otherwise, the index is not used. 13. For multi-table join queries, you must follow the following principles. This helps you to create indexes and improve query efficiency. The format is as follows: select sum (table1.je) from table1 table1, table2 table2, table3 table3 where (equivalent condition of table1 (=) and (non-equivalent condition of table1) and (Association condition of table2 and table1) and (equivalent condition of table2) and (non-equivalent condition of table2) and (Association condition of table3 and table2) and (equivalent condition of table3) and (non-equivalent condition of table 3 ). Note: The effect of the sequence of the tables following the from Statement on the efficiency of Multi-table queries remains to be studied. 14. subquery problems. Do not use subqueries for functions that can be implemented in connection or view mode. For example, select name from customer where customer_id in (select customer_id from order where money> 1000 ). Use the following statement instead: select name from customer inner join order on customer. mermer_id = order. customer_id where order. money> 100. 15. In the WHERE clause, avoid the four arithmetic operations on the column, especially on the left of the where condition. It is strictly prohibited to use arithmetic operations and functions to process the column. For example, in some cases, substring can be replaced by like. 16. If there is a not in (in) operation in the statement, you should consider using not exists (exists) for rewriting. The best way is to use external connections. 17. the shorter the time interval between the beginning and the end of a business process, the better. In principle, the read operation of the database is completed before and the write operation of the database is completed later, avoid crossover. 18. Be careful not to use column functions, order by, group by, etc. for too many columns, and use disti software development with caution. 19. Replace union with union all. When the database executes the union operation, it first executes the queries at both ends of union, puts them in a temporary table, sorts them, and filters Repeated Records. When the known business logic determines that no duplicate records exist in query A and query B, union all should be used instead of union to improve query efficiency. Efficiency of data update 1. In a transaction, multiple insert statements for the same table should be executed together in a centralized manner. 2. In a business process, try to execute the insert, update, and delete statements before the end of the business to reduce the possibility of deadlock. Database physical planning efficiency in order to avoid I/O conflicts, we should follow several basic principles when designing database physical planning (for example, ORACLE ):?? Table and index separation: table and index should be placed in different tablespaces. ?? Rollback Segment separation: Rollback Segment should be placed in an independent Tablespace. ?? Separation of System Tablespace: No user objects can be placed in System Tablespace. (In mssql, primary filegroup does not allow placing any user's object )?? Separation of Temp Tablesace: create a separate Temp Tablespace and specify the default Temp Tablespace ?? Avoid fragments: however, when a large number of fragments appear in a segment, the number of blocks to be accessed during data reading increases. For segemeng, which often suffers DML operations, fragments cannot be completely avoided. Therefore, we should separate tables that frequently perform DML operations from tables that rarely change in different tablespaces. After following the above principles, we still find that I/O conflicts exist. We can use the data separation method to solve them. ?? Join Table separation: in practice, tables that are frequently connected to query can be separated in different taclespaces to reduce I/O conflicts. ?? Partition: partitions are used for tables and indexes with large data volumes and placed in different tablespaces. In actual physical storage, RAID is recommended. Log files should be placed on separate disks.