mysql optimization (5) indexing and sorting

Sorting may occur in two situations:

1: For covering indexes, when querying directly on the index, there is order,
using index, or it may be along the index field when querying Sort query. At this time, the sorting cost is low

2: First take out the data and form a temporary table for filesort (file sorting, but the file may be on the disk or in the memory)



Our goal-----The retrieved data itself is in order!
Use index to sort.



For example: goods product table, (cat_id,shop_price ) to form a joint index,

where cat_id=N order by shop_price, you can use the index to sort,

select goods_id,cat_id,shop_price from goods order by shop_price;

/ / using where, the results taken out according to the shop_price index are themselves ordered.



select goods_id,cat_id,shop_price from goods order by click_count;

// using filesort uses file sorting, that is, the retrieved results are sorted again.

Mysim finds all of them during execution, sorts them, and then retrieves the rows. As long as there is an index, it is not very important to combine the index clicks and id to create an index.

innodb is just under the leaf

Duplicate indexes are meaningless

Redundant indexes improve efficiency. Two indexes will rent different left keys in different situations;

Index Fragmentation and maintenance

Large websites basically do not delete data, which will create a hole on the disk; and short bytes that are not easily reused will not be easily reused

Fragmentation of memory;

Fix the vulnerability

During the long-term data change process, both the index file and the data file will generate holes and form fragments.

We can use a nop operation (not operations that have a substantial impact on the data),
to modify the table.

For example: the engine of the table is innodb,
you can alter table xxx engine innodb which essentially has no impact on the data to modify the table But the data will be reorganized



optimize table The table name can also be repaired. Optimize this table to help you repair it;



Note: Repairing the data and index fragments of the table will rearrange all the data files to align them.

This process is also a very resource-consuming operation if the number of rows in the table is relatively large.

So, it cannot be repaired frequently. The cycle is longer! ! ! !



If the update operation of the table is very frequent, it can be repaired on a weekly/monthly basis.

If it is infrequent, it can be repaired in a longer period.

The above is the content of mysql optimization (5) indexing and sorting. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!