Home > Article > Backend Development > 8 Very Good MySQL Optimization Experiences_PHP Tutorial
8 Very Good MySQL Optimization Experiences_PHP Tutorial
- WBOYOriginal
- 2016-07-21 15:53:46697browse
1. Select the most applicable field attributes
MySQL can well support the access of large amounts of data, but generally speaking, the smaller the table in the database, the better the execution on it. The query will be faster. Therefore, when creating a table, in order to obtain better performance, we can set 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), it will obviously add unnecessary space to the database. Even using the VARCHAR type is redundant, because CHAR(6) is fine. Mission accomplished. Likewise, if possible, we should use MEDIUMINT instead of BIGINT to define integer fields.
Another way to improve efficiency is to set fields to NOT NULL when possible, so that the database does not need to compare NULL values when executing queries in the future.
For some text fields, such as "province" or "gender", we can define them as ENUM type. Because in MySQL, the ENUM type is treated as numeric data, and numeric data is processed much faster than text types. In this way, we can improve the performance of the database.
2. Use joins (JOIN) instead of sub-queries (Sub-Queries)
MySQL supports SQL subqueries starting from 4.1. This technique allows you to use a SELECT statement to create a single column of query results, 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 basic customer information table, we can use a subquery to first retrieve the IDs of all customers who issued orders from the sales information table, and then pass the results to the main query, as shown below :
DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);
Using subqueries can complete many SQL operations that logically require multiple steps to complete at one time, and at the same time Transaction or table locking can be avoided, and it is easy to write. However, in some cases, subqueries can be replaced by more efficient joins (JOINs). For example, assuming we want to extract all users who have no order records, we can use the following query to complete it:
SELECT * FROM customerinfo WHERE CustomerID NOT IN (Select CustomerID FROM salesinfo);
If Using a connection (JOIN) to complete this query 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:
SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL;
Join (JOIN) is more efficient because MySQL does not need to create a temporary table in memory to complete this logical two-step query.
3. Use union (UNION) to replace manually created temporary tables
MySQL supports UNION query starting from version 4.0, which can use temporary tables to Two or more SELECT queries combined into one query. When the client's query session ends, the temporary table will be automatically deleted to ensure that the database is tidy and efficient. When using UNION to create a query, we only need to use UNION as the 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.
SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author UNION SELECT Name, Supplier FROM product;
4. Transactions
Although we You can use sub-queries (Sub-Queries), connections (JOIN) and unions (UNION) to create a variety of queries, but not all database operations can be completed with only one or a few SQL statements. More often, a series of statements are needed to complete a certain kind of work.
But in this case, when a certain statement in this statement block runs incorrectly, the operation of the entire statement block will become uncertain. Imagine that you want to insert certain data into two related tables at the same time. This may happen: after the first table is successfully updated, an unexpected situation occurs in the database, causing the operation in the second table to not be completed. , In this way, the data will be incomplete and even the data in the database will be destroyed.
To avoid this situation, you should use transactions. Its function is: 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 starts with the BEGIN keyword and ends with the COMMIT keyword. If a SQL operation fails during this period, the ROLLBACK command can restore the database to the state before BEGIN started.
BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11 WHERE item='book';
COMMIT;
Another important role of transactions is When multiple users use the same data source at the same time, it can use the method of locking the database to provide users with a safe access method, thus ensuring that the user's operations are not interfered with by other users.
5. Locking tables
Although transactions are a very good way to maintain the integrity of the database, because of its exclusivity, it sometimes affects 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 wait until the transaction ends. If a database system is used by only a few users, the impact of transactions will not become a big problem; but if thousands of users access a database system at the same time, such as accessing an e-commerce website, it will cause Serious response delay.
In fact, in some cases we can obtain better performance by locking the table. The following example uses the lock table method to complete the transaction function in the previous example.
LOCK TABLE inventory WRITE Select Quantity FROM inventory WHERE Item='book';
…
UPDATE inventory SET Quantity=11 WHERE Item='book';
UNLOCK TABLES
Here , we use a SELECT statement to retrieve the initial data, and through some calculations, use an UPDATE statement to update the new values into the table. The LOCK TABLE statement containing the WRITE keyword ensures that there will be no other access to insert, update, or delete the inventory before the UNLOCK TABLES command is executed.
6. Using 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 without a valid 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 DELETE CASCADE ) TYPE = INNODB;
Note the parameter "ON DELET CASCADE" in the example.This parameter ensures that when a customer record in the customerinfo table is deleted, all records related to the customer in the salesinfo table will also be automatically deleted. If you want to use foreign keys in MySQL, you must remember to define the table type as a transaction-safe InnoDB type when creating the table. This type is not the default type for MySQL tables. The definition method is to add TYPE=INNODB to the CREATE TABLE statement. As shown in the example.
7. Use indexes
Indexes are a common method to improve database performance. They allow the database server to retrieve specific rows much faster than without indexes. Especially when the query statement contains commands such as MAX(), MIN() and ORDER BY, the performance improvement is more obvious. So which fields should be indexed? Generally speaking, indexes should be built on fields that will be used for JOIN, WHERE judgment and ORDER BY sorting. Try not to index a field in the database that contains a large number of duplicate values. For an ENUM type field, it is very possible to have a large number of duplicate values, such as the "province" field in customerinfo. Building an index on such a field will not help; on the contrary, it may also slow down the database performance. We can create appropriate indexes at the same time when creating the table, or we can use ALTER TABLE or CREATE INDEX to create indexes later. In addition, MySQL supports full-text indexing and search starting with 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 the data into a table without a FULLTEXT index and then use ALTER TABLE or CREATE INDEX 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.
8. Optimized query statements
In most cases, using indexes can improve query speed, but if the SQL statement is used inappropriately, the index will not be able to play its due role. The following are several aspects that should be paid attention to. First, it is best to perform comparison operations between fields of the same type. Before MySQL version 3.23, this was even a required condition. For example, an indexed INT field cannot be compared with a BIGINT field; but as a special case, when the field size of a CHAR type field and a VARCHAR type field are the same, they can be compared. Secondly, try not to use functions to operate on indexed fields.
For example, when using the YEAR() function on a DATE type field, the index will not function as it should. Therefore, although the following two queries return the same results, 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 situation will also occur with logarithmic values When fields are calculated:
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 later query will be much faster than the previous one. Third, when searching for character fields, we sometimes use LIKE keywords and wildcards. Although this approach is simple, it also comes at the expense of system performance. For example, the following query will compare every record in the table.
SELECT * FROM books WHERE name LIKE 'MySQL%';
But if you use the following query, the result returned is the same, but the speed is much faster:
SELECT * FROM books WHERE name>='MySQL' AND name<'MySQM';
Finally, you should be careful to avoid letting MySQL perform automatic type conversion in the query, because the conversion process will also make the index unusable. kick in.