21 MySQL standardization and optimization best practices!

Preface

Every good habit is a treasure. This article is divided into three directions: SQL regret medicine, SQL performance optimization, and SQL standard elegance. It shares 21 good habits and best practices for writing SQL!

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

When writing SQL in daily development, try to develop this good habit: after writing the SQL, use explain to analyze it, paying special attention to whether the index is used.

When operating a delete or update statement, add 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 adding limit has the following advantages:

1. Reduce the cost of writing incorrect SQL. When you execute this SQL in the command line, if you do not add limit, you may accidentally delete all the data during execution. What if you delete the wrong data? With the limit of 200, things are different. If you delete the data by mistake, you will only lose 200 records, which can be quickly restored through binlog.
2. SQL efficiency is likely to be higher. If you add limit 1 in the SQL line, if the first line hits the target return, without limit, it will continue to scan the table.

3. Avoid long transactions. When delete is executed, if age is indexed, MySQL will add write locks and gap locks to all related rows. All execution-related rows will be locked. If the number of deletions is large, it will directly affect the related business and make it unusable.
4. If the amount of data is large, it is easy to fill up the CPU. If you delete a large amount of data without adding a limit to the number of records, it is easy to fill up the CPU, causing the deletion to become slower and slower.

When designing a table, add corresponding comments to all tables and fields (SQL standard and elegant)

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

Positive example:

CREATE TABLE `account` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key Id',
 `name` varchar(255) DEFAULT NULL COMMENT 'Account name',
 `balance` int(11) DEFAULT NULL COMMENT 'Balance',
 `create_time` 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`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='Account table';

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;

In SQL writing format, keep the keyword size consistent and use indentation. (SQL is elegant and standardized)

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, unifying the capitalization of keywords and using indentation alignment will make your SQL look more elegant.

The INSERT statement indicates the corresponding field name (SQL standard and elegant)

Counterexample:

insert into Student values ​​('666','Amateur Grass','100');

Positive example:

insert into Student(student_id,name,score) values ​​('666','女生草','100');

The SQL change operation should be performed in the test environment first, with detailed operation steps and rollback plan, and reviewed before production. (SQL regret medicine)

• Test the SQL changes in a test environment first to avoid putting them in production with syntax errors.
• When changing SQL operations, detailed operation steps need to be stated, especially when there are dependencies, such as: modifying the table structure first and then adding the corresponding data.
• There is a rollback plan for SQL change operations, and the corresponding SQL changes are reviewed before production.

When designing a database table, add three fields: primary key, create_time, and update_time. (SQL standard elegance)

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 table';

Positive example:

CREATE TABLE `account` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key Id',
 `name` varchar(255) DEFAULT NULL COMMENT 'Account name',
 `balance` int(11) DEFAULT NULL COMMENT 'Balance',
 `create_time` 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`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='Account table';

reason:

1. The primary key must be added. A table without a primary key is soulless.
2. As for the creation time and update time, it is recommended to add them. Detailed audit and tracking records are useful.

Alibaba Development Manual also mentions this point, as shown in the figure

After writing the SQL statement, check the columns after where, order by, and group by, and whether the columns related to multiple tables have been indexed, giving priority to combined indexes. (SQL performance optimization)

Counterexample:

Positive example:

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

Before modifying or deleting important data, back it up first, back it up first, back it up 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 an incorrect operation, you can regret it later.

Pay attention to the implicit conversion of data types of the fields following where (SQL performance optimization)

Counterexample:

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

Positive example:

select * from user where userid ='123';

reason:

Because when single quotes are not added, the comparison is between a string and a number, and their types do not match. MySQL will do an implicit type conversion, converting them to floating-point numbers before comparing them, which will eventually cause the index to become invalid.

Try to define all columns as NOT NULL (SQL standard elegance)

NOT NULL columns save more space, while 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.

To modify or delete SQL, write WHERE first to check, and then add delete or update after confirmation (SQL regret medicine)
Especially when operating production data, if you encounter SQL for modification or deletion, first add a where query, and after confirming OK, execute the update or delete operation.

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

Counterexample:

select * from employee;

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

All tables must use the Innodb storage engine (SQL standard elegance)

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

The character set of the database and table is unified to use UTF8 (SQL standard elegant)

Use UTF8 encoding uniformly

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

If you are storing expressions, you can consider utf8mb4.

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:

First of all, variable-length fields take up less storage space, which can save storage space.

If you modify the meaning of a field or add additional status to the field, you need to update the field annotation in a timely manner. (SQL is elegant and standardized)
This point is the Mysql specification in the Alibaba development manual. Your fields, especially those representing enumeration states, need to update their annotations immediately if their meaning is modified or states are added for better maintenance later.

SQL modifies data and develops the habit of begin + commit transactions; (SQL regret medicine)

Positive example:

begin;update account set balance =1000000where name ='女生草';commit;

Counterexample:

update account set balance = 1000000 where name = 'Amateur Grass';

Index naming 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 is elegant and standardized)
Note: pk_ is the primary key; uk _ is the unique key; idx _ is the abbreviation of index.

No function conversion or expression calculation is performed on columns in the WHERE clause

Assume loginTime is indexed.

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);

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

Counterexample:

delete from account limit 100000;

Positive example:

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

reason:

• Large batch operations will cause master-slave delays.
• The operation will generate large transactions and block.
• If the amount of data is too large, the CPU will be fully utilized.

I hope the above content will be helpful to readers on their programming journey!

That’s all for this article on 21 MySQL standardization and optimization best practices! This is the end of the article. For more relevant MySQL standard optimization content, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!