高性能的MySQL(5)索引策略-索引和表的维护

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

维护表有三个主要的目的:

1、找到并修复损坏的表

对于MyISAM存储引擎来说,表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件的问题,MySQL本身的缺陷或者操作系统的问题导致索引的损坏。

损坏的索引,会导致查询返回错误的结果或者莫须有的主键冲突等问题,严重时还会导致数据库崩溃。

这类情况,可以尝试check table来检查是否发生了表损坏,有些存储引擎不支持这个命令。

可以使用repair table 来修复损坏的表,但同样不是所有引擎都支持该命令。

如果引擎不支持,可以使用alter操作重建表,或者将数据导出然后再重新导入。

InnoDB一般不会损坏,如果发生损坏,一般要么是数据库硬件问题,例如内存或者磁盘问题,要么就是数据库管理员操作导致。

常见的错误是由于尝试使用rsync备份InnoDB导致的。

如果是某条查询导致的,那一定是遇到了bug,而不是查询的问题。


2、维护准确的索引统计信息。

MySQL的查询优化器会通过2个API来了解存储引擎的索引值的分布信息,以决定如何使用索引。

第一个API是records_in_range(),通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。

对于某些引擎,该接口返回精确值,比方说MyISAM;对于InnoDB则是一个估算的值。

第二个API是info(),该接口返回各种类型的数据,包括索引的基数。

当返回信息不准确的时候,优化器会使用索引统计信息来估算扫描行数。如果表没有统计信息,或者统计信息不准确,优化器很可能做出错误的决定。

可以运行analyze table 来重新生成统计信息。

Memory引擎不存储索引统计信息

MyISAM将索引统计信息存储在磁盘中,analyze table 需要进行一次全表扫描,整个过程需要锁表。

MySQL5.5以后,InnoDB也不在磁盘存储索引统计信息,而是通过随机的索引访问来进行评估并存储在内存中。

使用show index from 命令可以察看索引基数(Cardinality)

170652224.png

InnoDB会在首次打开表,或者执行analyze table,或者表大小发生变化超过1/16或show table status,或show index时候都会计算索引的统计信息,如果服务器有大量的数据,这会是个严重的问题,只要show index查看索引统计信息就一定会触发统计信息更新,可以关闭

innodb_stats_on_metadata参数来关闭。

一旦关闭自动更新,那么需要周期性的使用analyze table 来手动更新,否则问题大了。


3、减少索引和数据碎片

B-Tree索引可能会碎片化,这会降低查询效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。

可以通过optimize table 或者导出再导入的方式来重新整理数据。

对于不支持optimize table 的存储引擎,可以通过一个不做任何操作的alter table来重建表。

1
alter  table  < table > engine=<engine>;

也可以先删除索引,重建表,最后重新创建索引来实现。


索引的介绍就先到这里了,明天进入查询性能优化部分!

















本文转自shayang8851CTO博客,原文链接:http://blog.51cto.com/janephp/1314057,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
SQL 关系型数据库 MySQL
MySQL索引与事务
MySQL索引与事务
|
1天前
|
存储 SQL 关系型数据库
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
22 0
|
2天前
|
监控 关系型数据库 MySQL
MySQL创建索引的注意事项
在索引的世界中,权衡是关键。权衡读写性能,权衡索引的数量和类型,权衡查询的频率和数据分布。通过谨慎的设计、定期的维护和持续的监控,我们能够确保索引在数据库中的角色得到最大的发挥,为应用提供更加高效和可靠的数据访问服务。在数据库优化的旅途中,索引是我们的得力助手,正确使用它将使数据库系统更具竞争力和可维护性。
10 0
|
3天前
|
SQL 关系型数据库 MySQL
京东三面:什么情况会导致 MySQL 索引失效?
为了验证 MySQL 中哪些情况下会导致索引失效,我们可以借助 explain 执行计划来分析索引失效的具体场景。
19 0
|
4天前
|
存储 监控 关系型数据库
Mysql内部在索引层面的优化
Mysql内部在索引层面的优化
|
4天前
|
SQL 算法 关系型数据库
MySQL索引优化实战二
MySQL索引优化实战二
|
2天前
|
关系型数据库 MySQL Linux
centos7.0环境下安装MySql_8.0.12
centos7.0环境下安装MySql_8.0.12
|
11天前
|
关系型数据库 MySQL 数据库
MYSQL解压版安装笔记
MYSQL解压版安装笔记
13 0
|
8天前
|
关系型数据库 MySQL Linux
CentOS7环境下安装MySQL5.6
CentOS7环境下安装MySQL5.6