一、好言
很多时候，一个人的改变是从另外一个人的到来或离去开始的。
二、背景
最近看《高性能Mysql》这本书的有关索引的章节，对于其中的碎片概念的没什么认知，就细致的咨询了下大神，顺便记录下笔记加上自己的理解。
三、有关索引碎片知识
3.1、如何查看表的索引大小及碎片优化问题
SELECT ROUND(SUM(data_length)/1024/1024) AS data_mb,
ROUND(SUM(index_length)/1024/1024) AS index_mb
FROM information_schema.tables;
data_length:数据长度;
index_length:索引长度;
free_data:所以这里是空闲空间或碎片大小;
https://dev.mysql.com/doc/refman/5.5/en/tables-table.html
The DATA_FREE column shows the free space in bytes for InnoDB tables.
For NDB Cluster, DATA_FREE shows the space allocated on disk for,
but not used
by, a Disk Data table or fragment on
disk. (In-memory data resource usage is reported by the
DATA_LENGTH column.)
如果free_data的数值大了,那么通常情况下，重建下没有坏处。所以一般先重建从库,然后从库提升为主库,然后重建主库,主库变为从库。
https://dev.mysql.com/doc/refman/5.5/en/innodb-file-defragmenting.html
所以在mysql对于非索引或者没有走索引的情况下，查询很慢，那么就可以考虑清理空间碎片
SELECT COUNT(*) FROM t WHERE non_indexed_column <> 12345;
<>是不走索引的。全表扫描，表文件越大扫的越慢，碎片跳不过去。所以该语句没法优化索引，只能从碎片角度考虑(可能还有其他角度)
那么怎么优化了？
对于innodb引擎,我们可以
https://dev.mysql.com/doc/refman/5.5/en/innodb-file-defragmenting.html
ALTER TABLE tbl_name ENGINE=INNODB
官网文档里面也说了，也可以把数据导出,然后删除表,再重新导入数据。
对于myisam引擎
OPTIMIZE TABLE table_name
3.2、删除和修改产生碎片问题
1：为什么说删除和修改会产生碎片？
插入为了性能，都是直接添加到末尾。否则需要维护空闲列表。
修改是由于有些变长字段修改会导致重新分配，比如varchar等字段，修改后造成原空间塞不下引起重新分配。所以可以使用char的定长来处理
变长，定长指存储空间变不变。varchar在存储时候是按照实际大小存的，不会留空间，但是char会留空间来操作。如果字段变化引起数据在原空间放不下，会插入到末尾去。那么之前的地方相当于删除了，就变成碎片了。
3.3、聚簇索引
主键就是聚簇索引，且是默认的。组合主键也是，索引非主键，非组合主键建立的索引都是非聚簇索引。
3.4、索引失效问题
对于范围条件查询，Mysql无法再使用范围列后面的其他索引列了，但是对于"多个等值条件查询"则没有这个限制。
在记录的时候还有这两个问题未定：
问题1：索引的碎片和数据碎片的道理是相同的吧？
问题2：二级索引的叶子节点包含了主键值，所以在列(A)上的索引就相当于在(A,ID)上的索引，如果有像WHERE A = 5 order by ID这样的查询,这个索引会很有作用。但如果将索引拓展为(A,B),则实际上就变成了(A,B,ID),那么上面查询的ORDER BY 子句就无法使用该索引做排序,而只能用文件排序了？
网友评论