High performance MySQL (5) indexing strategy a compression, redundancy, duplication, indexing and locking

One, compressed index

MyISAM uses prefix compression to reduce the size of the index, by default compressing only strings, but by setting it can also compress integers.

Compression can use less space, at the expense of some operations that may be slower. In particular, the reverse query, the test shows that for CPU-intensive applications, the query will be very slow, especially in reverse. Queries may be good for I/O-intensive applications.

You can specify the index compression method by specifying the Pack_keys parameter in the statement in the CREATE table.

Ii. Redundancy and duplicate indexing

Duplicate index: Refers to the same type of index that is created in the same order on the same column, avoiding duplicate indexing as much as possible, unless different types of indexes are created on the same column to satisfy different query requirements. For example, key (COL) and Fulltext Key (COL).

Redundant index: If an index (A,B) is created, and then an index (A) is created, it is a redundant index. Because the index (A,B) can also be used as (A) (for B-tree only). However, if you create an index (B,A) or (B) again, it is not a redundant index. or different types, such as hashing, full-text indexing, and so on.

A redundant index usually occurs when an index is added, and in most cases it does not require a redundant index, but rather expands an existing index, unless the extension of an existing index results in a large index, which affects the performance of other indexes.

For example, if you extend an index of a long varchar column on an index of an integer column, performance may drop dramatically. However, adding a new index will have a significant impact on the addition and deletion of the operation, so be balanced use.

How to find this write index so that you can delete it, access information_schema, or some existing tools to locate it.

There is a noteworthy place:

For InnoDB, the Index on column (A) is equivalent to (A,ID) because the primary key is changed in the two level index, so similar

This index is useful for queries such as the Where a=5 order by ID. But extended to index (A,B)

After that, it actually becomes (A,b,id), then the order by cannot use the index sort.

Iii. Index and lock

Indexes allow fewer rows to be locked by the query, because the index allows the query not to access those unwanted rows, and then locks fewer rows. This has 2 advantages:

1. Reduce the extra overhead of locking lines.

2, lock more than needed to increase lock contention and reduce concurrency.

InnoDB locks the row only when it is accessed, and the index reduces the number of rows accessed by InnoDB, thereby reducing the amount of locks. This is only valid if the InnoDB is able to filter out all unwanted rows at the storage engine layer. If the index cannot filter out invalid rows, the MySQL server can apply a WHERE clause for filtering after INNODB retrieves the data back to the server layer. This is the time to avoid locking rows, and InnoDB has locked all returned rows of data.

Illustration: Table structure

CREATE TABLE ' Emp3 ' (
  ' id ' int (one) not null DEFAULT ' 0 ',
  ' name ' varchar ' (m) not null,
  ' job ' varchar ' NULL,
  ' NUM1 ' int (a) default null,
  ' num2 ' int (a) default null,
  ' num3 ' int (a) default null,
  ' Job_num ' int (a) default null,
  ' d ' date default NULL,
  PRIMARY key (' id '),
  key ' Job_num ' (' job_num ')
engine= InnoDB DEFAULT Charset=utf8;

The data are as follows:

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/