In database tables, index fields can greatly improve the query speed. Suppose we create a mytable table:
Create a table mytable( ID INT NOT NULL, user name varchar (16) not null); We randomly inserted 10000 records, including one: 5555, admin.
Find a record with the user name "admin”select * from my table, where the user name is" admin "; If the index has been established on username, MySQL can find the record accurately without any scanning. Instead, MySQL will scan all records, that is, query 10000 records.
Indexes are divided into single-column indexes and combined indexes. A single-column index means that an index contains only one column, and a table can have multiple single-column indexes, but this is not a composite index. Composite index, that is, an index contains multiple columns.
MySQL index types include:
(1) general index
This is the most basic indicator, and there is no limit. It can be created in the following ways:
◆ Create an index
Create an index on mytable(username(length)) If it is of CHAR and VARCHAR types, the length can be less than the actual length of the field; If it is of BLOB and TEXT types, the length must be specified, the same below.
◆ Modify the table structure
Alter my table add index [index name] on (user name (length))
◆ Specify directly when creating a table.
CREATE TABLE my TABLE(ID INT NOT NULL，username VARCHAR( 16)NOT NULL，INDEX[INDEX name](username(length))；
Syntax for deleting an index:
Delete the index [indexName] on mytable;
(2) Unique index
It is similar to the previous ordinary index, except that the values of the index columns must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:
◆ Create an index
Create a unique index indexName (user name (length)) on mytable.
◆ Modify the table structure
Alter my table add unique [index name] on (user name (length))
◆ Specify directly when creating a table.
Create the table mytable( ID INT NOT NULL, Username Varchar (16) not null, Unique [index name] (username (length));
(3) Primary key index
This is a special unique index, and null values are not allowed. Usually, the primary key index is created at the same time as the table is built:
Create a table mytable( ID INT NOT NULL, user name VARCHAR( 16) NOT NULL, primary key (ID))；); You can also use the ALTER command. Remember: a table can only have one primary key.
(4) Comprehensive index
To visually compare a single-column index with a combined index, add multiple fields to the table:
Create a table mytable( ID INT is not empty, user name VARCHAR( 16) is not empty, city VARCHAR(50) is not empty, and age INT is not empty); In order to further extract the efficiency of MySQL, it is necessary to consider establishing a combined index. Is to build an index of names, cities and ages:
ALTER TABLE mytable adds index name_city_age (name( 10), city, age); When building the table, the length of usernname is 16, and 10 is used here. This is because in general, the length of the name will not exceed 10, which will speed up the index query, reduce the size of the index file, and improve the update speed of insertion.
If the single-column indexes are established on usernname, city and age respectively, so that the table has three single-column indexes, the query efficiency will be very different from the above combined indexes, far lower than our combined indexes. Although there are three indexes at this time, MySQL can only use the single-column index that it thinks seems to be the most efficient.
Establishing such a combination index is actually equivalent to establishing the following three groups of combination indexes respectively:
Why don't username, city, age usernname and city usernname have a combined index such as city and age? Is this because of MySQL composite index? The leftmost prefix? Result. The simple understanding is to combine only from the left. Not all queries containing these three columns will use the composite index, but the following SQL will use the composite index:
Select * from my table why username = "admin" and city = "Zhengzhou"
SELECT * FROM my table WHREE username = " admin "
And the following will not be used:
Select * Why age = 20 and city = "Zhengzhou" from my table.
Select * from my table why city = "Zhengzhou"
For example, there is a statement: select * from users where area=? Beijing? And age = 22;
If we create a single index on area and age respectively, mysql query can only use one index at a time, so although this is much more efficient than full table scanning without index, it will be more efficient if we create a composite index on area and age columns.
If we create a compound index (area, age and salary), it is actually equivalent to creating three indexes (area, age and salary), (area, age) and (area). This is called? Best left prefix? Features. So when we create a composite index, we should put the most commonly used columns as restrictions on the left, and then decrease them in turn.
(5) the timing of establishing the index
We learned to build an index here, so under what circumstances do we need to build an index? Generally speaking, columns that appear in WHERE and JOIN need to be indexed, but this is not the case, because MySQL is only used for.
Select t.name from my table left JOIN my table m on t.name = m.username, where m.age = 20, m.city =' Zhengzhou'. At this time, you need to index city and age, and you also need to index userame of my table, because it also appears in the join clause.
Just mentioned that only likes need to be indexed at a specific time. Because MySQL does not use indexes when making queries that start with wildcards% and _. For example, the following sentence uses an index:
SELECT * FROM mytable, where the user name is "admin%"
And the next sentence is not needed:
Select * from my table when name LIKE'% admin' Therefore, we should pay attention to the above differences when using like.
Indexes are especially important for applications where queries dominate. Many times, the performance problem is simply because you forgot to add an index, or you didn't add a more effective index. If you don't add
Index, then finding any or even a specific piece of data will scan the whole table. If a table has a lot of data and few qualified results, then not indexing will lead to fatal performance.
Put it down. However, it is not necessary to build an index in every case. For example, gender may have only two values. Indexing not only has no advantages, but also affects the update speed. This is called the transition index.
(6) the shortcomings of the index
All of the above have talked about the benefits of using indexes, but too much use of indexes will lead to abuse. Therefore, the index will also have its shortcomings:
Although the index greatly improves the query speed, it will also slow down the speed of updating tables, such as inserting, updating and deleting tables. Because when updating the table, MySQL should not only save the data, but also save the index file.
◆ It will occupy disk space when indexing files. Generally speaking, this problem is not too serious, but if you create multiple combined indexes on a large table, the index file will expand rapidly.
Indexing is only one factor to improve efficiency. If your MySQL has a large number of tables, you need to spend time researching and building the best index or optimizing query statements.
(7) Matters needing attention in using indicators
There are some tips and precautions when using indexes:
◆ The index will not contain columns with null values.
As long as the column contains null values, it will not be included in the index. As long as a column in a composite index contains null values, the column is invalid for the composite index. So we should not let the default value of the field be NULL when designing the database.
◆ Use a short index.
If possible, index the string column and specify the prefix length. For example, if there is a column with CHAR(255), if the multivalue is unique within the first 10 or 20 characters, do not index the whole column. Short index can not only improve the query speed, but also save disk space and I/O operation.
◆ Index column sorting
Mysql queries can only use one index at a time, so if the index is already used in the where clause, the columns in order by will not use it. Therefore, when the default sorting of the database can meet the requirements, do not use sorting operation; Try not to include sorting of multiple columns, and if necessary, it is best to create a composite index for these columns.
◆like statement operation
Generally speaking, the like operation is not encouraged. If necessary, how to use it is also a problem. Like what? %aaa%？ Can't use the index, like it? aaa%？ You can use the index.
◆ Do not perform operations on columns.
select * from users where YEAR(add date)& lt； 2007; This operation will be performed on each row, which will invalidate the index and scan the whole table, so we can change it to:
select * from users where adddate & lt； ? 2007-0 1-0 1? ;
◆ Don't use not in and.
The above introduces the index types of MySQL.
######################################################################################################################
Primary key of table
Automatically create a unique index
For example, hbs_bh (user identification number) in zl_yhjbqk (user basic information).
Field unique constraint of table
ORACLE uses indexes to ensure data integrity.
Such as lc_bh+hj_sx (process number+link order) in lc_hj (process link).
Fields of direct conditional query
Fields used for constraints in SQL
For example, qc_bh (area book number) in zl_yhjbqk (user basic information).
Select * from zl_yhjbqk where qc _ bh = & lt2000 and QC _ BH>；; =5000;
Fields associated with other tables in the query
Fields usually establish foreign key relationships.
Such as jldb_bh (metering point table number) in zl_ydcf (power consumption component).
Select * from zl_ydcf a, zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=? 540 1002 145 1 1?
Fields sorted in the query
If the sorted fields are accessed through the index, the sorting speed will be greatly improved.
Select * from ZL _ yhjbqk order by QC _ BH (build QC _ BH index)
Select * from zl_yhjbqk where qc_bh=? 700 1? Sort by cb_sx (create a composite index of qc_bh+cb_sx, note: it is only an index, including qc_bh and cb_sx fields).
Fields used 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 we not build or build fewer indexes?
There are too few table records.
If a table has only five records, and the records are accessed by index, then you need to access the index table first, and then access the data table through the index table. Usually, the index table and the data table are not in the same data block. In this case, ORACLE must read the data block back and forth at least twice. Without an index, ORACLE will read all the data at once, and the processing speed will be obviously faster than that with an index.
For example, the table zl_sybm (using department) generally has only a few records, and indexing any field except the primary key will not produce performance optimization. In fact, if a table is statistically analyzed, ORACLE will not use your index, but will automatically perform full table access. For example:
Select * from zl_sybm where sydw_bh=? 540 1? (Index sydw_bh does not lead to performance optimization)
Tables that are frequently inserted, deleted and modified.
For some business tables that are often processed, try to reduce the index as much as possible when the query allows, such as zl_yhbm, gc_dfss, gc_dfys, gc_fpdy and other business tables.
Table fields with repeated and evenly distributed data (such as gender fields)
If a table has 654.38 million rows of records, and a field A has only two values of T and F, and the distribution probability of each value is about 50%, then indexing the field A of this table will generally not improve the query speed of the database.
A table field that is usually queried with the main field, but has more index values of the main field.
For example, gc_dfss (paid-in electricity fee) table often inquires about a certain amount according to the charging serial number, user identification number, meter reading date, year and month of electricity fee occurrence and operation sign. If all fields are built in the index, it will increase the time for data modification, insertion and deletion. In fact, if you index a payment according to the charging serial number, it will reduce the number of records to only a few, and if you index according to the following fields, it will not produce much performance.
The problem of establishing index in MySQL database with tens of millions of levels and the method of improving performance
First, matters needing attention:
First of all, you should consider whether table space and disk space are enough. We know that an index is also a kind of data, which will inevitably occupy a lot of table space when building an index. Therefore, when indexing large tables, space capacity should be considered first.
Secondly, to lock the table when indexing, it should be noted that the operation should be carried out when the business is idle.
Second, the performance adjustment:
The first thing to consider is disk I/O. In fact, you should try to distribute the index and data on different disks (regardless of the array). Logically speaking, the data table space is separate from the index table space. This is the basic principle that should be followed when establishing an index.
Secondly, we know that we need to scan the whole table when building an index, so we should consider increasing the value of initialization parameter db_file_multiblock_read_count. Usually set to 32 or higher.
Thirdly, besides scanning the whole table, a large number of sorting operations are needed to establish the index, so the size of the sorting area should be adjusted.
Before 9i, the size of sort_area_size can be increased at the session level, for example, it can be set to 100m or more.
After 9i, if the value of the initialization parameter workarea_size_policy is TRUE, the sorting area is automatically allocated from pga_aggregate_target.
Finally, when creating an index, you can add the nologging option. In order to reduce a lot of redo in the indexing process and improve the execution speed.
######################################################################################################################
In general, the following fields must be indexed:
1. Foreign key fields of related queries should be indexed.
2. Fields to be sorted (put in the order method)
3. Fields to be queried (put in the where method)
4. Fields to be grouped (placed in the grouping method)
Write less, read more, build more indexes, write more, read less, build less indexes, and build less data without indexes.
Designing MySql index can make your database fly and greatly improve the efficiency of the database.
How to establish MYSQL database index correctly
Label: