普通索引和唯一索引的思考

大家好，我是IT修真院深圳分院java第4期学员，一枚正直善良的java程序员。今天给大家分享一下，修真院官网java任务一中关于普通索引和唯一索引的区别，以及在任务中使用的一些感想。

1.背景介绍

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。 从数据搜索实现的角度来看，索引也是另外一类文件/记录，它包含着可以指示出相关数据记录的各种记录。

2.知识剖析

在了解数据库索引之前，首先了解一下数据库索引的数据结构基础：B+tree

说一下重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）。如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

查询时B+Tree到底是怎么运作的？简单分析一下：

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+Tree可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

B+Tree的性质：

1.n棵子tree的结点包含n个关键字，不用来保存数据而是保存数据的索引。

2.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

索引分类：

mysql的索引分为单列索引(主键索引,唯一索引,普通索引)和组合索引。

单列索引:一个索引只包含一个列,一个表可以有多个单列索引。

组合索引:一个组合索引包含两个或两个以上的列。

普通索引,这个是最基本的索引。

唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样，允许有空值。 建立唯一索引一个比较重要的作用是避免数据出现重复。

主键索引,不允许有空值。主键索引建立的规则是 int优于varchar,一般在建表的时候创建, 最好是与表的其他字段不相关的列或者是业务不相关的列。一般会设为 int 而且是 AUTO_INCREMENT自增类型的。

索引的优点：

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性

2.建立索引可以大大提高检索的数据,以及减少表的检索行数

3.在表连接的连接条件，可以加速表与表直接的相连

4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)

5.建立索引,在查询中使用索引，可以提高性能。

索引的缺点：

1.在创建索引和维护索引，会耗费时间,随着数据量的增加而增加

2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间

3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件)。

关于索引的优缺点大家结合B+Tree很容易就理解了。

3.常见问题

1:如何创建索引？

2:隐式类型转换对MySQL选择索引的影响？

3:什么情况下建索引？

4.什么情况不建索引？

5:索引对提高查询速度能提升多少性能？

4.解决方案

sql插入索引语句：

普通索引：ALTER TABLE table_name ADD INDEX index_name ( column )

唯一索引：ALTER TABLE table_name ADD UNIQUE ( column )

主键索引：ALTER TABLE table_name ADD PRIMARY KEY ( column )

当文本字段与数字进行比较时，由于类型不同，MySQL 需要做隐式类型转换才能进行比较。

默认转换规则是：

不同类型全都转换为浮点型

如果字段是字符，条件是整型，那么会把表中字段全都转换为整型

什么情况下要建立索引：

1.在经常需要搜索的列上,可以加快索引的速度。

2.主键列上可以确保列的唯一性。

3.在表与表的而连接条件上加上索引,可以加快连接查询的速度。

4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间,   (单独order by 用不了索引，索引考虑加where 或加limit)。

5.尽量选择区分度高的列作为索引。

6.索引列不能参与计算，保持列“干净”。

7.尽量的扩展索引，不要新建索引

什么情况下不建立索引：

1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求。

2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行 的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率。

3.定义为text和image和bit数据类型的列不应该增加索引。

4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系。

5.编码实战

6.扩展思考

如果对学员QQ号做了一个唯一索引，在插入数据的时候，是否需要先判断这个QQ号已经存在了？

如果qq号做了唯一索引，我们插入时会提示我们重复插入，阻止我们插入。但是要是在现实业务中，我们需要把这个反馈给客户端，所以我们是需要做一个判断，如果qq号重复就把这个信息反馈给客户端。

7.参考文献

参考一：MySQL索引原理及慢查询优化

参考二：类型转换对MySQL选择索引的影响

参考三：MySQL8.0参考手册

8.更多讨论

PPT链接：戳这里

腾讯视频：戳这里

今天的分享就到这里啦，欢迎大家点赞、转发、留言、拍砖~