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