面试必备之数据库索引

注：本文的索引知识点，主要是基于MySQL数据库，其他类型的数据库不一定适用。

概念

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。数据库索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。索引是为来加速对表中数据行中的检索而创建的一种分散的数据结果，是针对表而建立的，它是由数据页面以外的索引页面组成，每个索引页中的行都含有逻辑指针，以便加速检索物理数据。索引的实现通常使用B树及其变种B+树。

特点

索引可以加快数据库的检索速度；
索引降低数据库插入、修改、删除等维护任务的速度；
索引创建在表上，不能创建在视图上；
索引既可以直接创建，也可以间接创建；
可以在优化隐藏中，使用索引；
使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引；
索引需要占物理和数据空间；
索引是按照特定的数据结构把数据表中的数据放在索引文件中，以便于快速查找；
没有建立索引，可能导致慢查询，服务接口超时，数据库服务器CPU 100%；

存储引擎

MySQL在建表时可以指定引擎，create table () engine=InnoDB/MyISAM
MyISAM存储引擎：
.frm后缀的文件存储的是表结构。
.myd后缀的文件存储的是表数据。
.myi后缀的文件存储的就是索引文件。

InnoDB 存储引擎：
.frm后缀的文件存储的是表结构。
.ibd后缀的文件存放索引文件和数据，需开启innodb_file_per_table参数

从生成的文件看来，这两个引擎底层数据和索引的组织方式并不一样。MyISAM 引擎把数据和索引分开在两个不同的文件，是为非聚集索引方式；MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录。
Innodb 引擎把数据和索引放在同一个文件里，聚集索引方式。InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据。建表时InnoDB 就会自动建立好主键 ID 索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。

优缺点

为什么要创建索引呢？创建索引可以大大提高系统的性能。

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
可以大大加快数据的检索速度
索引可以把随机I/O变为顺序I/O
对索引结果进行排序以避免使用磁盘临时表
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
通过使用索引，可以在查询的过程中，使用优化隐藏器提高系统的性能

缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占物理空间，聚簇索引占用空间更大
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低数据的维护速度

索引区分度与基数

区分度：指字段在数据库中的不重复比。
基数：单个列唯一键（distinct keys）的数量。
在MySQL中，区分度的计算规则如下：字段去重后的总数（即基数）与全表总记录数的商：select count(distinct(name))/count(*) from t_user;
区分度取值[0.0, 1.0]，值越大，也就是数据不重复率越大，新建索引效果也越好。在主键以及唯一键上面的区分度是最高的，为1.0；一般在数据量大的情况下，在状态、性别等字段上面的区分度值非常小。如果表中没有任何记录时，计算区分度的结果是为空值
对于多列索引（联合索引），将区分度较高的放在前面，这样联合索引才更有效。
select * from t_user where status = 1 and name = '';
这样建立联合索引：alter table t_user add index idx_name_status(name,status);
因为status一般是枚举值，只有有限的几个数据；name则不是。

最左前缀匹配原则

MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

原则

建立索引的考虑原则与顺序：

业务实际，从实际出发，作为查询条件多的字段、查询频繁、影响产品体验的重要查询、数据量大的表，可以考虑添加索引；
区分度，区分度越大，越有建立的必要性；
最左前缀匹配原则；

索引是建立在数据库表中的某些列的上面。在创建索引时，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE和ORDER BY子句中的列上面创建索引，加快条件的判断速度。Tips：根据EXPLAIN来查看是否用索引还是全表扫描。

其他索引创建的注意事项：

尽量不用UNIQUE，由程序保证约束；
不用外键，由程序保证约束；
字符字段只建前缀索引；
字符字段最好不要做主键；
使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引；
扩展优先，而不是新建索引，即尽量修改已有索引，组建联合索引；
不能在索引列上进行函数运算，否则索引会失效。因为b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

不应该创建索引的列：

在查询中很少使用
对于那些只有很少数据值的列（即重复数据较多的列）也不应该增加索引。因为这些列的取值很少，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
对于那些定义为text，image和bit数据类型的列不应该增加索引，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。因为修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
如果MySQL估计使用全表扫描要比使用索引快，则不建索引。
表经常进行INSERT/UPDATE/DELETE操作就不要建立索引，换言之：索引会降低插入、删除、修改等维护任务的速度；

创建方法

包括直接创建索引的方法和间接创建索引的方法。直接创建索引，如使用CREATE INDEX语句或者使用创建索引向导；间接创建索引，例如在表中定义主键约束或者唯一性键约束时，同时也创建索引。这两种方法都可以创建索引，但是它们创建索引的具体内容是有区别的。

使用CREATE INDEX语句或者使用创建索引向导来创建索引，这是最基本的索引创建方式，可定制创建出符合自己需要的索引。在使用这种方式创建索引时，可以使用许多选项，例如指定数据页的充满度、进行排序、整理统计信息等，这样可以优化索引。使用这种方法，可以指定索引的类型、唯一性和复合性，也就是说，既可以创建聚簇索引，也可以创建非聚簇索引，既可以在一个列上创建索引，也可以在两个或者两个以上的列上创建索引。

通过定义主键约束或者唯一性键约束，也可以间接创建索引。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚簇索引。同样，在创建唯一性键约束时，也同时创建索引，这种索引则是唯一性的非聚簇索引。因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定，由用户定制的余地比较小。

当在表上定义主键或者唯一性键约束时，如果表中已经有了使用CREATE INDEX 语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。

MySQL索引操作常用语句

# 添加PRIMARY KEY（主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `name` )
# 添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `name` )
# 添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `name`)
# 添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `name`)
# 添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `name1`, `name2`, `name3` )
# 删除索引：
ALTER TABLE `table_name`   DROP  INDEX   name
ALTER TABLE 'table_name'   DROP   FOREIGN   KEY name
ALTER TABLE   'table_name'   DROP   INDEX   KEY name

索引特征

两个特征，即唯一性索引和复合索引。

唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束，那么当创建表或者修改表时，SQL Server 自动创建一个唯一性索引。然而，如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性索引。当创建唯一性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯一性键约束时，SQL Server 自动创建一个唯一性索引；如果表中已经包含有数据，那么当创建索引时，SQL Server 检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQL Server 检查数据的冗余性：如果有冗余值，那么SQL Server 取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上创建唯一性索引，例如，不能在人事表中的姓名列上创建唯一性索引，因为人们可以有相同的姓名。

复合索引
一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列；当表中有多个关键列时，复合索引是非常有用的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量。

索引和存储引擎的关系

MySQL中，索引是在存储引擎中实现的，并不是所有的存储引擎都支持所有的索引类型，比如哈希索引，MyISAM和InnoDB是不支持的；对于同一类型的索引，不同的存储引擎实现的方式也可能是不同的，比如MyISAM和InnoDB对B-Tree索引，具体的实现是有差别。
总结

不同的存储引擎可能支持不同的索引类型；
不同的存储引擎对同一中索引类型可能有不同的实现方式。

索引分类

其他概念或者分类，如唯一索引，主键索引，普通索引都是基于B-Tree索引算法的。根据不同的判断条件，有不同的分类，没有必要深究，只需要搞清楚其相对意义和区别即可：

主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。

直接创建索引和间接创建索引

直接创建索引：CREATE INDEX mycolumn_index ON mytable (myclumn)
间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引

普通索引和唯一性索引

普通索引：同直接创建索引语句类似
唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用：
CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

单个索引和复合索引
　- 单个索引：即非复合索引
　- 复合索引：又叫组合索引，联合索引，在多个字段上建立的索引，能够加速复合查询条件的检索。在索引建立语句中同时包含多个字段名，最多16个字段；联合索引能够满足最左侧查询需求，即最左匹配原则。
聚簇索引和非聚簇索引
覆盖索引：被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

聚集索引和非聚集索引

回表

当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。

EXPLAIN命令结果中的Using Index意味着不会回表，通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据。

聚集索引

又叫群集索引，聚簇索引，clustered index，物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列；聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引，一般用primary key来约束。聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引在叶子节点存储的是表中的数据。
缺点：修改慢，为了保证表中记录的物理和索引顺序一致，在记录插入时，会对数据页重新排序。
创建：CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH ALLOW_DUP_ROW(允许有重复记录的聚簇索引)。

非聚集索引

也叫非聚簇索引，non-clustered index，二级索引，非聚集索引指定表中记录的逻辑顺序，但是记录的物理和索引不一定一致，非聚集索引层次多，不会造成数据重排。

非聚集索引在叶子节点存储的是主键和索引列，并不和实际数据页相重叠；使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

非聚集索引未必是单列，可以多个列来创建索引。此时就涉及到哪个列会走索引，哪个列不走索引的问题（最左匹配原则）。创建多个单列(非聚集)索引时，会生成多个索引树(所以过多创建索引会占用磁盘空间)。

创建：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

区别

两种索引都采用B+树结构
根本区别：表记录的排列顺序和与索引的排列顺序是否一致。

聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。
聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。
添加聚集索引，此时发现表的顺序发生变化，此时的排序按A字段的递增排序；删除聚集索引，会发现表的顺序不会发生改变；添加非聚集索引，添加新的记录，并没有影响表的顺序。

什么时候适合聚集索引，什么时候适合非聚集索引？

动作描述	使用聚集索引	使用非聚集索引
列经常分组排序	✔	✔
返回某范围内的数据	✔	✖
很少的不同值	✖	✖
小数目不同值	✔	✖
大数目不同值	✖	✔
频繁更新的列	✖	✔
外键列	✔	✔
主键列	✔	✔
频繁修改索引列	✖	✔

索引与主键的区别

主键：主键是唯一的，用于快速定位一条记录。
聚集索引：聚集索引也是唯一的。（因为聚集索引的划分依据是物理存储）。而聚集索引的主要是为了快速的缩小查找范围，即记录数目未定。
主键和索引没有关系。用途相近。如果聚集索引加上唯一性约束之后，作用就一样。

参考：
SQL聚集索引和非聚集索引的区别

索引使用

当字段数据更新频率较低，查询使用频率较高并且存在大量重复值是建议使用聚簇索引。
经常同时存取多列，且每列都含有重复值可考虑建立组合索引。
复合索引的前导列一定好控制好，否则无法起到索引的效果。如果查询时前导列不在查询条件中则该复合索引不会被使用。前导列一定是使用最频繁的列
多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案
where子句中对列的任何操作结果都是在sql运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被sql优化器优化，使用索引，避免表搜索。

例：select * from record where substring(card_no,1,4)='5378' && select * from record where card_no like '5378%'
任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边

where条件中的in在逻辑上相当于or，语法分析器会将in ('0','1')转化为column='0' or column='1'来执行。期望它会根据每个or子句分别查找，再将结果相加，这样可以利用column上的索引；但实际上它却采用"or策略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用column上索引，并且完成时间还要受tempdb数据库性能的影响。in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。

使用索引为什么能提高查询速度

简版回答：索引就是通过事先排好序，从而在查找时可以应用二分查找等高效率的算法。一般的顺序查找，复杂度为O(n)，而二分查找复杂度为O(log2n)。创建索引后，可由全表扫描（逐条扫描全部记录，直到找完符合条件的）改成索引扫描（可以直接定位）。
不管数据表有无索引，首先在SGA的数据缓冲区中查找所需要的数据，如果数据缓冲区中没有需要的数据时，服务器进程才去读磁盘。
1、无索引，直接去读表数据存放的磁盘块，读到数据缓冲区中再查找需要的数据。
2、有索引，先读入索引表，通过索引表直接找到所需数据的物理地址，并把数据读入数据缓冲区中。

副作用

索引是有大量数据的时候才建立的，没有大量数据反而会浪费时间，因为索引是使用二叉树建立；
当一个系统查询比较频繁，而新建/修改等操作比较少时，可以创建索引，这样查询的速度会比以前快很多，同时也带来弊端，就是新建或修改等操作时，比没有索引或没有建立覆盖索引时的要慢。
太多索引会占用很多的索引表空间，甚至比存储一条记录更多。
对于需要频繁新增记录的表，最好不要创建索引，没有索引的表，执行insert、append都很快，有索引后，会多一个维护索引的操作，一些大表可能导致insert 速度非常慢。

索引失效

like查询以%开头，like '%XX' or like '%XX%'；
where 子句中使用!=或<>操作符；
in 和 not in 也会导致全表扫描，如：select id from t where num in(1,2,3);，解决方法：1. 换成between：select id from t where num between 1 and 3; 2. 换成exists：select num from a where num in(select num from b);换成select num from a where exists(select 1 from b where num=a.num);
where 子句中对字段进行表达式操作：select id from t where num/2=100;
如果条件中有or，即使其中有条件带索引也不会使用索引（因此尽量少用or），要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不走索引。
在WHERE子句中对字段进行NULL值判断，将导致引擎放弃使用索引而进行全表扫描。

MySQL主要提供2种方式的索引：B-Tree索引，Hash索引
B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。
哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。
如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

索引不存储null值
更准确的说，单列索引不存储null值，复合索引不存储全为null的值。索引不能存储Null，所以对这列采用is null条件时，因为索引上根本没Null值，不能利用到索引，只能全表扫描。
为什么索引列不能存Null值？
将索引列值进行建树，其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null。
这样的话，null值实际上是不能参与进建索引的过程。也就是说，null值不会像其他取值一样出现在索引树的叶子节点上。

参考
MySQL索引专题一认识索引
 MySQL索引设计不可忽视的知识点

posted @ 2018-12-09 23:57 johnny233 阅读(19) 评论(0) 编辑收藏举报来源

刷新页面返回顶部

johnny233

搬自CSDN：https://blog.csdn.net/lonelymanontheway