Database has become an indispensable underlying dependency for Internet applications, among which MySQL has been more widely used as an open source database. Recently, I have been focusing on the development of project projects. I have summarized some database optimization principles used in the development process, hoping to help more application developers better use the MySQL database.
The optimization of MySQL mainly includes three aspects. The first is the optimization of SQL statements, and the second is the optimization of the table structure. This mainly refers to the optimization of indexes, and finally the optimization of server configuration.
1. SQLOptimization of statements
1) Query statements should try to avoid full table scans, first of all Consider creating indexes on the Where clause and the OrderBy clause, but each SQL statement will only use one index at most, and creating too many indexes will It brings overhead during insertion and update. At the same time, for fields with little distinction, you should try to avoid indexing. You can use the explain keyword before the query statement to view the SQL statement. Execution plan to determine whether the query statement uses an index;
2) Should try to use EXIST and NOT EXISTReplace IN and NOT IN, because the latter is likely to cause the full table scan to abandon the use of indexes;
3) Try to avoid NULL judgment on fields in the Where clause, because NULL judgment will lead to a full table scan;
4) You should try to avoid using or as a connection condition in the Where clause, because it will also lead to a full table scan;
5) should be avoided in the Where clause! The = or <> operator will also cause a full table scan;
6) Using like “%abc%” or like “%abc” will also result in a full table scan, and like “ abc%”will use index.
7) When using the Union operator, you should consider whether you can use Union ALL instead, because When the Union operator merges results, it will sort the results and delete duplicate records. For applications that do not have this requirement, Union ALL should be used, the latter just The results are merged and returned, which can greatly improve performance;
8) Try to avoid using expression operators in the Where clause. Because it will cause a full table scan;
9) You should try to avoid using functions on fields in the Where clause, because it will also cause Full table scan
10) Select Try to avoid using "*" in the statement , because in the process of parsing the SQL statement, "*" will be converted into the column names of all columns, and this work is completed by querying the data dictionary, there is a certain Overhead;
11) In the Where clause, the table connection condition should be written before other conditions, because the parsing of the Where clause is From back to front, so try to put the restrictions that can filter out most records at the end of the Where clause;
12)If there is a joint index such as index(a,b,c) on the database table, the order of appearance of the condition fields in the Where clause should be the same as the order of appearance of the index fields Consistent, otherwise the joint index cannot be used;
13) The order of appearance of the tables in the From clause will also affect the SQL statement. Execution performance is affected. The From clause is parsed from back to front, that is, the table written at the end will be processed first. The table with fewer records should be selected as the base table and placed at the end. If there are 3 and 3 or more table connection queries, the cross table should be used as the base table;
14) Try to use the >= operator instead of the > operator, for example, the following SQL statement, select dbInstanceIdentifier from DBInstance where id > 3, this statement should be replaced with select dbInstanceIdentifier from DBInstance where id >=4 , the execution results of the two statements are the same, but the performance is different , the latter is more efficient, because when the former is executed, it will first find the record equal to 3, and then scan forward, while the latter directly locates the record equal to 4.
2. Optimization of table structure
This mainly refers to how to correctly create indexes, because unreasonable indexes will lead to querying the entire table Scanning, and too many indexes will bring performance overhead for insertion and update;
1) First of all, we must clarify each itemSQL Statements can only use one index at most. If there are multiple indexes that can be used, the system will select an index for execution based on the execution cost;
2) For the Innodb table, although the system will automatically generate a primary key column if the user does not specify a primary key, the automatically generated primary key column has multiple problems1. Insufficient performance, cannot be read using cache; 2. Insufficient concurrency, all tables without primary key in the system share a global Auto_Increment column. Therefore, all tables in InnoDB must specify the primary key when creating the table.
3) Do not create indexes for fields that are not very distinctive;
4) You only need to build an index for a field, there is no need to create a unique index and create an INDEX index.
5) For large text fields or BLOB fields, do not create indexes;
6)The connection field of the connection query should be indexed;
7)The sorting field generally needs to be indexed;
8)Group statistical fields generally need to be indexed;
9)Use joint indexes correctly, The first field of the joint index can be used alone. For example, the following joint index index(userID,dbInstanceID), The following query statement can use this index, select dbInstanceIdentifier from DBInstance where userID=? , but the statement select dbInstanceIdentifier from DBInstance where dbInstanceID=? cannot use this index;
10) Indexes are generally used for tables with many records. If there is a table DBInstance, all queries have the userID condition field. It is currently known that this field can distinguish records very well, that is, each There are not many records under one userID, so the table only needs to create an index on userID. Even if other conditional fields are used, since each userIDThere is not much corresponding record data, so there is basically no impact if other fields are not indexed. At the same time, the performance overhead of inserting and updating caused by establishing too many indexes can be avoided;
3． MySQLServer configuration optimization
MySQLServer configuration optimization mainly refers to MySQL parameters Optimization;
1) The MySQL server has a slow connection log, which can record query statements that exceed a certain time interval and do not use indexes to facilitate developer tracking. Turn on and off the slow connection log function by setting slow_query_log=ON/OFF, slow_query_log_fileSet the file name of the slow connection log, long_query_timeSet the timeout, the unit is ms,Pay attention to the slow connection logMySQLThe default is closed;
2) MySQL has a query cache function. The server will save query statements and corresponding return results to reduce server overhead caused by the same query. You can set the query by setting query_cache_size The size of the cache, 0 means turning off the query cache, but it is worth noting that once the table is updated, all query caches will become invalid. By default, MySQL means turning off the query. Cached;
3) You can set the maximum number of connections to the database by configuring max_connections, wait_timeoutSet the maximum number of connections Long retention time, the time unit is s, MySQLdefault is 8 hours, once it exceeds 8 hours, the database will automatically disconnect the connection, this You need to pay attention when using the database connection pool, because the connection in the connection pool may have been disconnected by the server. At that time, the connection pool does not know, and the application will make an error when it obtains the connection from the connection pool and uses it. max_connect_errorsConfigure if the application encounters multiple exceptions, the host connection to the database will be terminated;
[Related recommendations]
1. Free mysql online video tutorial
2. MySQL latest manual tutorial
3 .Those things about database design
The above is the detailed content of MySQL optimization includes three aspects. For more information, please follow other related articles on the PHP Chinese website!