Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the MySQL index to quickly retrieve the database. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

I. the concept of index

1. An 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 (a linked list similar to the C language points to the memory address of the data record through a pointer).

2. After using the index, you can locate the data of a row without scanning the whole table, but first find the physical address of the corresponding data through the index table and then access the corresponding data, so it can speed up the query speed of the database.

3. An index is like a catalogue of a book. You can quickly find what you need according to the page number in the catalogue.

4. An index is a method of sorting the values of one or more columns in a table.

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

Second, the function of the index

1. After setting up the appropriate index, the database uses a variety of fast positioning technologies, which can greatly accelerate the query speed, which is the main reason for creating the index.

2. When the table is very large or the query involves multiple tables, the use of indexes can improve the query speed by thousands and tens of thousands of times.

3. It can reduce the cost of the database, and the index can also reduce the sorting cost of the database.

4. By creating a uniqueness index, we can ensure the uniqueness of each row of data in the data table.

5. You can speed up the connection between tables.

6. When using grouping and sorting, the time of grouping and sorting can be greatly reduced.

III. Side effects of the index

1. The index requires extra disk space.

For the MyISAM engine, the index file and the data file are separate, and the index file is used to hold the address of the data record.

The table data file of the InnoDB engine is itself an index file.

2. It takes more time to insert and modify data, because the index changes accordingly.

IV. The principle basis for creating an index

Indexes can increase the speed of database queries, but they are not suitable for creating indexes in all cases. Because the index itself will consume system resources, in the case of an index, the database will first query the index, and then locate to the specific data row, if the index is not used properly, it will increase the burden of the database.

1. The primary key and foreign key of the table must have an index. Because the primary key is unique, the foreign key is associated with the primary key of the primary table, which can be quickly located when querying.

2. Tables with more than 300 rows of records should have an index. If there is no index, each query needs to traverse the table, which will seriously affect the performance of the database.

3. Tables that are often connected to other tables should be indexed on the join field.

4. Fields with poor uniqueness are not suitable for indexing.

5. Fields that are updated too frequently are not suitable for index creation.

6. Fields that often appear in the where clause, especially those in large tables, should be indexed.

7. The index should be built on fields with high selectivity.

8. The index should be built in small fields. Do not index large text fields or even ultra-long fields.

V. Classification and creation of indexes

Use this table as a demonstration create table member (id int (10), name varchar (10), Cardid varchar (10), phone int (11), address varchar (50), remark text) before doing it.

(1). General index

General index: the most basic type of index, with no restrictions such as uniqueness.

1. Directly create the index CREATE INDEX index name ON table name (column name [(length)]); # (column name (length)): length is optional, same as below. If you omit the value of length, the value of the entire column is used as the index. If you specify that the length characters before the column are used to create the index, this helps to reduce the size of the index file. # the index name is recommended to end with "_ index".

2. Modify the table method to create the ALTER TABLE table name ADD INDEX index name (column name); for example: alter table member ADD INDEX phone_index (phone); select phone from member

3. Specify the index CREATE TABLE table name when creating the table (field 1 data type, field 2 data type [,...], INDEX index name (column name); example: create table test (id int (4) not null,name varchar (10) not null,cardid varchar (18) not null,index id_index (id)); show create table test

(2) the unique index is similar to the 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 created with a composite index, the combination of column values must be unique. Adding a unique key automatically creates a unique index. 1. Directly create a unique index CREATE UNIQUE INDEX index name ON table name (column name)

2. How to modify the table to create ALTER TABLE table name ADD UNIQUE index name (column name); for example: alter table member add unique name_index (cardid)

In order to facilitate the experiment, I first deleted the previous index

3. Specify the CREATE TABLE table name when creating the table (field 1 data type, field 2 data type [,...], UNIQUE index name (column name))

(3) Primary key index

Is a special unique index and must be specified as "PRIMARY KEY". A table can have only one primary key, and null values are not allowed. Adding a primary key automatically creates a primary key index.

ALTER TABLE table name ADD PRIMARY KEY (column name)

(IV), combined index (single-column index and multi-column index)

Composite index: it can be an index created on a single column or on multiple columns. The leftmost principle needs to be met, because the where condition of the select statement is executed from left to right, so when querying with the select statement, the order of the fields used by the where condition must be the same as the sort in the combined index, otherwise the index will not take effect. 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 The column name of AND is 2 percent. The column name of AND is 3 percent.'

(5) full-text index

Suitable for use in fuzzy queries, can be used to retrieve text information in an article. Prior to the MySQL5.6 version, FULLTEXT indexes are only available for the MyISAM engine, but after version 5.6, the innodb engine also supports FULLTEXT indexes. A full-text index can be created on a column of type CHAR, VARCHAR, or TEXT. Only one full-text index is allowed per table. CREATE FULLTEXT INDEX index name ON table name (column name); example: select * from member;create fulltext index name_index on member (name)

Create the way to modify the table

ALTER TABLE table name ADD FULLTEXT index name (column name)

Specify an index when you create a table

CREATE TABLE table name (field 1 data type [,...], FULLTEXT index name (column name))

# data type can be CHAR, VARCHAR or TEXT

Query using full-text index

SELECT * FROM table name WHERE MATCH (column name) AGAINST ('query content'); for example: select * from member where match (remark) against ('this is vip')

6. View the index

Show index from table name; show index from table name\ G; Vertical display table index information show keys from table name; show keys from table name\ G

The name of the Table table Non_unique is 0 if the index cannot include duplicate words, or 1 if it can. The name of the Key_name index. The column sequence number in the Seq_in_index index, starting with 1. Column_name column name. How Collation columns are stored in the index. In MySQL, there is an estimate of the number of unique values in the value'A' (ascending order) or NULL (unclassified) Cardinality index. Sub_part the number of characters indexed if the column is only partially indexed. NULL if the entire column is indexed. Packed indicates how keywords are compressed. NULLNull if not compressed. If the column contains NULL, it contains YES. If not, the column contains the Comment comments of the index methods (BTREE, FULLTEXT, HASH, RTREE) used by NOIndex_type

VII. Delete index

1. Directly delete index DROP INDEX index name ON table name; 2, modify table method to delete index ALTER TABLE table name DROP INDEX index name; 3, delete primary key index ALTER TABLE table name DROP PRIMARY KEY

VIII. Examples

Case: make a membership card system for a shopping mall. The system has a membership table with the following fields: member number INT member name VARCHAR (10) member ID number VARCHAR (18) member telephone number INT (11) member address VARCHAR (50) member remarks information TEXT create table member (id int (10), name varchar (10), cardid varchar (18), phone int (11), address varchar (50), remark text); alter table member add primary key (id); create index name_index on member (name) Create unique index cardid_index on member (cardid); alter table member add fulltext remark_index (remark); member number, as the primary key, use PRIMARY KEY member name, if you want to build an index, then it is an ordinary INDEX member ID number, if you want to build an index, then you can choose UNIQUE (unique, not allowed to repeat) member remarks information, if you need to build an index, you can choose FULLTEXT, full-text search. But FULLTEXT works best when it's used to search for long articles. Used in short text, if only one or two lines of words, ordinary INDEX can also be used. This is the end of this article on "how to quickly retrieve the database using MySQL index". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.