MYSQL Optimization Common method (reprint)

1. Select the most applicable field properties
MySQL is good at supporting large data volumes, but in general, the smaller the tables in the database, the faster queries are executed on it. Therefore, in order to achieve better performance when creating a table, we can set the width of the fields in the table as small as possible. For example, when you define a postal code field, if you set it to char (255), you obviously add unnecessary space to the database, and even this type of varchar is redundant, because char (6) can do a good job. Similarly, if possible, we should use Mediumint instead of Bigin to define integer fields.
Another way to improve efficiency is to set the field to not null whenever possible, so that the database does not have 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, enum types are 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 connection (join) instead of subquery (sub-queries)
MySQL supports SQL subquery starting from 4.1. This technique can use the SELECT statement to create a single-column query result, and then use the result as a filter for another query. For example, if we want to delete a customer who does not have any orders in the customer's basic information table, we can take advantage of the subquery to remove all the customer IDs 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)
The use of subqueries allows you to do a lot of SQL operations that logically require multiple steps to complete, as well as to avoid transactions or table locks and write them easily. However, in some cases, subqueries can be more efficiently connected (join). Alternative. For example, suppose we want to take out all the users without an order record, which can be done with the following query:
SELECT * FROM CustomerInfo WHERE CustomerID not in (SELECT CustomerID from Salesinfo)
If you use Connection (join): To complete this query work, the speed will be much faster. Especially if the Salesinfo table in the CustomerID index, the performance will be better, query 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 a temporary table in memory to complete this logical two-step query effort.
3. Use Union (Union) instead of manually created temporary table
MySQL supports UNION queries starting with version 4.0, which can combine two or more SELECT queries that need to use temporary tables in a single query. At the end of the client's query session, the temporary table is automatically deleted, ensuring that the database is neat and efficient. When using union to create a query, we only need to concatenate multiple SELECT statements with union as a keyword, and be aware that the number of fields in all SELECT statements is the same. The following example shows a query that uses union.
Select name, Phone from client UNION SELECT name, BirthDate from author
UNION
SELECT Name, Supplier from product
4. Business
Although we can use subqueries (sub-queries), join, and Union (union) to create a wide variety of queries, not all database operations can be done with one or a few SQL statements. More often it is necessary to use a series of statements to accomplish some kind of work. But in this case, when one of the statements in this block of statements runs out of error, the operation of the entire statement block becomes indeterminate. Imagine that, to insert a data into two associated tables at the same time, it may be that the first table in the successful update, the database suddenly unexpected situation, resulting in the operation of the second table is not completed, which will result in incomplete data, and even destroy the data in the database. To avoid this situation, you should use a transaction that either succeeds or fails for each statement in the statement block. In other words, the consistency and integrity of the data in the database can be maintained. Things begin with the BEGIN keyword, and the commit keyword ends. When a SQL operation fails, the rollback command can restore the database to the state it was in before begin.
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 secure way to access, so as to ensure that the user's operation is not disturbed by other users.
5. Lock the table
Although a transaction is a good way to maintain database integrity, it can sometimes affect the performance of a database because of its exclusivity, especially in large application systems. Because the database will be locked during the execution of the transaction, other user requests can only be temporarily waited until the transaction ends. If a database system has only a few users
, the impact of a transaction does not become too big a problem, but assuming that thousands of users are accessing a database system at the same time, such as accessing an e-commerce site, there is a more severe response delay.
In fact, there are cases where we can get better performance by locking the table. The following example uses the Lock table method to complete the function 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 use a SELECT statement to remove the initial data and, with some calculations, update the new value to the table with the UPDATE statement. The LOCK TABLE statement, which contains the WRITE keyword, guarantees that there will be no additional access to insert, update, or delete the inventory until 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 does not guarantee the data association. At this point we can use foreign keys. For example, a foreign key can guarantee that each sales record points to an existing customer. In this case, the foreign key can map the CustomerID in the CustomerInfo table to the CustomerID in the Salesinfo table, and any record with no 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 guarantees that when a customer record in the CustomerInfo table is deleted, all records related to that customer in the Salesinfo table will be automatically deleted. If you want to use a foreign key in MySQL, be sure to remember to define the table's type as the Transaction safe table InnoDB type when you create the table. This type is not the default type for MySQL tables. The method defined is to add Type=innodb to the CREATE TABLE statement. As shown in the example.
7. Use Index
Indexes are a common method of improving database performance, which allows the database server to retrieve specific rows at a much faster rate than without indexes, especially when the query contains Max (), MIN (), and the order of these commands. Which fields should be indexed? In general, indexes should be built on fields that will be used for join, where judgment, and order by ordering. Try not to index a field in the database that contains a large number of duplicate values. For a field of an enum type, it is possible to have a large number of duplicate values, such as "Province" in CustomerInfo. field, indexing on such a field will not help, and conversely, it may reduce the performance of the database. We can create the appropriate index at the same time when we create the table, or you can create an index at a later time using ALTER TABLE or CREATE INDEX. In addition, MySQL
Full-text indexing and searching is supported starting from version 3.23.23. Full-text indexing is a fulltext type index in MySQL, but can only be used for tables of type MyISAM. For a large database, it is very fast to load the data into a table that does not have a fulltext index, and then use ALTER TABLE or CREATE INDEX. However, if you load the 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 an index can improve the speed of the query, but if the SQL statement is used inappropriately, the index will not function as it should. Here are a few things to be aware of. First, it's best to compare between fields of the same type. This is even a required condition before MySQL version 3.23. For example, an indexed int field and a bigint field cannot be compared, but as a special case, they can be compared when the fields of the char type and the varchar type fields are of the same size. Second, try not to use functions to work with indexed fields.
For example, when you use the Yeae () function on a field of a date type, the index will not function as it should. As a result, the following two queries return the same results, 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 situation can occur when a numeric field is calculated:
SELECT * FROM inventory WHERE amount/7<24;
SELECT * FROM inventory WHERE amount<24*7;
The two queries above also return the same result, but the subsequent query will be much faster than the previous one. Third, when searching for character fields, we sometimes use the LIKE keyword and wildcard characters, which is simple, but at the expense of system performance. For example, the following query will compare each record in the table.
SELECT * FROM Books
WHERE name like "mysql%"
However, if you switch to the following query, the result is the same, but the speed will be much faster:
SELECT * FROM Books
WHERE name>= "MySQL" and name< "mysqm"
Finally, it is important to avoid having MySQL perform automatic type conversions in queries, because the conversion process also makes the index ineffective.
For more information, refer to: http://www.jb51.net/article/18934.htm

MYSQL Optimization Common method (reprint)