MySQL index,

MySQL index,

• Index type
  • B-Tree Index
    • B-Tree indexes usually mean that all values areSequential StorageAnd the distance from each leaf page to the root is the same.
    • B-Tree indexesAccelerate Data Access, Storage engineFull table scan is no longer requiredTo obtain the required data. InsteadSearch from the index Root Node.
    • B-Tree indexes are suitable for searching full key values, key value ranges, or key prefixes (Leftmost prefix Principle).
  • Hash Index
    • Hash indexes are implemented based on hash tables and onlyExact match IndexAll columns are valid.
    • Hash indexes are the default index types of Memory engine tables. However, Memory also supports B-Tree indexes.
    • Hash index itselfOnly need to store the corresponding hash value and row pointer, AndDo not store Field ValuesTherefore, the index structure is very compact, which also speeds up searching for hash indexes.
    • Hash index data is not stored in the order of index values, soCannot be used for sorting.
    • Hash IndexMatching of some index columns is not supportedBecause the hash index always uses all the content of the index column to calculate the hash value. For example, if only column A is queried, the index cannot be used.
    • Hash IndexQuery by any range is not supported, Such as WHERE score> 60.
    • Hash IndexOnly query by equivalent comparison is supported., Including =, IN (), <=> (note <> and <=> are different operations ).
    • IntroductionUse Cases: To store a large number of URLs, search for them based on the URLs. If you use B-Tree to store URLs, the stored content will be very large, because the URL itself is very long.
    • Create a table

      1 mysql> CREATE TABLE TB3 (2     -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,3     -> url VARCHAR(255) NOT NULL,4     -> url_crc INT UNSIGNED NOT NULL DEFAULT 0,5     -> PRIMARY KEY(id),
      6     -> KEY IDX(url_crc)7     -> );

    • Create a trigger

      mysql> DELIMITER //mysql> CREATE TRIGGER TB3_CRC_INS BEFORE INSERT ON TB3 FOR EACH ROW BEGIN SET NEW.url_crc=CRC32(NEW.url);
          -> END;
          -> //mysql> CREATE TRIGGER TB3_CRC_UPD BEFORE UPDATE ON TB3 FOR EACH ROW BEGIN SET NEW.url_crc=CRC32(NEW.url);
          -> END;
          -> //mysql> DELIMITER ;

    • Insert or update data

    •  1 mysql> INSERT INTO TB3(url) VALUES('http://www.mysql.com'); 2 mysql> SELECT * FROM TB3; 3 +----+----------------------+------------+ 4 | id | url                  | url_crc    | 5 +----+----------------------+------------+ 6 |  1 | http://www.mysql.com | 1560514994 | 7 +----+----------------------+------------+ 8  9 mysql> UPDATE TB3 SET url="https://www.mysql.com" WHERE id=1;10 mysql> SELECT * FROM TB3;11 +----+-----------------------+------------+12 | id | url                   | url_crc    |13 +----+-----------------------+------------+14 |  1 | https://www.mysql.com | 1053537447 |15 +----+-----------------------+------------+

      Query (we can see that ref: const is already the best level). Some people ask why a url_crc is not directly used as the filtering condition in the WHERE condition.Hash conflictWhen the hash value of another string is the same, it cannot be queried using url_crc, so avoid conflicts, the hash value and corresponding column value must be included in the WHERE clause.

       1 mysql> EXPLAIN SELECT * FROM TB3 WHERE url_crc=CRC32('https://www.mysql.com') AND url="https://www.mysql.com"\G 2 *************************** 1. row *************************** 3            id: 1 4   select_type: SIMPLE 5         table: TB3 6    partitions: NULL 7          type: ref 8 possible_keys: IDX 9           key: IDX10       key_len: 411           ref: const12          rows: 113      filtered: 100.0014         Extra: Using where
      

      PS:If this method is used, do not use SHA1 () and MD5 () as hash functions, because the hash values calculated by these two functions are very long strings, which wastes a lot of space, the comparison will also be slower. But if the data table is very large, CRC32 will have a lot of hash conflicts, You can implement a simple 64-bit hash function, such as select conv (RIGHT (MD5 ("https://www.mysql.com "), 16), 16, 10) AS HASH64;

  • Spatial Data Index
    • MyISAM tables support spatial indexes and can be used for storing geographical data.
    • MySQL GISIncomplete supportSo most people will not use this feature.
    • Spatial Index MEETINGIndex data from all dimensionsDifferent from B-Tree, such indexesNo prefix query required.
    • MySQL GIS-related functions such as MBRCONTAINS () must be used to maintain data.
  • Full-text index
    • Full-text index is a special type of index, which looksKeywords in textInstead of directly comparing the values in the index.
    • Full-text indexes use the match against operation instead of the WHERE condition operation.
  • Other Indexes
    • TokuDB uses the fragment Tree index, which has many advantages of B-Tree and avoids some disadvantages of B-Tree.
    • ScaleDB uses Patricia tries.
    • InfiniDB and Infobright use some special data structures to optimize some special queries.