MySQL creates an index and an understanding of the index

Creating an index is the establishment of an index on one or more columns of a table to increase the speed of access to the table. There are 3 ways to create an index, the 3 ways to create an index when creating a table, to create an index on a table that already exists, and to create an index by using the ALTER TABLE statement. This section explains the 3 ways to create indexes in detail.

7.2.1 Creating an index when creating a table (1)

The simplest and most convenient way to create a table is by creating an index directly. Its basic form is as follows:

CREATE Table Table name (property name data type [integrity constraint],
Property name data type [integrity constraint],
......
Property name Data type
[UNIQUE | Fulltext | SPATIAL] INDEX | KEY
Alias (Property name 1 [(length)] [ASC | DESC])
);
Where unique is an optional parameter that indicates that the index is a unique index; fulltext is an optional parameter that indicates that the index is a full-text index; Spatial is also an optional parameter that indicates that the index is a spatial index; the index and key parameters are used to specify that the field is indexed, and both of them choose one of them. The function is the same; an "Alias" is an optional parameter used to give a new name to the created index; the "Property 1" parameter specifies the name of the field for the index, which must be the field defined previously; "Length" is an optional parameter that refers to the length of the index, which must be the string type to be used; ASC and DESC Are optional, the ASC parameter indicates an ascending arrangement, and the "DESC" parameter indicates descending order.


1. Create a normal index

When creating a normal index, you do not need to add any unique, fulltext, or spatial parameters.

"Example 7-1" creates a table with the table named Index1, which is indexed on the ID field in the table. The SQL code is as follows:

CREATE  TABLE  index1 (id    INT, name   VARCHAR (), Sex    BOOLEAN, INDEX (ID));

The run result shows that the creation was successful, using the show CREATE TABLE statement to view the structure of the table. Shown below:

mysql> SHOW CREATE TABLE index1 \g *************************** 1. Row ***************************        table:index1 Create table:create Table ' index1 ' (   ' id ' int (one) ' DEFAULT null,
    ' name ' varchar () default NULL,   ' sex ' tinyint (1) default NULL,   KEY ' index1_id ' (' id ')) engine=innodb default C Harset=utf81 row in Set (0.00 sec)

As a result, you can see that an index named INDEX1_ID has been established on the ID field. Use the explain statement to see if the index is being used, and the SQL code is as follows:

Mysql> EXPLAIN SELECT * from index1 where id=1 \g *************************** 1. Row ***************************            id:1   select_type:simple         table:index1          type:ref Possible_keys: index1_id           key:index1_id       key_len:5           ref:const          rows:1         extra:1 row in Set (0.00 sec)

The results above show that the values at Possible_keys and key are index1_id. Indicates that the INDEX1_ID index already exists and has begun to function.

2. Create a Uniqueness Index

When you create a uniqueness index, you need to constrain it with a unique parameter.

Under Example 7-2, create a table named Index2, which establishes a unique index named INDEX2_ID on the ID field in the table, and is arranged in ascending order. The SQL code is as follows:

CREATE  TABLE  index2 (id    INT  UNIQUE, name   VARCHAR), unique  INDEX  index2_id ( ID  ASC));

The run result shows that the creation was successful, using the show CREATE TABLE statement to view the structure of the table. Shown below:

mysql> SHOW CREATE TABLE index2 \g *************************** 1. Row ***************************        table:index2 Create table:create Table ' index2 ' (   ' id ' int (one) ' DEFAULT Null,
    ' name ' varchar default NULL,   unique key ' ID ' (' id '),   unique key ' index2_id ' (' id ')) engine=innodb default C Harset=utf81 row in Set (0.00 sec)

As a result, you can see that a unique index named INDEX2_ID has been established on the ID field. The ID field here can be not uniquely constrained, or a unique index can be created successfully on that field. However, this may not achieve the purpose of increasing the query speed.


3. To create a full-text index

A full-text index can only be created on a field of char, varchar, or text type. Moreover, only the MyISAM storage engine now supports full-text indexing.

Under Example 7-3, create a table named Index3 with a full-text index named index3_ info on the Info field in the table. The SQL code is as follows:

CREATE  TABLE  index3 (id    INT  , Info   VARCHAR (), fulltext  INDEX  index3_info (info)) Engine=myisam;

The run result shows that the creation was successful, using the show CREATE TABLE statement to view the structure of the table. Shown below:

mysql> SHOW CREATE TABLE index3 \g *************************** 1. Row ***************************        table:index3 Create table:create Table ' index3 ' (   ' id ' int (one) ' DEFAULT Null,
    ' info ' varchar (default NULL,   fulltext KEY ' index3_info ' (' info ')) Engine=myisam default charset=utf81 row in SE T (0.00 sec)

As a result, you can see that a full-text index named Index3_info has been established on the Info field. If the table's storage engine is not the MyISAM storage engine, the system prompts "ERROR 1214 (HY000): The Used table type doesn ' t support fulltext indexes '.

Note: Only the MyISAM storage engine currently supports full-text indexing, and the InnoDB storage engine does not support full-text indexing. Therefore, you must be aware of the type of table's storage engine when you create a full-text index. For information such as strings, literal data, and so on, which are frequently required to be indexed, you can consider storing them in tables in the MyISAM storage engine.

4. Create a single-column index

A single-column index is an index that is created on a single field in a table.

Under Example 7-4, create a table with the table named Index4 and create a single-column index named Index4_st on the Subject field in the table. The SQL code is as follows:

CREATE  TABLE  index4 (id    INT  , subject   VARCHAR (+), INDEX  Index4_st (subject (10)));

The run result shows that the creation was successful, using the show CREATE TABLE statement to view the structure of the table. Shown below:

mysql> SHOW CREATE TABLE index4 \g *************************** 1. Row ***************************        table:index4 Create table:create Table ' index4 ' (   ' id ' int (one) ' DEFAULT null,
    ' subject ' varchar (+) default NULL,   KEY ' Index4_st ' (' Subject ')) Engine=innodb default charset=utf81 row in Set (0.00 sec)

As a result, you can see that a single-column index named Index4_st has been established on the subject field. Careful readers may find that the subject field is 20 long and the Index4_st index is only 10. This is done to improve the query speed. For character-type data, you can query only a few character information in front of it without querying all the information.

5. Creating Multi-column indexes

Creating a multicolumn index creates an index on more than one field in the table.

Under Example 7-5, create a table with the table named Index5, and set up a multicolumn index named Index5_ns on the name and sex fields in the table. The SQL code is as follows:

CREATE  TABLE  index5 (id  INT  , name   VARCHAR), Sex   CHAR (4), INDEX  Index5_ns (name, Sex));

The run result shows that the creation was successful, using the show CREATE TABLE statement to view the structure of the table. Shown below:

mysql> SHOW CREATE TABLE index5 \g *************************** 1. Row ***************************        table:index5 Create table:create Table ' index5 ' (   ' id ' int (one) ' DEFAULT Null,
    ' name ' varchar () default NULL,   ' sex ' char (4) default NULL,   KEY ' Index5_ns ' (' name ', ' Sex ')) Engine=innodb DEF Ault charset=utf81 Row in Set (0.00 sec)

As a result, you can see that a single-column index named Index5_ns has been established on the name and sex fields. In a multicolumn index, the index is used only when the first field in these fields is used in the query criteria. Use the Explain statement to view the usage of the index. If only the name field is queried as a query condition, the result is as follows:

Mysql> EXPLAIN SELECT * from index5 where name= ' Hjh ' \g *************************** 1. Row ***************************            id:1   select_type:simple         table:index5          type:ref Possible_keys: Index5_ns           Key:index5_ns       key_len:83           ref:const          rows:1         extra:using Index condition 1 row in SE T (0.00 sec)

The results show that both the Possible_keys and key values are index5_ns. Additional information (Extra) shows that the index is being used. This indicates that index Index5_ns is already in use when indexed using the Name field. If you query using only the sex field as the query criteria, the results are as follows:

Mysql> EXPLAIN SELECT * from index5 where sex= ' n ' \g  *************************** 1. Row ************************** *            id:1   select_type:simple         table:index5          type:all possible_keys:null           key:null       Key_len: NULL           ref:null          rows:1         extra:using where 1 row in Set (0.00 sec)

At this point, the results show that both the Possible_keys and key values are null. Additional information (Extra) shows that the Where condition query is being used instead of using an index.

Tip: When working with multi-column indexes, it is important to note that indexes are only triggered when the first field in the index is used. If the first field in the index is not used, the multi-column index will not work. Therefore, you can consider optimizing multi-column indexes when optimizing query speed.

6. Create a spatial index

You must use the spatial parameter to set the spatial index when you create it. When you create a spatial index, the storage engine for the table must be of type MyISAM. Also, the index field must have a non-null constraint.

Under Example 7-6, create a table named index6 that establishes a spatial index named INDEX6_SP on the Space field in the table. The SQL code is as follows:

CREATE  TABLE  index6 (id  INT  , Space  GEOMETRY not  NULL, SPATIAL  INDEX  index6_sp ( space)) Engine=myisam;

The run result shows that the creation was successful, using the show CREATE TABLE statement to view the structure of the table. Shown below:

mysql> SHOW CREATE TABLE index6 \g *************************** 1. Row ***************************        table:index6 Create table:create Table ' index6 ' (   ' id ' int (one) ' DEFAULT null,
    ' space ' geometry not NULL,   SPATIAL KEY ' index6_sp ' (' space ') Engine=myisam the DEFAULT charset=utf81 row in set (0. XX sec)

As a result, you can see that a spatial index named INDEX6_SP has been established on the space field. It is worth noting that the space field is non-empty and the data type is geometry type. This type is a spatial data type. Space types include geometry, point, linestring, and polygon types. These spatial data types are seldom used at ordinary times.

What is an index?


Indexes are used to quickly look for records with specific values, and all MySQL indexes are saved as B-trees. If there is no index, MySQL must start scanning all records of the entire table from the first record until it finds a record that meets the requirements. The higher the number of records in the table, the higher the cost of this operation. If an index has been created on a column that is a search condition, MySQL can quickly get to where the target record is without scanning any records. If the table has 1000 records, finding records by index is at least 100 times times faster than sequential scan records, and the individual feels 100 times times as fast as a little exaggerated.

Let's say we've created a table named people:

Code:create TABLE people (Peopleid SMALLINT not NULL, name CHAR (a) not null);

, we completely randomly insert 1000 different name values into the People table.
As you can see, the Name column in the data file does not have any definite order. If we create the index of the name column, MySQL will sort the name column in the index:
For each item in the index, MySQL internally holds a pointer to the location of the actual record in the data file. So if we want to find the name equals "Mike" Record Peopleid (SQL command for "Select Peopleid from people WHERE Name= ' Mike ';" ), MySQL can find the "Mike" value in the index of name, then go directly to the corresponding line in the data file and return exactly the line's Peopleid (999). In this process, MySQL only has to process one row to return the results. If there is no index to the "name" column, MySQL will scan all records in the data file, that is, 1000 records! Obviously, the fewer records that require MySQL to process, the faster it can complete the task.


Type of index

MySQL offers a variety of index types to choose from:

Normal index

This is the most basic type of index, and it has no limitations such as uniqueness. Normal indexes can be created in the following ways:

CODE: Create an index, such as the name of the CREATE INDEX < index >; On tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD index [name of index] (list of columns);
Specify an index when creating a table, such as CREATE TABLE TableName ([...], index [name of indexed] (List of columns));

Uniqueness Index

This index is basically the same as the previous "normal index", but there is one difference: all the values of an indexed column can only occur once, that is, they must be unique. A unique index can be created in the following ways:

CODE: Creating an index, such as the name of the Create UNIQUE Index < index >; On tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD UNIQUE [index name] (List of columns);
Specify indexes when creating tables, such as CREATE TABLE TableName ([...], UNIQUE [index name] (List of columns)
);

Primary key

The primary key is a unique index, but it must be specified as "PRIMARY key". If you've ever used a auto_increment type column, you're probably already familiar with concepts like the primary key. The primary key is typically specified when creating the table, such as "CREATE TABLE TableName ([...], PRIMARY KEY (List of columns)"; ”。 However, we can also add the primary key by modifying the table, such as "ALTER table tablename Add PRIMARY key (List of columns); ”。 There can be only one primary key per table.

Full-Text Indexing

MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column. It can be created by the CREATE TABLE command or by the ALTER TABLE or CREATE INDEX command. For large datasets, it is faster to create a full-text index by using the ALTER TABLE (or CREATE INDEX) command than to insert the record into an empty table with a full-text index. The following discussion in this article no longer involves full-text indexing, see MySQL documentation for more information.


Single-column and multicolumn indexes


The index can be a single-column index or a multicolumn index. Let's take a concrete example to illustrate the differences between the two indexes. Suppose there is such a people table:

Code:create TABLE people (Peopleid SMALLINT not NULL auto_increment, FirstName CHAR (50)
Not NULL, LastName CHAR (a) Not NULL, age SMALLINT NOT NULL, Townid SMALLINT not
NULL, PRIMARY KEY (Peopleid));

The

Below is the data we inserted into this people table:

There are four people named "Mikes" in this data fragment (two of them Sullivans, two surname McConnells), two are 17 years of age, and a different name, Joe Smith. The primary purpose of the

table is to return the corresponding Peopleid based on the specified user name, first name, and age. For example, we may need to find the Peopleid (SQL command for select Peopleid from people WHERE Firstname= ' Mike ' and Lastname= ' for the user who is named Mike Sullivan, who is 17 years of age Sullivan ' and age=17;). Since we don't want MySQL to scan the entire table every time it executes a query, we need to consider using an index.

First, we can consider creating an index on a single column, such as FirstName, LastName, or Age column. If we create an index of the FirstName column (ALTER TABLE people add index FirstName (firstname), MySQL will quickly limit the search to those firstname= ' Mike ' records through this index , and then search for other criteria on this intermediate result set: It first excludes records whose lastname are not equal to "Sullivan", and then excludes records whose age is not equal to 17. When the record satisfies all search criteria, MySQL returns the final search results.

with the FirstName column index, MySQL is much more efficient than a full scan of the execution table, but we require that the number of logs scanned by MySQL still far exceeds what is actually needed. Although we can delete the index on the FirstName column, and then create an index of the LastName or age column, it seems that the efficiency of creating an index search is still similar, regardless of which column.

To improve search efficiency, we need to consider using multi-column indexes. If you create a multi-column index for the three columns of FirstName, LastName, and age, MySQL can find the correct results with a single search! Here is the SQL command to create the multicolumn index:

Code:alter TABLE People ADD INDEX fname_lname_age (firstname,lastname,age);

Since the index file is saved in the B-tree format, MySQL can immediately go to the appropriate FirstName and then go to the appropriate LastName, and finally to the appropriate age. In the absence of any record of the scanned data file, MySQL correctly finds the target record of the search!

So, if you create a single-column index on the three columns of FirstName, LastName, and age, will the effect be the same as creating a multicolumn index of FirstName, LastName, and age? The answer is no, the two are totally different. When we execute the query, MySQL can use only one index. If you have three single-column indexes, MySQL will try to select one of the most restrictive indexes. However, even the most restrictive single-column index is limited in its ability to be significantly less than a multicolumn index on the three columns of FirstName, LastName, and age.


Leftmost prefix


A multi-column index has another advantage, which is manifested by the concept of the leftmost prefix (leftmost prefixing). Continuing to consider the previous example, we now have a multi-column index on the FirstName, LastName, and age columns, which we call the index fname_lname_age. When the search condition is a combination of the following columns, MySQL uses the fname_lname_age index:

Code:firstname,lastname,age
Firstname,lastname
FirstName

On the other hand, it is equivalent to the index we created (Firstname,lastname,age), (Firstname,lastname), and (FirstName) on these column combinations. The following queries all have the ability to use this Fname_lname_age index:

Code:select Peopleid from people WHERE firstname= ' Mike ' and lastname= ' Sullivan ' and
Age= ' 17 '; SELECT Peopleid from people WHERE firstname= ' Mike ' and
Lastname= ' Sullivan '; SELECT Peopleid from people WHERE firstname= ' Mike '; The
Following queries cannot use the index at All:select Peopleid from people WHERE
Lastname= ' Sullivan '; SELECT Peopleid from people WHERE age= ' 17 '; SELECT Peopleid
From people WHERE lastname= ' Sullivan ' and age= ' 17 ';

Select Index Column


In the performance optimization process, choosing which columns to create indexes on is one of the most important steps. There are two main types of columns that you can consider using indexes: columns that appear in the WHERE clause, columns that appear in the join clause. Consider the following query:

Code:select Age # # does not use the index from people WHERE firstname= ' Mike ' # # Consider using the index and
Lastname= ' Sullivan ' # # consider using an index

This query is slightly different from the previous query, but it still belongs to a simple query. Because age is referenced in the Select section, MySQL does not use it to restrict column selection operations. Therefore, it is not necessary to create an index of the age column for this query. The following is a more complex example:

Code:select people.age, # #不使用索引 Town.name # #不使用索引 from people left JOIN
People.townid=town.townid # #考虑使用索引 WHERE firstname= ' Mike ' # #考虑使用索引 and
Lastname= ' Sullivan ' # #考虑使用索引

As in the previous example, because FirstName and LastName appear in the WHERE clause, these two columns still have the necessary to create an index. In addition, because the Townid of the town table is listed in the JOIN clause now, we need to consider creating the index of the column.

So, can we simply assume that each column that appears in the WHERE clause and the JOIN clause should be indexed? Almost so, but not entirely. We also have to take into account the type of operator that compares the columns. MySQL uses the index only for the following operators: <,<=,=,>;,>;=,between,in, and sometimes like. The case in which you can use an index in a like operation is when another operand is not preceded by a wildcard character (% or _). For example, "Select Peopleid from people WHERE firstname like ' mich% ';" This query will use the index, but "select Peopleid from people WHERE firstname like '%ike ';" This query does not use indexes.


Analyze index efficiency


Now that we know a few things about how to choose indexed columns, we can't tell which one is the most effective. MySQL provides a built-in SQL command to help us complete this task, which is the explain command. The general syntax for the EXPLAIN command is: EXPLAIN <sql command >;. You can find more instructions on this command in the MySQL documentation. Here is an example:

Code:explain SELECT Peopleid from people WHERE firstname= ' Mike ' and lastname= ' Sullivan '
and age= ' 17 ';

This command will return the following analysis results:

Table Type Possible_keys key Key_len ref rows Extra
People ref fname_lname_age Fname_lname_age 102 Const,const,const 1 Where used

Let's take a look at the meaning of this explain analysis result.

Table: This is the name of the watch.

Type: Types of connection operations. The following is a description of the MySQL documentation about the ref connection type:

"For each combination of records in another table, MySQL reads all records with matching index values from the current table. If the connection operation uses only the leftmost prefix of the key, or if the key is not a unique or PRIMARY key type (in other words, if the connection operation cannot select a unique row based on the key value), then MySQL uses the ref connection type. If the key used by the connection operation matches only a small number of records, ref is a good type of connection. ”

In this example, because the index is not a unique type, ref is the best connection type we can get.

If explain shows that the connection type is "All" and you do not want to select most of the records from the table, then MySQL will be very inefficient because it will scan the entire table. You can add more indexes to solve this problem. For more information, see the MySQL manual for instructions.

Possible_keys:

The name of the index that may be available. The index name here is the index nickname specified when the index was created, and if the index does not have a nickname, the first column in the index is displayed by default (in this case, it is "FirstName"). The meaning of the default index name is often not obvious.

Key:

It shows the name of the index that MySQL actually uses. If it is empty (or null), then MySQL does not use the index.

Key_len:

The length of the part to be used in the index, in bytes. In this example, Key_len is 102, where FirstName accounts for 50 bytes, lastname accounts for 50 bytes, and age is 2 bytes. If MySQL only uses the FirstName portion of the index, then Key_len will be 50.

Ref

It shows the name of the column (or the word "const"), and MySQL will select rows based on these columns. In this example, MySQL selects rows based on three constants.

Rows

MySQL considers the number of records that it must scan before it can find the correct results. Obviously, the ideal number here is 1.

Extra:

Many different options may appear here, most of which will adversely affect the query. In this case, MySQL just reminds us that it will restrict the search result set with a WHERE clause.

MySQL creates an index and an understanding of the index