大家好,我是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索引原理及慢查询优化
参考三:MySQL8.0参考手册
8.更多讨论
PPT链接:戳这里
腾讯视频:戳这里
今天的分享就到这里啦,欢迎大家点赞、转发、留言、拍砖~