What kinds of MySQL database index types are there? Building methods and pros and cons

This article will focus on four types of masql database indexes, how do database indexes be built? The columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like. Hope this article can help everyone, first to understand the index is what it, in a nutshell is: Index is the key to fast search.

MySQL indexing is important for the efficient operation of MySQL. Here are a few common types of MySQL indexes

In a database table, indexing a field can greatly improve query speed. Suppose we create a mytable table:

The code is as follows:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL);

We randomly inserted 10,000 records, including one: 5555, admin.

In the Find username= "admin" record select * from MyTable WHERE username= ' admin '; If an index has been established on username, MySQL does not need any scanning, that is, the record can be found exactly. Instead, MySQL scans all records, that is, to query 10,000 records.

Index sub-column indexes and composite indexes. A single-column index, that is, an index contains only single columns, and a table can have multiple single-row indexes, but this is not a composite index. A composite index, that is, a cable that contains multiple columns.

MySQL index types include:

I. GENERAL index

This is the most basic index and it has no limitations. It is created in the following ways:

1. Create an index

The code is as follows:

CREATE INDEX indexname on mytable (username (length));

If it is a Char,varchar type, length can be less than the actual length of the field, and if it is a blob and text type, length must be specified.

2. Modify the table structure

The code is as follows:
ALTER mytable ADD INDEX [IndexName] on (username (length))--Specify directly when creating a table

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, INDEX [IndexName] (username (length)));

--syntax for dropping an index:

DROP INDEX [IndexName] on mytable;

Second, unique index

It is similar to the previous normal index, except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:

The code is as follows:

CREATE UNIQUE INDEX indexname on mytable (username (length))
--Modify table structure
ALTER mytable ADD UNIQUE [IndexName] on (username (length))
--Specify directly when creating a table
CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, UNIQUE [IndexName] (username (length)));

Third, primary key index

It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built:

The code is as follows:

CREATE TABLE mytable (ID INT not NULL, username VARCHAR (+) NOT NULL, PRIMARY KEY (id));

Of course, you can also use the ALTER command. Remember: A table can have only one primary key.

Iv. Combined Index

To visually compare single-column and composite indexes, add multiple fields to the table:

The code is as follows:

CREATE TABLE mytable (ID int NOT NULL, username varchar (+) NOT NULL, City VARCHAR () is not null, and age INT is not null);

To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index:

The code is as follows:

ALTER TABLE mytable ADD INDEX name_city_age (name (ten), city,age); [Code]
When the table is built, the usernname length is 16, which is used here in 10. This is because, in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.

If you set up a single-column index on Usernname,city,age, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient.

The establishment of such a composite index, in fact, is equivalent to the following three sets of composite indexes:

Usernname,city,age usernname,city Usernname Why not city,age such a combination index? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not as long as the combined index is used for queries that contain these three columns, the following SQL uses this combined index:
[Code]
SELECT * FROM MyTable whree username= "admin" and city= "Zhengzhou" select * FROM MyTable whree username= "admin"

And the next few are not used:

The code is as follows:
SELECT * FROM MyTable whree age=20 and city= "Zhengzhou" select * FROM MyTable whree city= "Zhengzhou"

V. How to build an index

Here we have learned to build an index, so where do we need to build the index? In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like. For example:

The code is as follows:

SELECT t.name from MyTable T left joins MyTable m on T.name=m.username WHERE m.age=20 and m.city= ' Zhengzhou '

The city and age need to be indexed, because the userame of the MyTable table also appears in the join clause, and it is necessary to index it.

Just now it is only necessary to index the like at certain times. Because MySQL does not use indexes when querying with wildcards% and _. For example, the following sentence will use the index:

The code is as follows:
SELECT * FROM MyTable WHERE username like ' admin% '
And the following sentence will not be used:

The code is as follows:

SELECT * FROM MyTable wheret Name like '%admin '

Therefore, you should pay attention to the above differences when using like.

Vi. Index Disadvantages

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. So the index has its drawbacks as well:

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

2. index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly.

Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements.

Vii. use of indexes should be noted:

There are some tips and considerations when working with indexes:

1. The index does not contain columns with null values

This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.

2. Using a short index

Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

3. Index column sorting

The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.

4.like Statement Operations

It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.

5. Do not perform calculations on columns

The code is as follows:
SELECT * from the users where year (adddate) <2007;

The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to:

The code is as follows:

SELECT * from users where adddate< ' 2007-01-01 ';

6. Do not use not and <> operations

Above, the MySQL index type is introduced. We hope to help you.

Two methods of indexing:

B-tree Index

Note: The name Btree index, the big aspect looks, all uses the balance tree, but the concrete realization on, each storage engine slightly different, for example, strictly speaking, NDB engine, uses is T-tree
Myisam,innodb, the B-tree index is used by default, B-tree's theoretical query time complexity is O (log2 (N-1)), N is the number of rows in the data table

Hash index
Using the Memory storage engine table, the default is the hash index, hash of the theoretical query Time Complexity O (1)