【Java】 数据库索引学习笔记

Mysql索引学习笔记

索引本质：数据结构（平衡树[B tree,B+ tree]、哈希桶）

索引特点

  • 避免进行数据库全表扫描

哪些列上创建索引?

  • 经常需要搜索的列上
  • 作为主键的列上
  • 经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
  • 经常需要根据范围进行搜索的列上
  • 经常需要排序的列上
  • 经常使用在where子句上面的列上

不应该在哪些列上创建索引?

  • ①查询中很少用到的列
  • ②对于那些具有很少数据值的列.比如人事表的性别列,bit数据类型的列
  • ③对于那些定义为text,image的列.因为这些列的数据量相当大
  • ④当对修改性能的要求远远大于搜索性能时.因为当增加索引时,会提高搜索性能,但是会降低修改性能

索引的分类

物理存储：

聚集索引:

  • 表记录的排列顺序和索引的排列顺序一致,所以查询效率快,只要找到第一个索引值记录,其余连续性的记录在物理上一样连续存放.聚集索引的缺点就是修改慢,因为为了使表记录和索引的排列顺序一致,在插入记录的时候,会对数据页重新排序

  • 我们平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。 事实上， 一个加了主键的表，并不能被称之为「表」。一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。没错， 再说一遍， 整个表变成了一个索引，也就是所谓的「聚集索引」。这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

非聚集索引:

  • 表记录和索引的排列顺序不一定一致,两种索引都采用B+树的结构,非聚集索引的叶子层并不和实际数据页相重叠,而采用叶子层包含一个指向表记录的指针.非聚集索引层次多,不会造成数据重排

  • 非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。

  • 每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

逻辑角度：

主键索引：特殊的唯一索引，不允许有NULL。一般是在建表的时候同时创建主键索引

1
2
3
4
5
6
7
CREATE TABLE user(
    id int unsigned not null auto_increment,
    name varchar(50) not null,
    age  int(3) not null,
    email varchar(40) not null,
    primary key (id)
);

普通索引：最基本的索引，没有任何限制

唯一索引：与普通索引类似，不同点在于：索引列的值必须唯一，但允许有NULL

1
CREATE UNIQUE INDEX idx_email ON user(email);

**复合索引(多列索引，联合索引)**：多个字段上建立的索引，提高复合条件查询的速度

1
CREATE index idx_name_age on user(name,age);

索引失效

  • 条件中使用or(如果想使其生效则or条件中每列都要加上索引)
  • 最左匹配：如索引包含(a,b,c)三列，查询条件如bc,c,acb,bac都不行。abc则生效
  • 使用不等式 (!=或者<>)
  • 使用is null 或者is not null
  • like以通配符%开头，左模糊匹配
  • 如果全表扫描比使用索引快，则不使用索引
  • 对索引列进行运算（+-*/）或者函数操作

④合理使用联合索引的最左前缀原则

如果在(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

比如说把(username,password)建立了联合索引，因为业务上几乎没有password的单条件查询，而有很多username的单条件查询需求，所以应该建立(username,password)的联合索引，而不要建立(password,username)的联合索引

参考
https://blog.csdn.net/qq_36071795/article/details/83956068
https://blog.csdn.net/qq_34777600/article/details/81513401