MySQL Index related

Explain can view the execution of the SQL statement, you can see the number of rows scanned per condition, and when SQL queries are needed within the system, look at the performance of the statement using explain.

1. Indexes are often used to increase the search speed of data rows in a where condition, or to match other tables when performing a join operation. So the columns we choose to index are also usually selected to appear in the WHERE clause, join clause, ORDER by, or column in the GROUP BY clause.

2. The more different values of the data columns, the better the index effect. If a data column contains only: 0 or 12 values, the index is of little use.

3. If multiple columns are indexed, follow the leftmost prefix rule. The so-called leftmost column, which refers to the query starting at the leftmost top of the index and does not skip the columns in the index. For example: T1,T2,T3 to establish a joint index, but also to T1 and T1,T2 index. However, if you specify the values of T2, T3, T1T3, and t2t3 separately, you will not be indexed.

4. Try to compare data columns with the same data type. For example, int is different from bigint. char (10) is considered to be char (10) or varchar (10), but differs from char (12) or varchar (12).

5. An indexed column should not be part of an expression, or a function can be used on an indexed column.

6. When using like, try not to use wildcards at the beginning

WHERE col_name like '%string% '

WHERE col_name like ' abc% '

7. Do not use type conversions. If an indexed column is of type int, and the assignment is a character type at query time, the index will not be used.

SELECT * from mytbl WHERE num_col = 1; use index

SELECT * from mytbl WHERE num_col = ' 1 '; index not used

8. The Straight_join keyword can be used in the SELECT statement to overload the optimizer's selection. A LEFT JOIN selects a table with a smaller result set as the base table, and Straight_join can specify sequential joins if the field with the sort condition is another table may affect efficiency.

9. When MySQL estimates that the number of rows checked may be "too much", range lookup optimizations will not be used.

Try to borrow an overlay index to reduce the select * from ... statement use;

One.ORDER BY clause, try to use the index method to sort , avoid the use of filesort way sorting;

Use the LEFT JOIN statement sparingly to avoid creating temporary tables when creating temporary tables using the LEFT JOIN statement. Try not to use left join, divide and conquer. When you want to use it, ask yourself if you really want to use it.

MySQL Index related