Preface

Every good habit is a fortune. This article is divided into three directions: SQL regret medicine, SQL performance optimization, and SQL standard elegance. It shares 21 good habits for writing SQL. Thank you for reading. Come on~

1. After writing the SQL, explain and view the execution plan (SQL performance optimization)

When developing and writing SQL on a daily basis, try to develop this good habit: after writing the SQL, use explain to analyze it, paying special attention to whether the index is used.

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 a delete or update statement, try to add a limit. Take the following SQL as an example:

delete from euser where age > 30 limit 200;

Because adding a limit has the following main benefits:

• "Reduce the cost of writing wrong SQL" . When you execute this SQL on the command line, if you don't add a limit, your hands will shake accidentally during execution , and all the data may be deleted. What if it is "deleted by mistake" ? Adding a limit of 200 makes the difference. If you delete the data incorrectly, you will only lose 200 pieces of data, which can be quickly restored through the binlog log.
• "SQL is likely to be more efficient ." If you add limit 1 to the SQL line, if the first line hits the target return, if there is no limit, the scan table will continue to be executed.
• "Avoiding 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 deletes is large, it will directly affect related businesses. not available.
• "If the amount of data is large, it is easy to fill up the CPU ." If you delete a large amount of data and do not add a limit to limit the number of records, it is easy to fill up the CPU, causing the deletion to become slower and slower.

3. When designing tables, add corresponding comments to all tables and fields (SQL specifications are elegant)

This good habit must be developed. When designing database tables, add corresponding comments to all tables and fields, which will make it easier to maintain later.

"Example:"

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

"Counterexample:"

CREATE TABLE `account` (
  `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, keep the keyword size consistent, and use indentation. (SQL specification is elegant)

"Example:"

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

"Counterexample:"

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

Obviously, unifying keywords in the same case and using indent alignment will make your SQL look more elegant~

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

"Counterexample:"

insert into Student values ('666','捡田螺的小男孩','100');

"Example:"

insert into Student(student_id,name,score) values ('666','捡田螺的小男孩','100');

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

• Change the SQL operation and test it in the test environment first to avoid syntax errors before putting it into production.
• Changing the Sql operation requires detailed 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 SQL operations, and the corresponding SQL changes are reviewed before going to production.

7. When designing the 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 '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

"Example:"

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

"reason:"

• A primary key must generally be added. A table without a primary key has no soul.
• As for the creation time and update time, it is recommended to add them. Detailed auditing and tracking records are both useful.

Alibaba development manual also mentions this point, as shown in the figure

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

"Counterexample:"

select * from user 
where address ='深圳' order by age;

"Example:"

添加索引
alter table user add index idx_address_age (address,age)

9. Before modifying or deleting important data, back up first, back up first, back up first (SQL regret medicine)

If you want to modify or delete data, you must first back up the data to be modified before executing SQL. In case of misoperation, you will still have "regret medicine" ~

10. For the fields after where, pay attention to the implicit conversion of their data types (SQL performance optimization)

"Counterexample:"

//userid 是varchar字符串类型
select * from user where userid =123;

"Example:"

select * from user where userid ='123';

"reason:"

• Because when single quotes are not added, the comparison is between strings and numbers. Their types do not match. MySQL will perform implicit type conversion and convert them into floating point numbers before comparison, which will eventually lead to index failure.

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

• "NOT NULL columns are more space-saving ." NULL columns require an extra byte as a flag to determine whether it is NULL.
• "NULL columns need to pay attention to the null pointer problem ." When calculating and comparing NULL columns, you need to pay attention to the null pointer problem.

12. To modify or delete SQL, first write WHERE to check, and then add delete or update after confirmation (SQL regret medicine)

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

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

"Counterexample:"

select * from employee;

"Example:"

select id，name from employee;

reason:

• Save resources and reduce network overhead.
• Covering indexes may be used to reduce table returns and improve query efficiency.

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

Innodb "supports transactions, supports row-level locks, and has better recovery performance" and has better performance under high concurrency, so there are no special requirements (that is, functions that Innodb cannot meet, such as column storage, storage space data, etc.) , all tables must use Innodb storage engine

15. Try to use UTF8 (SQL standard and elegant) for the character sets of databases and tables.

Try to use UTF8 encoding uniformly

• Can avoid garbled code problems
• It can avoid the index failure problem caused by comparison and conversion of different character sets.

"If you need to store expressions, choose utf8mb4 for storage, and pay attention to the difference between it and utf-8 encoding."

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

"Counterexample:"

  `deptName` char(100) DEFAULT NULL COMMENT '部门名称'

"Example:"

`deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'

reason:

• Because the storage space of variable-length fields is small first, storage space can be saved.

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

This point is the specification of Mysql in the Alibaba development manual. If the meaning of your fields, especially when representing enumeration status, is modified, or when the status is appended, you need to update the field comments immediately for better maintenance later.

18. Modify data on the SQL command line and develop the habit of begin + commit transactions (SQL regret medicine)

"Example:"

begin;
update account set balance =1000000
where name ='捡田螺的小男孩';
commit;

"Counterexample:"

update account set balance =1000000
where name ='捡田螺的小男孩';

19. Index naming should be standardized. The primary key index name is pk_field name; the unique index name is uk_field name; the ordinary index name is idx_field name. (SQL specification is elegant)

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

20. Function conversion and expression calculation are not performed on columns in the WHERE clause

Assume that loginTime is indexed

"Counterexample:"

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

"Example:"

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

"reason:"

• Using mysql's built-in function on the index column causes the index to fail.

21. If there is too much data to be modified/updated, consider doing it in batches.

Counterexample:

delete from account  limit 100000;

Positive example:

for each(200次)
{
 delete from account  limit 500;
}

reason:

• Large batch operations will cause master-slave delays.
• Large batch operations will generate large transactions and block.
• Large batch operations and excessive data volume will fill up the CPU.

References and thanks

Spring recruitment has begun. If you are not fully prepared, it will be difficult to find a good job in spring recruitment.

I’m sending you a big employment gift package, so you can raid the spring recruitment and find a good job!