当你已经创建了一张表,有合适的数据类型,并添加了索引之后,其实你的工作还并没有结束:你还需要维护你的表和索引使它们工作的更好。表的维护有三个主要的目标:发现和解决表的损坏,维护准确的索引统计,并且要降低存储碎片。
找到和修复损坏的表
最差的事情莫过于表已经损坏了。对于MyISAM,大部分是由于当机所造成的。然而,所有的存储引擎都会由于硬件问题或者MySQL内部BUG再或者操作系统的原因导致索引的损坏。
损坏的索引能导致查询返回不正确的结果,当没有重复值出现却抛出重复键值的错误,或者导致查询死锁和当机。如果你碰到了奇怪的行为-比如一个你意想不到的错误,就CHECK TABLE来查看表是否损坏。CHECK TABLE一般可以检查大部分表和索引的损坏。
你可以使用REPAIR TABLE来修复。但是并不是所有的引擎都支持这个命令。这样你可以使用ALTER命令,比如修改和表相同的存储引擎。
mysql> ALTER TABLE innodb_tbl ENGINE=INNODB;
你也可以使用离线的针对存储引擎的修复工具。比如myisamchk或者删除数据再重新加载。然而,如果换坏是发生在系统中,或者在表中的“行数据”取代了索引,你就无能为力了。这种情况下,你只能从备份中恢复表或者从损坏的文件中恢复数据。以后会详细说到。
更新索引的统计
MySQL的查询优化器使用两个API从存储引擎中得知当决定怎样使用索引的时候,索引是怎样分布的。第一个是records_in_range调用。它传入终结点范围并且返回了范围的记录的值。第二个就是info(),它返回了不同类型的数据,包括了索引的基数(对于每个键值有多少数据)。
当存储引擎并没有提供给优化器关于查询行数的准确信息,这个优化器就会使用索引的统计信息。这个信息你可以使用ANALYZE TABLE来估计下行数。MySQL的优化器是基于成本的,并且最主要的消耗因素就是这个查询要访问多少数据。如果这个统计信息没有生成,或者如果它们过期了,优化器可能就会有个比较差的决定。方案就是使用ANALYZE TABLE来生成统计数据。
每个存储引擎生成索引的统计数据各不相同,索引你使用ANALYZE TABLE的频率也不同,同样的消耗成本也不同:
- Memory存储引擎不会存储索引统计信息。
- MyISAM在硬盘上存储统计信息,并且ANALYZE TABLE执行了全索引扫描来计算。这个过程这张表是锁定的。
- InnoDB并不是在硬盘上存储统计信息。但是使用随机索引进入首次打开的表的方法来估算它们。对于InnoDB,ANALYZE TABLE使用的是随机的方式。因此统计结果不精确的,它们不需要手动更新,除非你服务器运行了很长时间。ANALYZE TABLE也不回加锁消耗也相对低些。因此你可以在线的更新统计信息而不会影响正常工作。
你可以使用SHOW INDEX FROM来查看索引信息。
mysql> SHOW INDEX FROM sakila.actor\G
*************************** 1. row ***************************
Table: actor
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: actor_id
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: actor
Non_unique: 1
Key_name: idx_actor_last_name
Seq_in_index: 1
Column_name: last_name
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
给出了很多索引信息。MySQL文档有详细说明。我们要注意的是Cardinality。这显示了在索引中存储引擎估算了多少个唯一的值。MySQL5.0中你也可以在INFORMATION_SCHEMA.STATISTICS表中获得这些信息,这样更方便了。举个例子,如果你可以写一条查询INFORMATION_SCHEMA的语句,来发现选择性更低的索引。
减少索引和数据碎片
B-TREE索引比较容易有碎片。这样会降低性能的。碎片的索引会糟糕的并且/或者不连续的存放在硬盘上。B-TREE索引需要随机硬盘访问来“跳”到叶子页上,因此随机访问是个规则,并不是例外。然而,如果他们物理存储是连续的并且轻微压缩的,那么叶子页的表现仍会很好。如果并不是这样,我们就说它们是碎片的,并且范围扫描和全索引扫描速度会成倍的降低。尤其是对于全索引覆盖的语句。
表的数据存储也会变为碎片。然而,数据存储碎片要比索引碎片复杂的多。有两种数据碎片:
- 行碎片.当行存储在多个位置的多个片段伤的时候,就产生了这个类型的碎片。即使查询需要索引中的一个单独的行,行碎片也能导致性能的降低。
- 内部行碎片。当逻辑的连续页或者行并不是连续的存储在硬盘上就导致了内部行的碎片。它能影响全表扫描和聚簇索引范围扫描,这些操作一般都可以从连续存储在硬盘上的数据得到一些好处。
MyISAM表都会遇到这两种碎片,而INNODB不会有短行的碎片。
为了去掉碎片数据,你可以使用OPTIMIZE TABLE或者丢掉和重新导入数据。
这两个方法对于大部分引擎来说是比较好用的。对于如MyISAM的引擎,通过排序算法重新创建索引来消除碎片。到目前为止,还没有方法消除InnoDB的碎片。在M又SQL5.0中InnoDB还不同通过排序来创建索引。甚至你重新删除和创建索引,它们还是碎片的。
对于一些不支持OPTIMIZE TABLE的存储引擎。你可以使用ALTER TABLE来重新创建表。仅仅要更改表的存储引擎,当然这个存储引擎就是当前表的。
mysql> ALTER TABLE <table> ENGINE=<engine>;
分享到:
相关推荐
oracle创建表,索引,表空间,触发器,schema用户,序列的Sql文
存储在介质上的数据的结构描述,含存储路径、存储方式、索引方式等 模式指全局模式 视图指外部视图 两层映像 E-C Mapping:External Schema-Conceptual Schema Mapping ----将外模式映射为概念模式,从而支持实现...
同步新表同步轴向变动:添加,修改同步索引变动:添加,修改支持预览(只对比不同步变动)邮件通知变动结果支持屏蔽更新表,基线,索引,外键支持本地比线上额外的多一些表,分段,索引,外键安装去-u github....
SQL语句解释大全--从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 --数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --...
使用命令行可以轻松列出给定表的数据库表和列。 安装 您可以通过composer安装该软件包: composer require ohseesoftware/laravel-schema-list 用法 列出默认连接中的表: php artisan schema:tables 列出给定表...
面向用户或应用程序员的用户级 面向建立和维护数据库人员的概念级 面向系统程序员的物理级 三级模式和两级映像 数据库原理与运用第二章-数据库系统结构全文共36页,当前为第4页。 DBMS管理数据的三个层次 External ...
SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 --数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --从数据库...
因为:1)提高性能:尽量把表和索引的表空间存储在不同在磁盘上,把两类不同IO性质的数据分开放,这样可以提高磁盘的IO总体性能; 2)便于管理:试想一下,如果索引的数据文件损坏,只要创建索引即可,不会引起数据...
mySQL索引查看 select * from information_schema.statistics where table_schema='数据库名称' and table_name = '表名称'
SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 --数据定义 CREATE TABLE --创建一个数据库表 DROP ...
语 句 功 能 --数据操作 SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 -数据定义 CREATE TABLE --创建一个数据库表 DROP ...
SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 --数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --从数据库...
SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 --数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --...
SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 --数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --从...
CREATE INDEX --为数据库表创建一个索引 DROP INDEX --从数据库中删除索引 CREATE PROCEDURE --创建一个存储过程 DROP PROCEDURE --从数据库中删除存储过程 CREATE TRIGGER --创建一个触发器 DROP TRIGGER --...
语 句 功 能 --数据操作 SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 --数据定义 CREATE TABLE --创建一个数据库...
SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 -数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --从...
深度克隆并返回模式的副本,向该模式添加关键路径/模式类型对,您还可以添加另一个模式并复制所有路径、虚拟、getter、setter、索引、方法和静态。 特征: 深度克隆模式 复制路径、虚拟、getter、setter、索引、...
Solr 可以高亮显示搜索结果,通过索引复制来提高可用,性,提供一套强大 Data Schema 来定义字段,类型和设置文本分析,提供基于 Web 的管理界面等。 Key-Value Store Indexer 这个组件非常关键,是 Hbase 到 Solr ...
SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 -数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --从...