当前位置:首页 > mysql > 正文内容

mysql中AnalyzeTable优化

phpmianshi4年前 (2017-04-25)mysql266

Analyze Table

MySQL的Optimizer(优化元件)在优化SQL语句时,首先需要收集一些相关信息,其中就包括表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度,那么索引就基本失效了。
我们可以使用SHOW INDEX语句来查看索引的散列程度


语法

ANALYZE TABLE 表名1 [,表名2…] ;


SHOW INDEX FROM user;



TABLE   KEY_NAME COLUMN_NAME CARDINALITY
------- -------- ----------- -----------
user   PRIMARY  name     14

因为此时user表中不同的name数量远远多于14,索引基本失效。
下面我们通过Analyze Table语句来修复索引:

ANALYZE TABLE user;
SHOW INDEX FROM user;


结果是:
TABLE   KEY_NAME COLUMN_NAME CARDINALITY
------- -------- ----------- -----------
user   PRIMARY  name    1000

此时索引已经修复,查询效率大大提高。

需要注意的是,如果开启了binlog,那么Analyze Table的结果也会写入binlog,我们可以在analyze和table之间添加关键字local取消写入。


Checksum Table

数据在传输时,可能会发生变化,也有可能因为其它原因损坏,为了保证数据的一致,我们可以计算checksum(校验值)。

使用MyISAM引擎的表会把checksum存储起来,称为live checksum,当数据发生变化时,checksum会相应变化。
在执行Checksum Table时,可以在最后指定选项qiuck或是extended;quick表示返回存储的checksum值,而extended会重新计算checksum,如果没有指定选项,则默认使用extended。

Checksum Table user;


Optimize Table

碎片产生的原因

(1)表的存储会出现碎片化,每当删除了一行内容该段空间就会变为空白、被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大;

(2)当执行插入操作时,MySQL会尝试使用空白空间,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片;

(3)当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分;



Optimize Table语句对MyISAM和InnoDB类型的表都有效,但是,OPTILMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段。

如果表经常更新,就应当定期运行Optimize Table语句,保证效率。

1.查看某个表的碎片

 SHOW TABLE STATUS LIKE 'user';

结果中Data_free列的值就是碎片大小

2.列出所有已经产生碎片的表

select table_schema db, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql')  and data_free > 0;


在MySQL 5.5中,默认情况下所有表共享一个名为ibdata的中央表空间.此表空间中所有表的data_Free将报告相同的数字,即整个表空间中空闲页面的空间量,而不仅仅是一个表.您还可以为每个表分配一个单独的表空间(innodb_file_per_table = 1),对于单独表空间中的表,您将看到data_free的每个表的不同值.


与Analyze Table一样,Optimize Table也可以使用local来取消写入binlog。

Optimize Table user;

innodb表可能提示:

Table does not support optimize, doing recreate + analyze instead

提示该表不支持 optimize,但是下边有显示OK.其实已经执行成功了。5.6.X的版本,其实已经支持Innodb了。

对于InnoDB的表、上面的内容并非报错、这是MySQL会帮你映射到:alter table table_name engine='InnoDB';


OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是做个shell,定期检查mysql中 information_schema.TABLES字段,查看 DATA_FREE 字段,大于0话,就表示有碎片

建议:清除碎片操作会暂时锁表,数据量越大,耗费的时间越长,可以做个脚本,定期在访问低谷时间执行,例如每周三凌晨,检查DATA_FREE字段,大于自己认为的警戒值的话,就清理一次。


下面提供一个脚本参考:

//shell脚本如下:

mysql_user=root
mysql_pass=xxxx
time_log=/opt/database/time
databases=/opt/database/databases
mysql -u$mysql_user -p$mysql_pass -e "show databases" | grep -v "Database" > $databases
sed -i "s/information_schema//" $databases
sed -i "s/mysql//" $databases
sed -i "s/test//" $databases
sed -i "s/performance_schema//" $databases
databases1=$(cat /opt/database/databases)
for i in $databases1
do
echo "database $i staring"
tables=$(mysql $i -u$mysql_user -p$mysql_pass -e "show tables" | grep -v "Tables" > /opt/database/$i)
tablelist=$(cat /opt/database/$i)
echo "optimize database $i starting" >> $time_log
echo "$i start at $(date +[%Y/%m/%d/%H/%M/%S])" >> $time_log
for list in $tablelist
do
echo $list
mysql $i -u$mysql_user -p$mysql_pass -e "alter table $list engine=InnoDB"
done
echo "$i end as $(date +[%Y/%m/%d/%H/%M/%S])" >> $time_log
echo >> $time_log
done



Check Table

数据库经常可能遇到错误,譬如数据写入磁盘时发生错误,或是索引没有同步更新,或是数据库未关闭MySQL就停止了。
遇到这些情况,数据就可能发生错误:
Incorrect key file for table: ' '. Try to repair it.
此时,我们可以使用Check Table语句来检查表及其对应的索引。
譬如我们运行

CHECK TABLE user;

结果是
TABLE      OP   MSG_TYPE MSG_TEXT
-------------- ----- -------- --------
dbname.users check status   OK

MySQL会保存表最近一次检查的时间,每次运行check table都会存储这些信息:

执行
SELECT   TABLE_NAME, CHECK_TIME
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME = 'user'
AND     TABLE_SCHEMA = 'dbname';  /*dbname是数据库名*/

结果是

TABLE_NAME   CHECK_TIME
----------   -------------------
users     2017-04-25 12:44:25

Check Table还可以指定其它选项:
UPGRADE:用来测试在更早版本的MySQL中建立的表是否与当前版本兼容。
QUICK:速度最快的选项,在检查各列的数据时,不会检查链接(link)的正确与否,如果没有遇到什么问题,可以使用这个选项。
FAST:只检查表是否正常关闭,如果在系统掉电之后没有遇到严重问题,可以使用这个选项。
CHANGED:只检查上次检查时间之后更新的数据。
MEDIUM:默认的选项,会检查索引文件和数据文件之间的链接正确性。
EXTENDED:最慢的选项,会进行全面的检查。

Repair Table


用于修复表,只对MyISAM和ARCHIVE类型的表有效。
这条语句同样可以指定选项:
QUICK:最快的选项,只修复索引树。
EXTENDED:最慢的选项,需要逐行重建索引。
USE_FRM:只有当MYI文件丢失时才使用这个选项,全面重建整个索引。

Repair Table user;


与Analyze Table一样,Repair Table也可以使用local来取消写入binlog。

版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

mysql中性能分析Profiling

mysql中性能分析Profiling

概念Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于Sql调优的测量。示例1、先查看是否开启了此功能,默认情况下,参数处于关闭状态,为OFF状态 ...

innodb中统计数据是如何收集的

InnoDB 统计数据如何查看    1. 通过SHOW TABLE STATUS可以看到关于表的统计数据    2....

mysql中找出无用的索引或重复索引

背景MySQL允许在相同列上创建重复的索引,但这样做对数据库却是有害而无利的,需要定期检查此类重复索引以改善数据库性能。可减少磁盘空间占用、减少磁盘IO、减少优化器优化查询时需要比较的索引个数、减少数...

mysql中count(x)到底慢不慢

总结MyISAM 表不支持事务,但是count(*) 很快,因为他直接记录了一个总数MyISAM在统计表的总行数的时候会很快,但是有个大前提,不能加有任何WHERE条件。这是因为:MyISAM对于表的...

mysql中长事务详解

什么是长事务运行时间比较长,长时间未提交的事务,也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时,容易造成主从延迟,要尽量避免使用长事务。下面我将演示下如何开启事务及模拟长事务:#假设我们有一...

mysql中Query语句对系统性能的影响

背景我们重点分析实现同样功能的不同 SQL语句在性能方面会产生较大的差异的根本原因,并通过一个较为典型的示例来对我们的分析做出相应的验证。为什么返回完全相同结果集的不同SQL语句,在执行性能方面存在差...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。