文档章节

MySQL优化之BTree索引使用规则

EverythingIsGoneBeOK
 EverythingIsGoneBeOK
发布于 2016/01/30 20:38
字数 726
阅读 25
收藏 0

MySQL优化之BTree索引使用规则

从一道题开始分析:

假设某个表有一个联合索引(c1,c2,c3,c4)以下那个只能使用该联合索引的c1,c2,c3部分

explain select * from t where c1='a1' and c2='a2' and c4='a4' and c3='a3';

explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c3;

explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c1;

explain select * from t where c1='a1' and c4='a4'group by c3,c2;

explain select * from t where c1='a1' and c4='a4'group by c2,c3;

explain select * from t where c1='a1' and c4='a4'order by c2,c3;

explain select * from t where c1='a1' and c5='a5' order by c2,c3;



创建表后插入数据:

insert into t
values
('a1','a2','a3','a4','a5'),
('b1','b2','b3','b4','b5');

添加索引:

alter table t add index c1234(c1,c2,c3,c4);

mysql> explain select * from t where c1='a1' and c2='a2' and c4='a4' and c3='a3’;(MySQL执行时会改变其中的位置,c3会在c4的前边)

+----+-------------+-------+------+---------------+-------+---------+-------------------------+------+-----------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref                     | rows | Extra                 |

+----+-------------+-------+------+---------------+-------+---------+-------------------------+------+-----------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 168     | const,const,const,const | 1    | Using index condition |

+----+-------------+-------+------+---------------+-------+---------+-------------------------+------+-----------------------+

1 行于数据集 (0.05 秒)

mysql> explain select * from t where c2='a1' and c3='a4’; (根据索引的左前缀规则,c2,c3部分没有索引)

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| 1  | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 4    | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 行于数据集 (0.08 秒)

mysql> explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c3;(根据索引的左前缀规则,order by部分没有索引)

+----+-------------+-------+------+---------------+-------+---------+-------------+------+------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref         | rows | Extra                              |

+----+-------------+-------+------+---------------+-------+---------+-------------+------+------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 84      | const,const | 1    | Using index condition; Using where |

+----+-------------+-------+------+---------------+-------+---------+-------------+------+------------------------------------+

1 行于数据集 (0.08 秒)



mysql> explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c1;(根据索引的左前缀规则,order by使用了索引,可以存在where与order by同时使用索引的情况)

+----+-------------+-------+------+---------------+-------+---------+-------------+------+-----------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref         | rows | Extra                 |

+----+-------------+-------+------+---------------+-------+---------+-------------+------+-----------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 84      | const,const | 1    | Using index condition |

+----+-------------+-------+------+---------------+-------+---------+-------------+------+-----------------------+

1 行于数据集 (0.09 秒)



(以下四组对比,说明在group by 和order by 中,使用索引的顺序,可以避免使用临时表和filesort)

mysql> explain select * from t where c1='a1' and c4=‘a4' group by c3,c2;

+----+-------------+-------+------+---------------+-------+---------+-------+------+---------------------------------------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                                                               |

+----+-------------+-------+------+---------------+-------+---------+-------+------+---------------------------------------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 42      | const | 1    | Using index condition; Using where; Using temporary; Using filesort |

+----+-------------+-------+------+---------------+-------+---------+-------+------+---------------------------------------------------------------------+

1 行于数据集 (0.09 秒)



mysql> explain select * from t where c1='a1' and c4='a4'group by c2,c3;

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                              |

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 42      | const | 1    | Using index condition; Using where |

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

1 行于数据集 (0.12 秒)



mysql> explain select * from t where c1='a1' and c4='a4'order by c2,c3;

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                              |

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 42      | const | 1    | Using index condition; Using where |

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

1 行于数据集 (0.13 秒)



mysql> explain select * from t where c1='a1' and c5='a5' order by c3,c2;(使用的c1索引)

+----+-------------+-------+------+---------------+-------+---------+-------+------+----------------------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                                              |

+----+-------------+-------+------+---------------+-------+---------+-------+------+----------------------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 42      | const | 1    | Using index condition; Using where; Using filesort |

+----+-------------+-------+------+---------------+-------+---------+-------+------+----------------------------------------------------+

1 行于数据集 (0.15 秒)


© 著作权归作者所有

EverythingIsGoneBeOK
粉丝 0
博文 7
码字总数 16196
作品 0
朝阳
私信 提问
关于mysql 删除数据后物理空间未释

OPTIMIZE TABLE 当您的库中删除了大量的数据后,您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE 是指对表进行优化。如果已经删除了表的一...

运维技术
2015/02/05
0
0
实例说明optimize table在优化mysql时很重要

实例说明optimize table在优化mysql时很重要 张映 发表于 2011-03-07 分类目录: mysql 今天在看CU的时候,发现有人问有关optimize来表优化的问题,当年因为这个问题,困扰我很长一段时间,今...

netmouse
2014/03/20
0
0
mysql联合索引 sql索引使用

注意:Index(Name,Age)表示在Name,Age两列上建立联合索引 由于索引对数据库的查询性能有着至关重要的影响,下面是我的一些总结和体会: 一个查询一次只能使用一个索引:select name fro...

五大三粗
2015/04/30
0
0
mysql学习(2)索引的本质

问题:SQL查询慢怎么办? 优化手段,加索引。 索引是帮助MYSQL高效的获取数据的排好序的数据结构。 问题:索引结构为什么使用Btree而不使用二叉树,红黑树或者HASH结构? 二叉树的特性,右边...

太猪-YJ
04/23
0
0
PostgreSQL 快速给指定表每个字段创建索引 - 2

标签 PostgreSQL , 索引 , 所有字段 , 并行创建单个索引 , maxparallelmaintenance_workers , 异步调用 , dblink , 并行创建多个索引 , adhoc查询 背景 PostgreSQL 支持丰富的类型、索引,统...

德哥
04/14
0
0

没有更多内容

加载失败,请刷新页面

加载更多

系列一、入门教程web端实现地图功能

废话不多说,社会我多多 实现步骤如下 第一步、在高德api注册账户 搜索高德api点击进入官网,自己注册一个账号,你懂得怎么注册撒 点击进入下图画框位置,来到官网api入门教程 第二步、按照以...

我叫小糖主
27分钟前
12
0
springboot统一校验validator实现

第一步: pom.xml需引入spring-boot-start-web依赖,其中包含validator框架包 <!--Spring Boot Web依赖--><dependency> <groupId>org.springframework.boot</groupId> <artifact......

zzx10
29分钟前
3
0
vue组件系列-预览、放大、缩小、旋转

这个用的是别人的,如果有问题,估计改起来会很纠结。 安装 npm install v-viewer --save 卸载 npm uninstall v-viewer 注册 在main.js中 // The Vue build version to load with the `impor...

轻轻的走过
30分钟前
3
0
Taro Input输入内容无法绑定state问题

在onInput事件中,返回内容,返回内容即是输入框内容 例如只能输入一个小数点: <Input type='digit' placeholder='带小数点的数字键盘' value={this.state.advance} onInput={ e => this.ch......

步步登高
32分钟前
2
0
Windows10远程连接CentOS7(搭建Xrdp服务器)

Windows10远程连接CentOS7(搭建Xrdp服务器) 听语音 浏览:0 | 更新:2018-02-11 12:56 1 2 3 4 5 6 7 分步阅读 通过VNC或Xdmcp的方式远程连接linux图形桌面,虽然都很方便,但有个缺点就是...

linjin200
32分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部