索引能显著提高数据库查询速度，通过创建唯一性索引确保数据唯一，加速表间连接和排序。然而，它们也需要额外的物理空间，维护索引会降低数据插入、删除和修改的速度。创建索引应基于字段的查询频率、唯一性和数据操作类型。索引类型包括B-Tree、Hash、R-Tree和Fulltext，以及聚集和非聚集索引。主键索引是一种特殊的唯一索引，而聚集索引使数据行与键值顺序一致，优化IO密集型应用，但也可能导致页分裂和全表扫描变慢。
索引的好处
创建索引可以大大提高数据库系统的查询性能、将随机 I/O 变为顺序 I/O（磁盘顺序）
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时，同样可以 显著减少查询中分组和排序的时间。帮助服务器避免排序和临时表
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
建立索引需要付出的代价（缺点）
-
索引需要占物理空间
除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引（是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起），那么需要的空间就会更大。
-
创建索引和维护索引要耗费时间
这种时间随着数据量的增加而增加。
-
降低维护速度
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度，同样降低了效率。
判断是否应该建索引的条件
- 较频繁的作为查询条件的字段应该创建索引
- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
- 增、删、改操作较多的数据库字段不适合建索引（加了索引后 update 会增加耗时）
- 表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。
对于小型的表，使用全表扫描更高效；对中到大型的表，使用索引非常有效。对于特大型的表，建立和使用索引的代价会随之增长。这种情况下可以使用分区来查出一组数据，而不是一条一条地匹配 - 性别字段（只有男、女）不适合建索引，性别字段的选择性低，使用索引查找还不如遍历表的效率高。最多只能少检索一半，但索引的额外开销更大，得不偿失
索引的类型
从数据结构角度：
- B-Tree 索引、
- hash 索引（memory 引擎支持，InnoDB支持自适应哈希索引）、
- R-Tree 索引（空间数据索引 ，MyISAM 支持 ， 用作地理数据存储 ） 、
- FULLTEXT （ 全文 ） 索引 （ MyISAM 、InnoDB 支持 ）
从物理存储角度：
聚集索引、非聚集索引
从逻辑角度 ：
- **普通索引 **
- **唯一索引 **
- **主键索引 **
尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。 - 空间索引 （ 只有MyISAM 支持且支持的不好
普通索引
索引值可以不唯一
唯一索引
唯一索引是不允许其中任何两行具有相同索引值的索引。
当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。
主键索引
数库表经常有一列或多列组合（联合主键：就是用多个字段一起作为一张表的主键。），其值唯一标识表中的每一行。该列称为表的主键。
在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
聚集索引
聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引（聚焦索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序，所以一个表只能建一个聚集索引。）
InnoDB 将通过主键聚集数据
“主键就是聚焦索引”这是极端错误的，是对聚焦索引的一种浪费。
主键并不一定是聚集索引，只是在 SQL SERVER 中，未明确指出的情况下，默认将主键定义为聚集，而 ORACLE 中则默认是非聚集
不是单独的索引类型，而是一种数据存储方式，指的是**数据行跟相邻的键值紧凑的存储**在一起
优点
-
可以把相关数据保存在一起
-
数据访问更快，因为索引和数据保存在同一个树
-
使用覆盖索引扫描的查询可以直接使用页节点中的主键值（二级索引叶节点储存主键）
这样做的理由（来自书本的补充）：减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB 在移动行时无须更新二级索引中的这个“指针”
缺点
- 聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
- 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候**，可能面临页分裂的问题**
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
- 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列
- 二级索引访问需要两次索引查找，而不是一次
为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值
解：这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作:两次B-Tree查找而不是一次。对于InnoDB，自适应哈希索引（当 InnoDB 注意到某些索引值被使用得非常频繁时，它会在内存中基于 B-Tree 索引之上再创建一个哈希索引）能够减少这样的重复工作
非聚簇索引
数据文件跟索引文件分开存放
如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配（逻辑连续的可能物理不连续）。与非聚集索引相比，聚集索引通常提供更快的数据访问速度
聚集索引和非聚集索引的区别
如字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。
聚集索引中为什么推荐代理主键
这种主键的数据应该和应用无关，最简单的方法是使用AUTO_ INCREMENT 自增列。这样可以保证数据行是按顺序写人，对于根据主键做关联操作的性能也会更好
最好避免随机的(不连续且值的分布范围非常大)聚簇索引，特别是对于1/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕:它使得聚簇索引的插人变得完全随机，这是最坏的情况，使得数据没有任何聚集特性，下面总结了一些缺点：
- 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插人之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O
- 因为写入是乱序的，InnoDB 不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页
- 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片
在把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZE TABLE 来重建表并优化页的填充。不难看出，使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行
顺序的主键什么时候会造成更坏的结果?（了解）
对于高并发工作负载，在 InnoDB 中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是 AUTO_INCREMENT 锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改 innodb_autoinc_lock_mode 配置。如果你的服务器版本还不支持 innodb_autoinc_lock_mode 参数，可以升级到新版本的 InnoDB，可能对这种场景会工作得更好。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
