Optimization method of MySQL database program

Optimization method of MySQL database program
1, select the most applicable field properties

MySQL can support a 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 executing the query 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, use the connection (join) to replace the subquery (sub-queries)

MySQL supports SQL subquery starting with 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 isn't in (SELECT CustomerID from Salesinfo)

You can use subqueries to perform a lot of SQL operations that logically require multiple steps to complete, while avoiding transactions or table locks and writing easily. In some cases, however, subqueries can be more efficiently connected (join) ... Alternative. For example, suppose we want to take out all the users who have no order records, and we can do this with the following query:

SELECT * FROM CustomerInfo WHERE CustomerID isn't in (select CustomerID from Salesinfo)

If you use a connection (join) ... To complete this query work, the speed will be much faster. Especially when the Salesinfo table has indexed the CustomerID, 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

Connect (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.

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.

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.

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.

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, the optimized query statement

In most cases, indexing can increase the speed of a query, 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 date type, 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 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 following query 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"

1: Adjust the server parameters
　　
Use shell>mysqld-help This command sound factory a table of all MySQL options and configurable variables. Output The following information:
　　
Possible variables for option--set-variable (-O) are:
　　
Back_log current Value:5//requires the number of connections that MySQL can have. Back_log indicates how many connection requests can be in the stack when MySQL pauses to accept connections
　　
Connect_timeout current Value:5//mysql server waits for a connection before it is answered with bad handshake (poor translation)
　　
Delayed_insert_timeout current value:200//An insert delayed waits for an insert before terminating
　　
Delayed_insert_limit current VALUE:50//insert delayed processor will check that any SELECT statements are not executed and, if so, execute them before continuing
　　
Delayed_queue_size current value:1000//How many teams are allocated for insert delayed
　　
Flush_time Current value:0//If set to non 0, then every flush_time time, all tables are closed
　　
Interactive_timeout current value:28800//server wait time on Ocean Interactive connection before closing it
　　
Join_buffer_size current value:131072//buffer size with all connections
　　
Key_buffer_size current value:1048540//term the size of the buffer of the index block, increasing it to handle the index better
　　
Lower_case_table_names Current value:0//
　　
Long_query_time Current Value:10//If a query takes longer than this time, the slow_queried count will increase
　　
Max_allowed_packet current value:1048576//size of a package
　　
Max_connections current value:300//number of simultaneous connections allowed
　　
Max_connect_errors Current VALUE:10//If there are more than this number of interrupt connections, will block further connections, you can use the flush hosts to resolve
　　
Max_delayed_threads current VALUE:15//The number of processing insert delayed that can be started
　　
Max_heap_table_size Current value:16777216//
　　
Max_join_size current value:4294967295//number of connections allowed to read
　　
Max_sort_length current value:1024//number of bytes used in the sort blob or text
　　
Max_tmp_tables current VALUE:32//number of temporary tables open at the same time for a connection
　　
Max_write_lock_count current value:4294967295//Specify a value (usually very small) to start the mysqld so that a read lock appears after a certain number of write locks
　　
Net_buffer_length current value:16384//communication buffer size-reset to that size at query time
　　
Query_buffer_size current value:0//query-time buffer size
　　
Record_buffer current value:131072//the size of the buffer allocated for each table scanned by the connection for each sequential scan
　　
Sort_buffer current value:2097116//the size of the buffer allocated for each of the sorted connections
　　
Table_cache current value:64//number of tables opened for all connections
　　
Thread_concurrency Current Value:10//
　　
Tmp_table_size current value:1048576//Temp Table size
　　
Thread_stack current value:131072//size of each thread
　　
Wait_timeout current value:28800//server waits on a connection before it closes 3
　　
Configuring the above information according to your own needs will help you.