Database Tuning Tutorial (vii) The cost of indexing

Third, index

4. The cost of the index

The previous sections describe the power of indexes to improve performance and how to use indexes, but indexes are also expensive

1) Consume disk space

As mentioned earlier, indexes are cost-represented after an index is added. ibd Files (InnoDB engines) or. myi files (MyISAM engines) can become larger.

2) causes DML operations to become slower

When you add an index, it is faster because the table sorts the data according to the index by an algorithm (binary tree, etc.), so the binary tree is reordered after deletion, increment, and insertion, resulting in a decrease in execution efficiency.

It is time to see if your database is a DML statement that executes many or more DQL statements.

Use the following statement to query

How many times did the query execute select

[Plain]View Plaincopy

1. Show status like ' Com_select '

Query How many times the insert was executed

[Plain]View Plaincopy

1. Show status like ' Com_insert '

And so on

In general, DQL statement operations are much more than DML statements! Close to 9:1

Since the index has both advantages and disadvantages, how to control the use of the index artificially?

Force not to use an index

[Plain]View Plaincopy

1. Select xxx from table Ignore index (INDEX_XXX)

Force the use of indexes

[Plain]View Plaincopy

1. SELECT * FROM Table Force index (INDEX_XXX)

Database Tuning Tutorial (vii) The cost of indexing