文档章节

数据库优化之索引

trayvon
 trayvon
发布于 2015/12/08 22:34
字数 2402
阅读 220
收藏 23

、引言

首先我们来思考一下什么是索引?索引的作用是什么?操作系统的文件索引和数据库的索引有什么不同?

什么是索引?对于这个问题我们可以打一个比喻,索引相对于文件的作用,就好比是目录相对于一本书的作用。所以它的作用也就显而易见了,就是为了查找,提高查找效率。是不是感觉不太有用,那再想一想你查字典的时候一页一页的找试一试,买一本最便宜的字典都要含着泪才能翻完。正常查找字典我们一般先找到部首的笔画,然后找到部首,再根据部首找到字,再根据字找到对应的页,这其实就是一个多级索引。所以说计算机科学里面的很多智慧来来自于生活。

接下来就是操作系统的文件索引和数据库的索引的区别了。一般操作系统都有一张索引表,因为一般操作系统的文件是无结构的字节系列,所以操作系统的索引表记录的是数据的逻辑块号和对应的物理块号。而数据库文件是有结构的记录,所以它可以由每一条记录的关键码来和物理块对应。

特别需要注意的是索引键对于的值是磁盘(外存)的物理地址,而不是内存中的逻辑地址。数据库在读取表的时候首先是先读取索引文件(可能数据文件本身就是索引文件,这和不同的实现方式相关,InnoDB就是这种实现)。然后根据索引表来读取数据。

、选择率

要理解利用索引对数据库查询做优化有一点非常重要,就是全表扫描和索引扫描的区别,索引下面的内容非常重要。

对数据库操作影响最大的就是IO操作。表的扫描操作就和IO密切相关。因为数据库一般都会通过操作系统的IO,操作系统中读取数据用两种非常重要方式:

  1. 顺序读取:就是读取磁盘上连续的块,速度快

  2. 随机读取:随机读是指访问的块是不连续的,需要磁盘的磁头不断移动。随机读的性能是远远低于顺序读的。

矛盾的问题来了,索引扫描是随机读取的,而全表扫描是顺序读取的。这明显不科学啊!明明我们利用索引是来做优化的。为什么用慢的随机读取呢?一个很重要的数据就是选择率,了解它你就会发现很多其他书籍或者文章中提到的利用索引优化的限制和技巧的原因了。

经常看见提醒要做有where条件上的列加上索引,为什么呢?where就意味着过滤,很多时候过滤意味着我们其实需要查询的数据量很小,例如登录时候的用户验证:

select 1 from user where username=’tom’ and password=sha1(’123456’)

我们只需要查找一条记录,如果我们有1亿条记录,如果做全表扫描按一半来算平均也要扫描5千万条记录。这需要多少次IO操作啊?而索引扫描只需要读取一条记录,基本上一次IO就可以搞定。所以当选择率很低的时候,就算是用索引的随机读取比顺序读取慢很多,但是也会比全表扫描效率高很多。

还有一些优化技巧提醒你在取值范围小的列上不要建立索引,也是因为选择率的问题。例如在性别字段上建立一个索引,然后读取数据:

select * from user where sex=’male’;

我们按男女比例均衡来算,至少也要选择一般的数据吧,就算加上一些特殊人群,也是几分之一的概率吧,如果数据量大,还不是就哭了。所以数据库的优化器会在执行查询计划的时候就会计算选择率,如果选择率过高,就直接放弃索引扫描,改用全表扫描。因为我们前面提过全表扫描用的顺序读取比索引扫描用的随机读取速度快。明显选择率高的时候我们读的数据多,用顺序读取的优势要大于用随机读取。

Hash索引和B+数索引

Hash索引相对比较简单,就是利用hash表来记录列的值和对应列存储的物理地址。它的效率和hash算法相关。

B+树的B代表的是Balance(平衡)而不是Binary(二叉),用B+树实现的 索引也不能定位到数据项,只能定位到页(见注1),这和B+树特点和实现相关,B+树也是树,也有节点,而MySQL数据库把一个节点的大小设置为一页,这也一次就可以读取一个节点。它有这很好的查询效率比O(n)好。

关于hash索引和B+树索引,如果你感兴趣可以参考后面给出的参考列表,这里就不做详细介绍了,下图是我截取的一张为什么有hash索引还要B+树索引的原因,因为认为可能你会用到,但是为了防止连接失效,所以截取了一张图。后面有原文连接,如果你感兴趣可以参考原文。

1:为什么还需要B+树索引

四 、实例

现在还是不太明显能够感受到索引对于数据库效率的影响是吗?没关系,我们用一个实例来分析一下使用索引和不使用索引的区别,然后对比一下使用索引的性能和不使用索引的性能差异。

假设我们有一张user表如下所示:

2user

user表的创建语句:

CREATE  TABLE IF NOT EXISTS `user` (
  `id` INT NOT NULL ,
  `name` CHAR(30) NOT NULL ,
  `phone` CHAR(11) NULL ,
  `address` VARCHAR(50) NULL ,
  `password` CHAR(40) NOT NULL ,
  `description` TEXT NULL ,
  PRIMARY KEY (`id`) )ENGINE = InnoDB;

user表只有一个主键索引,是数据库默认为主键加上的。我们理一下现在当我们想user表插入插入数据和查询数据时数据库是怎么做的。

插入一条记录时,数据库首先根据索引列id生成一个值作为索引的键值,把记录存放的物理地址作存放在相应的表记录中。索引表与数据存储在物理存储上的关系如下图2

3:数据库索引使用示意图

这也体现了索引的负效应,就是要维护索引表,当插入和删除记录的时候,要跟新索引表,这就带来了消耗。注意图2只是一个示意图,索引的结构并不一定是这样。

我们在来看查询数据,当我们只查询索引列的时候,就会使用索引扫描。否则使用顺序扫描读取。在MySQL中我们可以用explain语句来验证一下:

 

4:只查询索引

5:只查询非索引列

6:同时查询索引列和非索引列

我们看到了只有当我们查询索引列的时候才使用了索引,为什么都是一条记录,但是只有索引列才使用索引呢?这样和前文提到的顺序读取和随机读取的效率有关。

最后,如果可以在表中插入几百万条数据,然后来验证一下同样选择一行数据,利用索引扫描和利用全表扫描的效率差别。

、相关的参考

操作系统的IO管理介绍:http://www.94cto.com/index/Article/content/id/748.html

磁盘IO参数相关:http://storage.it168.com/a2011/0323/1169/000001169755.shtml

         B+树相关:http://blog.sina.com.cn/s/blog_4e0c21cc01010itp.html

Hash索引和B+树索引:

http://dev.mysql.com/doc/refman/5.6/en/index-btree-hash.html(官方文档)

http://blog.sina.com.cn/s/blog_6776884e0100pko1.html

操作系统分页相关:http://blog.chinaunix.net/uid-28458801-id-3505434.html

操作系统文件读取:http://blog.csdn.net/hguisu/article/details/6120991

理解B+树算法和Innodb索引:http://www.ruzuojun.com/topic/420.html

1:这里的页是指操作系统的页面大小,为什么要分页呢?简单的说就是我们的物理内存是有限的,我们要利用虚拟内存(磁盘等外存)。当我们的物理内存不够用的时候,就会把物理内存中的一些数据置换到虚拟内存中,但是置换多大的内存呢?为了方便管理,操作系统通常做法就是对内存就行分页,按页置换。读取数据时,一个块的大小一般也是一个页的大小。

© 著作权归作者所有

trayvon
粉丝 16
博文 130
码字总数 194002
作品 1
程序员
私信 提问
Android性能优化之数据库优化

最新最准确内容建议直接访问原文:性能优化之数据库优化 本文为性能优化的第一篇——数据库性能优化,原理适用于大部分数据库包括Sqlite、Mysql、Oracle、Sql server,详细介绍了索引(优缺点...

Trinea
2013/08/21
4.8K
1
SQL Server 性能优化之——系统化方法提高性能

阅读导航 1. 概述 2. 规范逻辑数据库设计 3. 使用高效索引设计 4. 使用高效的查询设计 5. 使用技术分析低性能 6. 总结 1. 概述 在比较大的范围内找出能够大幅提高性能的区域,并且专注于分析...

长平狐
2013/06/17
705
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
PHPer面试指南-MySQL 篇

本书的 GitHub 地址:https://github.com/todayqq/PHPerInterviewGuide 什么是索引,作用是什么?常见索引类型有那些?Mysql 建立索引的原则? 索引是一种特殊的文件,它们包含着对数据表里所...

angkee
2018/01/24
0
0
为 MySQL 查询优化选择最佳索引

我们的许多用户、开发者和数据库管理员不断向我们的团队咨询有关 EverSQL 的索引推荐算法。 所以,我们决定写一些这方面的内容。 本教程不会详细介绍该算法的所有内部特性,而是要简单地说明...

oschina
2018/02/05
3.7K
6

没有更多内容

加载失败,请刷新页面

加载更多

浅析大数据 学习大数据后能做什么

大数据时代的到来使得大数据开发人才迎来了前所未有的机遇和挑战!一个绝佳的入行机会摆在了众人面前!于是,很多人都在打听,大数据到底有何应用?可以用来做什么?好程序员今天就为大家作出...

好程序员IT
26分钟前
1
0
C# USB视频人脸检测

此程序基于 虹软人脸识别进行的开发 SDK下载地址:https://ai.arcsoft.com.cn/ucenter/user/reg?utm_source=csdn1&utm_medium=referral 前提条件 从虹软官网下载获取ArcFace引擎应用开发包,...

是哇兴哥棒棒哒
37分钟前
2
0
Vagrant虚拟机硬盘扩容

# 停止虚拟机vagrant halt <machine_name># 进入VirtualBox VMs目录,查看并记录原磁盘uuid,留作后用vboxmanage showhdinfo box-disk1.vmdk# 克隆磁盘,vmdk格式无法调整大小,需要...

sskill
39分钟前
1
0
分布式商业萌芽,银行迎来发展新机遇

01 分布式商业萌芽,银行迎来发展新机遇 金融界:近几年区块链的热度经历了过山车般的转折。目前追逐区块链的资本也开始冷静下来,于此同时,各大商业银行对区块链的研究应用也越来越多。您认...

Java领航员
44分钟前
3
0
Spring系列教程六: Spring jdbcTemplate在Dao中的使用

概念 Spring中的jdbcTemplate的主要作用是实现数据的交互,下面我们就在dao层中如何使用jdbctemplate写测试案例 项目目录如下 基于xml实现jdbctemplate 这里我们使用的是JdbcDaoSupport这个类...

我叫小糖主
48分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部