Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the methods commonly used in mysql optimization. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Share several common ways to optimize MySQL data. Select the most appropriate field property

1. MySQL can well support a large amount of data access, but generally speaking, the smaller the table in the database, the faster the query speed. Therefore, when building a table, in order to achieve better performance, the length of the fields in the table can be set as small as possible. 2. Set the field to NOT NULL as much as possible, so that the database does not have to compare null values when executing the query. 3. For some text fields, such as "gender" or "nationality", we can use enum to define them. MySQL treats data of type enum as numeric, and numeric data is processed much faster than text. In this way we can improve the performance of the database. Replace subqueries with joins (JOIN) Yes (sub-Queries)

MySQL has supported subqueries of SQL since 4. 1. This technique can use select statements to create a single-column query result, and then use that result as a filter condition in another query. Example: delete DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo) from the customer basic information table without any orders. Use the subquery to extract all the customer ID that placed the order from the sales information table, and then pass the results to the main query. Using subqueries can accomplish many SQL operations that logically require multiple steps to complete at one time, avoid transaction or table locking, and are easy to write. However, in some cases, subqueries can be replaced by more efficient JOIN. Example: take out all users who do not have an order record SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo) it will be very fast if you use JOIN to complete this query. Especially if CustomerID is indexed in the salesinfo table, the performance will be better: SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID WHERE salesinfo.CustomerID ISNULL

Swift Code

JOIN is more efficient because MySQL does not need to create temporary tables in memory to complete this logically required two-step query. Use union instead of manually created temporary tables

Union queries can combine two or more select queries that need to use temporary tables into a single query. At the end of the client query session, the temporary table will be deleted automatically, thus ensuring the tidiness and efficiency of the database. When using union to create a query, you only need to use union as the keyword to join multiple select statements (all select statements have the same number of fields) SELECT Name,Phone FROM client UNIONSELECT Name,BirthDate FROM author UNIONSELECT Name,Supplier FROM product transactions

Although we can use subqueries (Sub-Queries), JOIN (join), and UNION (UNION) to create a variety of queries, not all database operations can be done with one or a few SQL statements. More often, you need to use a series of statements to accomplish some kind of work. But in this case, when one of the statements in this block goes wrong, the operation of the whole block becomes uncertain. Imagine that if you want to insert some data into two associated tables at the same time, it may happen that after a successful update in the first table, there is a sudden unexpected condition in the database, resulting in the operation in the second table not being completed, thus resulting in incomplete data and even destroying the data in the database. To avoid this, you should use transactions, which either succeed or fail for every statement in the block. In other words, you can maintain the consistency and integrity of the data in the database. Things start with the BEGIN keyword and end with the COMMIT keyword. If a SQL operation fails in between, the ROLLBACK command can restore the database to the state it was before BEGIN started. Another important role of BEGIN; INSERT INTO salesinfo SET CustomerID=14; UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT; 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 of access, which can ensure that the operation of users will not be disturbed by other users. Lock table

Although transaction is a very good way to maintain the integrity of the database, it sometimes affects the performance of the database because of its independence, especially in large application systems, because the database will be locked during the execution of the transaction, so other user requests can only wait temporarily until the end of the transaction. If a database system is used by only a few users, the impact of transactions will not be a big problem; but if there are thousands of users accessing a database system at the same time, there will be serious corresponding delays. In fact, in some cases, better performance can be achieved by locking the table.

Interest rate resolution http://www.gendan5.com/decision/usd.html

LOCKTABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';...UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES here, we use a select statement to take the initial data, and through some calculations, use the update statement to update the new value into the table. A LOCKTABLE statement that contains the WRITE keyword ensures that there will be no other access to insert, update, or delete the inventory until the UNLOCKTABLES command is executed. Use foreign key

The method of locking the table can maintain the integrity of the data, but it cannot guarantee the relevance of the data. Foreign keys can be used at this time. For example, a foreign key can ensure that each sales record points to an existing customer. Foreign keys can map the CustomerID in the customerinfo table to the CustomerID in the salesinfo table, and any record without a legal CustomerID will not be updated or inserted into the salesinfo. CREATE TABLE customerinfo (CustomerIDINT NOTNULL,PRIMARYKEY (CustomerID)) TYPE=INNODB;CREATE TABLE salesinfo (SalesIDNT NOTNULL, CustomerIDINT NOTNULL,PRIMARYKEY (CustomerID,SalesID), FOREIGNKEY (CustomerID) REFERENCES customerinfo (CustomerID) ON DELETE CASCADE) TYPE=INNODB; pay attention to 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 that customer in the salesinfo table will also be automatically deleted. If you want to use foreign keys in MySQL, be sure to 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. The defined method is to add the TYPE=INNODB usage index to the CREATETABLE statement

Indexing is a common way to improve database performance, which enables the database server to retrieve specific rows much faster than without an index, especially when the query contains commands such as MAX (), MIN () and ORDERBY. In general, the index should be based on the fields that will be used for JOIN,WHERE judgment and ORDERBY sorting. Try not to index a field in the database that contains a large number of duplicate values. For a field of type ENUM, 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; on the contrary, it may degrade the performance of the database. We can create the appropriate index at the same time as we create the table, or we can use ALTERTABLE or CREATEINDEX to create the index later. In addition, MySQL supports full-text indexing and search since version 3.23.23. A full-text index is an index of type FULLTEXT in MySQL, but can only be used for tables of type MyISAM. For a large database, it would be very fast to load the data into a table without an FULLTEXT index, and then create an index using ALTERTABLE or CREATEINDEX. However, if you load the data into a table that already has an FULLTEXT index, the execution process will be very slow. Optimize query statement

In most cases, using an index can improve the speed of the query, but if the SQL statement is not used properly, the index will not play its due role. The operation of comparing fields of the same type.

Try not to use functions on indexed fields.

For example, using the yeae () function on a field of type date will prevent the index from working as it should. 3. When searching for character fields, we sometimes use like keywords and wildcards, which is simple, but at the expense of system performance. The query speed of the latter is obviously much faster than the former. SELECT * FROM books WHERE name like "MySQL%" SELECT * FROM books WHERE name > = "MySQL" andname < "MySQM" finally, care should be taken to avoid automatic type casting by MySQL in the query, because the conversion process can also render the index ineffective.

These are the commonly used methods of mysql optimization shared by the editor. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.