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

This article mainly explains "what are the good habits of writing SQL in MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what are the good habits of writing SQL in MySQL.

1. Explain to view the execution plan after writing SQL (SQL performance optimization)

When writing SQL in daily development, try to form this good habit: after writing SQL, use explain to analyze it, especially pay attention to whether to take the index.

Explain select userid,name,age from user where userid = 10086 or age = 18

2. Operate the delete or update statement and add a limit (SQL regret medicine)

When executing delete or update statements, add limit as much as possible. Take the following SQL as an example:

Delete from euser where age > 30 limit 200

Because adding limit has the following main benefits:

"reduce the cost of misspelling SQL", when you execute this SQL on the command line, if you do not add limit, when you execute a "accidental hand shake", the data may be deleted. What if you "delete it wrong"? With the addition of limit 200, it will be different. Deletion errors only lose 200 pieces of data, which can be quickly recovered through binlog logs.

"SQL is likely to be more efficient." you add limit 1 to the SQL line, and if the first line hits the target return, without limit, you 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 execution-related rows will be locked. If the number of deletions is large, it will directly affect the unavailability of the related business.

"if you have a large amount of data, it is easy to fill up the CPU." if you delete a large amount of data, do not add limit to limit the number of records, it is easy to fill up the cpu, resulting in slower and slower deletions.

3. When designing tables, all tables and fields are annotated accordingly (SQL specification is elegant)

This good habit must be formed. When designing database tables, all tables and fields are annotated accordingly, which is easier to maintain later.

"positive example:"

CREATE TABLE `account` (`id`varchar (11) NOT NULL AUTO_INCREMENT COMMENT 'key Id', `name` varchar' account name', `balance` int (11) DEFAULT NULL COMMENT 'balance', `create_ datetime NOT NULL COMMENT 'creation time', `update_ time`datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time', PRIMARY KEY (`id`), KEY `idx_ name` (`name`) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT=' account table'

"counterexample:"

CREATE TABLE `Secrett` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (255) DEFAULT NULL, `balance` int (11) DEFAULT NULL, `create_ time` datetime NOT NULL, `update_ time`datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_ name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8

4. SQL writing format, keyword size is consistent, using indentation. (SQL specification is elegant)

"positive example:"

SELECT stu.name, sum (stu.score) FROM Student stu WHERE stu.classNo = 'Class 1' GROUP BY stu.name

"counterexample:"

SELECT stu.name, sum (stu.score) from Student stu WHERE stu.classNo = 'Class 1' group by stu.name.

Obviously, uniform keywords are case consistent, and indentation alignment will make your SQL look more elegant.

5. INSERT statement indicates the corresponding field name (SQL specification is elegant)

"counterexample:"

Insert into Student values ('666' little boy picking up field snails', '100')

"positive example:"

Insert into Student (student_id,name,score) values ('666 million girls' little boy picking up snails', '100')

6. Change the SQL operation first in the test environment, write down the detailed operation steps and rollback plan, and review before production. (SQL regret medicine)

Change the SQL operation to test in the test environment first, to avoid syntax errors and put it on production.

To change the Sql operation, you need to specify the detailed operation steps, especially when there are dependencies, such as modifying the table structure first and then adding the corresponding data.

There is a rollback plan for changing the Sql operation, and before the production, the review changes the SQL accordingly.

7. When designing a database table, add three fields: primary key, create_time,update_time. (SQL specification is elegant)

"counterexample:"

CREATE TABLE `account` (`name` varchar (255) DEFAULT NULL COMMENT 'account name', `balance` int (11) DEFAULT NULL COMMENT 'balance',) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT=' account form'

"positive example:"

CREATE TABLE `account` (`id`varchar (11) NOT NULL AUTO_INCREMENT COMMENT 'key Id', `name` varchar' account name', `balance` int (11) DEFAULT NULL COMMENT 'balance', `create_ datetime NOT NULL COMMENT 'creation time', `update_ time`datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time', PRIMARY KEY (`id`), KEY `idx_ name` (`name`) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT=' account table'

"reason:"

The primary key is usually added. A table without a primary key has no soul.

If the creation time and update time, it is recommended to add it, detailed audit, tracking records, are useful.

This point is also mentioned in the Ali development manual, as shown in the figure

8. After writing the SQL statement, check the columns after where,order by,group by, whether the columns associated with multiple tables have been indexed, and give priority to the combined index. (SQL performance optimization)

"counterexample:"

Select * from user where address = 'Shenzhen' order by age

"positive example:"

Add index alter table user add index idx_address_age (address,age)

9. Back up before modifying or deleting important data (SQL regret medicine)

If you want to modify or delete data, be sure to back up the modified data before performing SQL. In case of misoperation, you can still eat "regret medicine" ~

10. Fields after where, pay attention to implicit conversions of their data types (SQL performance optimization)

"counterexample:"

/ / userid is the varchar string type select * from user where userid = 123

"positive example:"

Select * from user where userid = '123'

"reason:"

Because when not adding single quotation marks, it is a comparison between strings and numbers, and their types do not match. MySQL will do implicit type conversion, convert them to floating-point numbers and then compare them, resulting in index invalidation.

11. Try to define all columns as NOT NULL (SQL specification elegant)

The NOT NULL column saves more space, and the NULL column requires an extra byte as the flag bit to determine whether it is NULL or not.

"the NULL column needs to pay attention to the null pointer problem." the NULL column needs to pay attention to the null pointer problem when calculating and comparing.

twelve。 Modify or delete SQL, first write WHERE to check, confirm and then add delete or update (SQL regret medicine)

Especially when operating the production data, if you encounter a modified or deleted SQL, add a where query first, confirm the OK, and then perform the update or delete operation.

13. Reduce unnecessary field returns, such as using select instead of select * (SQL performance optimization)

"counterexample:"

Select * from employee

"positive example:"

Select id,name from employee

Reason:

Save resources and reduce network overhead.

May be used to overwrite the index, reduce back to the table, and improve the efficiency of the query.

14. All tables must use the Innodb storage engine (SQL specification is elegant)

Innodb "supports transactions, row-level locks, and better recoverability", and performance is better at high concurrency. Therefore, without special requirements (that is, functions that Innodb cannot meet, such as column storage, storage space data, etc.), all tables must use the Innodb storage engine.

15. Try to unify the character sets of databases and tables using UTF8 (SQL specification is elegant)

Try to unify the use of UTF8 coding

The problem of garbled code can be avoided

The problem of index failure caused by the comparison of different character sets can be avoided.

"if you need to store facial expressions, choose utf8mb4 to store them, and note the difference between it and utf-8 coding. "

16. Try to use varchar instead of char. (SQL performance optimization)

"counterexample:"

`deptName` char (100) DEFAULT NULL COMMENT 'department name'

"positive example:"

`deptName` varchar (100) DEFAULT NULL COMMENT 'department name'

Reason:

Because the storage space of the long field is small at first, the storage space can be saved.

17. If you modify the meaning of the field or append the status represented by the field, you need to update the field comments in a timely manner. (SQL specification is elegant)

This point is the specification of Mysql in Ali's development manual. If the meaning of your field, especially when representing the enumerated state, is modified, or when the state is appended, you need to update the comments of the field immediately for better maintenance later.

18. The SQL command line modifies the data to form the habit of begin + commit transactions (SQL regret medicine)

"positive example:"

Begin; update account set balance = 1000000 where name = 'little boy picking up field snails'; commit

"counterexample:"

Update account set balance = 1000000 where name = 'little boy picking up field snails'

19. The index name should be standardized: the primary key index name is the pk_ field name; the unique index name is the uk _ field name; and the normal index name is the idx _ field name. (SQL specification is elegant)

Description: pk_ is primary key;uk_ that is unique key;idx_ is the abbreviation of index.

20. Column function conversion and expression evaluation are not performed in WHERE clauses

Suppose loginTime adds an index

"counterexample:"

Select userId,loginTime from loginuser where Date_ADD (loginTime,Interval 7 DAY) > = now ()

"positive example:"

Explain select userId,loginTime from loginuser where loginTime > = Date_ADD (NOW (), INTERVAL-7 DAY)

"reason:"

Index invalidation due to the use of mysql's built-in function on the index column

21. If you modify / update too much data, consider doing it in batches.

Counterexample:

Delete from account limit 100000

Positive example:

For each (200times) {delete from account limit 500;}

Reason:

A large number of operations will cause master-slave delay.

A large number of operations will result in large transactions and blocking.

If you operate in large quantities, the amount of data is too large, it will fill up the cpu.

Thank you for your reading, the above is "what are the good habits of writing SQL in MySQL?" after the study of this article, I believe you have a deeper understanding of what the good habit of writing SQL in MySQL has, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.