Experience summary of optimizing MYSQL database _mysql

1, selecting the most applicable field properties
MySQL can support large amount of data access, but generally speaking, the smaller the table in the database, the faster the query 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 this field for a postal code, setting it to char (255) obviously adds unnecessary space to the database, and even the use of varchar is redundant because char (6) completes the task well. Similarly, if possible, we should use Mediumint instead of bigin to define an integral field.
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 the query is executed in the future.
for some text fields, such as "provinces" 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 type. In this way, we can improve the performance of the database.

2, using a connection (join) instead of a subquery (sub-queries)
MySQL supports SQL subquery starting from 4.1. This technique can use a SELECT statement to create a single column of query results, and then use the result as a filter condition in another query. For example, to delete a customer with no orders in the Customer profile table, you can use a 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)
use subqueries to perform a number of SQL operations that logically require multiple steps to complete, as well as avoid transactions or table locks, and are easy to write. In some cases, however, subqueries can be more efficiently connected (join) ... Alternative. For example, suppose that we want to remove all users who do not have an order record, we can do this with the following query:
select * from CustomerInfo WHERE CustomerID is not in (select CustomerID from Salesinfo)
If you use a connection (join) ... To complete this query work, the speed will be much faster. In particular, the performance will be better if the CustomerID is indexed in the Salesinfo table, and the query is as follows:
SELECT * from CustomerInfo left JOIN Salesinfoon customerinfo. Customerid=salesinfo. CustomerID WHERE Salesinfo. CustomerID is NULL
connection (join) ... It is more efficient because MySQL does not need to create temporary tables in memory to complete this logical two-step query effort.

3. Use Union (Union) instead of manually created temporary tables
MySQL supports the UNION query from version 4.0, which can combine a query that requires two or more SELECT queries that require the use of temporary tables. At the end of the client's query session, temporary tables are automatically deleted, ensuring that the database is neat and efficient. When we use union to create a query, we just need to use union as a keyword to connect multiple SELECT statements, and note that the number of fields in all SELECT statements is the same. The following example shows a query that uses union.

Copy Code code as follows:

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), joins (join), and Union (union) to create a wide variety of queries, not all database operations can be done with just one or a few SQL statements. More often, you need to use a series of statements to accomplish a certain kind of work. In this case, however, when one of the statements in the statement block runs in error, the entire statement block becomes indeterminate. Imagine inserting a data into two linked tables at the same time. This can happen when a successful update in the first table surprises the database, causing the operation in the second table to be incomplete, which can result in incomplete data and even damage to the data in the database. To avoid this, 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. A SQL operation between this fails, then the rollback command can restore the database to the state before the start of begin.

Copy Code code as follows:

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 a secure way for users to access, thus ensuring that the user's actions are not interfered by other users.

5. Lock the table
While transactions are a great way to maintain database integrity, they can sometimes affect database performance, especially in large applications, because of its exclusivity. Because 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 has only a few users to use, the impact of the transaction will not be a big problem, but assuming that tens of thousands of users simultaneously access a database system, such as access to an E-commerce site, will have a more severe response latency.
In fact, in some cases we can get better performance by locking the table. The following example uses a locking table to perform the function of the transaction in the previous example.

Copy Code code as follows:

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 take out the initial data and, with some calculations, update the new value to the table with the UPDATE statement. A LOCK TABLE statement with the WRITE keyword guarantees that there will be no additional accesses to insert, UPDATE, or delete the inventory until the UNLOCK TABLES command is executed.

6. Use FOREIGN key
The method of locking the table can maintain the integrity of the data, but it does not guarantee the relevance of the data. We can use foreign keys at this time. For example, a foreign key ensures 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 Salesinfo table CustomerID, and any record without a valid CustomerID will not be updated or inserted into the salesinfo.

Copy Code code as follows:

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 all records associated with the customer in the Salesinfo table are automatically deleted when a customer record in the CustomerInfo table is deleted. If you want to use a foreign key in MySQL, be sure to remember to define the table type as the Transaction security 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
Indexing is a common way to improve 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 order orders. Which fields should be indexed? Generally, indexes should be based on fields that will be used for joins, where judgments, and order by ordering. Try not to index a field that contains a large number of duplicate values in the database. For fields of an enum type, it is very likely that a large number of duplicate values occur, such as "Province" in CustomerInfo. field, indexing on such a field will not help, and conversely, it may degrade the performance of the database. When we create a table, we can create the appropriate index at the same time, or use ALTER TABLE or CREATE INDEX to create the index at a later time. In addition, MySQL

Supports Full-text indexing and searching starting with version 3.23.23. Full-text indexing is a fulltext type index in MySQL, but can only be used for MYISAM types of tables. For a large database, it is very quick to load the data into a table without a Fulltext index, and then create the index using 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
Most of the time, using indexes can increase the speed of queries, but if the SQL statement is not used properly, the index will not play its part. Here are a few things to be aware of. First, it is best to compare operations between fields of the same type. Before MySQL version 3.23, this was even a necessary condition. For example, you cannot compare an int field that is indexed and a bigint field, but as a special case, you can compare the fields of the char type with the field size of the varchar type field. Second, do not use functions to operate on indexed fields.

For example, when you use the Yeae () function on a field of type date, the index will not play its role. So, the following two queries, though returning the same result, are much faster than the former.
SELECT * from order WHERE year (OrderDate) <2001
SELECT * FROM order WHERE orderdate< "2001-01-01";
The same situation occurs when a numeric field is evaluated:
SELECT * from inventory WHERE amount/7<24;
SELECT * FROM inventory WHERE amount<24*7; The two queries above
also return the same results, but subsequent queries will be much faster than the one in front. Third, when searching for character fields, we sometimes use the LIKE keyword and wildcard characters, which, while simple, are 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%"
but if you use 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, care should be taken to avoid automatic type conversion in the query, because the conversion process also makes the index ineffective.