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

Show  status like ' Com_select '

Query How many times the insert was executed

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

Select xxx from table Ignore index (INDEX_XXX)

Force the use of indexes

SELECT * FROM Table Force index (INDEX_XXX)

The end of this lecture, the next talk about how to correctly use the index.

Database Tuning Tutorial (vii) The cost of indexing