Prepare to create the data table user_innodb table

DROP TABLE IF EXISTS `user_innodb`;
CREATE TABLE `user_innodb` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Union index and leftmost prefix principle

In normal development, the most common is a single column index (such as the primary key primary id). However, when a multi condition query is required, a joint index will also be created. A single column index can also be regarded as a special union index. For example, in the user table, create a joint index for name and phone.

ALTER TABLE can be used to create indexes, including normal, UNIQUE, or PRIMARY KEY indexes:

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

ALTER TABLE user_innodb add INDEX comidx_name_phone(name,phone); -- Create federated index

1.1 organization of joint index

The following figure shows the index structure of the B+ tree, which is a single value index:

It can be seen that all non leaf nodes are composed of two parts, index value + pointer, while single value index means that there is only one value (such as id) in the index value, while joint index may have multiple values (such as name and phone) in the index value

Different from single valued index

1. Union index is a composite data structure in B+ tree
2. Since the B+ tree itself is ordered, the joint index is used to build the search tree from left to right (name on the left and phone on the right). As can be seen from the above figure, name is orderly and phone is disordered. When the names are equal, the phone is ordered.
3. When the storage engine is innoDB, the leaf node stores data / primary keys

Q1: how does a federated index find data?
For example, when using where name = 'zhangsan' and phone='1320xx 'to query data

1. The B+ tree will compare name s first to determine the direction of the next search, left or right
2. Compare the phone if the name is the same

However, if the query criteria do not have a name, you do not know which node to query in the first step. Because the name is the first comparison factor when creating the search tree, the index is not used.
Q2: the relationship between joint index and single value index?
Suppose there are two slow queries in the project:

SELECT * FROM user_innodb WHERE name= ?; 
SELECT * FROM user_innodb WHERE name= ? AND phone=?;

According to our idea, one query creates one index, so do you think it is correct to create two indexes for these two SQL statements?

CREATE INDEX idx_name on user_innodb(name);  
CREATE INDEX idx_name_phoneonuser_innodb(name,phone);

When we create a joint index, we can also use the index when we use the field name on the left to query, so it is completely unnecessary to create an index for name alone, which is equivalent to establishing two joint indexes (name), (name,phone)
Creating an index(a,b,c) of three fields is equivalent to creating three indexes: index(a),index(a,b),index(a,b,c). Use where b=? And where b=? and c=? And where a =? and c = ? The index cannot be used. It is assumed that the first field cannot be used or interrupted.

1.2 leftmost prefix principle

Because the joint index contains multiple fields, it cannot be used directly like the single value index. The rules should be observed?
It is the leftmost prefix principle: the leading brother cannot die, and the middle brother cannot break

1. when creating an index, make sure to put the commonly used columns on the leftmost side. For example, can the following three statements use a joint index?
Using two fields, you can use the joint index (Note: reversing the order of the two fields does not affect it, because mysql will optimize the field order when the full value matches)

EXPLAIN SELECT * FROM user_innodb WHERE name= 'Zhang San' AND phone='12345678910'

2. using the name field on the left, you can use the joint index:

EXPLAIN SELECT * FROM user_innodb WHERE name= 'Zhang San'

3. using the phone field on the right, the index cannot be used. Full table scanning:

EXPLAIN SELECT * FROM user_innodb WHERE name= '12345678910'

Do not perform any operation on the index, because it may cause index invalidation and turn to full table scan

1.3 index failure

1. Use functions, expressions, and calculations (addition, subtraction, multiplication, and division). Because the current value cannot match the value stored in the index after being changed

SELECT * FROM user_innodb where left(name, 3)='Zhang San'；-- left Function is a string function that returns the left part of a string of a specified length

1. Using a range query will invalidate the right column. Because the search of binary tree is = search, if it is a range, it cannot continue to search

SELECT * FROM user_innodb where name='Zhang San' and age > 22；

1. like starts with a wildcard. If the mysql index fails, it will become a scan operation. Because it is impossible to determine how many characters% represents.

SELECT * FROM user_innodb where name like '%three'；

1. Invalid string without '' index. Because implicit conversion occurs, it is equivalent to an operation on the index column

SELECT * FROM user_innodb where name = 007；-- "007"From string to number 007

1. Use less or. When using or to connect, the index will fail in many cases

SELECT * FROM user_innodb where name = 'Zhang San' or name = 'Lisi'；

1. is null, is not null cannot use index

SELECT * FROM user_innodb where name is null；

==>Make a summary of this part through a doggerel:

						Full value matching my favorite, the leftmost prefix should be observed
						The leading brother cannot die, and the middle brother cannot be broken
						Less calculation on the index column, and all invalid after the range
						like Write 100% to the right and overwrite the index without writing stars
					    Unequal null values and or，Index failure should be avoided

Original reference: https://blog.csdn.net/weixin_43935927/article/details/109361168