Shulou(Shulou.com)05/31 Report--

This article mainly explains "What are the good suggestions for writing SQL?" Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "What are the good suggestions for writing SQL"!

1. When executing delete or update statements, try to add limit, which is SQL regret medicine plus limit mainly has these benefits:

Reduce the cost of writing wrong SQL, if you don't add limit, when you execute it, you accidentally shake your hand or get stuck, and the data will probably be deleted. What if you delete the wrong SQL? Add limit 200, everything is different, you can quickly recover through binlog log. SQL efficiency may be higher, you in the SQL line, add limit 1, if the first hit the target return, there is no limit, 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 related rows, and all related rows will be locked. If the number of deletions is large, the related business cannot be used directly. When you delete a large amount of data, add limit to limit the number of records, it is not easy to fill the cpu.

2. Change SQL operations first in the test environment test, can avoid syntax errors put into production. To change SQL operations, you need to specify detailed operation steps, especially when there are dependencies, such as modifying the table structure first and then adding corresponding data.

Replace SQL operations with rollback schemes and review corresponding changes to SQL before production.

3. Fields after where, watch for implicit conversions to their data types, which can optimize performance. Because there is no single quotation mark, it is a comparison of strings and numbers, and the types do not match, MySQL will do implicit type conversion, convert them to floating point numbers and then compare them, and finally cause the index to be invalid.

4. When operating on production data, modify or delete SQL, first write WHERE to check, and then add delete or update after confirmation.

5. Reducing unnecessary field returns can save resources, reduce network overhead, use overlay indexes, reduce table returns, and improve query efficiency. For example, use select instead of select *.

6. The character sets of databases and tables should use UTF8 encoding as much as possible to avoid the problem of garbled characters and the index failure caused by comparison and conversion of different character sets. To store emoticons, select utf8mb4 to store them, noting the difference between utf-8 and utf8.

7. Use varchar instead of char as much as possible. Because the storage space of variable length field is small, the storage space can be saved.

8. Use the distinct keyword with caution; it is generally used to filter duplicate records to return non-duplicate records. It can be used when querying a field or few fields, which can bring optimization effect. However, if you use it when there are many fields, it will reduce query efficiency.

9. SQL command line to modify data, develop the habit of begin + commit transactions, this is also a SQL regret medicine.

10. Index names should be standardized. Primary key index names are pk_field names; unique index names are uk _field names; common index names are idx _field names. For example, pk_stands for primary key;uk_stands for unique key;idx_stands for index.

11. The column function conversion and expression calculation are not performed on the column in the where clause, because the built-in function of mysql is used on the index column, and the index is invalid.

12. SQL statements do not contain too many IN values, if too many values, the consumption will be large. MySQL has done the corresponding optimization for IN, that is, all the constants in IN are stored in an array, and this array is ordered. If you can use between, don't use in, or use join instead.

13. When appropriate, overlay indexes are used. It allows you to SQL statements do not need to go back to the table, only access the index can get all the data you need, greatly improving the query efficiency.

At this point, I believe that everyone has a deeper understanding of "what good suggestions to write SQL", so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.