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

This article introduces the knowledge of "good habits to be formed in writing SQL in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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 * 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 don't add limit, if you accidentally shake when you execute it, all the data may be deleted. What if you delete it incorrectly? 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.

Long transactions are avoided. 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 related businesses.

If the amount of data is large, it is easy to fill up the CPU. If you delete a large amount of data, do not limit the number of records with limit, 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 must be 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 data you want to modify 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.

The NULL column needs to pay attention to the null pointer problem, and 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, better resilience, and better performance under high concurrency, so 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. Database and table character sets are unified using UTF8 (SQL specification is elegant)

Uniform 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.

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.

Secondly, for queries, searching in a relatively small field is more efficient.

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. SQL 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.

This is the end of the content of "good habits to be formed in writing SQL in MySQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.