2 创建联合索引_索引概述

什么是索引

数据库中的索引,就好比一本书的目录,他可以帮我们快速进行特定值的定位于查找,从而加快数据查询的效率

只要学习过 sql 都知道索引的重要性，索引在 sql 查询中起到很大的作用，索引用的好，可以有效的加快数据查询速度，大打减少I/O的操作，我看了一篇文章，索引是万能的么？当然不是，刚开始我只是知道索引肯定不是万能的，但是我却不太了解索引在哪些情况下不是万能的，通过这篇文章让我学习到了关于索引有关的知识

索引是万能的吗？

索引就好比一本书中的目录，就是帮助数据库管理系统高效获取数据的数据结构

如果我们不适用索引，当我们要在新华字典里查找某个字（例如“他”）具体含义的时候，就只能从第一条记录开始查询，查找每一页是否有“他”这个字，这样做（对应数据库中的全局扫描）确实可以找到，但是效率是非常低的，更高效的方法就是在首页李曼找到“他”对应的页数，然后直接跳到相应的页面查找，这样查找无疑大大的加快了查询速度，也就是创建索引，既然如此，那我们想要查找数据，就直接创建更多的索引就好了

但是索引不是万能的，在有些情况下使用索引反而会让效率变低

以下几点是我总结的当使用索引会让效率变低的情况：

1.当数据比较少的情况下，例如不到1000行
2.当数据重复度大，基数太小的情况下，例如“正常”情况下的性别字段就最好不要加索引
3.当一张表，增加删除修改的操作比查询的时候多，那么用索引就会降低性能
4.对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，
要么取值很少,不利于使用索引。

实验1：当数据量比较小的时候，比较索引效率

同样我根据他提供的数据表，做了相同的实验，有两张表分别是heros_without_index.sql 和 heros_with_index.sql

在第一张数据表中，除了自增的id以外没有建立额外的索引，在第二张数据表中，对name字段创建了唯一索引。

这两张表中都只有69条数据，数据量很少，当分别对没有索引和有索引的不同表中的name字段进行条件查询时，查看一下创建索引前后的效率如何，查询代码：

SELECT id, name, hp_max, mp_max FROM heros_without_index WHERE name = '刘禅

运行结果及时间如下图：（1条数据，条件查询字段不加索引运行 0.002s）

接下来查询建立索引的表中字段：

SELECT id, name, hp_max, mp_max FROM heros_with_index WHERE name = '刘禅'

运行结果及时间如下图：（1条数据，条件查询字段加索引运行 0.004s）

这时我们就能得出结论，在数据量不大的情况下，有时添加索引的效率会变的更低，所以一般在数据量较小的情况下就不需要创建索引

实验二：性别字段是否要添加索引

如果不是非正常的性别字段，特殊的情况下，由于他给的表中数据较大，我只保存了一万条数据用来测试，人口总数为一万人，男性只有1人，也就是占总人口的万分之一。

总⼈⼝数据表 user_gender其中数据表中的 user_gender 字段取值为 0 或 1，0 代表⼥性，1 代表男性。

如果我们要筛选出这个国家中的男性，代码如下：

SELECT * FROM user_gender WHERE user_gender = 1

运行结果及时间如下图：（1条数据，当条件语句不加索引运行时间0.008s）

上面是未添加索引的情况下，接下来我们对条件语句 user_gender 添加普通索引再次运行如下：

SELECT * FROM user_gender WHERE user_gender = 1

运行结果及时间如下图：（1条数据，当条件语句不加索引运行时间0.001s，有时达到0.000s）

根据上面两个实验我们可以得出结论，当数据量很大时，索引才会体现出他的作用，索引的价值也就是帮助我们快速定位，加快查询速度，当然如果数据的重复数据太大时，索引就失去了他的使用价值，所以我们不能只要是查询时就选择给该字段添加索引，我们应该考虑字段中的数值个数，还有根据数值的分布情况来考虑是否需要创建索引，根据实际情况来判断是否需要添加索引。

什么时候应该创建索引

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建

索引。一般来说，应该在这些列上创建索引我做了如下简单总结：

1.在经常需要搜索的列上，可以加快搜索的速度；
2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
5.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6.在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度；

索引的种类

从逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。

想要了解以上四种索引种类，参考这篇文章：索引

其实前三种索引（普通索引、唯⼀索引和主键索引）都是⼀类索引，只不过对数据的约束性逐渐提升。在⼀张数据表中只能有⼀个主键索引，这是由主键索引的物理实现⽅式决定的，因为数据存储在⽂件中只能按照⼀种顺序 进⾏存储。但可以有多个普通索引或者多个唯⼀索引

按照物理实现方式，索引可以分为 2 种：聚集索引和⾮聚集索引。我们也把⾮聚集索引称为二级索引或者辅助索引。

聚集索引

聚集索引可以按照主键来排序存储数据，这样在查找行的时候非常有效；一个表中只能包含一个聚集索引，因为数据行本身只能按⼀个顺序存储。

非聚集索引

在非聚集索引中，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。一个表中只能有一个聚集索 引，但表中的每一列都可以有自己的非聚集索引。

非聚集索引与聚集索引的区别：

·1.聚集索引的叶⼦节点存储的就是我们的数据记录，⾮聚集索引的叶⼦节点存储的是数据位置。⾮聚
集索引不会影响数据表的物理存储顺序。

·2.⼀个表只能有⼀个聚集索引，因为只能有⼀种排序存储的⽅式，但可以有多个⾮聚集索引，也就是
多个索引 ⽬录提供数据检索。

·3.使⽤聚集索引的时候，数据的查询效率⾼，但如果对数据进⾏插⼊，删除，更新等操作，效率会⽐
⾮聚集索引低。

实验三：使⽤聚集索引和⾮聚集索引的查询效率

还是根据之前的 user_gender 数据表，在表中，设置了 user_id 为主键，也就是聚集索引的字段是 user_id。

下面根据 user_id=90001 查询用户信息：

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001

运行结果及时间如下图：（1条数据，有聚集索引运行时间 0.001s）

接下来再直接对 user_name 字段进⾏条件查询，此时 user_name 字段没有创建索引

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = 'student_890001'

运行结果及时间如下图：（1条数据，非聚集索引运行时间 0.020s）

由上面两个可以的出结论，没有进行建立索引的字段，查询时速度明显降低

然后我们对 user_name 字段创建普通索引，进⾏ SQL 查询

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = 'student_890001'

运行结果及时间如下图：（1条数据，普通索引运行时间 0.009s）

根据这三次的 sql 查询结果的对比，可得出结论：

1.对 WHERE ⼦句的字段建⽴索引，可以⼤幅提升查询效率。 
2.采⽤聚集索引进⾏数据查询，⽐使⽤⾮聚集索引的查询效率更快。

索引按照字段个数进行划分

单一索引：索引列只有⼀列，这是称为单⼀索引；

联合索引：由单一索引可知，多个列组合在⼀起创建的索引就叫做联合索引

最左匹配原则： 把有索引的条件放到查询条件的前面，也就是按照最左优先的⽅式进⾏索引的匹配，当联合索引使用时需要将多个带有索引的列条件按顺序都写在条件语句前面，只写一列索引条件不能触发联合索引

实验4：联合索引的最左匹配原则

根据 user_gender 数据表，将 user_id 和 user_name 字段设置为联合主键，查看 SQL 查询效率区别。

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001 AND user_name = 'student_890001'

运行结果及时间如下图：（1条数据，运行时间 0.001s）

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001

运行结果及时间如下图：（1条数据，运行时间 0.001s）

在查询普通条件查询:

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = 'student_890001'

运行结果及时间如下图：（1条数据，运行时间 0.015s）

由实验可知：当我们使用联合索引时，在条件语句中对 user_id 和 user_name 字段进行条件查询，和只对user_id

字段进行查询，效率是基本是一样的，但是只对 user_name 字段进行条件查询时，效率就会降低很多，这是因为根

据联合索引的最左匹配原则，user_id 在 user_name 的左侧，如果没有使⽤ user_id，⽽是直接使⽤ user_name 进

⾏条件查询，联合索引就会失效。

总结

索引优点：

1.大大加快数据的检索速度
2.创建唯一索引，可以保证数据表中的每一行数据的唯一性
3.加快表和表之间的连接
.....

索引缺点：

1.索引需要占用一定的物理空间
2.当对表中的数据进行增加、删除和修改操作的时候，索引也需要动态的维护，降低了数据的维护速度
3.有多个索引还会增加索引选择的时间

当我们选择使用索引时，需要先对数据进行判断，根据表需要基于需求和数据本⾝的分布情况来确定是否使用索引，

索引虽然不是万能的，但是在数据量大的时候不适用索引查询速率会大大打折扣，，毕竟索引的本质，是帮助我们提升数据检索的效率；