How do I index a string field? August more challenge

In some cases, you want to query directly through a field as a condition. If the field has no index, the statement can only do a full table scan. So how do you index the string field?

Method for adding indexes to string fields

1. Prefix index – defines part of the string as an index – If the statement creating the index does not specify the prefix length – the index will contain the entire string
   
   Alter table SUser add index index1(email);
   
   2. alter table SUser add index index2(email(6));
   1. The pros and cons of prefix indexes
      1. It takes up less space
      2. Additional record scanning times may be added
   2. Select id, name, email from SUser where email=’ select id, name, email from SUser where email=’[email protected]’;
      1. Index1 [index structure of email entire string]
         1. Select * from index1 where ID = ‘[email protected]’; select * from index1 where ID = ‘[email protected]’;
         2. Select ‘ID’ from ‘primary key’; select ‘ID’ from ‘primary key’; select ‘ID’ from ‘primary key’; select ‘ID’ from ‘primary key’;
         3. =’XX’; =’XX’; =’XX’; =’XX’;

         You only need to retrieve data from the primary key index once. The system considers that only one row is scanned.

      2. Index [email(6) index structure]
         1. Select * from index tree where index = ‘zhangs’; select * from index tree where index = ‘ID1’;
         2. Select * from ‘ID1′ where ’email’ is not ‘[email protected]’; select * from ‘ID1′ where ’email’ is ‘[email protected]’;
         3. Select * from index2 where ‘zhangs’ is found; select * from ID2 where ID2 is found;
         4. I’m going to take the whole row from the ID index and I’m going to judge the value pairs and I’m going to add that row to the result set;
         5. Repeat steps 3 and 4. If the value of index2 index tree is not ‘zhangs’, the loop ends.

         Get data back to primary key index 4 times – scan 4 rows

   If the prefix index is used, the number of times the query statement reads data may increase Use the prefix index, define the length, can save space + do not add too much extra query cost

   How do I determine how long a prefix to use?

   The indexing is focused on differentiability - the more differentiability the better - the fewer duplicate keys by counting how many different values there are on the index to determine how long the prefix should be used to pre-set an acceptable percentage of losses - find values that are not less than L*95%Copy the code

   3. Effect of prefix indexes on overwrite Indexes Cannot be used with prefix Indexes Optimization of query performance – The system is not sure whether the definition of a prefix index truncates complete information or queries will go back to the primary key
2. Other ways – When the prefix is not distinguishable
   1. Reverse storage – for example, the last 6 digits of the ID card have enough distinctiveness, the first ones do not
   2. Hash field – Creates an integer field on the table – Stores the VERIFICATION code of the ID card and creates an index on this field
   3. The difference between reverse storage and hash fields:
      1. None of them support range queries
         1. Reverse order how to check by original order range
         2. Hash – Equivalent query