Mysql联合索引最左匹配原则

[toc]

为什么使用联合索引

以联合索引(a,b,c)为例

  • 建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。
  • 覆盖(动词)索引。同样的有联合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=xxx and b = xxx。那么MySQL可以直接通过遍历索引取得数据，而无需读表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
  • 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知

1. 示例表结构

CREATE TABLE `test_user` (
  `name`     varchar(20) DEFAULT NULL,
  `province` int(11)     DEFAULT NULL,
  `sex`      varchar(20) DEFAULT NULL,
  `birthday` int(11)     DEFAULT NULL,
  `phone`    double      DEFAULT NULL
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE INDEX test_user_name_phone_province_index
  ON test_user (name, phone, province);

2. 执行计划说明

以下全部详细解析explain各个属性含义：

+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+

各属性含义：

  • id: 查询的序列号

  • select_type: 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询

    • SIMPLE：查询中不包含子查询或者UNION
    • 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
    • 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

  • table: 输出的行所引用的表

  • type: 访问类型

    • ALL: 扫描全表
    • index: 扫描全部索引树
    • range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
    • ref: 使用非唯一索引或非唯一索引前缀进行的查找
    • （eq_ref和const的区别：）
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    • const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询- 的表只有一行的情况下， 使用system。
    • NULL: 不用访问表或者索引，直接就能得到结果，如select 1 from test where 1

  • key: 显示MySQL实际决定使用的索引。如果没有索引被选择，是NULL

  • key_len: 使用到索引字段的长度
    注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

  • ref: 显示哪个字段或常数与key一起被使用

  • rows: 这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的

  • Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。

  • Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。

  • Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。

3. 执行计划分析

mysql> explain select *
    ->         from test_user
    ->         where name = '张三';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 63      | const |   11 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

使用了一个const

mysql> explain select *
    ->         from test_user
    ->         where phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

全表扫描

mysql> explain select *
    ->         from test_user
    ->         where province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

全表扫描

mysql> explain select *
    ->         from test_user
    ->         where phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

全表扫描

mysql> explain select *
    ->         from test_user
    ->         where province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

全表扫描

mysql> explain select *
    ->         from test_user
    ->         where name = '张三' and province = '532130';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 63      | const |   11 |    10.00 | Using index condition |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

使用了一个const

mysql> explain select *
    ->         from test_user
    ->         where name = '张三' and phone = '13546294373';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 72      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

使用了两个const

mysql> explain select *
    ->         from test_user
    ->         where  phone = '13546294373' and province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |     1.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

全表扫描

mysql> explain select *
    ->         from test_user
    ->         where     province = '532130' and phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |     1.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

全表扫描

mysql> explain select *
    ->         from test_user
    ->         where name = '张三' and phone = '13546294373'  and province = '532130';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 77      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

使用了到了 三个const

4. 现象总结

对于联合索引

CREATE INDEX test_user_name_phone_province_index
  ON test_user (name, phone, province);

在查询中我们能用到的索引的是

  • name
  • name phone
  • name phone province

对于其他顺序,或者其他字段我们不能使用该联合索引,这个就是mysql联合索引原则

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 213,014评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,796评论 3赞 386
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,484评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,830评论 1赞 285
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,946评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,114评论 1赞 292
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,182评论 3赞 412
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,927评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,369评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,678评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,832评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,533评论 4赞 335
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,166评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,885评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,128评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,659评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,738评论 2赞 351