Optimization of SQL statements in objective MySQL -- Index

Optimization of SQL statements in balanced MySQL -- INDEX 1 combines two indexes in parallel [SQL] ALTER TABLE album ADD INDEX name_release (name, first_released); EXPLAIN SELECT. name, ar. name,. first_released FROM album a inner join artist ar USING (artist_id) WHERE. name = 'Greatest Hits 'order BY. first_released; mysql> explain select. name, ar. name,->. first_released-> FROM album a-> inner join artist ar USING (artist_id)-> WHERE. name = 'Greatest Hits '-> order by. first_released; + ---- + ------------- + ------- + -------- + upper + -------------- + --------- + upper + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + -------- + ------------------------------ + -------------- + --------- + ----------------- + ------ + --- ---------- + | 1 | SIMPLE | a | ref | name_release, name_2, name_part2 | name_release | 257 | const | 659 | Using where | 1 | SIMPLE | ar | eq_ref | PRIMARY | 4 | union. a. artist_id | 1 | + ---- + ------------- + ------- + -------- + upper + -------------- + --------- + upper + ------ + ------------- + 2 rows in set (0.00 sec) alter table album add index name_release (Name, first_released); MySQL can use indexes in the WHERE, order by, and group by columns. However, MySQL generally selects only one index for a table. Since MySQL 5.0, The Optimizer may use more than one index in some cases, but in earlier versions, this will lead to slow query operations. 2. merge two indexes. The first operation is to merge two indexes, this index merge operation occurs when you perform the OR operation on two indexes with a high base. See the following example: [SQL] SET @ session. optimizer_switch = 'index _ merge_intersection = On'; explain select artist_id, name FROM artist WHERE name = 'Queen 'OR founded = 1942 \ G mysql> explain select artist_id, name-> FROM artist-> WHERE name = 'Queen '-> OR founded = 1942; + ---- + ------------- + -------- + ------------- + --------------- + -------------- + --------- + ------ + ---------------------------------------- + | Id | select_type | table | type | partition | key | key_len | ref | rows | Extra | + ---- + ------------- + -------- + ------------- + -------------- + ----------- + ------ + response ---------------------------------------- + | 1 | SIMPLE | artist | index_merge | name, founded | name, founded | 257,2 | NULL | 499 | Using union (name, founded); Using where | + ---- + ------------- + -------- + ------------- + --------------- + -------------- + --------- + ------ + ------------------------------------------ + 1 row in set (0.01 sec) Extra: Using union (name, founded); the union index mode is adopted, collection. note that optimizer_switch is introduced for the first time in MySQL 5.1. You can enable or disable this variable to control these additional options. 2. The second type of index merge is to take the intersection of two indexes with a small number of unique values, as shown below: [SQL] SET @ session. optimizer_switch = 'index _ merge_intersection = On'; explain select artist_id, name FROM artist WHERE type = 'band' AND founded = 1942; mysql> SET @ session. optimizer_switch = 'index _ merge_intersection = On'; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN SELECT artist_id, name-> FROM artist-> WHERE type = 'band'-> AND founded = 19 42; + ---- + ------------- + -------- + ------ + --------------- + --------- + ------- + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + -------- + --------------- + --------- + ------- + ------ + ------------- + | 1 | SIMPLE | artist | ref | founded | 2 | const | 498 | Using where | + ---- + ------------- +- ------- + ------ + --------------- + --------- + ------- + ------ + ------------- + 1 row in set (0.00 sec) Extra: Using intersect (founded, type ); the Using where clause is AND. Therefore, you only need to retrieve the most efficient index of the two indexes to traverse the value. 3. The third type of index merge operation is similar to the two index Union operations, but it needs to be sorted first: [SQL] EXPLAIN SELECT artist_id, name FROM artist WHERE name = 'Queen 'OR (founded BETWEEN 1942 AND 1950); mysql> explain select artist_id, name-> FROM artist-> WHERE nam E = 'Queen '-> OR (founded BETWEEN 1942 AND 1950 ); + ---- + ------------- + -------- + ------------- + -------------- + --------- + ------ + keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + -------- + ------------- + --------------- + -------------- + --------- + ------ + ----------------- -------------------------- + | 1 | SIMPLE | artist | index_merge | name, founded | name, founded | 257,2 | NULL | 5900 | Using sort_union (name, founded ); using where | + ---- + ------------- + -------- + ------------- + --------------- + -------------- + --------- + ------ + rows + 1 row in set (0.00 sec) 4. When creating these examples, we also find a new situation that has never been found in any client query. The following is an example of merging three indexes: [SQL] mysql> EXPLAIN SELECT artist_id, name FROM artist WHERE name = 'Queen 'OR (type = 'band' AND founded = '000000 ');..... mysql> explain select artist_id, name-> FROM artist-> WHERE name = 'Queen '-> OR (type = 'band' AND founded = '20140901 '); + ---- + ------------- + -------- + ------------- + --------------- + -------------- + --------- + ------ + -------------------------------------- + | I D | select_type | table | type | partition | key | key_len | ref | rows | Extra | + ---- + ------------- + -------- + ------------- + -------------- + ----------- + ------ + response ---------------------------------------- + | 1 | SIMPLE | artist | index_merge | name, founded | name, founded | 257,2 | NULL | 499 | Using union (name, founded); Using where | + ---- + ------------- + -------- + ----------- +- -------------- + --------- + ------ + ------------------------------------------ + 1 row in set (0.00 sec) techniques should often evaluate whether multi-column indexes are more efficient than merge columns by the optimizer. Which of the following is more advantageous for Multiple Single-Column indexes and multiple multi-column indexes? This question can be answered only by combining the query type and query capacity of a specific application. Under various query conditions, it is highly flexible to merge the index of the Single Column indexes in some high-base columns. The performance reference factors of database write operations also affect the optimal data access path. 5. Creating better MySQL indexes mainly uses two more special indexes. By using indexes, the query execution time can be reduced from the second order to the millisecond order, such performance improvement can bring a leap to the performance of your applications. Reasonable adjustment of your index is very important for optimization, especially for high-throughput applications. Even if the improvement on the execution time is only several milliseconds, this is also a very meaningful performance improvement for a query that executes 1000 times per second. For example, reducing the execution of a query that originally took 20 ms to run times per second by 4 ms is crucial for optimizing SQL statements. We will use the method described in Chapter 4th to create multi-column indexes and create better overwriting indexes on this basis. ● Create override index alter table artist drop index founded, add index founded_name (founded, name); In InnoDB, the value of the master code is appended to each corresponding record of the non-master code index. Therefore, it is not necessary to specify the master code in the non-master code index. This important feature means that all non-master code indexes in the InnoDB Engine imply the master code column. And for tables converted from the MyISAM storage engine, the primary code is usually added as the last element in their InnoDB table index. When QEP displays the Using index in the Extra column, this does not mean that the index is used when accessing the underlying table data. This means that only this index meets all the query requirements. This index can significantly improve the performance of large queries or frequently executed queries. It is called overwrite index. The overwrite index is named after it satisfies all the columns used in the given table in the query. To create a overwriting index, the index must contain the WHERE statement, order by statement, group by statement (if any), and all columns in the SELECT statement. [Comment]: as the data capacity increases, especially when the memory and disk capacity exceed the maximum, creating an index for a large column may affect the system integrity. Covering indexes is an ideal Optimization Method for large-scale normalization modes that use many small-Length Primary code and foreign key constraints. ● Create an INDEX for a local column [SQL] ALTER TABLE artist DROP INDEX name, ADD INDEX name_part (name (20); here we mainly consider how to reduce the space occupied by the INDEX. A smaller index means less disk I/O overhead, which means faster access to the rows to be accessed, especially when the index and data column on the disk are much larger than the available system memory. The performance improvement will be more than the impact of a non-unique index with a low base. Whether local indexes are applicable depends on how data is accessed. When we introduced overwriting indexes, you can see that record a short version of the name column does not have any benefit to the SQL statement that has been executed. The biggest benefit is that you can only add restrictions on indexed columns. [SQL] EXPLAIN SELECT artist_id, name, founded FROM artist WHERE name LIKE 'Queen % '; mysql> EXPLAIN SELECT artist_id, name, founded-> FROM artist-> WHERE name LIKE 'Queen % '; + ---- + ------------- + -------- + ------- + ------------- + ------ + --------- + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + -------- + ------- + ----- ---------- + ------ + --------- + ------ + ------------- + | 1 | SIMPLE | artist | range | name | 257 | NULL | 93 | Using where | + ---- + ----------- + -------- + ------- + --------------- + ------ + --------- + ------ + ------------- + 1 row in set (0.00 sec) in this example, there is no Using Index after Extra, so record the full name in the Index does not bring any additional benefit. The local column index meets the WHERE condition. The proper length depends on the data distribution and access path. Currently, there is no accurate method to calculate the proper index length. Therefore, it is essential to compare the number of unique values in the column length within a given range. Count SELECT count (*) FROM artist WHERE name LIKE 'Queen % '; only 93 records, while SELECT count (*) FROM artist; there are 577983 records, according to the general situation, indexes can be taken. Is the definition of name (20) 20 too long? [SQL] ALTER TABLE artist DROP INDEX name_part, ADD INDEX name_part2 (name (10); mysql> ALTER TABLE artist-> DROP INDEX name_part, -> add index name_part2 (name (10); Query OK, 0 rows affected (3.41 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT artist_id, name, founded-> FROM artist-> WHERE name LIKE 'Queen % '; + ---- + ------------- + -------- + ------- + --------------- + ------------ + ------- -- + ------ + ------------- + | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + -------- + ------- + --------------- + ------------ + --------- + ------ + ----------- + | 1 | SIMPLE | artist | range | name_part2 | name_part2 | 12 | NULL | 93 | Using where | + ---- + ------------- + -------- + ------- + --------------- + ------------ + --------- + ------ + -- ---- + ------------- + 1 row in set (0.00 sec) to view the result, and try again with name (5. Mysql> alter table artist-> drop index name_part2,-> add index name_part3 (name (5); Query OK, 0 rows affected (3.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select artist_id, name, founded-> FROM artist-> WHERE name LIKE 'Queen % '; + ---- + ------------- + -------- + ------- + ------------- + ------------ + --------- + ------ + --------------- + | id | select_type | table | type | possible_k Eys | key | key_len | ref | rows | Extra | + ---- + --------------- + -------- + ------- + ------------- + ------------ + --------- + ------ + ------------- + | 1 | SIMPLE | artist | range | name_part3 | name_part3 | 7 | NULL | 93 | Using where | + ---- + ------------- + -------- + ------- + ------------- + ------------ + --------- + ------ + --------------- + 1 row in set (0.00 sec) it seems that the effect of partial indexes on like is not very obvious and may be related to the data distribution range. All the 93 pieces of data are scattered in each database block. Therefore, the parser considers that the data cannot be traversed by several index entries, therefore, the Using Index prompt is not displayed in the Extra column. Summary: correctly defining columns (including the order and position of the defined columns) in the index can change the actual usage of the index. Good indexes can greatly improve the performance of a slow query. The index may also reduce the execution time of the query that was executed quickly by several milliseconds. In a high-concurrency system, reducing queries by several milliseconds will significantly improve performance and achieve greater capacity and scalability. Creating optimal indexes for SQL queries can be considered an art.