MySQL 5.7 InnoDB Full-text index

Because of the increasing demand for fuzzy search, a full-text index is created from the library to cater for a variety of search needs. Now create a full-text search for a user name, with a full table size of 1100W records around.

1. Table structure Query

Mysql> desc ucenter_member;+------------------+------------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra |+------------------+------------------+------+-----+------------+----------------+| ID | Int (Ten) unsigned | NO | PRI | NULL | auto_increment | | Nickname | char (20) | YES |            MUL |                | || Devicetoken | varchar (60) | NO |            MUL |                | || Version | char (10) |     YES |            |                | || App_type | tinyint (1) |     YES | |                0 | || password | char (32) |     NO | |                NULL | || Salt | CHAR (4) |     NO | |                NULL | || Mobile | varchar (13) | YES | MUL |                NULL | || Avatar | Int (11) |     NO | |   0 |             || sex | tinyint (1) |     NO | |                0 | || Birthday | Date |     YES | |                0000-00-00 | |+------------------+------------------+------+-----+------------+----------------+11 rows in Set (0.00 sec)

Necessary parameter settings

Mysql> Show variables like ' Ft_min_word_len '; +-----------------+-------+| Variable_name   | Value |+-----------------+-------+| Ft_min_word_len | 1     |+-----------------+-------+1 row in Set (0.00 sec)

　　

2. Add full-text index

Mysql> ALTER TABLE Ucenter_member ADD fulltext Index Ix_ft_ucenter_member_nickname (nickname) with parser Ngram;

　　

3, add the process (add the whole process of the full text for a long time, and during this time all the business will not be able to execute)

Master-slave replication will be stopped
Mysql> Show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:192.168.1.200 Master_user:slave master_port:3306 connect_retry:60 master_log_file:mybin.000750 read_master_log_pos:746166232 Relay_log_file:relay-bin.             002247 relay_log_pos:17345090 relay_master_log_file:mybin.000750 Slave_io_running:yes Slave_sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Ta Ble:replicate_ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:sys.%,mysql.%,informatio n_schema.%,performance_schema.% last_errno:0 Last_error:skip_counte   r:0 exec_master_log_pos:736231270 relay_log_space:27280306           Until_condition:none until_log_file:until_log_pos:0 Master_ssl_allow Ed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_                Cipher:master_ssl_key:seconds_behind_master:1104master_ssl_verify_server_cert:no last_io_errno:0 last_io_error:last_sql_errno:0 Last_sql_error:replicate             _ignore_server_ids:master_server_id:1 master_uuid:5a00f33b-8551-11e5-9122-0016310207a0      Master_Info_File:mysql.slave_master_info sql_delay:0 Sql_remaining_delay:null                  Slave_sql_running_state:waiting for dependent transaction to commit master_retry_count:86400 Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Mas        Ter_ssl_crlpath:    retrieved_gtid_set:5a00f33b-8551-11e5-9122-0016310207a0:1451340674-1451398198 executed_gtid_set:5a00f33b-         8551-11e5-9122-0016310207a0:1-1451377326,9905e5a2-815d-11f5-8244-00163f004aa2:1-84 Auto_position:1 Replicate_rewrite_db:channel_name:1 row in Set (0.00 sec)

Query Process

Mysql> Show processlist;+----+-------------+-----------+--------+---------+------+--------------------------- ------------------+-------------------------------------------------------------------------------------------- ----------+| Id | User | Host | db | Command | Time | State | Info |+----+-------------+ -----------+--------+---------+------+---------------------------------------------+---------------------------  ---------------------------------------------------------------------------+| 1 |           System user | | NULL | Connect | 1673 | Waiting for Master to send event |  NULL | | 2 |           System user | | NULL | Connect | 1123 | Waiting for dependent transaction to commit |                                    Null                                                             || 3 |           System user | | NULL | Connect | 1123 | Waiting for table Metadata Lock |  NULL | | 4 |           System user | | NULL | Connect | 1195 | Waiting for a event from Coordinator |  NULL | | 5 |           System user | | NULL | Connect | 1673 | Waiting for a event from Coordinator |  NULL | | 6 |           System user | | NULL | Connect | 1673 | Waiting for a event from Coordinator |  NULL | | 9 | Root | localhost | Peiyin | Query | 1123 | Altering table | Alter Table Ucenter_member Add fulltext Index Ix_ft_ucenter_member_nickname (nickname) with parser ng | | 14 | Root | localhost | NULL |    Query | 0 | Starting | Show Processlist |+----+-------------+ -----------+--------+---------+------+---------------------------------------------+---------------------------　　 ---------------------------------------------------------------------------+8 rows in Set (0.00 sec)

　　

4. Complete the creation

Mysql> ALTER TABLE Ucenter_member ADD fulltext Index Ix_ft_ucenter_member_nickname (nickname) with parser Ngram; Query OK, 0 rows affected, 1 warning (min 7.29 sec) records:0  duplicates:0  warnings:1

5. Query Index

Query by Boolean full-text search mode

Mysql> Select Version,nickname from Ucenter_member where match (nickname) against (' China ' in Boolean mode) limit 10;+------ ---+--------------------------------------------------+| Version | Nickname                                         |+---------+--------------------------------------------------+| 4.63    | I love China position China                                | |         | China Unicom is not a Chinese mobile                 | | 4.21    | Jiang (China, North Branch, China, Shanghai Airlines)                   | | 4.63    | I am Chinese, I love China | |                             4.40    | Chinese Chinese Heart                                     | | 4.52    | China Youth Wealth                         | | 4.52    | Perfect China, China perfect                               | | 4.81    | Chinese dream, dream China |                                   | 4.52    | Chinese Dream ~ my dream                                   ||         | A   Chinese Dream                                       |+---------+--------------------------------------------------+10 rows in Set (0.01 sec)

Mysql> Select Version,nickname from Ucenter_member where match (nickname) against (' Chinese Dream ' in Boolean mode) limit 10; Empty Set (0.03 sec)

　　

Query by natural Language search mode

Mysql> Select Version,nickname from Ucenter_member where match (nickname) against (' China ' in natural language mode) Limit 1 0;+---------+--------------------------------------------------+| Version | Nickname |+---------+--------------------------------------------------+| 4.63 |         I love China position China | | | China Unicom is not a Chinese mobile 4.21 | Jiang (China, North Branch, China and Shanghai Airlines) | | 4.63 | I am a Chinese, I love China | | 4.40 | Chinese Chinese Heart | | 4.52 | Chinese special police--Chinese Youth Wealth | | 4.52 | Perfect China, China perfect | | 4.81 | China dream, dream China | | 4.52 |         Chinese Dream-my Dream | | | A Chinese Dream |+---------+--------------------------------------------------+10 rows in SE T (0.01 sec) mysql> Select Version,nickname from Ucenter_member where match (nickname) against (' Chinese Dream ' in natural language mode) limit 10;+------------+--------------------------------------------------+ | Version | Nickname |+------------+--------------------------------------------------+| 4.81 | China dream, dream China | | 4.63 | Chinese Dream my dream, my dream is the industry top | | 4.52 |            Chinese Dream-my Dream | | |            A Chinese Dream | | | Chinese Dream | | 3.17 | Chinese Dream | | 3.171 | AST China Dream | | 3.20201505 | Chinese dream of a Red mansions | | 3.211 | Chinese Dream | | 4.63 |  Chinese Dream |+------------+--------------------------------------------------+10 rows in Set (0.05 sec)

6, the resulting file

[[email protected] mydbs]# ls-alh fts*-rw-r-----1 mysql mysql 104M Oct 11:47 fts_0000000000000353_00000000000007 10_index_1.ibd-rw-r-----1 mysql mysql 17M Oct 11:47 fts_0000000000000353_0000000000000710_index_2.ibd-rw-r-----1 my SQL MySQL 14M Oct 11:47 fts_0000000000000353_0000000000000710_index_3.ibd-rw-r-----1 mysql mysql 40M Oct 11:47 FT S_0000000000000353_0000000000000710_index_4.ibd-rw-r-----1 mysql mysql 44M Oct 11:48 fts_0000000000000353_ 0000000000000710_index_5.ibd-rw-r-----1 mysql mysql 212M Oct 11:47 fts_0000000000000353_0000000000000710_index_6. Ibd-rw-r-----1 mysql mysql 96K Oct 11:43 fts_0000000000000353_being_deleted_cache.ibd-rw-r-----1 mysql mysql 96K Oc T-11:42 fts_0000000000000353_being_deleted.ibd-rw-r-----1 mysql mysql 96K Oct 11:41 fts_0000000000000353_config.ib D-rw-r-----1 mysql mysql 96K Oct 11:40 fts_0000000000000353_deleted_cache.ibd-rw-r-----1 mysql mysql 96K Oct 26 12: Fts_0000000000000353_deleted.ibd

　　

　　

MySQL 5.7 InnoDB Full-text index