MySQL query optimization and MySQL Optimization

MySQL query optimization and MySQL Optimization
There are many things to optimize database queries. The following is a summary:
Basic Principles:

1. Reduces disk I/O count during Database Query
   • Rational use of Indexes
   • Avoid full table traversal and Scanning
2. Reduces the amount of data transmitted over the network
   • Only query required fields

1. If both Group By and Join exist, try to Join Group By first and then
2. Avoid using MySQL functions in where statements
3. Use locate (substr, str) instead of like '% substr %'
4. Use composite index (use sub_part () for ultra-long fields ())
5. To avoid using select *, you should directly specify the field to select
   • Select * will cause the database to translate * into the actual field, with one more step
   • * Not every field is required, which wastes disk I/O and network transmission.
   • If the select field (such as user_id) has an index, select user_id returns the result directly from the index, and select * needs to query the result from the data table, at least once more disk I/O

Optimization Tool: runs several times in a row. Check the total time consumption: select benchmark (100000000, LOCATE ('foo', 'foobar'); select benchmark (100000000, 'foobar' LIKE '% foo % ');
Check whether the index is used:
Explain <SQL statement>
Fields that can be indexed:

• Which fields need to be indexed: used for JOIN, WHERE, order by, group by, MAX (), MIN (), and other fields
• Which fields are not suitable for indexing: fields with a large number of duplicate values, such as Boolean

Conditions for indexing to take effect:

• When the index will take effect:
  • Use>,> =, =, <, <=, if null, and
  • Use of MAX () and MIN () Functions
  • Used by order by and GROUP
  • When you use a wildcard LIKE at the end: where a like 'B %'
  • For combined indexes (multiple-column index or composite index), such as (c1, c2): where c1 = 1, where c1 = 1 and c2 = 2
    
• In which cases will the index not take effect:
  • Usage of not in and <>
  • When LIKE starts with a wildcard: where a like '% B'
  • If the column in WHERE already has an index, the index of another column in order by does not take effect.
  • For composite indexes, such as (c1, c2): where c2 = 2 does not take effect
  • When a column is computed, for example, where year (iDate) <2015 (you can change it to WHERE iDate <'2017-01-01 ')

MySQL index type:

• NORMAL: NORMAL index type, with no uniqueness restrictions
• UNIQUE: the value of the indexed field must be UNIQUE.
• FULLTEXT: it can be created in varchar or text type and can only be used for tables of the MyISAM type.

Select the Data Type of the index field:

• Small and simple data types (such as integer) should be used as much as possible for indexed fields, and complex types (such as string types) should be avoided)
• The field to be indexed should be not null as far as possible, and NULL can be replaced by 0,-1, and empty string

(For Original Articles, please note