MySQL main index mode: Fulltext,hash,btree,rtree.

How to use

CREATE TABLE ' user ' (' ID ' bigint) NOT NULL auto_increment, ' username ' varchar (a) NOT null COMMENT ' username ', ' password ' varchar (+) not NULL COMMENT ' password, encrypted storage ', ' phone ' varchar ' default NULL COMMENT ' registered mobile number ', ' email ' varchar (default) Null COMMENT ' registered mailbox ', ' created ' datetime NOT NULL, ' updated ' datetime NOT NULL, PRIMARY key (' id '), UNIQUE KEY ' Usernam E ' (' username ') using BTREE, unique key ' phone ' (' phone ') using BTREE, unique key ' email ' (' email ') using BTREE) engine= InnoDB auto_increment=37 DEFAULT charset=utf8 comment= ' user table ';

<textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none" tabindex="0"></textarea> x13 1

CREATE TABLE ' user ' (

2

  bigint (notNULL auto_increment,

3

  varchar (notNULL' user name ',

4

  varchar (notNULL' password, encrypted storage ',

5

  varchar (NULL' Register mobile number ',

6

  varchar (NULL' registered mailbox ',

7

  datetime  not NULL,

8

  datetime  not NULL,

9

  PRIMARY KEY (' id '),

10

  UNIQUE KEY ' username ' (' username ') USING BTREE,

11

  UNIQUE KEY ' phone ' (' phone ') USING BTREE,

12

  UNIQUE KEY ' email ' (' email ') USING BTREE

13

Engine=innodb auto_increment=PNs DEFAULT charset=utf8 comment=' user table ';

Fulltext

is a full-text index and currently only MyISAM engine support. It can be in CREATE table   , ALTER TABLE   , CREATE INDEX   used, but currently only   CHAR , VARCHAR   , TEXT   You can create a full-text index on the column. It is worth mentioning that when the data volume is large, the data is now placed in a table without a global index, and then created with CREATE index fulltext index, which is more than a table first Fulltext The then writes the data much faster.

the full-text index is not MyISAM born together, its appearance is to solve WHERE name like "%word%" This kind of fuzzy query for text is less efficient. Before the full-text index, such a query is to traverse the data table operations, visible, in the large amount of data is extremely time-consuming, if there is no asynchronous io processing, the process will be hijacked, it is a waste of time, of course, there is no asynchronous IO For further explanation, want to understand the children's shoes, self-Google.

The use of full-text indexing is not complex:

Create ALTER Table Table ADD INDEX ' Fullindex ' USING fulltext (' cname1 ' [, cname2 ...]);

Use SELECT * FROM table WHERE MATCH (cname1[,cname2 ...]) Against (' word ' MODE);

among them, mode for search mode (inBOOLEAN mode , inNATURAL LANGUAGE mode ,in NATURAL LANGUAGE MODE with query expansion/with query EXPANSION).

about these three kinds of search methods, here also do not do more to explain, simply, is, Boolean mode, allow Word contains special characters to mark specific requirements, such as + must have, - there must be no, * indicates a generic match, does not think of the regular, similar to it; natural language patterns are simple word matching; the natural language pattern with expressions is to first use natural language mode to deal with the returned results and then to match the expressions.

to the search engine a little bit of understanding of the classmate, must know the concept of participle, Fulltext indexes are also indexed according to the word segmentation principle. In Latin, most of the alphabet, Word segmentation can be easily separated by the space. However, it is obvious that Chinese cannot make participle in this way. And what about that? This introduces you to a Mysql Chinese word breaker plugin mysqlcft, with it, you can Chinese word segmentation, want to know the classmate please MYSQLCFT , and of course there are other word breaker plugins available.

HASH

HashThis word, it can be said, since the day we started the code, began to constantly see and use. In fact,Hashis a (Key=>value) Form a key-value pair, such as a function map in mathematics, that allows multipleKeycorresponds to the samevalue, but does not allow aKeycorresponds to multiplevalue. Precisely because of this feature,Hashideal for indexing, creating a column or columnsHashindex, the values of this column or columns are used to calculate a certain algorithmHashvalue, corresponding to one row or several rows of data (this is conceptually different from the function map, do not confuse). In theJavalanguage, each class has its ownhashcode ()method, none of the display definitions are inherited from theObjectclass, which enables each object to be unique, between objectsEqualplays an important role in the comparison and serialization of transmissions. HashThere are many ways to build a method that can guaranteeHashThe uniqueness of the code, for example, inMongoDB, each of theDocumenthas a unique system-generatedObjectID(contains timestamp, host hash value, processPID, and self-increasingID) is also aHash's performance. Well, I seem to be pulling away.-_-!

because Hash indexes can be positioned one at a time and do not need to be looked up by layers like a tree index , Therefore, it is highly efficient. So why do you need other tree-shaped indexes?

you don't have to summarize yourself here. References to other great gods in the garden: MySQL 's btree from the road the difference between index and hash index

(1)Hash indexes can only meet"=", "in"and the"<=>"query, you cannot use a range query.
because Hash indexes are compared to the Hash after the Operation Hash value, so it can only be used for filtering of the equivalent, not for range-based filtering, because the corresponding Hash after the algorithm is processed Hash value of the size relationship, and is not guaranteed andHashexactly the same before the operation.
(2)Hash indexes cannot be used to avoid sorting operations on data.
because Hash The index is stored in a Hash after the calculation Hash value, andHashthe size of the value relationship does not necessarily Hash the key values before the operation are exactly the same, so the database cannot use the indexed data to avoid any sort operations;
(3)Hash indexes cannot be queried with partial index keys.
for composite indexes,Hash index in the calculation Hash value is the combination of index keys combined and then evaluated together Hash values, rather than individual calculationsHash values, so when you query by combining the previous or several index keys of the index,Hash The index cannot be exploited either.
(4)Hash indexes cannot avoid table scans at any time.
as I have known before,Hash index is the index key through the Hash after the operation, the Hashof the result of the Operation Hash value and the corresponding row pointer information are stored in a Hash table, because different index keys exist in the same Hash value, so even if you take a Hash The number of record bars for the data of the key value, and cannot be Hash The query is completed directly in the index, or the actual data in the table is accessed, and the corresponding results are obtained.
(5)Hash The index encounters a large numberHashwhen values are equal, performance is not necessarilyB-treeThe index is high.
for low-selectivity index keys, if you create Hash index, there will be a large number of record pointer information stored in the same Hash values are associated. This can be very cumbersome to locate a record, wasting multiple table data access and resulting in poor overall performance.

let me add a little bit. HASH The process of indexing, by the way, explains the above 4,5 article:

when we build for a column or column Hash index (currently only MEMORY The engine explicitly supports this type of index), a file similar to the following will be generated on the hard disk:

Hash value	Storage Address
1db54bc745a1	77#45b5
4bca452157d4	76#4556,77#45cc ...

...

Hash The value is calculated from the specified column data by a particular algorithm, and the disk address is the address on the hard disk where the data row is stored (and possibly other storage addresses, in fact MEMORY Will be Hash table to import memory).

this way, when we do WHERE age = , The same algorithm is used to calculate a hash value ==> The corresponding storage address is found in the hash table ==> data is obtained from the storage address.

Therefore, each query is traversed Hash table until it finds the corresponding Hash values, such as ( 4 ), after a large amount of data, Hash tables can also become bulky, performance degrades, and traversal time increases, such as ( 5 ).

BTREE

BTREE index is a kind of index value according to a certain algorithm, into a tree-shaped data structure, I believe that learning data structure of the children's shoes are the original learning binary tree This data structure experience memories, anyway, I was in order to soft test but this thing was a good toss, but that exam seems not how to test this. Like a binary tree, each query is started from the root of the tree's portal , traversing nodein turn to get the leaf.

BTREE in the MyISAM in the form and Innodb slightly different

in the in Innodb, there are two forms: the first is the primary key form, and its leaf node holds the data, And not only the data of the index key is stored, but also the data of other fields. The second is secondary index, whose leaf node is similar to the ordinary BTREE , It just stores the information that points to the primary key .

and in MyISAM , the primary key is not much different from the others. But unlike Innodb , where the MyISAM is ,leaf node Instead of the primary key information, it points to the data row in the data file .

RTREE

RTREE in the MySQL rarely used, only supported Geometry data type that supports this type of storage engine only MyISAM , BDb , InnoDb , NDb , Archive several.

relative to BTREE , RTREE The advantage is the range finder .

Usage of various indexes

( 1 ) for BTREE this Mysql default indexing method, with universal applicability

( 2 ) because Fulltext Chinese support is not very good, in the absence of plugins, it is best not to use. In fact, some small blog applications, only need to set up a keyword list for it in the data collection, through the keyword index, is also a good way, at least I often do so.

( 3 for some search engine-level applications, Fulltext also not a good way to deal with it, Mysql the full-text index of the file is still relatively large, and the efficiency is not very high, even if the use of Chinese word-breaker, Chinese word segmentation support is only general. If you really encounter this problem,Apache Lucene may be your choice.

( 4 ) precisely because Hash tables have an unparalleled advantage in handling small amounts of data, so Hash indexes are good for caching (in-memory databases). such as The memory version of MySQL database memsql, the use of a wide range of caching tools mencached, NOSQL Database Redis and so on, all used. Hash Index this form. Of course, if you do not want to learn these things, Mysql MEMORY engine can also meet this demand.

( 5 ) as for RTREE , has not been used so far, it is specific how, I do not know. Have RTREE Use experience of classmate, can exchange next!

Null

MySQL main index mode: Fulltext,hash,btree,rtree.