How to correctly establish the MySQL database index

Indexing is the key to fast searching. MySQL indexing is important for the efficient operation of MySQL. Here are a few common types of MySQL indexes.

In a database table, indexing a field can greatly improve query speed. Suppose we create a mytable table:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL); We randomly inserted 10,000 records, including one: 5555, admin.

In the Find username= "admin" record select * from MyTable WHERE username= ' admin '; If an index has been established on username, MySQL does not need any scanning, that is, the record can be found exactly. Instead, MySQL scans all records, that is, to query 10,000 records.

Index sub-column indexes and composite indexes. A single-column index, that is, an index contains only single columns, and a table can have multiple single-row indexes, but this is not a composite index. A composite index, that is, an index that contains multiple columns.

MySQL index types include:

(1) General index

This is the most basic index and it has no limitations. It is created in the following ways:

Create an index

CREATE INDEX indexname on mytable (username (length)); If it is a Char,varchar type, length can be less than the actual length of the field, and if it is a blob and text type, length must be specified.

Modify Table Structure

ALTER mytable ADD INDEX [IndexName] on (username (length))

Specify directly when creating a table

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, INDEX [IndexName] (username (length)));

Syntax for dropping an index:

DROP INDEX [IndexName] on mytable;

(2) Unique index

It is similar to the previous normal index, except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:

Create an index

CREATE UNIQUE INDEX indexname on mytable (username (length))

Modify Table Structure

ALTER mytable ADD UNIQUE [IndexName] on (username (length))

Specify directly when creating a table

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, UNIQUE [IndexName] (username (length)));

(3) Primary key index

It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built:

CREATE TABLE mytable (ID INT not NULL, username VARCHAR (+) NOT NULL, PRIMARY KEY (id)); Of course, you can also use the ALTER command. Remember: A table can have only one primary key.

(4) Combined index

To visually compare single-column and composite indexes, add multiple fields to the table:

CREATE TABLE mytable (ID int NOT NULL, username varchar (+) NOT NULL, City VARCHAR (+) NOT NULL, age INT NOT NULL  ); To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index:

ALTER TABLE mytable ADD INDEX name_city_age (name (ten), city,age); When the table is built, the usernname length is 16, which is used here in 10. This is because, in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.

If you set up a single-column index on Usernname,city,age, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient.

The establishment of such a composite index, in fact, is equivalent to the following three sets of composite indexes:

Usernname,city,age usernname,city Usernname Why not city,age such a combination index? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not as long as the combined index is used for queries that contain these three columns, the following SQL uses this combined index:

SELECT * FROM MyTable whree username= "admin" and city= "Zhengzhou"

SELECT * FROM MyTable whree username= "admin"

And the next few are not used:

SELECT * FROM MyTable whree age=20 and city= "Zhengzhou"

SELECT * FROM MyTable whree city= "Zhengzhou"

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, the MySQL query could use only one index at a time, so while the full table scan was a lot more efficient when it was relatively non-indexed, it would be more efficient to create a composite index on the area or age two column.

If we create a composite index (area, age,salary), it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the "best left prefix" attribute. Therefore, when creating a composite index, the columns that are most commonly used as constraints should be placed on the leftmost, decreasing in turn.

(5) Timing of index creation

Here we have learned to build an index, so where do we need to build the index? In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like. For example:

SELECT t.name from MyTable T left joins MyTable m on T.name=m.username WHERE m.age=20 and m.city= ' Zhengzhou ' This time you need to establish a cable Because the userame of the MyTable table also appears in the join clause, it is necessary to index it.

Just now it is only necessary to index the like at certain times. Because MySQL does not use indexes when querying with wildcards% and _. For example, the following sentence will use the index:

SELECT * FROM MyTable WHERE username like ' admin% '

And the following sentence will not be used:

SELECT * FROM MyTable wheret Name like '%admin ' Therefore, you should pay attention to the above differences when using like.

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 have only two values, index not only has no advantage, but also affect the speed of the update, which is called the transition index.

(6) Deficiencies of the index

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. So the index has its drawbacks as well:

Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on 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 create multiple combinations of indexes on a large table, the index file will swell up quickly.

Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements.

(7) Considerations for using Indexes

There are some tips and considerations when working with indexes:

The index does not contain a column 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.

Use 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.

Indexed column Sorting

MySQL queries can use only one index at a time, 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.

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 indexes, while like "aaa%" can use indexes.

Do not perform calculations on columns

SELECT * from the users where year (adddate) <2007; The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to:

SELECT * from users where adddate< ' 2007-01-01 ';

Do not use not in and <> operations

Above, the MySQL index type is introduced.

############################################################################################################### #######

Primary key of the 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=<2000 and qc_bh>=5000;

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 composite 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 key to any one word Jianjian index will not produce performance optimization, in fact, if the table is statistically analyzed, Oracle will not use your index, but instead 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 where data is duplicated and distributed evenly (for example: Gender field)

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 based on the number of charges, user identification number, meter reading date, electricity charges occurred years, operating signs to specifically inquire about the situation of a collection, 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.

############################################################################################################### #######

In general, the following fields must be remembered as indexed:

1, the Foreign key field of the associated query to be indexed (Foreign key) 2, the column will be sorted (placed in the order method) 3, the field will be queried (in the Where method) 4, the group will be the field (is placed in the group method)

Write less read more to build indexes, write more read less to build indexes, less data will not build index.

The design of MySQL index can make your database fly up, greatly improve the efficiency of the database.

How to correctly establish the MySQL database index