This article mainly describes how to accelerate MySQL index analysis and optimization of dynamic websites. iSH China Tutorial Online
1. What is an index? iSH China Tutorial Online
Index is used to quickly find records with specific values. All MySQL indexes are saved in the form of B-trees. If there is no index, when executing a query, MySQL must scan all records in the entire table starting from the first record until it finds a record 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 the column used as the search condition, MySQL can quickly get the location of the target record without scanning any records. If the table has 1000 records, finding the records through the index is at least 100 times faster than scanning the records sequentially. iSH Chinese Tutorial Online
Suppose we create a table named people: iSH Chinese Tutorial Online
CREATE TABLE people ( peopleid SMALLINT NOT NULL, iSH Chinese Tutorial Online
name CHAR(50) NOT NULL ); iSH Chinese Tutorial Online
Then, we are completely random Insert 1000 different name values into the people table. The name columns do not have any explicit order in the data file. If we create an index on the name column, MySQL will sort the name column in the index. For each item in the index, MySQL internally saves a "pointer" to the actual record location in the data file. So, if we want to find the peopleid of the record whose name is equal to "Mike" (the SQL command is "SELECT peopleid FROM people WHERE name='Mike';"), 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 returns exactly the peopleid (999) of that row. During this process, MySQL only needs to process one row to return the result. If there is no index on the "name" column, MySQL will scan all records in the data file, that is, 1,000 records! Obviously, the smaller the number of records that MySQL needs to process, the faster it can complete the task. iSH Chinese Tutorial Online
2. Index types iSH Chinese Tutorial Online
MySQL provides a variety of index types to choose from: iSH Chinese Tutorial Online
ordinary index: iSH Chinese Tutorial Online
This is the most basic index type, and it has no restrictions such as uniqueness. . Ordinary indexes can be created in the following ways: iSH Chinese tutorial online
creates an index, for example, CREATE INDEX <index name> ON tablename (list of columns); iSH Chinese tutorial online
modifies the table, for example, ALTER TABLE tablename ADD INDEX [index name] ] (list of columns); iSH Chinese Tutorial Online
Specify the index when creating a table, such as CREATE TABLE tablename ([...], INDEX [name of index] (list of columns)); iSH Chinese Tutorial Online
Unique Index: iSH Chinese Tutorial Online
This kind of index is basically the same as the previous "ordinary index", but there is one difference: all values in the index column can only appear once, that is, they must be unique. Unique indexes can be created in the following ways: iSH Chinese tutorials online
create indexes, such as CREATE UNIQUE INDEX <index name> ON tablename (list of columns); iSH Chinese tutorials online
modify tables, such as ALTER TABLE tablename ADD UNIQUE [index name] (list of columns); iSH Chinese Tutorial Online
Specify the index when creating a table, for example, CREATE TABLE tablename ([...], UNIQUE [name of index] (list of columns)); iSH Chinese Tutorial Online
primary key: iSH Chinese Tutorial Online
The primary key is a unique index, but it must be specified as "PRIMARY KEY". If you have ever used columns of type AUTO_INCREMENT, you may already be familiar with concepts like primary keys. The primary key is generally specified when creating the table, such as "CREATE TABLE tablename ([...], PRIMARY KEY (list of columns));". However, we can also add primary keys by modifying the table, such as "ALTER TABLE tablename ADD PRIMARY KEY (list of columns);". Each table can only have one primary key. iSH Chinese Tutorial Online
Full-text Index: iSH Chinese Tutorial Online
MySQL supports full-text indexing and full-text search starting from version 3.23.23. In MySQL, the index type of full-text index is FULLTEXT. Full-text indexes can be created on VARCHAR or TEXT type columns. It can be created by the CREATE TABLE command, or by the ALTER TABLE or CREATE INDEX command. For large data sets, creating a full-text index through the ALTER TABLE (or CREATE INDEX) command is faster than inserting records into an empty table with a full-text index.