MySQL优化篇

库表结构优化

1.规范和反规范化

表设计之间性能和数据完整性，耦合和解耦合之间的取舍。

进而考虑是要冗余字段

2.数据类型选择

一句话原则：满足存储和扩展前提下，尽可能小的数据类型，以及支持索引

整数类型 tinyint,int,bigint

浮点数 float,double ,decimal 高精度数据decimal 或者可以用bigint

字符串类型选择：

char 18位身份证，6位邮政编码

varchar 文本数据，如名字，邮箱，产品描述等

ps: 对于IP地址这种存储场景用整数，MySQL中有相应的函数

对于ipv4 地址 INET_ATON和INET_NTOA函数

对于ipv6地址 INET6_ATON和INET6_NTOA函数

枚举类型 enum 不适用，查询性能很低

日期时间类型

日期用date ，时间用time，日期时间（时间戳）用datetime或者timestamp，但在不同时区时datetime和timestamp不一样，会话时区修改时timestamp会随着当前时区进行调整，datetime则不会变化

ps:

default current_timestamp --自动初始化
default current_timestamp on update current_timestamp --自动初始化且更新为当前日期时间

JSON类型 直接存JSON字符串

3.主键类型选择

优先用整数类型，避免用字符类型，便于索引检索。实在用uuid则字符串转16字节数字，存在binary（16）类型中

一般单机系统场景：自增主键整数型

分布式系统+保密+高安全场景： 考虑雪花算法或者UUID

索引优化

全表扫描在数据量大时磁盘IO消耗较大 ----->索引数据结构

B树 （每个节点都存有指向数据的指针）和B+树 （叶子节点存储指向数据的指针且叶子节点的兄弟节点间形成一个链表）

聚簇索引和辅助索引（一切的起源）

结构都为B+树

聚簇索引叶子节点存的不是指向数据的指针，直接存储表的数据

有主键，InnoDB用主键来聚集数据

没有主键时 InnoDB使用第一个非空的unique索引聚集数据

没有主键没有可用unique索引，使用隐藏的内部ID字段聚集数据

辅助索引叶子节点存的是指向主键的指针

因此查辅助索引的过程如下

辅助索引找到聚簇索引的主键 —>再在聚簇索引中找到存储的数据 （简称回表）

这种二级索引的设计好处 数据变化时只用更新聚簇索引，二级索引可以不用动。

坏处就是回表，走两次查询

---->因此主键建议用自增主键，值小， UUID值较大

复合索引

索引存储数据排序时是从左到右根据复合索引中的列排序

最左匹配原则，查询频率较高的放前面

复合索引 {s1,s2,s3} 相当于创建3个索引 {s1} ,{s1,s2} , {s1,s2,s3}

查询优化

待完成