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列會顯示出所有使用的索引。如下:
<img src=”https://i.iter01.com/images/cb91444a8709d4bee46a168437ff92115fa877d06438b554416f2248decf8c9d.png” alt=”index_merge_sql” width=”1405″ height=”198″ class=”alignnone size-full wp-image-809″ /></a></p>
在explain的extra欄位中會以下幾種:
Using union 索引取並集
Using sort_union 先對取出的資料按rowid排序,然後再取並集
Using intersect 索引取交集
你會發現並沒有 sort_intersect,因為根據目前的實現,想索引取交集,必須保證通過索引取出的資料順序和rowid順序是一致的。所以,也就沒必要sort了。
sort_union索引合併的示例
資料表結構
mysql> show create table testG
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`key1_part1` int(11) NOT NULL DEFAULT `0`,
`key1_part2` int(11) NOT NULL DEFAULT `0`,
`key2_part1` int(11) NOT NULL DEFAULT `0`,
`key2_part2` int(11) NOT NULL DEFAULT `0`,
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=4G
*************************** 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索引合併使用案例
資料表結構
mysql> show create table testG
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`key1_part1` int(11) NOT NULL DEFAULT `0`,
`key1_part2` int(11) NOT NULL DEFAULT `0`,
`key2_part1` int(11) NOT NULL DEFAULT `0`,
`key2_part2` int(11) NOT NULL DEFAULT `0`,
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使用案例
資料結構和資料和union案例中的一致。
使用索引合併的案例
mysql> explain select * from test where (key1_part1=1 and key1_part2=1) and (key2_part1=1 and key2_part2=1)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: index_merge
possible_keys: key1,key2
key: key2,key1
key_len: 8,8
ref: NULL
rows: 3
Extra: Using intersect(key2,key1); Using where; Using index
1 row in set (0.02 sec)
相關文章
- mysql 索引合併MySql索引
- Mysql索引的使用-組合索引+跳躍條件MySql索引
- 唯一索引,可以在索引列插入多個null嗎索引Null
- Mysql索引的使用 - 組合索引 + 範圍條件的處理MySql索引
- 聯合索引和多個單列索引使用中的索引命中情況及索引建立原則索引
- MySQL 配置索引頁的合併閾值MySql索引
- 又一個複合索引的SQL調優索引SQL
- mysql組合索引,abc索引命中MySql索引
- MySQL如何建立一個好索引?建立索引的5條建議【宇哥帶你玩轉MySQL 索引篇(三)】MySql索引
- MySql如何使用索引(一)MySql索引
- index merge合併索引Index索引
- Oracle索引合併coalesce操作Oracle索引
- MySQL 優化之 index_merge (索引合併)MySql優化Index索引
- MySQL複合索引探究MySql索引
- MySQL建立複合索引MySql索引
- oracle複合索引介紹(多欄位索引)Oracle索引
- SQL優化(二)(聯合索引的使用)SQL優化索引
- 【Mysql】InnoDB 中的聚簇索引、二級索引、聯合索引MySql索引
- Mysql索引使用MySql索引
- SQL優化案例一則--複合索引沒有被使用SQL優化索引
- MySQL 組合索引不生效MySql索引
- MySQL學習筆記:組合索引-索引下推MySql筆記索引
- MySQL單列索引和組合索引的區別MySql索引
- MySql索引使用策略MySql索引
- Mysql——index(索引)使用MySqlIndex索引
- MySQL索引和SQL調優MySql索引
- SQL最佳化案例一則--複合索引沒有被使用SQL索引
- SQL Server 索引結構及其使用(一)SQLServer索引
- 一個複合索引的優化案例索引優化
- Mysql索引以及使用索引注意事項MySql索引
- 【Mysql】一個簡易的索引方案MySql索引
- MySQL <唯一索引>MySql索引
- MySQL 聯合索引測試3MySql索引
- MySQL通過通用列索引來提供一個JSON列索引MySql索引JSON
- MySQL 調優之如何正確使用聯合索引MySql索引
- [zt] 聚集索引和非聚集索引(sql server索引結構及其使用)索引SQLServer
- 【原創】MySQL 模擬條件索引MySql索引
- MySQL單列索引和組合索引的區別介紹MySql索引