This article explores ideas for improving MySQL database performance and provides specific solutions from 8 aspects.
1. Select the most applicable field attributes.
MySQL can well support the 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) is fine. Mission accomplished. Likewise, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields.
Another way to improve efficiency is to set fields to NOT NULL when possible, so that the database does not need 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 types. Because in MySQL, the ENUM type is treated as numeric data, and numeric data is processed much faster than text types. In this way, we can improve the performance of the database.
2. Use joins (JOIN) instead of sub-queries (Sub-Queries).
MySQL supports SQL sub-queries starting from 4.1. This technique allows you to use a SELECT statement to create a single column of query results, and then use 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 basic customer information table, we can use a subquery to first retrieve the IDs of all customers who issued orders from the sales information table, and then pass the results 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. It can also avoid transaction or table locks, and it is also easy to write. However, in some cases, subqueries can be replaced by more efficient joins (JOIN).. For example, suppose we want to retrieve all users who do not have order records, we can use the following query:
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
If you use connection (JOIN).. to complete this query, the speed will be 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 customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
Set the data path and place the database data files in the shared NFS directory (NAS server).
The PID and innioDB files must be placed in the local directory of the server to start and stop the service normally:
1125 vi /etc/ my.cnf
[mysqld]
#Where to install a database data
datadir=/data/mysqldata
#Where to install a innoDB engine
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir= /usr/local/mysql/data
innodb_data_file_path=ibdata1: 50M;ibdata2:50M:autoextend
1106 cp ./support-files/mysql.server /etc/rc.d/init.d/
vi /etc/rc.d/init.d/mysql.server
Compile related items starting from 222nd The two lines of , place the PID file in the local directory of the server:
pid_file=/usr/local/mysql/data/mysqlmanager-`/bin/hostname`.pid
server_pid_file=/usr/local/mysql/data/`/bin/ hostname`.pid
installs the basic database of MySQL:
1123 mount 10.4.66.251:/data /data
1124 mkdir /data/mysqldata
1127 ./scripts/mysql_install_db --user=mysql
1145 chown -R mysql.mysql /data/mysqldata/
if If normal, you can see that mysql starts normally;
1146 /etc/rc.d/init.d/mysql.server start
1146 /etc/rc.d/init.d/mysql.server stop
configure HA high availability, do not set it NFS mount and mysql service run automatically when starting the server;
5. Locking tables
Although transactions are a very good way to maintain the integrity of the database, because of its exclusivity, it sometimes affects the performance of the database, especially in very long periods of time. in large application systems. Since 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 is used by only a few users, the impact of transactions will not become a big problem; but if thousands of users access a database system at the same time, such as accessing an e-commerce website, it will cause Serious response delay.
In fact, in some cases we can obtain better performance by locking the table. The following example uses the lock table method to complete the transaction function 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, and through some calculations, use an UPDATE statement to update the new values into the table. The LOCK TABLE statement containing the WRITE keyword ensures that there will be no other access to the inventory to insert, update, or delete before the UNLOCK TABLES command is executed.
6.
The method of using foreign keys to lock the table 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 the customer in the salesinfo table will also be automatically deleted. If you want to use foreign keys in MySQL, you must remember to define the table type as a transaction-safe InnoDB type when creating the table. This type is not the default type for MySQL tables. The defined method is to add TYPE=INNODB to the CREATE TABLE statement. As shown in the example.
7. Using indexes
is a common method to improve database performance. It allows the database server to retrieve specific rows much faster than without an index, especially if the query statement contains MAX(), MIN() and ORDERBY. When executing the command, the performance improvement is more obvious. So which fields should be indexed? Generally speaking, indexes should be built on fields that will be used for JOIN, WHERE judgment and ORDER BY sorting. Try not to index a field in the database that contains a large number of duplicate values. For an ENUM type field, it is very possible to have a large number of duplicate values, such as the "province".. field in customerinfo. Building an index on such a field will not be helpful; on the contrary, it is possible Reduce database performance. We can create appropriate indexes at the same time when creating the table, or we can use ALTER TABLE or CREATE INDEX to create indexes later. In addition, MySQL
Full-text indexing and search are supported starting with version 3.23.23. The full-text index is a FULLTEXT type index in MySQL, but it can only be used for MyISAM type tables. For a large database, it will be very fast to load the data into a table without a FULLTEXT index and then use ALTER TABLE or CREATE INDEX to create the index. But 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 SQL statements are used improperly, the index will not be able to play its due role. The following are several aspects that should be paid attention to. First, it is best to perform comparison operations between fields of the same type. Prior to MySQL version 3.23, this was even a required condition. For example, an indexed INT field cannot be compared with a BIGINT field; but as a special case, when a CHAR type field and a VARCHAR type field have the same size, they can be compared. Secondly, try not to use functions to operate on indexed fields.
For example, when using the YEAE() function on a DATE type field, the index will not function as it should. Therefore, 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 will also occur when calculating numeric fields:
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
The above two queries also return the same results, but the latter query will be much faster than the previous one. Third, when searching for character fields, we sometimes use LIKE keywords 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, the returned results 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 conversion in the query, because the conversion process will also make the index ineffective.