1. Explain the execution plan after writing SQL (SQL performance optimization)
Daily development write SQL, try to develop this good habit ah: after writing SQL, with explain analysis, especially pay attention to not go index.
2. Alter table delete or update statement add limit
When executing a delete or update statement, add a limit as much as possible
delete from euser where age > 30 limit 200;
Because limit has these main benefits:
“Reduce the cost of error writing SQL”, if you do not use the command line to execute this SQL, a “hand shaking”, may delete all data, if “delete error”? If you add limit 200, it’s different. Error deletion only loses 200 pieces of data, which can be quickly recovered by using binlog.
“SQL is probably more efficient”, you add limit 1 to the SQL line, if the first one hits the target return, if there is no limit, it will continue to scan the table.
“Avoid long transactions“. When delete is executed, if age is indexed, MySQL will add write locks and gap locks to all rows related to the execution, and all rows related to the execution will be locked. If the number of deleted rows is large, related services will be directly affected.
“With a large amount of data, it’s easy to fill the CPU.If you delete a large amount of data, do not add a limit to limit the number of records, easy to fill up the CPU, resulting in a slower deletion.
3. Annotate all tables and fields when designing a table (elegant SQL specification)
This is a good habit to develop. When designing a database table, annotate all tables and fields to make it easier to maintain later.
4.Write in SQL format with the same size of keywords and indentation. (SQL specification elegant) the following example:
SELECT stu.name, sum(stu.score)
FROM Student stu
WHERE stu.classNo = 'class 1'
GROUP BY stu.name
5. INSERT statement indicates the corresponding field name (SQL specification elegant)
6. Modify SQL operations in the test environment first, specify detailed operation steps and rollback scheme, and review it before production. (SQL regret medicine)
- SQL changes are first tested in the test environment to avoid syntax errors in production.
- Change Sql operations need to specify detailed operation steps, especially if there is a dependency, for example: first modify table structure and then add corresponding data.
- There is a rollback scheme for changing Sql operations, and review the corresponding changed Sql before production.
I hope the content of this article can bring some help to your study or work. Make progress a little bit every day. Come on!
