Shulou(Shulou.com)06/01 Report--

This article will explain in detail the skills of writing sql in Mysql. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

preface

Every good habit is a fortune, this article is divided into SQL regret medicine, SQL performance optimization, SQL specification elegant three directions, share the 21 good habits of writing SQL, thank you for reading, come on ha ~

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

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

explain select * from user where userid =10086 or age =18; copy code

2, operation delete or update statement, plus a limit(SQL regret medicine)

When executing delete or update statements, try to add limit, take the following SQL as an example:

delete from euser where age > 30 limit 200

Because the limitation has these main benefits:

Reduce the cost of writing wrong SQL, when you execute this SQL on the command line, if you do not add limit, when you execute it, you may accidentally delete all the data. If you delete the wrong one? With a limit of 200, it's different. Error-deletion is only 200 pieces of data lost, which can be quickly recovered through binlog logs. SQL efficiency is likely to 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. All related rows will be locked. If the number of deletions is large, it will directly affect the related business and cannot be used. If the amount of data is large, it is easy to fill the CPU. If you delete a large amount of data, you do not add limit to limit the number of records. It is easy to fill the CPU, resulting in slower deletion. 3. When designing tables, all tables and fields are annotated accordingly (SQL specification elegance)

This good habit must be formed, when designing database tables, all tables and fields are added with corresponding comments, which are easier to maintain later.

Positive example:

CREATE TABLE `account`( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primaryId', `name` varchar(255) DEFAULT NULL COMMENT 'accountname',`balance` int(11) DEFAULT NULL COMMENT ' balance',`create_time` datetime NOT NULL COMMENT 'create time',`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ' update time', PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT ='Account Table'; copy code

Counter example:

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; COPY CODE 4. SQL writing format, keyword size to maintain consistency, use indentation. (SQL specification elegant)

Positive example:

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

Counter example:

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

Obviously, uniform keyword case consistency, using indentation alignment, will make your SQL look more elegant ~

5. INSERT statements indicate the corresponding field names (SQL specification elegance)

Counter example:

insert into Student values ('666 ',' little boy picking field snails ',' 100'); copy code

Positive example:

insert into Student(student_id,name,score) values ('666 ','的小Boy ',' 100'); copy code 6. Change SQL operations are executed in the test environment first, specifying detailed operation steps and rollback schemes, and reviewed before production. (SQL regret medicine) Change SQL operation first in the test environment test, avoid syntax errors put into production. To change Sql operation, you need to specify detailed operation steps, especially when there is a dependency relationship, such as modifying the table structure first and then supplementing the corresponding data. Change SQL operations have a rollback scheme, and before production, review corresponds to change SQL. 7. When designing database tables, add three fields: primary key, create_time,update_time. (SQL specification elegant)

Counter example:

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

Positive example:

CREATE TABLE `account`( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primaryId', `name` varchar(255) DEFAULT NULL COMMENT 'accountname',`balance` int(11) DEFAULT NULL COMMENT ' balance',`create_time` datetime NOT NULL COMMENT 'create time',`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ' update time', PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT ='Account Table'; copy code

Reason:

The primary key must be added. If the table without the primary key has no soul creation time and update time, it is recommended to add it. Detailed auditing and tracking records are useful.

Ali's development manual also mentions this point, 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 composite indexes. (SQL Performance Optimization)

Counter example:

select * from user where address ='Shenzhen ' order by age ; copy code

Positive example:

add index alter table user add index idx_address_age (address,age) copy code

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

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

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

Counter example:

//userid is varchar string type select * from user where userid =123; copy code

Positive example:

select * from user where userid ='123'; copy code

Reason:

Because without single quotes, it is a comparison of strings and numbers, their types do not match, MySQL will do implicit type conversion, convert them to floating point numbers and then compare them, finally causing index invalidation 11. Try to define all columns as NOT NULL (SQL specification elegance) NOT NULL columns save space, NULL columns need an extra byte as a flag bit to determine whether it is NULL. NULL column needs to pay attention to the null pointer problem. NULL column needs to pay attention to the null pointer problem when calculating and comparing. 12. Modify or delete SQL, first write WHERE to check, confirm and then add delete or update (SQL regret medicine)

Especially when operating the produced data, if you encounter SQL for modification or deletion, add a where query first. After confirming OK, perform update or delete operation.

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

Counter example:

select * from employee; copy code

Positive example:

select id, name from employee; copy code

Reason:

Save resources and reduce network overhead. May use overlay index, reduce back table, improve query efficiency. All tables must use Innodb storage engine (SQL specification elegant)

Innodb supports transactions, row-level locking, better recoverability, and better performance under high concurrency. Therefore, all tables must use Innodb storage engine if there are no special requirements (i.e., functions that Innodb cannot meet, such as column storage, storage space data, etc.).

15. Character sets for databases and tables are unified using UTF8 (SQL specification elegance)

Uniform use of UTF8 encoding

Can avoid garbled code problem can avoid, different character set comparison conversion, resulting in index failure problem

If it is stored emoji, consider utf8mb4

16. Use varchar instead of char as much as possible. (SQL Performance Optimization)

Counter example:

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

Positive example:

`deptName` varchar(100) DEFAULT NULL COMMENT 'dept name' copy code

Reason:

Because the storage space of variable length field is small, the storage space can be saved. Second, for queries, it is more efficient to search within a relatively small field. 17. If you modify the meaning of a field or append the status indicated by the field, you need to update the field comments in time. (SQL specification elegant)

This point is the Ali development manual, Mysql specification. If your field, especially if it represents enumeration status, has its meaning modified, or if the status is appended, you need to update the field comments immediately for better maintenance later.

18. SQL modify data, develop the habit of begin + commit transactions;(SQL regret medicine)

Positive example:

begin;update account set balance =1000000where name ='little boy who picked up field snails';commit; copy code

Counter example:

update account set balance =1000000where name ='Little boy picking up field snails'; copy code 19. Index names should be standardized. The primary key index name is pk_field name; the unique index name is uk _field name; and the common index name is idx _field name. (SQL specification elegant)

Description: pk_is primary key;uk _is unique key;idx _is short for index.

20. Where clause does not perform function transformation and expression calculation on columns

Assuming loginTime is indexed

Counter example:

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

Positive example:

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

Reason:

Use mysql built-in function on index column, index invalid

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

Counter example:

delete from account limit 100000; copy code

Positive example:

for each(200 times){ delete from account limit 500;} copy code

Reason:

A large number of operations will cause master-slave delays. Bulk operations generate large transactions, blocking. Mass operation, data volume is too large, will hit the cpu full. The skills of writing sql in Mysql are shared here. I hope the above content can be of some help to everyone and learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.