Index analysis and optimization of MySQL techniques

mysql| Skills | index | The optimized index is used to quickly find records with a specific value, and all MySQL indexes are saved as B-trees. Without an index, MySQL must begin scanning all records of the entire table from the first record until a record is found that meets the requirements. The greater 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 the location of the target record without scanning any records. If the table has 1000 records, the index lookup record is at least 100 times times faster than the sequential scan record.

Let's say we created a table named people:

CREATE TABLE people (Peopleid SMALLINT not NULL, name CHAR () is not null);

Then we completely randomly insert 1000 different name values into the People table. The following figure shows a small portion of the data file for the People table:

As you can see, the Name column does not have any definite order in the data file. If we create an index of the name column, MySQL sorts the name column in the index:

For each item in the index, MySQL internally saves it a "pointer" to the location of the actual record in the data file. So, if we want to find the Peopleid (SQL command for the Select Peopleid from people WHERE Name= ' Mike ') that name equals "Mike" records , MySQL is able to find the "Mike" value in the index of name and then go directly to the corresponding row in the data file, returning exactly the Peopleid (999) of the row. In this process, MySQL can return results only by processing a single line. If there is no index for the "name" column, MySQL scans all the records in the data file, that is, 1000 records! Obviously, the fewer records that need to be processed by MySQL, the faster it can complete the task.

Types of indexes

MySQL offers a variety of index types to choose from:

Normal index

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

Create indexes, such as the name of the CREATE INDEX < index > on tablename (a list of columns); Modify tables, such as ALTER TABLE TableName ADD index [name of index] (list of columns); Specify indexes when creating tables, for example C reate TABLE tablename ([...], index [first name] (List of columns));

Uniqueness Index

This index is basically the same as the previous "normal index", but there is a difference: all values of an indexed column can only appear once, that is, must be unique. Uniqueness indexes can be created in several ways:

Create indexes such as the name > on tablename (List of columns) of Create unique index < index; modify tables, such as ALTER TABLE TableName ADD UNIQUE [name of index] (list of columns); A fixed index, such as Create TABLE tablename ([...], UNIQUE [name of index] (List of columns));

Primary key

A primary key is a unique index, but it must be specified as "PRIMARY key." If you've ever used a auto_increment type of column, you're probably already familiar with the concept of a primary key. Primary keys are typically specified when creating tables, such as "CREATE TABLE TableName ([...], PRIMARY key (List of columns)"; ”。 However, we can also add a primary key by modifying the table, such as "ALTER table tablename ADD PRIMARY key (a list of columns);" ”。 Each table can have only one primary key.

Full-text indexing

MySQL supports full-text indexing and Full-text search starting with version 3.23.23. In MySQL, the index type of the Full-text index is fulltext. Full-text indexing can be created on columns of varchar or text type. It can be created from the CREATE TABLE command, or through ALTER TABLE or CREATE INDEX commands. For a large dataset, it is faster to create a FULL-TEXT index through the ALTER TABLE (or CREATE INDEX) command than to insert the record into an empty table with Full-text indexing. The discussion below in this article no longer involves full-text indexing, and for more information, see MySQL documentation.

Single-column index and multiple-column index

An index can be a single-column index or a multiple-column index. Here's a concrete example to illustrate the difference between the two indexes. Suppose there is such a people table:

CREATE TABLE people (Peopleid SMALLINT NOT NULL auto_increment, FirstName char is NOT NULL, LastName char () is not NULL, Age SMALLINT NOT NULL, Townid SMALLINT NOT NULL, PRIMARY KEY (Peopleid));

[1] [2] [3] Next page