MySQL can be very good at supporting access to large amounts of data, but in general, the smaller the table in the database, the faster the query that will be executed on it. Therefore, when creating a table, we can set the width of the fields in the table as small as possible in order to get better performance. 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, as CHAR(6) will do the job just fine. Similarly, we should use MEDIUMINT instead of BIGIN to define integer fields if possible.
Another way to improve efficiency is that when possible, you should try to set fields to NOT NULL so that the database doesn't have to compare NULL values when performing future queries.
For some text fields, such as "province" or "gender", we can define them as ENUM. This is because ENUM types are treated as numeric data in MySQL, and numeric data can be processed much faster than text types. In this way, we can again improve the performance of the database.
2. Use JOIN instead of Sub-Queries
MySQL supports subqueries in SQL since 4.1. This technique allows you to use a SELECT statement to create a single-column query result, and then use that result as a filter in another query. For example, if we want to remove customers from the basic customer information table who do not have any orders, we can use a subquery to first take the IDs of all customers who have issued orders from the sales information table, and then pass the results to the main query as follows:
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
Using a subquery allows you to do many SQL operations at once that would logically take multiple steps to complete, as well as avoiding transactions or table locking, and is easy to write. However, there are cases where subqueries can be more efficiently joined (JOIN)... instead. For example, suppose we want to take out all the users who do not have an order record, which can be done with this query:
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
If you use a JOIN.... to accomplish this query, it would be much faster. Especially if the salesinfo table has an index on CustomerID, the performance will be better, the query is as follows:
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo. salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
JOIN ... The reason it's a little more efficient is that MySQL doesn't need to create temporary tables in memory to accomplish this logical query that requires two steps.
3. Use UNION instead of manually created temporary tables
MySQL has supported UNION queries since version 4.0, which allow you to combine two or more SELECT queries that require the use of temporary tables into a single query. At the end of the client's query session, the temporary tables are automatically deleted, keeping the database tidy and efficient. To create a query using UNION, you simply concatenate multiple SELECT statements using UNION as the keyword, keeping in mind 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
While we can use Sub-Queries (Sub-Queries), Join (JOIN) and Union (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 than not, a series of statements are used to accomplish a certain task. But in this case, when one of the statements in the block runs wrong, the operation of the whole block becomes uncertain. Imagine, 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 appeared in an unexpected situation, resulting in the second table operation is not completed, so that will cause the data is incomplete, and even will destroy the data in the database. To avoid this situation, you should use a transaction, which works as follows: either every statement in the statement block operates successfully or fails. In other words, it keeps the consistency and integrity of the data in the database. Transactions begin with the BEGIN keyword and end with the COMMIT keyword. An SQL operation in between fails, then the ROLLBACK command restores the database to the state it was in 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 are using the same data source at the same time, it can be used to lock the database to provide a secure way for users to access it, which ensures that their 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, they can sometimes affect the performance of the database because of their exclusivity, especially in very large applications. Because the database is locked during the execution of a transaction, other user requests have to wait until the transaction is completed. If a database system is used by only a few users
, the impact of transactions will not be much of an issue; however, assuming that thousands of users are accessing a database system at the same time, such as an e-commerce Web site, the response delays can be severe.
In fact, there are cases where we can get better performance by locking tables. The following example uses the locked table approach to accomplish the functionality of the transaction 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 take out the initial data with a SELECT statement, do some calculations, and update the new value with an UPDATE statement UPDATE statement to update the new values to the table. The LOCK TABLE statement with the WRITE keyword ensures that there is no access to the inventory to insert, update, or delete until the UNLOCK TABLES command is executed.
6, the use of foreign keys
Locking the table can maintain data integrity, but it does not guarantee the relevance of the data. This time we can use foreign keys. For example, a foreign key ensures that every sales record points to a particular customer that exists. Here, the foreign key maps the CustomerID in the customerinfo table to the CustomerID in the salesinfo table, and any record that does not have a legal 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 in the salesinfo table related to that customer are also 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 table InnoDB type when you create the table. This type is not the default type for MySQL tables. It is defined by adding TYPE=INNODB to the CREATE TABLE statement, as shown in the example.
7. Using indexes
Indexes are a common way to improve database performance by allowing the database server to retrieve specific rows much faster than it would without indexes, especially if the query statement contains commands such as MAX(), MIN(), and ORDERBY. What fields should be indexed? In general, 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. It is quite possible for a field of type ENUM to have a large number of duplicate values, such as "province" in the customerinfo... field. Creating an index on such a field will not help much; on the contrary, it may degrade the performance of the database. We can create the appropriate indexes when we create the table, or we can use ALTER TABLE or CREATE INDEX to create the indexes later. In addition, MySQL
supports full-text indexing and searching starting with version 3.23.23. A full-text index is a FULLTEXT type index in MySQL, but can only be used on MyISAM type tables. For a large database, loading data into a table without a FULLTEXT index and then creating the index using ALTER TABLE or CREATE INDEX will be very fast. However, if you load data into a table that already has a FULLTEXT index, the execution will be very slow.
8, optimized query statement
In most cases, the use of indexes can improve the speed of the query, but if the SQL statement is not used appropriately, the index will not be able to play its proper role. Here are a few things to keep in mind. First, it is best to perform operations that compare between fields of the same type. Prior to MySQL version 3.23, this was even a requirement. For example, you could not compare an indexed INT field with a BIGINT field; however, as a special case, you could compare a CHAR field with a VARCHAR field when they had the same field size. Second, try not to use functions on indexed fields.
For example, using the YEAE() function on a DATE-type field will prevent the index from functioning as it should. So, while 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 thing happens when performing calculations on numeric fields:
SELECT * FROM order WHERE YEAR(OrderDate)< "2001-01-01";
The same happens when performing calculations on numeric fields when performing calculations:
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
The two queries above 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 the LIKE keyword and wildcards, which is simple but comes at the expense of system performance. For example, the following query will compare every row in the table.
SELECT * FROM books
WHERE name like "MySQL%"
But the following query returns the same result, but is much faster:
SELECT * FROM books
WHERE name> ;="MySQL" and name< "MySQM"
Lastly, care should be taken to avoid letting MySQL do automatic type conversions in your queries, as the conversion process can also render indexes useless.