1 什么是索引
索引可加快检索的速度，提升查询性能，当前关系型数据库普遍采用的B+树索引，此索引是一种按字段排序的树形目录结构，主要用于提升查询性能和唯一约束支持。它的内容包括根节点、分支节点和叶子节点。根节点和分支节点主要保存了索引树的顺序以及各层级间引用关系；叶子节点保存索引字段内容（针对聚集索引存储的是实际数据，针对非聚集索引，存储的是聚集索引叶子节点的地址）。
索引的创建就是为了提升查询性能，索引就是把查询语句所需要的少量数据记录到索引分页中，这样访问数据时，只要访问少量数据的索引分页就可以了。
- 并不是索引越多越好，建少了，用where子句找数据效率比较低，建多了，执行增、删、改操作时，会额外的更新索引，导致性能较慢，同时，索引占用了磁盘空间，过多的索引也会导致空间浪费。
- 并不是有索引一定比没有索引要好。对于数据量较少的表，不需要创建索引，因为有索引时，系统首先会去寻找索引页，然后再去找实际的数据项。
2 索引分类
索引分为两大类：聚集索引和非聚集索引，其中，非聚集索引又可细分为普通索引、唯一索引、组合索引。
2.1 聚集索引
聚集索引规定了表中数据的物理存放顺序，数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引，可以类比于字典中的按拼音目录。它的优势在于：查询小数据量的表；查询指定范围内的数据。
在MySQL数据库的InnoDB存储引擎中，主键索引就是聚集索引，所有数据都会按照主键索引进行组织；而在MyISAM存储引擎中，就没有聚集索引了，因为MyISAM存储引擎中的数据不是按索引顺序进行存储的。
（1）聚集索引特点：
- 聚集索引的叶节点就是实际数据页；
- 在数据页中数据按照索引顺序存放；
- 行的物理位置和其在索引中的位置是相同的；
- 每个表只能有一个聚集索引。
（2）聚集索引不适用的场景
- 数据列频繁更改，这将导致整行移动。
2.2 非聚集索引
（1）非聚集索引是独立于数据表之外的结构，即数据存放在一个地方，索引存放在另一个地方，索引叶子节点通过其自带的指针指向数据的存储位置（聚集索引叶子节点）。
（2）表中的非聚集索引可以创建多个，可以为表常用的每个列都创建一个非聚集索引（索引个数适当就好，不要贪多）。
（3）形象的描述非聚集索引，可以用这个例子：一本介绍园艺的书可能包含一个植物通俗名称索引和一个植物学名索引，这两个索引就是非聚集索引。每当我们想看一个植物的详细信息时，先通过名称定为到页数，然后查看具体详情。
2.3 聚集索引和非聚集索引比较
（1）概念层次
- 聚集索引：物理存储按照索引排序，就像字典正文，我们按照正文的字母顺序就能找到我们的记录。
- 非聚集索引：物理存储不按照索引排序，就像偏旁查字法目录，每个偏旁目录下的字的页码不是顺序的。
（2）速度方面
- 聚集索引：插入数据时，速度较慢（时间主要花费在“物理存储的排序”上，也就是首先要找到位置然后再插入数据）。但查询数据的速度比非聚集索引要快。
（3）个数方面
- 聚集索引：每个表只能有一个聚集索引，默认主键是聚集索引，但经常业务上是不合适的，一定要将聚集索引建立在下面两种场景：第一，最频繁使用的、用以缩小查询范围的字段上；第二，最频繁使用的、需要排序的字段上。
- 非聚集索引：每个表可以创建多个。
（4）实际举例说明
我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字，您可以快速地从拼音表中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
（5）使用场景对比
| 动作描述 | 使用聚集索引 | 使用非聚集索引 |
|---|---|---|
| 列经常被分组排序 | 是 | 是 |
| 返回某范围内的数据 | 是 | 否 |
| 一个或极少不同值 | 否 | 否 |
| 小数目的不同值 | 是 | 否 |
| 大数目的不同值 | 否 | 是 |
| 频繁更新的列 | 否 | 是 |
| 外键列 | 是 | 是 |
| 主键列 | 是 | 是 |
| 频繁修改索引列 | 否 | 是 |
2.4 聚集索引和非聚集索引常见问题
（1）聚集索引的约束是唯一性，是否要求字段也是唯一的呢
分析：如果认为是的朋友，可能是受系统默认设置的影响，一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。
结论：聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。
（2）在数据库中通过什么描述聚集索引与非聚集索引的？
可以这样区分两者的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。
4 索引使用注意事项
4.1 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，组合索引中只要有一列含有NULL值，那么这一列对于此组合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
4.2 使用短索引
对串列进行索引，如果可能的话，应该指定一个前缀长度。例如，有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
4.3 索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此，数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建组合索引。
4.4 like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，不要将匹配符放于匹配字符串最前面，比如：like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。
4.5 不要在列上进行运算
例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：
select * from users where adddate<’2007-01-01′
关于这一点可以围观：一个单引号引发的MYSQL性能损失。
5 哪些场景会使索引失效
（1）以“%”开头的LIKE语句，模糊匹配
（2）OR语句前后没有同时使用索引
（3）数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）
（4）带非操作符，如<>/!=/not in/not exist
（5）列上作运算
最后总结一下，MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)，而对于<>，not in是不会使用索引的。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。
6 哪些场景需要创建索引
（1）不一定要创建索引，可通过优化sql实现。
（2）避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段(外键)应该创建索引，但要避免添加不必要的字段
（3）数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
（4）在条件表达式中经常用到的、不同值较多(主键的列)的列上建立索引，在不同值少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低更新速度。
（5）在频繁进行排序或分组（即进行GROUPBY或ORDERBY操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。在经常使用WHERE子句中的列上面创建索引，加快条件的判断速度。对于那些定义为text, image和bit数据类型的列不应该增加索引。
网友评论