`
student_lp
  • 浏览: 436615 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论
阅读更多

    即使用正确的类型创建了表并加上了合适的索引,工作也没有结束,还需要维护表和索引来确保他们都正常工作。维护表有三个主要的目的:找到并修复损坏的表,维护准确的索引统计信息,减少碎片。

一、找到并修复损坏的表

    表损坏是很糟糕的事情。对于MySQL存储引擎,表损坏通常是系统崩溃导致的。其他引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题,严重甚至还会导致数据库的崩溃。

    运行CHECK TABLE来检查是否发生了表损坏。check table通常能够找出大多数的表和索引的错误。可以使用repair table命令来修复损坏的表。(注意:有些存储引擎并不支持这两个命令)

    此外也可以使用一些存储引擎相关的离线工具,例如myisamchk;或者将数据导出一份,然后重新导入。不过,如果损坏的是系统区域,或者是表的“行数据”区域,而不是索引,那么上面的办法就没有用了。这种情况下,可以从备份中恢复表,或者尝试从损坏的数据文件中尽可能的恢复数据

    如果InnoDB引擎的表出现了损坏,那么一定是发生了严重的错误,需要立即调整调查一下原因。InnoDB一般不会出现损坏。InnoDB的设计保证了它并不容易损坏。如果发生损坏,一般要么是数据库的硬件问题例如内存或者磁盘问题,那么是由于数据库管理员的错误例如在MySQL外部操作了数据文件,抑或是InnoDB本身的缺陷(不太可能)。常见的类似错误通常是由于尝试使用rsync备份InnoDB导致的。不存在什么查询能够让InnoDB表损坏,也不用担心暗处有陷阱。如果某条查询导致InnoDB数据的损坏,那一定是遇到bug,而不是查询的问题。

    如果遇到数据损坏,最重要的是找出是什么导致了损坏,而不只是简单的修复,否则很有可能会不断的损坏。可以通过设置innodb_force_recovery参数进入InnoDB的强制恢复模式来修复数据。

二、更新索引统计信息

    MySQL的查询优化器通过两个API来了解存储引擎的索引值的分布信息,已决定如果使用索引。第一个API是records_in_range(),通过向存储引擎传入两个边界值获取在这个范围大概有多少记录。对于某些存储引擎,该接口返回精确值,例如MyISAM;但对于另一些存储引擎则是一个估算值,例如InnoDB。第二个API是info(),该接口返回各种类型的数据,包括索引的基数。

    如果存储引擎向优化器提供的扫描函数信息是不准确的数据,或者执行计划本身太复杂以致无法准确的获取各个阶段的匹配的行数,那么优化器会使用索引统计信息来估算扫描行数。MySQL优化器使用的是基于成本的模型,而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息,或者统计信息不准确,优化器就很可能做出错误的决定。可以通过运行analyze table来重新生成统计信息解决这个问题。

    每种存储引擎实现索引统计信息的方式不同,所以需要进行analyze table的频率也因不同的引擎而不同,每次运行的成本也不同:

  • memory引擎根本不存储索引统计信息;
  • MyISAM将索引统计信息存储在磁盘中,analyze table需要进行一次全索引扫描来计算索引基数。在整个过程中需要锁表;
  • 直到MySQL5.5版本,InnoDB也不在磁盘存储索引统计信息,而是通过随机的索引访问进行评估并将其存储在内存中。

    可以使用show index from命令来查看索引的基数。这个命令输出了很多关于索引的信息,这里特别提及的是索引列的基数(Cardinality),其显示了存储引擎估算索引列有多少不同的取值。需要注意如果服务器上的库表非常多,则从这里获取元数据的速度可能会非常慢,而且会给MySQL带来额外的压力。

    InnoDB引擎通过抽样的方式来计算统计信息,首先随机的读取少量的索引页面,然后依次为样本计算索引的统计信息。在老的InnoDB版本中,样本页面数是8,新版本的InnoDB可以通过参数innodb_stats_sample_pages来设置本页的数量。设置更大的值,理论上来说可以帮助生成更准确的索引信息,特别对于某些超大的数据表来说,但具体设置多大适合依赖于具体的环境。

    InnoDB会在表首次打开,或者执行analyze table,抑或表的大小发生非常大的变化的时候计算索引的统计信息。InnoDB在打开某些infomation_schema表,或者使用show table status和show index,抑或在MySQL客户端开启自动补全功能的时候都会触发索引统计信息的更新。如果服务器上有大量的数据,这可能是一个很严重的问题,尤其是当I/O比较慢的时候。客户端或者监控程序触发索引信息采样更新可能会导致大量的锁,并给服务器带来很多的额外压力,这会让客户因为启动时间漫长而沮丧。只要show index查看索引统计信息,就一定会触发统计信息的更新。可以关闭innodb_stats_on_metadata参数来避免上面提到的问题。

    一旦关闭索引统计信息的自动更新,那么就需要周期性的使用analyze table来动手更新,否则,索引统计信息就会永久不变。如果数据分布发生很大的变化,可能会出现一些很糟糕的执行计划。

三、减少索引和数据的碎片

    b-tree索引可能会碎片化,这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。根据设计,b-tree需要随机磁盘访问才能定位到叶子页,所以随机访问是不可避免的。然而,如果叶子页在物理分布上是顺序紧密的,那么查询的性能会更好。否则,对于范围查询、索引覆盖扫描等操作来说,速度可能会降低很多倍,对于索引覆盖扫描这一点更加明显。

    表的数据存储也可能碎片化。然而,数据存储的碎片化比索引更加复杂。有三种类型的数据碎片:

  • 碎片化---这种碎片指的是数据行被存储为多个片段中。即使查询也从索引中访问一行记录,碎片也会导致性能下降。
  • 行间碎片---行间碎片是指逻辑上顺序的页,或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响,因为这些操作原本能够从磁盘上顺序存储的数据中获益。
  • 剩余空间碎片---剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据,从而造成浪费。

    对于MyISAM表,这三类碎片都可能发生。但InnoDB不会出现短小的行碎片;InnoDB会移动短小的行并重写到一个碎片中。

    对于通过执行optimize table或者导出再倒入的方式重新整理数据。这对多数存储引擎都是有效的。对于一些储存引擎如MyISAM,可以通过排序算法重新索引的方式来消除碎片。对于那些不支持optimize table的存储引擎,可以通过一个不做任何操作的alter table操作来重新建表,如:alter table tablename engine=<engine>。对于开启了expand_fast_index_creation参数的Percona Server,按这种方式重建表,则会同时消除表和索引的碎片化。但对于标准版的MySQL则只会消除表的碎片化。可以先删除所有索引,然后重建表,最后重建索引的方式模拟percona Server的这个功能。

 

分享到:
评论

相关推荐

    分区索引,本地索引,全局索引的区别

    根据索引是否与表的分区策略相匹配,分区索引可以分为两大类:本地索引(Local Index)和全局索引(Global Index)。 #### 二、本地索引(Local Index) 本地索引是一种特殊的分区索引,它与表的分区紧密相关,即索引的...

    索引介绍聚集索引和非聚集索引

    ### 索引介绍:聚集索引与非聚集索引 #### 一、索引的基本概念 ...总之,了解聚集索引和非聚集索引之间的区别对于优化数据库性能至关重要。通过合理设计和维护索引,可以显著提高数据库系统的整体性能。

    索引维护方法

    索引分为聚集索引和非聚集索引,聚集索引决定了表数据的物理存储顺序,而非聚集索引则存储指向数据行的逻辑指针。维护索引的目标是确保它们保持高效,避免碎片和无效引用,从而优化查询性能。 在Microsoft SQL ...

    数据库维护计划和索引重建

    在选择维护任务阶段,确保包含了“检查数据库完整性”、“收缩数据库”、“重新组织索引”、“重新生成索引”和“更新统计信息”。这些任务旨在确保数据的完整性,释放空间,并优化查询性能。特别地,“重新生成索引...

    MySql练习4:创建学生表和成绩表索引并查看索引.zip

    虽然索引能提高查询性能,但也会占用额外的存储空间,并且在插入、更新和删除操作时需要维护索引,这可能会影响写操作的性能。因此,合理地设计和选择索引是至关重要的。 6. **优化索引策略**: - 索引的选择性越...

    Oracle数据库索引的维护

    本文将从Oracle数据库索引的基础概念出发,深入探讨索引维护的重要性和具体方法。 #### 一、Oracle数据库索引概述 索引是数据库中一种用于快速查找数据的数据结构。在Oracle数据库中,最常用的索引类型是B树索引...

    数据结构--索引顺序表查找

    索引密度的选择会影响索引表的大小和查找效率。 #### 四、索引顺序表查找算法 索引顺序表的查找算法主要包括以下几个步骤: 1. **初始化**: 首先确定待查找元素的范围。 2. **索引查找**: 使用索引表快速定位到...

    索引顺序表查找

    **索引顺序表查找**是一种在数据结构领域中常见的查找技术,它结合了索引和顺序查找的优点。在计算机科学中,数据存储和检索效率至关重要,而索引顺序表查找正是为了提高查找效率而设计的一种方法。下面我们将深入...

    修复表索引损坏无法打开的问题

    在数据库管理与维护的过程中,经常会遇到表索引损坏导致表无法正常打开的情况。这种情况通常会严重影响到数据库的性能以及数据的完整性,因此及时进行修复至关重要。本文将详细介绍如何修复表索引损坏无法打开的问题...

    Oracle数据库中索引的维护

    维护索引还包括监控索引的使用情况、定期重建索引以保持其性能、在必要时重建或删除无效索引等。在Oracle数据库中,索引维护是确保数据库性能稳定和高效运行的重要环节,对数据库管理员来说,理解和掌握这些技巧至关...

    建立词索引表相关程序

    在IT领域,尤其是在文本处理和信息检索中,建立词索引表是一项重要的任务。词索引表是一种数据结构,用于快速查找和访问文档中的特定单词或短语,它极大地优化了搜索性能。以下是对给定标题和描述所涉及的知识点的...

    关于SQL Server中索引使用及维护简介

    在实践中,选择和维护索引需要综合考虑查询模式、数据修改频率、空间利用率等因素。应定期分析查询执行计划,识别哪些索引能有效提升性能,哪些索引可能成为负担。通过索引重建、碎片整理、删除不必要的索引等维护...

    创建索引和调优索引

    《创建索引和调优索引:SQL Server 2005中的索引管理》 在SQL Server 2005中,索引是数据库管理的关键要素,它们极大地提升了数据检索速度,尤其是在处理大规模数据时。索引可以分为多种类型,包括聚集索引和非聚集...

    SQLSEVER索引和索引调整向导

    * 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。 * 索引占用一定的物理空间,如果要建立聚簇索引,需要的空间就会更大。 * 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样...

    C++实现索引表

    在IT领域,索引表是一种常见的数据结构,它允许快速访问和操作数据。在这个场景中,我们关注的是使用C++编程语言实现一个简单的索引表。C++是一种强大的、面向对象的编程语言,广泛用于系统软件、应用软件、游戏开发...

    数据库非聚集索引 聚集索引 模式 索引

    非聚集索引在查询性能上很有优势,尤其是对于多列联合索引和范围查询,但它们会占用额外的存储空间,并且在插入、删除和更新时可能需要维护索引。 接着是**聚集索引**。聚集索引决定了数据在表中的物理存储顺序。在...

    索引和视图的创建和修改

    ### 知识点详解 #### 一、索引的基础概念 ...通过以上步骤,可以有效地管理和优化数据库中的索引和视图,同时通过设置参照完整性来保证数据的一致性和准确性。这对于维护大型数据库系统的稳定性和性能至关重要。

    oracle分区表分区索引.docx

    对于全局索引,需要使用 update global indexes 语句来维护索引。对于本地索引,Oracle 会自动维护其索引分区。 五、查询分区索引信息 可以使用以下语句来查询分区索引信息: ```sql SELECT * FROM ALL_PART_...

    oracle在线创建索引和重组索引

    Oracle 在线创建索引和重组索引 Oracle 在线创建索引和重组索引是数据库管理员经常需要处理的问题。在线创建索引可以提高查询性能,而重组索引可以减少索引的碎片化和空间浪费。下面我们将详细介绍在线创建索引和...

    聚集索引和非聚集索引的区别

    聚集索引和非聚集索引是数据库管理系统中两种重要的索引类型,它们在数据库查询优化中扮演着关键角色。理解这两种索引的区别对于优化数据库性能至关重要。 首先,聚集索引(Clustered Index)决定了表中数据的实际...

Global site tag (gtag.js) - Google Analytics