catalogue

preface

1, Index and classification

1. Concept of index

2. Function of index

3. Side effects of index

4. Classification of index

4.1 General Index

4.2 unique index

4.3 primary key index

4.4 full text index

4.5 combined index (single column index and multi column index)

5. Principles and basis of index creation

2, Create and view indexes

1. Preparation

  2. Method of creating index

2.1 creating a general index

  2.3 creating a primary key index

  2.4 create full-text index

  2.5 create composite index (single column index and multi column index)

  3. Index viewing method

preface

• In the process of enterprise informatization, the data volume of tables in the database is increasing, and the performance will decline sharply. Creating indexes is very important to maintain good performance
• Index is the most effective means to optimize query performance, which can easily improve query performance by several orders of magnitude

1, Index and classification

1. Concept of index

• An index is a special file that contains reference pointers to all records in a data table
• Generally speaking, the index is like the directory of a book, which can speed up the query speed of the database
• For example, you need to traverse 200 pieces of data. If there is no index, the database will traverse all 200 pieces of data and select the one that meets the conditions
• With the corresponding index, the database will directly find the qualified options in the index
• Database index is a directory of values in some fields to improve the search efficiency of tables

2. Function of index

The purpose of indexing is to speed up the search or sorting of records in the table:

• After setting the appropriate index, the database can greatly speed up the query speed by using various fast positioning technologies, which is the main reason for creating the index
• When the table is large or the query involves multiple tables, using indexes can improve the query speed thousands of times
• It can reduce the IO cost of the database, and the index can also reduce the sorting cost of the database
• By creating a unique index, you can ensure the uniqueness of each row of data in the data table
• It can speed up the connection between tables
• When grouping and sorting words are used for data query, the time of grouping and sorting can be greatly reduced

3. Side effects of index

There are also costs to set indexes for tables:

• Indexing requires additional disk space
• For MyISAM engine, the index file and data file are separated, and the index file is used to save the address of data records; The table data file of InnoDB engine itself is an index file
• It takes more time to insert and modify data (because the index changes with it)

4. Classification of index

4.1 General Index

• This is the most basic index type
• And it has no restrictions such as uniqueness

4.2 unique index

• Similar to ordinary indexes, the difference is that all values of unique index columns can only appear once, that is, they must be unique
• When there are duplicate key values in the existing database, most databases do not allow the newly created unique index to be saved with the table
• The database may also prevent the addition of new data that will create duplicate key values in the table
• For example, if a unique index is created on the employee's last name (name) in the employee information statistics table, no two employees can have the same last name

4.3 primary key index

• A PRIMARY KEY is a unique index, but it must be specified as "PRIMARY KEY"
• Defining a primary key for a table in the database automatically creates a primary key index, which is a specific type of unique index
• The index requires that each value in the primary key be unique

4.4 full text index

• The index type is FULLTEXT
• It is suitable for fuzzy query and can be used to retrieve text information in an article
• Before MySQL version 5.6, FULLTEXT index can only be used for MyISAM engine. After version 5.6, innodb engine also supports FULLTEXT index
• Full TEXT indexes can be created on columns of type CHAR, VARCHAR, or TEXT
• Only one full-text index is allowed per table

4.5 combined index (single column index and multi column index)

• An index can be an index created on a single column or an index created on multiple columns
• A multi column index can distinguish rows in which one column may have the same value
• Indexes are also helpful if you often search two columns, multiple columns, and sort by two or more columns at the same time
• For example, if you often set query criteria for the last name and first name columns in the same query, it makes sense to create a multi column index on the two columns

5. Principles and basis of index creation

• Index can improve the speed of database query, but it is not suitable for index creation in any case
• Because the index itself will consume system resources, when there is an index, the database will first query the index and then locate the specific data row. If the index is not used properly, it will increase the burden of the database

The principles and basis for creating indexes are listed below:

• The primary key and foreign key of a table must have an index; The primary key is unique, and the index value is also unique. You can quickly locate the data row during query; Foreign keys are usually associated with the primary key of another table, so they can also be quickly located in multi table queries
• Tables with more than 300 rows of records should have indexes; If there is no index, you need to traverse the table, which will seriously affect the performance of the database
• For tables that often connect with other tables, an index should be established on the connection field
• Fields with poor uniqueness are not suitable for indexing, which will not improve the query speed, but slow down
• Fields that are updated too frequently are not suitable for index creation; When adding, deleting, modifying, and querying in a table, the index will also respond to operations; Too frequent field updates will lead to excessive occupation of system resources
• The fields that often appear in the where clause, especially the fields of large tables, should be indexed
• Indexes should be built on highly selective fields; If few fields have the same value, that is, there are many unique values, the selectivity is high
• The index should be built on small fields. For large text fields or even super long fields, do not build an index

2, Create and view indexes

In MySQL, you can use commands to create multiple types of indexes or view indexes

1. Preparation

create database club;
#Create Library

use club;
create table member (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
#Create table

desc member;
#View table structure

insert into member values (1,'ghr1','10010','111111','wuhan','this is vip');
insert into member values (2,'ghr2','10020','222222','changsha','this is vip');
insert into member values (3,'ghr3','10030','333333','qingdao','this is normal');
insert into member values (4,'ghr4','10040','444444','nanjing','this is normal');
insert into member values (5,'ghr5','10050','555555','beijing','this is vip');
#Add data content to the table

select * from member;
#View data records in the table

  2. Method of creating index

2.1 creating a general index

1. Create index directly

CREATE INDEX Index name ON Table name (Listing[(length)]);

#(column name (length)): length is optional, the same below
#If the value of length is ignored, the value of the entire column is used as the index
#If you specify to use the length characters before the column to create the index, it is helpful to reduce the size of the index file
#It is recommended that index names end with "_index"


Example:
create index phone_index on member (phone);
#Create index directly

select phone from member;

show create table member;
#Show the structure of the table and the specific statement to create the table

 

2. Modify table creation method

ALTER TABLE Table name ADD UNIQUE Index name (Listing);


Example:
alter table member add unique cardid_index (cardid);

select cardid from member;
show create table member;

3. Specify the index when creating the table

CREATE TABLE Table name (Field 1 data type,Field 2 data type[,...],UNIQUE Index name (Listing));


example:
create table test0 (id int,name varchar(20),unique id_index (id));
show creat table test0;

 

  2.3 creating a primary key index

1. Specify the index when creating the table

CREATE TABLE Table name ([...],PRIMARY KEY (Listing));


Example:
create table test1 (id int primary key,name varchar(20));
#There are two ways to create a field. One is to specify it directly in the field, and the other is to specify it after the field ends
create table test2 (id int,name varchar(20),primary key (id));

show create table test1;
show create table test2;

 

2. Modify table creation method

ALTER TABLE Table name ADD PRIMARY KEY (Listing); 


Example:
alter table member add primary key (id);

show create table member;

  2.4 create full-text index

1. Create index directly

CREATE FULLTEXT INDEX Index name ON Table name (Listing);


Example:
create fulltext index remark_index on member (remark);
#The full-text index fulltext can only be created on char, varchar and text types, and only one is allowed for each table
show create table member;

2. Modify table creation method

ALTER TABLE Table name ADD FULLTEXT Index name (Listing);

3. Specify the index when creating the table

CREATE TABLE Table name (Field 1 data type[,...],FULLTEXT Index name (Listing));
#The data type can be CHAR, VARCHAR or TEXT

4. Use full-text index query

SELECT * FROM Table name WHERE MATCH(Listing) AGAINST('Query content');

Example:
select * from member where match(remark) against('this is vip');

  2.5 create composite index (single column index and multi column index)

CREATE TABLE Table name (Column name 1 data type,Column name 2 data type,Column name 3 data type,INDEX Index name (Column name 1,Column name 2,Column name 3));

select * from Table name where Column name 1='...' AND Column name 2='...' AND Column name 3='...';


example:
create table test001 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));

show create table test001;

insert into test001 values(1,'ghr1','123123');

select * from test001 where name='ghr1' and id=1;

 

 

  3. Index viewing method

show index from Table name;
show index from Table name\G;
#Add "\ G" after the command to display the index information vertically
 or
show keys from Table name;
show keys from Table name\G;
#The display results of the two methods are exactly the same


Example:
show index from member;

  Index details

Display parameters	describe
Table	Table name
Non_unique	The uniqueness of the index value. 0 means uniqueness and 1 means non uniqueness
Key_name	The name of the index
Seq_in_index	Column ordinal in index, starting from 1
Column_name	Column name
Collation	How columns are stored in the index. In MySQL, there is A value of A (ascending order) or NULL (no classification)
Cardinality	An estimate of the number of unique values in the index
Sub_part	If the column is only partially indexed, the number of characters indexed; NULL if the entire column is indexed
Packed	Indicates how keywords are compressed; NULL if not compressed
Null	If the column contains NULL, it contains YES. If not, the column contains NO
Index_type	Used indexing methods (BTREE, FULLTEXT, HASH, RTREE)
Comment	remarks