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.
2. Unique index
A normal index allows the indexed data column to contain duplicate values. For example, because a person might have the same name, it may occur two or more times in the same employee profile data table.
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.
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. Composite Index
An index can overwrite multiple data columns, such as indexes such as index (ColumnA, COLUMNB). The feature of this index is that MySQL can selectively use one such index. If the query operation only needs an index on the ColumnA data column, you can use the composite Index, index (ColumnA, COLUMNB). However, this usage applies only to combinations of data columns that are arranged in the composite index. For example, index (a, B, c) can be used as an index to a or (a, b), but not as an index to B, C, or (b, c).
6 Full-text indexing
A normal index on a text field can only speed up the retrieval of the string that appears at the front of the field content, 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. A full-text index can be created with a data table, or it can be added using the following command when necessary later:
ALTER TABLE tablename 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 TableName
WHERE MATCH (Column1, Column2) against (' Word1 ', ' word2 ', ' Word3 ')
The above command will query all data records for Word1, Word2, and Word3 in the Column1 and Column2 fields.
Annotations: InnoDB data tables do not support full-text indexing.
optimization of queries and indexes
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.
People can often get some help from the explain select command when they are unsure of which data columns to create indexes on. 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 correlation, the system is most efficient, then the const, EQ_REF, ref, range, index, and all (all means: corresponds to each record in the previous data table, All records in this data table must be read over again-this can often be avoided with an index.
The Possible_keys data column gives the various indexes that MySQL can use when searching for data records. The Key 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, the Key_len data joins can also see which parts of MySQL it uses. As a general rule, the smaller the value in the Key_len data column, the better (meaning faster).
The Ref data column gives the name of the data column in the other data table in the association relationship. The row data column is the number of data rows that MySQL expects to read from this data table when it executes 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.
MySQL normal index, unique index, primary index, foreign key index, composite index, use of full-text index
