Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to create and use SQL index". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian slowly and deeply to study and learn "how to create and use SQL index" together.

Role of index

The purpose of an index is to speed up queries. If queries that use an index are considered Ferrari sports cars, queries that do not use an index are equivalent to bicycles. At present, the data volume of tables in actual projects is getting larger and larger, often millions of tens of millions of levels, queries without indexes will become very slow, and the use of indexes has become a mandatory item for query optimization.

Concept of index

Index is actually a special kind of data, also stored in the database file, index data holds the location of the actual data in the data table. Similar to the table of contents at the front of a book, this table of contents stores the number of pages in each chapter of the book. By looking at the table of contents, we can quickly locate the number of pages in each chapter, thus speeding up the search speed.

Let's look at a query statement:

select * from book where id = 1000000;

Assuming there are millions of rows in the book table, an unindexed query will traverse the first million rows to find results. If we establish a primary key index on id, we will locate the results directly on the index, which is much faster.

Advantages and disadvantages of indexing

Advantages: Increase query speed

Disadvantages: It is also data itself, which will take up disk space; the creation and maintenance of indexes also requires time and cost; when deleting, updating and inserting operations, the speed will be reduced because of the maintenance of indexes.

Syntax using indexes

create an index

Create indexes while creating tables

create table name

(

field name type,

...

field name type,

index index name (field name)

);

Add index after creating table

alter table name add index index name (field name);

or

create index name on table name (field name);

delete the index

alter table name drop index name;

or

drop index name on table name;

View indexes in tables

show index from table name;

View indexes used by query statements

explain query statements;

Classification of index

The index is divided into:

A normal index, an index built on a normal field, without any restrictions

Primary key index. An index automatically created when creating a primary key. Cannot be empty or duplicate.

Unique index. The field data to be indexed must be unique. Null is allowed.

Full-text indexing, indexing on large Text fields

Composite index, index created by combining multiple columns, multiple columns cannot have null values

Code example:

--Create book table

create table tb_book

(

--Create Primary Key Index

id int primary key,

--Create a unique index

title varchar(100) unique,

author varchar(20),

content Text,

time datetime,

--General index

index ix_title (title),

--Full-text index

fulltext index ix_content(content),

--Combined index

index ix_title_author(title,author)

);

--Add primary key index after creating table

ALTER TABLE tb_book ADD PRIMARY KEY pk_id(id);

--Add unique index after creating table

ALTER TABLE tb_book ADD UNIQUE index ix_title(title);

--Add full-text index after creating table

ALTER TABLE tb_book ADD FULLTEXT index ix_content(content);

--Use full-text indexing for queries

SELECT * FROM tb_book MATCH(content) ANGAINST ('victory ');

--Add composite index after creating table

ALTER TABLE tb_book ADD INDEX ix_book(title,author);

Note: When creating a composite index, follow the "leftmost prefix" principle, put the most frequently queried and sorted fields on the left, and decrease in importance.

Index usage strategy

Under what circumstances should an index be created?

1) Build indexes on fields that often need querying and sorting

2) There is a lot of data.

Under what circumstances should I not create an index?

1) There is a lot of duplication in field data, such as gender

2) Very little data

3) Fields that often need to be added or deleted

Under what circumstances will the index fail?

1) Fuzzy query, use like '% Zhang %' will be invalid, but like 'Zhang %' will not

2) When using is null or is not null query

3) When using composite index, a field is null

4) When using or to query multiple conditions

5) When fields are used in functions, such as where year(time) = 2019

indexed structures

Different storage engines use indexes with different structures:

Cluster index, supported by InnoDB. The order of index is consistent with the physical order of data, similar to the order of Pinyin directory arrangement and Chinese character arrangement in Xinhua dictionary. There can only be one cluster index in a table.

Non-clustered index, MyISAM support, index order is inconsistent with the physical order of data, similar to Xinhua dictionary radical directory and Chinese character arrangement order is inconsistent, non-clustered index table can have multiple.

Index data structure is mainly: BTree and B+Tree

BTree data structure is as follows, is a balanced search multi-fork tree, each node consists 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 are two pointers, pointing to another index position, and all the keys are sorted, so in the search index, you can use binary search, speed is faster, time complexity is h*log(n), h is the height of the tree, BTree is a more efficient search structure.

The data structure of B+Tree is as follows, it is an upgraded version of BTree, the difference is that non-leaf nodes do not store specific data, only store the keys of the index, the data is saved to the leaf nodes, and there is no pointer in the leaf nodes, only the keys and data. The advantages of B+Tree are: search efficiency is higher, because there is no data stored in non-leaf nodes, more keys can be stored, and the more keys in each layer, the height of the tree will be reduced, so the query speed will be improved.

Thank you for reading, the above is "SQL index how to create and use" content, after the study of this article, I believe we have a deeper understanding of how to create and use SQL index this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.