In most cases, you can estimate query performance by counting
disk seeks. For small tables, you can usually find a row in one
disk seek (because the index is probably cached). For bigger
tables, you can estimate that, using B-tree indexes, you need this
many seeks to find a row: log(row_count)
/ log(index_block_length
/ 3 * 2 /
(index_length
data_pointer_length))
1.
In MySQL, an index block is usually 1,024 bytes and the data
pointer is usually four bytes. For a 500,000-row table with a key
value length of three bytes (the size of MEDIUMINT),
the formula indicates log(500,000)/log(1024/3*2/(3
4)) 1 = 4 seeks.
This index would require storage of about 500,000 * 7 * 3/2 =
5.2MB (assuming a typical index buffer fill ratio of 2/3), so you
probably have much of the index in memory and so need only one or
two calls to read data to find the row.
For writes, however, you need four seek requests to find where
to place a new index value and normally two seeks to update the
index and write the row.
特别说明:
源自MySQL官方手册.