preface

In this chapter, we will learn about indexes in MySQL. This article will introduce the function of indexes, index classification, index syntax, index usage strategy and index implementation principle.

The role of indexes

Index concept

When we go to a building to find a company, we can’t go to each floor, but look at the floor index and go directly to the floor where the company is.

Database index is a kind of special data, which is also stored in database files. Index data stores the location of the actual data in the data table. By searching the index, you can directly find the location of the actual data, so as to speed up the search.

Let’s look at a query statement:

select * from book where id = 1000000;
Copy the code

Given that there are millions of rows in the book table, a query without an index would traverse the previous million rows to find the result. If we index on the ID, we can locate the result directly on the index, which is much faster.

Advantages and disadvantages of indexes

Use index syntax

Create indexes

1. Create indexes as you build tables

Create table name (column name type,... Index Index name (field name));Copy the code

2. Add the index after the table is created

Alter table name add index name;Copy the code

or

Create Index Index name on Table name (field name);Copy the code

Remove the index

Alter table drop index drop index;Copy the code

or

Drop Index Index name on Table name;Copy the code

View the index in the table

Show index from table name;Copy the code

Classification of indexes

Create table tb_book (-- create primary key index id int primary key, -- create unique index title varchar(100) unique, author varchar(20), -- index ix_title (title), -- fulltext index ix_content(content), -- Index ix_title_author(title,author));Copy the code

ALTER TABLE tb_book ADD PRIMARY KEY pk_id(id); ALTER TABLE tb_book ADD UNIQUE index ix_title(title); ALTER TABLE tb_book ADD FULLTEXT index ix_content(content); SELECT * FROM tb_book MATCH(content) ANGAINST(' win '); ALTER TABLE tb_book ADD INDEX ix_book(title,author);Copy the code

Index usage policy

When to build an index

1. Build indexes on fields that are frequently queried and sorted
2. There’s a lot of data

When do not build indexes

1. There is a lot of duplication in the field data, such as gender
2. The data rarely
3. Fields that need to be added or deleted frequently

When does an index fail

Explain statement

When debugging queries, use Explain to view index usage

create index ix_city_name on city(name);
explain select * from city where name = 'London';
explain select * from city where name like 'Lon%';
explain select * from city where name like '%don%';
Copy the code

In the above code, the index is created on the name field of the city table. The index is normal when the query is performed with ‘name = ‘



Indexes also take effect when name like ‘Lon%’ is used for query. Type is range query



Symptom When the name like ‘%don%’ command is used, the index is invalid and type is ALL

Index structure

The data structure of the index

The data structures used for indexes are: Each node is composed of key and data. Key is the key of the index, data is the data corresponding to the key. On both sides of the node, there are two Pointers pointing to other index positions, and all keys are sorted. In this way, binary search can be used to search the index. The speed is relatively fast, the time complexity is H *log(n), h is the height of the Tree, and B-tree is a relatively efficient search structure.

B+Tree

The data structure of B+Tree is as follows, which is an upgraded version of BTree. Non-leaf nodes do not store specific data, but only index keys. Data is stored in leaf nodes, and only keys and data are stored in leaf nodes without Pointers.



The advantages of B+Tree are: higher search efficiency, because there is no data stored in the non-leaf node, more keys can be saved. The more keys in each layer, the height of the Tree will be reduced, so the query speed will be improved.

conclusion