In database tables, index fields can greatly improve the query speed. Suppose we create a mytable table:
Create a table mytable( IDINTNOT NULL, user name VARCHAR( 16) NOT NULL.
); We randomly inserted 10000 records, including one: 5555, admin.
Find the record username="admin "SELECT * from my table, where.
Username =' admin If an index has been established on Username, MySQL can find records 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, a cable 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 INDEXindexName (user name (length)) on mytable;
If it is of type CHAR and VARCHAR, 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 Addindex [index name] on (user name (length))
◆ Specify directly when creating a table.
Create a table mytable( IDINTNOT NULL, user name VARCHAR( 16) NOT NULL,
INDEX [indexName] (user name (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 a table mytable( IDINTNOT NULL, user name VARCHAR( 16) NOT NULL,
UNIQUE [indexName] (user name (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 NOT NULL, user name VARCHAR( 16) NOT NULL,
City VARCHAR(50) is not empty, 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 my table addindex name _ city _ age (name (10), city, age);
When building a table, the length of usernname is 16, which is used here.
10。 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 they are
Establish a single-column index on usernname, city, age, so that the table has three single-column indexes, and the query efficiency will be very different from the above-mentioned combined index, far lower than our combined index. 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:
User name, city, age user name, city user name why not?
What about combined indexes like 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 mytable WHREE username="admin "and the following contents will not be used:
Select * from my table why age = 20 and city = "Zhengzhou" select * from my table why.
City= "Zhengzhou"
(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 t LEFT join my table m ON t . Name = m . username
Where m.age = 20 and m.city =' Zhengzhou'
At this time, you need to index city and age, and you also need to index userame of mytable table 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 my table, where the user name "admin%" and the following sentence will not be used:
Select * from my table when name LIKE'% admin' Therefore, we should pay attention to the above differences when using like.
(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 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 use only one index, so if an index is already used in the where clause, then order
Columns in by will not use indexes. 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%？ Such as not using an index.
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 cause the index to be invalid and the whole table will be scanned, 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.
30 Experiences of Improving SQL Query Optimization with mysql Ten Million Big Data (Notes on Mysql Index Optimization)
1. In order to optimize the query, we should avoid scanning the whole table as much as possible. First, we should consider building indexes on the columns involved in where and order by.
2. Try to avoid judging the null value of the field in the where clause, otherwise the engine will give up using the index and scan the whole table. For example, you can set the default value of num to 0 to ensure that the num column in the table has no null value, and then make the following query: Select ID from TWERE NUM = 0.
3. Try to avoid using it in the where clause! = or
4. Try to avoid using the or join condition in the where clause, otherwise the engine will give up using the index and scan the whole table, such as: Select ID from TW WHERE NUM =10 or num = 20. You can query as follows: select id from twwhere num =10 union all select id from twwhere num = 20.
5.in and not in should also be used with caution, otherwise the whole table will be scanned, for example, select ID from where num in (1,2,3). If you can use between, don't use in: select id from where num between1and 3.
6. The following query will also cause a full table scan: select ID from where name like' Li%'. In order to improve efficiency, full-text retrieval can be considered.
7. If parameters are used in the where clause, it will also lead to a full table scan. Because SQL only parses local variables at runtime, the optimizer cannot postpone the selection of access plan until runtime; You must select it at compile time. However, if the access plan is established at compile time, the value of the variable is still unknown, so it cannot be used as an input item for index selection. For example, the following statement will scan the whole table: select id from t where num=@num, which can be changed into a mandatory query by using index: select ID from t with (index name) where num = @ num.
8. Try to avoid expression operations on fields in the where clause, which will cause the engine to give up using indexes and scan the whole table. For example, you should change the select id from where num/2 =100 to select id from where num =100 * 2.
9. Function operations on fields in the where clause should be avoided as much as possible, which will cause the engine to abandon the use of indexes and scan the whole table. For example, select ID from where substring (name, 1, 3) =' abc', and the name begins with abc.
It should read:
Select the id from t, where the name is "abc%".
10. No need? =? Functions, arithmetic or other expression operations are performed on the left, otherwise the system may not use the index correctly.
1 1. When the index field is used as a condition, if the index is a composite index, the first field in the index must be used as a condition to ensure that the index is used by the system, otherwise the index will not be used, and the field order should be as consistent as possible with the index order.
12. Don't write some meaningless queries. If you need to generate an empty table structure: select col 1, col 2 into # t from where1= 0.
This kind of code will not return any result set, but it will consume system resources. It should be changed to this:
Create table #t (...)
13. It is a good choice to use exists instead of in many times: select num from a where num in (select num from b).
Replace with the following statement:
select num from a where exists(select 1 from b where num = a . num)
14. Not all indexes are valid for the query. SQL optimizes the query according to the data in the table. When there is a lot of duplicate data in the index column, the SQL query may not use the index. For example, if there are almost half of the fields in a table, men and women, even if the index is based on gender, it will not play a role in query efficiency.
15. The more indexes, the better. Although the index can improve the efficiency of corresponding selection, it will also reduce the efficiency of insertion and update. Because the index may be rebuilt during insertion or update, how to build the index needs to be carefully considered according to the specific situation. The number of indexes in a table should not exceed 6. If there are too many indexes, consider whether it is necessary to establish indexes on some columns that are not commonly used.
16. Updating clustered index data columns should be avoided as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the column values change, the order of the whole table records will be adjusted, which will consume considerable resources. If the application system needs to update the clustered index data columns frequently, it is necessary to consider whether the index should be built as a clustered index.
17. Try using a numeric field. If the field only contains numerical information, try not to design it as characters, which will reduce the performance of query and connection and increase the storage overhead. This is because the engine will compare each character in the string one by one when processing queries and connections, but only one comparison is enough for the number type.
18. Use varchar/nvarchar instead of char/nchar as much as possible, because firstly, the storage space of variable-length fields is small, which can save storage space, and secondly, for queries, the search efficiency is obviously higher in relatively small fields.
19. Do not use select * from t anywhere, but use a specific field list? *? , do not return any unnecessary fields.
20. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, please note that the index is very limited (only the primary key index).
2 1. Avoid creating and deleting temporary tables frequently to reduce the consumption of system table resources.
22. Temporary tables are not unavailable. Using them correctly can make some routines more effective, for example, when it is necessary to repeatedly refer to data sets in large tables or public tables. However, for one-time events, it is best to use export tables.
23. When creating a temporary table, if you insert a large amount of data at a time, you can use select into instead of create table to avoid creating a large number of logs and improve the speed; If the amount of data is not large, in order to reduce the resources of system tables, tables should be created first and then inserted.
24. If temporary tables are used, all temporary tables must be explicitly deleted at the end of the stored procedure. truncate table table first and then drop table table to avoid locking the system table for a long time.
25. Try to avoid using cursors, because cursors are inefficient. If the data of cursor operation exceeds 654.38+0 million rows, then it is necessary to consider rewriting.
26. Before using the cursor-based method or temporary table method, we must first find a set-based method to solve the problem, and the set-based method is usually more effective.
27. Like temporary tables, cursors are not unavailable. Using FAST_FORWARD cursor for small data sets is usually superior to other line-by-line processing methods, especially when multiple tables must be referenced to obtain the required data. Included in the result set? Total? Routine is usually faster than using cursors. If development time permits, both cursor-based method and set-based method can be tried to see which method works better.
28. Set SET NOCOUNT ON at the beginning of all stored procedures and triggers, and set set SET NOCOUNT OFF at the end. There is no need to send the DONE_IN_PROC message to the client after executing each statement of the stored procedure and trigger.
29. Try to avoid big transaction operations and improve system concurrency.
30. Try to avoid returning a large amount of data to the client. If the amount of data is too large, it is necessary to consider whether the corresponding requirements are reasonable.
How to establish MYSQL database index correctly and reasonably
Label: