MySQL Query Optimization

MySQL Query Optimization
There are many things to optimize database queries. The basic principles are as follows: reduce the disk I/O count that occurs during database queries. Use indexes to avoid full table traversal and scan to reduce the amount of data transmitted over the network. Only query fields required. If Group By and Join exist at the same time, try to Group By first and then Join to avoid using the MySQL function in the where statement to use locate (substr, str) replace like '% substr %' with a composite index (use sub_part () for ultra-long fields) to avoid using select *, directly specifying the select field will cause the database to translate * into the actual field first. If one more step is performed, not every field is required, disk I/O and network transmission are wasted. If the select field (such as user_id) has an index, select user_id will return the result directly from the index, select * needs to find the results from the data table, at least one more disk I/O optimization tool: runs several times in a row, and the total time consumption is: SELECT BENCHMARK (100000000, LOCATE ('foo', 'foobar'); select benchmark (100000000, 'foobar' LIKE '% foo %'); check whether indexes are used: description <SQL statement> fields that can be indexed: which fields need to be indexed: JOIN, WHERE, ORDER BY, GROUP BY, MAX (), MIN () which of the following fields are not suitable for indexing: fields with a large number of duplicate values, such as the condition for applying a Boolean index: when the index takes effect: Use>, >=, =, <, <=, if null and BETWEEN use the MAX () and MIN () functions. if order by and group by are used, LIKE use the wildcard at the end: where a like 'B %' is effective for combined indexes (multiple-column index or composite index), such as (c1, c2): where c1 = 1, where c1 = 1 and c2 = 2 when the index does NOT take effect: when not in or <> is used, LIKE when the wildcard is used: where a like '% B' if the column in WHERE has already been indexed, the index of another column in ORDER BY will not take effect for the composite index, such as (c1, c2 ): if where c2 = 2 does not take effect for column calculation, for example, where year (iDate) <2015 (can be changed to WHERE iDate <'2017-01-01 ') MySQL index type: NORMAL: NORMAL index type, with no UNIQUE restriction UNIQUE: the value of the indexed field must be unique fulltext: it can be created on varchar or text, it can only be used for Data Type Selection of index fields of the MyISAM type: the indexed fields should be small and simple data types (such as integer) as much as possible to avoid complex types (such as string types) the field to be indexed should be limited to not null. You can use 0,-1, and NULL strings instead of NULL to check whether the index is used: Explain <SQL statement>