mysql随笔
碎片
因为使用B+树,频繁的删除、更新和插入操作会导致碎片产生:
-
在InnoDB中,删除记录只是将这些行标记为“已删除”,不是真的从索引中物理删除了,磁盘的文件大小不会收缩。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。当执行插入操作时,MySQL会尝试使用空白空间,如果插入的数据刚好按照索引排序落在这个区间,可能会复用这个位置,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片;
-
对于大量的UPDATE,也会产生文件碎片化。Innodb的最小物理存储分配单位是页(page),而UPDATE也可能导致页分裂,频繁的页分裂,页会变得稀疏,并且被不规则的填充,所以最终数据会有碎片。例如原始数据长度varchar(100),大规模更新数据长度为50,这样的话,有50的空间被空白了,新入库的数据不能完全利用剩余的50,这就会产生碎片
处理碎片的几种方式:
optimize table table_name // 整理碎片,慎重使用
drop table_name //删除表
truncate table_name //初始化表
alter table table_name engine=InnoDB //本质上是recreate,Alter期间支持DML查询和更新操作
再就是复用id,可以解决一部分碎片问题
回表
非聚簇索引查询到的索引列,如果需要查找所有列的数据,则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键B+tree的叶子节点里面,非聚簇索引的B+树叶子节点都是存放的(索引列,主键)。
根据回表的基本原理,热门的查询字段和排序最好索引可以覆盖,这样就可以减少回表的操作。
索引
-
创建索引优化
数据量少的不适合加索引,区分度低的字段不适合加索引(性别、状态)
更新比较频繁的也不适合加索引 (如在线状态)
只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。 -
查询优化
应尽量避免在 where 子句中使用!=或<>操作符
应尽量避免在 where 子句中使用 or 来连接条件
任何查询也不要出现select *
避免在 where 子句中对字段进行 null 值判断