Effective MySQL SQL statement optimization--index

1 Union combination of two indexes

ALTER TABLE album ADD INDEX name_release (name,first_released);
EXPLAIN SELECT a.name, ar.name,
a.first_released
  FROM album a
 INNER JOIN artist ar USING (artist_id)
 WHERE a.name = 'Greatest Hits'
 ORDER BY a.first_released;
mysql> EXPLAIN SELECT a.name, ar.name,
    -> a.first_released
    ->   FROM album a
    ->  INNER JOIN artist ar USING (artist_id)
    ->  WHERE a.name = 'Greatest Hits'
    ->  ORDER BY a.first_released;
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
| 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                        | PRIMARY      | 4       | union.a.artist_id |    1 |             |
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
2 rows in set (0.00 sec)

ALTER TABLE album ADD INDEX name_release (name,first_released);

MySQL can use indexes in WHERE, ORDER BY and GROUP BY columns; however, Generally MySQL only selects one index on a table.
Starting with MySQL 5.0, the optimizer may use more than one index in individual exceptions, but doing so in earlier versions will cause queries to run more slowly.

2 Union of two indexes
First type: The most common index merging operation is the union of two indexes. When the user This kind of index merge operation occurs when two indexes with very
high cardinality perform an OR operation. Please
look at the following example:

 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        | possible_keys | key          | key_len | ref  | rows | Extra                                  |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
|  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); Using union's joint index mode, take Collection.

Note
The optimizer_switch system variable was first introduced in MySQL 5.1. You can
control these additional options by enabling or disabling this variable. For more information, please refer to the following link: //m.sbmmt.com/.

2 The second type of index merge is to intersect two indexes with a small number of unique values, as shown below:

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> 
mysql> 
mysql> EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->   WHERE type = 'Band'
    ->  AND founded = 1942;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | artist | ref  | founded       | founded | 2       | const |  498 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Extra: Using intersect(founded,type); Using where Since it is AND, you only need to take the most efficient index among the two indexes to traverse the value.

3 The third type of index merge operation is similar to the union of two indexes, but it needs to be sorted first:

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 name = 'Queen'
    ->   OR (founded BETWEEN 1942 AND 1950);
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
| 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 |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
1 row in set (0.00 sec)

You can learn more about index merging through the following link: //m.sbmmt.com/.

4 Situation of merging several indexes
In the process of creating these examples, I also discovered a method that had not appeared in any client query before. new situation. The following is an example of three index merges:

mysql> EXPLAIN SELECT artist_id, name
  FROM artist
  WHERE name = 'Queen'
 OR (type = 'Band' AND founded = '1942');
 .....
mysql> EXPLAIN SELECT artist_id, name
    ->   FROM artist
    ->   WHERE name = 'Queen'
    ->  OR (type = 'Band' AND founded = '1942');
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| 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 |  499 | Using union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
1 row in set (0.00 sec)

Tips
You should always evaluate whether multi-column indexes are better than letting the optimizer merge Sole is more efficient. Which one has more advantages, multiple single-column indexes or multiple multi-column indexes? The answer to this question can only be given in conjunction with the query type and query capacity of the specific application. Under various query conditions, merging single-column indexes on some high-cardinality columns can bring high flexibility. The performance reference factors of database write operations will also affect the optimal data access path for obtaining data.

5 Create better MySQL indexes
Mainly use two special indexes
By using indexes, query The execution time can be reduced from the order of seconds to the order of milliseconds. Such performance improvements can bring a leap in the performance of your application.

Properly tuning your indexes is very important for optimization, especially for high-throughput applications. Even if the improvement in execution time is only a few milliseconds, it is a very meaningful performance improvement for a query that is executed 1000 times per second. For example, shortening the

execution time by 4 milliseconds for a query that originally takes 20 milliseconds to execute 1,000 times per second is critical to optimizing SQL statements. We will use the methods introduced in Chapter 4 to create multi-column indexes and build on this foundation to create better covering indexes.


● Create covering index

ALTER TABLE artist

DROP INDEX founded, ADD INDEX founded_name (founded,name);
In InnoDB, the main code The value of will be appended to each corresponding record in the non-primary key index, so there is no need to specify the primary key in the non-primary key index. This important feature means that all non-primary key indexes in the InnoDB engine imply primary key columns. And for tables that are converted from the MyISAM storage engine, the primary key is usually
added as the last element in their InnoDB table indexes. When QEP displays Using index in the Extra column, it does not mean that the index is used when accessing the underlying table data. It means that only this index meets all the requirements of the query. This kind of index can bring significant performance improvements to large queries or frequently executed queries. It is called a covering index. A covering index gets its name from the fact that it covers all columns used in a given table in a query. If you want
to create a covering index, this index must contain all columns in the WHERE statement, ORDER BY statement, GROUP BY statement (if any) and
SELECT statement on the specified table.

[Comment]: As data capacity increases, especially when it exceeds the maximum capacity of memory and disk, creating an index for a large column may
have an impact on the overall system performance. Covering indexes are an ideal optimization for large normalized schemas that use many small-length primary key and foreign key constraints.


● Create an index for a local column

ALTER TABLE artist
 DROP INDEX name,
  ADD INDEX name_part(name(20));

  这里主要考虑的是如何减小索引占用的空间。一个更小的索引意味着更少的磁盘I/O 开销，而这又意味着能更快地访问到需
要访问的行，尤其是当磁盘上的索引和数据列远大于可用的系统内存时。这样获得的性能改进将会超过一个非唯一的并且拥有低
基数的索引带来的影响。局部索引是否适用取决于数据是如何访问的。之前介绍覆盖索引时，你可以看到记录一个短小版本的name 列不会对执行过
的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          | name | 257     | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

在这个示例中，Extra后面没有出现Using Index，所以在索引中记录全名并没有带来额外的益处。
而所提供的局部列索引满足了WHERE 条件。如何选择合适的长度取决于数据的分布以及访问路径。目前没有准确的方法计算索
引的恰当长度。因此对给定范围的列长度内的唯一值数目的比较
是必不可少的。

count了下SELECT count(*) FROM artist WHERE name LIKE 'Queen%'; 才93条记录，而SELECT count(*) FROM artist;有577983条记录，按照普遍的情况，可以走索引，难道是name(20)的20定义的太长了？

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)

看结果，再用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_keys | 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)

看来局部索引对like的效果不是很明显的，可能跟数据分布范围有关，也许这93条数据全部打散在各个数据库块中，
所以导致解析器认为不能简单地通过数次index就能遍历出数据，故而Extra栏里面就没有出现Using Index的提示。

 
总结：在索引中正确的定义列(包括定义列的顺序和位置)能够改变索引的实际使用效果。好的索引能够为一个执行缓慢的查询带来
巨大的性能提升。索引也可能使原来执行很快的查询的执行时间减少若干毫秒。在高并发系统中，将1 000 000 条查询减少几毫秒
将会显著改善性能，并且获得更大的容量和扩展性。为SQL 查询创建最优索引可以认为是一项艺术。

 

 以上就是Effective MySQL之SQL语句最优化--索引 的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！