High performance strategy in mysql

Posted on | Edited on | In

Clustered Indexes

solidDB and innoDB support clustered index.
It make sure the similar key’s row’s physical position similar too.
The field type ‘char’ or ‘varchar’ is not suitable for building clustered index.
Especially the field storage random char. It will lead to alot of movements.

innoDB will build a clustered index on primary key
If it not exists, system will replace it by a hidden primary key.

Ordinary speak, DBMS will use clustered index to storage data
Second level index is base on it.

Structure

MyISAM


Contain row’s num and row’s value, it’s no difference with orther index.
It’s only a primary key named PRIMARY index(clustered index).

InnoDB

  • TID transaction id (transaction)
  • RP rollback pointer (MVCC)

InnoDB’s secondary index have no need to maintain index
when base data moved or data splited.

Because secondary storage primary key not row num

Difference

Balance

Describe Clustered Not clustered
Offen group and sort yes yes
Return range data yes no
very little differ values no no
little differ values yes no
big differ values no yes
offen update field no yes
foreign key field yes yes
primary key field yes yes
Offen modify index field no yes

when not

  • query quantity is very low.
    If build a index, it will affect performance alot.
  • table data is very low, should not build as will.
  • text image bit blob, should not build.
  • INSERT DELETE UPDATE, much more than SELECT operation.
    They are mutex operation to each other.

Tips

  • build index on field always used to search/ link/ group.

  • don’t calculate on index field behind where.

  • build and maintain will take time
    and it’s proportional with table data quantity.

  • index file will obtain physical space.
  • when INSERT DELETE UPDATE it needs to dynamic maintain index.

If CRUD is balance in field search, the dynamic cost maybe worth while.
Because the other three except Retrieve, need to find operation target as will.