Create an index for Myql

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

All MySQL column types can be indexed. Define the maximum number of indexes and 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.

There are currently only two storage types of indexes (btree and hash), which are specifically related to the storage engine mode:
MyISAM btree
InnoDB btree
MEMORY/Heap hash, btree

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

The difference between MySQL's btree index and hash index
The particularity of the hash index structure, its retrieval efficiency is very high, the index retrieval can be located at one time, unlike btree (B- Tree) index requires multiple IO accesses from the root node to the branch node, and finally to the page node, so the query efficiency of the hash index is much higher than that of the btree (B-Tree) index.

Although the hash index is highly efficient, the hash index itself also brings many limitations and disadvantages due to its particularity, mainly as follows.
(1) The hash index can only satisfy =, <=>, IN, IS NULL or IS NOT NULL queries, and range queries cannot be used.
Since the hash index compares the hash value after hash operation, it can only be used for equivalent filtering and cannot be used for range-based filtering, because the hash value after processing by the corresponding hash algorithm The size relationship is not guaranteed to be exactly the same as before the hash operation.

(2) Hash index cannot be used to avoid data sorting operations.
Since the hash index stores the hash value after hash calculation, and the size relationship of the hash value is not necessarily exactly the same as the key value before the hash operation, so the database cannot use the index data to avoid any Sorting operation;

(3) The hash index cannot be queried using part of the index key.
For the combined index, when calculating the hash value of the hash index, the combined index keys are merged and then the hash value is calculated together, instead of calculating the hash value separately, so the hash value is calculated through the first one or several index keys of the combined index. When querying, the hash index cannot be used.

(4) Hash index cannot avoid table scan at any time.
As we know before, hash index is to store the hash value of the hash operation result and the corresponding row pointer information in a hash table after hashing the index key. Since different index keys have the same hash value , so even if you get the number of records that satisfy a certain hash key value, you cannot directly complete the query from the hash index. You still have to make corresponding comparisons by accessing the actual data in the table and get the corresponding results.

(5) When a hash index encounters a large number of equal hash values, its performance will not necessarily be higher than that of the B-Tree index.
For index keys with low selectivity, if a hash index is created, there will be a large number of record pointer information associated with the same hash value. In this way, it will be very troublesome to locate a certain record, and it will waste multiple accesses to the table data, resulting in low overall performance.


B-Tree index is the most frequently used index type in the MySQL database. All storage engines except the Archive storage engine support B-Tree indexes. This is not only true in MySQL, but in fact, in many other database management systems, B-Tree index is also the main index type. This is mainly because the storage structure of B-Tree index has certain functions in database data retrieval. Very good performance.
Generally speaking, most of the physical files of the B-Tree index in MySQL are stored in the Balance Tree structure, that is, all the actual required data is stored in the Leaf Node of the Tree, and can be accessed from any Leaf Node The length of the shortest path is exactly the same, so we all call it B-Tree index. Of course, various databases (or various storage engines of MySQL) may store their own B-Tree indexes. The structure was slightly modified.
For example, the actual storage structure used by the B-Tree index of the Innodb storage engine is actually a B Tree, which is a very small modification based on the B-Tree data structure, and a storage structure is provided on each Leaf Node. In addition to the information related to the index key, pointer information pointing to the next LeafNode adjacent to the Leaf Node is also stored. This is mainly to speed up the efficiency of retrieving multiple adjacent Leaf Nodes.
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 basically the same storage form as other storage engines (such as the MyISAM storage engine) Ordinary B-Tree index, this index is called Secondary Index in the Innodb storage engine.
In Innodb, if you access data through the primary key, it is very efficient. If you access the data through the Secondary Index, Innodb first retrieves the Leaf Node through the relevant information of the Secondary Index and the corresponding index key. Then obtain the corresponding data row through the primary key value stored in the Leaf Node and then through the primary key index.
The difference between the primary key index and the non-primary key index of the MyISAM storage engine is very small, 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 Innodb's Secondary Index. The main difference is that the MyISAM storage engine stores the index key information on the Leaf Nodes, and
can directly locate the MyISAM data file. The information of the corresponding data row in the data (such as Row Number), but the key value information of the primary key will not be stored.

Indexes are divided into single column indexes and combined indexes. A single-column index means that an index only contains a single column. A table can have multiple single-column indexes, but this is not a combined index. Combined index, that is, one index contains multiple columns.
MySQL index types include:
(1) Ordinary index, which is the most basic index and has no restrictions. It has the following creation methods:

-- Create index

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

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

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

-- If it is CHAR, VARCHAR type, the prefix length can be less than the actual length of the field ; If it is BLOB and TEXT types, the prefix length must be specified, the same below.

-- 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 does not need to be written here, the system will automatically assign names username, username_2, username_3,...

-- Directly specify

CREATE TABLE mytable(

id INT,

username VARCHAR(16),

## when creating the table #city VARCHAR(16),

age INT,

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

);

-- Here the indexName index name can also be omitted

(2) Unique index, which is similar to the previous ordinary index, the difference is: the value of the index column Must be unique, but null values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique. It has the following creation methods (only add UNIQUE before the keyword INDEX when creating a normal index):

--

Create 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 abbreviated as ALTER TABLE mytable ADD UNIQUE indexName (username(10));

--

Specify directly when creating the table

CREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

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

);

(3) Primary key index, which is a Special unique index that does not allow null values. The primary key created at the same time when creating the table is the primary key index. The primary key index does not need to be named. A table can only have one primary key. The primary key index can be a unique index or a full-text index at the same time, but the unique index or the full-text index cannot coexist in the same index:

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

-- Directly specify CREATE TABLE mytable(

id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT, PRIMARY KEY(id)
);

(4) Full-text index, InnoDB storage engine does not support full-text index:

-- Create an index CREATE FULLTEXT INDEX indexName ON mytable(username(10));

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

- - It can also be abbreviated as ALTER TABLE mytable ADD FULLTEXT indexName (username(10));

-- Directly specify CREATE TABLE mytable(

id INT,
username VARCHAR(16) when creating the table) ,
city VARCHAR(16),
age INT,
FULLTEXT INDEX indexName (username(10))

-- can also be abbreviated as FULLTEXT indexName (username(10)))ENGINE =MYISAM;

-- Create a full-text index when creating a table. You need to set the storage engine of the table to MYISAM. The default InnoDB storage engine of the new version of mysql does not support full-text indexes

-- Delete the index DROP INDEX indexName ON mytable;

Although the index greatly improves the query speed, it also reduces the speed of updating the table, such as INSERT, UPDATE and DELETE on the table. Because when updating the table, MySQL not only needs to save the data, but also save the index file.

Creating index files will occupy disk space. Generally, this problem is not serious, but if you create multiple combined indexes on a large table, the index file will expand quickly.

The above is what I have summarized about creating an index in Mysql. I hope it will be helpful to everyone in the future.

Related articles:

Inserting data into myql in PHP displays garbled characters

myql5.7.7 Optimize configuration parameters _MySQL

The above is the detailed content of Create an index for Myql. For more information, please follow other related articles on the PHP Chinese website!