mysql索引合併:一條sql可以使用多個索引-mysql教程-PHP中文網

首頁

資料庫

mysql教程

mysql索引合併:一條sql可以使用多個索引

黄舟

Feb 21, 2017 am 10:15 AM

mysql索引合并

轉載請註明來源：mysql索引合併:一個sql可以使用多個索引

前言

mysql的索引合併並不是什麼新特性。早在mysql5.0版本就已經實現。之所以還寫這篇博文，是因為好多人還一直保留著一條sql語句只能使用一個索引的錯誤觀念。本文會透過一些範例來說明如何使用索引合併。

什麼是索引合併

下面我們看下mysql文檔中對索引合併的說明：

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. 
The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. 
This access method merges index scans from a single table; 
it does not merge scans across multiple tables.

1、索引合併是把幾個索引的範圍掃描合併成一個索引。
2、索引合併的時候，會對索引進行並集，交集或先交集再並集操作，以便合併成一個索引。
3、這些需要合併的索引只能是一個表格的。不能對多表進行索引合併。

使用索引合併有啥收益

簡單的說，索引合併，讓一條sql可以使用多個索引。將這些索引取交集，並集，或先取交集再取並集。從而減少從資料表中取資料的次數，提高查詢效率。

怎麼確定使用了索引合併

在使用explain對sql語句進行操作時，如果使用了索引合併，那麼在輸出內容的type列會顯示 index_merge，key列會顯示所有使用的索引。如下：
mysql索引合併:一條sql可以使用多個索引

在explain的extra欄位中會以下幾種：
Using union 索引取並集
Using sort_union 先對取出的資料按rowid排序，然後再取並集
Using intersect 索引取交集

你會發現並沒有 sort_intersect，因為根據目前的實現，想索引取交集，必須保證透過索引取出的資料順序和rowid順序是一致的。所以，也就沒必要sort了。

sort_union索引合併的範例

資料表結構

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1_part1` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `key1_part2` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `key2_part1` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `key2_part2` int(11) NOT NULL DEFAULT &#39;0&#39;,
  PRIMARY KEY (`id`),
  KEY `key1` (`key1_part1`,`key1_part2`),
  KEY `key2` (`key2_part1`,`key2_part2`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

數據

mysql> select * from test;
+----+------------+------------+------------+------------+
| id | key1_part1 | key1_part2 | key2_part1 | key2_part2 |
+----+------------+------------+------------+------------+
|  1 |          1 |          1 |          1 |          1 |
|  2 |          1 |          1 |          2 |          1 |
|  3 |          1 |          1 |          2 |          2 |
|  4 |          1 |          1 |          3 |          2 |
|  5 |          1 |          1 |          3 |          3 |
|  6 |          1 |          1 |          4 |          3 |
|  7 |          1 |          1 |          4 |          4 |
|  8 |          1 |          1 |          5 |          4 |
|  9 |          1 |          1 |          5 |          5 |
| 10 |          2 |          1 |          1 |          1 |
| 11 |          2 |          2 |          1 |          1 |
| 12 |          3 |          2 |          1 |          1 |
| 13 |          3 |          3 |          1 |          1 |
| 14 |          4 |          3 |          1 |          1 |
| 15 |          4 |          4 |          1 |          1 |
| 16 |          5 |          4 |          1 |          1 |
| 17 |          5 |          5 |          1 |          1 |
| 18 |          5 |          5 |          3 |          3 |
| 19 |          5 |          5 |          3 |          1 |
| 20 |          5 |          5 |          3 |          2 |
| 21 |          5 |          5 |          3 |          4 |
| 22 |          6 |          6 |          3 |          3 |
| 23 |          6 |          6 |          3 |          4 |
| 24 |          6 |          6 |          3 |          5 |
| 25 |          6 |          6 |          3 |          6 |
| 26 |          6 |          6 |          3 |          7 |
| 27 |          1 |          1 |          3 |          6 |
| 28 |          1 |          2 |          3 |          6 |
| 29 |          1 |          3 |          3 |          6 |
+----+------------+------------+------------+------------+
29 rows in set (0.00 sec)

使用索引合并的案例

mysql> explain select * from test where (key1_part1=4 and key1_part2=4) or (key2_part1=4 and key2_part2=4)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index_merge
possible_keys: key1,key2
          key: key1,key2
      key_len: 8,4
          ref: NULL
         rows: 3
        Extra: Using sort_union(key1,key2); Using where
1 row in set (0.00 sec)

未使用索引合并的案例

mysql> explain select * from test where (key1_part1=1 and key1_part2=1) or key2_part1=4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: key1,key2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 29
        Extra: Using where
1 row in set (0.00 sec)

sort_union總結

從上面的兩個案例大家可以發現，相同模式的sql語句，可能有時能使用索引，有時不能使用索引。是否能使用索引，取決於mysql查詢優化器對統計資料分析後，是否認為使用索引更快。
因此，單純的討論一條sql是否可以使用索引有點片面，還需要考慮資料。

union索引合併使用案例

資料表結構

14

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1_part1` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `key1_part2` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `key2_part1` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `key2_part2` int(11) NOT NULL DEFAULT &#39;0&#39;,
  PRIMARY KEY (`id`),
  KEY `key1` (`key1_part1`,`key1_part2`,`id`),
  KEY `key2` (`key2_part1`,`key2_part2`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

数据结构和之前有所调整。主要调整有如下两方面：
1、引擎从myisam改为了innodb。
2、组合索引中增加了id，并把id放在最后。

数据

数据和上面的数据一样。

使用索引合并的案例

mysql> explain select * from test where (key1_part1=4 and key1_part2=4) or (key2_part1=4 and key2_part2=4)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index_merge
possible_keys: key1,key2
          key: key1,key2
      key_len: 8,8
          ref: NULL
         rows: 2
        Extra: Using union(key1,key2); Using where
1 row in set (0.00 sec)

union總結

相同的數據，相同的sql語句，只是資料表結構有所調整，就從sort_union變成union了。有以下幾個原因：
1、只要透過索引取出的資料已經按rowid進行了排序，就可以使用union。
2、組合索引中在最後加id字段，目的就是透過索引前兩個字段取出的資料是按id排序。
3、把引擎從myisam改為innodb，目的就是讓id和rowid的順序一致。

intersect使用案例

mysql索引合併:一條sql可以使用多個索引
http://www.php.cn/

以上就是mysql索引合併:一條sql可以使用多個索引的內容，更多相關內容請關注PHP中文網（www.php.cn）！

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

解釋InnoDB緩衝池及其對性能的重要性。Apr 19, 2025 am 12:24 AM

InnoDBBufferPool通過緩存數據和索引頁來減少磁盤I/O，提升數據庫性能。其工作原理包括：1.數據讀取：從BufferPool中讀取數據；2.數據寫入：修改數據後寫入BufferPool並定期刷新到磁盤；3.緩存管理：使用LRU算法管理緩存頁；4.預讀機制：提前加載相鄰數據頁。通過調整BufferPool大小和使用多個實例，可以優化數據庫性能。

MySQL與其他編程語言：一種比較Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比，主要用于存储和管理数据，而其他语言如Python、Java、C 则用于逻辑处理和应用开发。MySQL以其高性能、可扩展性和跨平台支持著称，适合数据管理需求，而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

學習MySQL：新用戶的分步指南Apr 19, 2025 am 12:19 AM

MySQL值得學習，因為它是強大的開源數據庫管理系統，適用於數據存儲、管理和分析。 1）MySQL是關係型數據庫，使用SQL操作數據，適合結構化數據管理。 2）SQL語言是與MySQL交互的關鍵，支持CRUD操作。 3）MySQL的工作原理包括客戶端/服務器架構、存儲引擎和查詢優化器。 4）基本用法包括創建數據庫和表，高級用法涉及使用JOIN連接表。 5）常見錯誤包括語法錯誤和權限問題，調試技巧包括檢查語法和使用EXPLAIN命令。 6）性能優化涉及使用索引、優化SQL語句和定期維護數據庫。

MySQL：初學者的基本技能Apr 18, 2025 am 12:24 AM

MySQL適合初學者學習數據庫技能。 1.安裝MySQL服務器和客戶端工具。 2.理解基本SQL查詢，如SELECT。 3.掌握數據操作：創建表、插入、更新、刪除數據。 4.學習高級技巧：子查詢和窗口函數。 5.調試和優化：檢查語法、使用索引、避免SELECT*，並使用LIMIT。

MySQL：結構化數據和關係數據庫Apr 18, 2025 am 12:22 AM

MySQL通過表結構和SQL查詢高效管理結構化數據，並通過外鍵實現表間關係。 1.創建表時定義數據格式和類型。 2.使用外鍵建立表間關係。 3.通過索引和查詢優化提高性能。 4.定期備份和監控數據庫確保數據安全和性能優化。

MySQL：解釋的關鍵功能和功能Apr 18, 2025 am 12:17 AM

MySQL是一個開源的關係型數據庫管理系統，廣泛應用於Web開發。它的關鍵特性包括：1.支持多種存儲引擎，如InnoDB和MyISAM，適用於不同場景；2.提供主從復制功能，利於負載均衡和數據備份；3.通過查詢優化和索引使用提高查詢效率。

SQL的目的：與MySQL數據庫進行交互Apr 18, 2025 am 12:12 AM

SQL用於與MySQL數據庫交互，實現數據的增、刪、改、查及數據庫設計。 1）SQL通過SELECT、INSERT、UPDATE、DELETE語句進行數據操作；2）使用CREATE、ALTER、DROP語句進行數據庫設計和管理；3）複雜查詢和數據分析通過SQL實現，提升業務決策效率。

初學者的MySQL：開始數據庫管理Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括創建數據庫、表格，及使用SQL進行數據的CRUD操作。 1.創建數據庫：CREATEDATABASEmy_first_db;2.創建表格：CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入數據：INSERTINTObooks(title,author,published_year)VA

See all articles