JAVA-DAY21（数据库中的索引）

qq_53439751

已于 2022-08-07 13:00:57 修改

阅读量608

点赞数 1

文章标签：大数据

于 2022-07-15 21:09:13 首次发布

本文链接：https://blog.csdn.net/qq_53439751/article/details/125782468

版权

本文详细介绍了数据库索引的优缺点，包括提升查询速度和占用额外存储空间。讲解了索引的创建、删除和修改，以及创建索引的原则。重点阐述了B+树作为索引的数据结构，对比了B树和B+树的区别，并讨论了聚簇和非聚簇索引。此外，还提到了如何根据数据离散度选择合适的索引，以及如何利用联合索引和覆盖索引进行查询优化。

摘要由CSDN通过智能技术生成

索引的优缺点

优点：索引可以大大提升查询的速度（最主要的原因）

缺点：（时间）牺牲增删改的速度，因为对表中数据进行增删改的时候，为了保证索引的有序性，需要动态的维护索引，会降低增/改/删的执行效率

（空间）创建索引需要占用硬盘（物理）空间

索引的增删改查

创建

1. 在create table 建表的时候添加索引

CREATE TABLE IF NOT EXISTS `user`(
id INT UNSIGNED PRIMARY KEY auto_increment COMMENT'主键ID',
username VARCHAR(32)UNIQUE NOT NULL COMMENT'用户名',

2. 在create table 建表成功后，使用alter语句添加索引

ALTER TABLE 表名 ADD INDEX 索引名 (字段名);

注意：ALTER TABLE用来创建普通索引、UNIQUE（唯一）索引或PRIMARY KEY（主键）索引。

3. 在create table 建表成功后，使用create index 语句添加索引

CREATE INDEX 索引名 ON 表名 (字段名);

注意：CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

创建索引时需要注意什么？

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的，数据越多一次IO操作获取的数据越大效率越高。
创建索引的原则
索引虽好，但也不是无限制的使用，最好符合一下几个原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。