Considerations and points for optimizing MySQL execution efficiency

1. The principle of SQL optimization is to minimize the number of blocks that need to be read for an operation, that is, to achieve maximum data throughput in the shortest possible time. Poorly tuned SQL can usually be cut from the following points:? Check for bad SQL, and consider if there is anything else you can do to optimize it? Check subqueries consider whether SQL subqueries can be re-written in a simple connection? Check the use of optimized indexes? Consider the optimizer of the database 2. Avoid a SELECT * FROM table statement to explicitly isolate the field. 3. In an SQL statement, if a Where condition filters more database records, the more accurate the location, the more the Where condition should be moved forward. 4. When possible, use index overrides when querying. That is, a composite index is created on the field of select, so that the query is indexed only and the data block is not read. 5. It is recommended that you do not use Select COUNT (*) and select top 1 statements When judging if there are no qualifying records. 6. Using the inner qualification principle, when you spell the SQL statement, the query conditions are decomposed, categorized, and as far as possible in the innermost layer of the SQL statement, to reduce the amount of data processing. 7. You should absolutely avoid using an expression in the ORDER BY clause. 8. If you need to read data from the associated table, the associated table generally does not exceed 7. 9. Be careful with in and OR, you need to be aware of the amount of data in the in collection. There are no more than 200 data in the recommended collection. <> Use <, > replace,> with >= instead of,< with <= instead, so that the index can be effectively used. 11. When querying, minimize the reading of redundant data including extra columns and extra rows. 12. Note For composite indexes, such as when the order of the columns is F1,F2,F3 when the composite index is established, the order in which these fields appear in the Where or ORDER BY clause is consistent with the order in which the fields are indexed and must contain the first column. can only be F1 or F1,f2 or f1,f2,f3. Otherwise, the index will not be used. 13. When Multi-table association query, the writing must follow the following principles, it is conducive to the establishment of indexes, improve query efficiency. The format is as follows select SUM (table1.je) from table1 table1, table2 table2, Table3 Table3 where (the equivalent condition of table1 (=)) and (non-equivalent condition of table1) and ( Table2 Conditions of association with Table1) and (equivalent condition of table2) and (table2 non-equivalent condition) and (association condition of Table3 and Table2) and (equivalent condition of table3) and (table3 non-equivalent condition). Note: The effect of the order of the appearance of the back table on the efficiency of multiple table queries remains to be studied. 14. Subquery problem. Do not use subqueries for functions that can be implemented in connection mode or view mode. For example: Select name from the customer where customer_id in (select customer_id from Order where money>1000). The following statement should be used instead: select name from Customer inner join order on customer.customer_id=order.customer_id where order.money>100. 15. In the WHERE clause, avoid arithmetic the column, especially to the left of the Where condition, and do not use operations and functions to handle the column. For example, some places substring can be replaced with like. 16. If the in statement has not the in operation, you should consider rewriting with not exists (exists), the best way is to use an outer join implementation. 17. The processing of a business process, should make the beginning and end of things between the time between the shorter the better, in principle, the database read operation completed in the front, the database write operation is completed in the back, to avoid cross. 18. Be careful not to use column functions and order By,group by for too many columns, and use disti software development T sparingly. 19. With UNION all instead of union, the database performs a union operation by first executing the query at each end of the union, placing it in a temporary table, and then sorting it to filter the duplicate records. When known business logic determines that there are no duplicate records in query A and query B, you should use union all instead of union to improve query efficiency. Efficiency of data Update 1. In one thing, multiple INSERT statements for the same table should be executed together. 2. In a business process, try to make insert,update,delete statements executed before the end of the business to reduce the likelihood of deadlocks. The efficiency of database physical planning in order to avoid I/O conflicts, we should follow a few basic principles in designing the database physical planning (oracle example):?? Table and Index separation: table and index should be placed in separate tablespace. Separation of Rollback segment: Rollback Segment should be placed in a separate tablespace. Separation of System Tablespace: Object in System tablespace is not allowed to be placed on any user. (primary filegroup in MSSQL is not allowed to place any user's object)?? Temp Tablesace separation: Create a separate temp tablespace and specify the default temp tablespace for each user?? Avoid fragmentation: But when there is a lot of fragmentation in the segment, the number of blocks that need to be accessed when reading the data increases. Fragmentation is not completely avoidable for segemeng that often occur with DML operations. Therefore, we should separate the tables that often do DML operations and those that rarely change in different tablespace. When we follow the above principles, we still find that I/O conflicts exist, we can use data separation method to solve. Connection table separation: In the actual application of the table is often made connection query, you can separate it in different taclespace, in order to reduce I/O conflicts. Use partitioning: Use partitions for table and index with large amounts of data, placed in different tablespace. In the actual physical storage, RAID is recommended. The log file should be placed on a separate disk.

Considerations and points for optimizing MySQL execution efficiency