索引和表设计相关优化

最新推荐文章于 2024-07-21 22:27:18 发布

weixin_33882443

最新推荐文章于 2024-07-21 22:27:18 发布

阅读量85

点赞数

文章标签： python

原文链接：https://my.oschina.net/604388847/blog/3031488

版权

说明：myisam引擎的data/table1/下有3个文件，.frm、.MYD、.MYI分别是表结构、表数据和表索引

innodb引擎的data/table1/下有2个文件，.frm、.idb分别是表结构、表索引数据（索引和数据存一起，也就是b+tree）

1.设计一个计数器，统计一个网站的点击次数

create table hit_counter (
    cut int unsigned not null
) engine=innodb

网站每次点击都会导致计数器更新：

update hit_counter set cnt = cnt + 1;

问题在于，对于任何想要更新这一行事务来说，这条记录都有一个全局的互斥锁（mutex）。这会使得这些事务只能串行执行，要想获得更高的并发性能，也可以将计数器保存在多行中，每次随机选择一行进行更新修改如下：

create table hit_counter (
    slot tinyint unsigned not null primary key,
    cut int unsigned not null
) engine=innodb

然后预先在这张表中添加100条数据，随机选择一行进行更新，然后使用聚合查询，如下：

update hit_counter set cnt = cnt + 1 where slot = rand() * 100;
select sum(cnt) from hit_countet;

2.加快alter table操作的速度

（1）改变或删除一个列的默认值

alter table table1 alter column c1 tinyint(3) not null default 5;

alter table table1 alter column c1 set default 5;

上面的语句针对整个表做了读和写的操作，也就是拷贝了整张表到一个新表，甚至列的类型、大小和是否null都未改变。

实际上可以跳过创建新表的操作，列的默认值实际存储在.frm文件中，可以直接修改这个文件不需要改动表。

下面这条语句会直接修改.frm文件而不涉及表数据，所以操作速度非常快。

（2）不需要重建变的一些操作，通过修改.frm文件

移除（不是增加）一个列的auto_increment属性，增加、移除或更改enum和set常量，如果移除的行数据已经被用到，将返回一个空值

思想是为新的表结构创建一个新的.frm文件代替旧的，如下：

a.创建一张相同结构的空表，并进行所需要的修改

b.flush tables with read lock。这将关闭所有正在使用的表，并禁止任何表被打开

c.交换.frm文件

d.unlock tables

（3）高效载入数据到myisam表中

常用技巧先禁用索引 alter table table1 disbled keys，载入数据，重新启用索引 alter table table1 enabled keys,

它能发挥作用是因为构建索引的工作被延迟到数据载入之后，能通过排序构建索引，这样会快很多，并且使索引树的碎片更少、更紧凑。

但是这个方法对unique索引无效，disbled keys 只对非唯一索引有效。myisam会在内存中构建唯一索引，并且为载入的每一行检查唯一性，当这个索引的

大小超过了有效内存大小，载入速度就会变得越来越慢。

在现代版本的innodb中，有个类似的技巧，这依赖于innodb的快速在线索引常见功能，先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引

对于myisam可以通过2张表分别生成.MYD和.MYI文件然后拼到一起的方式

转载于:https://my.oschina.net/604388847/blog/3031488

关注