网站建设的几种形式,铆焊加工平台,网站建设设计语言,个人空间网站模板目录 索引的分类
什么时候需要 / 不需要创建索引#xff1f;
有什么优化索引的方法
MySQL索引主要使用的两种数据结构是什么
为什么 MySQL 采用 B 树作为索引
聚簇索引和二级索引
根据给定的表#xff0c;如何创建索引比较好 索引的分类
普通索引#xff1a;最基本的…目录 索引的分类
什么时候需要 / 不需要创建索引
有什么优化索引的方法
MySQL索引主要使用的两种数据结构是什么
为什么 MySQL 采用 B 树作为索引
聚簇索引和二级索引
根据给定的表如何创建索引比较好 索引的分类
普通索引最基本的索引没有任何限制唯⼀索引与普通索引类似但索引列的值必须是唯⼀的允许空值主键索引⼀种特殊的唯⼀索引⼀个表只能有⼀个主键不允许有空值组合索引在多个字段上创建的索引只有在查询条件中使⽤了创建索引的第⼀个字段索引才会被使⽤全⽂索引主要⽤来查找⽂本中的关键字类似于搜索引擎
普通索引仅加速查询唯一索引加速查询 列值唯一可以有null主键索引加速查询 列值唯一不可以有null 表中只有一个组合索引多列值组成一个索引专门用于组合搜索其效率大于索引合并全文索引对文本的内容进行分词进行搜索索引合并使用多个单列索引组合搜索覆盖索引select的数据列只用从索引中就能够取得不必读取数据行换句话说查询列要被所建的索引覆盖聚簇索引表数据是和主键一起存储的主键索引的叶结点存储行数据(包含了主键值)二级索引的叶结点存储行的主键值。使用的是B树作为索引的存储结构非叶子节点都是索引关键字但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容) 什么时候需要 / 不需要创建索引
索引最大的好处是提高查询速度但是索引也是有缺点的比如
需要占用物理空间数量越大占用空间越大创建索引和维护索引要耗费时间这种时间随着数据量的增加而增大会降低表的增删改的效率因为每次增删改索引B 树为了维护索引有序性都需要进行动态维护。
什么时候适用索引
字段有唯一性限制的比如商品编码经常用于 WHERE 查询条件的字段这样能够提高整个表的查询速度如果查询条件不是一个字段可以建立联合索引。经常用于 GROUP BY 和 ORDER BY 的字段这样在查询的时候就不需要再去做一次排序了因为我们都已经知道了建立索引之后在 BTree 中的记录都是排序好的。
什么时候不需要创建索引
WHERE 条件GROUP BYORDER BY 里用不到的字段索引的价值是快速定位如果起不到定位的字段通常是不需要创建索引的因为索引是会占用物理空间的。字段中存在大量重复数据不需要创建索引比如性别字段只有男女如果数据库表中男女的记录分布均匀那么无论搜索哪个值都可能得到一半的数据。在这些情况下还不如不要索引因为 MySQL 还有一个查询优化器查询优化器发现某个值出现在表的数据行中的百分比很高的时候它一般会忽略索引进行全表扫描。表数据太少的时候不需要创建索引经常更新的字段不用创建索引比如不要对电商项目的用户余额建立索引因为索引字段频繁修改由于要维护 BTree的有序性那么就需要频繁的重建索引这个过程是会影响数据库性能的。 有什么优化索引的方法
这里说一下几种常见优化索引的方法
前缀索引进行优化覆盖索引进行优化主键索引最好是自增的防止索引失效
前缀索引进行优化
前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引那我们为什么需要使用前缀来建立索引呢
使用前缀索引是为了减小索引字段大小可以增加一个索引页中存储的索引值有效提高索引的查询速度。在一些大字符串的字段作为索引时使用前缀索引可以帮助我们减小索引项的大小。
不过前缀索引有一定的局限性例如
order by 就无法使用前缀索引无法把前缀索引用作覆盖索引
覆盖索引进行优化
覆盖索引是指 SQL 中 query 的所有字段在索引 BTree 的叶子节点上都能找得到的那些索引从二级索引中查询得到记录而不需要通过聚簇索引查询获得可以避免回表的操作。这种索引设计能够显著提高查询性能因为它减少了磁盘I/O操作和数据的传输。
假设我们只需要查询商品的名称、价格有什么方式可以避免回表呢
我们可以建立一个联合索引即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据查询将不会再次检索主键索引从而避免回表。
所以使用覆盖索引的好处就是不需要查询出包含整行记录的所有信息也就减少了大量的 I/O 操作。
主键索引最好是自增的
我们在建表的时候都会默认将主键索引设置为自增的具体为什么要这样做呢又什么好处
InnoDB 创建主键索引默认为聚簇索引数据被存放在了 BTree 的叶子节点上。也就是说同一个叶子节点内的各个数据是按主键顺序存放的因此每当有一条新的数据插入时数据库会根据主键将其插入到对应的叶子节点中。
如果我们使用自增主键那么每次插入的新数据就会按顺序添加到当前索引节点的位置不需要移动已有的数据当页面写满就会自动开辟一个新页面。因为每次插入一条新记录都是追加操作不需要重新移动数据因此这种插入数据的方法效率非常高。
如果我们使用非自增主键由于每次插入主键的索引值都是随机的因此每次插入新的数据时就可能会插入到现有数据页中间的某个位置这将不得不移动其它数据来满足新数据的插入甚至需要从一个页面复制数据到另外一个页面我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片导致索引结构不紧凑从而影响查询效率。
另外主键字段的长度不要太大因为主键字段长度越小意味着二级索引的叶子节点越小二级索引的叶子节点存放的数据是主键值这样二级索引占用的空间也就越小。
索引最好设置为 NOT NULL
为了更好的利用索引索引列要设置为 NOT NULL 约束。有两个原因
第一原因索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂更加难以优化因为可为 NULL 的列会使索引、索引统计和值比较都更复杂比如进行索引统计时count 会省略值为NULL 的行。第二个原因NULL 值是一个没意义的值但是它会占用物理空间所以会带来的存储空间的问题因为 InnoDB 存储记录的时候如果表中存在允许为 NULL 的字段那么行格式(opens new window)中至少会用 1 字节空间存储 NULL 值列表。
防止索引失效
用上了索引并不意味着查询的时候会使用到索引所以我们心里要清楚有哪些情况会导致索引失效从而避免写出索引失效的查询语句否则这样的查询效率是很低的。
这里简单说一下发生索引失效的情况
当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列做了计算、函数、类型转换操作这些情况下都会造成索引失效联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。
我上面说的是常见的索引失效场景实际过程中可能会出现其他的索引失效场景这时我们就需要查看执行计划通过执行计划显示的数据判断查询语句是否使用了索引。
执行计划的主要目的是帮助数据库管理员和开发人员理解数据库系统是如何执行查询的以便优化查询性能。通过分析执行计划可以识别潜在的性能问题、查询瓶颈和索引失效等。
在MySQL命令行或查询编辑器中输入要分析的SQL查询并在查询之前添加EXPLAIN关键字。例如
EXPLAIN SELECT * FROM your_table WHERE some_column some_value;
对于执行计划参数有
possible_keys 字段表示可能用到的索引key 字段表示实际用的索引如果这一项为 NULL说明没有使用索引key_len 表示索引的长度rows 表示扫描的数据行数。type 表示数据扫描类型我们需要重点看这个。
type 字段就是描述了找到所需数据时使用的扫描方式是什么常见扫描类型的执行效率从低到高的顺序为
All全表扫描index全索引扫描range索引范围扫描ref非唯一索引扫描eq_ref唯一索引扫描const结果只有一条的主键或唯一索引扫描。
在这些情况里all 是最坏的情况因为采用了全表扫描的方式。index 和 all 差不多只不过 index 对索引表进行全扫描这样做的好处是不再需要对数据进行排序但是开销依然很大。所以要尽量避免全表扫描和全索引扫描。
range 表示采用了索引范围扫描一般在 where 子句中使用 、、in、between 等关键词只检索给定范围的行属于范围查找。从这一级别开始索引的作用会越来越明显因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
ref 类型表示采用了非唯一索引或者是唯一索引的非唯一性前缀返回数据返回可能是多条。因为虽然使用了索引但该索引列的值并不唯一有重复。这样即使使用索引快速查找到了第一条数据仍然不能停止要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表因为索引是有序的即便有重复值也是在一个非常小的范围内扫描。
eq_ref 类型是使用主键或唯一索引时产生的访问方式通常使用在多表联查中。比如对两张表进行联查关联条件是两张表的 user_id 相等且 user_id 是唯一索引那么使用 EXPLAIN 进行执行计划查看的时候type 就会显示 eq_ref。
const 类型表示使用了主键或者唯一索引与常量值进行比较比如 select name from product where id1。
需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引不过这两个类型有所区别const 是与常量进行比较查询效率会更快而 eq_ref 通常用于多表联查中。 MySQL索引主要使用的两种数据结构是什么
哈希索引
对于哈希索引来说底层的数据结构肯定是哈希表因此在绝大多数需求为单条记录查询的时候可以选择哈希索引查询性能最快其余大部分场景建议选择BTree索引
BTree索引
Btree索引就是一种将索引值按一定的算法存入一个树形的数据结构中二叉树每次查询都是从树的入口root开始依次遍历node获取leaf。 为什么 MySQL 采用 B 树作为索引
要设计一个 MySQL 的索引数据结构不仅仅考虑数据结构增删改的时间复杂度更重要的是要考虑磁盘 I/0 的操作次数。因为索引和记录都是存放在硬盘硬盘是一个非常慢的存储设备我们在查询数据的时候最好能在尽可能少的磁盘 I/0 的操作次数内完成。
二叉搜索树虽然是一个天然的二分结构能很好的利用二分查找快速定位数据但是它存在一种极端的情况每当插入的元素都是树内最大的元素就会导致二分查找树退化成一个链表此时查询复杂度就会从 O(logn)降低为 O(n)。
为了解决二分查找树退化成链表的问题就出现了自平衡二叉树保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树每个节点只能有 2 个子节点随着元素的增多树的高度会越来越高。
而树的高度决定于磁盘 I/O 操作的次数因为树是存储在磁盘中的访问每个节点都对应一次磁盘 I/O 操作也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数所以树的高度越高就会影响查询性能。
B 树和 B 都是通过多叉树的方式会将树的高度变矮所以这两个数据结构非常适合检索存于磁盘中的数据。对于有 N 个叶子节点的 BTree其搜索复杂度为O(logdN)其中 d 表示节点允许的最大子节点个数为 d 个。
但是 MySQL 默认的存储引擎 InnoDB 采用的是 B 作为索引的数据结构原因有
B 树的非叶子节点不存放实际的记录数据仅存放索引因此数据量相同的情况下相比存储即存索引又存记录的 B 树B树的非叶子节点可以存放更多的索引因此 B 树可以比 B 树更「矮胖」查询底层节点的磁盘 I/O次数会更少。B 树有大量的冗余节点所有非叶子节点都是冗余索引这些冗余索引让 B 树在插入、删除的效率都更高比如删除根节点的时候不会像 B 树那样会发生复杂的树的变化
为什么冗余节点好
插入操作当你在B树中插入一个新的键值对时通常只需在叶子节点上进行操作而不需要修改非叶子节点。这是因为所有的键值对都存储在叶子节点上非叶子节点只包含索引信息。这减少了插入操作中的数据移动因此插入操作更高效。
删除操作当你从B树中删除一个键值对时也只需在叶子节点上进行操作。如果删除的键值对位于叶子节点上那么删除操作非常高效。即使你需要删除的键值对位于中间节点也不会像B树那样引发复杂的树变化因为中间节点仍然保留了部分冗余索引。
B 树叶子节点之间用双向链表连接了起来有利于范围查询而 B 树做不到要实现范围查询因此只能通过树的遍历来完成范围查询这会涉及多个节点的磁盘 I/O 操作范围查询效率不如 B 树。 BTree vs Hash Hash 在做等值查询的时候效率贼快搜索复杂度为 O(1)。 但是 Hash 表不适合做范围查询它更适合做等值的查询这也是 BTree 索引要比 Hash 表索引有着更广泛的适用场景的原因。 聚簇索引和二级索引
索引又可以分成聚簇索引主键索引和非聚簇索引二级索引它们区别就在于叶子节点存放的是什么数据
聚簇索引的叶子节点存放的是实际数据所有完整的用户记录都存放在聚簇索引的叶子节点二级索引的叶子节点存放的是主键值而不是实际数据。
因为表的数据都是存放在聚簇索引的叶子节点里所以 InnoDB 存储引擎一定会为表创建一个聚簇索引且由于数据在物理上只会保存一份所以聚簇索引只能有一个。
InnoDB 在创建聚簇索引时会根据不同的场景选择不同的列作为索引
如果有主键默认会使用主键作为聚簇索引的索引键如果没有主键就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键在上面两个都没有的情况下InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键
一张表只能有一个聚簇索引那为了实现非主键字段的快速搜索就引出了二级索引非聚簇索引/辅助索引它也是利用了 B 树的数据结构但是二级索引的叶子节点存放的是主键值不是实际数据。
我们经常需要根据非主键字段来进行查询。这就引入了二级索引
如果学生表的主键是学号那么主键索引将基于学号字段创建。如果你希望能够根据学生的姓名来进行快速搜索你可以创建一个基于姓名字段的二级索引。
这样二级索引将存储姓名与相应的主键学号之间的映射关系从而允许你通过姓名来快速定位到相应的学生记录而不必扫描整个表。这是数据库中典型的使用情况以支持多种查询需求。
因此如果某个查询语句使用了二级索引但是查询的数据不是主键值这时在二级索引找到主键值后需要去聚簇索引中获得数据行这个过程就叫作「回表」也就是说要查两个 B 树才能查到数据。
不过当查询的数据是主键值时因为只在二级索引就能查询到不用再去聚簇索引查这个过程就叫作「索引覆盖」也就是只需要查一个 B 树就能找到数据。 根据给定的表如何创建索引比较好
唯一性对于唯一性约束的字段例如身份证号或电子邮件地址应创建唯一索引以确保数据的完整性。如果尝试插入重复值数据库将引发唯一性约束冲突。在MySQL中可以使用UNIQUE关键字来创建唯一性索引。CREATE UNIQUE INDEX idx_email ON users (email);频繁用于搜索和过滤的字段对于经常在WHERE子句中用于搜索和过滤的字段例如日期、状态或类别创建索引可以显著提高查询性能。例如如果有一个订单表并且经常按订单日期过滤数据可以在订单日期列上创建索引。外键字段对于关联表中的外键字段最好创建索引。这将加速连接操作和JOIN查询。例如在订单表中的customer_id字段可以被索引以便快速检索特定客户的订单。不要过度索引避免过多的索引因为每个索引都需要额外的存储空间而且在写操作时需要维护。过多的索引可能会导致写操作性能下降。只创建那些真正需要的索引。组合索引对于经常使用多个字段进行查询的情况考虑创建组合索引。组合索引能够提高查询性能但要确保字段的顺序与查询中的顺序匹配。例如如果你经常在last_name和first_name上进行查询可以创建一个组合索引(last_name, first_name)。空值处理对于包含大量空值的字段不建议创建索引因为它们的查询效率较低。索引通常用于查找具有值的记录。数据量和性能测试在创建索引之前通过性能测试来评估查询性能。可以使用数据库查询分析工具来分析查询执行计划和性能瓶颈。根据测试结果和查询需求决定创建哪些索引以及如何优化它们。
