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 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 the non-index will 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.
063 Composite IndexFor 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), then 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.
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.
065 using a short indexIndex 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.
066 Sorted Index issuesThe 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.
067 like statement operationsIt 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.
068 MySQL Database design data type selection need to be aware of where? varchar and char types, varchar is variable length, requires additional 1-2 bytes of storage, Can save space and can be useful for performance. However, because it is variable length, it is possible to fragment, such as update data;  
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
None in and operations do not use the index to perform a full table scan. Not in can be replaced by not exists, and id! = 3 can be replaced with id>3 or id<3.
You cannot use NULL as an index, and any column that contains null values will not be included in the index. Even if the index has more than one column, the column is excluded from the index as long as there is a column in the column that contains null. This means that if a column has a null value, even indexing the column does not improve performance.
Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the indexed
Normal index Add index
ALTER TABLE ' table_name ' ADD INDEX index_name (' column ');
Primary key index Add primary key
ALTER TABLE ' table_name ' ADD PRIMARY KEY (' column ');
Unique index Add unique
ALTER TABLE ' table_name ' ADD UNIQUE (' column ');
Full-text index add fulltext
ALTER TABLE ' table_name ' ADD fulltext (' column ');
Multi-column Index
ALTER TABLE ' table_name ' ADD INDEX index_name (' column1 ', ' column2 ', ' column3 ')
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
If a table has only 5 records, Using an index to access a record, you first need to access the index table, and then access the table through the Index table, the General Index table and the data table is not the same data block, in which case Oracle must read the data block at least two times. Without an index, Oracle will read all the data at once, and the processing speed will obviously be faster than the index. &NBSP;
frequently inserted, deleted, modified tables
data repetition and distributed average table field
If a table has 100,000 rows of records, there is a field a that has only T and F two values, and the probability of distribution of each value is about 50%, then the Jianjian index of the table A is generally not increasing the query speed of the database. &NBSP;
frequently and the main field one query but the main field index value is more than the table fields
such as the GC_DFSS (electricity charge) table is often by the number of charges, Household identification number, meter reading date, electricity charges occurred years, operating signs to specifically query the situation of a certain 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 index of the charge by the number of records have been reduced to only a few, If you press the next few fields, the index query will not have much effect on performance.
1. To optimize the query, avoid full table scan as far as possible, you should first consider establishing an index on the columns involved in where and order by.
2. Avoid null values of fields in the WHERE clause as far as possible, otherwise it will cause the engine to discard the full table scan using the index, such as: Select ID from the where num is null to set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query: Select ID from t where num= 0
3. Try to avoid using the! = or <> operator in the WHERE clause, or the engine discards the full table scan using the index.
4. Try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to discard full table scans using the index, such as: Select ID from t where num=10 or num=20 can be queried like this: select ID from t where num=10 UNION ALL select ID FR Om t where num=20
5.in and not in should also be used with caution, which would otherwise result in a full table scan, such as: Select ID from t where num in (three-in-three) for consecutive values, you can use between to not use in: Select ID from t where num between 1 and 3
6. Avoid using wildcard characters. The following query will also cause a full table scan: Select ID from t where name like ' Li% ' to improve efficiency, you can consider full-text indexing.
7. If you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan: Select ID from t where [email protected] can be changed to force query using index: SELECT ID from T with (index name) where [email protected ]
8. You should try to avoid expression operations on fields in the WHERE clause, which causes the engine to discard full table scans using the index. For example: Select ID from t where num/2=100 should be changed to: Select ID from t where num=100*2
9. You should try to avoid function manipulation of fields in the WHERE clause, which causes the engine to discard full table scans using the index. For example: Select ID from t where substring (name,1,3) = ' abc ', name begins with ABC ID should be changed to: Select ID from t where name like ' abc% '
10. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, the index may not be used correctly by the system.
11. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.
12. Do not write some meaningless queries, if you need to generate an empty table structure: Select Col1,col2 into #t the from T where 1=0 such code will not return any result set, but will consume system resources, should be changed to this: CREATE TABLE #t (...)
13. It's a good choice to use exists instead of in.: Select num from a where num in (select num from B) is replaced 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 queries, SQL is optimized for queries based on the data in the table, and when there is a large amount of data duplication in the index column, the SQL query may not take advantage of the index, as there are fields Sex,male and female almost half of the table, so even indexing on sex does not work for query efficiency.
15. The index is not the more the better, the index can improve the efficiency of the corresponding select, but it also reduces the efficiency of insert and update, because the index may be rebuilt at insert or update, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.
16. Avoid updating clustered index data columns whenever possible, because the order of the clustered index data columns is the physical storage order of the table records, and once the column values change, the order of the entire table records is adjusted, which consumes considerable resources. If your application needs to update clustered index data columns frequently, you need to consider whether the index should be built as a clustered index.
17. Use numeric fields as much as possible, a field that contains only numeric information should not be designed as a character type, which reduces the performance of queries and connections and increases storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.
18. Use Varchar/nvarchar instead of Char/nchar as much as possible, because the first variable long field storage space is small, can save storage space, second for the query, in a relatively small field search efficiency is obviously higher.
19. Do not use SELECT * from t anywhere, replace "*" with a specific field list, do not return any fields that are not available.
20. Try to use table variables instead of temporary tables。 If the table variable contains a large amount of data, be aware that the index is very limited (only the primary key index).
21. Avoid frequent creation and deletion of temporary tablesTo reduce the consumption of system table resources.
22. Temporary tables are not unusable and are used appropriately to make certain routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table. However, for one-time events, it is best to use an export table.
23. When creating a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE tableTo avoid causing a large number of logs to increase speed; if the amount of data is small, to mitigate the resources of the system table, create table first and then insert.
24. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, then drop table, which avoids longer locking of the system tables.
25. Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.
26. Before using a cursor-based method or temporal table method, you should first look for a set-based solution to solve the problem, and the set-based approach is generally more efficient.
27. As with temporary tables, cursors are not unusable. Using Fast_forward cursors on small datasets is often preferable to other progressive processing methods, especially if you must reference several tables to obtain the required data. Routines that include "totals" in the result set are typically faster than using cursors. If development time permits, a cursor-based approach and a set-based approach can all be tried to see which method works better.
28. Set NOCOUNT on at the beginning of all stored procedures and triggers, set NOCOUNT OFF at the end. You do not need to send a DONE_IN_PROC message to the client after each statement that executes the stored procedure and trigger.
29. Try to avoid large transaction operation and improve the system concurrency ability.
30. Try to avoid returning large amounts of data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable.
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 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 the non-index will 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, 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.
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 the 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 not EXISTS instead, id! = 3 can use Id>3 or ID < 3
1, show processlist;
2, select * from Information_schema.processlist;
3. You can add the following in [Mysqld]:
Log =/var/log/mysql.log
If you need to monitor slow queries, you can add the following:
Log-slow-queries =/var/log/slowquery.log
Long_query_time = 1
Pros:
You can save the relevant data together . This way, extracting data from several pages on disk can fetch all the data of a user. If you do not use aggregation, reading each data will access the disk. &NBSP;
data access fast . The clustered index saves both the index and the data in the same b-tree, so getting data from a clustered index is usually faster than finding it in a nonclustered index. &NBSP;
Cons:
aggregation maximizes the performance of I/o-intensive loads . If the data can be loaded into memory, then the order is irrelevant. There is no use in such gatherings. &NBSP;
The insertion speed is heavily dependent on the insertion order . Updating a clustered index column is expensive because forcing InnoDB to move each updated row to a new location. &NBSP;
second (nonclustered) indexes may be larger than expected because their leaf nodes contain the primary key columns of the referenced rows. The second index access requires two index lookups instead of one at a time. The second index of the InnoDB leaf node contains the primary key value as the pointer to the row, not the row pointer. This strategy reduces the maintenance of indexes when moving rows or data paging. Using the row's primary key value as a pointer makes the index larger, but this means that InnoDB can move rows without updating the pointer.
index Type: B-tree index, hash index
B-tree Index(the default index type) accelerates data access because the storage engine does not scan the entire table to get the data that is needed. Instead, it starts at the root node. The root node holds pointers to child nodes, and the storage engine looks for data based on the pointer. It finds the correct pointer by finding the value in the node page, the node page contains a pointer to the child node, and the storage engine looks for the data based on the pointer. It finds the correct pointer by locating the values in the node page, which contains the upper and lower bounds of the values in the child nodes. Finally, the storage engine may not be able to find the data it needs, or it may successfully find the leaf page that contains the data.
Example: B-tree indexes are useful for the following types of queries. Match full name, match leftmost prefix, match column prefix, match range value, exact match part, and match another part of a range;
limitations of the B-tree index: If the lookup does not start at the far left of the indexed column, it is of little use. Columns in the index cannot be skipped, and the storage engine does not have priority access to any of the columns to the right of the first scope condition. Example: If the query is where last_name= ' Smith ' and first_name like ' j% ' and dob= ' 1976-12-23 '; Access can use only the first two columns of the index, as the as is the scope condition.
HachisoBased on a hash table, it is useful only for precise lookups that use each column in the index. For each row, the storage engine calculates the hash code for the indexed column, which is a smaller value and may be different from the hash code of the other rows. It saves the hash code in the index and holds a pointer to each row in the hash table.
Because the index contains only the hash code and the row pointer, not the value itself, MySQL cannot use the values in the index to avoid reading the rows.
MySQL cannot sort by using hash indexes because they do not save rows sequentially.
Hash indexes do not support partial key matching because they are computed by the full value of the index. That is, if there is an index on both columns (A, b), and only A is used in the WHERE clause, the index will not work.
The hash index only supports equality comparisons using = In () and <=>. They cannot speed up the range query. For example where price > 100;
Accessing data in a hash index is very fast, unless the collision rate is high. When a collision occurs, the storage engine must access each row pointer in the linked list and then row-by-line data comparison to determine the correct data. If there are many collisions, some index maintenance operations may become slower.
MySQL Index summary
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
