【学习笔记】维护索引统计状态信息

背景：索引建立好了，也是需要维护一下？

===================分割线=======================

不说无的，只写自己知道的学到的，哈哈

上干货:查看当前索引有哪些， SHOW INDEXES FROM tablename \G  仅查索引名字也可以DESC tablename 或show create table tablename;

====================分割线==========================

有了索引，有了优化目标，是耗时已经较小的业务量超多的SQL好？ 还是优化业务量极小，但耗时很大的SQL。 很明显优化前者带来的效益更好。

往往人们优化索引会向着 耗时较大的SQL去优化，这个方向往往不是最佳的。

优化业务量较多的SQL，减小耗时，服务器吞吐量更大，SQL锁更少，CPU更快的释放资源。。。有个SQL耗时2S，一天只跑几次，对其他无啥影响。显而易见优化前者。

这里索引优化，最后用到的是索引的根本是索引统计信息，一般是B+树索引，B+树二级索引。MySQL的优化器根据表中索引的分布统计信息， 抉择查询使用哪个索引执行查询最优，以及连接查询时的执行顺序；

======================分割线=========================

当表中数据更新时，索引的统计信息也会随之更新，分为自动更新 和 手动更新：

手动更新方式：ANALYZE TABLE tablename; 执行SHOW TABLE STATUS；在开启innodb_stats_on_metadata变量情况下，查询表 INFORMATION_SCHEMA.TABLES；

自动更新：默认情况下，开启参数innodb_stats_auto_recalc，10%的行更新会触发自动更新索引统计信息；

g

Sampling 20 data pages by default . Changed with the innodb_stats_persistent_sample_pages variable.

================================================

Innodb持久的保存索引统计信息到mysql.innodb_index_stats表中。默认是通过参数innodb_stats_persistent开启。

单独的表也可以单独设置是否开启索引统计，通过STATS_PERSISTENT参数实现。默认是开启的...想不到有不开的情况，哈哈

 

====================如何重建索引==============

1.重建除了全文索引外的索引,使用ALTER TABLE tablename FORCE；

可以跨数据页面更均匀地重组数据 ，可以回收空页面使用的文件系统空间。重建索引耗时长，ALTER TABLE会锁表，表中数据量较小时可以用ALTER TABLE

若数据量较大，建议看下替代方式删除索引，新建索引又不锁表，而不是直接重建索引，这里有点意思。

2.OPTIMIZE TABLE重建全文索引； 默认情况下OPTIMIZE TABLE会重建整张表；开启fulltext_only选项后，就会仅仅重建全文索引；

SET innodb_optimize_fulltext_only = 1;

OPTIMIZE TABLE tablename;

3.mysqlcheck客户端工具，可以从表级别，数据库级别，全部数据库级别，维护Innodb，MyISAM,ARCHIVE引擎的表；

mysqlcheck   --analyze: Perform an ANALYZE TABLE.

mysqlcheck   --optimize: Perform an OPTIMIZE TABLE.

mysqlcheck  --repair：perform a REPAIR TABLE 用于MyISAM引擎。

mysqlcheck   --check: Perform a CHECK TABLE (default).检查表中是否出错；

e.g.

 

==================End===================