MySQL数据类型、InnoDB与MyISAM和索引

1、MySQL数据类型

字段类型：

• 整型
  • TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别使用1、2、3、4、8个字节储存空间，一般情况下越小的列越好。INT(11)中的数字只是规定了连接工具的显示字符的个数，对于存储和计算是没有意义的。
• 浮点数
  • FLOAT和DOUBLE为浮点类型，DECIMAL为高精度小数类型。CPU原生仅支持浮点类型计算，不支持DECIMAL类型计算，因此使用DECIMAL的计算会比浮点类型需要更高的代价。FLOAT、DOUBLE和DECIMAL都可以指定列宽，例如DECIMAL(18,9)表示总共18位，取9位存储小数，剩下的9位存储整数部分。
• 字符串
  • 主要有CHAR和VARCHAR两种类型，前者是定长的，后者是变长的。
  • VARCHAR这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行UPDATE时可能会使行变得比原来长，当超出一个页所能容纳的长度大小时，就要指定额外的操作。MyISAM会将行拆分成不同的片段存储，而InnoDB则需要分裂页来使行放进页内。
  • VARCHAR会保留字符串末尾的空格，而CHAR会删除。
• 时间和日期
  • MySQL提供了两种相似的时间日期类型：DATETIME和TIMESTAMP。
  • DATETIME能够保存1001年到9999年的日期和时间，精确为秒，使用8字节的存储空间，与时区无关。默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME的值，例如”2023-08-29 09:46:08”，这是ANSI标准定义的日期和时间的表示方法。
  • TIMESTAMP和UNIX时间戳相同，保存1970年1月1日午夜（格林威治时间）以来的秒数，使用4个字节，只能表示1970年到2038年。和时区有关，也就是说一个时间在不同的时区所代表的时间是不同的。
  • MySQL提供了FROM_UNIXTIME()函数把UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为UNIX时间戳。默认情况下，如果插入时没有指定TIMESATMP列的值，会将这个值设置为当前时间。应该尽量使用TIMESATMP这个类型，因为他比DATETIME空间效率更高。

选择优化的数据类型：

1. 更小的通常更好；更小的数据类型通常更快，因为它占用更少的磁盘、内存和CPU缓存。并且处理时需要的CPU周期也更少；
2. 简单就好，例如：整形比字符串操作代价更低；不是让字符串来存储时间日期、用整形存储IP地址等
3. 避免使用NULL列，如果查询中包含有NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。尽管把可为NULL的列改为NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该尽量避免设置可为NULL的列。

VARCHAR和CHAR：

1. VARCHAR是最常见的字符串类型。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内。
2. VARCHAR适合的使用场景：
   1. 字符串最大长度比平均长度大很多
   2. 列的更新很少，所以·碎片不是问题
   3. 使用了像UTF-8这样的字符编码，每个字符都是用了不同的字节数进行存储。
3. CAHR适合的使用场景：
   1. 很短的字符串
   2. 所有值都接近一个长度，比如MD5值。
4. VARCHAR(50)和CHAR(50)有什么区别？
   1. 如果你定义一个 VARCHAR(50) 列，并存储一个长度为20的字符串，那么它将占用21个字节的存储空间（20个字符加上1个字节的长度信息）。
   2. 如果你定义一个 CHAR(50) 列，并存储一个长度为20的字符串，那么它将仍然占用50个字节的存储空间，不管实际存储了多少字符。
   3. 因此，VARCHAR 在存储上更加节省空间，适用于存储可变长度的字符串，而 CHAR 则在存储上较为浪费空间，适用于存储固定长度的字符串。

BLOB和TEXT的存储和其他类型不同，他们存储的内容会转存到外部文件，表中存储的指向外部文件的指针，通常为1-4个字节。最好不适合使用BLOB和TEXT作为索引，排序中取每列的最前max_sort_length个字节进行排序计算而不是全文。

数据类型	所属类型	占用字节	说明
TINYINT	整型	1个字节	INT(11)这样的数据类型后规定长度，只是规定了交互工具显示字符的个数，对于存储和计算没有意义。
SMALLINT	整型	2个字节	同上
MEDIUMINT	整型	3个字节	同上
INT	整型	4个字节	同上
BIGINT	整型	8个字节	同上
FLOAT	浮点型	4个字节	CPU原生支持浮点运算，计算代价小于decimal类型。
DOUBLE	浮点型	8个字节	同上
DECIMAL	高精度小数类型	根据声明长度决定	CPU原生不支持此类型运算，CPU计算代价大于浮点型。DECIMAL(M,D)中M表示总位数，D表示小数部分位数。M <= 9，占用4字节；M <= 18，占用8个字节；M <=38，占用16个字节。
VARCHAR	字符串	根据实际长度决定	可变长度，占用实际存储字符数 + 1个字节（n+1）。
CHAR	字符串	根据声明长度决定	固定长度，根据固定的长度分配固定的存储空间。
TEXT	字符串	根据实际长度决定	可变长度文本数据，适用于大段文本，根据实际存储占用不同空间
DATETIME	日期时间	8个字节	与时区无关，精确到秒，保存范围1001到9999年，默认表示“yyyy-MM-dd HHss”
TIMESTAMP	日期时间	4个字节	与时区有关，和UNIX相同，保存1970-01-01 00:00:00到2038-01-19 03:14:07 UTC的时间戳。插入时默认值为当前时间
BLOB	二进制类型	根据实际长度决定	可以存储各种二进制数据，例如图像、音频、视频、压缩文件等。

2、存储引擎

1. InnoDB
   • 是MySQL默认事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其他存储引擎
   • 支持事务，4个隔离级别，默认为可重复读。
   • 主索引时聚簇索引，因为在索引中保存了数据，避免直接读取磁盘。
   • 内部做了许多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并自动创建自适应哈希索引、能够加速插入操作的插入缓冲区等等。
   • 支持真正的在线热备份。其他存储引擎不支持在线热备份，要获取一致性视图就要停止对所有表的写入，在读写混合场景中，停止写入意味着停止读取。
2. MyISAM
   • 设计简单，数据以紧凑格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。
   • 提供大量特性，包括压缩表、空间数据索引等。
   • 不支持事务。
   • 不支持行级锁，只有表级锁。读取时会在表上加入共享锁，写入时对表加上排他锁。单台有读取操作的同时，也可以往表中添加新记录，这被称为并发插入。
   • 如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

比较InnoDB和MyISAM：

1. 事务：InnoDB支持事务，可以使用commit和rollback；MyISAM不支持。
2. 并发：InnoDB支持全局锁、表锁、页锁、行锁；MyISAM只支持表锁。
3. 外键：InnoDB支持外键；MyISAM不支持。
4. 备份：InnoDB支持在线热备份；MyISAM不支持真正热备份。
5. 崩溃恢复：MyISAM崩溃后发生损坏的概率比InnoDB高很多，且恢复速度更慢；InnoDB更好。
6. 全文搜索：InnoDB不支持；MyISAM支持。
7. 空间占用：InnoDB支持事务和行级锁，会占用更多磁盘空间；MyISAM占用更少。
8. 自增主键：都支持。InnoDB插入时会锁定整表；MyISAM不会锁定。
9. 其他特性：MyISAM支持压缩表和空间索引。

3、索引（B+树）

B+树是常用于数据库和文件系统一种数据结构，可以高校的存储检索有序数据。
在MySQL中，B+树用于管理索引，帮助加快数据库的查询速度。
B+树的特点：
平衡性：B+树基于B树进行改造，拥有B树的平衡性。通过在插入删除操作时进行节点分裂和合并来保持树的平衡。
有序性：B+树中所有叶节点都按照从左到右的顺序连接到一起，保证了范围查询的效率。
只有叶节点存储数据：B+树种，所有的非叶节点都不存储数据，这造成了在树结构中查询数据的路径更短，提高查询效率。

索引：

加速数据库查询操作的数据结构，理解为书中的目录。索引存储了一些列的值和对应的行位置。

分类：
普通索引：一个表中可以有多个普通索引，没有什么限制，允许重复和null。
唯一索引：保证列中的值必须是唯一的，但是允许为null。
主键索引(聚簇索引)：保证列中的值必须是唯一的，但是不允许为null。
组合索引：多个列组成的索引（相当于二级索引，比如name,age组合索引，先检索name再检索age得出数据），遵循最左原则（一直向右匹配，遇到范围查询【>、<、between、like】就停止匹配）。
全文索引：只能在文本类型（CHAR、VARCHAR、TEXT）类型字段上创建。

索引操作：

1. 创建索引

create [unique/fulltext] INDEX index_name on table_name (column1, column2, ...);
alter table table_name add INDEX index_name (column1, column2, ...);

  1. 普通索引

create index index_name on table_name(column1);

  2. 唯一索引

create index unique index on table_name(column);

  3. 主键索引

//与唯一索引创建一样，如果字段使用了private key关键字，则该字段的索引为主键索引

  4. 组合索引

create index index_name on table_name(column1, column2);

  5. 全文索引

create fulltext index index_name on table_name(colum1, column2);

1. 删除索引

drop INDEX index_name on table_name;
alter table table_name drop INDEX index_name;

1. 查看索引

show INDEX from table_name;
判断索引是否生效：使用explain关键字。
explain select * from table_name where column = 'value';
如何避免索引失效？

1. 组合索引中，准寻最左原则
2. 索引列上不要进行任何操作，如：计算、函数、类型转换
3. 尽量使用覆盖索引
4. 字符串加单引号（不加可能发生索引列隐式转换，导致失效）