Effective MySQL之SQL語句最優化--索引

1 兩個索引取並集組合

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 可以在WHERE、ORDER BY 以及GROUP BY 欄位中使用索引；然而，一般來說MySQL 在一個表格上只選擇一個索引。
從MySQL 5.0 開始，在個別例外情況中優化器可能會使用一個以上的索引，但是在早期的版本中這樣做會導致查詢運行更加緩慢。

 

2 兩個索引取並集
第一種: 最常見的索引合併的操作是兩個索引取並集，當用戶對兩個有很
高基數的索引執行OR 操作時會出現這種這種索引合併操作。請
看下面的範例：
 

 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); 採用了union的聯合索引模式,取合集.

itch系統變量，可以透過啟用或停用這個變數來控制這些附加選項。想了解更多可
以參考以下連結：http://www.php.cn/。
 

2 第二種類型的索引合併是對兩個有少量唯一值的索引取交集，如下所示：

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 這裡由於是AND,所以只需要取2個索引中最高效的那個索引來進行遍歷取值.

3 第三種類型的索引合併操作和對兩個索引取並集比較類似，但它需要先經過排序：

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)

 

可以透過以下連結了解更多關於索引合併的資訊：http://www.php.cn/。

 

4 數個索引合併的情況

在建立這些範例的過程中，也發現一種先前在任何客戶端的查詢中未曾出現過的新情況。以下是三個索引合併的範例：

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)

 

技巧

應該經常評估多列索引是否比讓最佳化器合併索列更有效率。多個單列索引和多個多列索引到底哪個更有優勢？這個問題
只有結合特定應用程式的查詢類型和查詢容量才能給出答案。在各種不同的查詢條件下，將一些高基數列上的那些單列索引進行索引合併能夠帶來很高的靈活性。資料庫寫入操作的效能參考因素也同樣會影響到取得資料的最優的資料存取路徑。

5 創建更好的MySQL 索引

主要用的比較多的2個特殊的索引
透過使用索引，查詢的執行時間可以從秒的數量級減少到毫秒數量級，這樣的性能改進能夠為你的應用程式的效能帶來飛躍。 合理的調整你的索引對最佳化來說是非常重要的，尤其是對於高吞吐量的應用程式。即使對執行時間的改進只是數毫秒，但對於

一個每秒執行1000 次的查詢來說這也是非常有意義的效能提升。例如，把一個原本需要20 毫秒執行的每秒運行1 000 次的查詢的

執行之間縮短4 毫秒，這對於優化SQL 語句來說是至關重要的。我們將使用第4 章介紹的方法建立多列索引，並在這基礎
上建立更好的覆蓋索引。


● 建立覆蓋索引

ALTER TABLE artist DROP INDEX founded,
 ADD INDEX founded_name (founded,name)； ，因此沒有必要在非主碼索引中指定主碼。 這一重要特性意味著InnoDB 引擎中所有非主碼索引都隱含主碼列了。並且對於那些從MyISAM 儲存引擎轉換過來的表，通常會
在它們InnoDB 表索引中將主碼添加為最後一個元素。 當QEP 在Extra 欄位中顯示Using index 時，這並不表示在訪
問底層表資料時使用到了索引，這表示只有這個索引才是滿足查詢所有要求的。這種索引可以為大型查詢或頻繁執行的查詢帶
來顯著的效能提升，它被稱為覆蓋索引。覆蓋索引得名於它滿足了查詢中給定表用到的所有的列。想
要建立一個覆蓋索引，這個索引必須包含指定表上包含WHERE語句、ORDER BY 語句、GROUP BY 語句(如果有的話)以及
SELECT 語句中的所有欄位。

[Comment]:隨著資料容量的增加，尤其是超過記憶體和磁碟最大容量的時候，為一個大型欄位建立索引可能
會對系統整體效能有影響。覆蓋索引對於那些使用了許多較小長度的主碼和外鍵約束的大型規範化模式來說是理想的最佳化方式。

● 建立局部欄位的索引

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中文网（www.php.cn）！