自己做一网站,seo人员的相关薪资,潍坊网站托管,网站资源做缓存1. 索引
在InnoDB存储引擎中#xff0c;索引分为聚簇索引和辅助索引两种类型。
聚簇索引是指基于表的主键构建的索引#xff0c;它决定了表中数据的物理存储顺序。也就是说#xff0c;聚簇索引中的键值按照主键的顺序来排序#xff0c;并且每个叶子节点存储的是整个表行的…1. 索引
在InnoDB存储引擎中索引分为聚簇索引和辅助索引两种类型。
聚簇索引是指基于表的主键构建的索引它决定了表中数据的物理存储顺序。也就是说聚簇索引中的键值按照主键的顺序来排序并且每个叶子节点存储的是整个表行的数据。因此通过聚簇索引可以快速地定位到特定主键的行数据而且相邻的行数据在物理上也是相邻存储的。如果表没有主键则InnoDB会选择一个唯一的非空索引作为聚簇索引如果没有这样的索引则会隐式地创建一个隐藏的主键。
辅助索引是指除了聚簇索引以外的其他索引它们的叶子节点存储的是主键值和指向对应行数据的指针。因此通过辅助索引可以快速地定位到符合特定条件的行数据并且可以使用覆盖索引避免访问聚簇索引中的行数据。辅助索引可以有多个每个辅助索引都有自己的B树结构。
需要注意的是聚簇索引和辅助索引在物理上是相互独立的。如果同时使用多个辅助索引每个索引都需要单独维护一个B树结构这可能会影响写入性能。此外在使用辅助索引时需要特别注意覆盖索引和最左前缀索引的使用以保证查询性能的最大化。 InnoDB必须有一个主键
InnoDB的表为什么必须有一个主键 聚集索引B树。 在InnoDB存储引擎中表中的数据是按照主键索引的B树结构进行组织的每行数据对应B树中的一个叶子节点。这种结构使得InnoDB能够高效地支持基于主键的数据访问和查询操作。
InnoDB中的B树
B树多路平衡搜索树 所有叶子节点都在同一层 叶子节点构成了一个双向链表 结点的大小都是16K
为什么选择B树 降低磁盘IO次数树相对矮胖 便于支持范围查询
索引使用场景(看col所在列是否设置了索引有就使用)
where colgroup by colorder by col
不会使用索引的场景 4. 没有使用上面三个的时候 5. 区分度不高的列 6. 经常修改的列不使用索引修改的话要改树维护代价很高 7. 数据量少的表没必要创建
2.约束 外键约束 示例如下
覆盖索引
覆盖索引一种数据查询方式针对辅助索引直接通过辅助索引的B树就能找到我们要查找的内容无需再进行回表查询。
覆盖索引是一种特殊的索引它包含了查询所需的所有列而不仅仅是索引列。当一个查询可以通过覆盖索引完全满足时就不需要进行回表查询。
在传统的查询中当使用非覆盖索引时数据库需要根据索引找到匹配的行并通过回表操作去访问主表来获取其他列的值。这个过程会增加额外的磁盘I/O和CPU开销。
相比之下覆盖索引可以避免回表操作。因为覆盖索引已经包含了查询所需的所有列数据库可以直接从索引中获取所需的数据而不需要再次访问主表。这样可以显著提高查询性能减少了不必要的磁盘I/O和CPU开销。
使用覆盖索引可以有效地减少查询的响应时间特别是对于那些只需要部分列数据的查询。然而覆盖索引也有一些限制例如索引列的长度和数量等。在设计数据库时需要根据具体的业务需求和查询模式来合理选择是否使用覆盖索引。
尽量不使用select * 只写我们需要的字段。
最左匹配规则
在MySQL中最左匹配规则是指当使用复合索引即包含多个列的索引进行查询时MySQL会从左到右依次匹配索引的列并且只有在前面的列都有匹配条件时才能使用后面的列进行进一步的筛选。
具体来说如果创建了一个复合索引 (col1, col2, col3)那么在查询时MySQL将首先尝试使用 col1 进行匹配。只有当查询中包含了 col1 的条件时索引才会被用到。如果查询中没有 col1 的条件那么索引将无法使用。
如果查询中有 col1 的条件MySQL 将根据剩余的条件继续匹配 col2然后是 col3。只有在前面的列都有匹配条件的情况下才能使用后面的列进行进一步的筛选。
这意味着如果要充分利用复合索引的最左匹配规则需要确保查询条件中的列与索引的列按照相同的顺序并且从左到右逐渐添加条件。
举个例子假设有一个复合索引 (col1, col2, col3)如果查询条件中只有 col2 和 col3 的条件而没有 col1 的条件那么该索引将无法使用。因为最左匹配规则要求必须从索引的最左边开始进行匹配。
因此在设计和优化索引时了解最左匹配规则对于正确选择索引和编写高效的查询语句是非常重要的。
索引下推面试会问
索引下推Index Condition Pushdown是MySQL中的一种优化技术用于在执行查询时将过滤条件尽可能地下推到存储引擎层级进行处理。
在传统的查询执行过程中MySQL会首先根据WHERE子句中的条件从表中检索出满足条件的行然后再应用其他的过滤条件。这意味着MySQL需要从磁盘中读取大量的数据然后在服务器层级上进行进一步的过滤。
而索引下推的优化技术可以在存储引擎层级上进行更早的过滤操作。当查询包含了索引列和其他非索引列的条件时MySQL可以将非索引列的过滤条件下推到存储引擎层级进行处理。这样在读取磁盘数据之前就能进行更精确的过滤减少了不必要的磁盘I/O和数据传输量。
通过索引下推MySQL可以最大限度地利用索引来提高查询性能。它可以减少从磁盘读取的数据量降低CPU和内存的使用从而加快查询的执行速度。
需要注意的是索引下推并不是对所有类型的查询都适用。它主要适用于那些包含了索引列和其他非索引列条件的查询语句。此外索引下推的效果也取决于存储引擎的具体实现和版本。
综上所述索引下推是MySQL中的一种优化技术通过将非索引列的过滤条件下推到存储引擎层级进行处理以减少不必要的磁盘I/O和数据传输量从而提高查询性能。
可以用EXPLAIN查看SQL语句是否索引下推了 如下图所示
没有索引下推机制的话server存储引擎server向引擎存储数据在server层根据条件判断进行数据的过滤。 有了索引下推存储引擎会过滤数据最终将数据返回。
索引下推的实现方式通常涉及数据库查询优化器和存储引擎的协作。当查询到达数据库系统时优化器会分析查询并确定哪些过滤条件可以下推到存储引擎层。然后优化器会生成一个优化后的执行计划其中包括下推的过滤条件。
存储引擎在执行查询时会根据优化器生成的执行计划利用下推的过滤条件来减少需要读取和处理的数据量。这通常涉及存储引擎利用索引来快速定位和过滤数据以及在存储引擎层面进行进一步的优化操作例如减少不必要的数据传输和处理。
总的来说索引下推的实现依赖于数据库系统的查询优化器和存储引擎的协作以及对查询执行计划的优化和索引的有效利用从而实现减少数据传输和处理的优化效果。 在数据库管理系统中缓冲池Buffer Pool是用于存储数据库中的数据页的内存区域。它充当了磁盘和内存之间的缓冲区以提高数据库的读取性能。
缓冲池中的数据是通过页Page进行组织的。每个页代表了数据库中的一个固定大小的块通常是4KB或8KB。这些页包含了数据库中的实际数据和元数据。
当数据库需要从磁盘读取数据时它首先检查缓冲池中是否已经存在所需的页。如果该页已经在缓冲池中则可以直接从内存中获取数据而无需访问磁盘。这样可以大大加快数据的读取速度。
如果所需的页不在缓冲池中数据库管理系统将从磁盘读取该页并将其放入缓冲池中。为了有效地管理缓冲池中的数据通常使用一种称为LRULeast Recently Used最近最少使用的算法来替换最久未使用的页。
缓冲池的大小是可配置的它取决于系统的可用内存和数据库的需求。较大的缓冲池可以容纳更多的数据页从而减少磁盘读取的频率提高数据库的整体性能。然而过大的缓冲池可能会导致内存不足的问题因此需要根据具体情况进行权衡和配置。
总而言之缓冲池中的数据是通过页进行组织的它提供了一个高效的方式来管理数据库中的数据页并加速数据库的读取操作。
在MySQL数据库中Change Buffer变更缓冲区是一种用于提高写入性能的机制。它主要用于延迟将数据页的修改操作写入磁盘而是先将这些修改操作记录在Change Buffer中。
当执行更新操作如插入、删除或更新行时MySQL会将这些修改操作记录到Change Buffer中而不是立即将其写入磁盘。这样可以减少对磁盘的随机写入操作从而提高写入性能。
Change Buffer是以页为单位进行组织的每个页都有一个对应的Change Buffer。当需要读取某个页的数据时MySQL首先检查Change Buffer中是否存在该页的未写入的修改操作。如果有MySQL会将Change Buffer中的修改应用到内存中的页上然后返回修改后的数据给用户。
定期地MySQL会将Change Buffer中的修改操作合并Merge到磁盘上的实际数据页中。这个过程通常发生在后台并且在系统负载较低的时候进行。合并操作的目的是保持数据的一致性并确保数据在重启之后仍然可用。
Change Buffer的使用可以有效减少磁盘的随机写入操作提高写入性能。尤其在具有大量随机写入操作的工作负载下Change Buffer的优化效果更为显著。然而Change Buffer也会占用一定的内存空间并且在某些情况下可能导致额外的磁盘I/O负载。
因此在配置MySQL时需要根据具体的工作负载和硬件条件来合理设置Change Buffer的大小以平衡性能和资源消耗。
索引失效
左模糊索引参与了运算使用函数进行表达式运算隐式转换where or了非索引in
索引原则 慢SQL 如何开启慢查询日志
