文档章节

物理查询优化之索引

sitaluoduoxi
 sitaluoduoxi
发布于 2016/07/26 22:13
字数 1747
阅读 4
收藏 0

索引是建立在表上的,本质上是通过索引直接定位表的物理元组,加速数据获取的方式,所以索引优化应该归属到物理查询优化阶段。

1. 如何利用索引

通常查询优化器所使用索引的原则如下:

 索隐裂座位条件出现在WHERE、HAVING、ON 子句中,这样有利于索引过滤元组;

  索引列是被链接的表对象的列且存在于连接条件中;

 还有一些情况可以使用索引,如排序操作、在索引列上球MIN、MAX等。

1.1 示例:

create table A(a1 int unique,a2 varchar(10),a3 int); (a1列上创建隐含索引)

  对表做查询,没有列对象作为过滤条件(如出现在WHERE子句中),只能顺序扫描

    mysql> explain extended select * from A;
  +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
  +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
  |  1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
  +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+

   type = ALL 表示 顺序扫描或范围扫描,不是用索引,顺序扫描,直接读取表上的数据(访问数据文件)。

对表做查询,有列对象列且索引列作为过滤条件,可作为索引扫描

    mysql> explain extended select * from A where a1 >2;
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | A     | range | a1            | a1   | 5       | NULL |    1 |   100.00 | Using index condition |
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+

    t ype = range 表示基于索引做范围扫描。Using index condition 表示尝试通过索引来获取

对表做查询,索引列被运算符“-” 处理,查询优化器不能再执行前进行取反操作,不可利用索引,只能顺序扫描。

select A.* from A where -A.a1 = -2; (不能利用索引)

select A.* from A where  A.a1 =  2; (利用索引)

对表做查询,选择条件不包括索引列,只能顺序扫描

 select A.* from A where A.a3 = 2;

 索引列参与了运算,不能使用索引

 select A.*from A whereA.a1 + A.a3 = 2;

 select A.*from A whereA.a1= A.a3 + 2;

 select A.*from A whereA.a1< 1+2;(可以使用索引)

 查询时,索引列对象座位过滤条件时,操作符是 <> 时,不可做索引扫描,如果是 < 或者 > 时,可以使用索引

    mysql> explain extended select A.* from A where A.a1 <> 2;(不使用索引)
    +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | A     | ALL  | a1            | NULL | NULL    | NULL |    2 |   100.00 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

    mysql> explain extended select A.* from A where A.a1 > 2;(使用索引)
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | A     | range | a1            | a1   | 5       | NULL |    1 |   100.00 | Using index condition |
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+

 查询时,索引列对象座位过滤条件时,操作符是 between - and 时,可以使用索引

    mysql> explain extended select A.* from A where A.a1 between 1 and 2;
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | A     | range | a1            | a1   | 5       | NULL |    2 |   100.00 | Using index condition |
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+


1.2 所以,对于索引列,索引可用的条件如下:

在WHERE, JOIN/ON/HAVING 的条件中出现 “key <op> 常量” 格式的条件子句(索引列不能参与带有变量表达式的运算)

操作符不能是<>操作符(不等于操作符在任何类型的列上都不能使用索引)

索引列的选择率越低,索引越有效,通常认为选择率小鱼0.1的索引扫描效果会好一些


2. 索引列的位置对使用索引的影响

2.1 对目标列、WHERE 等条件子句的影响

索引列出现在目标列通常不可以使用索引(但不是全部情况),索引列出现目标列对查询语句的优化没有好的影响PostgreSQL,如:

select A.1a from A;(PostgreSQL

注意:具体的数据库可能存在差别,在我的mysql环境是可以用到索引的:如下:

mysql> explain extended select A.a1 from A;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | A     | index | NULL          | a1   | 5       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+

聚集函数MIN/MAX 用在索引列上,出现在目标列中,可以使用索引PostgreSQL。如:

select MAX(A.a1) from A;(PostgreSQL

注意:具体的数据库可能存在差别,在我的mysql环境没有用到索引:如下:

mysql> explain extended select Max(A.a1) from A;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
()

索引出现在JOIN/ON 子句中,作为连接条件,不可使用索引。但是索引列出现限制条件满足“key<op>常量”格式可用到索引。

 create table B(b1 int unique,b2 varchar(10),b3 int);

mysql> explain extended select A.*,B.* from A join B on a1 = b1;
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | B     | ALL  | b1            | NULL | NULL    | NULL      |    1 |   100.00 | Using where |
|  1 | SIMPLE      | A     | ref  | a1            | a1   | 5       | test.B.b1 |    1 |   100.00 | NULL        |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------+-------------+

执行计划表示 对B表进行了全表扫描,对A表连接时使用了索引。

mysql> explain extended select A.*,B.* from A join B on a1 = b1 and a1 = 1;
+----+-------------+-------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | A     | const | a1            | a1   | 5       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | B     | const | b1            | b1   | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+-------+------+----------+-------+
对A和B都使用而索引。

还有些关于order by,group by 的,发现数据库不同的话存在差异,就不写了,

当然上面的也可能存在差异,具体的话在用的时候看下执行计划就知道了。

3. 联合索引对索引使用的影响

create table E(e1 int,e2 varchar(10),e3 int,e4 int,primary key(e1,e3,e4));

使用联合索引的前缀部分索引键,可触发索引的使用;

使用部分索引键,但不是联合索引的前缀部分,不可出发索引的使用。

select * from E where e1 = 1;

select * from E where e1 = 1 and e3 =1 ;(select * from E where e1 = 1 or e3 =1 ;这种情况不会用到索引)

select * from E where e1 = 1 and e3 =1 and e4 = 1;

以上三个都是可以使用索引的:

mysql> explain extended select * from E where e1 = 1;
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | E     | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> explain extended select * from E where e1 = 1 and e3 =1;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | E     | ref  | PRIMARY       | PRIMARY | 8       | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> explain extended select * from E where e1 = 1 and e3 =1 and e4 = 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+

注意:这里第三个看上去没有使用到索引,那是因为 e1 = 1 and e3 =1这两个条件过滤后就没有数据了,后面的e4=1不用看也知道返回false了。


下面的这种是不会用到索引的:

mysql> explain extended select * from E where e3 = 1 and e4 = 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | E     | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

另外这种情况:虽然使用到了索引,但是只是使用到了e1这一列上的索引,e4=1 这里没有用到索引。

mysql> explain extended select * from E where e1 = 1 and e4 = 1;
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | E     | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------+


4.多个索引对索引使用的影响




© 著作权归作者所有

共有 人打赏支持
sitaluoduoxi
粉丝 0
博文 33
码字总数 24992
作品 0
广州
后端工程师
私信 提问
SQL优化基础 使用索引(一个小例子)

按照本文操作和体会,会对sql优化有个基本最简单的了解,其他深入还需要更多资料和实践的学习: 1. 建表: 复制代码 代码如下: create table siteuser ( id int IDENTITY(1,1) PRIMARY KEY,...

鱼煎
2015/08/19
0
0
数据库优化之索引

一 、引言 首先我们来思考一下什么是索引?索引的作用是什么?操作系统的文件索引和数据库的索引有什么不同? 什么是索引?对于这个问题我们可以打一个比喻,索引相对于文件的作用,就好比是...

trayvon
2015/12/08
160
0
索引的优点和缺点,设计数据库的人员必看--转载

一、为什么要创建索引呢(优点)? 这是因为,创建索引可以大大提高系统的性能。 第一, 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 第二, 可以大大加快数据的检索速度,...

wsj
2010/11/08
0
0
索引的优点和缺点,设计数据库的人员必看

转自:http://my.oschina.net/kingfloger/blog/9644 面试的时候常问人索引的优缺点,今天看到开源中国中有这么一篇好文章,故转之一、为什么要创建索引呢(优点)?二、建立方向索引的不利因...

flynewton
2010/11/10
0
1
提高MSSQL数据库读取速度,降低CPU损耗(转载)

当你的MSSQL数据库cpu很高的时候,如何去定位损耗cpu的SQL语句,并解决损耗问题?接下来我为大家实际解决一下这个问题: 1.首先我们要用查询追踪器追踪耗费cpu的sql语句,如图设置: 打开查询...

Yamazaki
2012/06/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

C++ vector和list的区别

1.vector数据结构 vector和数组类似,拥有一段连续的内存空间,并且起始地址不变。 因此能高效的进行随机存取,时间复杂度为o(1); 但因为内存空间是连续的,所以在进行插入和删除操作时,会造...

shzwork
今天
4
0
Spring之invokeBeanFactoryPostProcessors详解

Spring的refresh的invokeBeanFactoryPostProcessors,就是调用所有注册的、原始的BeanFactoryPostProcessor。 相关源码 public static void invokeBeanFactoryPostProcessors(Configu......

cregu
昨天
4
0
ibmcom/db2express-c_docker官方使用文档

(DEPRECIATED) Please check DB2 Developer-C Edition for the replacement. What is IBM DB2 Express-C ? ``IBM DB2 Express-C``` is the no-charge community edition of DB2 server, a si......

BG2KNT
昨天
4
0
Ubuntu 18.04.2 LTS nvidia-docker2 : 依赖: docker-ce (= 5:18.09.0~3-0~ubuntu-bionic)

平台:Ubuntu 18.04.2 LTS nvidia-docker2 版本:2.0.3 错误描述:在安装nvidia-docker2的时候报dpkg依赖错误 nvidia-docker2 : 依赖: docker-ce (= 5:18.09.0~3-0~ubuntu-bionic) 先看一下依......

Pulsar-V
昨天
4
0
学习笔记1-goland结构体(struct)

写在前面:若有侵权,请发邮件by.su@qq.com告知。 转载者告知:如果本文被转载,但凡涉及到侵权相关事宜,转载者需负责。请知悉! 本文永久更新地址:https://my.oschina.net/bysu/blog/3036...

不最醉不龟归
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部