2018-06-16 15:52:19

index in mysql

There are some types of index which depends on storage engine. Most used index would be B-Tree indexes, which complement by InnoDB.

B-Tree indexes

  1. InnoDB refers to them by their primary key values
  2. store in order
  3. multicolumn index should satisfy leftmost prefix
  4. the index can be used by ORDER BY,GROUP BY and DISTINCT

Hash indexes

  1. probably have hash index collision, if this happen ,some index maintennance oprations can be slow
  2. in case hash index have collisions, must compare their values instead of only index to get the right row
  3. if some index values that innodb engine find they are being accessed very frequently, it'll automaticly build a hash index for them in memory on top of B-Tree indexes
  4. you can generate your own hash index by creating it on triggers or application level, CRC32(a set of char) for example, but should not use SHA1() or MD5() which waster a lot of space. If you think 32 bit interger could have plenty of collision, you should implement you own 64-bit hash function:
  5. you can also use FNV64() function(64 bit long) which ships with Percona server and can be installed as a plugin in any version of MySQL

clustered indexes

  • rows data exist on leaf pages of table's clustered index
  • a single table(InnoDB engine) can only have one clustered index
  • clustered indexes is always existed in innodb engine

      1. use primary key
      2. use a unique nonnullable index
      3. if 1 and 2 not satisfy, InnoDB will define a hidden primary key for you 
  • OPTIMIZE TABLE would be a good idea after a lot of data inserted, the best operation on inserting rows to table would be in pramary key order
  • Secondary indexes's leaf nodes contain the primary key and secondary index lookups the primary key index when need to read data from a table
  • use UUID as clustered index is a poor chioce

covering indexes

  • when retriving only index row
  • seconday can also be used to cover the primary key
  • there is a trick call "deferred join" which can use covering index, but it's perfomance depends on table data, and the subquery might be optimize as join which might be more inefficient

sometimes the index is not the best solution

  • for very small tables, it is often more effective to simply read all the rows
  • for medium or large tables, indexes can be very effective
  • for enormous tables, the maintenrance of index will cause some overhead.(maybe partitions on mysql, or nosql?)

index strategies

  • more selectivity
  • for long sting, the maximum and minimum of selectivity of prefix string index may be propriate solution(but MySQL can not use prefix indexes for ORDER BY or GROUP BY, nor covering index)
    ALTER TABLE sakila.city_demo ADD KEY (city(7)); 

a little about index design strategy

  • highest priority on rows that need to be ordered by or group by
  • enum type could be placed on left prefix so it can be more alternative
  • avoid multiple range conditions

Permanent link of this article:http://nulls.cc/post/index_in_mysql

-- EOF --