1. The concept of index
- The index is a sorted list, in which the value of the index and the physical address of the row containing the data containing this value are stored (similar to the linked list in c language pointing to the memory address of the data record through the pointer).
- After using the index, you do not need to scan the entire table to locate the data of a row, but first find the physical address corresponding to the row of data through the index table and then access the corresponding data, thus speeding up the query speed of the database.
- The index is like the table of contents of a book, you can quickly find what you need according to the page number in the table of contents.
- An index is a method of sorting the values of a column or several columns in a table.
- The purpose of indexing is to speed up the lookup or sorting of records in a table. (Speed up queries, sort field values)
Second, the role of the index
2.1 Advantages
- After setting up a suitable index, the database can greatly speed up the query speed by using various fast positioning technologies, which is the main reason for creating an index.
- When the table is large or the query involves multiple tables, using an index can speed up the query by thousands of times.
- It can reduce the I/O cost of the database, and the index can also reduce the sorting cost of the database.
- By creating a unique index, the uniqueness of each row of data in the data table can be guaranteed.
- Can speed up joins between tables.
- When using grouping and sorting, the time for grouping and sorting can be greatly reduced.
- Indexing can significantly improve performance when searching and restoring data in a database.
2.2 Disadvantages
-
1) Indexes require additional disk space.
- For the MyISAM engine, the index file and data file are separated, and the index file is used to save the address of the data record.
- The table data files of the InnoDB engine are themselves index files. (index file and data file are the same)
-
2) It will take more time and consume more performance when inserting and modifying data, because the index will also change accordingly.
3. The principle basis for creating indexes
Although indexes can improve the speed of database queries, they are not suitable for creating indexes in any situation. Because the index itself consumes system resources, if there is an index, the database will perform index query first, and then locate specific data rows. If the index is not used properly, it will increase the burden on the database.
- The primary key and foreign key of the table must have indexes. Because the primary key is unique, the foreign key is associated with the primary key of the main table, which can be quickly located when querying.
- Tables with more than 300 (or 500) rows should have indexes. If there is no index, each query needs to traverse the table, which will seriously affect the performance of the database. (will affect the performance of IO and CPU)
- Tables that are frequently joined with other tables should have indexes on the join fields.
- Fields that are too unique are not suitable for indexing. (Because the field value will be repeated)
- Fields that are updated too frequently are not suitable for indexing.
- Fields that frequently appear in where clauses, especially those of large tables, should be indexed.
- Build indexes on fields that are frequently grouped by and ordered by.
- Indexes should be built on highly selective fields. (i.e. fields with low repeatability)
- Indexes should be built on small fields, and do not build indexes on large text fields or even super-long fields. (Building an index with a large field will increase disk space, and it will affect performance when searching)
Note:
When a table has many writes and few reads, there is no need to build an index.
Fields with poor uniqueness, fields that are updated too frequently, and large fields are not suitable for indexing.
Fourth, the classification and creation of indexes
First create a data table member
create table member(id int(10) ,name varchar(10) ,cardid int(18) ,phone int(11) ,address varchar(50),remark text); copy code
4.1 Common Index
Ordinary index is the most basic index type, without restrictions such as uniqueness.
Method 1: Create an index directly
CREATE INDEX index name ON Table Name (column name(length)); copy code
- (column name (length)): length is optional, the same below. If the value of length is omitted, the value of the entire column is used as the index. If specified, uses the first length characters of the column to create the index, which helps reduce the size of the index file. The shorter the length, the better without loss of accuracy.
- The index name is suggested to end with "index".
Example:
create index name_index on member(name); #Create a normal index with the name field create index cardid_index on member(cardid(4)); #Specify the first 4 characters of the cardid field value as the value of the ordinary index copy code
An index is equivalent to a key:
Method 2: Modify the table to create an index
ALTER TABLE Table Name ADD INDEX index name(column name); Example: ALTER TABLE member ADD INDEX phone_index(phone); #Create a normal index with the phone field copy code
Method 3: Specify the index when creating the table
Generally, adding an index when creating a table will slow down inserting data.
CREATE TABLE Table Name(Field 1 data type,Field 2 data type[,...],INDEX index name (column name)); Example: create table member2(id int(10) ,name varchar(10) ,cardid int(18), phone int(11) ,address varchar(50),remark text,INDEX name_index(name)); copy code
Delete the index:
drop index name_index on member; #Delete the index directly drop index cardid_index on member; #Delete the index directly alter table member DROP index phone_index; #Delete the index by modifying the table copy code
4.2 Unique index (creating a unique key means creating a unique index)
Unique index: Similar to ordinary index, but the difference is that each value of the unique index column is unique. Unique indexes allow null values (note that they are different from primary keys). If it is created with a composite index, the combination of column values must be unique. Adding a unique key will automatically create a unique index.
Either creating a unique key or creating a unique index can be achieved.
Method 1: Create a unique index directly
CREATE UNIQUE INDEX index name ON Table Name(field name); Example: create unique index card_index on member(cardid); copy code
Method 2: Create by modifying the table
ALTER TABLE Table Name ADD UNIQUE index name(field name); Example: alter table member add unique phone_index(phone); copy code
Method 3: Specify the index when creating the table
CREATE TABLE Table Name(Field 1 data type,Field 2 data type[...],UNIQUE index name(field name)); Example: create table member3(id int(10) ,name varchar(10) ,cardid int(18) ,phone int(11) ,address varchar(50),remark text, unique phone_index(phone)); copy code
Method 4: Add a unique key
Adding a unique key will automatically create a unique index.
CREATE TABLE Table Name(Field 1 data type,Field 2 data type[...],unique key(field name)); CREATE TABLE Table Name(Field 1 data type unique key,Field 2 data type[...]); ALTER TABLE Table Name ADD unique key(field); copy code
Use the show create table member3; command to view the table structure.
It can be seen that adding a unique key will automatically create a unique index, and adding a unique index will create a unique key.
4.3 Primary key index (the same way as creating a primary key)
A primary key index is a special unique index that must be specified as "PRIMARY KEY". A table can only have one primary key, and null values are not allowed. Adding a primary key will automatically create a primary key index.
Method 1: Add a primary key when creating a table
create table Table Name(field 1 XXX, field 2 XXX, ...primary key(field)); create table Table Name(field 1 XXX primary key, ...); #Make the primary key an attribute of field 1 Example: create table room(id int(10),name varchar(10),primary key(id)); create table room2(id int(10) primary key,name varchar(10)); copy code
Method 2: Add a primary key to an existing table
ALTER TABLE Table Name add primary key(field name); Example: ALTER TABLE member add primary key(id); #Add the id field as the primary key copy code
4.4 Composite index (single-column index and multi-column index)
Composite index (single-column index and multi-column index): It can be an index created on a single column or an index created on multiple columns.
The leftmost principle needs to be met, because the where condition of the select statement is executed from left to right in sequence, so when using the select statement to query, the order of the fields used in the where condition must be consistent with the sorting in the composite index, otherwise the index will not take effect.
Method 1: Create an index directly
CREATE INDEX index name on Table Name(field 1,field 2, field 3); Example: create index name_cardid_phone_index on member4(name,cardid,phone); #Create composite index with 3 fields copy code
Method 2: Create an index by modifying the table
alter table Table Name add index index name(field 1,field 2, ..., field n); Example: alter table member4 add index phone_name_cardid_index(phone,name,cardid); copy code
Method 3: Specify the index when creating the table
CREATE TABLE Table Name(Column name 1 data type,Column name 2 data type,Column name 3 data type, INDEX index name(field 1,field 2,field 3)); Example: create table info2(id int(10),name varchar(10),phone int(11),index name_phone_index(name,phone)); copy code
Query using composite index:
Pay attention to the leftmost principle of where when using select query. The order of the query fields must be consistent with the composite index to take effect.
select * from Table Name where field 1=XXX and field 2=XXX and ...; copy code
Example:
select * from member4 where name=lisi and cardid=1103 and phone=1388888888; ##This query statement will use the index name_cardid_phone_index. select * from member4 where phone=1888888888 and name=Zhang San and cardid=1101; ##This query statement will use the index phone_name_cardid_index. select * from member4 where cardid=1102 and name=Wang Wu and phone=1588888888; ##This query statement cannot use the index because the order of the query fields is inconsistent with the composite index. copy code
4.5 Full-text indexing
Full-text index (FULLTEXT): It is suitable for fuzzy query and can be used to retrieve text information in an article.
Before MySQL 5.6, the FULLTEXT index was only available for the MyISAM engine. After the 5.6 version, the innodb engine also supports the FULLTEXT index.
Full-text indexes can be created on columns of type CHAR, VARCHAR, or TEXT.
Typically only one full-text index is created per table.
Only complete words/strings can be matched when querying.
Method 1: Create an index directly
create fulltext index index name on Table Name (field); Example: create fulltext index remark_index on member(remark); copy code
Method 2: Create an index by modifying the table
alter table Table Name add fulltext index name (field); Example: alter table member add fulltext address_index(address); copy code
Method 3: Specify the index when creating the table
create table Table Name (field.... , fulltext index name (field)); Example: create table room4(id int(10),name varchar(10),fulltext name_index(name)); copy code
Full-text index creation restrictions:
Full-text indexes can only be created on fields of char, varchar, and text types:
Query using a full-text index:
When searching for a full-text index, only complete words or strings can be matched.
select * from Table Name where match(field name) against(word/string);
Example:
#Query the data records whose remark field contains the string "vip".
select * from member where match(remark) against('vip');
#Query the data records whose remark field contains the string "vvvip".
select * from member where match(remark) against('vvvip');
#This statement cannot query matching records, because the full-text index can only match complete words/strings.
select * from member where match(remark) against('ip');
copy code
5. Check which indexes are in the table
show index from Table Name; #Can view the fields and details of the index, it is recommended to view it in a vertical format show index from Table Name\G #It is recommended to use \G to view in portrait format show keys from Table Name; show keys from Table Name\G show create table Table Name; #Only indexed fields and names can be viewed copy code
In the query results, the meanings of each field are as follows:
| field | meaning |
|---|---|
| Table | table name |
| Non_unique | 0 if the index cannot include duplicate words, 1 if it can |
| Key_name | index name |
| seq_in_index | The column number in the index, starting from 1 |
| column_name | column name |
| collation | How the column is stored in the index. In MySQL, has the value "A" (ascending) or NULL (no sorting) |
| 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 | Displays YES if the column contains NULL values. If not, display NO or be empty |
| lndex_type | used index method (BTREE, FULLTEXT, HASH, RTREE) |
| comment | Remark |
6. Delete the index
Except for deleting the primary key index, the method of deleting other indexes is the same.
6.1 Delete the primary key index (that is, delete the primary key)
alter table Table Name drop primary key; copy code
6.2 Delete other indexes
drop index index name on Table Name; #Delete the index directly alter table Table Name drop index index name; #Alter the table by dropping the index copy code
Example:
drop index address_index on member; #Delete the index directly alter table Table Name drop index index name; #Delete the index by modifying the table copy code
Summarize
1. The main functions and side effects of the index
Function: Speed up the query and sort the fields.
side effect:
-
Indexes require additional disk space.
- For the MyISAM engine, the index file and data file are separated, and the index file is used to save the address of the data record.
- The table data files of the InnoDB engine are themselves index files. (index file and data file are the same)
-
It will take more time and consume more performance when inserting and modifying data, because the index will also change accordingly.
2. How does the index improve the query speed?
- An index contains reference pointers to all records in the data table. An index is a sorted list in which the value of the index and the physical address of the row containing the data containing the value are stored.
- After using the index, you do not need to scan the entire table to locate the data of a row, but first find the physical address corresponding to the row of data through the index table and then access the corresponding data, thus speeding up the query speed of the database.
3. How to deal with the slow query problem of the select statement?
- Use the explain command, that is, explain select ...; to analyze whether the select statement uses an index or whether the index is used correctly.
- If no index is used or the index is used incorrectly, you can use create index index name on table name (field); or alter table table name add index index name (field); to add an index to optimize query speed.
4. Create an index
1) Ordinary index:
create index index name on table name (field);
alter table table name add index index name (field (4));
create table table name (field.... , index index name (field));
2) Unique index:
create unique index index name on table name (field);
alter table table name add unique index name (field);
create table table name (field.... , unique index name (field));
3) Primary key index:
alter table table name add primary key (field);
create table table name (field.... , primary key (field));
create table table name (field primary key, ... );
4) Combined index (single-column, multi-column index):
create index XXX_index on table name (field 1, field 2, ... , field n);
alter table table name add index XXX_index (field 1, field 2, ... , field n);
create table table name (column name 1 data type, column name 2 data type, column name 3 data type, INDEX index name (field 1, field 2, field 3));
Pay attention to the leftmost principle of where when using:
select * from table name where field 1=XXX and field 2=XXX and ....
5) Full text index:
create fulltext index index name on table name (field);
alter table table name add fulltext index name (field);
create table table name (field.... , fulltext index name (field));
Only complete strings can be matched when querying:
select * from table name where match( field) against('query string');
5. What indexes are there in the query table
show index from table name; //You can view the fields and details of the index, it is recommended to use \G to view vertically
show keys from table name;
show create table table name; //Only view the fields and names of the index
6. Delete the index
How to delete the primary key index:
alter table table name drop primary key;
Drop other indexes:
drop index index name on table name;
alter table table name drop index index name;