MySQL SQL optimization '% '

The main purpose of the design index is to help us get the results of the query quickly, whereas a like query starting with% cannot use the B-tree index.
Considering that the InnoDB tables are clustered tables (similar to the Index organization table in Oracle), and the structure of the two-level index leaf node is recorded in the (indexed fields and primary key fields), we can rewrite the SQL (MySQL optimizer is rather stupid, Need to give it enough hints) to take a lightweight approach instead of full-table sweep:
Use the index full scan to find the primary key, and then retrieve the data based on the primary key back to the table.
The speed advantage in this way is particularly noticeable when the volume of single-line records is large and the number of records in the table is high, because the IO overhead of index full scan is much smaller than the full table sweep.

The paper came to the end of the light, I know this matter to preach:
Create a test table with the self-increment primary key primary (ID) and level two index idx_name1 (name1) on the table with 5 million data in the table.

Mysql> desc test;+--------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | name1 | varchar (20) | YES | MUL |                NULL | || name2 | varchar (20) |     YES | |                NULL | || Name3 | varchar (20) |     YES | |                NULL | || Name4 | varchar (20) |     YES | |                NULL | || Name5 | varchar (20) |     YES | |                NULL | || Name6 | varchar (20) |     YES | |                NULL | || Name7 | varchar (20) |     YES | |                NULL | || Name8 | varchar (20) |     YES | |                NULL | || Name9 | varchar (20) |     YES | |                NULL | || Name10 | varchar (20) |     YES | |                NULL | |+--------+-------------+------+-----+---------+----------------+11 rows in Set (0.01 SEC) mysql> Show index from test\g*************************** 1.    Row *************************** table:test non_unique:0 key_name:primary seq_in_index:1 column_name:id Collation:a cardinality:4829778 sub_part:null packed:null Null:Index_type:BTREE commen T:index_comment: *************************** 2. Row *************************** table:test non_unique:1 key_name:idx_name1 seq_in_index:1 column_name:n      Ame1 collation:a cardinality:2414889 sub_part:null packed:null null:yes index_type:btree Comment:index_comment:2 rows in Set (0.00 sec) mysql> Select COUNT (*) from test;+----------+|  COUNT (*) |+----------+| 5000000 |+----------+1 row in Set (1.59 sec)

A like query based on NAME1 takes 11.13s, and from the execution plan, SQL executes a full table scan (Type:all):

mysql>  select * from test where name1 like ‘%O4JljqZw%‘\G*************************** 1. row ***************************    id: 1167352 name1: BO4JljqZws name2: BrfLU7J69j name3: XFikCVEilI name4: lr0yz3qMsO name5: vUUDghq8dx name6: RvQvSHHg4p name7: ESiDbQuK8f name8: GugFnLtYe8 name9: OuPwY8BsiYname10: O0oNGPX9IW1 row in set (11.13 sec)mysql> explain select * from test where name1 like ‘%O4JljqZw%‘\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: test         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4829778        Extra: Using where1 row in set (0.00 sec)

Rewrite SQL to ' select a. From Test A, (select ID from test where name1 like '%o4jljqzw% ') b where a.id=b.id; '
Tip Optimizer uses level two index idx_name1 in subqueries to get ID:

Mysql> Select A.* from Test A, (select ID from test where name1 like '%o4jljqzw% ') b where a.id=b.id\g****************** 1. Row *************************** id:1167352 name1:bo4jljqzws name2:brflu7j69j name3:xfikcveili name4:lr0yz3qmso Nam  E5:VUUDGHQ8DX name6:rvqvshhg4p name7:esidbquk8f name8:gugfnltye8 name9:OuPwY8BsiYname10:O0oNGPX9IW1 row in Set (2.46 SEC) mysql> Explain select a.* from Test A, (select ID from test where name1 like '%o4jljqzw% ') b where a.id=b.id\g***** 1. Row *************************** id:1 select_type:primary table: <derived2> type:allposs Ible_keys:null key:null key_len:null ref:null rows:4829778 extra:null********* 2. Row *************************** id:1 select_type:primary table:a type:eq_refpossible_keys:p      Rimary key:primary Key_len:4 ref:b.id rows:1  extra:null*************************** 3.  Row *************************** id:2 select_type:derived table:test Type:indexpossible_keys: NULL key:idx_name1 key_len:63 ref:null rows:4829778 extra:using where; Using index3 rows in Set (0.00 sec)

The rewritten SQL execution time is reduced to 2.46s, and the efficiency is nearly 4 times times higher!
The execution plan analysis is as follows:
Step 1:mysql first to overwrite the two-level index idx_name1 to remove the qualifying ID (Using where; Using index)
Step 2: Result set derived from step 1 table: <derived2> full table sweep, get ID (only one ID in this case meets the criteria)
Step 3: Finally get the data using the primary key back table according to the ID in step 2 (type:eq_ref,key:primary)

Summarize:
When the data volume of each record in the table is large, the SQL efficiency can be significantly improved by this method.
The data volume of each record in this experiment is still very small, if the data volume of each record is further enlarged, the execution efficiency of SQL will be improved in order of magnitude, we can verify it by ourselves.

MySQL SQL optimization '% '