9 postures of MySQL performance optimization, no longer afraid of interviews!

1. Choose the most suitable field attribute

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 query executed on it. Therefore, when creating a table, in order to obtain better performance, we can give up the width of the field in the table as small as possible.

For example: when defining the postal code field, if it is set to char(255), it obviously adds unnecessary space to the database, and even the use of varchar is redundant, because char(6) can work well Completed the task. Similarly, if possible, we should use MEDIUMINT instead of BIGINT to define the integer field.

2. Try to set the field as NOT NULL

When possible, try to set the field to NOT NULL so that the database does not need to compare NULL values when performing queries in the future. 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 the numeric data is processed much faster than the text type. In this way we can improve the performance of the database.

3. Use JOIN instead of Sub-Queries

MySQL has supported SQL subqueries since 4.1. This technique can use the select statement to create a singleton query result, 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 fetch all the customer ids who placed orders from the sales information table, and then pass the results to the main query, as shown in the following figure Show:

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

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

For example: if you want to JOIN the DECIMAL field and an INT field, MySQL cannot use their index. For those STRING types, you also need to have the same character set. (The character sets of the two tables may be different).

The inner join of inner join is also called equal join, and the left/right join is outer link.

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 performance is faster, because inner join is an equivalent join, and perhaps the number of rows returned is relatively small. But we must remember that some statements implicitly use equi-joins, such as:

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

The join query in sql has four ways: inner join, left join, right join, and full join. There is actually not much difference between them, just query The results are different.

For example, we have two tables:

The Orders table is related to the Persons table through the foreign key Id\_P.

inner join (inner join), when the two tables are connected and query, only the result set that exactly matches in the two tables is retained.

We use inner join to connect and query the two tables, the sql is as follows:

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o
ON p.Id_P=o.Id_P and 1=1  --用and连接多个条件
ORDER BY p.LastName

Query result set:

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

Note: The simple select * from a,b is the Cartesian product. For example, table a has 5 pieces of data and table b has 3 pieces of data, so the final result has 5*3=15 pieces of data.

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

select * from a inner join b on a.id = b.id。 -- 即就是内连接。

But this way of writing does not conform to the specification, and may only work for some databases, such as sqlserver. It is recommended not to write this way. It's 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 two tables are connected for query, all rows of the left table will be returned, even if there is no matching record in the right table.

We use left join to connect and 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:

As you can see, the Id\_P field of the row whose LastName is Bush in the left table (Persons table) does not match in the right table (Orders table), but the query result still retains this row.

right join, when two tables are connected for query, all rows of the right table will be returned, even if there is no matching record in the left table.

We use right join to connect and 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 record matching it in the left table, but it is still retained.

full join, when two tables are connected for query, all rows that do not match in the left and right tables are returned.

We use full join to connect and 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 to replace manually created temporary tables

MySQL has supported union queries since version 4.0. It can combine two or more select queries that require temporary tables into one query. At the end of the client query session, the temporary table will be automatically deleted, so as to ensure that the database is tidy 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 it is impossible to have duplicate result sets or do not care about duplicate result sets, try to use union all instead of union, because the difference between union and union all is mainly that the former needs to combine two or more result sets and then perform uniqueness Filtering operations, which involve sorting, increase a lot of CPU operations, increase resource consumption and delay.

5. Affairs

Although we can use sub-queries (Sub-Queries), joins (JOIN) and unions (UNION) to create a variety of queries, not all database operations can be completed with only one or a few . More often, it is necessary to use a series of statements to complete a certain kind of work. But in this case, when a certain statement in the statement block runs incorrectly, the operation of the entire statement block becomes uncertain.

Imagine that if you want to insert a certain data into two related tables at the same time, there may be such a situation: after the first table is successfully updated, the database suddenly appears unexpected, causing the operation in the second table to not be completed , This will cause incomplete data, and even destroy the data in the database. To avoid this situation, you should use transactions, which are used to either succeed in each statement in the statement block or fail.

In other words, the consistency and integrity of the data in the database can be maintained. The transaction begins with the BEGIN keyword and ends with the COMMIT keyword. In the meantime, a SQL statement operation fails, then the Rollback command can restore the database to the state before begin.

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

Another function of the transaction is that when multiple users use the same data source at the same time, he can use the method of locking the database to provide a secure access mechanism for the user, so as to ensure that the user's operation is not interfered by other users.

Generally speaking, transactions must meet four conditions (ACID): Atomicity (Atomicity, or indivisibility), consistency (Consistency), isolation (Isolation, also known as independence), durability (Durability).

Atomicity: All operations in a transaction are either completely completed or not completed at all, and will not end in an intermediate link. If an error occurs during the execution of the transaction, it will be rolled back (Rollback) to the state where the transaction started, as if the transaction had never been executed.

Consistency: Before the start of the transaction and after the end of the transaction, the integrity of the database has not been destroyed. This means that the written data must fully comply with all the preset rules, which includes the accuracy and continuity of the data, and the subsequent database can spontaneously complete the scheduled work.

Isolation: The ability of the database to allow multiple transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistencies caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including read uncommitted (Read uncommitted), read committed (Read committed), repeatable read (repeateable read) and serialization (Serializable).

Persistence: After the transaction is completed, the modification of the data is permanent, and it will not be lost even if the system fails.

Concurrency issues of transactions:

1. Dirty read: Transaction A reads the updated data of transaction B, and then B rolls back the operation, then the data read by A is dirty data

2. Non-repeatable reading: Transaction A reads the same thing multiple times, and transaction B updates and commits the data during the process of transaction A reading multiple times. As a result, when transaction A reads the same data multiple times, the results are inconsistent.

3. Phantom reading: System administrator A changes the scores of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record of specific scores at this time, when system administrator A finishes the change I found that there was another record that had not been changed, as if an illusion had occurred. This is called a phantom reading.

Summary: It is easy to confuse non-repeatable reading and phantom reading. Non-repeatable reading focuses on modification, and phantom reading focuses on adding or deleting. To solve the problem of non-repeatable read, you only need to lock the rows that meet the conditions, and to solve the phantom read, you need to lock the table

MySQL transaction isolation level

Transaction control statement:

• BEGIN or START TRANSACTION: open a thing explicitly.
• COMMIT : COMMIT WORK can also be used, but the two are equivalent. COMMIT commits the transaction and makes all modifications to the database permanent.
• Rollback : Rollback work can also be used, but the two are equivalent. The rollback will end the user's transaction and undo all uncommitted changes in progress.
• SAVEPOINT identifier : SAVEPOINT allows to create a save point in a transaction, and there can be many SAVEPOINTs in a transaction;
• RELEASE SAVEPOINT identifier : Delete the save point of a thing. When there is no specified save point, an exception will be thrown when the statement is executed.
• ROLLBACK TO inditifier : Roll back the transaction to the marked point.
• SET TRANSACTION : used to set the isolation level of the transaction. The InnoDB storage engine provides transaction isolation levels READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERLALIZABLE.

6. Use foreign keys

The method of locking the table 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 the customerid in the customerinfo table to the customerid in the salesinfo table. Any record that has no legal 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 guarantees that a customer record in the customerinfo table will also be automatically deleted. If you want to use foreign keys in mysql, you must remember to define the type of the table as the transaction-safe InnoDB type when you create the table. This type is not the default type of mysql table. The method of definition is to add engine=innoDB to the CREATE TABLE statement.

You are still looking for interview questions everywhere, click on this interview library to refresh the questions, all kinds of interview questions are too complete.

7, lock the table

Although the transaction is a very good way to maintain the integrity of the database, but because of its exclusivity, sometimes affect the performance of the database, especially in large application systems. Since the database will be locked during the execution of the transaction, other user requests can only temporarily wait until the end of the transaction.

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

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

Here, we use a select statement to fetch the initial data, and through some calculations, update the new value to the table with an update statement. The LOCKTABLE statement that contains the WRITE keyword can ensure that there will be no other access to insert, update, or delete the inventory before the UNLOCKTABLES command is executed.

8. Use the index

Indexing is a common method to improve database performance. It can make the database server retrieve specific rows much faster than without indexes, especially when the query contains MAX(), MIN() and ORDERBY commands. The improvement is more obvious.

Which fields should be indexed?

In general, the index should be built on those fields that will be used for join, where judgment and orderby sorting. Try not to index a field with a large number of repeated values in the database. For an ENUM type field, it is very likely that there will be a large number of repeated values.

For example, the "province".. field in customerinfo, indexing on such a field will not help; on the contrary, it may also reduce the performance of the database. When we create the table, we can create a suitable index at the same time, or we can use ALTERTABLE or CREATEINDEX to create an index in the future.

In addition, MySQL supports full-text indexing and search starting from version 3.23.23. The full-text index is a FULLTEXT type index in MySQL, but it can only be used for MyISAM type tables. For a large database, it is very fast to load data into a table without a FULLTEXT index, and then use ALTERTABLE or CREATEINDEX to create the index. But 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 of de

Do not use subqueries

例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);

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

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

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

But please note that: optimization is only valid for SELECT, not for UPDATE/DELETE sub-queries, and the use of sub-queries should be avoided in a solid production environment

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, it will scan the entire table directly.

Should be changed to --->

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

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

LIKE double percent sign cannot be used for index

SELECT * FROM t WHERE name LIKE ‘%de%’;

—–>

SELECT * FROM t WHERE name LIKE ‘de%’;

Currently only MySQL5.7 supports full-text indexing (supports Chinese)

Read the appropriate record LIMIT M, N

SELECT * FROM t WHERE 1;

—–>

SELECT * FROM t WHERE 1 LIMIT 10;

Avoid data type inconsistency

SELECT * FROM t WHERE id = ’19’;

—–>

SELECT * FROM t WHERE id = 19;

Group statistics can prohibit sorting

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

By default, MySQL sorts all the fields of GROUP BY col1, col2... If the query includes GROUP BY, and you want to avoid the consumption of sort results, you can specify ORDER BY NULL to prohibit sorting. questions and answers are all sorted out. You can view 16193964bb2322 MySQL Enterprise Questions 16193964bb2326 for WeChat search for the technical road of migrant workers, and read them online.

—–>

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

Avoid random fetching of records

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

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

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

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

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

Link: blog.csdn.net/weixin_42047611/article/details/81772149