MySQL database index usage and Performance Optimization

It is very difficult to master MySQL. Fortunately, the relational databases are similar, which is enough for me to upgrade from adding, deleting, modifying, querying to high-performance database architecture and tuning. The concepts in this period will not be mentioned, and it is hard for me to express them clearly, yesterday I wrote a small script to inject 2 million pieces of data into a table in my local MySQL database (Windows 7 flagship edition/1.66 GHz/2 GB memory/MySQL5.1.50). The data table structure is shown in, it is a relatively basic fixed-length table. Considering my poor affordability, id is auto-incrementing from 1, and the title field is one of the 20 random titles, the content is the same. time uses the timestamp instead of the datetime type, that is, 10-bit integer data.

For a table with an extremely simple structure, 2 million of complex queries will become very slow, such as executing the following SQL statement.

SELECT a. id, FROM_UNIXTIME (a. time)
FROM article AS
WHERE a. title = 'php pen questions and answers-Basic Language information'

The query time is generally 50-seconds. This is terrible. With the addition of joint queries and other constraints, the database will consume the memory.

If the index is set for the title field in the database, the query efficiency will be greatly improved, as shown in. It can be seen that index building is a very important Optimization Method for large databases (of course there will be many other methods to optimize such databases, but this topic is limited and will not be discussed for the moment .), So much nonsense, The following summarizes the use of MySQL indexes, performance optimization, and some precautions.

Index concept

An index is a special file (an index on an InnoDB data table is an integral part of a tablespace) that contains reference pointers to all records in the data table. More broadly speaking, database indexes are like directories in front of a book, which can speed up database queries. If the preceding SQL statement does not have an index, the database will traverse all 200 data entries and select the one that meets the criteria. With the corresponding index, the database directly finds the matching options in the index. If we replace the SQL statement with "SELECT * FROM article WHERE id = 2000000 ", so do you want the database to read 2 million rows of data in sequence and locate the results directly in the index? The comparison between the above two images has provided the answer (Note: by default, the database generates an index for the primary key ).

Indexes are divided into clustered indexes and non-clustered indexes. Clustered indexes are stored in the order of physical locations, rather than clustered indexes; clustering indexes can improve the speed of multi-row search, rather than clustering indexes can quickly retrieve individual rows.

Index type

1. Common Index

This is the most basic index with no restrictions. For example, the index created for the title field above is a common index.

-Directly create an index
Create index indexName ON table (column (length ))
-Add an index by modifying the table structure
ALTER tableADD INDEX indexName ON (column (length ))
-Create indexes at the same time when creating a table
Create table 'table '(
'Id' int (11) not null AUTO_INCREMENT,
'Title' char (255) character set utf8 COLLATE utf8_general_ci not null,
'Content' text character set utf8 COLLATE utf8_general_ci NULL,
'Time' int (10) null default null,
Primary key ('id '),
INDEX indexName (title (length ))
)
-Delete An index
Drop index indexName ON table

2. Unique Index

Similar to normal indexes, the value of an index column must be unique, but null values are allowed (note that it is different from the primary key ). If it is a combination index, the combination of column values must be unique. The creation method is similar to that of a common index.

-Create a unique index
Create unique index indexName ON table (column (length ))
-Modify Table Structure
ALTER table add unique indexName ON (column (length ))
-When creating a table, specify
Create table 'table '(
'Id' int (11) not null AUTO_INCREMENT,
'Title' char (255) character set utf8 COLLATE utf8_general_ci not null,
'Content' text character set utf8 COLLATE utf8_general_ci NULL,
'Time' int (10) null default null,
Primary key ('id '),
UNIQUE indexName (title (length ))
);

• 2 pages in total:
• Previous Page
• 1
• 2
• Next Page