High Performance MySql Reading Notes-Performance Optimization

Avoid NULL values as much as possible

It is difficult for MySql to optimize empty columns and can be replaced by 0, special value or null string.

Limitations of B-TREE Indexing

You can only start from the leftmost column of the index. You cannot skip the left or middle column of the index. You cannot optimize the access to the column on the right of the first range condition.

Limitations of HASH indexes:

 

How to determine the proper prefix index length:

Analyze the selectivity and the worst case selectivity.

Suffix index:

Sometimes it is very useful. For example, you can save the suffix separately and create an index based on the domain name summary of your mailbox.

Clustered index:

Is a B-TREE index for leaf nodes with data behavior, which is stored in order by index. Covering indexes can be regarded as small clustered indexes.

 

Index usage tips:

Supports multiple filtering Conditions

When creating an index, place the most selective column in front. However, if a column is often used as a range condition query, it is best to put it at the end. However, columns with poor selectivity cannot be placed in the front, which allows the index to support multiple filtering conditions, such as column sex, which is poorly selective, but is used in most queries, you can put it in front of the index. If sex is not in the where list, you can add and sex in (M, F) to ensure that the index is used. This avoids creating too many indexes.

Avoid multiple range conditions

In MySql, the range condition (between, <,> <>) will cause the index to ignore the subsequent index columns, but multiple equal conditions do not have this restriction (in ), therefore, when two range conditions appear in a query, you can try to convert one of the range conditions into multiple equal conditions (using the in keyword ). If conversion is not possible, consider that a range condition is not included in the index column.

 

Optimize sorting

You can create an index (sex, rating) for the following query ):

Select <cols> from tb where sex = 'M' order by rating limit 10;

However, when the request is not the first page, it will be slow. You can consider extracting only the primary key of the required column and then connecting to the original table:

Select <cols> from tb inner join (

Select <cols> from tb where sex = 'M' order by rating limit 100000,10

) As x using (<pk> );

 

Index and table maintenance

If you encounter an odd behavior, such as an error that shouldn't have occurred, run check table to check whether the table is damaged.

If the table is damaged, use repair table or alter (alter table innodb_tb engine = innodb.

You can use analyze table to update index statistics.

Optimize table can be used to reduce index and table fragmentation.

 

Optimize Alter Table

In MySQL, alter table often leads to table reconstruction. If the data volume is large, it is very slow. If you only modify the column information of a table, you can use alter column to avoid table reconstruction (modify column will cause table reconstruction ):

Alter table tb

Alter column col set default;

This command only modifies the. frm file and does not modify the table, it will be very fast.

 

Similarly, you can use the following method to quickly alter table by modifying only the. frm file ):

Create an empty table with the same structure and modify it as needed;

Execute flush with read lock;

Use the. frm file of the new table to overwrite the. frm file of the original table;

Run the unlock tables command to release the read lock.

 

Optimize connections

You only need to add an index to the second table in the join. Column c is used when table A and table B are joined, and the optimizer joins the table in the order from B to, you do not need to add an index to table B.

Make sure that group by or order by references only the columns in a table, so that MySQL can try to use indexes for these operations.