Shulou(Shulou.com)06/01 Report--

This article uses simple and easy-to-understand examples to introduce php database optimization methods, code is very detailed, interested partners can refer to, hope to help you.

php Optimize database methods: 1. Select the most applicable field attributes;2. Use join JOIN instead of subquery "Sub-Queries";3. Use union "UNION" instead of manually created temporary tables;4. Use foreign keys;5. Use indexes;6. Use optimized query statements.

This paper discusses the idea of improving MySQL database performance, and gives concrete solutions from 8 aspects.

1. Select the most applicable field attributes

MySQL works well for large data volumes, but in general, the smaller the table in the database, the faster queries 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 for better performance. For example, when defining the field ZIP code, if you set it to CHAR(255), you obviously add unnecessary space to the database, and even using VARCHAR is redundant, because CHAR(6) can do the job well. Similarly, we should use MEDIUMINT instead of BIGIN to define integer fields if possible.

Another way to improve efficiency is to set fields to NOT NULL whenever possible, so that the database does not have to compare NULL values in future queries.

For some text fields, such as "province" or "gender," we can define them as ENUM types. Because ENUM types are treated as numeric data in MySQL, numeric data is processed much faster than text types. In this way, we can improve the performance of the database.

Use JOIN instead of Sub-queries

MySQL has supported SQL subqueries since 4.1. This technique uses SELECT statements to create a single-column query result, which can then be used as a filter condition in another query. For example, if we want to delete customers who do not have any orders in the customer basic information table, we can use sub-query to extract all customer IDs that issue orders from the sales information table, and then pass the results to the main query, as shown below:

DELETE FROM customerinfoWHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

Using subqueries allows you to complete many SQL operations that logically require multiple steps at once, avoid transaction or table locks, and are easy to write. However, there are cases where subqueries can be joined more efficiently. Substitution. For example, suppose we want to extract all users who do not have an order record, we can do this with the following query:

SELECT * FROM customerinfoWHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

If JOIN is used.. to complete this query, much faster. Especially if there is an index on CustomerID in the salesinfo table, the performance will be better. The query is as follows:

SELECT * FROM customerinfoLEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerIDWHERE salesinfo.CustomerID IS NULL

JOIN.. It's more efficient because MySQL doesn't need to create temporary tables in memory to complete this logical two-step query.

Use union instead of manually created temporary tables

MySQL has supported UNION queries since version 4.0, which combines two or more SELECT queries that require temporary tables into one query. Temporary tables are automatically deleted at the end of the client query session, thus keeping the database clean and efficient. When using UNION to create a query, we only need to use UNION as a keyword to connect multiple SELECT statements. It is important to note 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 clientUNIONSELECT Name, BirthDate FROM authorUNIONSELECT Name, Supplier FROM product

4. Business

Although we can use Sub-Queries, JOINs, and UNION to create a wide variety of queries, not all database operations can be done with one or a few SQL statements. More often than not, a series of sentences are needed to accomplish some kind of work. But in this case, when one of the statements in the block fails, the operation of the entire block becomes uncertain. Imagine that to insert a certain data into two associated tables at the same time, there may be such a situation: after a successful update in the first table, the database suddenly appears unexpected, causing the operation in the second table not to be completed, which will cause incomplete data or even destroy the data in the database. To avoid this, you should use transactions, which either succeed or fail every 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 end with the COMMIT keyword. If an SQL operation fails in between, ROLLBACK restores the database to the state it was in before BEGIN began.

BEGIN;INSERT INTO salesinfo SET CustomerID=14;UPDATE inventory SET Quantity=11 WHERE item='book';COMMIT;

Another important function of transactions is that when multiple users use the same data source at the same time, it can provide a secure access mode for users by locking the database, which can ensure that users 'operations are not interfered by other users.

5. Lock table

Although transactions are a good way to maintain database integrity, they can sometimes affect database performance because of their exclusivity, especially in large application systems. Because the database will be locked during the transaction, other user requests will have to wait until the transaction ends. If a database system is used by only a few users, the impact of transactions is not a big problem; but if thousands of users access a database system simultaneously, such as an e-commerce website, there will be significant response delays.

In fact, there are cases where we can get better performance by locking tables. The following example performs the function of the transaction in the previous example by locking the table.

LOCK TABLE inventory WRITESELECT Quantity FROM inventory WHEREItem='book';... UPDATE inventory SET Quantity=11 WHEREItem='book';UNLOCK TABLES

Here, we take the initial data with a SELECT statement, and update the new value to the table with an UPDATE statement after some calculations. The LOCK TABLE statement with the WRITE keyword ensures that no other access will insert, update, or delete the inventory until the LOCK TABLES command is executed.

6. Use foreign keys

The method of locking tables maintains data integrity, but it does not guarantee data associativity. At this point we can use foreign keys. For example, a foreign key ensures that every sales record points to an existing customer. Here, the foreign key maps CustomerIDs in the customerinfo table to CustomerIDs in the salesinfo table, and any record without a valid CustomerID is not 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 associated with that customer in the salesinfo table are automatically deleted. If you want to use foreign keys in MySQL, remember to define the type of the table as the transaction-safe table InnoDB type when you create it. 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 of indexes

Indexing is a common way to improve database performance by allowing the database server to retrieve specific rows much faster than without indexing, especially when the query statement contains MAX(), MIN(), and ORDER BY commands. Which fields should be indexed? In general, indexes should be built on fields that will be used for JOIN, WHERE, and ORDER BY sorting. Try not to index a field in the database that contains a large number of duplicate values. For an ENUM field, it is possible to have a large number of duplicate values, such as "province" in customerinfo. Field, indexing on such fields will not help; on the contrary, it may degrade database performance. We can create the appropriate index at the same time we create the table, or we can create the index later using ALTER TABLE or CREATE INDEX. MySQL also supports full-text indexing and search starting with version 3.23.23. A full-text index is a FULLTEXT type index in MySQL, but can only be used with tables of type MyISAM. For a large database, loading data into a table without a FULLTEXT index and then creating an index using ALTER TABLE or CREATE INDEX is very fast. However, if you load data into a table that already has a FULLTEXT index, execution will be slow.

8. Optimized query statements

In most cases, using an index can speed up queries, but if the SQL statement is used incorrectly, the index will not work as it should. Here are a few things to watch out for. First, it's best to compare fields of the same type. Before MySQL version 3.23, this was even a required condition. For example, you cannot compare an indexed INT field to a BIGINT field; however, as a special case, you can compare CHAR and VARCHAR fields if they have the same field size. Second, try not to use functions on fields with indexes.

For example, using the YEAE() function on a field of type DATE will prevent the index from functioning properly. So, the following two queries return the same results, but the latter is much faster than the former.

SELECT * FROM order WHERE YEAR(OrderDate)

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.