0.2-Mysql学习-联合索引的修改和建表规范

1、背景

在某种情况下,创建了一个表,并且使用 联合索引 作为表的主键,然后在实际测试中发现,联合主键设置不当,某些正常的数据也无法插入,所以需要对联合主键进行修改。

2、过程记录

2.0、建库 使用 utf8mb4
  • utf8则是 Mysql 早期版本中支持的一种字符集,只支持最长三个字节的 UTF-8字符。
    而在MySQL5.5.3版本后,要在 Mysql 中保存 4 字节长度的 UTF-8 字符,就可以使用 utf8mb4 字符集了。例如可以用utf8mb4字符编码直接存储emoj表情,而不是存表情的替换字符。

  • utf8mb4_bin区分大小写,utf8mb4_0900_ai_ci 不区分大小写

# 如果存在权限问题 ERROR 3680: Failed to create schema directory
sudo chown -R mysql:mysql /usr/local/mysql/data

# 新建 schema
CREATE SCHEMA `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ;
2.1、修改索引的方法

Mysql 并没有提供修改索引设置的方法,所以只能先删除索引,再创建索引。

#新建普通索引
ALTER TABLE 表名 ADD INDEX idx_索引名 (字段1,字段2);
# 主键索引-先删除,再重建
ALTER TABLE 表名 DROP primary key, ADD primary key(字段1,字段2);
# 普通索引-先删除,再重建-唯一索引
ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 ADD UNIQUE KEY 索引名(字段1,字段2);

例子
alter table demo1 add index idx_a1_a2 (a1,a2);
ALTER TABLE demo2 DROP INDEX idx_a3;

2.2、出问题了:FATAL No shared unique key can be found after ALTER! Bailing out

在执行删除主键的过程中,报错了,查了一下资料发现是目前表除了主键索引,没有其他的唯一索引,这样会导致主键被删除之后,在新主键被创建之前,这段时间表是没有唯一索引的——可能会插入脏数据,删除索引和创建索引都是需要时间的。

2.3、解决办法:创建临时 唯一索引

在 2.2 中提到问题愿意是,若删除主键索引,表必须先存在 唯一索引,所以我们可以采取先创建唯一索引的方法,这里需要注意一个问题,本文创建的唯一索引是联合索引,遇到了另一个问题,即联合索引字段不许非空,但是默认建表时没有限定这个条件,所以还需要先满足相关属性非空的表DDL 限定。

3、反思

事中和小伙伴一起看了这个问题,然后参考了一下业内的规范,得出下面的结论。

3.1、主键 id 是必须的

由于业务场景的缘故,此表日增量很小,但是需要保证数据唯一性,开始直接将联合索引作为主键,其实这样的危害不单单在修改联合索引时遇到问题,将来一旦数据量大到需要分库分表的程度,就很难处理了。

3.2、联合索引的修改

先删除再创建,参考 2.1

4、建表规范

根据《阿里巴巴Java 开发手册》
主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。
说明:pk_ 即 primary key;uk_ 即 unique key;idx_ 即 index 的简称。
utf8mb4_bin区分大小写,utf8mb4_0900_ai_ci 不区分大小写

建表时创建主键索引
id  bigint auto_increment primary key,
建表时创建联合索引
 constraint uk_字段1_字段2 unique (字段1, 字段2)
自动获取时间
 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
无符号存储 unsigned

int类型(tinyint,smallint,int,bigint等),如果理论上不存在负值,一定要加 unsigned。这样做既安全,还能使数值上限翻倍(float unsigned 只会限制负值,不会增加数值上限)

demo_status tinyint unsigned not null comment '状态,0-生效;1-删除',
datetime类型精确到毫秒、微秒的问题

原文链接:https://blog.csdn.net/luo15242208310/article/details/113249376

mysql里面的datetime类型的精确度是可以到1 / 1 0 6 1/10^61/10
6
秒的(即百万分之一秒,即微妙),
某些客户端(如navicat for mysql)的显示经常只能看到精确到秒,其实是设计表的时候的配置问题,
可通过datetime列的长度进行设置,如下图:
在这里插入图片描述
在”设计表”中关于datetime类型设置如下:
长度: 最小为0,最大为6,就是显示时间的时候的秒后面的小数点后的位数,如长度为3则表示毫秒,长度为6则表示微妙。
小数点: 只能为0,就算填上别的数值,保存时候也会被改为0,可见只能为0。

一个demo 表
create table demo_table
(
	id bigint(20) unsigned auto_increment comment '主键' ,
	aaa_id bigint(20) unsigned not null comment '业务 ID',
	demo_status tinyint(4) unsigned not null comment '业务 状态,0-生效;1-删除',
	operator varchar(256) not null comment '最后操作人',
	gmt_create datetime not null default current_timestamp comment '创建时间',
	gmt_modified datetime not null default current_timestamp on update current_timestamp comment '更新时间',

	PRIMARY KEY (id),
	UNIQUE KEY  uk_aaa_id_gmt_create  (aaa_id,gmt_create),
	KEY idx_aaa_id_gmt_create (aaa_id,gmt_create)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='demo 表';

5、表存储状态

查看 所有数据库 所有表容量大小

select table_schema as ‘数据库’,
table_name as ‘表名’,
table_rows as ‘记录数’,truncate(data_length/1024/1024, 2) as ‘数据容量(MB)’,truncate(index_length/1024/1024, 2) as ‘索引容量(MB)’
from information_schema.tables order by data_length desc, index_length desc;

查看指定数据库容量大小

select table_schema as ‘数据库’,sum(table_rows) as ‘记录数’,sum(truncate(data_length/1024/1024, 2)) as ‘数据容量(MB)’,sum(truncate(index_length/1024/1024, 2)) as ‘索引容量(MB)’
from information_schema.tables where table_schema=‘数据库名’;

查看所有数据库容量大小

select table_schema as ‘数据库’,sum(table_rows) as ‘记录数’,sum(truncate(data_length/1024/1024, 2)) as ‘数据容量(MB)’,sum(truncate(index_length/1024/1024, 2)) as ‘索引容量(MB)’
from information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc

查看表、索引占空间

select table_schema as ‘数据库’,
table_name as ‘表名’,
table_rows as ‘记录数’,truncate(data_length/1024/1024, 2) as ‘数据容量(MB)’,truncate(index_length/1024/1024, 2) as '索引容量(MB)'from information_schema.tables
where table_schema='message_message’order by data_length desc, index_length desc;

6、检验效果

explain 语句
filtered

关于MySql中explain结果filtered的理解
MySQL首先使用索引(这里的type是range)扫描表a,预计会得到174条记录,也就是rows列展示的记录数。接下来MySql会使用额外的查询条件对这174行记录做二次过滤,最终得到符合查询语句的32条记录,也就是174条记录的18%。而18%就是filtered的值
一般而言,filtered 越接近1越好。

Extra 展示 Using filesort

表示使用了 order by,但是没有order by 属性没有命中索引,可以改善联合索引

Extra 展示 using index/using where/using index condition

using where 可以和其他情况一起出现

using index :使用覆盖索引,无需回表,二级索引包括了要查询的所有字段
using where:不表示是否使用索引,只表示用到了过滤条件
using index condition:使用索引,需要回表
using index & using where:using index表示用到覆盖索引,无需回表;using where表示用到了过滤条件

select_type

https://www.cnblogs.com/joimages/p/14521966.html

参考资料

[1]、https://blog.csdn.net/weixin_41863129/article/details/104468990

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值