MySQL indexing usage and performance optimization

One, indexing base usage

1. Create an index

You can create an index when you execute a CREATE TABLE statement, or you can add an index to a table by using either CREATE INDEX or ALTER TABLE alone.

1>alter TABLE

ALTER table is used to create a normal index, a unique index, or a primary key index.

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Where table_name is the name of the table to increase the index, column_list indicates which columns are indexed, and when multiple columns are separated by commas. Index name index_name optional, by default, MySQL assigns a name to the first indexed column. In addition, ALTER TABLE allows multiple tables to be changed in a single statement, so multiple indexes can be created at the same time.

2>create INDEX

Create index adds either a normal index or a unique index to a table.

CREATE INDEX index_name on table_name (column_list)
CREATE UNIQUE INDEX index_name on table_name (column_list)

TABLE_NAME, index_name, and column_list have the same meaning as the ALTER TABLE statement, and the index name is not selectable. In addition, you cannot create a primary key index with the CREATE INDEX statement.

2. Delete Index
You can use ALTER TABLE or DROP INDEX statements to delete an index. Similar to the CREATE INDEX statement, DROP Index can be handled as a statement within the ALTER TABLE, as follows.

DROP INDEX index_name on Talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

Where the first two statements are equivalent, delete the index index_name from the table_name. The 3rd statement is only used when deleting the primary key index, because a table can have only one primary key index, so you do not need to specify an index name. If the primary key index is not created, but the table has one or more unique indexes, MySQL deletes the first unique index. If a column is deleted from the table, the index is affected. For indexes of multiple-column combinations, if you delete one of the columns, the column is also deleted from the index. If all the columns that make up the index are deleted, the entire index is deleted.

Note: Use the Delete primary key index mysql> ALTER TABLE t_user drop PRIMARY key;

The error 1075 (42000) appears as follows: Incorrect table definition; There can is only one auto column and it must be defined as a key
Reason: The primary key field in the table is an automatic growth cause failure, which can be removed from growth.

3. View Index

Mysql> Show index from Tblname;
Mysql> show the keys from Tblname;

Parameter description:
· Table
The name of the table.
· Non_unique
0 if the index cannot include duplicate words. 1 if it is possible.
· Key_name
The name of the index.
· Seq_in_index
The column sequence number in the index, starting at 1.
· column_name
The column name.
· Collation
How the columns are stored in the index. In MySQL, there is a value of ' a ' (ascending) or null (no classification).
· Cardinality
An estimate of the number of unique values in the index. Can be updated by running Analyze table or myisamchk-a. The cardinality is counted according to the statistics that are stored as integers,
So even for small tables, this value is not necessarily accurate. The larger the cardinality, the greater the chance that MySQL will use the index when it comes to syndication.
· Sub_part
The number of characters indexed if the column is only partially indexed. Null if the entire column is indexed.
· Packed
Indicates how the keyword is compressed. Null if it is not compressed.
· Null
If the column contains null, it contains Yes. If not, the column contains No.
· Index_type
Used indexing method (Btree, Fulltext, HASH, Rtree).
· Comment
Memo Information

Two, index performance optimization

This is a very simple table, 2 million order of magnitude of complex queries will be very slow, such as the execution of the following SQL statement.

SELECT A.id,from_unixtime (A.time)

From article as a

WHERE a.title= ' php pen questions and Answers--Basic language aspects '

The query time basically takes 50-100 seconds, this is very scary, if combined with some other constraints, the database will be crazy to consume memory.

If the database is indexed to the title field at this time, query efficiency will be greatly enhanced, as shown in the following figure. Visible for large databases, indexing is a very, very important optimization tool (there will certainly be many other ways to optimize such databases, but this article does not discuss the topic. ), so much nonsense, below begins to summarize the usage and performance optimization of indexes in MySQL and some considerations.

The concept of an index
An index is a special file (an index on a INNODB datasheet is an integral part of a tablespace) that contains a reference pointer to all records in the datasheet. More commonly, the database index is like a book in front of the directory, can speed up the query speed of the database. The SQL statement above, in the absence of an index, the database will traverse all 200 data to select the eligible, and after the corresponding index, the database will directly find the criteria in the index of the option. If we replace the SQL statement with the "SELECT * from article WHERE id=2000000", do you want the database to read 2 million rows of data in order to give you the result or locate it directly in the index? The above two pictures in sharp contrast have been given the answer (note: The general database by default will be the primary key generation index).

Index is divided into clustered index and nonclustered index, clustered index is based on the physical location of data storage, but not clustered index is different; clustered index can improve the speed of multiple-line retrieval, but not clustered index is fast for single line retrieval.

Types of indexes
1. General Index

This is the most basic index, and it has no limitations, for example, the index created in the title field above is a normal index.

– Create an index directly

CREATE INDEX indexname on table (column (length))

– Add indexes by modifying the table structure

ALTER Tableadd INDEX indexname on (column length)

– Create indexes at the same time when tables are created

CREATE table ' table ' (

' id ' int (one) 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 (ten) null DEFAULT NULL,

PRIMARY KEY (' id '),

INDEX IndexName (title (length))

)

– Delete Index

DROP INDEX indexname on table

2. Unique index

Like a normal index, the value of an indexed column must be unique, but a null value (note is different from the primary key) is allowed. If it is a combined index, the combination of the column values must be unique, and the method created is similar to the normal index.

– Create a unique index

CREATE UNIQUE INDEX indexname on table (column length)

– Modify the table structure

ALTER table ADD UNIQUE indexname on (column length)

– Specify directly when creating a table

CREATE table ' table ' (

' id ' int (one) 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 (ten) null DEFAULT NULL,

PRIMARY KEY (' id '),

UNIQUE IndexName (title (length))

);

3. Full-Text indexing (fulltext)

MySQL starts with version 3.23.23 for Full-text indexing and Full-text search, and fulltext indexes are available only for MyISAM tables; they can be created from a char, varchar, or text column as part of a CREATE TABLE statement, or subsequently use alter TABLE or CREATE index is added. For a larger dataset, enter your data into a table without a Fulltext index, and then create an index that is faster than entering the data into the existing Fulltext index. But it's important to remember that for large data tables, generating Full-text indexes is a very time-consuming way to consume hard disk space.

– Create a table that is appropriate to add a Full-text index

CREATE table ' table ' (

' id ' int (one) 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 (ten) null DEFAULT NULL,

PRIMARY KEY (' id '),

Fulltext (content)

);

– Modify table structure to add a Full-text index

ALTER TABLE Article ADD fulltext index_content (content)

– Create an index directly

CREATE Fulltext INDEX index_content on article (content)

4. Single column index, multiple column index

Multiple single-column indexes have different query effects than single, multiple-column indexes, because MySQL can only use one index when executing a query, and selects one of the most restrictive indexes from multiple indexes.

5. Combined index (leftmost prefix)

Usually used in the SQL query statements are generally more restrictive conditions, so in order to further squeeze the efficiency of MySQL, it is necessary to consider establishing a composite index. For example, in the previous table, a composite index is established for title and time: ALTER TABLE article ADD index Index_titme_time (title), Time (10)). Creating such a composite index is equivalent to creating the following two sets of combined indexes:

–title,time

–title

Why not have time such a combination index? This is because the MySQL composite index "leftmost prefix" results. The simple understanding is to just start from the left. The combined index is not used as long as the query that contains both columns, as shown in the following few sql:

– Use the index to the above

SELECT * FROM article Whree title= "led Daylight tube" and time=1234567890

SELECT * FROM article Whree utitle= "led Daylight tube"

– Do not use the above index

SELECT * from article Whree time=1234567890

Optimization of MySQL Index
The benefits of using indexes are mentioned above, but excessive use of indexes can cause abuse. So the index has its drawbacks: while indexing greatly improves query speed, it reduces the speed at which tables are updated, such as INSERT, UPDATE, and delete tables. Because when you update the table, MySQL not only saves the data, but also saves the index file. Index files that create indexes that consume disk space. Generally this is not a serious problem, but if you create multiple combinations of indexes on a large table, the index files will swell up very quickly. Indexing is just a factor in improving efficiency, and if your MySQL has large data tables, it takes time to study the best indexes, or optimize the query statements. Here are some summaries and favorites for MySQL indexing Considerations and Optimization methods.

1. When do I use clustered or nonclustered indexes?

Action description using a clustered index with a nonclustered index
Columns are often grouped sorted using
Returns a range of data used without using
One or very few different values do not use without using
A small number of different values are used without using the
Large numbers of different values do not use
Frequently updated columns are not used
The foreign key columns are used using the
Use the primary key column using the
Frequently modifying index columns does not use

In fact, we can understand the table above by using examples of the definitions of the previous clustered and nonclustered indexes. For example, returns a range of data items. For example, if you have a table with a time column that happens when you set up the aggregate index in that column, this speed will be quick when you query all the data from January 1, 2004 to October 1, 2004, because the text of your dictionary is sorted by date, The clustering index only needs to find the beginning and end data in all the data to be retrieved, and unlike nonclustered indexes, you must first look up the page number of each item in the table of contents, and then find the specific content based on the page number. In fact, this specific usage I am not very understanding, can only wait for the late project development slowly learned.

2. The index does not contain columns with null values

This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as one column in the composite index contains null values. So we don't want the default value of the field to be null when designing the database.

3. Using Short Index

Index A string column, if possible, to specify a prefix length. For example, if you have a column with char (255), if most values are unique within the first 10 or 20 characters, do not index the entire column. Short indexing can not only improve query speed but also save disk space and I/O operations.

4. Indexed column Sorting

The MySQL query uses only one index, so the columns in the order by are not indexed if the index is already used in the WHERE clause. Therefore, do not use sort operations when the database default sort meets the requirements, and try not to include sorting of multiple columns, preferably if you need to create a composite index for these columns.

5. Like statement operation

It is generally discouraged to use like operations, and how to use them is also a problem if not used. Like "%aaa%" does not use indexes and like "aaa%" can use indexes.

6. Do not perform operations on columns

For example: SELECT * from the users where year (adddate) <2007, which will operate on each row, will cause the index to fail with a full table scan, so we can change to: SELECT * from Users where adddate < ' 2007-01-01′. On this point can be onlookers: a single quotation mark caused by MySQL performance loss.

Finally, MySQL uses the index only for the operator: <,<=,=,>,>=,between,in, and sometimes like (without the wildcard% or _). In theory each table can create up to 16 indexes, but unless it is really a lot of data, otherwise too much use of the index is not so fun, such as I just for the text type of the field to create an index, the system almost stuck to death.