Principles and optimization of database Indexing __ Index

This article mainly originates from the Internet mainstream article, just according to the individual understanding slightly makes the integration, behind has the reference link. I. Summary

This paper takes the MySQL database as the research object and discusses some topics related to database index. Specifically, MySQL supports a number of storage engines, and the various storage engines support different indexes, so the MySQL database supports multiple types of indexes, such as btree indexes, hash indexes, full-text indexing, and so on. To avoid confusion, this article will focus on the Btree index only, since this is the main index to use when MySQL is used, and the hash index and FULL-TEXT index are not discussed in this article. second, the common query algorithm and data structure

Why this is about query algorithms and data structures. Because the reason to build an index, in fact, is to build a data structure, you can apply an efficient query algorithm, and ultimately improve the data query speed. 2.1 Nature of the index

MySQL's official definition of the index is that index is the data structure that helps MySQL get data efficiently. By extracting the skeleton of the sentence, you can get the nature of the index: The index is the data structure . 2.2 Common query algorithms

As we know, database query is one of the most important functions of database. We all want to query the speed of data as quickly as possible, so the database system designers from the perspective of the query algorithm to optimize. So what query algorithms can make queries faster? 2.2.1 Order Search (linear search)

The most basic query algorithm is, of course, sequential lookup (linear search), that is, the method of comparing each element, but this algorithm is very inefficient when the data volume is very large.
Data structures: ordered or unordered queues
Complexity: O (N)
Instance code:

Order Lookup
int sequencesearch (int a[], int value, int n)
{
    int i;
    For (i=0 i<n; i++)
        if (a[i]==value) return
            i;
    return-1;
}

2.2.22 Point Search (binary search)

Query methods that are faster than sequential lookups should be two-point lookup, the principle of binary lookup is that the lookup process starts with the middle element of the array, and if the intermediate element is exactly the element to look for, the process ends; If a particular element is greater than or less than the intermediate element, it is found in the half of the array greater than or less than the middle element. And start with the middle element as the beginning of the comparison. If an array of steps is empty, the representation cannot be found.
Data structure: ordered array
Complexity: O (LOGN)
Instance code:

Binary lookup, recursive version
int BinarySearch2 (int a[], int value, int low, int high)
{
    int mid = low+ (high-low)/2;
    if (A[mid]==value) return
        mid;
    if (a[mid]>value) return
        BinarySearch2 (A, value, low, mid-1);
    if (a[mid]<value) return
        BinarySearch2 (A, value, mid+1, high);
}

2.2.32 Fork Sort Tree Lookup

The characteristic of binary sort tree is that if its left subtree is not empty, the value of all nodes in the left subtree is less than the value of its root node; if its right subtree is not empty, the value of all nodes on the right subtree is greater than the value of its root node, and its left and right subtrees are two-fork-sorted trees, respectively.

The principle of search: If B is an empty tree, the search fails, otherwise: if x equals the value of the data field of the root node of B, the lookup succeeds; otherwise: if x is less than the value of the data field of the root node of B, the left subtree is searched; otherwise: find the right subtree.

Data structure: Two fork sort tree
Time complexity: O (log2n) 2.2.4 Hash hashing method (hash table)

The principle is to create a hash table (hash list) based on the key value and hash function, and to locate the data element position by using the hash function.

Data structure: Hash table
Time complexity: Almost O (1), depending on how much conflict is generated. 2.2.5-Block lookup

Block lookup, also known as index order lookup, is an improved method of sequential lookup. The idea of the algorithm is to divide n data Elements "by block order" into M blocks (m≤n). The nodes in each piece don't have to be ordered, however, blocks and blocks must be "sorted by block", that is, the keywords for any element in block 1th must be less than the keywords of any element in the 2nd block, and each element in the 2nd block must be less than any element in the 3rd block, and so on.
　　
Algorithm Flow: First select the largest number of keywords in each block to form an index table; Find in two parts: first, the index table is binary lookup or sequential lookup, to determine where the record of the discovery is recorded in, and then in the determined block with the order method to find.

Each comparison of this search algorithm narrows the search range by half. Their query speed has been greatly improved, the complexity of the. A little analysis will find that each lookup algorithm can only be applied to a specific data structure, such as binary lookup requires that the data be ordered, and binary tree lookup can only be applied to the binary lookup tree, but the organization of the data itself can not fully meet the various data structures (for example, It is theoretically impossible to organize both columns sequentially, so in addition to data, the database system maintains a data structure that satisfies a particular lookup algorithm, which refers to data in some way, so that advanced lookup algorithms can be implemented on these data structures. This data structure is the index. 2.3 Balanced multi-channel search tree B-tree (b-tree)

The above mentioned two fork tree, its search time complexity of O (log2n), so its search efficiency and the depth of the tree, if you want to improve the speed of the query, it is necessary to reduce the depth of the tree. To reduce the depth of the tree, the natural way is to use the multi-fork tree, combined with the idea of balanced binary tree, we can build a balanced multi-tree structure, and then we can build a balanced multi-channel search algorithm, improve the large amount of data search efficiency. 2.3.1 B Tree

B-Tree (Balance) is also called a B-tree (in fact, B-is translated by B-tree, so B-tree and B-tree is a concept), it is a balanced multi-channel lookup tree. The following figure is a typical B-tree:

From the above we can see some of the characteristics of B-tree, in order to better describe the B-tree, we define the record as a two-tuple [key, Data],key for the record of the key value, data for other figures (the above figure only key, did not draw data). The following is a detailed definition of B-tree:

1. There is a root node, the root node has only one record and two children or the root node is empty;
2. The key and pointer in each node record are spaced together, the pointer points to the child node, and
3. D is the width of the tree, except for the leaf node, where each node has a [d/2,d-1] record. And some of the key records are from left to right by size, there are [d/2+1,d] children;
4. In a node, all keys in the nth subtree are less than the nth key in this node, which is greater than the first n-1 key, For example, all keys in the 2nd sub node e of the B node in the above figure are less than the 2nd key 9 in B, greater than the 1th key 3;
5. All leaf nodes must be at the same level, i.e. they have the same depth;

Because of the characteristics of B-tree, the algorithm of retrieving data by key in B-tree is very intuitive: first, from the root node for binary lookup, if found then return the corresponding node data, otherwise the corresponding interval of the pointer to the node recursively lookup until the node or find a null pointer, the former search for success , the latter lookup failed. The pseudo code for the lookup algorithm on the B-tree is as follows:

Btree_search (node, key) {
     if (node = null) return null;
     foreach (Node.key) {
          if (node.key[i] = = key) return node.data[i];
          if (Node.key[i] > key) return Btree_search (Point[i]->node);
      }
     Return Btree_search (Point[i+1]->node);
  }
data = Btree_search (root, My_key);

There are a series of interesting properties about B-tree, for example, a b-tree with a degree of D, with its index N keys, the upper limit of H is logd ((n+1)/2), a key is retrieved, and the asymptotic complexity of the number of lookup nodes is O (LOGDN). It can be seen from this that b-tree is a very efficient index data structure.

In addition, because inserting deletes a new data record can destroy the nature of b-tree, so when inserting deletes, need to divide, merge, transfer to the tree to maintain b-tree nature, this article does not intend to discuss b-tree these content completely, Because there is already a lot of information about the mathematical nature of b-tree and insert deletion algorithm, interested friends can consult other literature for detailed study. 2.3.2 B+tree

In fact, B-tree has many variants, the most common of which is b+tree, such as MySQL on the common use of B+tree to implement its index structure. Compared to the B-tree, the B+tree has the following differences: The pointer cap for each node is 2d instead of 2d+1; the inner node does not store data, only key is stored, and leaf nodes do not store pointers;

Here is a simple b+tree schematic.

Since not all nodes have the same domain, the b+tree and inner nodes generally differ in size. This differs from B-tree, although the number of keys and pointers stored in the B-tree may be inconsistent, but the domain and the upper bound of each node are the same, so in the implementation b-tree often apply the same amount of space to each node. Generally speaking, b+tree is more suitable than b-tree to implement the external storage index structure, the specific reason is related to the principle of external memory and the principle of computer access, which will be discussed below. 2.3.3 B+tree with sequential access pointers

Generally, the b+tree structures used in the database system or file system are optimized on the basis of classical b+tree, and the sequential access pointers are added.

As shown in the figure, each leaf node in B+tree adds a pointer to the neighboring leaf node, forming a b+tree with sequential access pointers. The purpose of this optimization is to improve the performance of the interval access, for example, in Figure 4, if you want to query the key is from 18 to 49 of all data records, when found 18, just follow the node and pointer sequence traversal can be one-time access to all data nodes, a great mention of the interval query efficiency.

This section provides a brief introduction to B-tree and B+tree, and the next section describes why the current b+tree is the preferred data structure for the database system to implement the index, based on the memory access principle. third, the index data structure sets the related computer principle

As mentioned above, binary tree, red and black tree and other data structures can also be used to achieve the index, but the file system and database system commonly used B-/+tree as the index structure, this section will be combined with the computer composition principle related knowledge to discuss b-/+tree as the theoretical basis of indexing. 3.1 Two types of storage

In the computer system generally contains two types of storage, computer main memory (RAM) and external storage (such as hard disk, CD, SSD, etc.). When designing indexing algorithms and storage structures, we have to take into account both types of storage features. Main memory reading speed, relative to main memory, external disk data reading rate is slower than the master and several orders of magnitude, the difference between the details will be described in detail. All of the above query algorithms are assumed that the data stored in the computer main memory, computer main memory is generally relatively small, the actual database data are stored to external memory.

In general, the index itself is also very large, it is not possible to store all in memory, so the index is often stored in the form of an index file on the disk. In this way, the index lookup process will generate disk I/O consumption, relative to memory access, I/O access consumption of several orders of magnitude, so the evaluation of a data structure as an index of the most important indicator is the number of disk I/O operations in the search process of the gradual complexity. In other words, the structure of the index organizes to minimize the number of disk I/O accesses during the lookup process. The following is a detailed description of memory and disk access principles, and then combining these principles to analyze the efficiency of B-/+tree as an index. 3.2 Main Memory Access principle

At present, the main memory used by the computer is basically random read and write memory (RAM), the structure of modern RAM and the principle of access is more complex, here in this article to discard the specific differences, abstract a very simple access model to illustrate the working principle of RAM.

From an abstract perspective, main memory is the matrix of a series of storage units, each storing a fixed size of data. Each storage unit has a unique address, and modern main memory is more complex to address, which is simplified to a two-dimensional location: through a row address and a column address can be uniquely positioned to a storage unit. The figure above shows a main memory model of 4 x 4.

Main memory access process is as follows:

When the system needs to read main memory, the address signal is placed to main memory, main memory read the address signal, parse the signal and locate to the designated storage unit, and then put this storage unit data on the bus for other parts to read. The process of writing main memory is similar, the system will write the cell address and data on the address bus select、read and bus, main memory read two bus content, do the corresponding write operation.

Here you can see that the main memory access time only with the number of accesses linear relationship, because there is no mechanical operation, two access data "distance" does not have any effect on the time, for example, first take A0 and then take the A1 and first take the A0 and then take the D3 time consumption is the same. 3.3 Principle of disk access

As stated above, indexes are generally stored on disk as files, and index retrieval requires disk I/O operations. Unlike main memory, disk I/O has a mechanical movement cost, so the time consuming of disk I/O is enormous.

Disk read data by mechanical movement, when the need to read data from the disk, the system will be the data logical address to the disk, the control circuit of the disk according to the addressing logic to translate the logical address to the physical address, that is, determine the data to read in which track, which sector. To read the data in this sector, to achieve this, the head needs to be moved to the corresponding track, which is called Seek, and the time spent is called seek time, and the disk rotation rotates the target sector to the head, and the process takes time called rotational time, The last is the transmission of the read data. Therefore, the time spent reading the data can be divided into search time, rotation delay, transmission time three parts. Wherein: Seek time is the time required for the magnetic arm to move to a specified track, and the main disk is generally below 5ms. Rotation delay is what we often hear about disk speed, such as a disk 7200 rpm, which means 7,200 times per minute, that is 1 seconds to turn 120 times, rotation delay is 1/120/2 = 4.17ms. Transmission time refers to the time that is read from disk or written to disk, typically in a fraction milliseconds, and is negligible relative to the first two times.

Then the time to access the disk, that is, the time of disk IO is about equal to 5+4.17 = 9ms, sounds pretty good, but to know that a 500-mips machine can execute 500 million instructions per second, because the instructions rely on the nature of electricity, In other words, the time to perform an IO can execute 400,000 instructions, the database is 1.001 billion or even tens data, 9 milliseconds each time, is obviously a disaster. 3.4 Principle of locality and disk pre-read

Because of the nature of the storage media, the disk itself is much slower to access than main memory, coupled with the cost of mechanical exercise, disk access speed is often one of the hundreds of of main memory, so in order to improve efficiency, to minimize disk I/O. To do this, the disk is often not read strictly on demand, but read each time, even if only a single byte, the disk will start from this location, sequentially reading a certain length of data into memory. The rationale for this is the famous local principle in computer science: When a data is used, the data near it is usually used immediately. The data that is required during program execution is usually more centralized.

Because of the high efficiency of disk sequential reads (no seek time is required, only a small amount of rotation time), prefetching can improve I/O efficiency for locally-accessible programs. The length of the prefetch is typically the integer multiple of the page. Pages are logical blocks of Computer Management memory, and hardware and operating systems often split main memory and disk storage into contiguous blocks of equal size, each of which is called a page (in many operating systems, the page size is typically 4k), and main memory and disk Exchange data in page units. When the program to read the data is not in main memory, will trigger a page fault exception, the system will be sent to the disk read signal, the disk will find the starting position of data and read one or several pages back to load in memory, and then abnormal return, the program continues to run. IV. Data structure B-/+tree and performance analysis of database indexing

Here we can finally analyze why the database index uses the B-/+TREE storage structure. As mentioned above, database indexes are stored on disk and we generally use disk I/O times to evaluate the index structure. First from the B-tree analysis, according to the definition of B-tree, you can retrieve the maximum access to the H-1 node (the root node resident memory). The designer of the database system cleverly utilizes the disk prefetching principle to set the size of a node to be equal to a page so that each node needs only one I/O to be fully loaded. In order to achieve this goal, the following techniques need to be used to actually implement B-tree: each time you create a new node, directly request a page space, so that a node is physically also stored in a page, plus the computer storage allocation is page-aligned, the implementation of a node only once I/O.

A maximum of h-1 I/O (the root node resident memory) is required at one time in B-tree, and the asymptotic complexity is O (h) =o (LOGDN). In general practical applications, the out degree d is a very large number, usually more than 100, so H is very small (usually no more than 3).

To sum up, if we use the B-tree storage structure, the number of I/O in the search is generally not more than 3 times, so using B-tree as index structure efficiency is very high. Performance Analysis of 4.1 B + Tree

As we know from the above, the search complexity of B-trees is O (h) =o (LOGDN), so the greater the size of the tree, the smaller the depth h, the less I/O. B+tree precisely can increase the width of the degree d, because each node size is one page size, so the upper limit of the degree depends on the size of key and data within the node:

Dmax=floor (pagesize/(keysize+datasize+pointsize))//floor represents rounding down

Because the b+tree is removed from the data domain, it can have a greater degree of performance, thereby having better properties. 4.2 B + Tree lookup process

B-Tree and B + Tree lookup processes are basically consistent. As shown in the figure above, if you are looking for data item 29, then the disk Block 1 is loaded into memory by the disk, at which time Io, in memory with a binary lookup to determine 29 between 17 and 35, locking disk Block 1 P2 pointer, memory time because very short (compared to disk IO) can be ignored, Disk Block 3 is loaded into memory by the disk address of the P2 pointer of disk Block 1. The second io,29 occurs between 26 and 30, locking the disk block 3 P2 pointer, loading the disk block 8 to memory via the pointer, taking a third Io, while in memory doing a binary lookup find 29, ending the query, totaling three times io. The real situation is that 3-storey B + trees can represent millions of data, if millions of data lookups only need three times IO, performance improvement will be huge, if there is no index, each data item will occur once IO, then a total of million Io, obviously the cost is very high.

This chapter discusses the index-related data structure and algorithm from a theoretical point of view, and the next chapter discusses how B+tree is implemented as a MySQL index, combining the MyISAM and INNDB storage engines to introduce two different index implementations of nonclustered indexes and clustered indexes. Five, MySQL index implementation

In MySQL, the index belongs to the concept of the storage engine level, the different storage engine to the index implementation is different, this article mainly discusses the MyISAM and InnoDB two storage engine's index realization way. 5.1 MyISAM Index Implementation

The MyISAM engine uses B+tree as the index structure, and the data field of the leaf node holds the address of the record. The following diagram is the schematic diagram of the MyISAM index:

Here set a total of three columns, assuming that we are Col1 as the primary key, then the image above is a MyISAM table of the main index (Primary key) schematic. You can see that the MyISAM index file only holds the address of the data record. In MyISAM, the primary index and the secondary index (secondary key) are structurally indistinguishable, except that the primary index requires the key to be unique, and the key of the secondary index can be duplicated. If we establish a secondary index on the Col2, the structure of this index is shown in the following illustration:

It's also a b+tree,data domain. The address of the data record. Therefore, the algorithm of index retrieval in MyISAM is to search the index according to the B+tree search algorithm first, if the specified key exists, take out the value of its data field, then read the corresponding data record with the value of the data field as the address.
The MyISAM index is also called "nonclustered," and is so called to distinguish it from the InnoDB clustered index. 5.2 InnoDB Index Implementation

Although InnoDB also uses B+tree as the index structure, the implementation is different from MyISAM.

The first major difference is that the InnoDB data file itself is the index file. As you know from the above, the MyISAM index file and the data file are separate, and the index file only holds the address of the data record. In InnoDB, the table data file itself is an index structure that is organized by b+tree, and the leaf node of this tree holds the complete data record. The key of this index is the primary key of the datasheet, so the InnoDB table data file itself is the primary index.

The diagram above is a schematic diagram of the InnoDB Primary index (also the data file), and you can see that the leaf node contains the complete data record. This index is called a clustered index. Because the InnoDB data file itself is clustered by the primary key, the INNODB requires the table to have a primary key (MyISAM can not), and if not explicitly specified, the MySQL system automatically selects a column that uniquely identifies the data record as the primary key, and if no such column exists, Then MySQL automatically generates an implied field for the InnoDB table as the primary key, which is 6 bytes long and has a type of length shaping.

The second difference from the MyISAM index is that the secondary index data field of the InnoDB stores the value of the corresponding record primary key instead of the address. In other words, all of the secondary indexes of INNODB reference the primary key as the data field. For example, the following figure is a secondary index defined on the COL3:

The ASCII code of English characters is used here as the comparison criterion. Clustered indexes This implementation makes searching by primary key efficient, but a secondary index search requires a two-pass index: First retrieving the secondary index to obtain the primary key, and then retrieving the record with the primary key to the primary index.

Understanding how different storage engines are indexed is very helpful for proper use and optimization of indexes, for example, once you know InnoDB's index implementation, it's easy to see why you don't recommend using too long fields as primary keys, because all secondary indexes reference the primary index, and the too long primary index makes the secondary index too large. For example, using a non monotone field as a primary key is not a good idea in InnoDB because the InnoDB data file itself is a b+tree, and a non monotone primary key causes the data file to be frequently split and adjusted to maintain b+tree characteristics when inserting new records, which is very inefficient. Using the self-added field as the primary key is a good choice.

These index-related optimization strategies are specifically discussed in the next chapter. Vi. Index usage strategy and optimization

MySQL's optimization is mainly divided into structure optimization (Scheme optimization) and query Optimization (optimization). The high performance indexing strategy discussed in this chapter mainly belongs to the structure optimization category. The content of this chapter is based entirely on the rationale above, and once the mechanism behind the index is understood, the choice of high-performance strategies becomes pure reasoning, and the logic behind these strategies can be understood. 6.1 Combined index and the principle of the leftmost prefix Federated Index (composite index)

First, introduce the federated index. Federated indexes are actually very simple, with only one field relative to the general index, and the federated Index can create an index for more than one field. The principle is simple, for example, if we create a federated index on the (a,b,c) field, the index records are sorted first by the A field, followed by the B field and then the C field, so the joint index is characterized by: the first field must be ordered

When the first field value is equal, the second field is ordered, for example, when a=2 in the following table all B values are ordered, and so on, and so on, when the same B is worth all C fields in an ordered order.

| A | B | C |
| 1 | 2 | 3 |
| 1 | 4 | 2 |
| 1 | 1 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 6 |
| 2 | 5 | 5 |

In fact, the search of the federated index is the same as the dictionary, first look at the first letter, and then according to the second letter, or only according to the first letter, but can not skip the first letter from the second letter to start looking. This is called the principle of the leftmost prefix. principle of the left prefix

Let's go over the query for the Federated index in more detail. Or the example above, we built a federated index on the (a,b,c) field, so the index was first pressed A and then B and then C, so:

The following query methods can be used in the index

SELECT * FROM table where a=1,
select * from table where a=1 and b=2,
select * from table where a=1 and b=2 and c= 3;

The above three queries are available to the index in the Order of (a), (A,b), (A,B,C), which is the leftmost prefix match.

If the query statement is:

SELECT * FROM table where a=1 and c=3; then only index A is used.

If the query statement is:

SELECT * FROM table where b=2 and c=3 because the leftmost prefix A is not used, so this query is user-indexed.

The index code is used if the leftmost prefix is used, but the order is reversed.

Like what:

SELECT * FROM table where b=2 and a=1,
select * from table where b=2 and a=1 and c=3;

If you use the leftmost prefix and just reverse the order, you can use the index, because the MySQL query optimizer will determine how efficiently to correct this SQL statement, and finally generate a real execution plan. However, we'd better query by index order so that the query optimizer does not have to recompile. Prefix index

In addition to the federated index, there is actually a prefix index for MySQL. The prefix index is to use the prefix of the column instead of the entire column as the index key, which can be done both to make the selectivity of the prefix index close to the full column index and to reduce the index file size and maintenance cost because the index key becomes shorter.

In general, a prefix index can be used: a string column (Varchar,char,text, etc.) that requires a full field match or a forward match. That is, the "xxx" or the like ' xxx% ' string itself may be longer, and the first few characters begin to be different. For example, we have no meaning in using the prefix index on Chinese names, because the Chinese name is very short, in addition to the recipient address using a prefix index is not very practical, because on the one hand, the address is usually the beginning of xx province, that is, the first few characters are similar, and the recipient address is generally like '% xxx% ', does not use the front match. Conversely, the name of a foreigner can be indexed with a prefix because its characters are longer and the first few characters are more selective. The same e-mail message is also a field that can be indexed with a prefix. The first half of the character's index selectivity is already close to the whole-field index selectivity. If the length of the entire field is 20, index selectivity is 0.9, and we have a prefix index of the first 10 characters and only 0.5 of the selectivity, then we need to continue to increase the length of prefix characters, but this time the advantage of the prefix index is not obvious, not too much to build a prefix index necessary.

Some articles also mention the following:

The MySQL prefix index can effectively reduce the size of index files and increase the speed of indexing. But the prefix index also has its disadvantages: MySQL cannot use the prefix index in order by or GROUP by, nor can it be used as an overlay index (covering index).6.2 Index Optimization StrategyThe leftmost prefix matching principle, which says that primary key external check must be indexed to the columns that appear in Where,on,group By,order by, use the index to select the highest-distinguishing column as the index, and the formula for the degree of discrimination is count (distinct col)/count (*). Represents the proportion of the field does not repeat, the greater the proportion the fewer records we scan, the distinction between unique keys is 1, and some states, gender fields may be in front of large data to distinguish the degree is 0 to the smaller data columns using the index, which makes the index file smaller, while in memory can also load more index columns cannot participate in the calculation, Keep the column "clean", such as from_unixtime (create_time) = ' 2014-05-29 ' can not use to the index, the reason is very simple, B + trees are stored in the data table in the field values, but when retrieving, all elements need to apply functions to compare, Obviously the cost is too high. So the statement should be written as Create_time = Unix_timestamp (' 2014-05-29 '); Use the prefix index to extend the index as much as possible for longer strings, and do not create new indexes. For example, the table already has an index of a, now want to add (a,b) index, then only need to modify the original index can not too much to create the index, weighing the number of indexes and the relationship between DML, DML is inserting, delete data operations. This needs to weigh a problem, the purpose of indexing is to improve the efficiency of the query, but the establishment of too many indexes, will affect the speed of inserting, delete data, because we modified the table data, the index needs to be adjusted to rebuild for like query, "%" do not put in front.
SELECT * fromhoudunwangwhereunamelike ' backing% '-Go index
SELECT * Fromhoudunwangwhereunamelike% backing%--no index query where condition data type does not match and cannot use index
Strings do not use indexes compared with numbers;
CREATE TABLEa (Achar (10));
EXPLAIN SELECT * fromawherea= "1"-Go Index
EXPLAIN SELECT * from a WHERE a=1– does not go index
Regular expressions do not use indexes, which should be well understood, so why is it difficult to see the REGEXP keyword in sql

Reference articles:

Http://blog.csdn.net/suifeng3051/article/details/49530299?locationNum=1
Http://tech.meituan.com/mysql-index.html
https://yq.aliyun.com/articles/39841
http://blog.csdn.net/lovelion/article/details/8462814