mysql索引优化

为什么需要索引(Why is it needed)?

当数据保存在磁盘类存储介质上时,它是作为数据块存放。这些数据块是被当作一个整体来访问的,这样可以保证操作的原子性。硬盘数据块存储结构类似于链表,都包含数据部分,以及一个指向下一个节点(或数据块)的指针,不需要连续存储。 记录集只能在某个关键字段上进行排序,所以如果需要在一个无序字段上进行搜索,就要执行一个线性搜索(Linear Search)的过程,平均需要访问N/2的数据块,N是表所占据的数据块数目。如果这个字段是一个非主键字段(也就是说,不包含唯一的访问入口),那么需要在N个数据块上搜索整个表格空间。 但是对于一个有序字段,可以运用二分查找(Binary Search),这样只要访问log2 (N)的数据块。这就是为什么性能能得到本质上的提高。

什么是索引(What is indexing)?

索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引,将创建另外一个数据结构,包含字段数值以及指向相关记录的指针,然后对这个索引结构进行排序,允许在该数据上进行二分法排序。 副作用是索引需要额外的磁盘空间,对于MyISAM引擎而言,这些索引是被统一保存在一张表中的,这个文件将很快到达底层文件系统所能够支持的大小限制,如果很多字段都建立了索引的话。

索引如何工作(How does it work?)

首先,我们建立一个示范数据库表: 字段名 数据类型 大小 id (Primary key) Unsigned INT 4 bytes firstName Char(50) 50 bytes lastName Char(50) 50 bytes emailAddress Char(100) 100 bytes 注意:使用char是为了指定准确的磁盘占用大小。这个示范数据库包含500万行,而且没有索引。我们将分析一些查询语句的性能,一个是使用主键id(有序)查询,一个是使用firstName(非关键无序字段)。 例1 我们的示范数据库有r=5,000,000条记录,每条记录长度R=204字节而且使用MyISAM引擎存储(默认数据块大小为B=1024字节),这张表的块因子(blocking factor)会是bfr = (B/R) = 1024/204 = 5 条记录每磁盘数据块。保存这张表所需要的磁盘块为N = (r/bfr) = 5000000/5 = 1,000,000 blocks。 在id字段上的线性搜索平均需要N/2 = 500,000块访问来找到一条记录假设id字段是查询关键值,不过既然id字段是有序的,可以执行一个二分查询,这样平均只需要访问log2 (1000000) = 19.93 = 20 个数据块。我们马上就看到了极大的提高。 现在firstName字段既不是有序的,无法执行二分搜索,数值也不具有唯一性,所以对这张表的查找必须到最后一个记录即全表扫描N = 1,000,000个数据块访问。这就是索引用来改进的地方。 假如索引记录只包含一个索引列以及一个指向原记录数据的指针,那么它显而易见会比原记录(多列)要小。所以索引本身所需要的磁盘块要更少,扫描数目也少。firstName索引表结构如下: Field name Data type Size on disk firstName Char(50) 50 bytes (record pointer) Special 4 bytes 注意: MySQL里的指针按表大小的不同分别可能是 2, 3, 4 或 5 个字节。 例2 假设我们的数据库有r = 5,000,000 条记录,建立了一个长R = 54字节的索引,并且使用默认磁盘块大小为1,024字节。那么该索引的块因子为bfr = (B/R) = 1024/54 = 18 条记录每磁盘块。容纳这个索引表总共需要的磁盘块为N = (r/bfr) = 5000000/18 = 277,778 块。

现在使用FirstName字段来进行搜索就可以利用索引来提高性能。这允许使用一个二分查找,平均log2 (277778) = 18.08 -> 19次数据块访问。找到实际记录的地址,这需要进一步的块读取,这样总数达到19 + 1 = 20次数据块访问,这和非索引表的数据块访问次数有天壤之别。

什么时候使用索引(When should it be used?)

鉴于创建索引需要额外的磁盘空间(上面的例子需要额外的277778个磁盘块),以及太多的索引会导致文件系统大小限制所产生的问题,所以对哪些字段建立索引,什么情况下使用索引,需要审慎考虑。

由于索引只是用来加速数据查询,那么显然对只是用来输出的字段建立索引会浪费磁盘空间以及发生插入、删除操作时的处理时间,所以这种情况下应该尽量避免。此外鉴于二分搜索的特性,数据的基数或独立性是很重要的。在基数为2的字段上建立索引,将把数据分割一半,而基数为1000则将返回大约1000条记录。低基数的二分查找效率将降低为一个线性排序,而且查询优化器可能会在基数小于记录数某个比例时(如30%)的情况下将避免使用索引而直接查询原表,所以这种情况下的索引浪费了空间。

原文发布于微信公众号 - java一日一条(mjx_java)

原文发表时间:2017-03-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏北京马哥教育

SQL的几种连接:内连接、左联接、右连接、全连接、交叉连接

SQL连接可以分为内连接、外连接、交叉连接。 数据库数据: ? ? book表 ...

34740
来自专栏Java帮帮-微信公众号-技术文章全总结

JavaWeb05- 就这几步轻松操作MySQL数据库!

mysql-1 一.数据库 1. 数据库介绍 数据库就是存储数据的仓库,其本质是一个文件系统,数据按照特定的格式将数据存储起来,用户可以通过sql语句对数据库中...

27750
来自专栏Java学习之路

Hibernate学习---关联关系映射

关联关系是用到的最多的一种关系,非常重要,在内存中反映为实体关系,映射到DB中主键外键关系,实体间的关联,即对外键的维护,关联关系的发生,即对外键数据的改变。 ...

32560
来自专栏人工智能LeadAI

深度学习之主流数据库 | MySQL基础

这篇文章主要是讲一下常见的MySQL的安装,和基本操作。适合完全没有MySQL知识但是又急需一些MySQL知识的童靴作为快速入门使用。 背景与安装 背景不用多说...

54560
来自专栏数据库

干货!超过500行的Mysql学习笔记

本文为作者初学Mysql时做的笔记,囊括了Mysql相关基本知识,内容较多超过500行笔记,希望对大家有帮助。 ? /* 启动MySQL */ net star...

22660
来自专栏栗霖积跬步之旅

java多线程编程核心技术——第六章总结

1.0立即加载/“饿汉式”   立即加载:实用类的时候已经将对象创建完毕,常见的实现方法就是直接new实例化。   注:是在调用方法前,就已经实例化了(通常是...

19360
来自专栏抠抠空间

Django之ORM其他骚操作

Django ORM执行原生SQL # extra # 在QuerySet的基础上继续执行子语句 # extra(self, select=None, wher...

30450
来自专栏java架构师

【SQL Server】系统学习之二:索引优化

页大小8192个字节,行限制为8060字节(大型对象除外)。 包含varchar nvarchar varbinary sql_variant(8012,obj...

25160
来自专栏架构师小秘圈

高效sql性能优化极简教程

一,sql性能优化基础方法论 对于功能,我们可能知道必须改进什么;但对于性能问题,有时我们可能无从下手。其实,任何计算机应用系统最终队可以归结为: cpu消耗 ...

56550
来自专栏数据小魔方

MySQL数据库基础——本地文件交互

从这一篇开始,大概会花四五篇的内容篇幅,归纳整理一下之前学过的SQL数据库,一来可以为接下来数据分析工作提前巩固基础,二来把以前学的SQL内容系统化、结构化。 ...

357120

扫码关注云+社区

领取腾讯云代金券