面试题答案
一键面试SQLite显示类型和存储类底层存储机制原理
- 存储类
- SQLite采用动态类型系统，存储类决定数据在数据库中的实际存储方式。它有五种主要存储类：NULL、INTEGER、REAL、TEXT和BLOB。
- NULL：表示值缺失，在存储中占用最少空间，通常用于标识未设置或未知的数据。
- INTEGER：存储带符号整数，根据值的大小占用1、2、3、4、6或8字节。小整数占用较少字节，提高存储效率。例如，一个小的计数器值可能只需要1字节存储。
- REAL：以8字节IEEE 754格式存储浮点数值，适合存储近似数值，如科学计算中的测量数据。
- TEXT：存储文本字符串，根据所使用的编码（UTF - 8、UTF - 16BE或UTF - 16LE）存储字符。字符串长度可变，存储开销与字符串长度相关。
- BLOB：用于存储二进制大对象，如图片、音频片段等，数据按原样存储，不进行任何转换。
- 显示类型
- 显示类型是在CREATE TABLE语句中指定的数据类型，如“INT”“VARCHAR”等。然而，SQLite并不强制严格匹配显示类型和存储类。例如，即使将某列定义为“INT”显示类型，也可以存储符合其他存储类的合法值（如TEXT类型的数字字符串）。显示类型更多是一种提示，用于辅助SQLite在处理数据时做出更合适的选择，如在数据转换或约束检查时。
大数据量下基于业务需求和数据特征的优化
- 基于存储结构的优化
- 选择合适的存储类
- 整数类型数据：如果业务数据是整数且范围已知且较小，如表示状态码（0 - 255），选择INTEGER存储类并利用其可变字节存储特性，指定合适的字节数，避免浪费空间。对于更大范围的整数，确保选择足够字节数的INTEGER类型，防止溢出。
- 数值近似数据：若数据是科学测量值、货币金额（在允许一定精度损失时）等需要近似表示的数值，使用REAL存储类，它占用固定8字节，相比精确存储整数类型在空间上更紧凑，特别是对于大数据量。
- 文本数据：对于短文本且数据量巨大的情况，如标签、类别名称等，要考虑使用更紧凑的编码（如UTF - 8）存储TEXT数据。如果文本数据长度基本固定，可考虑使用固定长度文本存储方式优化存储结构。对于长文本，如文章内容，可考虑分块存储策略。
- 二进制数据：对于BLOB数据，如图片、视频片段，要根据业务需求决定是否直接存储在SQLite数据库中。如果数据量极大，可考虑将BLOB数据存储在文件系统中，而在数据库中仅存储文件路径，以减少数据库文件大小。
- 利用索引
- 根据查询模式创建合适的索引。对于经常用于WHERE子句条件的列，创建索引可以显著提高查询速度。例如，如果经常按某个整数字段进行范围查询（如SELECT * FROM table WHERE integer_column BETWEEN 10 AND 20），为该整数列创建索引可以加快查询执行。
- 对于文本列，如果经常进行LIKE查询（如SELECT * FROM table WHERE text_column LIKE 'prefix%'），可以考虑创建全文索引（FTS），FTS适用于大数据量文本搜索，能提供比普通LIKE查询更高效的搜索性能。
- 选择合适的存储类
- 基于查询执行的优化
- 避免类型转换：确保查询条件中的数据类型与列的存储类一致。例如，如果列存储为INTEGER，在查询时尽量使用整数常量，避免将字符串类型的数据转换为整数进行比较，因为类型转换会增加查询执行的开销。
- 优化JOIN操作：在大数据量下，JOIN操作可能成为性能瓶颈。如果涉及多个表的JOIN，确保JOIN条件基于索引列，并且合理选择JOIN类型（如INNER JOIN、LEFT JOIN等）。对于大数据集的JOIN，可以考虑使用临时表缓存中间结果，减少重复计算。
- 批量操作：尽量使用批量插入、更新等操作，而不是单个操作。例如，使用INSERT INTO... VALUES (...),(...),...语法一次性插入多条记录，而不是多次执行单个INSERT操作，这样可以减少磁盘I/O和事务开销，提升整体性能。
- 查询计划分析：使用EXPLAIN QUERY PLAN语句分析查询计划，了解SQLite如何执行查询。根据分析结果，调整查询语句或数据库结构，如添加或删除索引，优化JOIN顺序等，以提高查询性能。