MySQL optimization includes three aspects

Database has become an indispensable underlying dependency for Internet applications, among whichMySQLhas 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 theMySQLdatabase.

The optimization of MySQLmainly includes three aspects. The first is the optimization ofSQLstatements, 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 theWhereclause and theOrderByclause, but eachSQLstatement 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 theexplainkeyword before the query statement to view theSQLstatement. Execution plan to determine whether the query statement uses an index;

2)Should try to useEXISTandNOT EXISTReplaceINandNOT IN, because the latter is likely to cause the full table scan to abandon the use of indexes;

3)Try to avoidNULLjudgment on fields in theWhereclause, becauseNULLjudgment will lead to a full table scan;

4)You should try to avoid usingoras a connection condition in theWhereclause, because it will also lead to a full table scan;

5)should be avoided in theWhereclause! The=or<>operator will also cause a full table scan;

6)Usinglike “%abc%”orlike “%abc”will also result in a full table scan, andlike “ abc%”will use index.

7)When using theUnionoperator, you should consider whether you can useUnion ALLinstead, because When theUnionoperator merges results, it will sort the results and delete duplicate records. For applications that do not have this requirement,Union ALLshould be used, the latter just The results are merged and returned, which can greatly improve performance;

8)Try to avoid using expression operators in theWhereclause. Because it will cause a full table scan;

9)You should try to avoid using functions on fields in theWhereclause, because it will also cause Full table scan

10)SelectTry toavoid using "*" in the statement , because in the process of parsing theSQLstatement, "*" 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 Whereclause, the table connection condition should be written before other conditions, because the parsing of theWhereclause is From back to front, so try to put the restrictions that can filter out most records at the end of theWhereclause;

12)If there is a joint index such asindex(a,b,c)on the database table, the order of appearance of the condition fields in theWhereclause 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 Fromclause will also affect theSQLstatement. Execution performance is affected. TheFromclause 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 are3and3or 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 followingSQLstatement,select dbInstanceIdentifier from DBInstance where id > 3, this statement should be replaced withselect 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 to3, and then scan forward, while the latter directly locates the record equal to4.

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 itemSQLStatements 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 theInnodbtable, 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 usingcache;2.Insufficient concurrency, all tables without primary key in the system share a globalAuto_Incrementcolumn. Therefore, all tables inInnoDBmust 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 anINDEXindex.

5)For large text fields orBLOBfields, 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 indexindex(userID,dbInstanceID),The following query statement can use this index,select dbInstanceIdentifier from DBInstance where userID=?, but the statementselect dbInstanceIdentifier from DBInstance where dbInstanceID=?cannot use this index;

10)Indexes are generally used for tables with many records. If there is a tableDBInstance, all queries have theuserIDcondition field. It is currently known that this field can distinguish records very well, that is, each There are not many records under oneuserID, so the table only needs to create an index onuserID. Even if other conditional fields are used, since eachuserIDThere 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 toMySQLparameters Optimization;

1) The MySQLserver 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 settingslow_query_log=ON/OFF,slow_query_log_fileSet the file name of the slow connection log,long_query_timeSet the timeout, the unit isms,Pay attention to the slow connection logMySQLThe default is closed;

2) MySQLhas 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 settingquery_cache_sizeThe size of the cache,0means turning off the query cache, but it is worth noting that once the table is updated, all query caches will become invalid. By default,MySQLmeans turning off the query. Cached;

3)You can set the maximum number of connections to the database by configuringmax_connections,wait_timeoutSet the maximum number of connections Long retention time, the time unit iss, MySQLdefault is8hours, once it exceeds8hours, 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!