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

This article mainly introduces the method of creating index to Myql, which is very detailed and has certain reference value. Friends who are interested must finish reading it.

In order to improve the performance of Mysql, we can create indexes to improve the search speed of Mysql and relieve the pressure on Mysql database. Let's talk about indexes and some advanced uses of Mysql.

All MySQL column types can be indexed. Define the maximum number of indexes and the maximum index length for each table according to the storage engine.

All storage engines support at least 16 indexes per table, with a total index length of at least 256 bytes. Most storage engines have higher limits.

Currently, there are only two storage types of indexes (btree and hash), which are related to the storage engine mode:

MyISAM btree

InnoDB btree

MEMORY/Heap hash,btree

By default, the MEMORY/Heap storage engine uses hash indexes

The difference between btree Index and hash Index of MySQL

Because of the particularity of hash index structure, its retrieval efficiency is very high, and index retrieval can be located at once, unlike btree (B-Tree) index, which needs to access page nodes from root node to branch node for so many times, so the query efficiency of hash index is much higher than that of btree (B-Tree) index.

Although hash index is efficient, hash index itself brings a lot of limitations and disadvantages because of its particularity, mainly as follows.

(1) the hash index can only satisfy the =, IN,IS NULL or IS NOT NULL query, not the range query.

Because the hash index compares the hash value after the hash operation, it can only be used for equivalent filtering, not for range-based filtering, because the size relationship of the hash value processed by the corresponding hash algorithm cannot be guaranteed to be exactly the same as before the hash operation.

(2) hash indexes cannot be used to avoid sorting data.

Because the hash index stores the hash value after hash calculation, and the size relationship of the hash value is not necessarily the same as the key value before the hash operation, the database cannot use the index data to avoid any sort operation.

(3) hash indexes cannot be queried by partial index keys.

For a composite index, the hash index calculates the hash value by combining the index keys and then calculating the hash value together, rather than calculating the hash value separately, so the hash index cannot be utilized when querying through one or more of the first index keys of the composite index.

(4) hash indexes can not avoid table scans at any time.

As we already know, the hash index stores the hash value of the hash operation result and the corresponding row pointer information in a hash table after the index key is calculated through hash. Because different index keys have the same hash value, even if you take the number of records that meet a certain hash key value, you can not directly complete the query from the hash index, or you have to access the actual data in the table to compare and get the corresponding results.

(5) when a large number of hash values are equal, the performance of hash index is not necessarily higher than that of B-Tree index.

For index keys with low selectivity, if you create a hash index, there will be a large amount of record pointer information associated with the same hash value. In this way, it will be very troublesome to locate a certain record, which will waste many visits to table data, resulting in poor overall performance.

B-Tree index is the most frequently used index type in MySQL database, and all storage engines except Archive storage engine support B-Tree index. Not only in MySQL, but also in many other database management systems, B-Tree index is also the most important index type. This is mainly because the storage structure of B-Tree index has excellent performance in database data retrieval.

Generally speaking, most of the physical files of the B-Tree index in MySQL are stored in the structure of Balance Tree, that is, all the actual data are stored in the Leaf Node of Tree, and the length of the shortest path to any Leaf Node is exactly the same, so we all call it the B-Tree index. It is possible that various databases (or MySQL's various storage engines) will slightly modify the storage structure when storing their own B-Tree indexes.

For example, the actual storage structure used by the B-Tree index of the Innodb storage engine is actually B+Tree, that is, a small modification has been made on the basis of the B-Tree data structure. In addition to storing the relevant information of the index key on each LeafNode, it also stores the pointer information to the next LeafNode adjacent to the LeafNode, which is mainly to speed up the efficiency consideration of retrieving multiple adjacent LeafNode.

In the Innodb storage engine, there are two different forms of indexes, one is the primary key index (Primary Key) in the form of Cluster, and the other is the ordinary B-Tree index which is basically the same as other storage engines (such as MyISAM storage engine). This kind of index is called Secondary Index in the Innodb storage engine.

In Innodb, it is very efficient to access data through the primary key, but if the data is accessed through Secondary Index, Innodb first retrieves the Leaf Node through the relevant information of Secondary Index and the corresponding index key, and then needs to obtain the corresponding data row through the primary key value stored in Leaf Node and then through the primary key index.

There is little difference between the primary key index of the MyISAM storage engine and the non-primary key index, except that the index key of the primary key index is a unique and non-empty key. Moreover, the storage structure of the index of the MyISAM storage engine is basically the same as that of the Secondary Index of Innodb, except that the MyISAM storage engine stores the index key information on the Leaf Nodes.

Then store the information that can be located directly to the corresponding data row in the MyISAM data file (such as Row Number), but does not store the key value information of the primary key.

Indexes are divided into single-column indexes and combined indexes. A single-column index, that is, an index contains only a single column, and a table can have multiple single-column indexes, but this is not a combined index. Composite index, that is, a single index contains multiple columns.

MySQL index types include:

(1) the general index, which is the most basic index, does not have any restrictions. It can be created in the following ways:

-create an index

CREATE INDEX indexName ON mytable (username (10));-- single column index

-- CREATE INDEX indexName ON mytable (username (10), city (10));-- Composite index

-- indexName is the index name, mytable table name, username and city are column names, and 10 is the prefix length, that is, the length of information stored by the index from the leftmost character in the column, in bytes

Modify the table structure to create an index

ALTER TABLE mytable ADD INDEX indexName (username (10))

-ALTER TABLE mytable ADD INDEX indexName (username (10), city (10))

-- the indexName index name cannot be written here, and the system automatically assigns the name username, username_2, username_3,....

-- specify directly when you create a table

CREATE TABLE mytable (

Id INT

Username VARCHAR (16)

City VARCHAR (16)

Age INT

INDEX indexName (username (10))-INDEX indexName (username (10), city (10))

);

The indexName index name can also be omitted here

(2) unique index, which is similar to the previous ordinary index, except that the value of the index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique. It can be created in the following ways (only the keyword INDEX is preceded by UNIQUE when creating a normal index):

-create an index

CREATE UNIQUE INDEX indexName ON mytable (username (10))

Modify the table structure to create an index

ALTER TABLE mytable ADD UNIQUE INDEX indexName (username (10));-- can also be simplified to ALTER TABLE mytable ADD UNIQUE indexName (username (10))

-- specify directly when you create a table

CREATE TABLE mytable (

Id INT

Username VARCHAR (16)

City VARCHAR (16)

Age INT

UNIQUE INDEX indexName (username (10))-can also be abbreviated to UNIQUE indexName (username (10))

);

(3) Primary key index, which is a special unique index and does not allow null values. The primary key created at the same time as the table is the primary key index.

Primary key indexes do not need to be named, and a table can have only one primary key. A primary key index can also be a unique index or a full-text index, but a unique index or a full-text index cannot co-exist in the same index:

Modify the table structure to create an index ALTER TABLE mytable ADD PRIMARY KEY (id)

Specify CREATE TABLE mytable directly when creating the table (

Id INT

Username VARCHAR (16)

City VARCHAR (16)

Age INT,PRIMARY KEY (id)

);

(4) full-text indexing, full-text indexing is not supported by InnoDB storage engine:

-- create index CREATE FULLTEXT INDEX indexName ON mytable (username (10))

Modify the table structure to create the index ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username (10))

-- it can also be simplified as ALTER TABLE mytable ADD FULLTEXT indexName (username (10))

Specify CREATE TABLE mytable directly when creating the table (

Id INT

Username VARCHAR (16)

City VARCHAR (16)

Age INT

FULLTEXT INDEX indexName (username (10))

-- it can also be simplified to FULLTEXT indexName (username (10)) ENGINE=MYISAM

Create a full-text index when creating a table. To set the storage engine of the table to MYISAM, the default InnoDB storage engine for the new version of mysql does not support full-text indexing.

-- Delete index DROP INDEX indexName ON mytable

Although indexing greatly improves query speed, it also slows down the speed of updating tables, such as INSERT, UPDATE, and DELETE on tables. Because when updating the table, MySQL not only saves the data, but also saves the index file.

Create an index file that takes up disk space. In general, this problem is not too serious, but if you create multiple composite indexes on a large table, the index file will expand very quickly.

The above is all the contents of this article entitled "how to Index Myql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.