MySQL CREATE index

Excerpt from: HTTP://BLOG.CHINAUNIX.NET/UID-25063573-ID-3032578.HTML1. Index function

In addition to the ordered lookups mentioned above, the database uses a variety of fast location techniques to greatly improve the query efficiency. In particular, when the amount of data is very large and the query involves more than one table, using an index can often speed up the query by tens of thousands of times.

For example, there are 3 unindexed tables T1,T2,T3, each containing only columns C1, c2, andC3, each containing 1000 rows of data, referring to the value of 1~ 1000, respectively. The query that finds the equivalent row of values is shown below.

SELECT c1,c2,c3 from T1,t2,t3 WHERE c1=c2 and C1=C3

The result of this query should be 1000 rows with 3 equal values per row. To process this query without indexing, you must look for all the combinations of 3 tables in order to derive those rows that match the WHERE clause. and the number of possible combinations is 1000x1000x1000 (1 billion), obviously the query will be very slow.

If you index each table, you can greatly speed up the query process. Queries using the index are handled as follows.

(1) Select the first row from the table T1 to see the data that this row contains.

(2) Use the index on table T2 to directly locate the row in the T2 that matches the value of t1. Similarly, use the index on table T3 to directly locate rows in T3 that match the values from T1.

(3) scan the next line of the table T1 and repeat the previous procedure until all the rows in the T1 are traversed.

In this case, a full scan is still performed on table T1, but the ability to index lookups on tables T2 and T3 directly takes rows from those tables, 1 million times times faster than unused indexes.

Using the index,MySQL accelerates the search where the clause satisfies the criteria row, while in a multi-table join query, it speeds up matching rows in other tables when the connection is executed.

2. Create an index

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

1. ALTER TABLE

ALTER table is used to create a normal index, aunique 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 to index, and 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. CREATE INDEX

CREATE Index to add a normal or 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 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. Index type

When you create an index, you can specify whether the index can contain duplicate values. If not included, the index should be created as a PRIMARY key or a unique index. For single-column uniqueness indexes, this guarantees that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is guaranteed to be distinct.

The PRIMARY key index is very similar to a unique index. In fact, thePRIMARY key index is only a unique index with the name PRIMARY . This means that a table can contain only one PRIMARY KEY because it is not possible to have two indexes with the same name in a table.

The following SQL statement adds a PRIMARY key index to the students table on the SID .

ALTER TABLE Students ADD PRIMARY KEY (SID)

4. Deleting an index

You can use the ALTER table or DROP INDEX statement to delete an index. Similar to the CREATE index statement,DROP index can be handled as a statement inside ALTER table, with the following syntax.

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 in table_name .

The 3 statement is only used when deleting the PRIMARY key index, because a table may 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 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.

5. View Index

Mysql> Show index from Tblname;

Mysql> show keys from Tblname;

· Table

The name of the table.

· Non_unique

0 if the index cannot include a repeating word. 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

The column is stored in the index in what way. 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. You can update by running analyze table or myisamchk-a. The cardinality is counted according to the statistics 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 is federated.

· Sub_part

The number of characters that are 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 indexed methods (BTREE, Fulltext, HASH, RTREE).

· Comment

6. Where to use the index
primary key for table

Automatically create a unique index

HBS_BH (Household identification number) in Zl_yhjbqk (user base)

Field UNIQUE constraint for table

Oracle leverages indexes to ensure data integrity

such as LC_HJ (process link) in the LC_BH+HJ_SX (flow number + link order)

fields for direct criteria queries

Fields used in SQL for conditional constraints

QC_BH (Area book number) in Zl_yhjbqk (user base)

SELECT * from zl_yhjbqk where qc_bh= ' 7001 '

Fields associated with other tables in the query

Field often establishes a foreign key relationship

such as ZL_YDCF (electrical components) in the JLDB_BH (Metering point table number)

SELECT * from ZL_YDCF a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh= ' 540100214511 '

Fields sorted in the query

Sorted fields if accessed through the index, that will greatly improve the sorting speed

SELECT * from Zl_yhjbqk ORDER by QC_BH (build QC_BH index)

SELECT * from zl_yhjbqk where qc_bh= ' 7001 ' ORDER by CB_SX (build QC_BH+CB_SX Index, note: Just an index, which includes QC_BH and CB_SX fields)

fields for statistics or grouping statistics in a query

Select Max (HBS_BH) from Zl_yhjbqk

Select Qc_bh,count (*) from ZL_YHJBQK GROUP by QC_BH

under what circumstances should not be built or less indexed

Too few table records

If a table has only 5 records, using an index to access the records, the first need to access the index table, and then through the Index table access to the table, the General Index table and the data table is not the same data block, in this case, Oracle at least a round trip to read the data block two times. Without an index, Oracle will read all the data at once, and the processing speed will obviously be faster than the index.

such as table ZL_SYBM (use department) generally only a few records, in addition to the primary keyword on any one word Jianjian index will not produce performance optimization, in fact, if the table is statistically analyzed by the Oracle will not use your index, but instead of automatically perform full table access. Such as:

SELECT * from ZL_SYBM where sydw_bh= ' 5401 ' (indexing of SYDW_BH does not result in performance optimizations)

Tables that are frequently inserted, deleted, modified

For some business tables that are frequently processed, you should minimize the indexes, such as Zl_yhbm,gc_dfss,gc_dfys,gc_fpdy, as the query allows.

Table fields with repeated data and distributed averages

If a table has 100,000 rows of records, a field A has only T and F two values, and the probability of distribution of each value is approximately 50%, then the Jianjian index of the table A is generally not improving the query speed of the database.

Table fields that are frequently queried with the main field but larger than the primary field index value

If the GC_DFSS (electricity charges) table is often in accordance with the number of charges, household identification number, meter reading date, electricity charges occurred years, operating signs to specifically query the situation of a certain payment, if all the fields are built in an index that will increase the data modification, insertion, deletion time, From the actual analysis of a collection if the record is reduced to only a few by the charge ordinal index, then the query will not have much effect on performance if you press the next few field index queries.

Indexing of Tens MySQL database and the means to raise high performance

First, the matters needing attention:

First, you should consider whether tablespace and disk space are sufficient. We know that the index is also a kind of data, when the index is set to occupy a large number of table space. Therefore, the first thing to consider when indexing a large table is the problem of spatial capacity.

Secondly, the table should be locked when the index is built, so be aware that the operation should be done when the business is idle.

Second, performance adjustment aspects:

The first factor to consider is disk I/O. Physically, you should try to spread the index and data to different disks (regardless of the pattern). Logically, the data table space is separated from the index table space. This is the basic guideline that should be followed when indexing is under construction.

Second, we know that the table should be scanned for the full table when indexing, so we should consider the value of Db_file_multiblock_read_count the initialization parameter. The general setting is 32 or greater.

Again, the index should be adjusted to the size of the sorting area, except for a full table scan and also to perform a large number of sorting operations on the data.

Before 9i, you can increase the size of the sort_area_size at the session level, for example, set to 100m or larger.

After 9i, if the value of the initialization parameter workarea_size_policy is true, the sort area is automatically assigned from Pga_aggregate_target.

Finally, when indexing is established, you can add the nologging option. To reduce the number of redo generated during the indexing process, thus increasing the speed of execution.

MySQL issues to be aware of when establishing index optimization

The design of MySQL index can make your database fly up, greatly improve the efficiency of the database. There are a few things to note when designing MySQL indexes:

1, creating an index

Indexing is especially important for queries that are the primary application. A lot of times the performance problem is simply because we forgot to add an index, or we didn't add a more efficient index. If you do not add

Index, then find any even a particular piece of data will be a full table scan, if a table of large amounts of data and meet the results of the condition is very small, then not indexed can cause fatal performance

Drop. However, it is not always possible to index, for example, gender may be only two values, index not only have no advantage, but also affect the speed of the update, which is called over-index.

2, composite Index

For example, there is a statement like this: SELECT * from users where area= ' Beijing ' and age=22;

If we were to create a single index on area and age, because the MySQL query can only use one index at a time, even though this has been relatively non-indexed, the full table scan has improved a lot

Rate, but if you create a composite index on the area and age two columns, it will be more efficient. If we create a (area, age,

Salary), then it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix

Characteristics. Therefore, when creating a composite index, the columns that are most commonly used as constraints should be placed on the leftmost, decreasing in turn.

3, 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 there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.

4, using a short index

Index A string, or specify a prefix length if possible. 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.

5, sort the index problem

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. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.

6,like Statement Operations

It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.

7, do not perform calculations on columns

SELECT * from Users where

Year (adddate)

8, do not use not in and operation

None in and operations do not use the index to perform a full table scan. Not in can be replaced by not exists, ID3 can use id>3 or ID

MySQL CREATE index