mysql索引与优化文档Word文件下载.docx

mysql索引与优化文档Word文件下载.docx

《mysql索引与优化文档Word文件下载.docx》由会员分享，可在线阅读，更多相关《mysql索引与优化文档Word文件下载.docx（13页珍藏版）》请在冰豆网上搜索。

信息科学与工程学院

指导老师：

杨金民

2015年12月15日

索引与优化

一、索引概念

索引就是加快检索表中数据的方法。

数据库的索引类似于书籍的索引。

在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。

在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

1、索引的优点

我们先来看这样一个例子，不带索引的表仅仅是一个无序的数据行集合。

例如,图1显示的ad表就是不带索引的表,因此如果需要查找某个特定的公司,就必须检查表中的每个数据行看它是否与目标值相匹配。

这会导致一次完全的数据表扫描,这个过程会很慢。

如果这个表很大,但是只包含少量的符合条件的记录,那么效率会非常低。

图2是同样的一张数据表,但是增加了对ad表的company_num数据列的索引。

这个索引包含了ad表中的每个数据行的条目,但是索引的条目是按照company_num值排序的。

现在不是逐行查看以搜寻匹配的数据项,而是使用索引。

假设我们查找公司13的所有数据行,开始扫描索引并找到了该公司的三个值。

接着碰到了公司14的索引值,它比我们正在搜寻的值大。

索引值是排过序的,因此当读取了包含14的索引记录的时候,就知道再也不会有更多的匹配记录,可以结束查询操作了。

因此使用索引获得的功效是:

找到了匹配的数据行在哪儿终止,并能够忽略其它的数据行。

另一个功效来自使用定位算法查找第一条匹配的条目,而不需要从索引头开始执行线性扫描（例如,二分搜索就比线性扫描要快一些）。

通过使用这种方法,可以快速地定位第一个匹配的值,节省了大量

的搜索时间。

为什么不对数据行进行排序从而省掉索引,这样不是也能实现同样的搜索速度的改善吗?

如果表只有一个索引,这样做也可能达到相同的效果。

但是如果添加第二个索引,那么就无法一次使用两种不同方法对数据行进行排序了（例如,你可能希望在顾客名称上建立一个索引,在顾客ID号上建立另外一个索引）。

把与数据行相分离的条目作为索引解决了这个问题,它允许创建多个索引。

此外,索引中的行一般也比数据行短一些。

当你插人或者删除新的值的时候，移动较短的索引值比移动较长数据行的排序次序更容易。

2、索引的缺点

索引在数据库中的缺陷主要表现在以下几个方面：

1创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

3当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

二、索引与优化

1、选择索引的数据类型

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。

通常来说，可以遵循以下一些指导原则：

（1）越小的数据类型通常更好：

越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

（2）简单的数据类型更好：

整型数据比起字符，处理开销更小，因为字符串的比较更复杂。

在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；

以及用整型数据类型存储IP地址。

（3）尽量避免NULL：

应该指定列为NOTNULL，除非你想存储NULL。

在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。

你应该用0、一个特殊的值或者一个空串代替空值。

2、索引分类

在MySQL中，主要有四种类型的索引，分别为：

B-Tree索引，Hash索引，Fulltext索引和R-Tree索引。

我们主要分析B-Tree索引。

B-Tree索引是MySQL数据库中使用最为频繁的索引类型，除了Archive存储引擎之外的其他所有的存储引擎都支持B-Tree索引。

Archive引擎直到MySQL5.1才支持索引，而且只支持索引单个AUTO_INCREMENT列。

不仅仅在MySQL中是如此，实际上在其他的很多数据库管理系统中B-Tree索引也同样是作为最主要的索引类型，这主要是因为B-Tree索引的存储结构在数据库的数据检索中有非常优异的表现。

一般来说，MySQL中的B-Tree索引的物理文件大多都是以BalanceTree（平衡树）的结构来存储的，也就是所有实际需要的数据都存放于Tree的LeafNode（叶子节点），而且到任何一个LeafNode的最短路径的长度都是完全相同的，所以我们大家都称之为B-Tree索引。

当然，可能各种数据库（或MySQL的各种存储引擎）在存放自己的B-Tree索引的时候会对存储结构稍作改造。

如Innodb存储引擎的B-Tree索引实际使用的存储结构实际上是B+Tree，也就是在B-Tree数据结构的基础上做了很小的改造，在每一个LeafNode上面出了存放索引键的相关信息之外，还存储了指向与该LeafNode相邻的后一个LeafNode的指针信息（增加了顺序访问指针），这主要是为了加快检索多个相邻LeafNode的效率考虑。

下面主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式：

●MYISAM索引实现

对于MyISAM数据表，该表的数据行保存在一个数据文件中，索引值保存在索引文件中。

一个数据表上可能有多个索引，但是它们都被存储在同一个索引文件中。

索引文件中的每个索引都包含一个排序的键记录（它用于快速的访问数据文件）数组。

1.主键索引

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

下图是MyISAM主键索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primarykey）示意。

可以看出MyISAM的索引文件仅仅保存数据记录的地址。

2.辅助索引

在MyISAM中，主索引和辅助索引（Secondarykey）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

●InnoDB索引实现

InnoDB也使用B+Tree作为索引结构，但具体实现却和MYISAM截然不同。

InnoDB使用单个的数据表空间，在表空间中管理所有InnoDB表的数据和索引存储。

可以把InnoDB配置为每个表都在自己的表空间中创建，但是即使是这样，数据表的数据和索引也存储在同一表空间文件中。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。

这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

InnoDB的所有辅助索引都引用主键作为data域。

例如，下图为定义在Col3上的一个辅助索引：

InnoDB表是基于聚簇索引建立的。

因此InnoDB的索引能提供一种非常快速的主键查找性能。

不过，它的辅助索引（SecondaryIndex，也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。

如果想在表上定义、很多索引，则争取尽量把主键定义得小一些。

InnoDB不会压缩索引。

文字符的ASCII码作为比较准则。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：

首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

3、索引使用

我们来看一个索引使用的实例。

从上面的介绍，了解到MyISAM的每个表都对应在硬盘上有三个文件。

table_name.frm保存表的定义、table_name.myd保存表数据、table_name表的索引。

●创建数据库db_index_MyISAM，设置当前默认引擎MyISAM。

这就创建了一个名为db_index_MyISAM的数据库，那么这个时候想看一下这个数据库是怎么保存的，有不知道数据库文件保存在哪个目录，怎么办呢？

下面的这条命令可以用到。

●显示数据文件保存路径。

这个时候，可以看到，只有一个opt文件。

文件用来存储当前数据库的默认字符集和字符校验规则。

用记事本打开可以看到：

default-character-set=latin1

default-collation=latin1_swedish_ci

●创建数据表employees，不带索引。

找到上述，数据库文件存放目录，即创建一个表后的数据库文件如下：

使用python写的小代码插入数据。

那么就可以对比有无索引的效果了。

使用索引的效果，我们通过查找的行数来断定。

比如我查询employees中的第491000编号的员工。

从结果来看似乎不对，employees表没有定义索引，查询次数不该这么少。

原因在于emp_no是为该表主键，主键默认是索引的。

那么将刚查的这员工数据都拿出来Valeska1965-01-10|Valeska|Cools|M|1993-12-16根据这些信息应该是能定位到这个员工的。

可以看到通过first_name和last_name找到了这个员工，但是查询了16209次。

添加普通索引。

再次查询:

可以看出查询次数变小了。

三、索引小工具

写的这个小工具主要用来对数据库中，表中的索引信息分析。

分析信息主要包括：

索引项、索引数据类型、索引文件大小、索引文件大小和数据文件大小比例。

同时在分析索引相关信息后，可以对索引进行增删改。

通过用户键入数据库名称db_name，程序连接数据库，可以获取数据库中所有的表的名称。

并将其显示在tb_name栏中。

这样，在tb_name栏中，选择所需查询表格，点击查询，可以方便的得到该表中索引的基本信息。

依据这些简要的信息，可以对索引进行简要的性能优化。

本实验中，数据库的创建及数据的导入，由另外的几个Python脚本完成。

这里值提供一个创建数据库和表的sql语句。

1、db_name中键入数据库名，这里是employess，之后点击连接。

连接成功和失败提示显示如下：

2、此时tb_name中会显示出数据库中所有的表，选择一张查询，此处，查看employees表。

从上面的创表过程，我们可以看到employees表主键是INT类型。

没有索引。

点击查询，显示如下：

图中显示，出现的索引文件。

但是并没有设置索引呀。

是因为，主键是被默认设置为索引的。

从查询显示，可以看到，索引文件大小是3014KB,大约是数据文件的1/3。

3、这里我们在表中添加一个索引，同样选择tb_name，这里选择employees表，添加first_name字段为索引，那么在Index中写入first_name,添加，再次查询。

相比看出，添加first_name作为索引，增加了1070KB的空间。

用户可以依据此类信息对比选择优化方式。

4、删除索引，同理，在Index中键入删除的索引。

这里我们先在其他表中添加索引，查询。

这里是存在和删除了from_date列的索引对比，可以看出有将近3M的差距。

四、总结

通过，这次高级数据库技术，我学习了MySQL中的多种索引相关的知识，同时对索引的使用、调优有了一定的了解，在对索引有一定认识的基础上，写了一个用于方便索引查看和调试的小程序。

同时，也认识到我们需要继续学习高级数据库知识，加深对数据库的理解，加强对数据库高级技术的认知和使用能力。

后续，我将会继续学习数据库高级技术，并且从多个层次增加对数据库学习的深度，加强自身的能力和技术水平。