有哪些档案网站,中苏园林建设集团网站,广告设计与制作学啥,做网站送白酒聚簇索引 聚簇索引像一本按目录排版的书#xff0c;用空间换时间#xff0c;适合读多写少的场景。设计数据库时#xff0c;主键的选择#xff08;如自增ID vs 随机UUID#xff09;会直接影响聚簇索引的性能。
什么是聚簇索引#xff1f; 数据即索引#xff1a;聚簇索引…聚簇索引 聚簇索引像一本按目录排版的书用空间换时间适合读多写少的场景。设计数据库时主键的选择如自增ID vs 随机UUID会直接影响聚簇索引的性能。
什么是聚簇索引 数据即索引聚簇索引的叶子节点直接存储整行数据而不是指向数据的指针因此表中的数据行物理上按索引键的顺序存储。 唯一性一张表只能有一个聚簇索引就像一本书只能按一种顺序排版。
类比生活场景 普通索引非聚簇索引像一本书末尾的“关键词索引”每个关键词后标注页码你需要先查索引再翻到对应页。 聚簇索引像书的目录本身章节内容严格按照目录顺序排版。找某一章时直接按目录顺序翻到对应页即可无需二次跳转。
优点 查询快范围查询如 WHERE id BETWEEN 1000 AND 2000效率高因为数据在磁盘上是连续的。 排序快如果按聚簇索引的字段排序如主键数据库可以直接按物理顺序读取无需额外操作。
缺点 插入/更新慢新增数据时如果新数据需要插入到中间位置可能导致数据页分裂类似电话簿插页时需要撕开重新装订。 依赖主键设计如果主键设计不合理例如用随机UUID可能导致存储碎片化降低性能。
常见应用 主键默认是聚簇索引如MySQL的InnoDB引擎。 适合频繁查询的字段比如用户表的用户ID、订单表的订单时间。
哈希索引 哈希索引通过哈希函数将数据的关键字如手机号直接转换成存储位置适合快速等值查找但无法支持范围查询或排序。
什么是哈希索引 哈希函数将任意长度的输入如字符串、数字转换成固定长度的哈希值如 0x3A7F。 直接定位数据通过哈希值直接找到数据在内存或磁盘上的存储位置无需遍历。
类比生活场景 普通索引如B树索引像图书馆按书名首字母分类的书架需要按顺序查找。 哈希索引像快递柜的编号输入手机号就能秒定位到具体柜子。
优点 查询极快等值查询如 WHERE id 123的时间复杂度接近 O(1)。 无数据顺序要求适合随机读写场景如用户登录、缓存。
缺点 不支持范围查询无法高效执行 WHERE id 100 或 ORDER BY。 哈希冲突不同关键字可能生成相同的哈希值如手机号A和B被分配到同一个柜子需要额外处理如链表串联。 动态扩容成本高数据量增长时哈希表可能需要重新分配空间并重新计算所有哈希值。
常见应用 内存数据库如Redis的键值存储。 快速查找表如用户Session、短链映射。 数据库的Hash Join关联查询时临时使用哈希表加速。
哈希索引 vs B树索引 覆盖索引
使用普通索引时通常需要两步1.查找索引2.回表操作
使用覆盖索引则通常是直接从索引中直接获取数据也就是省略了回表步骤
覆盖索引的优点 减少IO操作查询时直接从索引中获取数据无需访问表中的数据行显著提高查询性能特别是在数据量很大或者磁盘IO成本很高。 索引中的数据索引通常由被索引的列及其指向的行标识符组成覆盖索引会存储更多的数据以满足查询需求
索引分类
按物理存储方式分类 聚簇索引、非聚簇索引
按功能分类 唯一索引、普通索引、全文索引、空间索引
按索引结构分类 BTree索引、Hash索引
按应用场景分类 单列索引、复合索引、覆盖索引
按存储引擎支持情况分类
InnoDB 支持 B-Tree 索引、唯一索引、全文索引从 MySQL 5.6 开始。MyISAM 支持 B-Tree 索引、全文索引。Memory 支持 Hash 索引和 B-Tree 索引。
最左前缀原则 “最左前缀原则”是数据库尤其是 MySQL 中在使用复合索引时的一条重要规则它规定在复合索引中查询条件必须从索引的最左侧开始连续地使用索引列才能使该索引被有效利用。 如果创建了一个复合索引(col1, col2, col3)当使用索引时必须是从左到右使用如果跳过前面的去使用后面的话就会导致索引失效。 提高查询效率 通过利用复合索引数据库可以直接定位到符合条件的数据行减少扫描数据量显著提升查询速度。 优化设计索引 在设计复合索引时应考虑查询的常用模式将最常用的查询条件放在索引的最左侧。例如如果大多数查询都是根据 col1 进行过滤再根据 col2 进行细分那么将 col1 放在最前面可以最大程度发挥索引优势。 避免索引失效 如果查询条件没有覆盖复合索引的最左前缀那么索引将无法被利用这样会导致查询性能下降。因此在编写 SQL 语句和设计索引时必须充分考虑这一原则。 和 IN 条件的顺序无关性 对于等值查询和 IN 查询条件的顺序不影响索引使用。MySQL 优化器会自动调整条件顺序以匹配索引的最左前缀。 WHERE b 2 AND a 1 AND c 3; 索引 (a, b, c) 优化器会将条件重排为 a 1 AND b 2 AND c 3完全匹配索引。 即使条件顺序不同索引仍能覆盖所有列。
判断索引使用情况
以MySQL为例
查看索引的使用统计
-- 查看表的索引使用情况需开启性能模式
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME your_table;-- 查看索引的碎片化程度
SHOW INDEX FROM your_table;
分析查询是否使用索引
EXPLAIN SELECT * FROM your_table WHERE indexed_column value; 关键字段 typeref 或 range 表示使用了索引。 key显示实际使用的索引名称。 rows扫描的行数值越小效率越高。
优化数据库表
OPTIMIZE TABLE table_name;作用 回收未使用的磁盘空间 删除大量数据后会有空闲的磁盘空间OPTIMIZE TABLE 可以释放这些空间。 重新组织索引 对于索引密集的表优化索引布局可以提高查询性能。 提高数据读取速度 通过整理数据和索引可以减少磁盘 I/O 操作提升读取速度。
索引失效条件 使用范围查询、不满足最左前缀原则、使用了不等于运算符、使用函数或表达式、隐式类型转换、模糊查询以通配符开头、使用OR、查询返回过多数据、数据分布不均匀、索引未覆盖查询无法使用覆盖索引、表统计信息不准确数据库优化器依赖表的统计信息进行查询计划的选择。如果统计信息过时或不准确可能导致错误地放弃索引、使用了不支持索引的操作
如果避免索引失效
遵循最左前缀原则合理设计索引。避免在索引列上使用函数、表达式或隐式类型转换。在模糊查询中尽量避免 % 开头。对 OR 条件进行优化例如重构为 UNION ALL。确保表的统计信息及时更新。使用覆盖索引减少回表查询。确保查询返回的数据量较少。
优化查询性能的方法
减少请求的数据量
只返回必要的列最好不要使用SELECT *语句、使用LIMIT语句来限制返回的数据。缓存重复查询的数据使用缓存可以避免在数据库中进行查询特别是在要查询的数据经常被查询时使用缓存会极大的提升查询性能
减少服务器端扫描的行数
使用索引来覆盖查询 避免索引失效
查询重构优化
避免使用复杂的JOIN和子查询批量操作代替循环操作循环执行单条 SQL 会增加网络和事务开销。 -- 不推荐循环插入
INSERT INTO logs (message) VALUES (log1);
INSERT INTO logs (message) VALUES (log2);-- 推荐批量插入
INSERT INTO logs (message) VALUES (log1), (log2);
数据库配置优化
调整缓冲区大小 -- 增加 InnoDB 缓冲池大小通常设为物理内存的 70%~80%
SET GLOBAL innodb_buffer_pool_size 4G; 配置数据库连接池避免短连接使用连接池如 HikariCP、Druid复用连接。 参数调优设置合理的最大连接数和空闲超时时间
架构层面优化
读写分离 主库处理写操作和高一致性读。 从库处理读操作通过复制同步数据。
分库分表 垂直分表将大字段如 TEXT、BLOB拆分到独立表。 水平分库按业务分库如订单库、用户库。 水平分表按哈希或范围分表如 user_0、user_1。
