MySQL common test points for the second-level computer exam 8 MySQL database design optimization methods

8 optimization methods for MySQL database design, the details are as follows

1. Select the most suitable field attributes

MySQL can well support the storage and access of large amounts of data, but generally speaking, the smaller the table in the database, the faster the queries executed on it will be. Therefore, when creating a table, in order to obtain better performance, we can set the width of the fields in the table as small as possible. For example, when defining the postal code field, if you set it to CHAR(255), it will obviously add unnecessary space to the database. Even using the VARCHAR type is redundant because CHAR(6) can complete the task well. Likewise, 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 type. Because in MySQL, the ENUM type is treated as numeric data, and numeric data is processed much faster than text data. In this way, we can improve the performance of the database again.

2. Use JOIN instead of sub-queries

MySQL supports SQL subqueries starting from version 4.1. This technique uses a SELECT statement to create a single-column query result, and then uses this result 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 a subquery to first retrieve the customer IDs of all customers who have placed orders from the sales information table, and then pass the result to the main query, as shown below:

DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

Using subqueries can complete many SQL operations that logically require multiple steps to complete at one time, while also avoiding transaction or table locks, and is also easy to write. However, in some cases, a subquery can be replaced by a more efficient JOIN.. For example, suppose we want to retrieve all users who have no order records, we can use the following query to complete:

SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

If you use a JOIN to complete this query, the speed will be much faster. Especially when there is an index on CustomerID in the salesinfo table, 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

JOIN.. is more efficient because MySQL does not need to create a temporary table in memory to complete this logical two-step query.

3. Use UNION instead of manually created temporary tables

MySQL supports UNION queries starting from version 4.0, which can combine two or more SELECT queries that require the use of temporary tables into one query. When the client's query session ends, the temporary table will be automatically deleted to ensure that the database is neat and efficient. When using UNION to create a query, we only need to use UNION as the keyword to connect multiple SELECT statements. It should be noted that the number of fields in all SELECT statements must be the same. The following example demonstrates a query using UNION.

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product

4. Affairs

Although we can use sub-queries, joins, and unions to create a variety of queries, not all database operations can be completed with only one or a few SQL statements. More often than not, a series of statements are needed to complete a task. However, in this case, when a statement in this block fails to run, the operation of the entire block becomes uncertain. Imagine that you want to insert a certain data into two related tables at the same time. The following situation may occur: after the first table is successfully updated, an unexpected situation suddenly occurs in the database, causing the operation in the second table to not be completed. This will cause incomplete data or even damage the data in the database. To avoid this situation, you should use transactions, which have the following functions: either each statement in the statement block succeeds or fails. In other words, the consistency and integrity of the data in the database can be maintained. Transactions begin with the BEGIN keyword and end with the COMMIT keyword. If an SQL operation fails during this period, the ROLLBACK command can restore the database to the state before BEGIN started.

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 users with a safe access method by locking the database, thus ensuring that the user's operation is not interfered with by other users.

5. Lock table

Although transactions are a very good way to maintain database integrity, their exclusivity sometimes affects database performance, especially in large application systems. Since the database will be locked during the execution of the transaction, other user requests can only wait temporarily until the transaction ends. 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, such as accessing an e-commerce website, there will be serious response delays.

In fact, in some cases we can get better performance by locking the table. The following example uses the method of locking the table 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 retrieve the initial data, perform some calculations, and use an UPDATE statement to update the new values ​​into the table. The LOCK TABLE statement containing the WRITE keyword ensures that no other access to the inventory can insert, update, or delete data before the UNLOCK TABLES command is executed.

6. Use foreign keys

The locking table method can maintain the integrity of the data, but it cannot guarantee the relevance of the data. At this time we can use foreign keys. For example, a foreign key can ensure that each sales record points to an existing customer. Here, the foreign key can map the CustomerID in the customerinfo table to the CustomerID in the salesinfo table. Any record without a valid 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 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, remember to define the table type as a transaction-safe table InnoDB type when creating the table. This type is not the default type for MySQL tables. The definition method is to add TYPE=INNODB in the CREATE TABLE statement. As shown in the example.

7. Use indexes

Indexing is a common method to improve database performance. It allows the database server to retrieve specific rows much faster than without an index. This is especially true when the query statement contains commands such as MAX(), MIN(), and ORDER BY. So which fields should be indexed? Generally speaking, indexes should be created on fields that will be used for JOIN, WHERE judgments, and ORDER BY sorting. Try not to create an index on a field in your database that contains a large number of repeated values. For an ENUM type field, it is very likely that a large number of duplicate values ​​will appear, such as the "province" field in customerinfo. Creating an index on such a field will not help; on the contrary, it may also reduce database performance. We can create appropriate indexes at the same time when we create the table, or we can create indexes later using ALTER TABLE or CREATE INDEX. In addition, MySQL supports full-text indexing and searching starting from version 3.23.23. A full-text index in MySQL is a FULLTEXT type index, but it can only be used for MyISAM type tables. For a large database, loading the data into a table without a FULLTEXT index and then creating the index using ALTER TABLE or CREATE INDEX will be very fast. However, if you load 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 indexes can improve query speed, but if the SQL statement is not used properly, the index will not play its due role. Here are a few aspects that should be noted. First, it is best to compare fields of the same type. Prior to MySQL version 3.23, this was even a requirement. For example, you cannot compare an indexed INT field with a BIGINT field; however, as a special case, you can compare a CHAR type field and a VARCHAR type field when they have the same field size. Secondly, try not to use functions to operate on indexed fields.

For example, using the YEAE() function on a DATE type field will prevent the index from functioning properly. So, although the following two queries return the same results, 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 also occurs when calculating numeric fields:

SELECT * FROM inventory WHERE Amount/7＜24;
SELECT * FROM inventory WHERE Amount＜24*7;

The two queries above also return the same results, but the latter query will be much faster than the former one. Third, when searching character fields, we sometimes use the LIKE keyword and wildcards. Although this approach is simple, it also comes at the expense of system performance. For example, the following query will compare every record in the table.

SELECT * FROM books
WHERE name like "MySQL%"

But if you use the following query instead, the results returned are the same, but the speed is much faster:

SELECT * FROM books
WHERE name＞="MySQL"and name＜"MySQM"

Finally, you should be careful to avoid letting MySQL perform automatic type conversions in your queries, because the conversion process can also render indexes ineffective.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.