MySQL index detailed and optimized (key and index differences)

The concept of MySQL indexing
An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. More generally, the database index is like a directory in front of a book, which can speed up the database query.
The index is divided into clustered index and non-clustered index , and the clustered index is in order according to the physical location of the data, and the non-clustered index is different; The clustering index can improve the speed of multi-row retrieval, but the non-clustered index is very fast for the single-line retrieval.
It is important to note that too many indexes will affect the speed of updates and insertions because it requires the same update for each index file. For a table that often needs to be updated and inserted, there is no need to index a rarely used where clause, and for smaller tables, the cost of sorting is not significant and there is no need to create additional indexes.

1. General Index

The only task for a normal index (an index defined by the keyword key or index) is to speed up access to the data . Therefore, it should be only for those that most often appear in the query condition (where column = ...). or the data column in the sort condition (order by column) to create the index. Whenever possible, you should choose a data column that is the most tidy and compact data (such as an integer type of data column) to create an index.

[SQL]View Plain copy

2. – Create an index directly (length means 1ength characters before the name is used)
4. CREATE INDEX index_name on table_name (column_name (length))
6. – How table structure is modified to add an index
8. ALTER TABLE table_name ADD INDEX index_name on (column_name)
10. – Create the index at the same time when creating the table
12. CREATE TABLE ' table_name ' (
14. ' ID ' int (one) not NULL auto_increment,
16. ' title ' char (255) is not NULL,
18. PRIMARY KEY (' id '),
20. INDEX Index_name (title)
22. ) Engine=innodb DEFAULT charset=utf8mb4;
24. – Delete Index
26. DROP INDEX index_name on table_name;
30. Create a composite index.
32. CREATE INDEX Mytable_categoryid_userid on mytable (category_id,user_id);
34. Notice the habit of naming it? Use the table name _ Field 1 _ Field 2 Name Method

2. Unique index

Like a normal index, the difference is that the value of the indexed column must be unique, but it allows for a null value (note differs from the primary key). If it is a composite index, the combination of column values must be unique, similar to the creation method and the normal index.
If you can determine that a data column will contain only values that are different from each other, you should define it as a unique index with the keyword unique when creating an index for that data column. The Advantage of this is that it simplifies MySQL's management of the index, which makes it more efficient, and that MySQL automatically checks to see if the value of this field in the new record has already appeared in the field of a record when a new record is inserted into the data table; MySQL will refuse to insert that new record. In other words, a unique index guarantees the uniqueness of the data record. In fact, in many cases, the goal of creating a unique index is often not to improve access speed, but to avoid duplication of data.

[SQL]View Plain copy

2. – Create a unique index
4. CREATE UNIQUE INDEX index_name on table_name (column_name)
6. – Modify Table Structure
8. ALTER TABLE table_name ADD UNIQUE index_name on (column_name)
10. – Specify directly when creating a table
12. CREATE TABLE ' table_name ' (
14. ' ID ' int (one) not NULL auto_increment,
16. ' title ' char (255) is not NULL,
18. PRIMARY KEY (' id '),
20. UNIQUE Index_name (title)
22. );

3. Primary index

It has been repeated several times before: You must create an index for the primary key field, which is called the "primary Index". The only difference between a primary index and a unique index is that the first keyword used in the definition is primary rather than unique.

4. Foreign KEY Index

If you define a FOREIGN key constraint for a foreign key field, MySQL defines an internal index to help you manage and use foreign key constraints in the most efficient way.

5. Full-Text Indexing (fulltext)

MySQL supports full-text indexing and full-text search starting from version 3.23.23. fulltext index is only available for MyISAM tables ; they can be created as part of a CREATE TABLE statement from a char, varchar, or text column, or subsequently added using ALTER TABLE or CREATE INDEX. For larger datasets, enter your data into a table without a Fulltext index, and then create an index that is faster than entering the data into an existing Fulltext index. But remember, for a large data table, generating a full-text index is a very expensive way to consume hard disk space. The normal index on the
        text field can only speed up the retrieval of the string that appears at the front of the field, that is, the character at the beginning of the field content. If a field contains a large paragraph of text consisting of several or even multiple words, the normal index does not work. This kind of retrieval often appears in the form of like%word%, which is very complex for MySQL, and if the amount of data to be processed is large, the response time will be very long.  
Such occasions are where full-text indexing (Full-text index) can take its place. When this type of index is generated, MySQL creates a list of all the words that appear in the text, and the query operation retrieves the relevant data records based on the list.　　The full-text index can be created with the data table, or it can be added with the following command if necessary later:  
ALTER TABLE table_name Add fulltext (Column1, Column2)  
With full-text indexing, you can use the Select query command to retrieve data records that contain one or more given words. The following is the basic syntax for this type of query command:  
SELECT * from table_name 
WHERE MATCH (column1, Column2) against (' Word1 ', ' word2 ', ' wo Rd3 ')  
The above command will query all data records for Word1, Word2, and Word3 in the Column1 and Column2 fields.  

[SQL]View Plain copy

2. – Create tables that are suitable for adding full-text indexes
4. CREATE TABLE ' table_name ' (
6. ' ID ' int (one) not NULL auto_increment,
8. ' Content ' text CHARACTER SET UTF8 COLLATE utf8_general_ci NULL,
10. PRIMARY KEY (' id '),
12. Fulltext (content)
14. );
16. – Modify table structure to add full-text indexes
18. ALTER TABLE table_name ADD fulltext index_name (column_name)
20. – Create indexes directly
22. CREATE Fulltext INDEX index_name on table_name (column_name)

6. Single-column index, multicolumn index

Multiple single-column indexes differ from the query effect of a single multicolumn index because MySQL can use only one index when executing a query, and one of the most restrictive indexes is selected from multiple indexes.

5. Combination (composite) index (leftmost prefix)

Usually use the SQL query statements generally have more restrictive conditions, so in order to further extract the efficiency of MySQL, we should consider the establishment of a composite index. For example, the previous table establishes a composite index for title and time: ALTER Table Article ADD index Index_titme_time (title (), Time (10)). Creating such a composite index is actually equivalent to establishing the following two sets of composite indexes:
–title,time
–title
Why is there no such combination index as time? 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 all queries that contain these two columns will use the combined index, as shown in the following SQL
– Use the index above
SELECT * from article Whree title= ' test ' and time=1234567890;
SELECT * from article Whree title= ' test ';
– Do not use the index above
SELECT * from article Whree time=1234567890;

optimization of MySQL index

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. Therefore, the index also has its drawbacks : 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. 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. Here are some tips and optimizations for summarizing and collecting MySQL indexes.

1. When do I use a clustered or nonclustered index?

Action Description	Using Clustered Indexes	Using Nonclustered indexes
Columns are often sorted by grouping	Use	Use
Return data in a range	Use	Do not use
One or very few different values	Do not use	Do not use
A small number of different values	Use	Do not use
A large number of different values	Do not use	Use
Columns that are frequently updated	Do not use	Use
FOREIGN key columns	Use	Use
Primary key columns	Use	Use
Frequently modifying index columns	Do not use	Use

2. 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.

3. 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.

4. 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.

5. 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 .

6. Do not perform calculations on columns

For example: SELECT * from the users where year (adddate) <2007 will be performed on each row, which will cause the index to fail with a full table scan, so we can change to: SELECT * from Users where adddate < ' 2007-01-01′. On this point can be onlookers: a single quotation mark caused by the MySQL performance loss.

Finally, MySQL uses the index only for the operator: <,<=,=,>,>=,between,in, and sometimes like (not in the case of a wildcard% or _). In theory each table can create up to 16 indexes , but unless the amount of data is really many, otherwise too much use of the index is not so fun, such as I just for the text type of the field to create an index, the system almost stuck.

Supplemental EXPLAIN Usage:

Only when there is enough test data in the database, its performance test results have practical reference value. If there are only hundreds of data records in the test database, they are often loaded into memory after executing the first query command, which will make subsequent query commands execute very quickly-regardless of whether the index is used or not. The performance test results of a database are meaningful only if the database has more than 1000 records and the total amount of data exceeds the total amount of memory on the MySQL server.
When you are unsure of which data columns to create the index on, peopleEXPLAINThe Select command can often get some help there. This simply adds a explain keyword as a prefix to an ordinary select command. With this keyword, MySQL is not going to execute that select command, but to parse it. MySQL will list information such as the execution of the query and the index (if any) that is used in the form of a table.
In the output of the explain command, the 1th column is the name of the data table read from the database, sorted by the order in which they were read. The Type column specifies the association relationship (JOIN) between this data table and other data tables. Among the various types of association relationships,the most efficient is the system, followed by const, EQ_REF, ref, range, index, and all(all means: For each record in the previous data table, all records in the table must be read again-this can often be avoided by an index).
　　 Possible_keysThe data column gives the various indexes that MySQL can use when searching for data records.KeyThe data column is the actual mysql-selected index, which is given in the Key_len data column for the length of the byte count. For example, for an index of an integer data column, the byte length would be 4. If a composite index is used, theKey_lenData joins can also see which parts of MySQL are used specifically. As a general rule,the smaller the value in the Key_len data column, the better (meaning faster).
　　 refThe data column gives the name of the data column in the other data table in the associated relationship.RowThe data column is the number of data rows that MySQL expects to read from this data table when executing this query. The product of all the numbers in the row data column gives us a rough idea of how many combinations this query needs to handle.

--------------------------------------------------------------------------------------------------------------

8.key and Index differences

MySQL's key and index are somewhat confusing, which actually examines the understanding of the database architecture.
1).key is the physical structure of the database, it contains two levels of meaning, one is the constraint (emphasis on constraints and standardize the structure of the database integrity), and the second is the index (auxiliary query)。 Includes primary key, unique key, foreign key, and more.
Primary key has two functions, one is the binding function (constraint), used to standardize a storage primary key and uniqueness, but at the same time also set up an index on this key;
The unique key also has two functions, one is the constraint function (constraint), the specification data uniqueness, but at the same time also set up an index on this key;
Foreign key also has two functions, one is the binding function (constraint), normative data referential integrity, but also on this key set up an index;
It can be seen that MySQL key is both constraint and index meaning, which may differ from other database performance. (at least a foreign key is established on Oracle, and index is not automatically created), so there are several ways to create a key:
(1) Established at the field level in key mode, such as CREATE TABLE T (ID int not null primary key);
(2) established in CONSTRAINT mode at the table level, such as CREATE TABLE T (id int, CONSTRAINT pk_t_id PRIMARY key (ID));
(3) Set at the table level in key mode, such as CREATE TABLE T (ID int, primary key (ID));
Other key creation is similar, but in that way, both the constraint and index are established, but the index is using this constraint or key.

2). Index is the physical structure of the database, it's just a secondary query,It is created in a separate table space (the InnoDB tablespace in MySQL) and is stored in a similar directory structure. Index to classify, divided into prefix index, full-text index and so on;
Therefore, the index is simply an index, and it does not constrain the behavior of the indexed fields (that's what key does).
For example, CREATE TABLE t (ID int, index inx_tx_id (id));

3). The final explanation:
(1). We say index classification, sub-primary key index, unique index, ordinary index (this is the pure index), and so on, is based on whether the index as a key.
For example, CREATE TABLE t (ID int, unique index inx_tx_id (id)); --index as a key to use
(2). Most importantly, no matter how it is described, the understanding that index is a purely index, or as a key, will have two meanings or two functions.

I have a table, the aid is of type char.
For example
Aid
0314
0314589
0128
0789
031475684
0987

I need to execute a SQL to find out the total number of aid that starts with 0314
SELECT Count (*) as ' count ' from Aid_info WHERE ' aid ' like ' 314% ';
There is an index on the aid, but it is still relatively slow. So I want to use a short index to increase the speed ALTER TABLE Aid_info Add index Aid (Aid (4));
Because I think if you use a 4-bit short index, the above record should be in the index:
0314
0314
0128
0789
0314
0987
I thought it would be quicker to find like ' 314% ', but the truth was even slower. I want to know why this is? Who is the master to tell me?

MySQL index detailed and optimized (key and index differences)