高性能Mysql读后感(三)-其他一些关于索引的话

其他一些关于索引的话

建立索引

** 所建立的索引最好支持多种过滤条件 **

** 在所建立的索引列上避免多个范围条件 **

** 过滤数据的同时最好能照顾到排序 **

维护索引和表

找到并修复损坏的表 ( corruption )

分为 ** 索引损坏 ** 和 ** 数据损坏 **

check table 来检查是否发生了表损坏 (MyISAM友好)
repair table 修复损坏的表 (MyISAM友好)
InnoDB 表 alter table t1 ENGINE=INNODB; 来重建表.
如果数据损坏了, 恢复备份吧.

维护索引统计信息

mysql 的查询优化器会通过两个API接口 records_in_range() 和 info() 提供的索引值分布信息, 来决定
如何使用索引.

records_in_range(): 通过向存储引擎传入两个边界值 来获取 这个范围大概有多少条记录.
MyISAM 返回精确值: InnoDB 返回估算值.
info(): 返回各种类型的数据, 索引的基数(每个索引有多少条记录)等等.

** mysql 优化器使用基于成本的模型, 衡量成本的主要指标就是一个查询需要扫描多少行. 所以, 如果 mysql 优化器 records_in_range() 和 info() 提供的信息不准确. 那么优化器可能做出错误的决定.**

ANALYZE TABLE —> 重新生成统计信息

• Memory 引擎不存储统计信息
• MyISAM 将统计信息 存储在 磁盘中, ANALYZE TABLE 需要进行一次全索引扫描来计算索引基数. (整个过程会锁表)
• mysql5.5 及其以前的版本, InnoDB 不保存索引统计信息在磁盘, 而是通过随机的索引访问进行评估并保存在内存中.
  ps: InnoDB 在首次打开表, 或是 执行 ANALYZE TABLE , 或是 表的大小发生了非常大的变化(大小变化超过了1/16或插入了20亿行数据) 的时候计算索引的统计信息. ** 在表的数据量很大时, 这可能会产生严重的性能问题, I/O 和 锁 **

减少碎片

** 索引的碎片 ** 和 ** 数据的碎片 **

** 索引的碎片化 ** 会降低查询效率, 尤其是 范围查询, 索引覆盖扫描等.

** 数据的碎片 **

• 行碎片: 单条数据行 被存储在多个地方的多个片段中. (根据 id 去访问一条数据行, 也会产生影响)
• 行间碎片: 多个数据行之前 不是被顺序的存储在磁盘中. (对全表扫描和聚簇索引扫描有很大影响影响)
• 剩余空间碎片: 硬盘上的 ‘数据页’ 中有大量的空余空间. (服务器会读取大量不需要的数据, 浪费)

MyISAM 三种情况都会发生, InnoDB 不会出现短小的行碎片, 会将其合并到一个片段中.
** OPTIMIZE TABLE ** 来优化表 , 不支持此命令的 存储引擎可以 alter table t1 ENGINE=< engine >来操作.