MySQL index creation and use

Turn from "http://www.cnblogs.com/mywebname/articles/555696.html"

First, the concept of the index
An index is a way to speed up the retrieval of data in a table. The index of the database is similar to the index of the book. In books, the index allows users to quickly find the information they need without having to scroll through the entire book. In the database, the index also allows the database program to quickly locate the data in the table without having to scan the entire database.

Second, the characteristics of the index
1. Indexes can speed up the retrieval of databases
2. Indexing reduces the speed of maintenance tasks such as database insertions, modifications, and deletions
3. The index is created on the table and cannot be created on the view
4. The index can be created either directly or indirectly
5. You can use the index in the optimization hide
6. Execute SQL statements using the query processor, on one table, with only one index at a time
7. Other

Third, the advantages of the index
1. Create a unique index that guarantees the uniqueness of each row of data in a database table
2. Greatly accelerate the retrieval speed of data, which is the main reason for creating indexes
3. Accelerating the connection between tables and tables, particularly with regard to the realization of the referential integrity of data.
4. When using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.
5. By using an index, you can improve the performance of your system by using an optimized hidden device in the process of querying.

Iv. Disadvantages of the index
1. It takes time to create indexes and maintain indexes, and this time increases with the amount of data
2. The index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space required will be greater
3. When the data in the table is added, deleted and modified, the index should be maintained dynamically, reducing the maintenance speed of the data.

V. Classification of indexes
1. Creating indexes directly and indirectly creating indexes
Creating indexes directly: Create INDEX mycolumn_index on MyTable (myclumn)
Indirectly create indexes: Define primary KEY constraints or uniqueness key constraints, you can create indexes indirectly
2. General index and Uniqueness Index
Normal index: CREATE index mycolumn_index on mytable (myclumn)
Uniqueness Index: guarantees that all data in the indexed column is unique and can be used for both clustered and non-clustered indexes
CREATE UNIQUE coustered INDEX myclumn_cindex on MyTable (MyColumn)
3. Single index and composite index
Single index: That is, non-composite index
Composite index: Also called a composite index, contains multiple field names in the index statement, up to 16 fields
CREATE INDEX Name_index on username (firstname,lastname)
4. Clustered and non-clustered indexes (cluster index, clustered index)
Clustered index: Physical index, the same as the physical order of the base table, the order of the data values is always sorted in order
CREATE CLUSTERED INDEX mycolumn_cindex on MyTable (MyColumn) with
Allow_dup_row (allows clustered index with duplicate records)
Non-clustered index: CREATE unclustered index Mycolumn_cindex on mytable (MyColumn)

Vi. Use of indexes
1. When the field data Update frequency is low, the query is used more frequently and there are a large number of duplicate values is recommended to use the clustered index
2. Multiple columns are frequently accessed simultaneously, and each column contains duplicate values to consider a composite index
3. The leading column of the composite index must be well controlled, otherwise the effect of the index cannot be played. The composite index is not used if the leading column is not in the query condition when queried. The leading column must be the most frequently used column
4. Multi-table operations before they are actually executed, the query optimizer lists a number of possible connection scenarios based on the connection criteria and finds the best solution with the lowest system overhead. Join conditions to fully consider the table with the index, the number of rows of tables; The selection of the internal and external tables can be determined by the formula: number of matching rows in the outer table * the number of times each lookup in the inner-level table, the product is minimized as the best scheme
Any action on a column in a 5.where clause results in a column-wise calculation at the SQL runtime, so it has to perform a table search without using the index above the column, and if the results are available at query compile time, it can be optimized by the SQL optimizer, using the index, and avoiding table searches ( Example: SELECT * from record where substring (card_no,1,4) = ' 5378 '
&& SELECT * from the record where card_no like ' 5,378% ') Any action on a column will cause a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.
The ' in ' is logically equivalent to ' or ' in the 6.where condition, so the parser converts in (' 0 ', ' 1 ') to column= ' 0 ' or column= ' 1 ' to execute. We expect it to look for each or clause separately, and then add the result so that it can take advantage of the index on column, but in fact it takes an "or policy", that is, the row that satisfies each or clause is first taken out of the worksheet in the staging database, and a unique index is created to remove the duplicate rows. Finally, the result is calculated from this temporary table. Therefore, the actual process does not take advantage of the column index, and the completion time is affected by the tempdb database performance. The IN, or clauses often use worksheets to invalidate the index, and if you do not produce a large number of duplicate values, consider taking the sentence apart; the disassembled clause should contain an index
7. To be good at using stored procedures, it makes SQL more flexible and efficient


Index usage principles:

1. Use the index to traverse the table more quickly.
The index established by default is a non-clustered index, but sometimes it is not optimal. Under a non-clustered index, the data is physically randomly stored on the data page. A reasonable index design should be based on
On the analysis and prediction of various queries. Generally speaking:
A. There are a large number of duplicate values, and often have a range of queries (>,<,> =,< =) and order BY, group by occurrences of the column, you can test
The cluster index is established;
B. Frequent simultaneous access to multiple columns, and each column contains duplicate values to consider the establishment of a composite index;
C. Composite indexes to make the key query as much as possible to form an index overlay, its leading column must be the most frequently used column. Indexes can help improve performance but not as many indexes as possible, but too many indexes in the opposite direction cause the system to be inefficient. Each index is added to the table, and maintenance of the index collection will be done with the corresponding update work.
2, in the vast number of queries as far as possible to use the format conversion.
3, order BY and Gropu by using the order by and the group by phrase, any index helps to improve the performance of select.
7. Any action on a column will cause a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.
4, IN, or clauses often use worksheets to invalidate the index. If you do not produce a large number of duplicate values, you can consider taking the sentence apart. The disassembled clause should contain an index.

optimization principle of SQL 2:
1. Use smaller data types whenever possible to meet your needs: for example, using mediumint instead of int
2, try to set all the columns to NOT NULL, if you want to save null, manually set it, rather than set it as the default value.
3. Use varchar, TEXT, blob type as little as possible
4. If your data is only a few of the few you know. It is best to use the enum type
5, as Graymice said, to build an index.
Here's an experiment I've done to find that indexes can greatly improve the efficiency of queries:

I have a membership information Form users, which has 37,365 user records:

Query when not indexed:
SQL statement A:
SELECT * from the users where username like '% Xu ';
8 queries in Mysql-front: 1.40,0.54,0.54,0.54,0.53,0.55,0.54 960 records found

SQL Statement B:
SELECT * from the users where username like ' Xu% ';
8 queries in Mysql-front: 0.53,0.53,0.53,0.54,0.53,0.53,0.54,0.54 836 Records found

SQL statement C:
SELECT * from the users where username like '% Xu ';
8 queries in Mysql-front: 0.51,0.51,0.52,0.52,0.51,0.51,0.52,0.51 7 Records found

To add an index to the username column:
Create index Usernameindex on users (username (6));

Query again:
SQL statement A:
SELECT * from the users where username like '% Xu ';
8 queries in Mysql-front: 0.35,0.34,0.34,0.35,0.34,0.34,0.35,0.34 960 records found

SQL Statement B:
SELECT * from the users where username like ' Xu% ';
8 queries in Mysql-front: 0.06,0.07,0.07,0.07,0.07,0.07,0.06,0.06 836 Records found

SQL statement C:
SELECT * from the users where username like '% Xu ';
8 queries in Mysql-front: 0.32,0.31,0.31,0.32,0.31,0.32,0.31,0.31 7 Records found

In the experimental process, I did not open any program, the above data shows that in a single table query, the index can greatly improve the query speed.
Another thing to say is that if an index is established, the speed increase is most noticeable for queries like '% ' type. Therefore, we also try to query this way when we write SQL statements.

For multi-table queries, our optimization principles are:

Try to establish the index in: Left JOIN On/right join on ... + Condition, on the field that is involved in the conditional statement.

Multi-table queries can better reflect the advantages of indexes than single-table queries.

6, the establishment of the index principle:
If the prefix value of the data in a column is very small, we'd better just index the prefix. MySQL supports this index. The index method I used above is to index the leftmost 6 characters of username. The shorter the index, the more

The smaller the disk space, the less time is spent in the retrieval process. This method can index up to 255 characters in the left.

On many occasions, we can build multiple columns of data to index.

The index should be based on the fields that are compared in the query criteria, not on the fields we want to find and display

7, limit the use of the index to avoid the return.
7.1 In, or clauses often use worksheets to invalidate indexes.
If you do not produce a large number of duplicate values, you can consider taking the sentence apart. The disassembled clause should contain an index. How to solve this sentence, please give an example

The

example is as follows:  
If an index is established on both FIELDS1 and FIELDS2, the FIELDS1 is the primary index  
The following SQL uses the index  
SELECT * from tablename1 where fields1= ' value1 ' and fields2= ' value2 '  
The following SQL does not use the index  
Select * from tablename1 where fields1= ' Value1 ' or fields2= ' value2 '  
7.2 Using is null or was NOT NULL
         Using is null or is not NULL also restricts the use of the index. Because null values are not defined. Using NULL in an SQL statement can be a lot of trouble. Therefore, it is suggested that the      personnel should be set to not NULL when the table is being built. If the indexed column has a null value in some rows, the index is not used (unless the index is a bitmap index and the bitmap index is discussed later in detail).

7.3 Using a function
If you do not use a function-based index, the optimizer ignores these indexes when you use functions in the WHERE clause of the SQL statement for the column that has the index. The following query does not use an index (as long as it is not a function-based index)
   select Empno,ename,deptno
           from   EMP
          where  Trunc (hiredate) = ' 01-may-81 ';
          Change the above statement to the following statement so that it can be searched by index.
          Select Empno,ename,deptno
           from   EMP
          where  hiredate< (to_date (' 01-may-81 ') +0.9999);

7.4 Comparing mismatched data types
Comparing mismatched data types is also one of the more difficult to find performance issues. Note The following query example, Account_number is a VARCHAR2 type and has an index on the Account_number field. The following statement performs a full table scan.
         Select Bank_name,address,city,state,zip
          from   Banks
         where  account_number = 990354;
         Oracle can automatically turn the WHERE clause into to_number (account_number) = 990354, this restricts the use of the index, change to the following query can use the index:
         Select Bank_name,address, City,state,zip
         from   Banks
          where  account_number = ' 990354 ';
     Special Note: Comparisons between mismatched data types will allow Oracle to automatically limit the use of indexes, even if the query executes explain plan does not let you understand why a                 "full table Scan".

Add:
1. The index results in a significant increase in the speed of the query, but the index also takes up additional hard disk space (which is not a problem with normal hard disk space, of course), and it also takes some time for the index to update when inserting new records into the table.
Some tables do not have to be indexed if they are frequently insert and fewer select. Otherwise, every time the data is written, the index will be rewritten, and it takes a while.
This depends on the actual situation, and usually the index is required.
2. When I have doubts about query efficiency, I usually use MySQL explain to track the query situation.
When you compare the Mysql-front with the length of time, I think it is more accurate to write the number of fields scanned from the query.

MySQL index creation and use