数据库索引与缓存

数据库设计 & 索引和缓存管理

聚簇索引，复合索引，非B+树索引...

表结构是否合理，范式化？数据类型？

varchar（可变长，便于压缩）和char在底层存储上的差异(char不存指针, varchar存指针)
varchar和text/blob（存储一个指针，外部fs存储的原文）, 在太大的时候(>65535=16K, innodb的一个最小block, 单行限制)的时候用varchar会直接报错
- TEXT最大64KB, LONGTEXT最大4GB

索引:

提高select性能最好的办法就是在经常查询的列上面建立索引

索引条目充当指向表行的指针，允许查询快速确定哪些行符合WHERE子句中的条件，并检索这些行的其他列值。

索引尽可能建立在取值为不为空，而且值唯一（或者很少有重复的）列上面

并不是索引建立的越多越好，不必要的索引建立会浪费空间和时间来决定用什么索引搜索

如果建立了一堆索引，对于数据更新的时候，索引改变需要调整，那么由于大量的索引建立，导致调整b+ tree需要消耗大量的时间（所以需要管理者权衡考虑！）

系统会自动在自增的主键建立索引，这样最大的好处就是在join操作的时候，可以快速定位到目标

当表格比较小的时候，建立索引很浪费，不如全表扫描

获取要findall的时候，索引也很浪费，本来就是要把整个表数据读取出来，索引失去意义

聚簇索引: 索引的顺序和数据存储的顺序完全一致的索引叫做聚簇索引, 主键索引/不含主键时的唯一列

作业中问答题:

A.聚簇索引效率高的原因是他是按照数据在磁盘上的物理存储顺序排列的，b+树在磁盘上直接顺序读写效率远远高于随机读写。此外，聚簇索引的叶子节点包含实际的数据行，这样可以减少磁盘I/O操作，提高查询效率。聚簇索引还有助于减少数据页的分裂，因为它按照索引键的顺序存储数据，插入和更新操作更不容易引起数据页的重组。

因而主键需要有增长性来保证range query的性能, 随机主键是不好的, 会导致写入乱序, 稀疏页, 缓存miss(OS 预取)和频繁分裂

主键尽量短: 字符串比较是从前往后的, 并不是数字比较

复合索引: 生效性: 最左匹配原则

索引列(a,b,c)

CREATE INDEX book_author_title_date_idx ON books (author ASC, title ASC, publish_date DESC);

只有当左边的生效的时候, 右边才会生效

原因:bc是全局无序, 在相同a下有序的
应用:区分度大的字段在前面

只有=基本走索引(大数据量下), range query可能是索引下推/放到最后判断条件, 看cost

a=2 and c=1, 由最左匹配, a索引,c放到最后条件不走

a=2 and b>1, 都能利用

索引失效:

带左的模糊匹配 LIKE %X
对索引列进行计算
MySQL在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
联合索引非最左匹配
...

前缀索引: 可以只使用字符串的前几个字符进行索引, 减少IO和单条索引的大小

hash索引：只支持=查找

压缩: 有些时候不压缩反而比较好，因为每个record都是长度一样的时候，反而可能比较利于查找。所以一般来说要有所权衡。

如果一个表格里面的，很多查询对于这个表都不包含一些列，怎么办？

考虑切开这个表，然后使用join连接，拆两个表，常用的放一个，不常用的放一个

数据库的block按照列存储，好处就是统计一列的总和，可能效率非常高，缺点是多次插入一个行的时候非常不适合。

blob

是一个二进制数组
不存储在表格里面，通过指针关联
如果要判断两个blob相等，没有必要把两个特别大的blob读取出来，可以通过计算hash值，如果hash不等数值，那肯定不相等

存图片base64用blob而不用text/varchar的原因

B.(存图片)使用LONGBLOB, VARCHAR有长度最大限制,默认是64KB, 而图书封面图像base64编码之后很可能会超过这个限制,此外还有效率问题, BLOB类型不需要字符集转换, 对图片类型的二进制数据字符集转换没什么意义

meta data:

例如table_open_cache,innodb_page_size,autocommit,innodb_change_buffering=all...

默认开启autocommit建议关掉

把大型的事务分成几个小型的事务，不要把一个执行插入特大的大量数据的事务运行，这样会特别消耗性能。

缓存:

在MySQL中， innodb_buffer_pool_instances 是一个配置参数，定义了缓冲池的实例数。每个实例都有自己的缓冲池，这意味着多个实例可以并行处理数据库请求，提高整体性能。特别是在多核心处理器中，多实例可以最大限度地利用CPU资源。

innodb_buffer_pool

用途: innodb_buffer_pool 是 InnoDB 存储引擎用来缓存数据和索引的主要内存区域。它用于存储表和索引的数据页，以加快数据访问速度。
功能: 通过缓存数据页，减少对磁盘的访问，从而提高数据库的性能。适用于 InnoDB 表。
配置: 通常设置为可用内存的 50% 到 75%，以便为操作系统和其他应用程序留出足够的内存。

table_open_cache

用途: table_open_cache 是 MySQL 用来缓存已打开表的文件描述符和相关元数据的缓存。
功能: 通过缓存表的元数据，加快表的打开速度，减少文件系统调用的开销。
配置: 影响 MySQL 同时可以打开的表的数量。根据服务器的工作负载和表数量进行调整。

区别

缓存内容:
- innodb_buffer_pool 缓存的是数据页和索引页。
- table_open_cache 缓存的是表的文件描述符和元数据。
作用对象:
- innodb_buffer_pool 仅适用于 InnoDB 存储引擎。
- table_open_cache 适用于所有存储引擎的表。
性能影响:
- innodb_buffer_pool 直接影响数据读取和写入的性能。
- table_open_cache 影响表的打开和关闭速度。

mysql 缓存多实例原因:

并发优化, 多核支持和减少锁竞争
便于内存限制管理和隔离
提高缓存命中率(原因类似分区提高数据访问速度)
负载均衡, 多环境支持
故障隔离, 灵活拓展

id选择: 能自增自增, 不能自增也要尽可能维护增长性, 例如雪花算法

作业:

D. 自增主键，UUID损耗空间较大, 且纯粹的UUID会损失自增的特性, 使得磁盘上聚簇索引的效率下降, 不适合范围查询。在我的项目之中书籍数量并没有到需要分库分表才能装得下,所以需要分布式的ID独立性的情况,自增id在时间和空间上都有优势。并且后续ID缓存分析等,作为连续数字的ID相较uuid也更容易去和bitmap等集成

InnoDB和MyISAM差异

事务支持：InnoDB支持事务处理，而MyISAM不支持。
锁机制：InnoDB支持行级锁，而MyISAM使用表级锁。
外键支持：InnoDB支持外键约束，MyISAM不支持。
崩溃恢复：InnoDB提供事务日志来支持崩溃恢复，MyISAM没有这种机制。
存储结构：InnoDB的表和索引存储在一起，而MyISAM的索引和数据是分开存储的。
性能：MyISAM在读取密集型操作中表现更好，InnoDB在写入密集型操作中表现更好。
空间使用：MyISAM支持压缩表，可以节省磁盘空间，InnoDB不支持压缩。
索引类型：InnoDB支持B-tree和全文索引，MyISAM也支持B-tree和全文索引，但不支持哈希索引。
表行数：MyISAM存储表的行数，InnoDB不存储表的行数，需要全表扫描来计算

数据库设计 & 索引和缓存管理​

数据库设计 & 索引和缓存管理