InnoDB 5.6 One of the new features: Fulltext INDEXES[1. Brief introduction]

First Look at an SQL statement: SELECT * from AA where Acol like '%like_normal% ';

when we use Innodb , it is meaningless to optimize the statement anyway, some people will say, for this situation first recommended to use MyISAM table to store, yes,MyISAM is really a good choice But we have this table not only this column of data, there are other columns, development and said, for this table of data need to ensure certain things, the amount of what to do?

     see this perverted sql essql mysql Inside is a headache problem, in mysql5.6

The good news is the MySQL5.6 version, added this new feature InnoDB Fulltext index East , simply is a full-text index, it can be the text of the table you specify , CHAR,VARCHAR Build Full-text index, note!! not what we're talking about. A generic index, or a prefix index, is a full-text index.

Here's the question, What is full-text indexing? believe that contact with MyISAM, search engines,Lucen of the full-text index is not unfamiliar, in short, the full-text index is to your file in each word to create an index, through the word established index, we can quickly find , where these words are.

well, with the basic concept of full-text indexing, let's look at how MySQL InnoDB 's full-text index is implemented, and talk less, directly to test the results:

We first create a table:

CREATE TABLE test_fullindex (id int (one) not NULL auto_increment PRIMARY KEY, Aname char ($) DEFAULT NULL, bname text , CNAME varchar (400));

After the table has been built, We can add some indexes to it, and for comparison we add two indexes:

ALTER TABLE Test_fullindex add fulltext idx_aname_bname (Aname,bname); Note Here is a Warning !!! , a little we're talking about.

ALTER TABLE Test_fullindex add index idx_bname_cname_idx(bname( Cname( a ));

Note The syntax for a full-text index is different from the syntax of a normal index!

Look at the table structure:

Mysql> Show CREATE TABLE Test_fullindex\g

1. Row ***************************

Table:test_fullindex

Create table:create Table ' Test_fullindex ' (

' id ' int (one) not NULL auto_increment,

' Aname ' char (+) DEFAULT NULL,

' Bname ' text,

' CNAME ' varchar (+) DEFAULT NULL,

PRIMARY KEY (' id '),

KEY ' Idx_bname_cname_idx ' (' bname ' (), ' CNAME ' (20)),

Fulltext KEY ' idx_aname_bname ' (' aname ', ' bname ')

) Engine=innodb auto_increment=6095778 DEFAULT charset=utf8mb4

OK, so far Our base tables have been built, and then we'll start to discover the mystery of full-text indexing :

Generally speaking, we will open innodb_file_per_table This option, the reason is self-evident. And we also know that the index on the table is stored on the table space of the tables, but for this full-text index, it is not the case, we go to hug a data file to see exactly ha:

-RW-RW----1 DBA dba 96K Mar 23:16 fts_0000000000000015_0000000000000018_index_1.ibd

-RW-RW----1 DBA dba 72M Mar 00:02 fts_0000000000000015_0000000000000018_index_2.ibd

-RW-RW----1 DBA dba 23M Mar 00:02 fts_0000000000000015_0000000000000018_index_3.ibd

-RW-RW----1 DBA dba 27M Mar 00:02 fts_0000000000000015_0000000000000018_index_4.ibd

-RW-RW----1 DBA dba 40M Mar 00:02 fts_0000000000000015_0000000000000018_index_5.ibd

-RW-RW----1 DBA dba 96K Mar 23:16 fts_0000000000000015_0000000000000018_index_6.ibd

-RW-RW----1 DBA dba 96K Mar 23:16 fts_0000000000000015_being_deleted_cache.ibd

-RW-RW----1 DBA dba 96K Mar 23:16 fts_0000000000000015_being_deleted.ibd

-RW-RW----1 DBA dba 96K Mar 00:02 fts_0000000000000015_config.ibd

-RW-RW----1 DBA dba 96K Mar 23:16 fts_0000000000000015_deleted_cache.ibd

-RW-RW----1 DBA dba 96K Mar 23:16 fts_0000000000000015_deleted.ibd

-RW-RW----1 DBA dba 8.5K Mar 23:43 test_fullindex.frm

-RW-RW----1 DBA dba 2.1G Mar 00:05 test_fullindex.ibd

A look at this thing, is stunned, just now we have only built a table, why so many more ibd file it? Where did the files come from?

so we connect to the database, check the table to know:

Mysql> Select Table_id,name,flag,space, Row_format from information_schema.innodb_sys_tables where NAME like '%test/ %‘;

See a few conversions first: 21  = 0x15 ;24 = ox18

     From here we can also see that these several files (fts_*) Span lang= "ZH-CN" is the data file that is stored in full-text index, these files are unified management by the system, the unified name, and its naming way believe you have seen clearly. Yes, our system assigns a idid16 binary then it can be stitched into the name of our full-text index table, the system will default to this full-text index to build 6 hash distribution, which 6 fts_****_***_index_* and then there are a few additional tables, including config

Well, now that we know how the full-text index is stored, and its intuitive representation, here's a look at how to use this fulltext .

the use of Fulltext is relatively simple, the syntax is also better understood, we can use SELECT * from test_fullindex WHERE MATCH (...) Against (...); this syntax to query the results we want. 　

first explain the meaning of the two special fields: Match () Here you need to specify the full-text index you want to use, note for innodbaname So here it should be written: match (aname); If you are creating a full-text index with two fields (aname,bname so you should write: match (Aname,bname) ()

about against () , Here are a few syntax to write:

The first we can directly specify a string, such as I want to find the word containing "netease" , then we can directly write against(' NetEase ') or write against(' NetEase ' in NATURAL languate MODE) , note that This is case-sensitive.

Two specific examples:

Mysql> SELECT * from Test_fullindex where match (aname,bname) against (' NetEase ') limit 1;

Mysql> SELECT * from Test_fullindex where match (aname,bname) against (' NetEase ' in natural language mode) limit 1;

+---------+-------+----------------+-------+

| ID | Aname | bname | CNAME |

+---------+-------+----------------+-------+

| 6095778 | NET | NetEase Nethot | Hah |

+---------+-------+----------------+-------+

The second kind: called bollean Lookup, This lookup syntax is as follows:

SELECT * from articles WHERE match (aname,bname) against ('+netease -net' in BOOLEAN MODE);

This syntax has 3 keywords: + for and,-on behalf of not , no for OR;

OK, the basic introduction to here, the following will introduce, fulltext the internal details of things.

InnoDB 5.6 One of the new features: Fulltext INDEXES[1. Brief introduction]