Summary of experience in optimizing mysql database

  • 2020-05-19 06:04:39
  • OfStack

1. Select the most applicable field properties
MySQL is good for accessing large amounts of data, but generally speaking, the smaller the table in the database, the faster the queries will be executed on it. Therefore, when creating a table, we can make the width of the fields in the table as small as possible for better performance. For example, when defining the zip code field, setting it to CHAR(255) would obviously add unnecessary space to the database, and even using the VARCHAR type would be redundant, because CHAR(6) would do the job well. Similarly, if possible, we should define integer fields using MEDIUMINT instead of BIGIN.
Another way to be more efficient is to set the field to NOT NULL whenever possible, so that the database does not have to compare the NULL value when the query is executed in the future.
For some text fields, such as "province" or "gender," we can define them as ENUM types. Because in MySQL, the ENUM type is treated as numeric data, and numeric data can be processed much faster than text types. In this way, we can improve the performance of the database.

2. Use joins (JOIN) instead of subqueries (Sub-Queries)
MySQL has supported SQL's subqueries since 4.1. This technique USES the SELECT statement to create a single-column query result, which can then be used as a filter on another query. For example, if we want to delete customers without any orders from the customer base information table, we can use the subquery to first pull out ID from the sales information table and then pass the result to the main query, as shown below:
DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
Using subqueries allows you to perform many SQL operations that logically require multiple steps, while avoiding transaction or table locking, and is easy to write. However, there are cases where subqueries can be joined more efficiently (JOIN).. Alternative. For example, if we want to pull out all the users who don't have an order record, we can do this with the following query:
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
If you use a connection (JOIN).. To complete this query, it would be much faster. Especially if there is an index on CustomerID in the salesinfo table, the performance will be better. The query is as follows:
SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL
Connect (JOIN).. It is more efficient because MySQL does not need to create temporary tables in memory to perform this logical two-step query.

3. Use UNION instead of manually created temporary tables
MySQL supports UNION queries from version 4.0, which can combine two or more SELECT queries that require temporary tables into one query. At the end of the client's query session, the temporary tables are automatically deleted to keep the database clean and efficient. To create the query using UNION, we just need to concatenate multiple SELECT statements using UNION as the keyword. Note that the number of fields in all SELECT statements should be the same. The following example demonstrates a query using UNION.


SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product

4, transaction
Although we can create a variety of queries using subqueries (Sub-Queries), joins (JOIN), and unions (UNION), not all database operations can be done with just one or a few SQL statements. More often than not, you'll need a series 1 statement to get something done. However, in this case, when one of the statements in this block runs incorrectly, the operation of the whole statement block will become uncertain. Imagine 1, to insert some data into two associated tables at the same time, such a situation may occur: after the first table is successfully updated, the database suddenly appears unexpected conditions, resulting in the operation in the second table is not completed, in this way, will cause incomplete data, or even damage the data in the database. To avoid this, you should use transactions, which either operate on every statement in a block successfully or fail. In other words, the data in the database can be kept 1 and integrity. Things start with the BEGIN keyword and end with the COMMIT keyword. One of the SQL operations in between fails, and the ROLLBACK command restores the database to the state it was before BEGIN started.


BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11
WHERE item='book';
COMMIT;

Another important function of transactions is that when multiple users are using the same data source at the same time, it can use the method of locking the database to provide users with a secure access, so as to ensure that the user's operation is not interfered by other users.

5. Lock the table
Although transactions are a very good way to maintain database integrity, they can sometimes affect database performance, especially in large applications, because of their exclusivity. Because the database will be locked during the execution of the transaction, other user requests will have to wait until the end of the transaction. If a database system is only used by a few users, the impact of transactions will not be a big problem. However, if there are thousands of users accessing a database system at the same time, such as an e-commerce website, there will be serious response delays.
In fact, there are situations where you can lock tables to get better performance. The following example USES the locking table method to complete the transaction function in the previous example.


LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem='book';
...
UPDATE inventory SET Quantity=11
WHEREItem='book';
UNLOCK TABLES

Here, we use an SELECT statement to fetch the initial data, perform some calculations, and update the new values to the table with an UPDATE statement. The LOCK TABLE statement containing the WRITE keyword guarantees that no other access will be made to insert, update, or delete inventory until the UNLOCK TABLES command is executed.

6. Use foreign keys
Locking a table maintains data integrity, but it does not guarantee data affinity. This is where we can use foreign keys. For example, a foreign key ensures that every sales record points to an existing customer. Here, the foreign key can map CustomerID in customerinfo table to CustomerID in salesinfo table, and any record that is not legally CustomerID will not be updated or inserted into salesinfo.


CREATE TABLE customerinfo
(
 CustomerID INT NOT NULL ,
 PRIMARY KEY ( CustomerID )
) TYPE = INNODB;
CREATE TABLE salesinfo
(
 SalesID INT NOT NULL,
 CustomerID INT NOT NULL,
 PRIMARY KEY(CustomerID, SalesID),
 FOREIGN KEY (CustomerID) REFERENCES customerinfo
 (CustomerID) ON DELETECASCADE
) TYPE = INNODB;

Note the parameter "ON DELETE CASCADE" in the example. This parameter ensures that when a customer record in the customerinfo table is deleted, all records related to that customer in the salesinfo table are automatically deleted. If you are using a foreign key in MySQL, remember to define the table type as the transaction security table InnoDB type when creating the table. This type is not the default type for the MySQL table. The method is defined by adding TYPE=INNODB to CREATE TABLE statement. As shown in the example.

7. Use indexes
Indexes are a common way to improve database performance by enabling the database server to retrieve specific rows much faster than if there were no indexes, especially if the query contained commands MAX(), MIN(), and ORDERBY. Which fields should be indexed? 1 generally, indexes should be based on the fields that will be used for JOIN, WHERE judgment, and ORDER BY sorting. Try not to index a field in the database that has a lot of duplicate values. It is possible for a field of type ENUM to have a large number of duplicate values, such as "province" in customerinfo.. Field, on which indexing will not help; Conversely, it is possible to degrade the performance of the database. We can create the appropriate index at the same time we create the table, or we can use ALTER TABLE or CREATE INDEX to create the index later. In addition, MySQL

Full-text indexing and search are supported starting with version 3.23.23. A full-text index is an index of type FULLTEXT in MySQL, but can only be used for tables of type MyISAM. For a large database, loading data into a table without an FULLTEXT index and then creating an index using ALTER TABLE or CREATE INDEX would be very fast. But if you load the data into a table that already has an FULLTEXT index, the execution will be very slow.

8. Optimized query statements
Most of the time, using an index will speed up a query, but if the SQL statement is not used properly, the index will not do its job. Here are a few things to look out for. First, it is best to compare operations between fields of the same type. Before MySQL 3.23, this was even a requirement. For example, you cannot compare an indexed INT field with an indexed BIGINT field. But as a special case, you can compare fields of type CHAR with fields of type VARCHAR if they are the same size. Second, try not to use functions on indexed fields.

For example, using the YEAE() function on a field of type DATE will prevent the index from functioning as it should. Therefore, the following two queries return one result, but the latter is much faster than the former.
SELECT * FROM order WHERE YEAR(OrderDate) < 2001;
SELECT * FROM order WHERE OrderDate < "2001-01-01";
The same is true for numeric fields:
SELECT * FROM inventory WHERE Amount/7 < 24;
SELECT * FROM inventory WHERE Amount < 24*7;
The above two queries also return the same results, but the latter one will be much faster than the previous one. Third, when searching for character fields, we sometimes use the LIKE keyword and wildcards, which is simple but comes at the expense of systematicness. For example, the following query will compare every record in the table.
SELECT * FROM books
WHERE name like "MySQL%"
However, if you use the following query, the result will be 1, but it will be much faster:
SELECT * FROM books
WHERE name > ="MySQL"and name < "MySQM"
Finally, care should be taken to avoid having MySQL do automatic type conversion in the query, as the conversion process will also render the index ineffective.


Related articles: