| 0.2 | 您所在的位置：网站首页 › mysql设置hash索引 › 0.2 |
0.2
|
文章目录
1、背景2、过程记录2.0、建库 使用 utf8mb42.1、修改索引的方法2.2、出问题了：FATAL No shared unique key can be found after ALTER! Bailing out2.3、解决办法：创建临时 唯一索引
3、反思3.1、主键 id 是必须的3.2、联合索引的修改
4、建表规范建表时创建主键索引建表时创建联合索引自动获取时间无符号存储 unsigneddatetime类型精确到毫秒、微秒的问题一个demo 表
5、表存储状态查看 所有数据库 所有表容量大小查看指定数据库容量大小查看所有数据库容量大小查看表、索引占空间
6、检验效果explain 语句filteredExtra 展示 Using filesortExtra 展示 using index/using where/using index conditionselect_type
参考资料
1、背景
在某种情况下，创建了一个表，并且使用 联合索引 作为表的主键，然后在实际测试中发现，联合主键设置不当，某些正常的数据也无法插入，所以需要对联合主键进行修改。 2、过程记录 2.0、建库 使用 utf8mb4utf8则是 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 '更新时间', 无符号存储 unsignedint类型(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列的长度进行设置，如下图： 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 conditionusing where 可以和其他情况一起出现 using index ：使用覆盖索引，无需回表,二级索引包括了要查询的所有字段 using where：不表示是否使用索引，只表示用到了过滤条件 using index condition：使用索引，需要回表 using index & using where：using index表示用到覆盖索引，无需回表;using where表示用到了过滤条件 select_typehttps://www.cnblogs.com/joimages/p/14521966.html 参考资料[1]、https://blog.csdn.net/weixin_41863129/article/details/104468990 |
在”设计表”中关于datetime类型设置如下： 长度： 最小为0，最大为6，就是显示时间的时候的秒后面的小数点后的位数，如长度为3则表示毫秒，长度为6则表示微妙。 小数点： 只能为0，就算填上别的数值，保存时候也会被改为0，可见只能为0。【本文地址】