Database optimization tutorial (7) cost and cost of INDEX OPTIMIZATION

Database optimization tutorial (7) cost and cost of INDEX OPTIMIZATION
Iii. Index

4. index cost

The previous sections introduced the power of indexes in improving performance and how to use indexes. However, indexes also have overhead.

1) disk space occupied

As described above, indexing is costly, as the. ibd file (innodb engine) or. myi file (myisam engine) increases after an index is added.

2) Slow dml operations

After an index is added, it is faster because the table sorts the data based on the index according to a certain algorithm (Binary Tree, etc.). Therefore, the binary tree needs to be re-sorted after deletion, addition, and insertion, resulting in lower execution efficiency.

 

In this case, it depends on whether your database runs many dml statements or more dql statements.

Use the following statement to query

 

Queries the total number of select executions

[Plain]View plaincopy

1. Show status like 'com _ select'

Queries the total number of insert operations performed

[Plain]View plaincopy

1. Show status like 'com _ insert'

And so on

 

In general, dql statements operate much more than dml statements! Close

 

Since indexing has advantages and disadvantages, how can we artificially control the use of indexing?

Disable Indexing

[Plain]View plaincopy

1. Select xxx from table ignore index (index_xxx)

Use index forcibly

[Plain]View plaincopy

1. Select * from table force index (index_xxx)