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 faster the queries executed on it will be. 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 BIGIN 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 types. 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 sub-queries 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. It can also avoid transaction or table locks, and it is also easy to write. However, in some cases, subqueries can be replaced by more efficient joins (JOIN).. For example, suppose we want to retrieve all users who do not have order records, we can use the following query:
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
If you use connection (JOIN).. to complete this query, the speed will 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
connection (JOIN).. The reason why it is more efficient is that 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 queries starting from version 4.0, which can merge two or more SELECT queries that require the use of temporary tables 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 can use sub-queries, connections (JOIN) and unions (UNION) to create a variety of queries, not all database operations can be performed with just one Or it can be completed with just 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. Things start with the BEGIN keyword and end 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 that 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, which can ensure that the user's operations are not blocked by other users. interfered with.
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
WHEREItem='book';
...
UPDATE inventory SET Quantity=11
WHEREItem='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 the inventory to insert, update, or delete before the UNLOCK TABLES command is executed.
6.
The method of using foreign keys to lock 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 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 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 defined method is to add TYPE=INNODB to the CREATE TABLE statement. As shown in the example.
7. Using indexes
is a common method to improve database performance. It allows the database server to retrieve specific rows much faster than without an index, especially if the query statement contains MAX(), MIN() and ORDERBY. When executing the command, 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 be helpful; on the contrary, it is possible Reduce 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 will be 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 SQL statements are used improperly, 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. Prior to 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 a CHAR type field and a VARCHAR type field have the same size, they can be compared. Secondly, try not to use functions to operate on indexed fields.
For example, when using the YEAE() 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 when calculating 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 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 returned results are 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 ineffective.