Shulou(Shulou.com)06/01 Report--

1. Avoid using the double percent sign "% col%" in the MySQL SQL query condition to avoid using "% col%" to use the index in the where condition, and doing a full table scan affects the query efficiency 2. Avoid function appearing in where condition example: where year ('activedate') =' 2018 'use the function in the condition even if the field has an index, the execution plan will not use the index and perform a full table scan 3. When performing query operations to avoid inconsistent data types, use the same value as the field type in the where condition, and try to avoid MySQL performing conversion operations, for example: activedate < STR_TO_DATE ('2018-08-08,'% YMMI% mmi% d') should not be written as activedate < '2018-08-08. Read the appropriate records and required fields to avoid select * from tab_name5. Sorting can be disabled when grouping statistics. MySQL sorts all group by fields by default, and order by null6 can be disabled if the query includes group by and does not require sorting. To reduce the use of subqueries or not, you can use join instead of 7. 5. When creating a table, select the appropriate field properties and make the field not null as much as possible, so that the database does not have to compare the null value 8. 5 when executing the query. Use transactions to maintain data integrity begin; DML statements when performing DML (update, insert, delete) operations; commit;9. Lock table, when doing batch operations using transactions will lock the database, if a large number of user access will cause delay, you can use lock table instead of transaction locktable write select col1,col2 from tab_name.update.; unlocktables;10. The rational use of the index is an effective way to improve the performance of the database, but the creation of the index should be reasonable and avoid being built on fields with a large number of duplicate values. Create reasonable indexes on where, join, order by and other fields to improve performance. The above personal summary is for reference, welcome to correct!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.