MySQL: Federated indexing and optimization

Naming rules: Table name _ Field name
1. Fields that need to be indexed, in the Where condition
2. Fields with low data need not be indexed
3, if the Where condition is an OR relationship, index does not work
4, accord with the left principle

https://segmentfault.com/q/1010000003984016/a-1020000003984281

A federated index is also called a composite index. For composite indexes: MySQL left-to-right uses fields from the index, and a query can use only one part of the index, but only the leftmost section. For example, the index is key index (A,B,C). Can support a | A, b| a,b,c 3 combinations to find, but B,c is not supported. The index is very effective when the leftmost field is a constant reference.

An index on two or more columns is called a composite index.
With additional columns in the index, you can narrow the scope of your search, but using an index with two columns differs from using two separate indexes. The structure of a composite index is similar to a phone book, where a person's name consists of a surname and a name, and the phone book is first sorted by last name, and then by name for people with the same last name. If you know the last name, the phone book will be useful, and if you know the first and last names, the phone book is more useful, but if you only know the first name, the phone book will be useless.
So when you create a composite index, you should carefully consider the order of the columns. A composite index is useful when you perform a search on all columns in an index or when you perform a search on only the first few columns, and the composite index is useless when you perform a search on any subsequent column.

http://blog.csdn.net/lmh12506/article/details/8879916

When a table has more than one index to go, Mysql chooses which index to follow based on the cost of the query statement, and it often calculates the first field (the leftmost one), which tends to go wrong. Such as:
Index Index_1 (create_time, category_id), Index_2 (category_id)

If there is a lot of data on a daily basis and there are many category, there will not be a lot of records for each category.

When the query SQL condition is select ... where create_time .... and category_id=. , it is possible not to walk the index index_1, while walking index index_2, resulting in a slow query.

The workaround is to swap the order of the indexed fields.

Http://www.cnblogs.com/krisy/archive/2013/07/12/3186258.html

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;

< Span lang= "en-US" > < Span lang= "en-US" >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= ' <???? 甼 difference??? >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 specific data will be a full table scan, if a table of large amounts of data and meet the results of a few, then non-indexed can cause a fatal performance degradation.
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 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.

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: Federated indexing and optimization