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。
标签:AnalyzeTable,mysql,table,索引,user,Table,TABLE,优化 From: https://blog.51cto.com/u_6353447/5873670