preface
Every good habit is a treasure. In this article, I will share 21 good habits of writing SQL from three directions: Regret, performance optimization, and refinement of SQL specification
Github address, thanks to each star
Github.com/whx123/Java…
Public number: a boy picking up snails
1. Explain the execution plan after writing SQL (SQL performance optimization)
Daily development write SQL, try to develop this good habit ah: after writing SQL, with explain analysis, especially pay attention to not go index.
explain select * from user where userid =10086 or age =18;
Copy the code
Alter table delete or update statement add limit
When executing a delete or update statement, add a limit as far as possible.
delete from euser where age > 30 limit 200;
Copy the codeBecause limit has these main benefits:
- Reduce the cost of writing errors in SQL. If you do not add limit to SQL, you may delete all data. What if you delete errors? If you add limit 200, it’s different. Error deletion only loses 200 pieces of data, which can be quickly recovered by using binlog.
- SQL is probably more efficient, you put limit 1 in the SQL line, if the first one hits the target return, if there is no limit, you will continue to scan the table.
- Long transactions are avoided. If age is indexed during delete execution, MySQL will add write locks and gap locks to all rows related to the execution, and all rows related to the execution will be locked. If the number of deletes is large, related services will be directly affected.
- If there is a large amount of data, the CPU will be filled up easily. If you delete a large amount of data, do not limit the number of records, the CPU will be filled up easily, resulting in slower deletion.
3. Annotate all tables and fields when designing a table (elegant SQL specification)
This is a good habit to develop. When designing a database table, annotate all tables and fields to make it easier to maintain later.
Is:
CREATE TABLE 'account' (' id 'int(11) NOT NULL AUTO_INCREMENT COMMENT' id ', 'name' varchar(255) DEFAULT NULL COMMENT 'balance ',' 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=' table ';Copy the codeExample:
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 the code4. Write in SQL format with the same size of keywords and indentation. (SQL specification elegant)
Is:
SELECT stu.name, sum(stu.score) FROM Student WHERE stu.classNo = '1 'GROUP BY stu.nameCopy the codeExample:
SELECT stu.name, sum(stu.score) from Student WHERE stu.classNo = '1 'group by stu.nameCopy the codeObviously, uniform keyword capitalization and use indentation alignment will make your SQL look more elegant
5. INSERT statement indicates the corresponding field name (SQL specification elegant)
Example:
Insert into sc values ('666',' 100');Copy the codeIs:
Insert into score (student_id,name,score) values ('666',' 100');Copy the code6. Modify SQL operations in the test environment first, specify detailed operation steps and rollback scheme, and review it before production. (SQL regret medicine)
- SQL changes are first tested in the test environment to avoid syntax errors in production.
- Change Sql operations need to specify detailed operation steps, especially if there is a dependency, for example: first modify table structure and then add corresponding data.
- There is a rollback scheme for changing Sql operations, and review the corresponding changed Sql before production.
SQL > select primary key, create_time,update_time; (SQL specification elegant)
Example:
CREATE TABLE 'account' (' name 'varchar(255) DEFAULT NULL COMMENT' account ', 'balance' int(11) DEFAULT NULL COMMENT 'balance ', ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET= UTf8 ROW_FORMAT=REDUNDANT COMMENT=' mysql ';Copy the codeIs:
CREATE TABLE 'account' (' id 'int(11) NOT NULL AUTO_INCREMENT COMMENT' id ', 'name' varchar(255) DEFAULT NULL COMMENT 'balance ',' 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=' table ';Copy the codeReason:
- A primary key must be added, because a table without a primary key is soulless
- Create time and update time, or suggest to add it, detailed audit, tracking records, are useful.
Ali development manual also mentions this point, as shown in the picture
SQL > select * from ‘where’, ‘ORDER BY’, ‘group BY’, ‘select * from’ where ‘, ‘order by’, ‘group BY’, ‘select * from’ where ‘, ‘order by’, ‘group BY’, ‘select * from’ where ‘. (SQL performance optimization)
Example:
Select * from user where address =' shenzhen 'order by age;Copy the code
Is:
Alter table user add index idx_address_age (address,age)Copy the code
9. Back up, back up, back up important data before modifying or deleting it.
If you want to modify or delete data, be sure to back up the data to be modified before executing SQL, in case of misoperation, you can also regret
Mysql > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL
Example:
Select * from user where userid =123;Copy the code
Is:
select * from user where userid ='123';
Copy the code
Reason:
- MySQL will perform an implicit type conversion to convert the string to a floating point number for comparison, which will invalidate the index
11. Try to define all columns as NOT NULL (SQL specification elegant)
- The NOT NULL column is more space-efficient and requires an extra byte as a flag bit to determine whether it is NULL.
- NULL columns need to be aware of NULL Pointers. NULL columns need to be aware of NULL Pointers when they are evaluated and compared.
SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > delete
In particular, when operating production data, if you encounter a MODIFIED or deleted SQL, first add a WHERE query, confirm OK, and then perform the update or delete operation
13. Reduce unnecessary field returns, such as using SELECT < specific field > instead of SELECT * (SQL performance optimization)
Example:
select * from employee;
Copy the codeIs:
Select id, name from employee;Copy the codeReason:
- Save resources and reduce network overhead.
- Overwrite indexes may be used to reduce table returns and improve query efficiency.
14. All tables must use Innodb storage engine (SQL specification elegant)
Innodb supports transactions, row-level locking, better recovery, better performance with high concurrency, so all tables must use Innodb storage engine if there are no special requirements (i.e. Innodb can’t meet the features such as column storage, storage space data, etc.)
15. Use UTF8 for database and table character sets
UTF8 is used for encoding
- Garbled characters can be avoided
- It can avoid index invalidation caused by different character set comparison conversions
If you store emoticons, consider UTF8MB4
16. Use varchar instead of char whenever possible. (SQL performance optimization)
Example:
'deptName' char(100) DEFAULT NULL COMMENT '表 名'Copy the codeIs:
'deptName' varchar(100) DEFAULT NULL COMMENT 'deptName'Copy the codeReason:
- Because variable-length fields have less storage space in the first place, storage space can be saved.
17. If the meaning of a field is changed or the status of a field is appended, update the field comment in a timely manner. (SQL specification elegant)
This point is the Mysql specification in the Ali development manual. Your fields, especially when they represent enumeration states, need to update their comments immediately for better maintenance if their meaning is changed or if the state is appended.
18. SQL modify data, form the habit of begin + commit transaction; (SQL regret medicine)
Is:
begin; Update account set balance =1000000 WHERE name =' 1 '; commit;Copy the codeExample:
Update account set balance =1000000 WHERE name =' 1 ';Copy the code19. Index name should be standard, primary key index name pk_ field name; Unique index name UK _ field name; The common index name is the idx _ field name. (SQL specification elegant)
Pk_ is the primary key; UK _ stands for unique key; Idx _ is short for index.
20. WHERE clause does not perform function conversions and expressions on columns
Suppose loginTime is indexed
Example:
select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();
Copy the codeIs:
explain select userId,loginTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);
Copy the codeReason:
- Index column using mysql built-in function, index invalid
21. If there is too much change/update data, consider doing it in batches.
Example:
delete from account limit 100000;
Copy the codeIs:
For each(200 times) {delete from account limit 500; }Copy the codeReason:
- Large volume operations can cause master-slave delays.
- Large volume operations generate large transactions and block.
- If a large amount of data is used, the CPU will be fully loaded.
Reference and thanks
- Is it a good habit to add limit after delete
- Ali Development Manual
The public,
Back end technology stack public number: the little boy picking up snails