9 Tips for MySQL Database Optimization

1. Choose the most appropriate field attributes

Mysql is a relational database that can well support the storage of large amounts of data, but generally speaking, the smaller the table in the database, the faster the queries executed on it. Therefore, when creating a table, in order to obtain better performance, we can make the width of the fields in the table as small as possible.

For example, when defining the postal code field, if you set it to char(255)， this will obviously add unnecessary space to the database. Even using varchar type is redundant because char(6) can accomplish the task well. Similarly, if possible, we should use MEDIUMINT instead of BIGINT to define integer fields.

2. Try to set the field to NOT NULL

Whenever possible, set the field to NOT NULL so that the database does not have to compare NULL values ​​when executing future queries.

For some text fields, such as "province" or "gender", we can define them as ENUM (enumeration) type. Because in MySQL, the ENUM type is treated as numeric data, and numeric data is processed much faster than text data. This way we can improve the performance of the database.

3. Use JOIN instead of sub-queries

MySQL supports SQL subqueries starting from version 4.1. This technique can be used to create a singleton query result using a select statement, and then use this result as a filter condition in another query.

For example, if we want to delete customers who do not have any orders in the customer basic information table, we can use a subquery to first retrieve the customer IDs of all customers who have placed orders from the sales information table, and then pass the result to the main query, as shown in the following figure:

If you use a JOIN to complete this task, the speed will be much faster, especially when there is an index on CustomerID in the salesinfo table, the performance will be better. The query is as follows:

The reason why JOIN is more efficient is that MySQL does not need to create a temporary table in memory to complete this query that logically requires two steps.

Additionally, if your application has many JOIN queries, you should make sure that the fields in the two tables being JOINed are indexed. In this way, MySQL will start a mechanism to optimize the JOIN SQL statement for you. And the fields used for JOIN should be of the same type.

For example: If you JOIN a DECIMAL field with an INT field, MySQL cannot use their indexes. For those STRING types, the same character set is also required. (The character sets of the two tables may not be the same).

Inner inner join is also called equijoin, and left/right join is an outer join.

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id=B.id;

SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;

SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

It has been confirmed in many aspects that inner join has faster performance because inner join is an equal join and may return fewer rows. But we must remember that some statements implicitly use equijoins, such as:

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

Recommendation: Use inner join if possible.

有inner join , left join right join , right join, and full join join. There is actually not much difference between them, only the query results are different.

For example, we have two tables:

Orders table is related to the Persons table through the foreign key Id_P .

inner join join: when two tables are joined for query, only the result sets that fully match the two tables are retained.

We use inner join to query the two tables. The SQL is as follows:

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders of
ON p.Id_P=o.Id_P and 1=1 -- Use and to connect multiple conditions ORDER BY p.LastName

Query result set:

In this connection method, if the Id_P field in the Orders table cannot find a match in the Persons table, it will not be listed.

Note: Simply select * from a,b is a Cartesian product. For example, if table a has 5 records and table b has 3 records, then the final result will be 5*3=15 records.

But if we join two tables: select * from a,b where a.id = b.id , the meaning changes, and it is equivalent to:

select * from a inner join b on a.id = b.id. -- That is, inner join.

However, this writing method does not conform to the standard and may only work for certain databases, such as sqlserver . It is recommended not to write like this. It is best to write it as inner join.

The difference between inner join query ( select * from a join b on a.id = b.id ) and associated query (select * from a , b where a.id = b.id)

left join , when joining two tables, returns all rows from the left table, even if there are no matching records in the right table.

We use left join to query the two tables. The SQL is as follows:

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
LEFT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

The query results are as follows:

You can see that the Id_P field of the row with LastName Bush in the left table ( Persons table) has no match in the right table ( Orders table), but the query result still retains the row.

right join, when two tables are joined, all rows in the right table will be returned, even if there are no matching records in the left table.

We use right join to query the two tables. The SQL is as follows:

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
RIGHT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

The query results are as follows:

The Id_P field value of the last record in the Orders table is 65. There is no matching record in the left table, but it is still retained.

full join , when performing a join query on two tables, returns all unmatched rows in the left and right tables.

We use full join to query the two tables. The SQL is as follows:

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
FULL JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

The query results are as follows:

The query result is the union of left join and right join .

4. Use UNION instead of manually created temporary tables

MySQL has supported union queries since version 4.0, which can combine two or more select queries that require the use of temporary tables into one query. When the client query session ends, the temporary table will be automatically deleted to ensure that the database is neat and efficient. When using union to create a query, we only need to use union as a keyword to connect multiple select statements. It should be noted that the number of fields in all select statements must be the same. The following example demonstrates a query using union.

When we can confirm that there will be no duplicate result sets or we don't care about duplicate result sets, try to use union all instead of union. The main difference between union and union all is that the former needs to merge two or more result sets before performing unique filtering operations, which involves sorting, adding a lot of CPU operations, increasing resource consumption and latency.

5. Affairs

Although we can use Sub-Queries queries, joins, and unions to create a variety of queries, not all database operations can be completed with only one or a few. More often than not, a series of statements are needed to complete a task. However, in this case, when a statement in this block fails to run, the operation of the entire block becomes uncertain.

Imagine that you want to insert a certain data into two related tables at the same time. The following situation may occur: after the first table is successfully updated, an unexpected situation suddenly occurs in the database, causing the operation in the second table to not be completed. This will cause incomplete data or even damage the data in the database. To avoid this situation, you should use transactions, which ensure that either every statement in the statement block succeeds or fails.

In other words, the consistency and integrity of the data in the database can be maintained. A transaction begins with the BEGIN keyword and ends with the COMMIT keyword. If an SQL statement fails during this period, the Rollback command can restore the database to the state before begin.

BEGIN; 
INSERTINTOsalesinfoSETCustomerID=14;
UPDATEinventorySETQuantity=11WHEREitem='book';
COMMIT;

Another function of a transaction is that when multiple users use the same data source at the same time, it can provide users with a secure access mechanism by locking the database, thus ensuring that the user's operations are not interfered with by other users.

Generally speaking, transactions must meet four conditions (ACID): Atomicity (also known as indivisibility), Consistency (also known as Isolation ), and Durability .

• Atomicity: All operations in a transaction are either completed or not completed at all, and will not end at some intermediate stage. If an error occurs during the execution of a transaction, it will be rolled back to the state at the beginning of the transaction, as if the transaction had never been executed.
• Consistency: The integrity of the database is not compromised before the transaction begins and after the transaction ends. This means that the data written must fully comply with all preset rules, including the accuracy and concatenation of the data and the ability of the subsequent database to spontaneously complete the scheduled tasks.
• Isolation: The database allows multiple transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistency due to cross-execution when multiple transactions are executed concurrently. There are different levels of transaction isolation, including Read uncommitted , Read Read committed , repeateable read , and Serializable .
• Persistence: After the transaction is completed, the changes to the data are permanent and will not be lost even if the system fails.

Concurrency issues of transactions:

• Dirty read: Transaction A reads the data updated by transaction B, and then B rolls back the operation, so the data read by A is dirty data
• Non-repeatable read: Transaction A reads the same transaction multiple times. During the process of transaction A reading the same transaction multiple times, transaction B updates the data and commits it. This causes inconsistent results when transaction A reads the same data multiple times.
• Phantom read: System administrator A changes the grades of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record with a specific score at this time. When system administrator A finishes the change, he finds that there is still a record that has not been changed, which is like an illusion. This is called phantom read.
• Summary: Non-repeatable reads and phantom reads are easily confused. Non-repeatable reads focus on modifications, while phantom reads focus on additions or deletions. To solve the problem of non-repeatable reads, you only need to lock the rows that meet the conditions. To solve the problem of phantom reads, you need to lock the table.

MySQL transaction isolation levels

Transaction control statements:

BEGIN or START TRANSACTION : explicitly start a transaction.

• COMMIT: You can also use COMMIT WORK , but the two are equivalent. COMMIT commits the transaction and makes all modifications made to the database permanent.
• Rollback: You can also use Rollback work , but the two are equivalent. A rollback ends the user's transaction and undoes any uncommitted changes in progress.
• SAVEPOINT identifier: SAVEPOINT allows creating a savepoint in a transaction. There can be many SAVEPOINTs in a transaction.
• RELEASE SAVEPOINT identifier: Deletes the save point of a transaction. When there is no specified save point, executing this statement will throw an exception.
• ROLLBACK TO inditifier: Roll back the transaction to the marked point.
• SET TRANSACTION: used to set the transaction isolation level. The transaction isolation levels provided by the InnoDB storage engine are READ UNCOMMITTED , READ COMMITTED , REPEATABLE READ , and SERLALIZABLE .

6. Use foreign keys

The locking table method can maintain the integrity of the data, but it cannot guarantee the relevance of the data. At this time we can use foreign keys. For example: a foreign key can ensure that each sales record points to an existing customer.

Here, the foreign key can map customerid in the customerinfo table to customerid in the salesinfo table. Any record that does not have a valid customerid will not be updated or inserted into salesinfo .

CREATE TABLE customerinfo(customerid int primary key) engine = innodb;

CREATE TABLE salesinfo( salesid int not null,customerid int not null, primary key(customerid,salesid),foreign key(customerid) references customerinfo(customerid) on delete cascade)engine = innodb;

Note the parameter " on delete cascade " in the example. This parameter ensures that when a customer record in customerinfo table is deleted, it will also be automatically deleted. If you want to use foreign keys in MySQL, remember to define the table type as a transaction-safe table InnoDB type when creating the table. This type is not the default type for mysql tables. The definition method is to add engine=innoDB in the CREATE TABLE statement.

7. Lock table

Although transactions are a very good way to maintain database integrity, their exclusivity sometimes affects database performance, especially in large application systems. Since the database will be locked during the execution of the transaction, other user requests can only wait temporarily until the transaction ends.

If a database system is used by only a few users, the impact of transactions will not be a big problem; but if there are thousands of users accessing a database system at the same time, such as accessing an e-commerce website, there will be serious response delays.

In fact, in some cases we can get better performance by locking the table. The following example is a method of locking a table to complete the transaction function in the previous example.

Here, we use a select statement to retrieve the initial data, perform some calculations, and use an update statement to update the new values ​​into the table. The LOCKTABLE statement containing the WRITE keyword ensures that no other access to inventory can be performed to insert, update, or delete data until the UNLOCKTABLES command is executed.

8. Use indexes

Indexing is a common method to improve database performance. It allows the database server to retrieve specific rows much faster than without an index. This is especially true when the query contains commands such as MAX(),MIN()和ORDERBY .

So which fields should be indexed?

Generally speaking, indexes should be created on fields that will be used for join , where statements, and orderby sorting. Try not to create an index for a field in the database that contains a large number of repeated values. For an ENUM type field, it is very likely that a large number of repeated values ​​will appear.

For example, the " province " field in customerinfo . Creating an index on such a field will not be helpful; on the contrary, it may even reduce the performance of the database. We can create appropriate indexes at the same time when we create the table, or we can use ALTERTABLE or CREATEINDEX to create indexes later.

In addition, MySQL supports full-text indexing and searching starting from version 3.23.23. The full-text index in MySQL is a FULLTEXT type index, but it can only be used for MyISAM type tables. For a large database, loading the data into a table without a FULLTEXT index and then creating the index using ALTERTABLE or CREATEINDEX INDEX will be very fast. However, if you load data into a table that already has a FULLTEXT index, the execution process will be very slow.

9. Optimize the query statement

9.1 Not using subqueries

Example: SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');

In MySQL 5.5, the internal execution planner executes the subquery in this way: first check the outer table and then match the inner table, instead of checking the inner table t2 first. When the data in the outer table is very large, the query speed will be very slow.

In MariaDB10/MySQL5.6, the join method is used to optimize it. This SQL statement will be automatically converted to

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

But please note that the optimization is only effective for SELECT , not for UPDATE / DELETE subqueries. Subqueries should be avoided in production environments.

9.2 Avoid Functional Indexes

example:

SELECT * FROM t WHERE YEAR(d) >= 2016;

Since MySQL does not support functional indexes like Oracle, even if the d field has an index, the entire table will be scanned directly.

Should be changed to —–>

SELECT * FROM t WHERE d >= '2016-01-01';

9.3 Replace OR with IN

Inefficient query

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

—–> Efficient query

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

9.4 LIKE double percent sign cannot use index

SELECT * FROM t WHERE name LIKE '%de%';

—–>

SELECT * FROM t WHERE name LIKE 'de%';

Currently only MySQL 5.7 supports full-text indexing (supports Chinese)

9.5 Read the appropriate records LIMIT M,N

SELECT * FROM t WHERE 1;

—–>

SELECT * FROM t WHERE 1 LIMIT 10;

9.6 Avoiding Data Type Inconsistencies

SELECT * FROM t WHERE id = '19';

—–>

SELECT * FROM t WHERE id = 19;

9.7 Group statistics can prohibit sorting

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

By default, MySQL sorts all fields in GROUP BY col1 , col2 … If your query includes GROUP BY and you want to avoid the overhead of sorting the results, you can specify ORDER BY NULL to suppress the sort.

—–>

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

9.8 Avoid Random Record Fetching

SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

MySQL does not support functional indexes, which will result in a full table scan—–>

SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

9.9 Prevent unnecessary ORDER BY sorting

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;

—–>

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

9.10 Batch INSERT

INSERT INTO t (id, name) VALUES(1,'Bea');
INSERT INTO t (id, name) VALUES(2,'Belle');
INSERT INTO t (id, name) VALUES(3,'Bernice');

—–>

INSERT INTO t (id, name) VALUES(1,'Bea'), (2,'Belle'),(3,'Bernice');

This concludes this article about 9 techniques for optimizing MySQL databases. For more techniques related to optimizing MySQL databases, please search previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!