Using MySQL indexing tips and considerations

I. Role of the Index

General application system, reading and writing ratio of about 10:1, and the insertion operation and general update operations rarely appear performance problems, encountered the most, is also the most prone to problems, or some complex query operations, so the optimization of query statements is obviously the most serious.

MySQL access is very fast when the amount of data and the amount of traffic is small, and whether or not indexing has little impact on access. However, when the amount of data and traffic surges, you will find that MySQL is slow, or even down, it must be considered to optimize SQL, the database to establish a proper index, is an important means of MySQL optimization.

The goal of the index is to improve the efficiency of the query, you can analogy dictionary, if you want to check the word "MySQL", we definitely need to locate the letter M, and then find the Y letter from the bottom, and then find the remaining SQL. If you don't have an index, you may need to look through all the words to find what you want. In addition to dictionaries, there are examples of indexes in life, such as train station schedules, book catalogs, and so on. They all work the same way, by shrinking the range of data they want to filter out the results they want, and by turning random events into sequential events, that is, we always lock data by the same search method.

When you create an index, you need to consider which columns are used for SQL queries, and then create one or more indexes for those columns. In fact, an index is also a table that holds a primary key or an indexed field, and a pointer to the actual table that can point each record. Database users do not see indexes, they are used to speed up queries only. The database search engine uses indexes to quickly locate records.

The INSERT and UPDATE statements take more time to execute in the table that owns the index, and the SELECT statement executes faster. This is because the database also needs to insert or update index values when inserting or updating.

Two. Creation and deletion of indexes

Type of index:

• Unique (unique index): cannot appear the same value, can have a null value
• Index (normal index): Allows the same indexed content to appear
• Promary key (primary key index): does not allow the same value to appear
• Fulltext Index (full-text index): You can target a word in a value, but the efficiency is really not flattering.
• Composite index: Essentially Jianjian multiple words into an index, the combination of column values must be unique

(1) Use the ALTER TABLE statement to create a

Apply to the table after it is created and then add.

ALTER table name ADD index type (unique,primary key,fulltext,index) [index name] (field name)

Normal index ALTER TABLE table_name ADD index index_name (column_list);//UNIQUE index ALTER TABLE table_name add unique (column_list);// PRIMARY KEY index ALTER TABLE TABLE_NAME ADD PRIMARY key (column_list);

ALTER table can be used to create a normal index, a unique index, and a primary key index in 3 index formats,table_name is the name of the table to increase the index, andcolumn_list indicates which columns are indexed. Multiple columns are separated by commas. Index name index_name optional , by default, MySQL assigns a name based on the first indexed column. In addition, ALTER TABLE allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time.

(2) Add index to table using the CREATE INDEX statement

The CREATE index can be used to add a normal index or a unique index to a table, which can be used when building a table.

If it is a Char,varchar type, length can be less than the actual length of the field, and length must be specified if it is a blob and text type.

Create can only add these two indexes; 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 in the ALTER TABLE statement, and the index name is not selectable . In addition, the primary key index cannot be created with the CREATE INDEX statement .

(3) Deleting an index

Deleting an index can be accomplished by using the ALTER TABLE or the DROP INDEX statement. DROP index can be handled as a statement inside the ALTER TABLE, in the following format:

DROP INDEX index_name on TABLE_NAME; ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE table_name DROP PRIMARY key ;

In the preceding two statements, the index index_name in table_name is removed. In the last statement, it is only used in the Delete primary key index, because a table may have only one primary key index , so you do not need to specify the 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 removed from the table, the index is affected. For multiple-column combinations of indexes, if one of the columns is deleted, the column is also removed from the index. If you delete all the columns that make up the index, the entire index is deleted.

(4) Combined index and prefix index

It is to be noted here that the combination index and prefix index are a salutation to the indexing technique, not the type of the index. For a better presentation, create a demo table as follows.

CREATE TABLE User_demo (   ID                   int not null auto_increment comment ' primary key ',   login_name           varchar (+) NOT NULL Comment ' login name ',   PASSWORD             varchar (+) NOT null comment ' password ',                 city varchar (+) NOT null comment ' cities ',                  Age int. NOT NULL comment ' ages ',   sex                  int not null comment ' sex (0: Female 1: male) ',   primary key (ID));

To further extract the efficiency of MySQL, consider building a composite index that will be built into an index: login_name,city,age

When the table is built, the length of the login_name is 100, which is 16, because in general, the length of the name does not exceed 16, which speeds up the index query, reduces the size of the index file, and increases the insert,update update speed.

If you set up a single-column index for Login_name,city,age, so that the table has 3 single-column indexes, the efficiency of query and composite indexes is very different, even far below our combined index. Although there are three indexes at this time, MySQL can only use the one that it considers to be the most efficient single-column index, the other two are not used, that is, a full-table scan process.

Creating such a composite index is equivalent to establishing the following three combinations of indexes:

Login_name,city,agelogin_name,citylogin_name

Why is there no such combination index as city,age? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is that the combined index is used only from the far left, not as long as the query that contains the three columns will use it. That is, name_city_age (Login_name (), city,age) is indexed from left to right, and if there is no left front index, MySQL does not perform index queries .

If the index column length is too long, this column index will produce a large index file, not easy to operate, can be indexed using the prefix index, the prefix index should be controlled at a suitable point, control at 0.31 gold value (greater than this value can be created).

SELECT COUNT (DISTINCT ("title"))/count (*) from Arctic; --this value is greater than 0.31 to create the prefix index, distinct to repeat alter TABLE ' user ' ADD index ' uname ' (title (10)); --Increase the prefix index SQL, set the index of the person name to 10, which can reduce the index file size and speed up the index query.

Three. Use of the index and precautions

Explain can help developers analyze SQL problems, explain shows how MySQL uses indexes to process SELECT statements and join tables to help select better indexes and write more optimized query statements.

Using the method, add explain to the SELECT statement:

Explain SELECT * from user where id=1;

Try to avoid these non-indexed sql:

Select ' sname ' from ' Stu ' where ' age ' +10=30;--does not use the index because all indexed columns participate in the calculation of select ' Sname ' from ' Stu ' where left (' Date ', 4) <1990; --Do not use the index because the function operation is used, the same principle as the above select * from ' Houdunwang ' where ' uname ' like ' backing% '-Go to index SELECT * from ' Houdunwang ' where ' una Me ' like '% backing% '--do not go index--the regular expression does not use the index, which should be well understood, so why it is difficult to see the REGEXP keyword in SQL--string and numeric comparisons do not use indexes; CREATE TABLE ' A ' (' A ' char (10)); EXPLAIN select * from ' a ' where ' a ' = ' 1 '-Go to index EXPLAIN select * from ' a ' where ' a ' = 1--Do not go index SELECT * FROM dept WHERE dname= ' xxx ' or loc= ' xx ' or deptno=45--if there is or in the condition, it will not be used even if there is a conditional index. In other words, all the fields required for use must be indexed, and we recommend that you try to avoid using the OR keyword--if MySQL estimates that using a full table scan is faster than using an index, do not use the index

indexes are a lot of benefits, but an excessive use of indexes can have the opposite problem, and the index is flawed:
• Although the index greatly improves query speed, it reduces the speed of updating tables, such as insert,update and delete tables. Because when updating a table, MySQL not only saves the data, but also saves the index file
• Index files that create indexes that consume disk space. The general situation is not very serious, but if you are going to build multiple indexes on large tables, the index file will swell wide

Indexing is just one way to improve efficiency, and if MySQL has a large data size table, it takes time to study the optimal index, or to refine the query statement.

There are a few tricks when working with indexes:

1. The index will not contain NULL columns

As long as the column contains a null value, it will not be included in the index, and if one column contains a null value in the composite index, then that column is invalid for this conforming index.

2. Using a short index

Index A string, or specify a prefix length if you can. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

3. Index column sorting

The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. Therefore, the database default ordering can meet the requirements of the case do not use the sorting operation, try not to include more than one column of the sorting, if necessary to build a composite index of these columns.

4.like Statement Operations

It is generally discouraged to use the like operation, and if not, pay attention to the correct usage. Like '%aaa% ' does not use indexes, and like ' aaa% ' can use indexes.

5. Do not perform calculations on columns

6. Do not use not in, <>,! = operation, but <,<=,=,>,>=,between,in can be used to index the

7. The index is to be established on the field where the select operation is frequently performed.

This is because if these columns are seldom used, then there is no index that can significantly change the query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.

8. The index is to be built on a field that is unique in value comparison.

9. For columns that are defined as text, image, and bit data types, the index should not be incremented. Because the amount of data in these columns is either quite large or has very little value.

10. The columns that appear in the where and join need to be indexed.

The 11.where query condition has an equal sign (where column! = ...), and MySQL will not be able to use the index.

12. If a function is used in the query condition of the WHERE clause (for example: Where day (column) = ...), MySQL will not be able to use the index.

13. In a join operation (when data needs to be extracted from multiple data tables), MySQL can use the index only if it has the same data type as the primary key and foreign key , otherwise the index will not be used in a timely manner.

Using MySQL indexing tips and considerations