mysql索引与优化文档

《mysql索引与优化文档》由会员分享，可在线阅读，更多相关《mysql索引与优化文档（11页珍藏版）》请在金锄头文库上搜索。

1、HUNAN UNIVERSITY高级数据库技术技术报告技术报告题目： MySQL 索引与优化学 生 姓 名 ： 王 斌学 生 学 号 ： S1510W0702专 业 班 级 ： 软件工程专业院 系 名 称 ： 计算机科学与技术系学 院 名 称 ： 信息科学与工程学院指 导 老 师 ： 杨金民2015 年 12 月 15 日 湖南大学信息科学与工程学院 索引与优化一、索引概念索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。1、索引的优点我们先

2、来看这样一个例子，不带索引的表仅仅是一个无序的数据行集合。例如 , 图 1 显示的 ad 表就是不带索引的表,因此如果需 要查找某个特定的公司,就必须检查表中的每个数据行看它是否与目标值相匹配。这会导致一次完全的数据表扫描, 这个过程会很慢。如果这个表很大, 但是只包含少量 的符合条件的记录,那么效率会非常低。图 2是同样的一张数据表,但是增加了对 ad表的 company_num数据列的索引。这个索引包含了 ad表中的每个数据行的条目,但是索引的条目是按照company_num值排序的。现在不是逐行查看以搜寻匹配的数据项,而是使用索引。假设我们查找公司 13的所有数据行,开始扫描索引并找到了

3、该公司的三个值。接着碰到了公司 14的索引值,它比我们正在搜寻的值大。索引值是排过序的,因此当读取了包含 14的索引记录的时候,就知道再也不会有更多的匹配记录,可以结束查询操作了。因此使用索引获得的功效是:找到了匹配的数据行在哪儿终止,并能够忽略其它的数据行。另一个功效来自使用定位算法查找第一条匹配的条目,而不需要从索引头开始执行线性扫描(例如,二分搜索就比线性扫描要快一些)。通过使用这种方法,可以快速地定位第一个匹配的值,节省了大量的搜索时间。为什么不对数据行进行排序从而省掉索引,这样不是也能实现同样的搜索速度的改善吗?如果表只有一个索引,这样做也可能达到相同的效果 。但是如果添加第二个索引

4、,那么就无法一次使用两种不同方法对数据行进行排序了(例如 ,你可能希望在顾客名称上建立一个索引,在顾客 ID号上建立另外一个索引)。把与数据行相分离的条目作为索引解决了这个问题,它图 1图2 湖南大学信息科学与工程学院 允许创建多个索引。此外,索引中的行一般也比数据行短一些。当你插人或者删除新的值的时候，移动较短的索引值比移动较长数据行的排序次序更容易。2、索引的缺点索引在数据库中的缺陷主要表现在以下几个方面： 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大

5、。 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。二、 索引与优化1、选择索引的数据类型MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和 CPU缓存中都需要更少的空间，处理起来更快。(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在 MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储 IP地址。(3)尽量避免 NULL：应该指定列为 NOT NULL，除非你

6、想存储 NULL。在 MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0、一个特殊的值或者一个空串代替空值。2、索引分类在 MySQL 中，主要有四种类型的索引，分别为： B-Tree 索引， Hash 索引， Fulltext 索引和 R-Tree 索引。我们主要分析 B-Tree 索引。B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。Archive 引擎直到 MySQL 5.1 才支持索引，而且只支持索引单个 AUTO_INCREM

7、ENT 列。不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中 B-Tree 索引也同样是作为最主要的索引类型，这主要是因为 B-Tree 索引的存储结构在数据库的数据检索中有非常优异的表现。一般来说， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree （平衡树）的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node(叶子节点) ，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的，所以我们大家都称之为 B-Tree 索引。当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tre

8、e 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结 湖南大学信息科学与工程学院 构实际上是 B+Tree，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个 Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息（增加了顺序访问指针） ，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。下面主要讨论 MyISAM和 InnoDB两个存储引擎的索引实现方式： MYISAM索引实现对于 MyISAM 数据表，该表的数据行保存在一个数据文件中，索

9、引值保存在索引文件中。一个数据表上可能有多个索引，但是它们都被存储在同一个索引文件中。索引文件中的每个索引都包含一个排序的键记录（它用于快速的访问数据文件）数组。1. 主键索引MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。下图是 MyISAM 主键索引的原理图：这里设表一共有三列，假设我们以 Col1 为主键，图 myisam1 是一个 MyISAM 表的主索引（Primary key）示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。2. 辅助索引在 MyISAM 中，主索引和辅助索引（ Secondary key）在结构上没

10、有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示： 湖南大学信息科学与工程学院 同样也是一颗 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。MyISAM 的索引方式也叫做“非聚集”的，之所以这么称呼是为了与 InnoDB 的聚集索引区分。 InnoDB索引实现InnoDB也使用 B+Tree作为索引结构，但具体实现却和

11、 MYISAM截然不同。InnoDB 使用单个的数据表空间，在表空间中管理所有 InnoDB表的数据和索引存储。可以把 InnoDB配置为每个表都在自己的表空间中创建，但是即使是这样，数据表的数据和索引也存储在同一表空间文件中。1. 主键索引 湖南大学信息科学与工程学院 上图是 InnoDB 主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，所以InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列

12、，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形。2. 辅助索引InnoDB 的所有辅助索引都引用主键作为 data 域。例如，下图为定义在 Col3 上的一个辅助索引：InnoDB 表是基于聚簇索引建立的。因此 InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。文字符的 ASCII 码作为比较准则。聚集索引这种

13、实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB 的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在 InnoDB 中不是个好主意，因为 InnoDB 数据文件本身是一颗 B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。3

14、、索引使用 湖南大学信息科学与工程学院 我们来看一个索引使用的实例。从上面的介绍，了解到 MyISAM 的每个表都对应在硬盘上有三个文件。table_name.frm 保存表的定义、table_name.myd 保存表数据、table_name 表的索引。 创建数据库 db_index_MyISAM，设置当前默认引擎 MyISAM。这就创建了一个名为 db_index_MyISAM 的数据库，那么这个时候想看一下这个数据库是怎么保存的，有不知道数据库文件保存在哪个目录，怎么办呢？下面的这条命令可以用到。 显示数据文件保存路径。这个时候，可以看到，只有一个 opt文件。文件用来存储当前数据库的默

15、认字符集和字符校验规则。用记事本打开可以看到：default-character-set=latin1default-collation=latin1_swedish_ci 创建数据表 employees，不带索引。找到上述，数据库文件存放目录，即创建一个表后的数据库文件如下： 湖南大学信息科学与工程学院 使用 python写的小代码插入数据。那么就可以对比有无索引的效果了。使用索引的效果，我们通过查找的行数来断定。比如我查询 employees中的第 491000编号的员工。从结果来看似乎不对，employees 表没有定义索引，查询次数不该这么少。原因在于emp_no是为该表主键，主键默认是索引的。那么将刚查的这员工数据都拿出来 Valeska 1965-01-10 | Valeska | Cools | M | 1993-12-16根据这些信息应该是能定位到这个员工的。 可以看到通过 first_name 和 last_name找到了这个员工，但是查询了 16209次。添加普通索引。再次查询:可以看出查询次数变小了。三、索引小工具 写的这个小工具主要用来对数据库中，表中的索引信息分析。分析信息主要包