I Several cases of index failure

The index does not always take effect. For example, the following situations will lead to index invalidation

1. If there is or in the condition, it will not be used even if some of the conditions are indexed (this is why or is used as little as possible) (user_id is not indexed)
   Note: to use or and make the index effective, you can only add an index to each column in the or condition

     # Index validation
     select * from user where id = 140  
     
     # Index does not take effect
     select * from user where id = 140 or user_id  = 124

1. For the composite index, if the front row is not used, the subsequent columns will not be able to use the class phonebook.
2. like query starts with%

     # Index validation
     select * from user where name like 'M%'  
     
     # Index does not take effect
     select * from user where name like '%M'

1. If there is an implicit conversion of the data type of the index column, the index cannot be used. For example, if the column type is a string, the data must be quoted in quotation marks in the condition, otherwise the index will not be used
   Note: name is a string.

    # Index validation
     select * from user where name = '123'
     
    # Index does not take effect
     select * from user where name = 123

1. There are mathematical operations on the index column in the where clause, and the index is not used

    # Index validation
     select * from user where id = 1
     
    # Index does not take effect
     select * from user where id  =  id +1

1. In the where clause, the function is used for the indexed column, but the index is not used

    # Index validation
     select * from user where id = 1
     
    # Index does not take effect
     select * from user where  ABD(id) = 1

1. If mysql estimates that using full table scanning is faster than using indexes, indexes are not used
2. NULL value condition exists

    # Index does not take effect
     select * from user where  name = null

II When are indexes not recommended?

1. Do not use indexes for fields with poor data uniqueness (when there are only several values for a field)

For example, there are only two possible data. It means that the binary tree level of the index is less and more horizontal. Such a binary tree lookup is no different from a full table scan.

1. Do not use indexes for frequently updated fields

For example, frequent changes in logincount login times lead to frequent changes in indexes, which increases database workload and reduces efficiency.

1. Do not add an index when the field does not appear in the where statement. If there are conditions such as IS NULL /IS NOT NULL/ like '% input character%', it is not recommended to use an index

Only when the where statement appears will mysql use the index

4) The use of index columns in the where clause is not equal to (< >), and the effect of using index is general

III When should an index be established

1. Primary key of table

Automatic unique indexing

Such as ZL_ hbs_bh (user identification number) in yhjbqk (user basic information)

2. Table field unique constraint

ORACLE uses indexes to ensure data integrity

Such as LC_ lc_bh+hj_sx (process number + phase sequence) in HJ (process phase)

3. Fields for direct condition query

Fields used for conditional constraints in SQL

Such as ZL_ qc_bh (area book number) in yhjbqk (basic information of users)

select * from zl_yhjbqk where qc_bh='7001'

4. Fields associated with other tables in query

Fields often have foreign key relationships

Such as ZL_ Jldb#u BH (number of metering point meter) in ydcf (power consumption component)

select * from zl_ydcf a,zl_yhdb b where 
                                   a.jldb_bh=b.jldb_bh and b.jldb_bh='540100214511'

5. Fields sorted in query

If the sorted fields are accessed through the index, the sorting speed will be greatly improved

select * from zl_yhjbqk order by qc_bh(establish qc_bh (index)

select * from zl_yhjbqk where 
                       qc_bh='7001' order by cb_sx(establish qc_bh+cb_sx Indexes,
                                              Note: it is only an index, including qc_bh and cb_sx Fields)

6. Fields of statistics or group statistics in query

select max(hbs_bh) from zl_yhjbqk

select qc_bh,count(*) from zl_yhjbqk group by qc_bh

IV Which fields are indexed

1. The primary key and foreign key of the table must have an index;

2. Tables with more than 300 data should have indexes;

3. Add an index to the columns in the where clause, group by clause, order by clause and on clause

4. The index should be built on fields with high selectivity;

5. The index should be built on small fields. For large text fields or even super long fields, do not build an index;

6. The establishment of composite index needs careful analysis; Try to use single field index instead of:

V How is the index queried

1. Cluster column index

Write an example to illustrate:
Execute table creation statement:

CREATE TABLE `student` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT 'Primary key id',
  `student_no` VARCHAR(64) COMMENT 'Student number',
  `name` VARCHAR(64) COMMENT 'Student name',
  `age` INT COMMENT 'Student age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='Student information sheet';

Insert 5 pieces of data:

insert into student(student_no,name,age) values(101,"Alice",18);
insert into student(student_no,name,age) values(102,"Bob",19);
insert into student(student_no,name,age) values(104,"Brandt",15);
insert into student(student_no,name,age) values(105,"David",19);
insert into student(student_no,name,age) values(109,"David",18);

In the process of inserting, MySQL will use the primary key you specify, here is the incremental primary key, and maintain a B + tree. The primary key increases from 1 and five items are inserted, so it is 1 to 5:

From 1 to 5, insert one by one, and you will see how the B + tree maintains several characteristics during the insertion process:

Ordered: nodes on the left are smaller than those on the right
Self balance: the number of left and right sides tends to be equal
Node splitting: how a node is split into two when the number of elements exceeds the capacity of the node. This is also the principle of MySQL page splitting

Execute SQL statement:

select * from student where id = 5;

# 1. From top to bottom, find 3 or 5 larger than it and find the right node
# 2. Then find 4 and find that 5 is still larger than it. Continue to find the right node
# 3. The leaf node is reached this time. The leaf node is an increasing array. Use the dichotomy to find the data with id=5

The above uses the cluster column index. The cluster column index will only speed up the primary key index, but what if you want to query by name? You have to use a secondary index.

2. Secondary index

You can see from the tree above that the data is not organized by name, so you can only scan the whole table.

What if you don't want to scan the whole table? Then add an index to the name field to make the data organized regularly according to the name:

create index idx_name on student(name);

At this time, MySQL will build a new B + tree:

It is found that the leaf node of this tree has only two fields: name and primary key ID, and there is no complete data of the row. At this time, you execute:

select * from student where name = "David";

MySQL queries the B + tree just created. It quickly finds two records with the name "David" and gets their primary keys, which are 4 and 5 respectively. But what do you want is select *?

Don't forget that MySQL built a B + tree at the beginning. Put the two trees together, take the two primary key ID S found in the tree, and find them in the cluster index. Isn't it solved?

This index without complete information of row data is called secondary index, also known as auxiliary index.

3. Composite index

What if you want to query by name and age at the same time?

select * from student where name = "David" and age = 18;

That's the same reason. Although the data is organized regularly by name, it is not organized regularly by age, so we need to index name and age at the same time:

create index idx_name_age on student(name,age);

At this time, MySQL will build a B + tree. In the nodes of the B + tree, there are not only name, but also age:

Notice the two nodes outlined by the red dotted line. This is the only difference between this tree and the tree above that only indexes name. The two elements have changed bits, because when sorting, name is used to compare the size. If the names are the same, age is used to compare the size